Forum Discussion
Dynamic Report Data Source
For those who are looking to do the same thing. I think I have figured it out.
I'm using multiple Dataflow Gen2s (one for each database) using the dataflow, I'm adding new columns for the primary and foreign keys (<database name>_<key name>). I connect all of the Dataflows to 1 Warehouse and store everything in the same tables. I've set one dataflow on 'replace' to clear the warehouse database with the new data. All the other databases are set to 'append' to add the data.
Then I've created a pipeline that first runs the dataflow with the replace and when that is successful, it runs the dataflows with append.
I also created a seperate Semantic Model from the warehouse to make use of the roles feature. The wareshouses default models doens't allow you to. There I created 2 roles. One called 'Full access' with no limitations and one called 'Limited Access' with the DAX query '[Email] == USERPRINCIPALNAME()' on the table where I store my users emails. This will make sure the user only has access to the data connected to the logged in user. (Make sure to create the relations on the new model, otherwise it won't work.)
Super UserThis is possible if you combine all the 20+ client data into 1 semantic model, and then apply RLS on the semantic model based on the username i.e when [email protected] logs in, A will only see CompanyA data.
https://radacad.com/dynamic-row-level-security-with-power-bi-made-simple
Thank you for your anwer. We are using integers as primary keys. Wouldn't this cause collisions if you merged everything into one semantic model?
How do you suggest we handle this? Looking forward to your thoughts.
- Tutu_in_YYC
A surrogate key can be created in Power Query. But the solution that you came up with good too!