Probably not once you met with the situation in which you had to create a simple, ordinary report based on some data. Me too. Recently, I had to create a report showing the effectiveness of employees using the data collected by the dedicated tool. Nothing hard. Standard procedure. I create a new report, connect to data, model data, create measures, display data. Done!
Then I got another task. Another report showing the progress of work in projects. I need data. The ones used in the first report will be great.
I create a new report, download data, model data, create measures, display data ... no, no, no! A lot of unnecessary work and wasted time. There must be a better solution.
The easiest way would be to add additional pages to an existing report. Less work to do. However, a lot of pages are already there. Later switching between them will be very uncomfortable. After that there are also a lot of measures that we will not be used, but there are also a few that may be useful. What if I am asked to create another report using the same data set? This will be one big chaos in one pbix file.
So, maybe I make a copy of the existing pbix. I change the name, remove what is unnecessary: existing visualizations and unnecessary measures. The report will contain only the things you need. It looks promising. However, what if I want to modify the model or add a measure needed in both reports? I need to update both files. What if they ask me about another report using these data .... Another file to update. It will be terrible.
Is there a golden mean? Well, there is an interesting solution that will solve several problems. It also has several disadvantages. I came across them on the Guy in a cube channel. This also prompted me to write this post.
The solution takes account in using the Power BI Service connector. This allows you to use a single, existing data model in different reports. The solution has been for over a year. Some people still do not know or forget about it, creating next similar or identical data sets for each report.
First of all, we need to prepare a main report (parent) that will contain the main model. This report may contain only the data model (for model management) or additionally data visualizations (I would say mostly for debugging). The decision depends on you.
Secondly, we publish this report in the Power BI Service.
Thirdly, all child reports must retrieve the data from the previously published report (our data model) via the Power BI Service connector.
We assume that the report is already finished.
What disturbs me the most is the fact that the model in children reports is fixed because it is only a reference to the main data model. Sometimes there is a small need to extend the model. The ideal solution would be if you could, from the child's report level, use the Power BI Service connector, connect to the main data model and then add some other source to this report, eg excel files. As a result, an additional new data set would be created that would contain:
The administrator would determine if the user can extend the main data model to other data sources or not. A bit complicated, but useful when we want to extend the existing model by some additional data (e.g. an additional excel file) in only one report, and the main structure of the data model remains the same. Unfortunately, this is now not possible.
On the other hand, the application of the solution with the current limitations allows to fully avoid the creation of data silos, which is a very good practice.
Some disadvantages might be actually advantages at the same time. It all depends on the situation and what we expect.
This solution is good in situations when we work in one workspace and:
For more complicated situations, for example when you need to work in many workspace and one centralized data model, you can use Azure Analysis Services and Power BI Rebind API.
Decide for yourself whether in your case it is the best solution. Remember, the data model must be universal for all reports. If you have not found a solution for yourself, follow us. Soon there will be more posts with solutions on our blog regarding the problem of several reports and one set of data.
Preventing Data Silos with Power BI
Connect to datasets in the Power BI service from Power BI Desktop