Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
leemmcc
Frequent Visitor

Theoretical question, any help would be greatly appreciated!

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!

2 REPLIES 2
Greg_Deckler
Super User
Super User

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.


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

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.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors