Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago

Loading data into power bi - huge dateset

so I have created a view in Oracle, and that view has around 4 million rows of data , i want to build a power bi report based on this view as dataset.   I will have parameters in the report and the...
Need Help
rainer1's avatar
rainer1
Resolver III
6 years ago

Hi,

 

there's a one-million-row limit for returning data when using DirectQuery, unless using a Premium capacity. The limit doesn't affect aggregations or calculations used to create the dataset returned using DirectQuery. 

 

For example, you can aggregate 10 million rows with your query that runs on the data source. The query accurately returns the results of that aggregation to Power BI using DirectQuery if the returned Power BI data is less than 1 million rows. If over 1 million rows are returned from DirectQuery, Power BI returns an error (unless in Premium capacity, and the row count is under the admin-set limit).

 

To answer your questions:

Question: when I build a new power bi report from scratch using this view as source, my understanding is, i have to load the 4 million worth data...: see above but 1 Million rows will requested, all DirectQuery requests are sent to the source database, so the required visual refresh time depends on how long that back-end source takes to respond with the results from the query

 

See aslo: https://docs.microsoft.com/en-us/power-bi/desktop-use-directquery#important-considerations-when-using-directquery

 

-------------------------------------------------------------------
Did I answer your question? Mark my post as a solution!
It was useful? Press Thumbs Up!

 

 

 

  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

    so my view has 20 million rows, I cannot aggregate anything and there are no calculations required. are you saying, when user applies the filter, he will see inaccurate data ? Pls advise, the above link you sent is not in English and i was not able to translate to english

    • rainer1's avatar
      rainer1
      Resolver III
      6 years ago

      Here is the link in English sry for that 🙂 :

      https://docs.microsoft.com/en-us/power-bi/desktop-use-directquery#important-considerations-when-using-directquery

       

      I think you will get an error like this but not shure because i never tested such ab big table in direct query.

       

      "The resultset of a query to external data source has exceeded"

      -------------------------------------------------------------------
      Did I answer your question? Mark my post as a solution!
      It was useful? Press Thumbs Up!

       

      • Anonymous's avatar
        Anonymous
        Not applicable
        6 years ago

        Thanks, I will try out power bi service ..i am thinking i can use microsoft ssrs as an alternative solution, as this is adhoc report, are you aware if similar problem exists with microsoft ssrs ?