Power BI Composite Models: True Reporting Agility

Using DirectQuery to blend self-service and corporate BI

Power BI composite models is a game changer, fundamentally changing how organizations will merge self-service and corporate business intelligence. Authors can bring in their own local data sources while still leveraging the authoritative Power BI or Azure Analysis Services datasets created by others in their organization.

When Microsoft released composite models in 2018, the moment marked the biggest technological breakthrough since Power BI first hit the market. Composite models essentially introduced data blending to Power BI, leveling out the competitive playing field with Tableau. With composite models, you could combine imported data and/or multiple DirectQuery sources together for analysis.

But something was missing: you could not DirectQuery multiple Power BI/Azure Analysis Services (AAS) datasets from a single  model.

Typically Power BI data models have data from multiple sources. The data is loaded into the model by importing or DirectQuery. The import makes a copy of the data and loads the data into the Power BI file. With DirectQuery, the data remains in the data source, but sends queries to retrieve data on-demand.

Prior to the 2020 release of composite models, in order to combine data from corporate, published Power BI datasets, you had to download the entire Power BI dataset to a local file. Let’s say you wanted to create a Power BI report combining Google Analytics data with corporate BI data, like your sales data. To do so, you’d have to export the sales dataset to Power BI before you could join it to the Google Analytics data.

That repetitive manual effort is the very antithesis of what we think of when we say agile BI. Plus, the option of duplicating your entire database into a Power BI dataset would be highly impractical due to small size limits or high costs for Premium capacity.

Composite models for 2021 brings real agility

At the end of 2020, Microsoft mended this functionality oversight. You can now use DirectQuery to connect to Power BI datasets and combine them with other data sources and/or imported data or combine multiple Power BI/AS/AAS models.

Image from Microsoft

Centralized datasets such as Azure Analysis Services models or Power BI shared datasets are often used to build reports. Now report authors can connect to these datasets and use composite models to either extend them with their own measures or combine them with other imported data (for example, Excel files). In this way, organizations can scale their enterprise datasets by allowing users to use them in more self-service scenarios.

This new functionality results in a series of cascading benefits that add up to big implications for how organizations will merge self-service and corporate business intelligence.

1. Reduces the number of copies of data (data redundancy).
2. Stretches your Power BI Premium investment by reducing your data footprint.
3. Increases consistency, you’re pointing to a single centrally controlled dataset, which gets you closer to a single source of truth.
4. Enables true self-service on top of an enterprise dataset.

Practical scenarios for applying composite models

1. 1. DirectQuery Power BI/AAS + Import
      Example connections: Power BI dataset + imported Excel data.
      Use case: Report authors want to view team or departmental data to augment the organizational model. They import team or departmental metadata stored in Excel and model it with the organizational model.
      Old way of doing it:
      • • Export Power BI data into Excel and modify (manual intervention).
        • Import Power BI data into a new model and import the Excel data alongside (duplication of effort and data).

       

       

   2. DirectQuery Power BI/AAS + DirectQuery Relational Database
      Example connections: Power BI/AAS dataset + SQL.
      Use case: Report authors want to view team or departmental data to augment the organizational model. They import team or departmental metadata stored in a SQL database and model it with the organizational model.Old way of doing it:
      • • Export Power BI data into Excel and modify (manual intervention).
        • Import Power BI data into a new model and import the Excel data alongside (duplication of effort and data).
        • Build second model (duplication of effort and data).

       

       

   3. Multiple DirectQuery Power BI/AAS
      Example connections: Power BI dataset + Power BI dataset.
      Use case: An organization with department-specific models with desire for cross-department KPI(s) on a single report.
      Old way of doing it: Build second model (duplication of effort and data).

 

1. 1. Multiple DirectQuery Power BI/AAS + Import
      Example connections: Power BI dataset + Power BI dataset + imported Excel data. Use case: An organization with department-specific models with desire for extra team/department augmented data.
      Old way of doing it: Build a second model (duplication of effort and data).

 

1. 1. Different refresh schedules
      Example: Model A refreshed daily, model B refreshed weekly, composite model connecting the two.
      Use case: Avoiding redundant refreshes.
      Old way of doing it: Both in single model refreshed daily (or Power BI Premium = XMLA refresh dependency).

 

Considerations when building Power BI composite models

Take these considerations into account when building composite models.

1. Dataset structure dependency
   Your composite model is dependent on all underlying datasets. If the underlying dataset is altered, your model will be affected as well.
2. Dataset timing dependency
   Your composite model is dependent on all underlying datasets. If the underlying dataset is imported and the refresh schedule is altered, you may need to make adjustments to your schedule as well.
3. Naming conflicts
   If there are table naming conflicts between two DirectQuery models, Power BI will suffix the conflicting source table with sequential numbers. This may break measures in the conflicting dataset. If measure naming conflicts exist between two DirectQuery models, you will not be able to add the subsequent dataset with the naming conflict.
4. Table naming conventions
   There is no easy method to see what dataset each table is sourced from. You can rename tables in the local model when building the composite model.
5. Dimension attributes
   If one model’s dimension is filtered in a manner specific to that model, it may conflict with the secondary data when architecting the composite model. Keep this in mind in creating the common dimensions between the models.

Conclusion

Composite models is a huge milestone in the Power BI ecosystem. By reducing redundancy and allowing authors to reuse and customize datasets, it will empower self-service users and significantly alter how datasets are built and used in your environment.

Allowing for self-service data modeling is an important part of a successful BI, but it needs to ride on top of excellent enterprise level data models and data architecture. See this chart for a comparison of the capabilities of five Microsoft data modeling and transformation tools.

High-quality data organized for practical business use, in combination with good governance practices, are essential to success in modern BI and the health of a data-driven organization. Senturus offers consulting and training to help you at any point in your Power BI and analytics journey.