Multiple tablular models

[xmark]

I am currently desiging a DW architecture and trying to understand the optimal way to fit Power BI into the reporting and analytics side of the architecture.

Right now the plan is to use SSAS in tablular mode to create models ready for Power BI consumption. However, it appears that Power BI can only conenct to one tablular model at a time...

What is generally best practice in the situation where you have varying data from different business units (finance, HR, memberships, etc.) with respect to creating the tablular models? One big one, or many small ones?

Obviously, if we have many small models, I cannot connect Power BI to many of them at once (as stated above), but one large one means lots of potentially useless data in a single model which could also take a long time to refresh/process.

[Anonymous]

I think this is more related to the design strategy you choose.

If you are creating Tabular model from a data warehouse, you can create your data model by incorporating data from many business units like HR, Finance, operations etc. Live connection means Power BI mainly using as a front-end for data visualisation and all modelling and logics sitting in tabular models.

You can use features like drill through and drill across for more efficient reports

[v-yuezhe-msft]

@xmark，

If you choose “Connect to Live” mode in Power BI Desktop to connect to SSAS tabular, you are only able to connect to one model in one Power BI Desktop file. And in this mode, there are some limitations, for example, we are not able to manage relationships, create table/columns using DAX, etc.

However, if you use import mode instead, you are able to connect to multiple tabular models in one Power BI Desktop file. You can create small tabular models and use import mode to connect to there models in Power BI Desktop.

Regards,
Lydia

[xmark]

Hy Lydia,

Thanks for the reply. So my understanding is correct then... If I conntect using Import mode, I will be downloading lots of data which also has a large downside. If I connect to my tabular models within the Power BI website (via the gateway we have running), can I use multiple models in a single report? Or still limited to the 1 tabular data source?

[v-yuezhe-msft]

@xmark,

You can have multiple models in the single report and the report will work in Power BI Service.

But please note that if you don't have Power BI premium license, the size of PBIX file and data model in the dataset will be limitted to 1GB. If your dataset exceeds the limitaion, you will get error message when publishing the PBIX file to Power BI Service and refreshing it in Power BI Service.

Regards,
Lydia

[xmark]

Thanks @v-yuezhe-msft 

In order to perform some capacity planning, how exactly would one go about testing the 'size' of a model? That 1GB limit feels incredibly small, my client currently has Pro, does that still have the 1GB limit?

[v-yuezhe-msft]

@xmark,

You can rename the PBIX file or Excel file to .zip and check the size of the data model file within. For more details, please review this blog.

Pro users still have the 1GB limit.


Regards,
Lydia

[xmark]

hi @v-yuezhe-msft thanks for the reply, sorry for my slow response.

I think I may have confused you, I wasnt talking about an excel workbook. I am specifically talking about a SSAS tabluar model (or multiple models).

If my tabular model, which reflects the entire company's data footprint, exceeds 1GB what are the options, (other than upgrade to Premium)? Can we request a size limit increase?

[v-yuezhe-msft]

@xmark,

When you choose "Import" mode in Power BI Desktop to connect to your tabular models, the PBIX file has 1GB limitation once you publish the report to Power BI Service.

If you don't want to purchase Premium, use "Connect live" mode instead to connect to your tabular mode, in  this case. the PBIX file you publish to Power BI Service don't have 1GB limitation.

Regards,
Lydia

[Anonymous]

Hi @v-yuezhe-msft, @xmark and others,

We are having just this issue at the moment, so any comments will be really helpful.

We are working with PBIRS On-Prem, and have a number of medium sized SSAS Tab models we would like to surface measures from on a single PBIX report. Actually we are using this single PBIX report as a kind of PBI dashboard, bringing together measures from a number of lower level, more detailed anaysis. But as On-Prem does not support PBI Dashboards we are using a report to try and simulate the effect.

Following the advice that if we IMPORT the multiple SSAS Tab models we can then work with them in one PBIX we went ahead and imported...... we won't be doing that again, it crached the server we were running on. The server crashed because the memory useage went to 99% and SSAS / PBI wanted more. It seems that importing the SSAS Tab model is a very resource intensive process.

So we are back to the original question, one large SSAS Tab model or multiple small ones. Looks like we are going to have to go down the one large model. Unless someone has any ideas?

All the best

Rob

[okusai3000]

Hi,

In our company we are facing the same kind of problem... one mega big SSAS model or several small ones? So far, this hasn't been an issue, as every department uses their own data, but our Board of Directors needs some KPIs from the mix of different sources, so we have to figure it out how to mix it.... 

The easy way is just a big SSAS Model, but I'm afraid of memory limitations...

Any ideas?

thanks!