Loading data into power bi - huge dateset

[Anonymous]

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 user wants to use it once in a quarter so its not for everyday use, the parameters should restrict the data to be shown in the report...I am going for direct query mode because this is adhoc

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 ...is this true and is this true when I deploy this report to production ? (meaning deploying this report involves loading of data)..note this is in direct query mode

[rainer1]

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

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

[Anonymous]

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]

Here is the link in English sry for that 🙂 :

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

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]

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 ?

[Anonymous]

Hi @Anonymous ,

You are able to pull more than one million rows data from SSAS to Power BI Desktop via connect live mode.

Check the following topics for more details.

https://community.powerbi.com/t5/Desktop/live-connection-limitation/td-p/566199.

https://community.powerbi.com/t5/Desktop/Limitations-about-connecting-a-SSAS-multidimensional-model/td-p/450262.

Best Regards,

Jay

Community Support Team _ Jay Wang

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

[rainer1]

I don't know exactly but it may be work for you.

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