Dimensional modeling in 2018

It is quite common to update your skills in a product, Power BI for example. It is not so common to re-validate concepts or theory very often. For example "Joins are performance heavy", or "Data warehouses need surrogate keys". Usually these things don't change very often. Still it is important to update your view on these concepts as well. What is a data warehouse in 2018 is a different question than data warehouses in 2008.

One of the most interesting concepts in data platform is the dimensional model. This used to be the norm for data marts and data models that are exposed to the user. In this post I try to determine whether the dimensional model is still relevant in 2018 for Microsoft BI solutions.

Important reasons to use the dimensional model for data platforms were:

  1. Databases were slow to answer questions that needed a lot of rows, think OLTP.
  2. Joins were performance heavy, so we needed to reduce amount of tables and denormalize the model for faster queries. Dimensional modeling does just that.
  3. The dimensional model is intuitive for end users. Third normal form is not.
  4. The dimensional model is needed for SSAS multidimensional cubes, which was a nice way to get more performance as OLAP was a lot faster than SQL.

Back in the day, if you needed performance, you had to have a dimensional model, either in SQL datamarts or in SSAS multidimensional.

Nowadays this doesn't really fly anymore. Your SQL datamart does not have to be the layer that has to answer your BI questions. It could very well be the layer that just hands over the data to SSAS or Power BI, which do not need to be in dimensional form at all anymore. Even if your data is denormalized: the SSAS/Power BI tabular engine normalizes it back behind the scenes anyway.

When you evaluate the four reasons, only the third reason still stands. Databases are a lot faster, joins are not needed in SSAS or Power BI and most of the analytical workload has shifted to other tools that use Column storage and RAM to answer the questions your business is looking for. If we include big data in the equation, we see that there are a lot more solutions for our data needs.

Is dimensional model still a concept that holds up in 2018? For sure, it is a intuitive model that end users understand. It also helps to create conformed dimensions. For relational business data it is still widely used. But you could also argue that as long as the model is intuitive for end users and conformed where it needs to be, it is also a good model.

Principal BI consultant at Rubicon

Leave a Reply

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

4 Comments

  1. Can you elaborate on this:
    "Even if your data is denormalized: the SSAS/Power BI tabular engine normalizes it back behind the scenes anyway."?

  2. A great blog post. But I still recommend building a dimension model. The underlying architecture is SSAS which is designed on the dimensional model. As well as it makes creating measures and getting the right values in the data.

    • Jesse Gorter

      Jesse Gorter

      I also prefer the dimensional model, but I do not use words like 'fact' and 'dimensions' for friendly name purposes.

      Are you saying that tabular model underlying architecture is the multidimensional engine of SSAS? Because I am pretty sure that tabular does not use the multidimensional concepts.