Combining secondary datasets with large datasets

[PetyrBaelish]

I have a user with a large dataset - over 10GB of data. As this breached size limits in Power BI for a single dataset, we have created a tabular model which Power BI connects to in live connection mode, and the report is published to Power BI Service.

The user now wants to combine their own datasets with this data - which can't be done. They want to do this in a self service fashion going forward, wihtout relying on IT to produce different versions of the tabular model every time they have a new dataset they wish to combine.

Is there any way this can be facilitated in Power BI - to have a large (over 10GB) dataset and combine this with other datasets (Excel, Access databases for example) on a self service basis?

The user is considering Tablaue, which doesn't have this limit, but I'd rather keep things to Power BI for consistency of reporting across our organisation.

[v-xicai]

Hi @PetyrBaelish ,

From the Microsoft official document, we know Composite models allows a report to have multiple data connections, including DirectQuery connections or Import, but not supports Connect live mode.

So in your scenario, if you connect to Tabular model using Import mode, it is supported to create composite model to connect to other data sources referred above.

While note that Power BI Pro or Power BI Free supports uploading Power BI Desktop (.PBIX) files that are up to 1 GB in size. Power BI Premium supports uploading Power BI Desktop (.PBIX) files that are up to 10 GB in size. If you need to upload PBIX file that is larger than 10 GB, consider to move data from your source to SQL Server, Oracle or SSAS, then use DirectQuery/Live connection.

As a workaround, you may use the dataflow in Power BI Service to connect to the tabular model using connect live mode and other data sources referred above, then you can create reports in Power BI Service. You may need install and configure enterprise gateway for the dataflow.

Note: Dataflow capabilities are available for Pro and Premium licenses. To access dataflow premium features, dataflows must first be enabled for the relevant capacity. See more Configure and manage capacities in Power BI Premium.

Best Regards,

Amy

Community Support Team _ Amy

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

[PetyrBaelish]

Thanks for the responses  @Greg_Deckler and @v-xicai.

Considering your suggestions:

Composite Models: 

We have tried this one before - but there are DAX limitations compared to what can be done in a Tabular Model and also performance issues of using DirectQuery. I also receive a warning in Query Editor that "this step results in a query that is not supported in DirectQuery mode" on the Source step in Applied Steps. I'm assuming this is because there's too much data.

Tabular Mode in Import Mode:

The data is too big - I get the error message "There's not enough memory to complete this operation"

I'm exploring data flows, but I can see how to connect to tabular models. I tried following the guidance at the link below but was unsuccessful (I couldn't open the advanced editor on a report using a tabular model live connection)

https://docs.microsoft.com/en-us/power-bi/service-dataflows-data-sources

Could you expand on your final suggestion please - what steps do I need to follow to use a dataflow to connect to a tabular model in live conneciton mode?

[Greg_Deckler]

Composite data models are supported in Power BI:

https://radacad.com/composite-model-directquery-and-import-data-combined-evolution-begins-in-power-b...