Which tables/queries are using most space and refresh time

IanR
Posts: 13
Joined: Wed Aug 09, 2017 11:30 pm

Which tables/queries are using most space and refresh time

Postby IanR » Sun Nov 26, 2017 9:07 pm

Hi,
Is there a way to find out which tables/queries in the model take up the most space and which Power Query operations take the most time? I have a model that has suddenly bloated from 70 MB to over 200 MB. The change I made before this happened was to filter out around a million records. The model should therefore have got smaller, not tripled in size. It is also taking over two hours to refresh. It would really help to have some clue as to which tables and processes were causing these problems.
Thanks
Ian

MattAllington
Posts: 929
Joined: Sun May 04, 2014 4:01 pm
Location: Sydney, Australia

Re: Which tables/queries are using most space and refresh time

Postby MattAllington » Tue Nov 28, 2017 7:38 am

Hi Ian

Is it Power Pivot or Power BI? If Power Pivot, use https://www.sqlbi.com/tools/power-pivot-utilities/ to check the table size.

If it is Power BI, you could try my local host workbook https://exceleratorbi.com.au/measure-de ... -power-bi/

For Queries, you may need to write some VBA to record time stamps
Matt Allington is Self Service BI Consultant, Trainer and Author of the book "Supercharge Power BI".
https://exceleratorbi.com.au/power-bi-online-training/

IanR
Posts: 13
Joined: Wed Aug 09, 2017 11:30 pm

Re: Which tables/queries are using most space and refresh time

Postby IanR » Wed Nov 29, 2017 12:06 am

Hi Matt,

I tried the Power BI version and got and Application Defined or Object-Defined error in RefreshSSASConnection. Ordinarily I would get the VBA to skip the problem step but as it seems to have got stuck on just the bit I am interested in (Sheets("Memory Usage").PivotTables("PivotTable1").PivotCache.Refresh) that might not help.

Thanks


Ian

MattAllington
Posts: 929
Joined: Sun May 04, 2014 4:01 pm
Location: Sydney, Australia

Re: Which tables/queries are using most space and refresh time

Postby MattAllington » Wed Nov 29, 2017 10:02 am

Thanks. Yes it seems there is a new bug in my workbook. MS keeps changing how it works and I have to keep changing the workbook. You can use DAX Studio to extract the table sizes manually. Here is an intro to DAX Studio
https://exceleratorbi.com.au/getting-st ... ax-studio/

And here is the SQL to extract the size details.

Code: Select all

SELECT dimension_name AS tablename,
       attribute_name AS columnname,
       datatype,
       (dictionary_size/1024) AS size_kb
FROM   $system.discover_storage_table_columns #(lf)
WHERE  dictionary_size > 0
Matt Allington is Self Service BI Consultant, Trainer and Author of the book "Supercharge Power BI".
https://exceleratorbi.com.au/power-bi-online-training/


Return to “Power Query”

Who is online

Users browsing this forum: Google [Bot] and 5 guests

cron