…brew your own Business Intelligence

SSAS Multidimensional and Super DAX

An interesting question came up during a recent engagement involving Power BI reporting against a Multidimensional data source. The client wanted to know if there was any benefit for them in upgrading from SSAS 2014 Multidimensional to SSAS 2016 Multidimensional.

My initial response was “no, there would be little benefit in carrying out that upgrade… other than a few more years of support and a couple of random hotfixes. That time and effort would be better spent migrating to Tabular 2016+”

Update 20170825: some enhancements between SSAS MD 2014 to 2016/2016 worth mentioning… improved locking/blocking, memory allocation, heap fragmentation, database consistency checking (DBCC), and xEvents GUI.

As the meeting carried on, I started thinking a bit more about that question. For folks using Power BI reporting, there’s a pretty significant benefit in reporting against Tabular 1200+ models deployed to a SSAS 2016+ instance as opposed to a Tabular 1103 (and below) model deployed to a SSAS 2014 (and below) instance. That benefit is “Super DAX” (more info).

What I wasn’t sure of (and just finished testing) is whether or not the same was true for Multidimensional cubes. In other words, would Power BI generate “Super DAX” against 2016/2017 multidimensional instances? Can 2016/2017 multidimensional models even handle “Super DAX”?

Long story short: Not only does Power BI generate the same DAX queries regardless of the SSAS Multidimensional version… “Super DAX” is NOT supported by any version of Multidimensional instance. Read on for more details.

Testing Setup

To test this out, I installed SSAS 2014 Tabular & Multidimensional instances along side the existing SSAS 2017 Tabular & Multidimensional instances and deployed an equivalent SSAS database to each instance.

image

Next, I used a very simple Power BI report with a single visual (showing sales amount by day of month) and captured the DAX queries via profiler.

image

 

Results

Against a 1103-level model deployed to a SSAS 2014 Tabular instance, we get the following query captured in profiler:

EVALUATE
  TOPN(
    1001,
    ADDCOLUMNS(
      KEEPFILTERS(
        ADDCOLUMNS(
          KEEPFILTERS(
            FILTER(
              KEEPFILTERS(VALUES('Date'[Day])),
              NOT(ISBLANK('Sale'[Sales Amount]))
            )
          ),
          "Sales_Amount_Day", 'Sale'[Sales Amount]
        )
      ),
      "Sales_Amount", [Sales_Amount_Day]
    ),
    [Sales_Amount_Day],
    0,
    'Date'[Day],
    1
  )

ORDER BY
  [Sales_Amount_Day] DESC, 'Date'[Day]

 

Against a 1400-level model deployed to a SSAS 2017 Tabular instance, we get the following query captured in profiler:

EVALUATE
  TOPN(
    1001,
    SUMMARIZECOLUMNS('Date'[Day], "Sales_Amount", 'Sale'[Sales Amount]),
    [Sales_Amount],
    0,
    'Date'[Day],
    1
  )

ORDER BY
  [Sales_Amount] DESC, 'Date'[Day]

 

As you can see, Power BI detected the model compatibility level and generated different DAX queries accordingly. Better yet… the first version of the query (against the 2014 tabular model) results in 3 storage engine queries…

image

…where as the second version of the query (against the 2017 tabular model) results in only 1 storage engine query…

image

That, my friends, why Super DAX is such a big deal!

Against an equivalent SSAS cube deployed to a SSAS 2014 Multidimensional instance, we get the following query captured in profiler:

EVALUATE
  TOPN(
    1001,
    ADDCOLUMNS(
      KEEPFILTERS(
        ADDCOLUMNS(
          KEEPFILTERS(
            FILTER(
              KEEPFILTERS(SUMMARIZE(VALUES('Date'), 'Date'[Day.UniqueName], 'Date'[Day])),
              NOT(ISBLANK('Sale'[Sales Amount]))
            )
          ),
          "Sales_Amount_Day_UniqueName", 'Sale'[Sales Amount]
        )
      ),
      "Sales_Amount", [Sales_Amount_Day_UniqueName]
    ),
    [Sales_Amount_Day_UniqueName],
    0,
    'Date'[Day],
    1,
    'Date'[Day.UniqueName],
    1
  )

ORDER BY
  [Sales_Amount_Day_UniqueName] DESC, 'Date'[Day], 'Date'[Day.UniqueName]

Against an equivalent SSAS cube deployed to a SSAS 2017 Multidimensional instance, the same query was generated…which means Power BI did not try to generate Super DAX for the SSAS 2017 Multidimensional instance.

I then tried to take the Super DAX query generated for the SSAS 2017 Tabular instance and run it via SSMS against the SSAS 2017 Multidimensional instance and got the following error message:

Executing the query ...
Query (4, 5) The function 'SUMMARIZECOLUMNS' is not available for Multidimensional models.
Run complete
image

What this tells me is that DAXMD (the feature added to SSAS Multidimensional instances allowing them to process DAX queries) has not been updated to handle Super DAX.

Take Away

  • Power BI is awesome!
  • Super DAX is awesome!
  • If you’re using Tabular, I hope you’re using 2016+ (if not, it’s time to upgrade)
  • If you’re using Multidimensional, I hope an migration to Tabular is on your road map (cause it should be)

13 thoughts on “SSAS Multidimensional and Super DAX

  1. Wim Cos says:

    I haven’t really found a really good answer on why people should migrate to tabular from multidimensional yet.

    If an Enterprise already has a good working set of cubes, what would be the benefit of switching to Tabular?

    1. Bill says:

      fair enough… that statement does deserve a bit of qualification…

      reasons for migrating… from a performance perspective tabular is typically as fast (if not faster) than Multidimensional, Azure Analysis Services, DAX is easier than MDX (which means lower developer costs due to bigger talent pool), new MSFT features have been (and will likely continue to be) developed for tabular and later made compatible with multidimensional, faster to develop and expand, Import from Power BI…

      If an enterprise already has a good working set of cubes (i.e. suitable performance, suitable reporting capabilities), a team of capable developers who understand MDX (which is becoming harder to find), and are not looking to move to Azure in the next 2-3 years… then no, there’s no immediate reason to migrate.

      1. Wim Cos says:

        With Data Warehousing software like TimeXtender and decent cube browsers like Power BI and Targit, MDX knowledge is not as critical as it is made up to be IMHO. I do agree on the development being focussed to Tabular so it’s something to keep in mind. The development budget of overhauling a well defined set of cubes is huge and compared to the unclear advantages of Tabular, I don’t know why people would suddenly jump ship. Very interesting and exciting to see where this is heading.

        1. Bill says:

          re: MDX knowledge being not as critical
          I’m simply going to disagree.

          re: migrating existing cubes to tabular
          “it depends”…

          re: semantic layer for anything new (i realize you didn’t ask this but thought it was worth stating)
          there are very VERY few reasons to go with MD over TAB (I used to feel differently 2 years ago)

          1. tom garvin says:

            I arrived a bit late to the DW world, having only read and observed cubes and multi-dimensional literature. But I did have a very strong background in tabular, by way of Excel, and also 25 years with SQL Server development (TDS starts with “tabular”, eh?)

            To me, multi-dimensional gave the appearance of high-science, but the learning path for tabular felt so much more natural. As a developer, I concur with Bill, whatever past superior benefits to be found in multi are quickly being surpassed by tabular, once you consider all factors: ease of use, clarity of data model, design of tools (Power BI) targeted to a much broader range of Citizen Developers, etc.

            Accordingly, I bet my business on tabular, catching the new wave. I think this is one of those strategies loses me some legacy $$ consulting opportunities. But I do think all of the exciting work will be on the frontlines of tabular.

            And since I mentioned legacy, note that Excel for Windows celebrates its 30th anniversary this November. The vast majority of power users have an easy “lateral move” into PowerPivot and then Power BI, all of which are friction-free paths to tabular, bypassing a steep multi / MDX learning curve.

      2. tom garvin says:

        I’m new, sure wish I could find a “Like” button for this post.

  2. Steven J Neumersky says:

    I thought the better locking implementation is a good reason to upgrade from 2014 MD to 2016/2017 MD. I think there is a lower time duration for server level locking in some scenarios.

    1. Bill says:

      That’s a good reason especially for those folks doing frequent processing!

      (added an update note near the top of the post)

  3. Craig Myers says:

    What would a roadmap from 2012 MD to 2016 tabular look like?

    1. Bill says:

      that’s a great question…

      technology-wise, there’s no tool (that I’m aware of) Visual Studio or otherwise that automates the conversion of a MD project to a Tabular project. So you’d be looking at a complete rebuild of the solution from scratch. The data model stays the same (for the most part) so the bulk of the work is going to be converting MDX calculations to DAX calculations.

      How much of an effort that is obviously depends on the length/complexity of the existing MDX script… simple cubes where the MDX script consists of a few time-intelligence calculations, no problem … a skilled SSAS developer could do it in a day or 2. Complex cubes (e.g. financial applications) with MDX scrpts spanning thousands of lines of MDX code… filled with scoped assignements, recursion, custom assemblies, custom rollups, etc… you’d need a very strong reason to justify the cost of migrating.

      1. CRAIG MYERS says:

        Thank you for the response and all the great information on your site!

Leave a Reply