I am happy to announce that v3.1.0 of DAX Studio has just been released. There are a number of new features in this release including a brand new command line which will allow for the automation of common tasks. Be sure to read through the details of all the new and updated features here: v3.1.0 Release | DAX Studio
Tag: daxstudio (Page 1 of 2)
Today I am pleased to announce that the next update to DAX Studio is now available. It is a minor point release that includes one or two small updates and a number of fixes including what is hopefully a definitive fix to the “xmlReader in use” errors some of you have been seeing in 3.0.6
Check out the detailed release notes here v3.0.7 Release | DAX Studio
I am pleased to announce that v3.0.6 of DAX Studio has just been released. You can download this version from https://daxstudio.org and for more details on what’s new in blog post here v3.0.6 Release | DAX Studio
Some of you may have already seen the video from SQLBI showing the new timeline feature in Server Timings – if not, you should definitely watch this as it has some great information on this new feature and how to use it.
In my previous post I had a short 40 line script for Tabular Editor (free | paid) which could generate descriptions for the measures in your tabular model. There were a number of interesting questions in the comments to that post which I thought would make a follow up post worthwhile.
The things I want to cover in the post are
- Running the script in Tabular Editor 2 (free)
- Running the script in Tabular Editor 3 (paid)
- Dealing with Rate Limiting
- A Final Warning
How to run the script using TE2
Below is a very brief tutorial on running a script using Tabular Editor 2
- If you launched Tabular Editor from the External Tools ribbon in Power BI desktop you can skip this step, otherwise click on this icon to connect to your data model.
- Click on the C# Script tab and paste in the script from my earlier post
- There are instructions in my earlier post for requesting your own API key from Open AI you need to paste the key into this line between the quotes (“)
- Optional: if you are targeting users that speak a language other than English you can update the question text on line 10 to your desired language (from what I understand ChatGPT understands about 100 different languages so you might need to test if this works in your language)
- Click the play button to run the script, this step could take a while depending on the number of measures and whether you hit throttling (more on this below)
- Once the script has run you can check some of the descriptions by clicking on a measure in the model explorer. At this point all the descriptions are still local and have not been saved back to your data model.
- The text generated by ChatGPT will appear in the Description property for the measure.
- If you are happy with the generated descriptions clicking the Save Changes button will save the new descriptions back to your data model.
How to run the script using TE3
The steps for Tabular Editor 3 are almost the same with the main difference being at step 2 because TE3 allows you to have multiple C# scripts open at the same time.
- If you launched Tabular Editor from the External Tools ribbon in Power BI desktop you can skip this step, otherwise click on this icon to connect to your data model.
- Click on the New C# Script button to create a C# script document and paste in the script from my earlier post
- There are instructions in my earlier post for requesting your own API key from Open AI you need to paste the key into this line between the quotes (“)
- Optional: if you are targeting users that speak a language other than English you can update the question text on line 10 to your desired language (from what I understand ChatGPT understands about 100 different languages so you might need to test if this works in your language)
- Click the play button to run the script, this step could take a while depending on the number of measures and whether you hit throttling (more on this below)
- Once the script has run you can check some of the descriptions by clicking on a measure in the model explorer. At this point all the descriptions are still local and have not been saved back to your data model.
- The text generated by ChatGPT will appear in the Description property for the measure.
- If you are happy with the generated descriptions clicking the Save Changes button will save the new descriptions back to your data model.
Dealing with Rate Limiting
If you have not worked with API calls before you may not have come across the term “rate limiting” before. Basically, it is a mechanism that APIs use to prevent users from monopolizing the resources on a service by limiting the number of calls that can be made within a given timeframe.
In the case of the Open AI APIs they document their rate limits here and at the time of writing they only allow 20 calls per minute for a free account. Once you hit that limit the API will return a 429 error code which is a common code meaning “Too many requests”. There are 2 approaches that you can use to work around this.
- You can add pauses in the code to wait until the next minute when you can then make another 20 calls.
- You can upgrade to a paid account which has much a higher limit.
If you would like to see an example of an updated script which will skip measures which already have descriptions (so if you’ve manually updated some or if there was an issue part way through running a previous script
Update Tabular Model Descriptions from ChatGPT with rate limiting logic (github.com)
A Final Warning
The Large Language Model (LLM) behind ChatGPT is an amazing piece of technology and it’s only going to get better over time. But I’ve also seen it described as “a B grade intern who hallucinates occasionally”. In it’s simplest form all these AI models do is to break a piece of text into a series of tokens and then predict what tokens are likely to come next based on a corpus of training material.
In my testing I’ve seen a number of examples where the prediction has missed some important detail. And in a couple of cases, I’ve even seen it include a modification to the original measure and then it has described the modified measure, not the original one!
So, while this is definitely a huge time saver, just be aware that it’s not perfect. Before publishing these descriptions into production, I would suggest doing a review of them to ensure that they make sense and include all the important details.
If you want to see all the descriptions at once you can use a tool like DAX Studio to run the following query to list them.
SELECT [Name], [Description], [Expression] FROM $SYSTEM.TMSCHEMA_MEASURESI’ve just pushed out an update to DAX Studio with a number of fixes and a few small updates. You can download it now from https://daxstudio.org
Updates
- Added query information button to traces
(This captures the actual query text and any parameter values plus the ActivityID) - Updated Query History icons
- #949 Exposed Theme/Mode setting in Options
- Added MPARAMETER keyword to syntax highlighting
Fixes
- Fixed #891 Error exporting to static Excel file
- Fixed #889 crash when dragging trace windows
- Fixed #886 incorrect link to tutorial in Getting Started dialog
- Fixed #894 Define and Expand sometimes producing incorrect output
- Fixed #924 username blank in All Queries trace
- Fixed #930 increased default code completion window width
- Fixed #931 automatically select all text in find/replace dialog
- Fixed #906 improved waterfall layout in Server Timings
- Fixed setting focus in the editor when changing tabs
- Fixed file icon registration
- Improved error handling around shutdown operations
- Added extra hotkey validation rules
- Fixed #916 added retry logic to all clipboard operations
- Improved Power BI filename detection for non-English cultures
- Removed unused dependencies and reduced installer/portable file size
I know it’s been a while since I’ve released an update to DAX Studio. But I’m happy to announce that version 3.0 is now available with a major update to the User Interface including the option for a Dark theme. You can download it from https://daxstudio.org (which has also had a major update and can be viewed in Dark mode) and you can read about what’s new in this release here: v3 Release | DAX Studio
DAX Studio 2.17.2 has just been released and the following is a run down of the new features and fixes that you will find in this release.
Apologies to anyone who attempted to download the 2.17.0 release – there was an update to the code signing tool and it failed to correctly sign the installer. The automated build has now been fixed so that it correctly signs the installer again.
New Features
Added Parameter support to Query Builder filters
The Query Builder now supports parameters. Typing an @ as the first character in a query builder filter value will switch the value into parameter mode and the following text will be treated as the parameter name. (typing @ a second time or clicking on the purple @ button will toggle the parameter back to the standard value mode)
When you run the query builder where a filter has a parameter you will be prompted for the parameter value just as if you had typed out a query that included a parameter reference. This allows you to build a query with parameters that can be pasted into a paginated report or any other tool that supports DAX parameters.
Removing <pii> tags from xmSQL in Server Timings to improve readability
Power BI and AzureAS have added <pii> tags around some of the text in the query events that DAX Studio captures as part of the Server Timings. These tags allow Microsoft to strip out information that could potentially be personally identifiable when writing them to centralized logs. But because DAX Studio does not send any of this information off your machine and because this information also includes important contextual information for troubleshooting and performance tuning we are just removing these tags to reduce the noise and improve readability.
Fixing accuracy of Server Timings for queries with Batch events
Prior to this update Storage Engine batch events timings were not being added to the SE duration figures. For a lot of models this difference is insignificant, but for large queries against large models this can result in an incorrect inflation of the FE duration. Keep an eye on the posts from Marco and Alberto at sqlbi.com as they are working on an in-depth video to explain this improvement to the Server Timings.
Query Builder queries are now captured in the query history
Prior to this update the Query History pane was only capturing the text of queries that had been typed into the query editor. Now the Query History pane captures any query that was executed regardless if it was from the edit window or from the Query Builder.
There is now an icon next to each query history row indicating where it was captured from and if you double click on a query that was originally run in the Query Builder it will be restored back into the Query Builder window
The /f parameter can now open vpax files
Now when you run DAX Studio from the command prompt the /f parameter will open either .dax or .vpax files.
Added extra information to the error message when the XMLA endpoint is not correctly enabled
If the Power BI XMLA endpoint is not properly enabled the Microsoft ADOMD provider returns a very generic error message making it hard to know what has gone wrong. DAX Studio now adds some extra text after the Microsoft error if you are attempting to connect to an XMLA endpoint and hit this generic WebException error to suggest you check the XMLA endpoint settings in your tenant.
Improved caching of measure expressions
Prior to this update features that required access to the text of the multiple measure expression such as the “Define All Measures” feature would request the expression text one at a time, now all of the measure expressions are retrieved in a single request.
Fixes
- Fixed #703 preventing a crash when entering a proxy password
- Fixed #715 added DOUBLE data type to intellisense and syntax highlighting
- Fixed #740 export to Excel failed on some machines
- Fixed #755 saving parameters to XML was not correctly encoding special characters
- Fixed #750 would crash on startup if the WMI information on the machine was corrupt
- Fixed download link in Help – About screen
DAX Studio v2.16.2 has just been released and is available at daxstudio.org. It includes the following fixes and improvements. The first one being particularly important:
Traces fail to start with a timeout error
Fixed issue #664 – A small refactoring of the trace engine for some future work has accidentally resulted in the engine not being able to detect if a trace has been started or not, resulting in timeout errors. This appears to affect most scenarios apart from one or two edge cases. Unfortunately it was one of those edge cases that was used when testing that the previous code change had not broken anything. The 2.16.2 code base has been tested against all the different engine types PowerPivot, Power BI Desktop, Power BI Desktop as an External Tool, a local SSAS Instance, a remote SSAS Instance, Power BI Premium XMLA Endpoints and Azure Analysis Services
Showing View Metrics Last Refreshed and Analysis Date in the local timezone
Fixed issue #663 – Previously the Last Data Refresh and Date of Analysis were both displayed in UTC in the Summary tab of the View Metrics pane. Internally the dates are still stored in the vpax file in UTC so that that if you send the vpax file to someone in a different timezone the times will n
When saving a file with the Query Builder open custom measure names were not saved
Fixed issue #659 – if you save a file with the query builder open which has custom measures that were created within the query builder then the name of those custom measures was not saving properly prior to this fix.
Debug commas not handling lines with indented comments correctly
Fixed issue #663 – the new debug commas preview feature was not correctly positioning the commas if one of the lines of the query had an indented comment. This issue and a few other ones around different comment placements have been fixed.
View Metrics – Last Refreshed date was incorrectly adjusting from UTC time
Fixed issue #670 – The Last Refreshed Datetime in the View Metrics summary page was incorrectly getting the timezone offset applied twice when calculating the UTC time. This resulted in the time being moved too far forward if your local timezone is behind UTC or too far back if your local timezone is ahead of UTC.
This update to DAX Studio includes all the new features introduced in 2.16.0 but includes the following bug fixes:
- #657 Unable to open a new query tab either with a new connection or with a copied connection
- #658 passing the database name on the command line
- #647 hidden objects not hidden correctly when show hidden objects option was turned off (this was partially fixed in 2.16.0 but should work in all scenarios now)
If you were in my recent session at the DataANZ 2021 conference I mentioned that I would make a copy of the queries I created during that session so here they are!
In my demos I used the Adventure Works 2020 pbix file from here https://aka.ms/dax-docs-samples
And I added the following simple measure to the Sales table:
Total Sales = SUM( Sales[Sales Amount])
You can download the script of the demo queries from my OneDrive
Recent Comments