Importing table with 140 million rows is timing out

[Anonymous]

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?

[Anonymous]

@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?

[Anonymous]

Source is SQL Server

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

[Anonymous]

@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?

[Anonymous]

SQL Server 2008

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

[Anonymous]

@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.

[Bbrown44]

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?

[giftedbrain]

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.