AI Assistant for Tabular Editor
For now, this is just a quick proof of concept, but it works (at least it worked on my side). It consists of two C# scripts: one script creates an assistant with an attached data model description (in JSON format), and the other script adds comments to a copy of the selected measure using the assistant.
I used Tabular Editor 3, but the script should work in Tabular Editor 2 as well. I believe so, although I haven’t used TE2 in a while.
Github: https://github.com/avatorl/TabularEditor/tree/main/ai-assistant
What is an assistant? When you create a custom GPT in ChatGPT, you can upload files and provide instructions that are stored permanently and used as a knowledge base for all future requests to that specific GPT. These files and instructions help tailor the GPT’s behavior and responses based on your needs.
An assistant API allows you to achieve a similar result via API—you can provide instructions, attach files, and even enable the assistant to call other APIs if necessary. The data provided to the assistant in this way remains accessible across interactions with the assistant.
For example, when you upload a Model.bim file (which contains a list of tables, columns, calculated groups and items, measures and measure expressions, and other data model metadata), the assistant will be able to analyze the file, and then add comments to the measures, and provide other forms of assistance, all while being aware of the entire data model. The assistant will retain this knowledge, enabling it to assist you with future queries related to this specific data model.
ai-create-assistant.csx
This script creates an assistant.
To start using the script, insert your OpenAI API KEY into the following line, replacing the OPEN_AI_API_KEY placeholder:
string apiKey = "OPEN_AI_API_KEY";
To create an API key read https://help.openai.com/en/articles/9186755-managing-your-work-in-the-api-platform-with-projects. You’ll need to create a project and then a new API KEY.
https://platform.openai.com/api-keys
I can’t export the data model as .BIM due to TE3 Desktop subscription limitations, but the script generates a simple JSON containing some details about the data model, including tables, columns, measures, and relationships.
If you wish to save the content as a JSON file for review, uncomment the following line:
// SaveDataModelToFile(jsonContent);
and update file path:
string filePath = @"D:\DataModel.json"; // Update the path as needed
Otherwise the JSON file will be uploaded into an Open AI storage without saving a local copy.
Run the script and it will create an Open AI assistant with DataModel.json attached.
List of assistants is available at https://platform.openai.com/assistants (you must be logged in to see it)
Once there is an assistant you can use the Playground to test it (to chat with it about the data model).
Playground chat with an assistant:
The list of uploaded files is available at https://platform.openai.com/storage/ (you must be logged in to see it).
The assistant ID will be placed into the clipboard. Use it to config the other script.
Current version of the scripts do not support any assistant management (there are no scripts to edit or delete an existing assistant). If data model has been significantly changed, you may need a new assistant to be created. Or use https://platform.openai.com/assistants/ to upload new DataModel.json manually, or to delete an assistant. Also, use https://platform.openai.com/storage/ to delete uploaded files from the storage.
ai-comment-measure.csx
This script interacts with the OpenAI assistant using the assistant ID. It sends a selected DAX measure to the assistant for commenting, and creates a new measure with the AI-generated DAX code.
To start using the script edit the following lines:
string apiKey = "OPEN_AI_API_KEY";
string assistantId = "ASSISTANT_ID"; // Replace with your Assistant IDNote: ai-create-assistant.csx puts assistant ID into the clipboard
Now select a measure and run the script. The script should create a copy of the selected measure YourMeasure_Copy_with_AI_comments. The copy contains updated (with AI comments) DAX code.
Once an assistant created, you can use ai-comment-measure.csx script multiple times with the same data model. It’s OK to close Tabular Editor and .PBIX file and reopen them later. The assistant exists permanently (until deleted) on the Open AI server.
I do not trust AI enough to allow it to edit the existing measures. Therefore, the script just creates a copy of the measure so a human user can review it, edit it if required, and copy the code into the original measure.
This is just an experiment and a base for further development. Future versions will include scripts for managing existing assistant(s), optimizing the code, creating new measures, optimized prompts, and more.
Feel free to fork the repository and create a pull request if you made any useful changes.
Video: creating an assistant, using the assistant
Example
Original measure:
Average Country Sales :=AVERAGEX ( ALLSELECTED ( ‘Country’ ), [Sales Total] )
Measure copy with AI comments:
Average Country Sales_Copy_with_AI_comments :=— Calculate the average sales total for the selected countries
AVERAGEX (
— Evaluate the expression for all selected countries
ALLSELECTED ( ‘Country’ ),
— Reference the sales total measure
[Sales Total]
)
Security/privacy note: Do not publish code samples with your API KEY. Review the code and DataModel.json to see what the scripts sends to the Open AI server. In current versions it’s a list of tables, columns, measures, calculated columns and measures DAX expressions and other data model metadata. The script doesn’t send any data from the tables/columns.