Working with the Semantic Model
1. Working with the Semantic Model
Welcome back. In this video, we'll learn how to use the Cortex Analyst API. We'll start by calling the REST API. So if your notebook is not already open, open it up again and re-run it to this cell, using semantic models to share context with the LLMs. Ready? Let's go. The reason that we use semantic models is to improve the SQL generated by our LLMs. These models give us a structured way to capture the information that we need from our data, which relates to our business. When we pass this information to the LLM with the request, the SQL generation improves. Now take a look. Here, we're choosing our semantic model and naming it revenue. From there, we are updating semantic model name to add it to the semantic model. In the next part, we're defining our semantic model base table and the primary keys for these tables. We define our dimensions, time dimensions, and our measures. As we go through this, we see that for our base table, daily revenue, we also provide a description of what it does. We also add the database schema and table, as well as their primary keys, date, product ID, and region ID. Under that, we identify the name, expression, and data type for both product ID and region ID in the dimensions table, and we define the time dimension table with a date expression. Note that we add a description here, as well as setting unique to equal true. Next, we define the measures of daily revenue, daily cost of goods sold, daily forecasted revenue, daily profit, and daily forecast absolute error. Note the synonyms that we set for this and the description. Then we update the semantic model with what we just built. Now that we've expended some effort in writing down our semantic model with the data we want to use, let's try again to naively pass it to a SOTA or state of the art LLM. That worked out okay, but the region ID is not very helpful for a human user. It would be better if we are able to return the natural language name for the region instead. We want to add context for the LLM to reference when it generates its response. We could adjust our prompt to give the LLM the required context so that it can substitute the region IDs for the names, but an analyst wouldn't do that. They'd create a view with a join that returns both the region ID and the related region name. But appending more and more information into the prompt could result in that prompt eventually exceeding the context window. Instead of that, we'll expand the semantic model to include the information that we are missing. For this, we'll join the revenue table to the region table to get the region name instead of just the region ID. Let's do that now. I want to set up the relationship between the region name and the region identifier to make this more human readable. To do this, I'll define semantic model region to associate sales region to region ID through the region identifier. Under dimensions, I define all of this and pass some sample values for sales region to the LLM for it to use. Then I'll update the model again. I have both sides of the relationship defined but now I have to set up the relationship that sits on top of that. I define semantic model region relationships, name it, and set the join that I want. In this case, I want a left outer many-to-one join relationship with the daily revenue as the left table to join on the sales region. Once more, I update and append the model. Let's run it again. That's interesting. Once we increase the task complexity, the state-of-the-art LLM fails to generate valid SQL. Maybe the LLM isn't expecting this format. Let's take a look at what we did here. We did not call Cortex Analyst this time. We'll run this using Cortex Analyst in the next video to see how it handles this. Nice work. You learned the basic structure, the semantic YAML file, and created your own. We passed it to complete and had moderately good results. Now that we've created the basic structure, we can append it in the future. In the next couple of videos, I will show you how important these models are in achieving high accuracy SQL generation. I'll see you in the next video. Thank you for watching.2. Let's practice!
Create Your Free Account
or
By continuing, you accept our Terms of Use, our Privacy Policy and that your data is stored in the USA.