Hello community
I wonder how one could set up a Power BI "process" (end-to-end) for 20 different clients and manage them efficiently. Especially when it comes to changes after the initial development.
I have not found an efficient way therefore I ask the community for help and opinions. Any suggestion is greatly appreciated and I am thankful for any help.
I will be pleased to explain my cryptic question in more detail in the next lines.
My goal is to create a set of Power BI reports for 20 different clients (= 20 different datasets with reports).
I get the data of these 20 clients from one DWH and they are all subject to the same data model. Each client has its own customers etc. In DWH there are no client-specific tables, but only generic ones, in which there is always a field "ClientID", so that each client and his respective data can be filtered precisely. For example, there is one product table, which contains all products of all clients and via the ClientID field, the products of a specific client can be filtered.
Based on that, I loaded all tables from DWH via DataFlow and included parameter "ClientID" to each table. I then connected DataFlow to PBI Desktop and built the datamodel and created general KPI's and reports which are valid for every client. The relations between the tables are made via combined keys, which among other things also takes the field ClientID into account.
This PBIX-file became my masterfile and with this and the ClientID-parameter I am able to publish very fast for all clients their respective report - So far so good, but...
When it comes to changes the whole thing becomes quite painful as each client comes with additional and individual KPI's, reports and excel files to be added to the dataset ("Self-Service" must be possible). How to manage such a thing?
Until the changes are made, there are exactly 20 datasets and reports that are identical in structure and form. But once changed, I will have 20 different ones and each of them have to be managed individually.
I would prefer to make all changes only in my master file and then publish it again 20 times. At least the data model would remain consistent. But that is not optimal, as I could then overwrite changes made by the client himself: The clients could have integrated their own files, key figures or reports and republished them. These changes would then be overwritten when I republish my master file and they would have to re-create them.
The other way would be to change each of the 20 published datasets individually, which would result in a lot of work and possibly many errors. But this is not practical anyways, because in the DWH, new data topics, tables or fields are often being added that affect all clients and which then also have to be integrated into Power BI model. As a result, the same repetetive work would have to be done again 20 times so that all data models are at the same level.
But what is the best way then? I don't know which path to take. Both are more or less not really of any use, as they would either mean repetitive work on developer side or customer side.
It is really annoying that the data model is part of the PBIX file. It should be separated in another layer between data source and Dataset, so that the basic model is not affected by changes.
I have read that these problems can be solved with the new datamart feature in premium. However, this feature is full of bugs and still not stable yet, as it is still under development.
What I would like to ask you is,
(1) Is there a more efficient way than the one I chose or do you just "live" with the same problems I have?
(2) How would you set up a process for a situation like this given the current PBI version/features?
(3) Is anyone already working with the Datamart feature professionally for applications on enterprise level?
I am grateful for any advice, as the whole thing will soon become unmanageable.
Best regards
Zinan
