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.
How to do it?
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.
Let’s see how it looks in practice
1. I already have the first report – Time Reporting Main. Here is the data model:
2. I publish it to a previously prepared workspace.
We assume that the report is already finished.
3. We are going to create a second report. At the beginning, we will connect to the data. To do this, click Get Data and select the Power BI Service connector from the Online Service tab.
4. We choose a workspace and a dataset from which we want to get data.
5. The data has been loaded. We have access to the measure from the previous report, but we cannot modify it directly, only pack it in another one measure. We cannot retrieve data from additional sources and modify existing ones. It is also blocked to create new or modify existing columns (sorting, formatting, etc.). We cannot directly see data or see a diagram of tables and relationships.
6. Let’s try to add a measure to the child report (Total Time).
7. Then, publish the report in the same workspace. Let’s refresh the first report with the data model. As we can see, a new measure at this level is not available.
8. Let’s try to do the opposite. Let’s add a measure in the first report.
9. Publish it and refresh the second report. A new measure (Users Count) is available.
10. Finally, let’s see that there is one data set and two reports associated with this data set in our workspace in Power BI Service.
Advantages of the solution
- once created data set can be used repeatedly
- managing the data model in one place
- modification of the main model is propagated to all connected reports
- security – defining a data model at the main level allows you to restrict access to the database to specific tables or views, files or just the data which is filtered. The child report developers have a predefined data model and data that they cannot modify. Everything is under the control of the main data model
- global measures – measures created in the main report will be available in child reports
- the child report may contain measures on a given topic, such measures will not be available in other reports, in other words: new measures will not be added to the global dataset, it is only related to that specific report, „local measure“
- clean and transparent data model
- no chaos of pbix files caused by subsequent copies
- no additional costs
As we are connecting to the model using live connection, there are couple disadvantages
- the existing model cannot be modified from the child report – the Edit Queries button is blocked
- additional data sources cannot be added from the child report – Get Data button is blocked
- the above point also shows that from the level of the child report you cannot group new measures by a new group (empty query), but you can prepare groups in the main model
- you cannot add new columns or modify existing ones from the child report level
- child reports can be published only within the workspace in which the main model report was published
- to modify the data model, you need to access to the report with the data model
- the data model must be universal for all reports
- workspace members who can view Power BI content cannot connect to the dataset
- a workspace member must at least be able to edit the Power BI content to connect to the dataset
- data export option (tenant configuration) must be enabled
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:
- reference to the main data model, this model could not be modeled from this level (its global modifications would still be automatically propagated to this report)
- and additional tables representing data from excel files that can be modeled and combined by relations to tables from the main model
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:
- we already have a ready data model and we want to create other reports from it
- or we know that we will create several reports based on the one data model
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.