Multidimensional or Tabular

This post is not a list of multidimensional features unsupported by tabular. This has been documented already by various reliable sources including the following posts, as well as PASS presentations.

First thing I would like to say is I agree that there is lots of work for tabular to catch up to the feature-rich multidimensional. The tabular-model designer inherited from Power Pivot is sluggish for models with lots of tables. The Excel-like DAX formula bar is, to put it politely, annoying. Without saying anything too controversial, MS corporate BI has been playing second fiddle lately.

However, tabular does make sense for many customers today. For most customers, having fast performance is more important than the unsupported features – which invariably either have “workarounds” or are fringe use cases.

On the workarounds, if the same functionality can be delivered to the business, they don’t care if we technical people see it as a “workaround” because it’s not delivered the same way we are used to. And the business people are the ones that matter. This applies to many-to-many relationships, parent-child hierarchies, role-playing dimensions (can create multiple instances of same table), and various other items.

For what I’m calling the fringe use cases, the supportability of some of these does not make sense for many customers. Hand a solution to support that uses MDX stored procs, extensive scoped-cell assignments and they will struggle. How many implementations use these features because the developer thought they were cool rather than having any real business need? I think quite a few.

Other use cases may be showstoppers like unary operators and writeback, but not for the majority of implementations.

Scoped-cell assignments are in the potential showstopper list too, but in most cases if calculation logic is pushed to the ETL layer (where it belongs if not one of the strengths of the cube/tabular model like aggregated level calcs, or those which would cause a data explosion problem at the relational level, etc) to avoid the formula engine where possible, then DAX is a pretty capable and powerful language for calculations built into the tabular model.

On the tabular memory limitation, many customers I’ve talked to are worried they won’t fit into memory when they are actually nowhere near the upper limit of what they can relatively easily get on a server (especially when limited to the required data). Also, more memory will only become more viable in the future. For a multi-terabyte data warehouse implementation like a, then yes but again this is a fringe use case.

As noted elsewhere,

  • When a project chooses multidimensional or tabular, it is not possible to change your mind without starting development again.
  • I think it is widely accepted that Microsoft is more likely to build new features and put future development investment into tabular than multidimensional.

It is a valid statement that a project may need some of the features unsupported by tabular at a later date, which could be a problem. Conversely a project may encounter performance issues with multidimensional that were not anticipated at the start of the project.  One could also argue that, as new features are built into tabular, could be stuck with multidimensional and unable to leverage better tabular capabilities in the future.

Update Nov 13 2013 – post from Marco Russo: Updates about Multidimensional vs Tabular

5 Responses to Multidimensional or Tabular

  1. Pingback: Tabular model: Not ready for prime time? | James Serra's Blog

  2. Pingback: Experiences & Tabular Tips | Christian Wade's Blog

  3. Mark says:

    Christian, would you consider using scoping in multidimensional to implement a date calculation dimension a fringe case ? AFAIK there is no analogue to this in tabular, and it is clearly not something that can be moved back into the ETL. Yet, in my experience anyway, it is often a fundamental necessity. Perhaps that has not been your experience ? Or perhaps I am wrong about this in tabular ?

    • Christian says:

      Hi Mark,

      I do not consider the end-user business functionality delivered through scoping in multidimensional to implement a date-calculation dimension to be a fringe use case. This technique is often referred to as time-utility dimensions.

      All the multidimensional cubes I ever built extensively use time-utility dimensions. They allow the same business logic to be applied to multiple measures. Typical examples are classic BI calculations such as YTD, MTD, 12 month moving average, … reused across multiple measures by scoping on them. The Business Intelligence Wizard in multidimensional uses this technique (or close to it) in many cases.

      Whenever I created time-utility dimensions, I seldom exposed them directly to the end user. I would instead create calculated measures that are sliced by members of the time-utility dimensions and expose the calculated measures to the users. In my experience, most users don’t want to conceptualize time utility dimensions; they just want the same logic applied to various measures and an easy way to get at it.

      You are right that there is no equivalent (in terms of backend implementation) to this in tabular, and it is not something that can be moved to the ETL. I agree this is one of the disadvantages of tabular.

      However, the same end-user business functionality can be delivered in tabular. It is not as elegant on the backend as time-utility dimensions, and the re-usability of code is reduced (or relegated to copy-and-paste reuse), but from the end-users’ perspective they can still get what they want. It is just a different way to organize code on the backend.

      The downside in tabular is inelegant re-usability of business logic. Most people would consider the benefits of code re-use to include maintainability, supportability, readability, … One could argue that time-utility dimensions in multidimensional – despite higher code reuse – actually result in less of these benefits.

      I agree this is one of the disadvantages of tabular. However, it is another case where we can deliver the same business functionality using a “workaround”.

      Thanks for the comment,

  4. Pingback: Experiences & Tabular Tips | Christian Wade's Blog

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

%d bloggers like this: