Connect to SQL data source with 1 billion rows of data possible?

[natabird3]

Hello,

I am having a problem connecting to Power Bi SQL server to a view which has more than 1 billion row of data, i keep getting a timeout error message:

Any suggestions on how to actually load this view and make visualizations from the data? I am trying with direct query as i imagine with import it would be impossible to load all that data succesfully considering direct keeps timeout. Hope someone can advice, thanks for the help in advance. 

[V-lianl-msft · Accepted solution]

Hi @natabird3 ,

In import mode, it's hard for me to tell you how long it takes to import such a large dataset.

The VertiPaq works as compressed columnar db.

It is recommended you create a view to aggregate the data you need and import the view to Power BI, and you can take a look at the following blog about other best practices.

https://www.sqlbi.com/articles/data-import-best-practices-in-power-bi/ 

Power bi desktop has to run the whole model in memory on your local system while developing. Theoretically you're limited only by your budget for DRAM and to a lesser extent CPU, but in practice if the pbix file gets over 1gb, you're going to start to struggle with development performance.

You can use incremental refresh to only import a subset of data during development and then load the rest after publishing.

Best Regards,
Liang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[amitchandak]

@natabird3 , Try setting a higher connection time out. https://community.powerbi.com/t5/Desktop/Query-Timeout-on-update/td-p/53459

Or create a direct query connection and create a mix model with aggregate in power BI

[natabird3]

I put 10,000 should be high enougn no? But in that case it didnt even took the time as it gave an error way before the 10,000 minutes of timeout set...so not sure if power bi simply cannot handle this much data?