Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
Because of the lack of multi-tenancy support in PowerBI, I need to deploy a report per customer. Each cutomer gets a seperate copy of the pbix with their own data in it. I assume this means I need to filter on CustomerId in my import sql (unless there is a better approach). I can't see a way to change the sql programmatically. The REST API doesn't seem to have that provision.
I could really use some help on this one.
You might have a look at this.
In a similar manner as described in the linked post, you could create a CustomerID parameter (aka blank query), and possibly pass it as a parameter to stored procedures fetching the data.
It sounds like there's a big concern of cross-customer data contamination - you want to make sure no customer can access other customers data. One way of doing this would be to make sure no data is returned unless correct CustomerID is passed (at least), that your CustomerID is stored in a single place, and you don't expose any naked sql in your report.
Just to add to my post:
I managed to create a global parameter and use that in the sql for each table. But then found that parameters can only be used in PBI Desktop! Not that it would have helped unless there were a way to update the parameter programmatically.
I just don't see a way to use PowerBI in a multi tenant environment.
Check out if row level security would do this job for you: https://gqbi.wordpress.com/2016/10/03/power-bi-dynamic-row-level-security-things-you-need-to-know-to...
or: http://radacad.com/dynamic-row-level-security-with-power-bi-made-simple
Imke Feldmann (The BIccountant)
If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!
How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries
Hi Imke,
Yeah, I've looked into RLS. It doesn't help in my case. I can't have all the data in a single PBIX - there's simply too much. Also the data for each tenant comes from a different set of tables.
Ah, now I get it - sorry, didn't read careful enough.
How about allocating a group in the active directory which then point to the correct keys? As far as I know you can import tables from the AD and use them as lookup tables for the correct "parameters" programatically.
.. well - this would work if we could use USERNAME in M already. But: We only have it in DAX at the moment. And as DAX is (very unfortunately) a one-way-street at the moment, this seems to be a dead end 😞
So as a workaround, you could ask the users to store a file with their username on their desktops with fixed path (that would the same for every user). This would be imported and passed as a parameter. If you need additional security here, you could then add RLS if needed.
Anyone has an idea for a more elegant way to solve this?
Imke Feldmann (The BIccountant)
If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!
How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries
I have a very ellegant way to solve this problem. I'm doing a proof of concept. If it pans out I might put together a blog post. I'll let you know...
Really? Can't wait!! 🙂
Imke Feldmann (The BIccountant)
If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!
How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries
Except I've just realised something that might blow it out of the water: no data refresh in PowerBI Embedded. I can't believe this is true. It pretty much makes Embedded unusable. There are posts saying that to refresh your data you need to re-import using PBI Desktop and re-upload. And even that can't be automated! I have hundreds of customers with individual PBIX files. I need hourly refresh.
This is not my area of expertise - but you should make your voice heard in the ideas-section of this forum.
Maybe the techniques in this article would help temporarily: http://prologika.com/automating-power-bi-desktop-refresh/
Imke Feldmann (The BIccountant)
If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!
How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries
User | Count |
---|---|
140 | |
113 | |
104 | |
77 | |
65 |
User | Count |
---|---|
136 | |
118 | |
101 | |
71 | |
61 |