Power BI – Best Practices for Metadata Modeling using Composite Models

/by

Microsoft recently released one of the most sought after features for Power BI. As of the December 2020 update, there is a new preview feature that allows you to build a partitioned metadata model in Power BI. Formally called “DirectQuery for Power BI datasets and Analysis Services”, this is essentially just an expansion of the Composite Model feature introduced in 2018. Previously, Composite Models did not support live connections to published Power BI datasets and Azure Analysis Services, but that is no longer the case with the latest enhancement.

So what is “DirectQuery for Power BI datasets and Analysis Services”? With this feature, Power BI users can now build an enterprise semantic Power BI metadata model for sharing (and enhancement!) across their organization. Starting with this “master” model, individual departments can quickly and flexibly extend and enhance the model as business needs dictate.  Updates made to the underlying master model are seamlessly inherited without impacting business continuity.

For example, IT creates a Product Master or Customer Master model in PowerBI.com as a dataset. Later, individual departments can then import and enrich the enterprise model to meet their own needs. If IT decides to update the Master model, all the dependent data set models will automatically inherit the updates.

The release of this functionality greatly enhances an organization’s ability to support and drive governance and master data efforts by allowing the promotion of a “single source of the truth” through Power BI.

So what are the details of this new feature? How does it really work?

  • Begin by publishing your enterprise Power BI dataset to powerbi.com.  An example might be a Customer Master dataset. 
  • Next, in Power BI desktop, enable the new preview feature.
  • You can now connect live to the existing dataset publish in Power BI Services.
  • Just like any other data source in Power BI desktop, you can extend the model by connecting to more data sources (e.g. an Excel file or a SQL database) and combine them into one semantic model, while still maintaining the relationship to the original enterprise model. If the enterprise model’s data is updated, then the newly created composite model will also see the new data.
  • A composite model does require you to have a local model. By default, if your PBIX model only has a live connection to your source data (like AAS) then you will not have a local metadata model in your PBIX file. If you start from a live connection to a data set then you’ll need to convert to a DirectQuery connection before you can import additional datasets. This will happen automatically once you import another data source. A local model is needed to “extend” the metadata model to include any new data source like Excel or MS SQL.
  • You can now extend and enhance your model by linking together additional data sources.  Important to understand is that extending beyond a “chain” length of 3 is not currently supported. Effectively this means that one cannot extend a data model that has already been extended. For example, if you publish an extended model to Power BI Service then another user can not further extend the model with additional data.

Additional suggestions to be aware of:

  1. If row level security is defined in the master model, it will be available in the extended model but it will not automatically apply to any newly imported data sources. You’ll need to define row level security on any data sources that you import into your composite model.
  2. Display folders, KPIs, date tables, row level security, and translations will not be imported from the source in this preview release. You can still create display folders in the local model. This maybe resolved before General Availability.
  3. You may see some unexpected behavior when using a date hierarchy. To resolve this issue, use a date column instead. After adding a date hierarchy to a visual, you can switch to a date column by clicking on the down arrow in the field name, and then clicking on the name of that field instead of using Date Hierarchy:
  4. Using on premise SQL Server Analysis Services (SSAS) as a DirectQuery source is not currently supported but Azure Analysis Services (AAS) is supported.

 “DirectQuery for Power BI datasets and Analysis Services” preview feature is a major step toward creating centralized and standardized metadata for any organization. There is no need to waste time remodeling and maintaining master data for each and every Power BI model. You can now simply extend your semantic models by leveraging a standard set of master metadata that is maintained in a central Power BI dataset. You can think of this new preview feature as Composite Models 2.0.