What is Azure OpenAI service?
Azure OpenAI Service is a comprehensive collection of generative models made possible through a collaboration between OpenAI and Microsoft. These models are trained on vast amounts of text data, allowing them to understand and generate human language. With the ability to perform tasks like text generation, translation, and code-to-natural language translation, generative models find applications across various fields and industries.
How can the Azure OpenAI service be used?
Developers can leverage the power of OpenAI's models through the Azure OpenAI Service, which provides REST API access to models such as GPT-3, codex, and more. In this insight, we will be doing just that by automatically documenting our data model in Power BI using GPT-3. More specifically, we will take a look at two use cases, namely how we can generate descriptive explanations for DAX measures and how we can translate power query steps from M code to natural language.
What value does this bring?
This capability simplifies the documentation and comprehension of data models in Power BI, facilitating effective communication between technical and non-technical users. Furthermore, it enhances the quality and usability of Power BI solutions, enabling users to confidently extract insights from their data.
Prerequisites
- Azure OpenAI resource with a model deployed
- Power BI data model with DAX measures
Use case 1: generating measure descriptions
To accomplish this task, we rely on Tabular Editor to write a C# script that iterates through all the measures in our data model and makes a call to the OpenAI API to generate descriptions for each measure. The code begins by establishing the necessary connections and retrieving an API key. Next, we send a prompt to the API, which includes the statement 'Please explain the following calculation in simple business terms,' along with the DAX measure. Also, we specify parameters such as maxTokens and temperature, amongst others, that allow us to tailor the output to our needs. To illustrate, the maxTokens parameter allows us to control the length of the generated response, while temperature influences the randomness or creativity of the output. Lastly, the response received contains descriptions that are then assigned to their respective measures. This is summarized in the diagram below, followed by the C# script.
#r "System.Net.Http"
using System.Net.Http;
using System.Text;
using Newtonsoft.Json.Linq;
string apiKey = Environment.GetEnvironmentVariable("apiKey");
const string uri = "https://openai-dsa.openai.azure.com/openai/deployments/text-davinci-model/completions?api-version=2022-12-01";
const string promptPrefix = "Please explain the following calculation in simple business terms:";
const int maxTokens = 1600;
using (var client = new HttpClient()) {
client.DefaultRequestHeaders.Clear();
client.DefaultRequestHeaders.Add("api-key", apiKey);
foreach (var t in Model.Tables)
{
foreach ( var m in t.Measures)
{
var body =
"{ \"prompt\": " + JsonConvert.SerializeObject(promptPrefix + m.Expression) +
",\"temperature\": 0.5 " +
",\"max_tokens\": " + maxTokens +
",\"stop\": \".\" }";
var res = client.PostAsync(uri, new StringContent(body, Encoding.UTF8,"application/json"));
res.Result.EnsureSuccessStatusCode();
var result = res.Result.Content.ReadAsStringAsync().Result;
var obj = JObject.Parse(result);
var desc = obj["choices"][0]["text"].ToString().Trim();
m.Description = desc;
}
}
}
And that’s all it takes! You can see the results of running the script below.
Given that the result of some descriptions will be better than others, it is recommended to rerun the script until you find descriptions that are appropriate for all your measures. Changing the parameter values is also encouraged during the experimentation process.
Use case 2: documenting power query transformations
The method needed to achieve our second use case is similar to the use case presented above. We write a C# script in Tabular Editor that allows us to make a call to the OpenAI API to automatically generate descriptions for Power Query code snippets. Once more, we establish the necessary connections and retrieve an API key. The script then iterates through the partitions in the model's tables, specifically targeting Power Query code partitions. For each partition, it constructs a prompt using the following statement ‘Can you please explain this power query code in simple business terms,’ along with the code expression and sends it to the OpenAI API. The response, containing the generated description, is extracted and assigned as the name of a new calculation item in the 'Documentation' table of a calculation group, resulting in a calculation item per table. Note that the calculation group called ‘Documentation’ needs to be created before running the script as follows:
The steps above are summarized in the diagram below, followed by the C# script.
#r "System.Net.Http"
using System.Net.Http;
using System.Text;
using Newtonsoft.Json.Linq;
string apiKey = Environment.GetEnvironmentVariable("apiKey");
const string uri = "https://openai-dsa.openai.azure.com/openai/deployments/text-davinci-model/completions?api-version=2022-12-01";
const string promptPrefix = "Can you please explain this power query code in simple business terms:";
using (var client = new HttpClient()) {
client.DefaultRequestHeaders.Clear();
client.DefaultRequestHeaders.Add("api-key", apiKey);
foreach (var t in Model.Tables)
{
foreach ( var p in t.Partitions)
{
string _type = Convert.ToString(p.SourceType);
string _exp = Convert.ToString(p.Expression);
if ( _type == "M" )
{var body =
"{ \"prompt\": " + JsonConvert.SerializeObject( promptPrefix + p.Expression ) +
",\"model\": \"text-davinci-003\" " +
",\"temperature\": 1 " +
",\"max_tokens\": 1500 " +
",\"stop\": \".\" }";
var res = client.PostAsync(uri, new StringContent(body, Encoding.UTF8,"application/json"));
res.Result.EnsureSuccessStatusCode();
var result = res.Result.Content.ReadAsStringAsync().Result;
var obj = JObject.Parse(result);
var desc = obj["choices"][0]["text"].ToString().Trim();
var calculationItem1 = (Model.Tables["Documentation"] as CalculationGroupTable).AddCalculationItem();
//removes any quotes in the chatGPT description
var s = desc.Replace("\"", "");
//calculationItem1 = "\"" + s + "\"";
calculationItem1.Name = t.Name;
}
}
}
}
After running the script and saving the changes to our model, the descriptions can be viewed by adding a table visual to one of the report pages. Include the calculation group name and an empty measure in the table. The value of this measure will be automatically replaced with the expression of the corresponding calculation item, allowing you to see the generated descriptions in the table.
Summary
The use cases presented in this article demonstrate how Azure OpenAI can be leveraged to enhance data model comprehension and communication between technical and non-technical users in Power BI, ultimately improving the usability and quality of Power BI solutions. Nonetheless, automated documentation in Power BI is just one example among many where generative models can be utilized to provide business value. Interested in exploring further use cases together? We’re now offering an Azure OpenAI Roadmap initiative, aimed to work together with your business and data teams to identify the most impactful use cases for implementing Azure OpenAI solutions within your organization. Find out more here or contact us directly!
