Using query parameters within an online Power BI dashboard that access large amounts of data

1
votes

I am new to Power BI. I need to create a dashboard publish it so that users without the desktop app can still access it.

The table I am trying to retrieve statistics from is very large (400 MM rows). I can write queries that use a parameter to filter results to a much lower number (2MM rows). I need to create a link on the application that takes the user to the online dashboard showing the filtered results (retrieved based on the search parameter) which should be refreshed every time a user accesses the dashboard.

A few questions I have are:

1) Are 2 MM rows already too much data for Power BI Online to manage?

2) I know parameters are available within the Power BI Desktop but I read that if I publish the report with a parameter, it will use the param value that was set when publishing. Changing this parameter from the URI or per request won't be possible. Is this accurate?

3) Am I better off creating a SSRS report instead of querying the database directly from Power BI? And, if I do use SSRS, is there any value in showing the report through a Power BI dashboard? (rather than embedding the SSRS report in the application)

The database technology I am using currently is Oracle but I am going to migrate to SQL Server (either on prem or the cloud, I haven't decided that yet)

Thanks in advance!

1
reporting-servicesoracle11gpowerbi
Can you please clarify if you're importing the data into PowerBI or if you're using DirectQuery mode to connect to the database. – mendosi
Don't think there's directquery support for Oracle – user5226582

1 Answers

0
votes

It seems like you have many requirements that are difficult to mix. PowerBI can easily handle 400MM rows and get good, responsive interactivity.

You could easily schedule refresh this data a couple of times per day, but if you wanted it to refresh when a user accesses the report then that will cause some waiting, even if it was only loading 2 million rows.

Perhaps a solution is to have “near real time” data in PowerBI which contains all 400 million rows, and “live” data available through SSRS which will be less responsive and less interactive.

Migrating to SQL Server later might allow you to use DirectQuery which would give you live data (aggregated by the database engine) in PowerBI.