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
Hi DataInsights
Just wondering if you've had any thoughts based on my response above perhaps?
I've had the concept of Template files shared with me elsewhere, but am not 100% convinced this will do what I need.
😞
- DataInsights
Anonymous,
Regarding your statement below:
"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."
This sounds like a use case for a golden dataset (the one IT prepared with 4 tables), and multiple pbix files based on the golden dataset (with additional tables and measures, resulting in a composite model). Would you be able to draw up a simple diagram illustrating the structure and why the golden datset/composite model approach won't work?
Also, you mentioned not having the full use of DAX. That's a limitation of DirectQuery, but there may be some workarounds we can explore.
Thanks DataInsights 🙂
Here's a view of the current architecture;
The BI reports are connecting to the Ops Mart, but are too constrained for my purposes, e.g. cannot connect to additional sources, write custom queries or DAX, hence why the Analysis reports have been set up.
The trouble is, the SQL queries against the mart all sit in these reports, meaning any changes have to be replicated to each individual report.
It sounds like the Golden Dataset approach is what might work for us but where is this built; in PBI itself or elsewhere?
Just to check my understanding here; as new composite elements are built from the mart, this could be done using SQL queries in the Golden Dataset. As long as these changes do not affect the existing structure and content of the tables, the new elements would simply appear in the downstream reports - is that right? I'm a little concerned that any DAX or M queries in the downstream reports might 'break' if the Golden Dataset changes key components in these. (hopefully that makes sense in my noddy language! lol)
Edit: Just found this resource, which seems to explain it really well. https://exceleratorbi.com.au/new-power-bi-reports-golden-dataset/
N.B. I've changed from DirectQuery to Import, which has opened up the full use of DAX and seems to be working fine.
Thanks again for your continued assistance with this. It's very much appreciated. 🙂
- DataInsights
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).