How to segregate data model from reports using shared dataset

Check list of all posts

This document is intended to apply the shared dataset solution to reduce refresh time dramatically. The best approach is to make sure that we should set up a clear architecture at the beginning. To migrate existing reports to use a shared dataset does require some effort.

Problems: Refresh too many datasets

When publishing reports to Power services, we will automatically introduce a dataset and reports in the workspace. If there are different versions of reports published, then there will be different reports and datasets in the workspace. it will end up as below

Based on the diagram above, We need to refresh 6 datasets.

Solution Step 1: Separate reports from the dataset

This step aims to make sure that reports with different versions will use the same dataset.

Detail steps to proceed:

Step 1: Get shared asset ReportX 

1. Open report ReportX v2 from power BI desktop, remove all report parts, rename it as ReportX, and then publish it.

Step 2: Convert ReportX  v2 with shared dataset ReportX 

2. Open report ReportX v2 from power BI desktop, click "transform Data"

3. Delete all queries, and then click "Close and Apply", reports show not working status

4. Click "Power BI dataset", point to the workspace, select ReportX, (you may experience that you are not allowed to add Power BI dataset. the solution is to remove all tables from model and data view. ) 

5. Save ReportX v2, and publish; now this report is using shared dataset ReportX

This interim step will make sure that different versions of reports will use the same shared datasets

Solution Step 2: Make all reports in the same workspace share the single dataset

This step aims to make sure that all reports in the same workspace will use the same dataset.

This is purely based on business requirements. If all these reports use the same dataset, then we can further consolidate all these reports to point to the same dataset. We can follow the same solution from the last step to convert all reports against the same dataset. If not working, then we need to adjust both reports and the shared dataset. We could even leverage Power BI report template to speed up such changes.

Summary

 We can make the dataset shared to all other workspaces; however, we need to make sure that dataset permission will be correctly set so that users from different workspaces can access these shared datasets.

Besides, this solution does provide a consistent model and reduce business logic maintenance and refresh time. But we need to make sure we need to do the regression test each time. 

Note:

1. We do not need to detach and clean up the shared dataset report. Systems refresh datasets only rather than reports. Both shared dataset and report will override each time when publishing. It doesn't have any impact on the capacity over time at all.
2. We can even use shared datasets from other workspaces, which is not applied for now. For example,  we can build individual reports from centralized datasets.
3. We can add new measures using the shared dataset, but it might not be the best practice. Of course, we can't change the shared dataset itself.