Following up on a previous question, since I cannot do any additional modeling when connecting to a Power BI Dataset, I'm curious as to the best way to be able to work off of a common data model.
For the sake of discussion, let's assume that I have 50 different reports in various apps published to the Power BI Service online and each of these has it's own dataset that it feeds from. Since much of the underlying data and measures are the same across multiple reports, I figured it would be better to have a handful (say 3 to 5) of distinct master data models that I can build pretty nearly all of my 50 reports off of to avoid duplication of data and code. However, each individual report may have an extra parameter table or calculated column in order to function which cannot be added to that report if it is connected to the live Power BI dataset which supplies 95% percent of the data and measures needed for that particular report.
What is the best workaround to this limitation? I can obviously keep going with every report having its own independent dataset, but I worry that isn't easily scalable and maintainable when updates and improvements need to be made to models. I guess I could also try to just add everything I need to a master model as I build new reports, but I worry that will create a master model that has way too much stuff attached to it resulting in a mess of a table diagram where most stuff is irrelevant to any single report I'm trying to build.
I'm sure I can't be the only one trying to figure out what to do. What have you guys tried? What works and what doesn't? Any advice or recommendations for how to tackle this?
Based on my understanding, you have multiple Power BI reprots connecting to similar data source, what you want to achieve is that after publishing reports to Power BI service, they share the same dataset hosted on service in order to avoid dataset redundancy. However, publishing a report to service will automatically saving a dataset on service, this is by design. Unless you connect to the existing dataset stored on Power BI service which can be shared by multiple reports. Live connecting to Power BI dataset still allows you to generate measures. Apart from this, I'm afraid there is no other workaround.
1) Dataset can be stored in one Power BI workbook and multiple Report-only Power BI workbooks can connect to the Dataset Power BI workbook using "live connection" option
See the post below for more details.
2) Microsoft whitepaper "Planning a Power BI Enterprise Deployment" also talks the design problem you highlighted. The below is what is said in it.
Therefore, it is important to separate the development of the datasets used by reports from the reports themselves wherever possible.
This can be achieved in two ways:
Use of Analysis Services
The first option is to utilize SQL Server Analysis Services to store your data. The pros and cons of this approach are discussed extensively in Section 6 of this whitepaper.
.pbix file with dataset only
The second option is to create a dataset in a .pbix file without any reports in that file. This .pbix file can then be published to Power BI, creating a new dataset in the Power BI service, and then users can create new reports in Power BI Desktop that connect to the published dataset. This creates what is called a Power BI Live Connection, and behaves in a very similar way to creating a live connection to Analysis Services – without, of course, the need for a separate server – and is more suitable for smaller workgroups. Alternatively, reports can be created directly in the Power BI Service using the web interface.