Loading data into power bi - huge dateset

Question

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 · Answer

Hi,&nbsp;there's a one-million-row limit for returning data when using DirectQuery, unless using a Premium capacity.&nbsp;The limit doesn't affect aggregations or calculations used to create the dataset returned using DirectQuery.&nbsp;&nbsp;For example, you can aggregate 10 million rows with your query that runs on the data source.&nbsp;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.&nbsp;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).&nbsp;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&nbsp;See aslo:&nbsp;https://docs.microsoft.com/en-us/power-bi/desktop-use-directquery#important-considerations-when-using-directquery&nbsp;-------------------------------------------------------------------Did I answer your question? Mark my post as a solution!It was useful? Press Thumbs Up!&nbsp;&nbsp;&nbsp;

anonymous · Answer

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 · Answer

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 · Answer

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 ?

rainer1 · Answer

I don't know exactly but it may be work for you.&nbsp;-------------------------------------------------------------------Did I answer your question? Mark my post as a solution!It was useful? Press Thumbs Up!

Forum Discussion

Loading data into power bi - huge dateset

6 Replies

Related content

Error loading Power BI report

Power BI Incremental Load HELP

power query editor slow loading

Cannot Load Model

Microsoft Load