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.)
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.)