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
Anonymous
Not applicable

Is there a way to programmatically change the import sql of a table?

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.

9 REPLIES 9
mpo
Helper II
Helper II

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.

Anonymous
Not applicable

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.  

Anonymous
Not applicable

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

Anonymous
Not applicable

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

Anonymous
Not applicable

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

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.