Forum Discussion

opticshrew's avatar
opticshrew
Resolver II
8 years ago
Solved

Using Power Bi with Large Datasets

Hey folks,   I have Power Bi desktop and a Azure SQL data source that contains 450 million rows of data. When I load the data into desktop or using Direct Query the time it takes is unreasonable. ...
Need Help
  • opticshrew's avatar
    opticshrew
    8 years ago

    Anonymous

     

    I found that if I load by CSVs into Azure Data Lake Store I can use Azure Data Lake Analytics to choose the data I need and push that out to a new CSV. This new CSV should be below that 1GB limit anad therefore I can use ADLS as my Power Bi data source.

     

    If the 1GB limit is reach I can split out the CSV and import them seperately combining them in Power Bi.

     

    Thanks for the help,

    J

Anonymous's avatar
Anonymous
Not applicable
8 years ago

opticshrew,

In DirectQuery mode, you should have no problem to connect to the Azure SQL database as data is not imported into Power BI model in this case. But there is a 1 million row limit for returning data when using DirectQuery, you can get more details in this article.

For example, you can aggregate 10 million rows with your query that runs on the data source, and accurately return the results of that aggregation to Power BI using DirectQuery as long as the data returned to Power BI is less than 1 million rows. If more than 1 million rows would be returned from DirectQuery, Power BI returns an error.

Additionally, in import mode, how long does it take to import data from Azure SQL to Power BI Desktop? I would recommend 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/

Regards,
Lydia

opticshrew's avatar
opticshrew
Resolver II
8 years ago

Anonymous

 

I don't feel that I'm going to be able to aggregate the data, here is an example of what is coming through:

 

FilePath            AccessTime                    WriteTime
C:\FilePathA     01/01/2018 10:00 AM     01/01/2018 10:00 AM

The data is loaded into the SQL db from blob storage. Each blob is a CSV containing roughly 1 million lines. These are then loaded into SQL where they are given the correct column data types. *note the FilePath is differnt each time*

 

As for load time we don't currently have 450 million rows of data yet however this is what we need to plan for. Currently with around 200,000 rows of data your looking at around 20-30 mins.

 

Would you say it is best to create a report purely on a per CSV basis?

 

Thanks,

J