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.
why group by doesn’t work?
Hi Matt,
This is great and very helpful, but I ran into a weird case: the model got impressively fat, at about 540MB, and the query just returns 60. Is there anything else that might be the reason for such a big difference?
As additional information: the model was slightly bigger when I started the analysis; reducing the size of one table, as shown by the analyser, by 35MB resulted into an equivalent shrink of the size on disk.
What can I do?
There are lots of things that take up space. the tables, the relationships, the dictionaries to name a few. You could take a look at vertipaq analyzer, but I don’t know if that will connect to Power BI. You will have to check https://www.sqlbi.com/tools/vertipaq-analyzer/
Does anybody know why $SYSTEM.DISCOVER_CALC_DEPENDENCY does not work with .pbix files anymore?
I have already asked around at Microsoft about this. The response I got was that this was never a supported feature. I will be at the MVP conference in March and I intend to take it up then.
Does this mean that it is possible to call a stored procedure from within DAX Studio?
That would support a governed self-service approach when the user community ( in my company, for example, ) are not able to handle building the model and doing the joins on their own.
I assume when you say Stored Procedure you mean SQL Server. DAX Studio doesn’t connect to a SQL database, only a Tabular model (SSAS, Power Pivot or Power BI).
Thank You, What I found interesting and good reminder on the impact of cardinality (the number of unique values in a column). I listed each column under the table name in the table name. It lists the size of the column so you can see the impact of each column in your tables. In my data, I found that the “amount” column comprises 93% of my sales table. The next largest column is the “Customer ID”, that makes up only 5%! of the sales table.
Exactly Gary! Just ‘seeing’ this information can make a big difference to your understanding. I think a blog about compression may be in order some time. My tip is take a look at the precision of your amount column. If you can reduce the precision (eg round to cents, or dollars, or less decimals) you will reduce the file size
And the TIP about running DAX using ADD In inside Excel to Output to Excel is an undocumented feature that you have just let me discover…..thankyou Sir 🙂 ..Could never work out why it didnt work when i ran it from desktop.
I had the same experience, and I even logged a bug stating that the export to Excel was missing. Then I was told it wasn’t a bug. At least this work around solves the problem 🙂
Useful stuff…per the norm from you! thanks Matt again for your time on helping us at the bottom of the mountain.
@Matt – The size of my PBIX file is 2919 kB
The total in the pivot is 5350kB – what could be the reason for the difference
Does the PBIX file inflate after it is opened ?
There are a few things, but most likely it is uncompressed data. The data is compressed when stored, but can be decompressed during usage. To get the most accurate read, you should close the PBIX, reopen it and then run the extract immediately.