Excel CUBE-Functions and MDX UniqueNames

Two weeks ago at the German SQL Server Conference 2015 I was at Peter Myer’s session about Mastering the CUBE Functions in Excel. (PS: Peter is also speaking on our upcoming SQLSaturday #374 in Vienna next week and at PASS SQLRally in Copenhagen the week after). After his session we had a further discussion about this topic and our experiences on how to use Excels CUBE-functions in order to build nice Dashboards with native Excel functionalities that also work with e.g. Excel Services. Its always great to exchange with people that share the same passion on he same topic! One thing we both agreed on that is missing currently is a way to get the MDX UniqueName of something that is selected in a slicer, filter or simply in a cell using CUBEMEMBER-function. I once used a special Cube Measure which was created in MDX Script which returned the UniqueName of a given member that was selected together with this special measure. For this to work with Excel you need to know how Excel builds the MDX when querying cube values using CUBEVALUE-function. Here is a little example:
Excel_CubeValue_Formula
This produces the following MDX query:

  1. SELECT
  2. {
  3.     (
  4.         [Measures].[Internet Sales Amount],
  5.         [Product].[Category].&[1]
  6.     )
  7. } ON 0
  8. FROM [Adventure Works]
  9. CELL PROPERTIES VALUE, FORMAT_STRING, LANGUAGE, BACK_COLOR, FORE_COLOR, FONT_FLAGS

So it basically creates a tuple that contains everything you pass into the CUBEVALUE-Function as a parameter. Knowing this we can create a calculated measure to get the MDX UniqueName of this tuple using MDX StrToTuple()- and MDX AXIS()-function:

  1. MEMBER [Measures].[Excel TupleToStr] AS (
  2. TupleToStr(axis(0).item(0))
  3. )

Replacing the [Measures].[Internet Sales Amount] of our initial CUBEVALUE-function with this new measure would return this to Excel:

  1. ([Measures].[Internet Sales Amount],[Product].[Category].&[1])

 

Ok, so far so good but nothing really useful as you need to hardcode the member’s UniqueName into the CUBEVALUE-function anyway so you already know the UniqueName.
However, this is not the case if you are dealing with Pivot Table Page Filters and/or Slicers! You can simply refer to them within the CUBEVALUE-function but you never get the UniqueName of the selected item(s). Well, at least not directly! But you can use the approach described above, using an special MDX calculated measure, to achieve this as I will demonstrate on the next pages.

Calculated measures can only be created using the Pivot Table interface but can also be used in CUBE-functions. So first thing you need to do is to create a Pivot Table and add a new MDX Calculated Measure:
Excel_Create_MDX_calculated_measure

!Caution! some weird MDX coming !Caution!


Excel_Create_MDX_calculated_measure2

You may wonder, why such a complex MDX is necessary and what it actually does. What it does is the following: Based on the example MDX query that Excel generates (as shown above) this is a universal MDX that returns the MDX UniqueName of any other member that is selected together with our measure using the CUBEVALUE-function. It also removes the UniqueName of the measure itself so the result can be used again with any other measure, e.g. [Internet Sales Amount]
The reason why it is rather complex is that Excel may group similar queries and execute them as a batch/as one query to avoid too many executions which would slow down the overall performance. So we cannot just reference the first element of our query as it may belong to any other CUBEVALUE-function. This MDX deals with all this kinds of issues.

The MDX above allows you to specify only two additional filters but it may be extended to any number of filters that you pass in to the CUBEMEMBER-function. This would be the general pattern:

  1. MID(
  2.   IIf(axis(0).item(0).count > 0 AND
  3.         NOT(axis(0).item(0).item(0).hierarchy IS [Measures]),
  4.     "," + axis(0).item(0).item(0).hierarchy.currentmember.uniquename,
  5.     "")
  6. + IIf(axis(0).item(0).count > 1 AND
  7.         NOT(axis(0).item(0).item(1).hierarchy IS [Measures]),
  8.     "," + axis(0).item(0).item(1).hierarchy.currentmember.uniquename,
  9.     "")
  10. + IIf(axis(0).item(0).count > n AND
  11.         NOT(axis(0).item(0).item(n).hierarchy IS [Measures]),
  12.     "," + axis(0).item(0).item(n).hierarchy.currentmember.uniquename,
  13.     "")
  14. , 2)

After creating this measure we can now use it in our CUBE-functions in combination with our filters and slicers:
Excel_MDX_CUBEVALUE_UniqueNames_Filter
Excel_MDX_CUBEVALUE_UniqueNames_Slicer

You may noted that I had to use CUBERANKEDMEMBER here. This is because filters and slicers always return a set and if we would pass in a set to our CUBEVALUE function a different MDX query would be generated which would not allow us to extract the single UniqueNames of the selected items using the approach above (or any other MDX I could think of). So, this approach currently only works with single selections! I hope that the Excel team will implement a native function to extract the UniqueName(s) of the selected items in the future to make this workaround obsolete!

Once we have our UniqeName(s) we can now use them in e.g. a CUBESET-function to return the Top 10 days for a given group of product (filter) and the selected year (slicer):
Excel_MDX_CUBESET_TopCount

And that’s it!

So why is this so cool?

  • It works with SSAS (multidimensional and tabular) and Power Pivot as Excel still uses MDX to query all those sources. It may also work with SAP HANA’s ODBO connector but I have not tested this yet!
  • It does not require any VBA which would not work in Excel Services – this solution does!
  • The calculation is stored within the Excel Workbook so it can be easily shared with other users!
  • There is no native Excel functionality which would allow you to create a simple Top 10 report which works with filters and slicers as shown above or any more complex dynamic report/dashboard with any dynamic filtering.

So no more to say here – Have fun creating your interactive Excel web dashboards!

Download sample Workbook: Samples.xlsx

Note: You may also rewrite any TOPCOUNT expression and use the 4th and 5h parameter of the CUBESET-function instead. This is more native and does not require as much MDX knowledge:Excel_MDX_CUBESET_TopCount_Native
However, if you are not familiar with MDX, I highly recommend to learn it before you write any advanced calculations as show above as otherwise the results might be a bit confusing in the beginning! Especially if you filter and use TOPCOUNT on the same dimension!

10 Replies to “Excel CUBE-Functions and MDX UniqueNames”

  1. Great article, thank you. Could you additionally explain the network architecture of such solution? Excel generates DAX, SSAS executes it, but does the direct access to the SSAS required and ? Is there the way to get the DAX request as a http/wcf/odata request on the custom middle layer service, then send it to the SSAS and when get results as recordset return them as ODATA back to excel ?

    • Hi Roman,
      so Excel executes MDX if you use Pivot Tables or Cube-Value functions and DAX if you run Power View reports. In both cases, in order to see live data, you need a access to the SSAS server. All this does not involve any http/wcf/odata or whatsoever but just XMLA (http://en.wikipedia.org/wiki/XML_for_Analysis) where the MDX/DAX is wrapped into as an -command.
      I don’t actually understand why you want a mid-layer and return the data as OData? could you elaborate a bit on your scenario?
      if you need HTTP-connectivity you may want to take a look at msmdpump.dll (http://wordpress.gbrueckl.at/2014/10/configure-http-access-to-analysis-services-using-powershell-2/ or just google it) which exposes the XMLA interface via HTTP

      -gerhard

      • Hi Gherard. As I’m thinking about the middle layer architecture could be useful for distributed geographically and administratively organisations that have common intranet but do not have VPN. Such middle layer could isolate SQL Server (as it is usually installed with SSAS on the same machine). Also such middle layer could quickly inspect and audit requests.

        But I can’t imagine how Excel could execute MDX/DAX if data is remote… What SSAS is doing then?

      • Thank you for the link to SSAS access via http. If such server could be installed outside the SSAS machine – it is a solution.

        P.S. But the question what is the role of SSAS server if Excel executes (means parse MDX/DAX and translates them to data read operations) left unclear for me.

        • Its a simple client-server architecture, similar to executing a SQL query from a client (e.g. using Management Studio) on a remote SQL Server.
          HTTP access should work for your scenario, SSAS and IIS does not have to be installed on the same machine but need to be in the same network and reachable

          -gerhard

          • SSMS does not execute query… It sends it to the server as plain text, server really do all work. Let say that was a terminology problem, what is important that excel sends the MDX/DAX as plain text command to the SSAS and SSAS return recordset and the http proxy can be used for that.

    • what exactly do you mean by EWA?
      in general the approach described should work fine in Excel Services also but to be honest, I have not tested it yet

      -gerhard

  2. Hello Gerhard, I’m trying to create a measured based on a Tabular model (pivot) but cant figure out the syntax.

    What I’m trying to do is to get ‘Net Cash Invoice Sales’ per ‘Employee’ where the customer type is ‘Existing Guest’.

    I tried the syntax below but get an error. again this is creating the measure in Excel.

    Can you help?

    Filter (
    [Employee].[Employee Name].[Employee Name].ALLMEMBERS,
    ([Measures].[Net Cash Invoice Sales],
    [Customer].[Customer Type].&[Existing Guest])

    • well, FILTER() will return a set and not a scalar value so it cannot be used as measure
      you would need to iterate over the set and then combine its item with the original measure ([Measres].[Net Cash Invoice Sales]) again

Leave a Reply

Your email address will not be published. Required fields are marked *

*