Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
DarylM
Helper II
Helper II

How to load millions of rows of data quickly in Power BI Desktop

Hi All, 

I have been modeling in power BI for about two years now and our datasets are starting to become more and more complex. 

We have about 12 millions rows of transactions for the last three years. When building the model I am aware of the best practices like keeping your fact table narrow and lookup tables short. Also using numeric instead of Alpha's where possible, query-folding as much as possible on transformations, using measure rather than columns as much as possible, etc. We have a far amount of transformations / calculations on the fact table though link unique keys for relationships with other tables. 

 

After doing all of this to the best of my ability, my data still takes about 30-40 minutes to load 12 million rows. 

I tried aggregating the fact table as much as I could, but it only removed a few rows. I am connecting to a SQL database. 

 

This dataset gets updated daily with new data along with history. So since I can't turn off my fact table history in Power Bi service, all 12 million rows get loaded every day. 

 

Is there any suggestions on how to increase the load speed of my dataset? Primarily the fact table? 

I am not in IT and don't have control of the data or how it is stored, etc. 

 

Thanks!

 

2 REPLIES 2
Anonymous
Not applicable

If you have premium and it fits with your data model the easy solution is to use incremental refresh.

 

However if you do not have access to a premium workspace then I would suggest looking into using composite models.

Given you have said you are using SQL server you could use a combination of a composite model and aggregations to avoid having to load all 12 million rows at all. 

 

However again, depending on your use case aggregations may not help a lot depending on what level of granualirty you require.

 

 

 

Thanks ThomasFoster the suggestions!

We only have pro and I doubt the company will dish out for premium anytime soon. 

 

I considered composite model but looks like I would need a detail and agg table which would need to be loaded anyway even with a agg table. My fact table has customers and items ordered so I can't aggregate it too much without.

 

I will just try to find ways to minimize transitions and make the fact table narrower for faster loading. 

 

Thanks again!

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.