cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
majdkaid22
Helper V
Helper V

compress dataset

Hello Community,

 

I have a Dataset used by a user who every time create new measure or update something in the power query, would refresh the dataset on the desktop and then publish it. 

 

The DataSet size is now over 1gb and it's taking considerable time in the auto refresh of Power Update app (it does auto refresh every 5 mins) 

 

I would like to reverse the the amount of records in the Desktop Dataset, and Let Power BI Service query the refresh. 

 

Is there anyway I can reverse the data size in power query to minimize the dataset size? 

 

 

Much appreciated,

5 REPLIES 5
Phil_Seamark
Microsoft
Microsoft

HI @majdkaid22

 

Do you just want to reduce the amount of time taken refreshing when building the reports, or for just refreshing a published report?

 

What is your underlying data source?

 

You could set a filter on your dataset to only bring in recent records?

 

Perhaps remove unnecesssary columns and make sure you don't have any columns in your largest tables that have Datetime fields that include hours/mins/seconds (convert these to Date only column, and have a 2nd column for Hours/minutes if needed)

 

Just some ideas.


To learn more about DAX visit : aka.ms/practicalDAX

Proud to be a Datanaut!

@Phil_Seamark thanks mate. all the above have been taken into consideration already. we are in the FX Trading and I have tables with over 1m rows a day. 

 

My data sources are SQL, MYSQL mainly 

If your datasources is MS-SQL, have you considered building a summary table in SQL prior to Power BI rather than reading from raw tables?

 

What is the lowest grain you need to report on in Power BI?  That would be the target level of summarisaion in SQL.

 

 


To learn more about DAX visit : aka.ms/practicalDAX

Proud to be a Datanaut!

@Phil_Seamark that is a long term option which am working on. at the moment I have MYSQL DB which are a bit messy and I don't have the enough resources to structure views in there. 

 

Guess I will have to try and go on another round of removing unrequired columns...

 

 

Cheers mate

Columns that have unique values are good ones to target.  If you can get rid of these then your ability to compress will be improved.

 

Also if you can pivot data to be vertical rather than horizonal, this will help


To learn more about DAX visit : aka.ms/practicalDAX

Proud to be a Datanaut!

Helpful resources

Announcements
MBAS on Demand

2021 Release Wave 2 Plan

Power Platform release plan for the 2021 release wave 2 describes all new features releasing from October 2021 through March 2022.

July 2021 Update 768x460.png

Check it out!

Click here to read more about the July 2021 Updates

Power Query PA Forum 768x460.png

Check it out!

Did you know that you can visit the Power Query Forum in Power BI and now Power Apps

Urdu Hindi D365 Bootcamp 768x460.png

Urdu Hindi D365 Bootcamp

Dont miss our very own April Dunnam’s The Developer Guide to the Galaxy! Find out what the Power Platform has to offer for the traditional developer.

Top Solution Authors
Top Kudoed Authors