In December, Microsoft released a highly desirable feature, ‘Power BI Composite Models’, for its reporting and analytics software, Power BI.

This is big news as it allows people to extend enterprise data models for unique reporting and analytics purposes whilst retaining a direct connection to the underlying model.

In Marco Russo’s article on the subject he sums it up as the following: ‘In the absence of a better solution, “download in Excel” was always the way to go. There was never an architectural solution to solve this problem once and for all. Until today.’ Our Director of Professional Services, John Gamble, expands the new feature, specifically why he thinks this is a big deal in the world of Data & Analytics.

About Composite Models

‘The Composite model, or to give it the full name Direct Query to Power BI and Analysis Services, is the ability to edit or extend an existing already published Data Model (or Cube), hosted in the Power BI Service or Azure Analysis Services, for a specific or one-off function whilst retaining the connection to the existing model. From this release of Power BI you can connect live to an existing Power BI model or Cube, build a wonderful report or dashboard, and then, and this is the crucial part, create a link in the model to a completely separate new data set and write calculations over the combined model.

This enables you to take advantage of all the processing and aggregation power provided by the existing enterprise model and then extend it locally without interfering with the Enterprise model. If you have a reporting need that the built-in functionality for the Cube you are connecting to does not allow for, then no longer would you need to resort to the “default” of exporting the data to Excel (or another tool). This can potentially result in extracting hundreds-of-thousands of rows, storing the file locally on your workstation and then adding the special calculations you need to the raw data. You can then also publish this new extended report back to the Power BI service for others to use.

Business Benefits

This feature solves lots of issues that have potentially hindered the growth of the use of data within an enterprise. The aggregation, calculation and usability advantages of Cubes and data models as used by tools like Power BI have long been recognised as a way of providing great access for business users to core company data sets (e.g., Finance, Customer Sales, etc.). A lot of time and effort can go into defining Cubes and models along with the infrastructure and data loading routines required for them.

Change as we all know, is constant and it can be time consuming to go through all the development, testing and governance required to update the enterprise reporting platform. Quite often it is not seen as worth it if it is just the addition of a couple of new calculations or perhaps the ability to correlate one data set vs. another for a one-off task. Typically, in this scenario the result is that IT gets involved and someone gets a large data extract which is usually dumped into Excel for further processing. This leads to dissatisfaction on the part of the end user as they lose all the capability inherent in the Cubes and reporting platform; and the new data sets end up being static, stored on the filesystem or worse emailed around the organisation creating a GDPR headache as data springs up literally everywhere.

A Practical Example Using Composite Models

Here is a scenario. A retailer may have a sales data set that describes each quantity and price of each individual item sold along with the attributes detailing the product, the sales channels, the store, the salesperson, the Customer, the supplier and when the sale took place. A well-designed Power BI data model, or Analysis Services Cube would enable them to slice and dice the sales volumes and amounts across all those attributes. It is not inconceivable that there could be many millions of sales records. A query such as: “show me all the Online Sales in January for Product ‘X’ vs the same period last year”, could quite easily be constructed and the Cube would do its magic and return an almost instantaneous answer, processing millions of rows of data, with no complex coding required.

Now imagine the user wants to correlate the data with say weather information, perhaps daily temperatures. If the data set for this is not in the enterprise data warehouse or reporting system, the user can download an extract – weather data is often freely available – but to correlate it directly with the sales data requires getting the data out of Cube and into another tool (usually Excel) and then performing complex formulae to align sales and weather dates. This new model is typically outside of the Data Platform, probably poorly performing depending on the data volumes involved and undoubtedly static requiring manual updating on behalf of the end user. No doubt the IT department and Data Protection Officer are also thrilled by its existence especially if it contains customer data.

With the composite model feature, Power BI addresses all these negative points and gives more power back to the end user. The user can switch their connection to the enterprise sales data set to be a “direct query”, then add in the weather data from a separate external source and link it straight into the Sales data model all within Power BI. The new model is then “live” and continues to be up to date as it is auto refreshed by the existing enterprise data load mechanisms. The core data is always retained in the enterprise data platform, the existing cubes continue to provide the core processing power. The only data on the user’s machine is the weather data; and if that is useful to others the new workbook can be published back to the Power BI Service.

Best Practice

I am sure best practice around the use of the composite model will emerge over the coming months and I do have a concern about governance and potential for one model to build on another and another and another, but for now I am happy to celebrate a great new feature in the world of Data and Analytics. Well done to Microsoft and the Power BI team on cracking this one.’

For more information about how we can support your organisation with data reporting & analytics using Power BI, email us at enquiries@c5alliance.com or call +441534 633 733.