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.
Updated Multiple Query Detection to work with // Dax Query in addition to // SQL Query comments
Adding extra checks around QueryEnd timeout in Server Timings
Close the Trace Layout ribbon tab when all documents are closed
Made ‘Add New’ disabled in the Query Builder if the current model has no tables
Fixed #458 added docs and tooltip to explain memory sizes
Fixed #517 updated clipboard bindings to allow for copying from the View Metrics tabs
Fixed #539 error starting traces when using Roles or EffectiveUserName
Fixed bug in retry for ViewMetrics when there is an error reading statistical information.
Added configuration to read statistical data for VertiPaq Analyzer. Disable reading statistical data for legacy versions of SSAS. Added retry logic and log of warning if there is an error reading statistical data.
Fixed #525 F1 and Context Menu to open dax.guide for functions
The next version of DAX Studio has just been released and you can get it from the home page at https://daxstudio.org
This release has actually ended up to be a bit larger than originally planned, but hopefully this means there will be something in here for everyone.
In addition to a number of bug fixes and smaller improvements, this version includes the following major features:
Native Excel output
Clipboard results target
New Options layout
View Metrics improvements
Query Builder improvements
Prompt before refreshing metadata
Export Data improvements
Read on for more detail about each of these.
This is one of the bigger features that hopefully no one will notice. I spent a few days re-architecting the internals of the connection management so that now every tab has it’s own ConnectionManager object and if a connection is broken for any reason the ConnectionManager will automatically attempt to re-connect to the same data source and database. This is particularly an issue for Azure Analysis Services and the XMLA endpoint for Power BI Premium where backend operations like deployments, scaling and model synch operations can cause sessions to be dropped. Whenever the connection is retried for any reason you will see a warning in the Output pane like the following.
If you see one of these warnings while you are doing anything involving performance tuning you should re-try the previous operation because at least part of the time would have been taken up by re-connecting to the data source.
Native Excel Output
As of v2.13 you can now output directly to a static XLSX file from the standalone version of DAX Studio (so no need to launch from the Excel add-in just to output data directly to an XLSX file). The existing csv export format was specifically optimized to work well in Excel, but I have heard that in the past people were sometimes using the Excel addin just to get output into a native Excel file. In addition this should be much more efficient on memory usage as the data gets streamed directly to the file and you do not need have to have Excel running.
To keep in synch with the Excel addin I have also added a “Linked” Excel output. This basically generates an ODC file with an embedded DAX query.
I had previously held off from implementing a native export to Excel as there was no good solution that did not involve generating the Excel file in memory before saving it to disk. But thanks to a new library called LargeXlsx it’s now possible for me to generate potentially huge files while maintaining a low memory footprint.
Although note that Excel files still cannot exceed 1 millions rows. That limitation is built-in to the file format.
Clipboard Output Target
In addition to the new Excel result targets we now also have a Clipboard target. When using this the results of any queries are sent directly to the Windows clipboard where you can paste them into another application.
New Options layout
Over the years I’ve added numerous settings to the options page in DAX Studio, but it’s now at the point where having everything visible on the one page does not work and it’s just too hard to find things. So I’ve re-built the Options page using a new framework that organises the settings into categories which can easily be browsed through and searched
View Metrics Improvements
There have been a number of small improvements to the View Metrics functionality in this release including:
Added a Loading indicator. There was one in the status bar, but the Metrics view has been aligned to show the same sort of “busy” overlay as the other windows do.
Fixed Sorting in View Metrics table view to do a nested sort and to sort Descending on first click
View Metrics – Fixed KB/MB/GB (it was lowercase, which is for bits not bytes)
Added Tooltips to most of the columns
Query Builder Improvements
The original filter patterns used by the query builder were copied from queries generated by Power BI Desktop and were in the form of
The Query Builder now also supports BETWEEN filters for dates and numeric columns and IN and NOT IN lists for text columns.
Prompt before refreshing
There is now an additional option in the automatic metadata refresh where you can ask DAX Studio to prompt before refreshing.
The Data Export feature will now check if the data source supports the TOPNSKIP function and will use that if possible to export data in batches of 1 million rows at a time. This should allow us to export massive data volumes without triggering out of memory errors. I was able to do a test exporting 120 million rows from Power BI Desktop which generated a 20 GB csv file on my laptop (with 16 GB of RAM) and during the export Power BI stayed at around 1 GB of memory usage and DAX Studio hovered around 300 MB of memory usage.
The default file export format has been changed to csv instead of tab delimited since csv is probably a more common format and handles things like embedded line breaks better.
Fixed #400 duplicate New option in customize Quick Access Toolbar
Fixed #401 unable to re-show Power BI Performance window after hiding it
Fixed #405 / #419 incorrectly trying to write external tools file when doing a non-admin install
Fixed crashes when right-clicking on certain tool windows
I know a lot of BI developers that have strong skills in DAX, Power Query and SQL, but don’t often use C# and many of them don’t use VS Code and have not heard of nuget. So this made me wonder if there was a another way of doing the same thing with the tools that many BI developers already use like DAX Studio and Tabular Editor
I actually figured out a couple of approaches to achieving the same result as Phil. The first one uses the following 2 pieces of information.
Tabular Editor has a brilliant feature called Advanced Scripting which lets you run little pieces of C# code and is an excellent way of automating changes in your models. And in addition to being able to use the standard properties and methods Daniel has build a series of helpful “helper” methods like AddMeasure which has parameters for <Name>, <Expression> and <Folder>.
When you open DAX Studio from the External Tools menu and run this query you get output that looks like the following and you can selected the “ScriptExpression” column and copy that.
Then you open Tabular Editor from the External Tools menu. Click on the Advanced Scripting tab and paste in the output from the “ScriptExpression” column. Note this may include the “ScriptExpression” column header at the top which you will need to delete.
(note to self, I should add a “copy without headers” option to DAX Studio, there is an option for this, but it would be nice to add it to the right-click menu on the results)
Then when you click “run” (1) on the advance script, you will see a folder with all your new measures appear (2). You can then check that the expression has been entered correctly and click save (3) to make these appear back in Power BI Desktop.
Stay tuned for the next post in this series where I will show another technique for doing this.
The 2.11.0 release of DAX Studio is now available and brings with it the following new features and fixes.
New Preview Features
There are 2 new preview features this month, so you need to go into Options > Advanced and enable them if you want to use them
When enabled, the Query Builder appears as a button in the main ribbon
It lets you drag and drop columns and measures to build up a query which can include basic filters. You can also add custom measures or override the expression of a measure from your data model.
You can either run the content of the query builder directly or you can click the “Edit Query” button to send the text for the query to the main editor window where you can run it or further customize it.
The Query Benchmark tool appears as a button on the Advanced ribbon. It allows you to run a given query a number of times both against a cold and warm cache. This is useful because even on a quiet development server there can be a number of factors that can cause variability in the server timings.
The Benchmark feature makes use of the Server Timings functionality to record detailed information about each query execution.
You get the option of how many runs of cold vs warm cache (and by default these are linked)
The output of a Benchmark run shows a summary view with the Avg, StdDev, Min and Max of both runs for the Total Duration and the Storage Engine Duration
The detailed output shows the timings of every single query execution.
In addition to the two big features above there are a number of smaller features that have been added in 2.11.0
I’ve just released the 2.10.2 update to DAX Studio.
Starting with the 2.10.0 releases the crash reporting has been improved as there was previously an edge case where certain types of crashes were not triggering the crash report dialog. So this release includes a number of stability updates that have come from people submitting crash reports.
There are also a few smaller features that are detailed below
It seems that the last few years I’ve always seemed to do a pre-Christmas release for DAX Studio and this year is no different. The latest update of DAX Studio has just been released which includes a number of new features and bug fixes which are outlined below.
When you hover over the database dropdown in the metadata pane we now display an informational tooltip which includes the compatibility level of the database, the culture of the database and the date the data was last refreshed.
A couple of new settings have been added File – Options screen
The first lets you configure whether the editor inserts tabs or spaces when you indent code. This can be useful if you use other code editors and wish to keep a consistent style across them all.
The second lets you configure a custom export format by specifying a delimiter to use and whether or not to always quote string fields (however if your strings include your delimiter field they will still be automatically quoted as not doing this would not allow the file to be consumed properly).
This configuration appears as an option when you save query results to a file target or when you use the Export Data Wizard
A new Summary Pane has been added to the Vertipaq Analyzer view. This page gives the user a single place to see summary information about the model including the total number of tables and the total size of the model.
The tables pane now has a new column that indicates if any of the relationships for that table have RI violations. This uses the DMV that Phil Seamark pointed out in his recent blog post
You can now also import metrics that were previously exported
Date Table indicator
The metadata pane has been updated to indicate if a table has been marked as a “Date” table.
Ribbon buttons for Issues and Feature requests
I have seen a few issues posted in various other forums like the Power BI community forum over the years so I’ve decided to add buttons to the help ribbon to allow a quick and direct way of posting issues directly to our github issues list (although a github login is still required)
Search for Functions and DMVs
Both the Function and DMV panes now have a search box.
And in addition to the above features it includes numerous bug fixes including the following:
#224 Server Timings and Query Plan traces now work with the EffectiveUsername connection string parameter
There is an ongoing issue with some people getting an error from the Excel Add-in reporting a MissingMethodException which is preventing connections to PowerPivot models. However I’ve double checked a copy of the dll in question and it does contain the method so this error does not make any sense. And I have been unable to reproduce this issue on any of the systems which I have access to. If anyone is able to assist with more information on how to reproduce this issue I’d love to hear from you as I’d like to get this issue fixed.