The Enterprise Tabular model

In Microsoft BI, a tabular model is often the way to go. OLAP gets more and more often replaced by Tabular cubes and Power BI models. However, it's really hard to create a model that covers the whole enterprise. In this post I propose a solution for doing just that in a simple way.

In the OLAP times, you could create a multidimensional solution and define all measures and dimensions once and use them in multiple cubes. A finance and a human resources cube could share a single conformed dimension, say employees, without having to define that dimension twice. You would define your data model once and create cubes on top of that model.

In tabular models this is not as easy. In tabular model, the model is the cube itself. This means that if you want to have two cubes for two departments, and they both share dimensions and measures, you have to write them twice and also update them separately.

My solution for this is quite simple: create one data model cube that serves as the master data model. All conformed dimensions and all conformed measures should be defined here. All tabular cubes that need to be created for the departments must be derrived from this master cube. To do this, you need a tool called BISM Normalizer http://bism-normalizer.com/ . This tool allows you to do a schema compare between two tabular models and deploy changes from one to the other. It is really simple.

This way, you can create one single place where you write your DAX. If you need conformed dimensions, you also need to be able to edit them in a single place, not several models.

Obviously this only works for a situation that asks for one version of the truth. If you want data silo's or unique logic for each model, this might not be for you.

Principal BI consultant at Rubicon

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.

2 Comments

  1. Akthar

    Hi Jesse
    Your solution may be difficult to maintain. May take a very long time to process. This may make it difficult to work with. Therefire may not be suitable. I am looking for reducing data silos but one single model with all data sets and processing may not work. Will you counter this thought? I am in an an environment where the same data source (dimension) has to be built across multiple power bi models (report/app)

    • Jesse Gorter

      Jesse Gorter

      Hi,
      I understand where you are coming from. I updated my post to mention that you should have one model only for the conformed dimensions and measures. I do not think you should make every dimension conformed and just dump everything in one model. What do you think? All conformed logic in one model works fine for me. Obviously I would suggest to stay practical and deviate if needed.

Next ArticlePower BI release management