Combining Datasets Hosted in Multiple Premium Workspaces

[stepanov-a-o]

Hello, Power BI community!

I need your help. The task I have at hand is to consolidate business metrics from multiple Power BI reports developed and maintained by different teams, which are hosted in various premium workspaces.

Initially, I considered using a Power BI App for this purpose. However, this approach would require all reports and datasets to be in a single workspace, which is not feasible and challenging to achieve since each team prefers to keep their reports in their own workspace.

Another option I'm exploring is to use a composite model. I have conducted tests, and it works well with small datasets. In my experiment, I successfully combined six datasets into a single report, with each dataset being less than 100 MB. However, my concern is that a composite model may lead to performance issues in the mid to long term, especially as both the datasets and the user base grow. I project that the total dataset size (the sum of all datasets from all reports) could potentially reach 3-4 GB.

Is there anyone in the community who has experience using a composite model for production reports with large datasets (1+ GB)? If so, could you kindly share your experiences?

Alternatively, can anyone recommend a more effective approach for consolidating metrics from different datasets hosted in multiple premium workspaces?

Thank you!

[Brunner_BI]

I am currently working with a composite model with a couple of hundred MBs but have never had anything above 1GB. Cant you just test it out and see how slow it will be?

Alternatively, I am thinking maybe you can use some tools to combine your datasets.

Do you have reports connected to the datasets currently or do you just want to load everything? I would recommend only keeping things in the dataset that you are actually using somewhere in a report which will also cut down the size.

[stepanov-a-o]

Hi @Brunner_BI ! Thanks for your reply and suggestions. During my previous tests, I noticed it takes longer to render some visuals where data come from another dataset (with a direct query mode). 

I learned I could copy reports from other workspaces (need to have build permission) to my project workspace (without duplicating datasets) and include them in my app. There are multiple limitations to this approach. For example, I can only edit visuals of the copied report in the service, and there is no way to edit a copied report on the PBI desktop. However, this approach will save me a lot of time, and it is sufficient, at least for the first iteration of my project.

[amitchandak]

@stepanov-a-o , In Premium the dataset limit is not 1 GB. So you can have multiple datasets for more than 1 GB 

If you have transformations for fact; you can consider dataflows, to have transformations at one place.

But you need to have multiple datasets, as per need.

Mastering Microsoft Fabric 35+ Videos: https://www.youtube.com/watch?v=p-v0I5S-ybs&list=PLPaNVDMhUXGYU97pdqwoaociLdwyDRn39&index=1

Microsoft Fabric, which under public preview can avoid such copies

[stepanov-a-o]

Thank you, @amitchandak for your quick reply and suggestion! I have read about Microsoft Fabric and tested it in action with a trial account. However, (as you mentioned) Fabric is still in preview, and my organisation has not yet decided whether we will use Fabric or just continue using Power BI.

When you say Fabric "avoids such copies", are you referring to a OneLake Direct Lake model?