How to combine multiple existing Power BI datasets

[MattClapp]

We currently have three very granular Power BI datasets that are used to support operations in discrete business units (Endpoint Protection : Intune + AD datasources,  Education and Awareness: API + ADLS2 datasource , Access Management:  AD + ADLS2 datasources).   Each published Power BI dataset is complex and rather large.

We have the need to create a single report which uses elements from each of the three published Power BI datasets.   We know that we can’t use more than one Power BI dataset in a report.  We also know that we do not want to duplicate the modelling by trying to recombine the 6-10 source data systems to make a huge standalone model that we would then need to support and maintain.

Does anyone have experience with this type of requirement? 

What is the best practice to deliver measures from three separate Power BI datasets to a single report/dashboard?

Thanks!

[Sundaynero]

I'm not sure the proposed solution actually solves your problem. Here's something I do and I haven't read anyone else talk about this possibility but to me this was an absolute game changer. Yes, you can actually combine Power BI datasets. Here's what I do:

- You have dataset A, B and C. You have created those for example in your desktop power bi, wether you build them in storage mode "Import" or "Direct Query". You upload those datasets into a Workspace in PBI service.

- You create a new dataset in the desktop. I call this a "hub" dataset: you connect with dataset A, usually this enables the "live mode" storage mode. Then you connect to another dataset and automatically the connection/storage mode is switched to Direct Query. In order to be able to connect to a second dataset you may need to enable a preview feature in settings in your desktop power bi ( I just checked and I couldn't find which was it, so it's probably no longer a preview feature you need to enable or its located somewhere else in the options menu). You can add as many datasets as you like, each one will be represented in the blocks diagram view in a different color.

- In order to create the connections between the datasets you need to be aware of which dimension tables will be governing the whole dataset to avoid chaos and ambiguity. For example you can connect the dates table of the 2 (or many) datasets or also the business unit dimension table, etc. (update: There is more explained about how this should be done on my next replyin this conversation).

- measures: measures of each dataset will be imported when you connect to the dataset. However I recommed building measures in the Hub dataset, it will allow you to group the measures in folders and don't have to go back to datasets A, B, C for measures definition etc.

- all you have to do now is upload the new "hub" dataset into a power bi service workspace. Now you can connect to that single dataset in live mode from your PBI desktop.

- Yes, in order to update the "hub" dataset you will have to update its "sub"datasets A, B and C. Hub datasets usually take little time to be refreshed from Power BI service as all the source datasets are already in the cloud.

Hope this works for you.

[brookbracewell3]

I am trying to work out is this functionaility has a name. It looks like a super powerful feature- but not shouted about. Any thoughts on risks and considerations if using in " in anger"

OUr use case is simple. I have large fact tables which will be applied to each domain model, and I also have a massive customer dimension (lucky me) 

So having a model for customer, and a model for each domain allows me to toad the customer once and reuse. It also allows me to combine domains to compare "stuff"

This functionailty is super- but what is the risk?

[Sundaynero]

I have been using this multi-dataset functionality for a couple years and it has always worked as it was suposed to.

I would say the only drawback or risk factor involved is the inherent complexity of enlarging your dataset with more tables and measures by adding more datasets to it: You have to be very consistent on which dimensions/tables of which particular sub-dataset in your "hub" dataset are used to filter the whole "hub" dataset. For example, you might find you have multiple date tables (one per subdataset) and any of them could play the role of filtering the other date tables, but you only want to stablish this role on one dates table (always the same), and I think its best to always define single oriented filter/directionality (even if that forces you to switch from 1to1 relationship to 1 to many, Im not sure you can have single oriented directionality on on 1 to 1 relationships) with the other dates tables so that at all times the filter flows from your dimensional/ruling dates table to the others and not the other way round, which  led to some trouble in some occasions. 

[v-eqin-msft]

Hi @MattClapp ,

Thanks for your sharing ! Could you please kindly Accept your post as the solution to make the thread closed. More people will benefit from it.😀

Best Regards,
Eyelyn Qin

[MattClapp · Accepted solution]

We are likely targeting a solution that looks like below.   Its not a strong solution as the lineage is a little messy but with the reports now in a common location,  we can move forward with delivering the solution to the intended audience

-in workspace1 create a visual-a
-in workspace2 create a visual-b
-in workspace3 create a visual-c
-'save a copy' of visual-a to workspace4
-'save a copy' of visual-b to workspace4
-'save a copy' of visual-c to workspace4
---------------------------------------------
-deliver the 3 reports in the workspace4 app as tabs on left menu
OR
-deliver the 3 reports in a dashboard within workspace4

[v-eqin-msft]

Hi @MattClapp ,

One way to work around, creating dashboard could combine different visuals from different reports. 

Please refer to:

https://www.linkedin.com/pulse/enhance-your-power-bi-skills-combine-multiple-jeroen-van-de-erve

https://sqlse rverbi.blog/2021/06/01/doing-power-bi-the-right-way-10-designing-and-managing-large-da...

https://www.youtube.com/watch?v=Vhn2SLt_kts&t=5s

Best Regards,
Eyelyn Qin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[MattClapp]

I beleive you can only combine reports within a single dashboard when they are in a common workspace.     In this scenario,  we have 3 different workspace+datasets .

I beleive this is the only option:

https://ideas.powerbi.com/ideas/idea/?ideaid=851b8e21-2d86-4406-a2b5-9e12bd379329