Forum Discussion
Multiple reports from a single dataset in desktop
Anonymous,
That link on golden datasets is the basis for how I structure my environments. We create a golden dataset in Power BI Desktop, and publish it to a golden workspace. The golden dataset contains centralized logic that serves as a single source of truth. Then, we create two types of reports based on the golden dataset: live connection (thin reports, with no additional data sources), and composite models (additional data sources).
Super UserAnonymous,
It sounds as if a composite model is a good option for your requirements. In Power BI Desktop, connect to the master published dataset. Then, add other data sources, measures, etc. This will result in a composite model (combination of DirectQuery and Import sources).
You'll need to enable the Preview feature below in Power BI Desktop (Options):
"DirectQuery for PBI datasets and AS"
https://docs.microsoft.com/en-us/power-bi/transform-model/desktop-composite-models
Thanks DataInsights .
Yes, this is what I need in terms of my ability to connect to additional data sources, and this looks to be working OK.
Where I'm struggling specifically, is with the following;
At present, I have 4 tables in my desktop dataset. The query for each uses the same syntax, an appears to be extracting data directly from the datamart, e.g. "= Sql.Database("CRR_DWHOUSE", "Ops_Mart", [Query="SELECT ....."
Logically, if additional tables are added to the dataset from the datamart using this method, they will each require a new DirectQuery. Also, if additional fields are added to an existing table (query), the code will need to be updated.
I'm aware that we can hang multipple reports off a single dataset within the PBI service/workspace, but this doesn't permit the full use of DAX so files need to be downloaded to PBI Desktop.
I will need to create multiple reports using the same dataset, and it looks like the only way to do this in PBI desktop will be to duplicate the dataset multiple times.
If/as/when new tables/fields are aded to the dataset, it looks like I'm going to have to update the code in each query for each Desktop report. Surely there must be a more efficient method than this?!
If the SQL queries against the datamart populated a single source, I could surely just connect my queries to this single source so that any changes are automatically pulled through to each of my .pbix files.
Hope this makes sense! I'm no data engineer! lol
Thanks