Power BI Desktop and large data volume

[Lorenz33]

We are creating a Power BI report (hosted on premises on Power BI Report Server). This report contains a table that retrieves the data from a fact table in a relational SQL Server database. The number of columns requested is 38 and the report pulls in approximately 35 million rows. I came into this project in the middle of it and this was what was requested of me.

When attempting to load data into the report, the VDI runs out of memory and Power BI Desktop crashes. I then experimented and tried loading the data using Direct Query. It failed due to the fact that Power BI Desktop has a fixed limit of 1 million rows that can be imported. See:

https://docs.microsoft.com/en-us/power-bi/connect-data/desktop-directquery-about

It does say that to get around this, Power BI Desktop Premium can be used with the Max Intermediate Row Set Count setting used to adjust how many rows an individual query can fetch from the data source. Still, there are pros and cons with Direct Query and I am not sure if performance would be adequate with this number of rows. As question just for reference, how would we request Power BI Desktop Premium (for Report Server) and what is the cost?

Should we look into getting Power BI Desktop premium and use SSAS? Is there still a 1 million row limit with using a SSAS cube ast the data source? Even with Power BI Desktop Premium I suspect that we could run into issues with this much data.

Any advice and/or recommendations on how to handle this situation will be most appreciated.

[parry2k]

@Lorenz33 when you are working with a large dataset, you can restrict the number of rows when you are developing a solution and once it is ready, publish it and then you load all the rows in the service. This can be easily achieved (either you create views in the backend, give you fewer rows when developing) or create a parameter in the report, to switch between development and production and that can restrict the rows.

✨ Follow us on LinkedIn and  to our YouTube channel

Learn about conditional formatting at Microsoft Reactor

My latest blog post The Power of Using Calculation Groups with Inactive Relationships (Part 1) (perytus.com) I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos to whoever helped to solve your problem. It is a token of appreciation!

⚡ Visit us at https://perytus.com, your one-stop-shop for Power BI-related projects/training/consultancy.

[parry2k]

@Lorenz33 one million rows is a restriction when you are using DQ nothing to do with the license. Just wondering why you cannot use import? 

[Lorenz33]

Because when we use import, the computer we built it on runs out of memory and it crashes.

[parry2k]

@Lorenz33 I think everyone missed a point. Let me start:

- there is nothing like Power BI Desktop Premium. Premium is a capacity which you use and is only applicable in Power BI Service

- Why I mention the above point since you are using Power BI Report Server (On-Prem), Power BI Premium is not even in the picture.

- I don't have much experience with Power BI Report server so I cannot comment on it

- I worked with a very large database (it is not necessarily you can always aggregate the data) but I have worked with a dataset with a size of 300 ~ 400 million rows, with full optimization and filtering/aggregation where possible

- Using such a large dataset was possible in Power BI Premium and the performance was lightning fast since the model is fully optimized.

So for your question on Power BI Premium is not in the picture since you are using an on-prem Report Server, how does it work and what can be done there to work with large volume, sorry I'm not the right person but anything outside report server, I will be more than happy to assist.

Cheers,

[Lorenz33]

Thank you for your advice. I currently have a Power BI Pro license. If I get a Power BI Premium license (per user), I should be able to create and upload the report with more than 1 million rows right? Will users with the Power BI Pro license still be able to access all of the data in the report? In other words is it only required that the report creator has Power BI Premium in order to create all the report with the more than 1 million rows?

[AlexisOlson]

If you create a report in a PPU workspace, then only other people with a PPU license can view it.

Please re-read the other answers though. You should be able to work with datasets containing many millions of rows without running into this DirectQuery limit since this limit is not the dataset size but rather the number of rows returned in a single query. If you have a DirectQuery returning more than 1 million rows for a particular visual, then something is likely bad with the model or measure (whether you have premium or not).

[smpa01]

@Lorenz33 

I have queried, modeled, viz'd in PBI with SQL table currently at 63M+. 

It is possible that you don't need to bring the whole granualr table for analysis. In that case, you can easily do some server-side filtering /transformation/aggregation and bring the aggrgated data, which will dramatically cut down the size.

Should  you need the whole granualr table (probably not if you deep dive), you have premium workspace, you can take advantage of the dataflow ,incremental refresh and qery and store the result in dataflow and build report by querying dataflow. Make sure the SQL filtering and transformations are optimized else the dataflow will time out.

[AlexisOlson]

There's a fixed limit of 1 million rows placed on the number of rows that can be returned in any single query to the underlying source. This limit generally has no practical implications, and visuals themselves aren't going to display that many points. However, the limit can occur in cases where Power BI isn't fully optimizing the queries sent, and there's some intermediate result being requested that exceeds the limit. It can also occur while building a visual, on the path to a more reasonable final state.

Before looking at Premium I would try to figure out why in the world you're returning queries with that many rows (a visual typically doesn't require more than a few hundred data points). Something is likely wrong with the query optimization if it needs that many rows from a query.