How Companies can use Composite Models in Power BI as a Game-Changer for Self-Service Business Intelligence

With Composite Models, Microsoft significantly expands the application spectrum of Power BI for business departments in companies. At the same time, a very powerful instrument for self-service BI is created for the central business intelligence and IT departments, in order to provide the departments with new freedoms and capabilities while taking their governance guidelines into account. The article is therefore aimed at both responsible and advanced users of Power BI Desktop in Controlling as well as those responsible in central IT or Business Intelligence. Used correctly, composite models open up a new world for self-service BI.

The use case

Every Microsoft Power BI report developer or "power user" is familiar with the following situation: A new Power BI dashboard or report is to be created based on a Power BI dataset provided centrally by BI or IT departments. In the best case scenario, this dataset is already optimally suited for the report question at hand and, in addition to the required data in the appropriate granularity, also contains centrally secured and coordinated logics for the key figures to be evaluated. Nevertheless, it often happens that a few additional pieces of information are missing in the dataset for the requested report. There are many conceivable use cases for such small extensions to a central dataset, which are required at short notice for certain reports and ad-hoc analyses and may even be needed only temporarily:

• Individual groupings of customers, products, etc. in the form of calculated columns in the corresponding dimension tables of the central model or as an extension via additional tables that can be related to specific keys of an existing dimension table of the central model.
• Plan and forecast values from planning tools are to be imported and related to the central dimensions and thus to the actual values from the central model.
• Benchmark values from the internet, web service based sources, etc.
• Import of individually created threshold values that can be used in DAX logics for ABC, Pareto analyses, etc.
• ...

As a power user, however, one has no authorization to extend the central dataset due to data governance aspects. Although the required extension might even be relatively easy to implement (e.g. as a simple, additional calculated column in an existing table for an additional aggregation or as an additional fact table for benchmark values), this is not included (fast enough) in the central dataset for certain reasons. Examples of such reasons are:

• In the short term, IT staff or IT specialists do not have spare capacity to extend the central dataset.
• The extension would be irrelevant for most other dataset users, would only confuse them and jeopardize the acceptance of the central dataset.
• The extension is only temporarily relevant for a few ad-hoc analyses and becomes obsolete again after some time. It would then either have to be removed again with additional effort or would remain as a "corpse" in the analysis model.
• According to the IT experts, the enhancement must be installed "cleanly" and this would take several days/weeks, including the work in the central data warehouse. Again, this creates a conflict regarding priorities and resource availability in the central BI department.
• ...

This situation creates a big dilemma for the power user. One possible solution to the problem would be to agree with the requester of the report on a more limited scope/content without the necessary enhancement. If this - which is the rule - is not a real option, the power user has to create his own database in self-service with Power BI Desktop by extracting data from the central dataset or importing it into his own model, where he can relate it to the required, additional information. In addition to a significantly higher effort, this procedure has many other serious disadvantages. All conceivable aspects regarding data governance, security, data protection, IT alignment, flexibility, etc. would actually speak clearly for a temporary extension of the central model - in contrast to the now required self-service BI procedure using export/import mechanisms in Power BI Desktop or other tools. Especially if, in the worst case, Excel, Access, etc. are also used for this purpose, a shadow IT quickly flourishes in the departments instead of the desired, preferably integrated self-service business intelligence. A responsible power user is well aware of this, but in this case there is no other choice, is there? With Composite Models for Microsoft Power BI, there is an elegant solution to this dilemma...

Read Marco Nielinger's full article in nc360°, the noventum info platform:

What are Composite Models?

How do Composite Models work?

Areas of application for composite models

For more information about noventum BI consulting, please visit our website or read further articles on our info platform nc360°.