I was explaining to one of my blog readers recently how to extract table size from Power BI Desktop using DAX Studio and thought it would be useful for others. I have covered the basics of DAX Studio in the past so I wont cover this detail again here.
The latest version of DAX Studio can be downloaded here.
Connecting DAX Studio to Power BI Desktop
Open a Power BI Desktop file that has the data model you want to analyse. I have used one of my old Adventure Works files for this example. Once you have Power BI Desktop running and a pbix file open, you can then proceed to open DAX Studio. There are 2 ways to do this.
Options to Launch DAX Studio
If you want to use DAX Studio with an Excel Power Pivot data model, you must launch DAX Studio from the Add-ins menu in Excel. If you want to use DAX Studio for any other purpose (including connecting to Power BI Desktop), the normal process is to start the program directly from the Windows menu. There is a twist to this later approach however. If you launch DAX Studio from the Windows menu, there is no option to output your results directly to Excel (only to a text file).
If on the other hand you launch DAX Studio from within Excel, you can output your results directly to Excel.
Launch DAX Studio From Excel
Open a workbook in Excel. Navigate to the Add-Ins menu and launch DAX Studio.
You will be prompted with a warning saying there is no PowerPivot model. Just ignore the warning and select the PBI Desktop file you have opened (as shown in #1 below), then click connect (#2).
Extract Table Sizes
Now that DAX Studio is open and running, it is just a matter of executing a SQL statement that will fetch the table size information. You can copy and paste the SQL code from below.
SELECT dimension_name AS tablename, attribute_name AS columnname, datatype,(dictionary_size/1024) AS size_kb FROM $system.discover_storage_table_columns WHERE dictionary_size > 0
Copy and paste this code into the DAX Studio code window as shown below.
If you press F5 now, the query will run and the results will be returned to the results pane (as shown below).
A more useful approach however is to send the results to Excel for summarisation. Select Output (#1 below) then one of the 2 Excel output options. Remember these Excel options will only appear if you launch DAX Studio from within Excel.
Click on the Run button (#3 above) or press F5.
Switch to Excel. You will find the results in a new worksheet named DAXResults (#1 below) in your Excel workbook.
The sizes (#4 above) are given for each column (#3) in each table(#2).
Summarise the Results
- Click inside the table of results and then insert a Pivot Table.
- Place the tablename on Rows and size_kb on Values.
- You may like to change the custom name of the implicit measure to Size and format it to Number with one decimal place.
Your Pivot Table displays the sizes of the tables in your Power BI file.
You can also add the columnname to Rows in your Pivot Table and add some conditional formatting. This will help you spot the tables and columns taking up most of the space in your PBIX file.
Other Extract Tricks
In this previous post, I showed you how to extract measures and columns from Power Pivot. Now that you know how to connect to Power BI Desktop, you can apply these tricks from this previous post to Power BI too.