Prerequisites

• Fabric Capacity for SQL Database, Power BI, and Copilot
• An Azure OpenAI Service with the gpt-4 and text-embedding-ada-002 model deployed

Starting the workshop

1. The Microsoft Fabric Portal

In this section of the workshop, you will be logging into the Microsoft Fabric Portal and creating a new Fabric Workspace.

Logging into the Microsoft Fabric Portal

1. Using a web browser, please navigate to this Microsoft Fabric link.

2. Creating a Workspace and SQL Database

Create the Microsoft Fabric Workspace

1. Back on the Microsoft Fabric Database Home Page, click the New Workspace tile to open the Create a workspace blade on the right side.

   

2. In the Create a workspace blade,

   

   double check to make sure the cursor is in the Name field

   

   and enter a unique name for the Workspace Name field.

   

3. Next, click the green Apply button on the bottom left of the Create a workspace blade.

   

4. On the following page, you may get a popup titled "Introducing task flows (preview)". Click the green Got it button.

   

Create the Azure SQL Database in Microsoft Fabric

1. On the Microsoft Fabric Workspace page, click the New item button on the top right of the page.

   

2. In the New item blade on the right,

   

   use the Filter by item type search box in the upper right

   

   to enter SQL

   

3. With the New item results filtered down, click on the SQL database (preview) tile.

   

1. In the New SQL database dialog window,

   

   Use a unique name to name the database.

   

2. With the database name entered, click the green Create button.

   

3. Once the database is finished creating,

   

   you will be taken to that SQL database's home page where we can see database objects and issue T-SQL statements right in the web browser.

   

Loading the database with sample data

1. We need some sample data in the database to work with. We can easily do this with the Sample data tile right on the database home page. Click the Sample data tile right on the database home page.

   

2. In the upper right corner of the database home page, you will see a notification indicating that the data is being loaded into the database.

   

3. Allow this process to run (about 30-60 seconds) until you see a notification indicating that the data was successfully loaded into the database appearing again, in the upper right corner.

   

4. Also, once the data has finished loading, the middle of home page will change show a Query, preview, or connect your data message and image.

   

3. Working with the Azure SQL Database in Microsoft Fabric

In this next section, we will be using the Database Explorer and SQL Query worksheets in Microsoft Fabric

1. To start, look_the Database Explorer area on the left of the page. Here, click the dropdown arrow next to the database

   

   to see a list of database schemas.

   

   and you can further expand the SalesLT schema to see object types

   

   as well as objects in the object type folders.

   

2. If not already done, expand the SalesLT schema, followed by expanding the Tables folder. Then click on the Address table.

   

3. You can see in the editor window, a read only Data preview of the contents of the Address table.

   

4. After browsing the data in the Address table, close the Data preview by clicking on the X next to the Address Data preview tab.

   

5. Now, click the New Query button on the tool bar

   

   to open a new query editor window so we can work directly with the database.

   

6. To remove the gray Copilot Preview Banner, click the X on the right side.

   

7. Once the banner is gone, be sure to click in the SQL editor sheet so that the next step will copy the code to the correct location.

   

8. Copy and paste the following code into the query editor:

   select * from [SalesLT].[Product]
   

9. Once the code is in the query editor, click the Run button.

   

10. You will see the results of the query on the bottom of the query editor.

    

11. Starting on the left side of the Results area, there are 3 options for exporting the data.

    

12. From left to right, first is download results as .xlsx (Excel) file.

    

13. Next, is download as a .csv file

    

14. With the last option being download as a .json file.

    

15. And looking_the right side of the results area, you can use the Copy button to copy the results in multiple formats.

    

16. Now, refresh the browser page by clicking the refresh icon on the Edge Browser toolbar.

    

17. The query editor you were working with is no longer in the main window

    

    but we can easily bring it back, or any other query editors we have been working with. We can do this by clicking on the editor sheet name in Explorer on the left side of the page in the Queries folder.

    

18. Also, by clicking on the 3 dots next to a query editor sheet name, you can duplicate it, rename it, or delete it.

    

19. To get ready for the next section, Delete any SQL you have in the query editor so that it is blank. Do not delete the entire query sheet.

    

Copilot for the SQL Database in Microsoft Fabric

Microsoft Copilot for SQL database in Fabric is an AI assistant designed to streamline your database tasks.

Copilot Chat

Use the chat pane to ask questions to Copilot through natural language. Copilot responds with a generated SQL query or natural language based on the question asked.

• Natural Language to SQL: Generate T-SQL code from plain text requests, allowing users to query data without needing to know SQL syntax.
• Document-based Q&A: Ask Copilot questions about general SQL database capabilities, and it responds in natural language. Copilot also helps find documentation related to your request.

1. To use Copilot chat, click the Copilot button on the Database details homepage toolbar.

   

2. On the right side of the page, you will see the Copilot Chat pane.

   

   click the green Get started button on the bottom of the chat pane to continue.

   

3. Let's use one of the suggested questions that the chat pane has offered us. Click the Get Insights: Retrieve the total number of tables in my database. button. This will add the text "Retrieve the total number of tables in my database." in the chat box on the bottom of the Chat Pane.

   

4. Once the text is in the chat text box, click the Arrow/Paper Airplane Icon on the right of the chat text box.

   

   and Copilot will start answering the question

   

5. Once the answer is returned by Copilot,

   

   you can either click the copy code button and paste it into the query editor

   

   or click the insert code button to have it instantly pasted into the current active query editor sheet.

   

6. Once the code is copied into the query editor, you click the run button it to see the results.

   

7. Try the following question suggestions in the chat pane and see the results. Feel free to run the SQL it provides from the answers or just move on to the next question:

   Important

   Copilot for SQL database in Microsoft Fabric is in preview and you may encounter unexpected results. If so, try the question again or move on to the next question.

   

   Performance and Database Questions

   What table is using the most space?
   

   

   Are there any poorly performing indexes?
   

   

   Object Manipulation Questions

   Help me create a table to store AI chat history and code to insert some sample rows?
   

   

   Help me alter the Address table to add a Subdivision column.
   

   

   Documentation Questions

   What is an append only ledger table?
   

   

   Does SQL in fabric support CLR?
   

   

8. When done with the questions, you can close the Copilot for SQL chat window using the X on the top right.

   

SQL Editor and Quick Actions

Start writing T-SQL in the SQL query editor and Copilot will automatically generate a code suggestion to help complete your query. The Tab key accepts the code suggestion or keeps typing to ignore the suggestion.

In the ribbon of the SQL query editor, the Fix and Explain options are quick actions. Highlight a SQL query of your choice and select one of the quick action buttons to perform the selected action on your query.

• Fix: Copilot can fix errors in your code as error messages arise. Error scenarios can include incorrect/unsupported T-SQL code, wrong spellings, and more. Copilot will also provide comments that explain the changes and suggest SQL best practices.
• Explain: Copilot can provide natural language explanations of your SQL query and database schema in comments format.

1. In an empty query editor, copy and paste the following code.

   -- list the top 5 colors of products
   

   

2. Then press enter/return_the end of the line of text in the query editor. On the bottom of the query editor, you should see that "Copilot is working on it..."*

   

3. Back in the query editor, you should see the code Copilot generated for you

   

4. You can press tab to accept the code or hover over the code with your mouse and click accept.

   

5. Once you accept the code, the color will change indicating the code has been accepted

   

   and you can run it with the run button in the query editor to see the results.

   

6. In an empty query editor sheet, try this example (remember to press return/enter after putting the statement in the query editor):

   -- show me the top 5 customers with the most orders and their address and the number of orders
   

   it should provide you with code similar to the following (Remember to accept the results or press tab to accept the results):

   -- show me the top 5 customers with the most orders and their address and the number of orders
   
   SELECT TOP 5
   C.[CustomerID],
   C.[FirstName],
   C.[LastName],
   A.[AddressLine1],
   A.[City],
   A.[PostalCode],
   COUNT(OD.[SalesOrderID]) AS OrderCount
   FROM [SalesLT].[Customer] AS C
   JOIN [SalesLT].[CustomerAddress] AS CA ON C.[CustomerID] = CA.[CustomerID]
   JOIN [SalesLT].[Address] AS A ON CA.[AddressID] = A.[AddressID]
   JOIN [SalesLT].[SalesOrderHeader] AS OH ON C.[CustomerID] = OH.[CustomerID]
   JOIN [SalesLT].[SalesOrderDetail] AS OD ON OH.[SalesOrderID] = OD.[SalesOrderID]
   GROUP BY C.[CustomerID], C.[FirstName], C.[LastName], A.[AddressLine1], A.[City], A.[PostalCode]
   ORDER BY OrderCount DESC;
   

7. Now, delete the 5 after the word TOP in the first line of code.

   

   Then use the run button to execute the SQL query.

8. You should get an error on the bottom of the page in the messages pane stating "Msg 102, Level 15, State 1, Line 4, Incorrect syntax near 'C'."

   

9. Back on the query editor toolbar. click the Fix query errors button.

   

10. You will see that it added back the 5 and notated the error with how it fixed it.

    

11. Next, click the Explain query button on the query editor toolbar.

    

12. This option annotates your code for you adding comments outlining the purpose/function of each section.

    

3. Call REST services from the Azure SQL Database in Microsoft Fabric with External REST Endpoint Invocation

External REST Endpoint Invocation

Azure SQL Database External REST Endpoint Invocation provides the ability to call REST endpoints from other Azure services such as OpenAI, Azure Event Hub, Azure Functions, Power BI and more. Common use cases for developers to use External REST Endpoint Invocation are:

• Utilize Azure OpenAI services such as chat, embeddings, language, and content safety
• Ability to push business logic out of the database and into Azure Functions
• Pull/push data to/from external sources (including Azure Blob Storage) for ETL or derived data stores
• Participate in event-based architectures with Azure Event Hub or Kafka

External REST Endpoint Invocation can be called in an Azure SQL Database using the sp_invoke_external_rest_endpoint stored procedure.

Getting started with REST in the Azure SQL Database

In this first section, you will test the External REST Endpoint Invocation (EREI) feature of the database to ensure you have connectivity to other Azure services by asking ChatGPT for a joke. This section will also create a database scoped credential. A database scoped credential is a record in the database that contains authentication information for connecting to a resource outside the database. For this lab, we will be creating one that contains the api key for connecting to Azure OpenAI services.

Using T-SQL to check connectivity to Azure OpenAI and creating database scoped credentials

Chat Completion

1. Back in the query sheet, remove the previous code by highlighting it and pressing delete/backspace.

2. Using an empty query sheet in Microsoft Fabric, copy and paste the following code:

   
   -- Create a master key for the database
   if not exists(select * from sys.symmetric_keys where [name] = '##MS_DatabaseMasterKey##')
   begin
       create master key encryption by password = N'V3RYStr0NGP@ssw0rd!';
   end
   go
   
   -- Create the database scoped credential for Azure OpenAI
   if not exists(select * from sys.database_scoped_credentials where [name] = 'https://AI_ENDPOINT_SERVERNAME.openai.azure.com/')
   begin
       create database scoped credential [https://AI_ENDPOINT_SERVERNAME.openai.azure.com/]
       with identity = 'HTTPEndpointHeaders', secret = '{"api-key":"YOUR_OPENAI_KEY"}';
   end
   go
   

3. Then click the run button on the query sheet

   

   The master key will be set, and the database scoped credential will be created.

4. Back in the query sheet, remove the previous code by highlighting it and pressing delete/backspace.

5. Let's test the connectivity to Azure OpenAI and see the ability to call external REST endpoints in action. Copy and paste the following code into a blank query editor in Microsoft Fabric:

   declare @url nvarchar(4000) = N'https://AI_ENDPOINT_SERVERNAME.openai.azure.com/openai/deployments/gpt-4/chat/completions?api-version=2024-06-01';
   declare @payload nvarchar(max) = N'{"messages":[{"role":"system","content":"You are an expert joke teller."},                                   
                                      {"role":"system","content":"tell me a joke about a llama walking into a bar"}]}'
   declare @ret int, @response nvarchar(max);
   
   exec @ret = sp_invoke_external_rest_endpoint
       @url = @url,
       @method = 'POST', 
       @payload = @payload,
       @credential = [https://AI_ENDPOINT_SERVERNAME.openai.azure.com/],    
       @timeout = 230,
       @response = @response output;
       
   select json_value(@response, '$.result.choices[0].message.content') as "Amazing, Awesome, Stupendous Joke";
   

6. Click the run button on the query sheet. The result will be an amazing joke you can tell your friends and family!

   

   Question: tell me a joke about a llama walking into a bar.

   Response: A llama walks into a bar and the bartender says, "Hey, why the long face?" The llama replies, "I'm just here to meet my alpaca friend, he always packs the good jokes!"

   Classic alpaca joke.

7. Next, we can use AI Translation and Content Safety.

AI Content Safety

1. Lets start with Content Safety. Copy and paste the following code into a blank query editor in Microsoft Fabric:

   -- Create the database scoped credential for Azure AI Content Safety
   if not exists(select * from sys.database_scoped_credentials where [name] = 'CONTENT_SAFETY_ENDPOINT_SERVERNAME')
   begin
       create database scoped credential [CONTENT_SAFETY_ENDPOINT_SERVERNAME]
       with identity = 'HTTPEndpointHeaders', secret = '{"Ocp-Apim-Subscription-Key":"YOUR_OPENAI_KEY","Ocp-Apim-Subscription-Region":"YOUR_OPENAI_REGION"}';
   end
   go
   

2. Then click the run button on the query sheet.

3. Using the following code, we can use the Text Analysis feature of Azure AI Content Safety. Copy and paste the following code into a blank query editor in Microsoft Fabric:

   declare @url nvarchar(4000) = N'CONTENT_SAFETY_ENDPOINT_SERVERNAME/contentsafety/text:analyze?api-version=2024-09-01';
   declare @payload nvarchar(max) = N'{"text": "I want to kill all the ant in the house!"}';    
   declare @ret int, @response nvarchar(max);
   
   exec @ret = sp_invoke_external_rest_endpoint
        @url = @url,
        @method = 'POST',
        @payload = @payload,
        @credential = [CONTENT_SAFETY_ENDPOINT_SERVERNAME],
        @timeout = 230,
        @response = @response output;
   
   SELECT JSON_VALUE(D.[value],'$.category') as "Category",
       JSON_VALUE(D.[value],'$.severity') as "Severity"
       FROM OPENJSON(@response,'$.result.categoriesAnalysis') AS D
   

4. Then click the run button on the query sheet.

5. Content Safety also can check to see if a string of text is protected or copyrighted. Copy and paste the following code into a blank query editor in Microsoft Fabric:

   declare @url nvarchar(4000) = N'CONTENT_SAFETY_ENDPOINT_SERVERNAME/contentsafety/text:detectProtectedMaterial?api-version=2024-09-01';
   declare @payload nvarchar(max) = N'{"text":"The people were delighted, coming forth to claim their prize They ran to build their cities and converse among the wise But one day, the streets fell silent, yet they knew not what was wrong The urge to build these fine things seemed not to be so strong The wise men were consulted and the Bridge of Death was crossed In quest of Dionysus to find out what they had lost"}';
   declare @ret int, @response nvarchar(max);
   
   exec @ret = sp_invoke_external_rest_endpoint
        @url = @url,
        @method = 'POST',     
        @payload = @payload,     
        @credential = [CONTENT_SAFETY_ENDPOINT_SERVERNAME],     
        @timeout = 230,     
        @response = @response output;
        
        select json_value(@response, '$.result.protectedMaterialAnalysis.detected') as "Protected Material Analysis";
   

AI Translation

1. Now, lets try the Translation service. Copy and paste the following code into a blank query editor in Microsoft Fabric:

   -- Create the database scoped credential for Azure AI Translation
   if not exists(select * from sys.database_scoped_credentials where [name] = 'https://api.cognitive.microsofttranslator.com/')
   begin
       create database scoped credential [https://api.cognitive.microsofttranslator.com/]
       with identity = 'HTTPEndpointHeaders', secret = '{"Ocp-Apim-Subscription-Key":"YOUR_OPENAI_KEY","Ocp-Apim-Subscription-Region":"YOUR_OPENAI_REGION"}';
   end
   go
   

2. Then click the run button on the query sheet.

3. We can now try the translation services using a product description from the sample database data we just loaded. Copy and paste the following code into a blank query editor in Microsoft Fabric:

   declare @url nvarchar(4000) = N'https://api.cognitive.microsofttranslator.com/translate?api-version=3.0&from=en&to=es';
   declare @message nvarchar(max);    
   SET @message = (SELECT [Description] FROM [SalesLT].[ProductDescription] WHERE ProductDescriptionID = 457);
   declare @payload nvarchar(max) = N'[{"Text": "'+ @message +'"}]';    
   declare @ret int, @response nvarchar(max);
   
   exec @ret = sp_invoke_external_rest_endpoint
        @url = @url,
        @method = 'POST',
        @payload = @payload,
        @credential = [https://api.cognitive.microsofttranslator.com/],
        @timeout = 230,
        @response = @response output;
   
   select json_value(@response, '$.result[0].translations[0].text') as "Translation";
   

4. Then click the run button on the query sheet.

5. AI Translation can also perform transliteration; the conversion of a text from one script to another. In this example, we will be taking Japanese Hiragana and converting it to Latin characters.

   declare @url nvarchar(4000) = N'https://api.cognitive.microsofttranslator.com/transliterate?api-version=3.0&language=ja&fromScript=jpan&toScript=latn';    
   declare @message nvarchar(max);      
   declare @payload nvarchar(max) = N'[{"Text": "こんにちは"}]';    
   declare @ret int, @response nvarchar(max);
   
   exec @ret = sp_invoke_external_rest_endpoint        
        @url = @url,        
        @method = 'POST',         
        @payload = @payload,        
        @credential = [https://api.cognitive.microsofttranslator.com/],           
        @timeout = 230,        
        @response = @response output;    
         
   select json_value(@response, '$.result[0].text') as "Translation";
   

4. Creating embeddings for relational data

Understanding embeddings in Azure OpenAI

An embedding is a special format of data representation that machine learning models and algorithms can easily use. The embedding is an information dense representation of the semantic meaning of a piece of text. Each embedding is a vector of floating-point numbers. Vector embeddings can help with semantic search by capturing the semantic similarity between terms. For example, "cat" and "kitty" have similar meanings, even though they are spelled differently.

Embeddings created and stored in the Azure SQL Database in Microsoft Fabric during this lab will power a vector similarity search in a chat app you will build.

The Azure OpenAI embeddings endpoint

1. Using an empty query sheet in Microsoft Fabric, copy and paste the following code. This code calls an Azure OpenAI embeddings endpoint. The result will be a JSON array of vectors.

   declare @url nvarchar(4000) = N'https://AI_ENDPOINT_SERVERNAME.openai.azure.com/openai/deployments/text-embedding-ada-002/embeddings?api-version=2024-06-01';
   declare @message nvarchar(max) = 'Hello World!';
   declare @payload nvarchar(max) = N'{"input": "' + @message + '"}';
   
   declare @ret int, @response nvarchar(max);
   
   exec @ret = sp_invoke_external_rest_endpoint 
       @url = @url,
       @method = 'POST',
       @payload = @payload,
       @credential = [https://AI_ENDPOINT_SERVERNAME.openai.azure.com/],
       @timeout = 230,
       @response = @response output;
   
   select json_query(@response, '$.result.data[0].embedding') as "JSON Vector Array";
   

2. Again, click the run button on the query sheet. The result will be a JSON vector array.

   

   Using the built in JSON function json_query, we are able to extract JSON array from REST response payloads. In the above T-SQL, json_query(@response, '$.result.data[0].embedding') as "JSON Vector Array" will extract the vector array from the result payload returned to us from the Azure OpenAI REST endpoint.

   For reference, the JSON response message from the Azure OpenAI embeddings endpoint will look similar to the following and, you can see how we extract the array found_$.result.data[0].embedding.

   Tip

   This code is for reference only

   {
       "response": {
           "status": {
               "http": {
                   "code": 200,
                   "description": ""
               }
           },
           "headers": {
               "Date": "Thu, 24 Oct 2024 19:32:59 GMT",
               "Content-Length": "33542",
               "Content-Type": "application/json",
               "access-control-allow-origin": "*",
               "apim-request-id": "ac67032f-41c1-4ec3-acc6-3f697c262764",
               "strict-transport-security": "max-age=31536000; includeSubDomains; preload",
               "x-content-type-options": "nosniff",
               "x-ms-region": "West US",
               "x-request-id": "84baf32d-f1f7-4183-9403-a95365d01a3e",
               "x-ms-client-request-id": "ac67032f-41c1-4ec3-acc6-3f697c262764",
               "x-ratelimit-remaining-requests": "349",
               "azureml-model-session": "d007-20240925154241",
               "x-ratelimit-remaining-tokens": "349994"
           }
       },
       "result": {
           "object": "list",
           "data": [
               {
                   "object": "embedding",
                   "index": 0,
                   "embedding": [
                       0.0023929428,
                       0.00034713413,
                       -0.0023142276,
                       -0.025654867,
                       -0.011492423,
                       0.0010358924,
                       -0.014836246,
                       0.0035484824,
                       0.000045630233,
                       -0.027581815,
                       0.023816079,
                       0.005012586,
                       -0.027732948,
                       -0.010088143,
                       ...
                       -0.014571763
                   ]
               }
           ],
           "model": "text-embedding-ada-002",
           "usage": {
               "prompt_tokens": 3,
               "total_tokens": 3
           }
       }
   }
   

   This JSON vector array can now be used with new vector datatype and functions in the Azure SQL database such as VECTOR_DISTANCE.

Preparing the database and creating embeddings

This next section of the lab will have you alter the Adventure Works product table to add a new vector datatype column. You will then use a stored procedure to create embeddings for the products and store the vector arrays in that column.

1. In a new query sheet or an existing bank one in VS Code, copy and paste the following T-SQL:

   alter table [SalesLT].[Product]
   add  embeddings VECTOR(1536), chunk nvarchar(2000);
   

   this code adds a vector datatype column to the Product table. It also adds a column named chunk where we will store the text we send over to the embeddings REST endpoint.

2. Then click the run button on the query sheet

   

3. Next, we are going to use the External REST Endpoint Invocation procedure (sp_invoke_external_rest_endpoint) to create a stored procedure that will create embeddings for text we supply as an input. Copy and paste the following code into a blank query editor in Microsoft Fabric:

   create or alter procedure dbo.create_embeddings
   (
       @input_text nvarchar(max),
       @embedding vector(1536) output
   )
   AS
   BEGIN
   declare @url varchar(max) = 'https://AI_ENDPOINT_SERVERNAME.openai.azure.com/openai/deployments/text-embedding-ada-002/embeddings?api-version=2024-06-01';
   declare @payload nvarchar(max) = json_object('input': @input_text);
   declare @response nvarchar(max);
   declare @retval int;
   
   -- Call to Azure OpenAI to get the embedding of the search text
   begin try
       exec @retval = sp_invoke_external_rest_endpoint
           @url = @url,
           @method = 'POST',
           @credential = [https://AI_ENDPOINT_SERVERNAME.openai.azure.com/],
           @payload = @payload,
           @response = @response output;
   end try
   begin catch
       select 
           'SQL' as error_source, 
           error_number() as error_code,
           error_message() as error_message
       return;
   end catch
   if (@retval != 0) begin
       select 
           'OPENAI' as error_source, 
           json_value(@response, '$.result.error.code') as error_code,
           json_value(@response, '$.result.error.message') as error_message,
           @response as error_response
       return;
   end
   -- Parse the embedding returned by Azure OpenAI
   declare @json_embedding nvarchar(max) = json_query(@response, '$.result.data[0].embedding');
   
   -- Convert the JSON array to a vector and set return parameter
   set @embedding = CAST(@json_embedding AS VECTOR(1536));
   END;
   

4. Click the run button on the query sheet to create the procedure in the database.

5. We have our embeddings procedure, now we can use it with data from the various products table. We are taking descriptive elements from each product and concatenating them into a single string to send to the embeddings endpoint. We construct this text string with the following SQL:

   Tip

   This code is for reference only

   SELECT p.Name + ' '+ ISNULL(p.Color,'No Color') + ' '+  c.Name + ' '+  m.Name + ' '+  ISNULL(d.Description,'')
   FROM 
       [SalesLT].[ProductCategory] c,
       [SalesLT].[ProductModel] m,
       [SalesLT].[Product] p
       LEFT OUTER JOIN
       [SalesLT].[vProductAndDescription] d
           on p.ProductID = d.ProductID
           and d.Culture = 'en'
   where p.ProductCategoryID = c.ProductCategoryID
   and p.ProductModelID = m.ProductModelID
   and p.ProductID = @ProductID
   

   Looking_the SQL, the text we are embedding contains the product name, product color (if available), the category name the product belongs to, the model name of the product, and the description of the product.

   [!Hint] Seeing you are now familiar with how to copy, paste, and run SQL in the Query Editor in Visual Studio Code, when the instructions say, "Run this in a blank query editor", it is referring to the process of copying, pasting, and running the code.

   If you have questions, please raise your hand or ask a proctor or speaker in the room

6. Run the following T-SQL in a blank query editor in Microsoft Fabric to create embeddings for all products in the Products table:

   Important

   This code will take 30 to 60 seconds to run

   SET NOCOUNT ON
   DROP TABLE IF EXISTS #MYTEMP 
   DECLARE @ProductID int
   declare @text nvarchar(max);
   declare @vector vector(1536);
   SELECT * INTO #MYTEMP FROM [SalesLT].Product
   SELECT @ProductID = ProductID FROM #MYTEMP
   SELECT TOP(1) @ProductID = ProductID FROM #MYTEMP
   WHILE @@ROWCOUNT <> 0
   BEGIN
       set @text = (SELECT p.Name + ' '+ ISNULL(p.Color,'No Color') + ' '+  c.Name + ' '+  m.Name + ' '+  ISNULL(d.Description,'')
                       FROM 
                       [SalesLT].[ProductCategory] c,
                       [SalesLT].[ProductModel] m,
                       [SalesLT].[Product] p
                       LEFT OUTER JOIN
                       [SalesLT].[vProductAndDescription] d
                       on p.ProductID = d.ProductID
                       and d.Culture = 'en'
                       where p.ProductCategoryID = c.ProductCategoryID
                       and p.ProductModelID = m.ProductModelID
                       and p.ProductID = @ProductID);
       exec dbo.create_embeddings @text, @vector output;
       update [SalesLT].[Product] set [embeddings] = @vector, [chunk] = @text where ProductID = @ProductID;
       DELETE FROM #MYTEMP WHERE ProductID = @ProductID
       SELECT TOP(1) @ProductID = ProductID FROM #MYTEMP
   END
   

7. To ensure all the embeddings were created, run the following code in a blank query editor in Microsoft Fabric:

   select count(*) from SalesLT.Product where embeddings is null;
   

   You should get 0 for the result.

8. Run the next query in a blank query editor in Microsoft Fabric to see the results of the above update to the Products table:

   select top 10 chunk, embeddings from SalesLT.Product
   

   You can see that the chunk column is the combination of multiple data points about a product and the embeddings column contains the vector arrays.

   

Vector similarity searching

Vector similarity searching is a technique used to find and retrieve data points that are similar to a given query, based on their vector representations. The similarity between two vectors is usually measured using a distance metric, such as cosine similarity or Euclidean distance. These metrics quantify the similarity between two vectors by calculating the angle between them or the distance between their coordinates in the vector space.

Vector similarity searching has numerous applications, such as recommendation systems, search engines, image and video retrieval, and natural language processing tasks. It allows for efficient and accurate retrieval of similar items, enabling users to find relevant information or discover related items quickly and effectively.

The VECTOR_DISTANCE function is a new feature of the Azure SQL Database that can calculate the distance between two vectors enabling similarity searching right in the database.

The syntax is as follows:

VECTOR_DISTANCE ( distance_metric, vector1, vector2 )

You will be using this function in some upcoming samples as well as in the RAG chat application; both utilizing the vectors you just created for the Products table.

1. The first query will pose the question "I am looking for a red bike and I dont want to spend a lot". The key words that should help with our similarity search are red, bike, and dont want to spend a lot. Run the following SQL in a blank query editor in Microsoft Fabric:

   Query 1

   declare @search_text nvarchar(max) = 'I am looking for a red bike and I dont want to spend a lot'
   declare @search_vector vector(1536)
   exec dbo.create_embeddings @search_text, @search_vector output;
   SELECT TOP(4) 
   p.ProductID, p.Name , p.chunk,
   vector_distance('cosine', @search_vector, p.embeddings) AS distance
   FROM [SalesLT].[Product] p
   ORDER BY distance
   

   And you can see from the results, the search found exactly that, an affordable red bike. The distance column shows us how similar it found the results to be using VECTOR_DISTANCE, with a lower score being a better match.

   Query 1 results

   ProductID	Name	chunk	distance
   763	Road-650 Red, 48	Road-650 Red, 48 Red Road Bikes Road-650 Value-priced bike with many features of our top-of-the-line models. Has the same light, stiff frame, and the quick acceleration we're famous for.	0.16352240013483477
   760	Road-650 Red, 60	Road-650 Red, 60 Red Road Bikes Road-650 Value-priced bike with many features of our top-of-the-line models. Has the same light, stiff frame, and the quick acceleration we're famous for.	0.16361482158949225
   759	Road-650 Red, 58	Road-650 Red, 58 Red Road Bikes Road-650 Value-priced bike with many features of our top-of-the-line models. Has the same light, stiff frame, and the quick acceleration we're famous for.	0.16432339626539993
   762	Road-650 Red, 44	Road-650 Red, 44 Red Road Bikes Road-650 Value-priced bike with many features of our top-of-the-line models. Has the same light, stiff frame, and the quick acceleration we're famous for.	0.1652894865541471

   

2. The next search will be looking for a safe lightweight helmet. Run the following SQL in a blank query editor in Microsoft Fabric:

   Query 2

   declare @search_text nvarchar(max) = 'I am looking for a safe helmet that does not weigh much'
   declare @search_vector vector(1536)
   exec dbo.create_embeddings @search_text, @search_vector output;
   SELECT TOP(4) 
   p.ProductID, p.Name , p.chunk,
   vector_distance('cosine', @search_vector, p.embeddings) AS distance
   FROM [SalesLT].[Product] p
   ORDER BY distance
   

   With the results returning lightweight helmets. There is one result that is not a helmet but a vest but as you can see, the distance score is higher for this result than the 3 helmet scores.

   Query 2 results

   Name	chunk	distance
   Sport-100 Helmet, Black	Sport-100 Helmet, Black Black Helmets Sport-100 Universal fit, well-vented, lightweight , snap-on visor.	0.1641856735683479
   Sport-100 Helmet, Red	Sport-100 Helmet, Red Red Helmets Sport-100 Universal fit, well-vented, lightweight , snap-on visor.	0.16508593401632166
   Sport-100 Helmet, Blue	Sport-100 Helmet, Blue Blue Helmets Sport-100 Universal fit, well-vented, lightweight , snap-on visor.	0.16592580751312624
   Classic Vest, S	Classic Vest, S Blue Vests Classic Vest Light-weight, wind-resistant, packs to fit into a pocket.	0.19888204151269384

   

3. In the previous 2 examples, we were clear on what we were looking for; cheap red bike, light helmet. In this next example, we are going to have the search flex its AI muscles a bit by saying we want a bike seat that needs to be good on trails. This will require the search to look for adjacent values that have something in common with trails. Run the following SQL in a blank query editor in Microsoft Fabric to see the results.

   Query 3

   declare @search_text nvarchar(max) = 'Do you sell any padded seats that are good on trails?'
   declare @search_vector vector(1536)
   exec dbo.create_embeddings @search_text, @search_vector output;
   SELECT TOP(4) 
   p.ProductID, p.Name , p.chunk,
   vector_distance('cosine', @search_vector, p.embeddings) AS distance
   FROM [SalesLT].[Product] p
   ORDER BY distance
   

   These results are very interesting for it found products based on word meanings such as absorb shocks and bumps and foam-padded. It was able to make connections to riding conditions on trails and find products that would fit that need.

   Query 3 results

   Name	chunk	distance
   ML Mountain Seat/Saddle	ML Mountain Seat/Saddle No Color Saddles ML Mountain Seat/Saddle 2 Designed to absorb shock.	0.17265341238606102
   LL Road Seat/Saddle	LL Road Seat/Saddle No Color Saddles LL Road Seat/Saddle 1 Lightweight foam-padded saddle.	0.17667274723850412
   ML Road Seat/Saddle	ML Road Seat/Saddle No Color Saddles ML Road Seat/Saddle 2 Rubber bumpers absorb bumps.	0.18802953111711573
   HL Mountain Seat/Saddle	HL Mountain Seat/Saddle No Color Saddles HL Mountain Seat/Saddle 2 Anatomic design for a full-day of riding in comfort. Durable leather.	0.18931317298732764

   

5. Creating a GraphQL API for RAG applications

In this section of the lab, you will be deploying a GraphQL API that uses embeddings, vector similarity search, and relational data to return a set of Adventure Works products that could be used by a chat application leveraging a Large Language Model (LLM). In essence, putting all the pieces together in the previous sections.

In the section of the lab, you will create a stored procedure that will be used by the GraphQL API for taking in questions and returning products.

Creating the stored procedure used by the GraphQL API

1. Run the following SQL in a blank query editor in Microsoft Fabric:

   create or alter procedure [dbo].[find_products]
   @text nvarchar(max),
   @top int = 10,
   @min_similarity decimal(19,16) = 0.80
   as
   if (@text is null) return;
   declare @retval int, @qv vector(1536);
   exec @retval = dbo.create_embeddings @text, @qv output;
   if (@retval != 0) return;
   with vector_results as (
   SELECT 
           p.Name as product_name,
           ISNULL(p.Color,'No Color') as product_color,
           c.Name as category_name,
           m.Name as model_name,
           d.Description as product_description,
           p.ListPrice as list_price,
           p.weight as product_weight,
           vector_distance('cosine', @qv, p.embeddings) AS distance
   FROM
       [SalesLT].[Product] p,
       [SalesLT].[ProductCategory] c,
       [SalesLT].[ProductModel] m,
       [SalesLT].[vProductAndDescription] d
   where p.ProductID = d.ProductID
   and p.ProductCategoryID = c.ProductCategoryID
   and p.ProductModelID = m.ProductModelID
   and p.ProductID = d.ProductID
   and d.Culture = 'en')
   select TOP(@top) product_name, product_color, category_name, model_name, product_description, list_price, product_weight, distance
   from vector_results
   where (1-distance) > @min_similarity
   order by    
       distance asc;
   GO
   

2. Next, we need to encapsulate the stored procedure into a wrapper so that the result set can be utilized by our GraphQL endpoint. Using the WITH RESULT SET syntax allows you to change the names and data types of the returning result set. This is needed in this example because the usage of sp_invoke_external_rest_endpoint and the return output from extended stored procedures

   Run the following SQL in a blank query editor in Microsoft Fabric:

   create or alter procedure [find_products_api]
       @text nvarchar(max)
       as 
       exec find_products @text
       with RESULT SETS
       (    
           (    
               product_name NVARCHAR(200),    
               product_color NVARCHAR(50),    
               category_name NVARCHAR(50),    
               model_name NVARCHAR(50),    
               product_description NVARCHAR(max),    
               list_price INT,    
               product_weight INT,    
               distance float    
           )
       )
   GO
   

3. You can test this newly created procedure to see how it will interact with the GraphQL API by running the following SQL in a blank query editor in Microsoft Fabric:

   exec find_products_api 'I am looking for a red bike'
   

   

4. To create the GraphQL API, click on the New API for GraphQL button on the toolbar.

   

5. In the New API for GraphQL dialog box, use the Name Field and name the API find_products_api.

   +++find_products_api+++

   

6. After naming the API, click the green Create button.

   

7. The next dialog box presented is the Choose data dialog box where you will pick a table or stored procedure for the GraphQL API,

   

   use the Search box in the Explorer section on the left

   

   and enter in find_products_api.

   +++find_products_api+++

   

8. Choose the find_products_api stored procedure in the results. You can ensure it is the find_products_api stored procedure by hovering over it with your mouse/pointer. It will also indicate the selected database item in the preview section. It should state "Preview data: dbo.find_products_api".

   

9. Once you have selected the find_products_api stored procedure, click the green Load button on the bottom right of the modal dialog box.

   

10. You will now be on the GraphQL Query editor page. Here, we can run GraphQL queries similar to how we can run T-SQL queries on the query editor.

    

11. Replace the sample code on the left side of the GraphQL query editor with the following query:

    query {
        executefind_products_api(text: "I am looking for a red bike") {
                product_name
                product_color
                category_name
                model_name
                product_description
                list_price
                product_weight
                distance 
        }
    }
    

    

12. Now, click the Run button in the upper left of the GraphQL query editor.

    

    and you can scroll through the results from the GraphQL query in the lower section of the editor.

    

13. Back on the toolbar, find and click the Generate code button.

    

14. This feature will generate the code for calling this API via python or node.js to help give you a jumpstart in the application creation process.

    

15. When done looking_the python and node.js code, click the X in the upper right corner to close the Generate code dialog box.

    

Adding chat completion to the GraphQL API

The API you just created could now be handed off to an application developer to be included in a RAG application that uses vector similarity search and data from the database. The application may also_some point hand the results off to a LLM to craft a more human response.

Let's alter the stored procedure to create a new flow that not only uses vector similarity search to get products based on a question asked by a user, but to take the results, pass them to Azure OpenAI Chat Completion, and craft an answer they would typically see with an AI chat application.

1. Before we can start creating new stored procedures, we need to go back to the SQL Database home page. Do this by using the navigator on the left side of the page and clicking on the SQL Database icon.

   

2. The first step in augmenting our RAG application API is to create a stored procedure that takes the retrieved products and passes them in a prompt to an Azure OpenAI Chat Completion REST endpoint (this is the same endpoint that was used to ask the llama joke). The prompt consists of telling the endpoint who they are, what products they have to work with, and the exact question that was asked by the user.

   Copy and run the following SQL in a blank query editor in Microsoft Fabric:

   CREATE OR ALTER PROCEDURE [dbo].[prompt_answer]
   @user_question nvarchar(max),
   @products nvarchar(max),
   @answer nvarchar(max) output
   
   AS
   
   declare @url nvarchar(4000) = N'https://AI_ENDPOINT_SERVERNAME.openai.azure.com/openai/deployments/gpt-4/chat/completions?api-version=2024-06-01';
   declare @payload nvarchar(max) = N'{
       "messages": [
           {
               "role": "system",
               "content": "You are a sales assistant who helps customers find the right products for their question and activities."
           },
           {
               "role": "user",
               "content": "The products available are the following: ' + @products + '"
           },
           {
               "role": "user",
               "content": " ' + @user_question + '"
           }
       ]
   }';
   
   declare @ret int, @response nvarchar(max);
   
   exec @ret = sp_invoke_external_rest_endpoint
       @url = @url,
       @method = 'POST', 
       @payload = @payload,
       @credential = [https://AI_ENDPOINT_SERVERNAME.openai.azure.com/],    
       @timeout = 230,
       @response = @response output;
   
   select json_value(@response, '$.result.choices[0].message.content');
   
   GO
   

3. Now that you have created the chat completion stored procedure, we need to create a new find_products stored procedure that adds a call to this chat completion endpoint. This new stored procedure contains 2 additional steps that were not found in the original:

   1. A section to help package up the results into something we can use in a prompt.

   STRING_AGG (CONVERT(NVARCHAR(max),CONCAT( 
                                   product_name, ' ' ,
                                   product_color, ' ',
                                   category_name, ' ', 
                                   model_name, ' ', 
                                   product_description )), CHAR(13)))   
   

   2. A section that calls the new chat completion stored procedure and provides it with the products retrieved from the database to help ground the answer.

   exec [dbo].[prompt_answer] @text, @products_json, @answer output;
   

4. Copy and run the following SQL in a blank query editor in Microsoft Fabric:

   create or alter procedure [dbo].[find_products_chat]
   @text nvarchar(max),
   @top int = 3,
   @min_similarity decimal(19,16) = 0.70
   as
   if (@text is null) return;
   declare @retval int, @qv vector(1536), @products_json nvarchar(max), @answer nvarchar(max);
   exec @retval = dbo.create_embeddings @text, @qv output;
   if (@retval != 0) return;
   with vector_results as (
   SELECT 
           p.Name as product_name,
           ISNULL(p.Color,'No Color') as product_color,
           c.Name as category_name,
           m.Name as model_name,
           d.Description as product_description,
           p.ListPrice as list_price,
           p.weight as product_weight,
           vector_distance('cosine', @qv, p.embeddings) AS distance
   FROM
       [SalesLT].[Product] p,
       [SalesLT].[ProductCategory] c,
       [SalesLT].[ProductModel] m,
       [SalesLT].[vProductAndDescription] d
   where p.ProductID = d.ProductID
   and p.ProductCategoryID = c.ProductCategoryID
   and p.ProductModelID = m.ProductModelID
   and p.ProductID = d.ProductID
   and d.Culture = 'en')
   select
   top(@top)
   @products_json = (STRING_AGG (CONVERT(NVARCHAR(max),CONCAT( 
                                   product_name, ' ' ,
                                   product_color, ' ',
                                   category_name, ' ', 
                                   model_name, ' ', 
                                   product_description, ' ',
                                   list_price, ' ',
                                   product_weight )), CHAR(13)))
   from vector_results
   where (1-distance) > @min_similarity
   group by distance
   order by    
       distance asc;
   
   set @products_json = (select REPLACE(REPLACE(@products_json, CHAR(13), ' , '), CHAR(10), ' , '));
   
   exec [dbo].[prompt_answer] @text, @products_json, @answer output;
   
   GO
   

5. The last step before we can create a new GraphQL endpoint is to wrap the new find products stored procedure. Copy and run the following SQL in a blank query editor in Microsoft Fabric:

   create or alter procedure [find_products_chat_api]
       @text nvarchar(max)
       as 
       exec find_products_chat @text
       with RESULT SETS
       (    
           (    
               answer NVARCHAR(max)
           )
       )
   GO
   

6. You can test this new procedure to see how Azure OpenAI will answer a question with product data by running the following SQL in a blank query editor in Microsoft Fabric:

   exec find_products_chat_api 'I am looking for a red bike'
   

   with the answer being similar to (your answer will be different): "It sounds like the Road-650 Red, 62 Red Road Bikes Road-650 would be an excellent choice for you. This value-priced bike comes in red and features a light, stiff frame that is known for its quick acceleration. It also incorporates many features from top-of-the-line models. Would you like more details about this bike or help with anything else?"

   

7. To create the GraphQL API, click on the New API for GraphQL button on the toolbar just as you did previously.

   

8. In the New API for GraphQL dialog box, use the Name Field and name the API find_products_chat_api.

   +++find_products_chat_api+++

   

9. After naming the API, click the green Create button.

   

10. The next dialog box presented is again the Choose data dialog box where you will pick a table or stored procedure for the GraphQL API,

    

    use the Search box in the Explorer section on the left

    

    and enter in find_products_chat_api.

    +++find_products_chat_api+++

    

11. Choose the stored procedure in the results. You can ensure it is the find_products_chat_api stored procedure by hovering over it with your mouse/pointer. It will also indicate the selected database item in the preview section. It should state "Preview data: dbo.find_products_chat_api".

    

12. Once you have selected the find_products_chat_api stored procedure, click the green Load button on the bottom right of the modal dialog box.

    

13. You will now be back on the GraphQL Query editor page.

    

14. Replace the sample code on the left side of the GraphQL query editor with the following query:

    query {
        executefind_products_chat_api(text: "I am looking for padded seats that are good on trails") {
                answer
        }
    }
    

    

15. Now, click the Run button in the upper left of the GraphQL query editor.

    

16. And you can review the response in the Results section of the editor

    

17. Try it again with the following code and see what answer the chat completion endpoint provides!

    query {
        executefind_products_chat_api(text: "Do you have any racing shorts?") {
                answer
        }
    }
    

6. Creating a Power BI Report from a SQL Database in Microsoft Fabric with Copilot

It's easy to quickly create reports in Power BI with SQL database in Fabric using Copilot.

Build a new report with Copilot

1. Using the navigator on the left side of the Microsoft Fabric page, click the tile for your database. It will be named sqlDatabase followed by a set of numbers.

   

2. Now on the database details page, find the SQL database dropdown in the upper right of the page.

   

3. Click the SQL database dropdown and select SQL analytics endpoint.

   

   Important

   You can also get to the SQL analytics endpoint by clicking on your workspace tile, and selecting the SQL analytics endpoint on the bottom of the page.

   

4. On the SQL database analytics endpoint details page, click the Reporting tab from the ribbon.

   

5. Now, click the New Report button.

   

6. When the New report with all available data modal window opens, click the green Continue button to create a new report with all available data.

   [!Alert] If you get an error after clicking the green Continue button saying:

   "Unable to add the selected objects because_least one of them has been removed from the source. Please refresh and try again."

   Close the dialog box and reopen it. Then, click the green Continue button again.

   

7. The Upgrade to a paid Power BI license modal window is next. Simply click the white Try free button.

   

8. And confirm the free license on the following model by clicking the green Got it button.

   

9. Once the new report opens in Power BI, click the Try it button in the green callout box for Copilot.

   

10. On the right side of the page, the Copilot blade opens.

    

    Click the green Get started button.

    

11. Copilot presents some questions to get you started. Click Suggest content for a new report page.

    

12. Suggested outlines for your report are returned by Copilot based on the data it has access to in the analytics endpoint.

    Important

    The suggested reports returned by Copilot may differ from the following images.

    

13. You can review the selections by expanding their cards to see a quick description about the report.

    

14. Click the Create button for the Sales Order Details report or any other report you would like to create. Go wild!

    

15. Copilot will then begin creating your report.

    

16. You now have a Power BI report of your SQL database data!

    

17. Minimize the blades

    

    to expand the size of the report.