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

opticshrew's avatar
opticshrew
Resolver II
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

  • kotvir's avatar
    kotvir
    New Member
    8 years ago

    Its not useful, when you need realtime analytics and your query mode is "Direct Query"

    Potential solution one looking for should be, reduce the dataset size which is being used to load the inital set of rows by PowerBI to 10 or 100 and than let end user decide the recordset actually needed based on their reporting needs (restricting data via filter or other means).

    • opticshrew's avatar
      opticshrew
      Resolver II
      8 years ago

      Hi kotvir

       

      I agree and disagree with you :) 

       

      So yes this wouldn't be good for streaming data, it just wouldn't work. Ideally you'd want to create a streaming data set and tackle the whole situation differently.

       

      However, In by scenario I'm looking at static data that changes infrequently and will be using "Import". Additionally the dataset we're using contains all the information we need where we need to view the dataset in it's entirety. Essentially we're looking at SharePoint farms and need to understand file type distribution throughout them. We then need the ability to dril down into a specific server, drive, file etc.

       

      Thanks,

      Jay

      • prabhuss's avatar
        prabhuss
        Helper I
        7 years ago

        any final answer for this. i am also facing same issue

    • Nammy_shah123's avatar
      Nammy_shah123
      New Member
      6 years ago

      Hi , 
      Hope you are doing well.

      Since i have the same scenario where more then 1million records needs to fetch & create the report .So in realtime would like to know ,Is power Bi good solution for that? If No then what would be the solution or tool for this.

      Thanks & Regards.

    • katherine_stz's avatar
      katherine_stz
      Frequent Visitor
      4 years ago

      When I tried this on Power BI desktop recently using direct query, I couldn't get rows past the initial set of 1000. I used a native query when connecting a SQL database for the limit 1000. Has someone else tried this method and got it to work?