topic Re: Theoretical question, any help would be greatly appreciated! in Developer

Theoretical question, any help would be greatly appreciated!

leemmcc — Mon, 05 Aug 2019 14:26:25 GMT

Hi All,

I'm currently looking at PowerBI to replace our aging and expensive Crystal Reports Server. I'm struggling a little with what we want to achieve so anyone can point us in the right direction that would be wonderful (or even give ideas of what we could change?).

The scenario:

We have an app handling thousands of users per hour.
The users need to see embedded analysis.
The analysis should be their data but via a "shared" report.

The current setup:

Several Azure SQL Database instances, each one a subset of users' data.
Crystal Server displays various filters to the end user. The end user enters these and the values are mapped to a Stored Procedure's parameters and said procedure is executed.
The result of the sproc is loaded into in the report, on the fly, and rendering occurs.
We pass a UserID into the report viewer which is automatically mapped to a sproc parameter.

What we want to acheive with BI:

Create several non-duplicated embeddable reports.
Have the reports constantly connect to the database to get new data.
Allow us to pass UserID into the embedded report and for it to filter the result set.
Allow us to select the user's data from the correct database (as mentioned above).

From what I understand DirectQuery allows us to constantly request new data without importing it into the report. What I can't figure out is if this is going to allow us to do everything we need to achieve.

Thank in advance!

Re: Theoretical question, any help would be greatly appreciated!

Greg_Deckler — Mon, 05 Aug 2019 14:29:40 GMT

In theory you sould be able to accomplish your scenario in Power BI using Power BI Embedded along with dynamic RLS. The multiple databases and stored procedure introduces a bit of a wrinkle though.

Re: Theoretical question, any help would be greatly appreciated!

leemmcc — Mon, 05 Aug 2019 14:40:05 GMT

Hi Greg,

Thanks for the response, really appreciate it.

Regarding the multiple database/sproc issue, this is to overcome limitations in Crystal. We could change it to query the data directly and even merge to a single DB if needed.

Am I thinking correctly, that using Row Level Security would mean querying more data than I need in order to render the report? There would potentially be millions of records if all users were taken into consideration. I was thinking if UserID is passed (somehow) then the select query would only return records for that user? Again, purely theoretical.

Thanks.