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

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

Reply
Anonymous
Not applicable

Importing table with 140 million rows is timing out

Hello,

 

I'm trying to import data from a table with 140 million rows and I'm running into timeout issues. Any ideas on how to handle this import. 

And also can powerbi handle 140 million rows of data, whats the limit?

7 REPLIES 7

@Anonymous Importing probably isn't the best option for this many records... It would also depend on how many columns/unique values.

The Desktop has no limitation, but you can't publish a file that is over 1GB... which you might hit.

Where is your data stored?


Looking for more Power BI tips, tricks & tools? Check out PowerBI.tips the site I co-own with Mike Carlo. Also, if you are near SE WI? Join our PUG Milwaukee Brew City PUG
Anonymous
Not applicable

Source is SQL Server

 

So are you recommending me to aggregate data and remove unwanted columns?

@Anonymous Aggregation may not be necessary, but you should in practice never include extra data that you won't be using. This is especially true when working with larger datasets.

 

Other options. You could use Direct Query, this would keep all your data in SQL. But you would have to determine whether this performs well.

 

What version of SQL are you using?


Looking for more Power BI tips, tricks & tools? Check out PowerBI.tips the site I co-own with Mike Carlo. Also, if you are near SE WI? Join our PUG Milwaukee Brew City PUG
Anonymous
Not applicable

SQL Server 2008

I dont want to use direct query as I have to perform some calculations.

@Anonymous Minimize the dataset as much as possible to only the columns you need. In File -> Options and Settings -> Options, under "Current File" -> Data Load 

Turn off Type detection, turn off import relationships from data sources

turn off autodetect new releationships

Turn off Time intelligence - this is a huge one that will increase size exponentially for every date column you have. But this will also force you to create your own date dimensions if needed.

turn off background data

 

For increasing time out see this thread.

 

If you still have issues, your last step would be to aggregate to see if you can minimize the dataset size.


Looking for more Power BI tips, tricks & tools? Check out PowerBI.tips the site I co-own with Mike Carlo. Also, if you are near SE WI? Join our PUG Milwaukee Brew City PUG

one of the reasons why there are so many rows  (140M) is that each row represents a the state of a user on a specific date... so I am assuming that if I turn off the time intelligence I won't be able to leverage that power of the time series... Correct?

Correct.

However, since your dates will flow from the backend SQL Server 2008, time intelligence shouldn't be an issue. If it's a must have, you may replicate Date table from SQL or create a new one.

Hope that helps!

Please assign points if this helps.

Helpful resources

Announcements
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.