Forum Discussion
How to combine multiple existing Power BI datasets
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
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.
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?
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.