Analysis Services · MDX

What the MDX Axis() Function Actually Returns

A month or so ago, before I went on holiday, I was working on a really cool MDX idea that involved the Axis() function. Unfortunately I’ve forgotten what that idea was but while I was working on it I did find out something interesting about the Axis() function – namely that it doesn’t do exactly what the documentation says it does.

The documentation says that the Axis() function returns the set of tuples on a given axis in an MDX query. Here’s a simple example query on the Adventure Works cube showing it in action:

[sourcecode language='text'  padlinenumbers='true']
WITH
MEMBER MEASURES.TEST AS SETTOSTR(AXIS(1))
SELECT {MEASURES.TEST} ON 0,
[Customer].[Gender].MEMBERS ON 1
FROM
[Adventure Works]
[/sourcecode]

image

Here, I’m using the SetToStr() function to take the set returned by the Axis() function and display it in a calculated measure. As you can see from the screenshot, I’m showing all three members from the Gender hierarchy on the Customer dimension on rows and the set returned by Axis(1) is indeed that set.

BUT, now look at this second query and what it returns:

[sourcecode language='text' ]
WITH
MEMBER MEASURES.FIRSTMEMBER AS 
MEMBERTOSTR(AXIS(1).ITEM(0).ITEM(0))

MEMBER MEASURES.TEST AS 
IIF(
[Customer].[Gender].CURRENTMEMBER.UNIQUENAME = 
MEASURES.FIRSTMEMBER, NULL, 1)

SELECT MEASURES.TEST ON 0,
NON EMPTY
[Customer].[Gender].MEMBERS ON 1
FROM
[Adventure Works]
[/sourcecode]

Why is this interesting? The calculated measure FIRSTMEMBER returns the unique name of the first member in the set returned by Axis(1), which should be the first member shown on the rows axis. The calculated measure TEST returns null if the currentmember on the Gender hierarchy has the same unique name as the member returned by FIRSTMEMBER. The calculated measure TEST is on columns in the query, and on rows we get all the members on the Gender hierarchy that return a non null value for TEST. Since only Female and Male are returned, the All Member on Gender must return null for TEST, which means that the All Member is the first member in the set returned by the Axis() function.

So, to summarise, the Axis() function actually returns the set of members on an axis the current query before any NON EMPTY filtering is applied.

5 thoughts on “What the MDX Axis() Function Actually Returns

  1. gbrueckl – Austria – Gerhard has been implementing traditional BI and data warehouse solutions based on the Microsoft Data Platform for over 10 years and has been sharing his knowledge with the community ever since. With the rise of “Big Data” over the last years he also shifted his interests towards new technologies, mainly related to the Hadoop eco system to fight the masses of data he frequently encounters at his customers. Having this strong background with the Microsoft Data Platform, he always tries to combine this “old” and the “new” world which still have a lot of things in common!
    gbrueckl says:

    another interesting thing happens if you replace the NON EMPTY-keyword with the NONEMPTY()-function
    this returns all 3 rows and the first row has a value of NULL

    so it seems that there are different dependencies between AXIS() and NON EMPTY / NONEMPTY() and how/when they are evaluated

    any thoughts on that?

    -gerhard

    1. Chris Webb – My name is Chris Webb, and I work on the Fabric CAT team at Microsoft. I blog about Power BI, Power Query, SQL Server Analysis Services, Azure Analysis Services and Excel.
      Chris Webb says:

      Gerhard, I would expect those differences: NON EMPTY is a part of the SELECT statement, and evaluated at a different time to functions on the axis declaration like NONEMPTY().

  2. Apologies to barge in almost 8 years later. How do I check if the AXIS(1) itself exists or not?
    i.e want to check
    IIF(MEMBERTOSTR(AXIS(1)) = NULL, ‘No Row’, ‘Row Exists))
    This throws #Error

    1. Chris Webb – My name is Chris Webb, and I work on the Fabric CAT team at Microsoft. I blog about Power BI, Power Query, SQL Server Analysis Services, Azure Analysis Services and Excel.
      Chris Webb says:

      Good question! I don’t know, sorry. Why do you want to do this?

      1. Hi Chris

        Many thanks for your Reply.

        We have a new situation where all the dimensions can be Crossjoined in the WHERE slicer. This used to be always in the ROW axis.

        We have a calculate member which essentially is a Tuple (one DIMENSION Member, Measure). This dimension member can be extracted as a CurrentMember (of the Dimension hierarchy) or as an absolute member. To dynamically generate the correct query I need to check if the ROW Axis exists at all. And then I want to check if the Dimension ([Dim Access Method]) exists at all. If the AXIS exists then it can be in a crossjoin anywhere. Eg I use the following Calculated Member to check (considering I do not have more than 8 Crossjoins in Row Axis)

        CREATE MEMBER CURRENTCUBE.[Measures].[Dim Access Method CHECK] AS
        IIF(AXIS(1).Item(0).Item(7).HIERARCHY_UNIQUE_NAME NULL,
        IIF(VBAMDX!INSTR(AXIS(1).Item(0).Item(7).HIERARCHY_UNIQUE_NAME, ‘[Dim Access Method]’, 1 >= 1), AXIS(1).Item(0).Item(7).HIERARCHY_UNIQUE_NAME,
        IIF(VBAMDX!INSTR(AXIS(1).Item(0).Item(6).HIERARCHY_UNIQUE_NAME, ‘[Dim Access Method]’, 1 >= 1), AXIS(1).Item(0).Item(6).HIERARCHY_UNIQUE_NAME,
        IIF(VBAMDX!INSTR(AXIS(1).Item(0).Item(5).HIERARCHY_UNIQUE_NAME, ‘[Dim Access Method]’, 1 >= 1), AXIS(1).Item(0).Item(5).HIERARCHY_UNIQUE_NAME,
        IIF(VBAMDX!INSTR(AXIS(1).Item(0).Item(4).HIERARCHY_UNIQUE_NAME, ‘[Dim Access Method]’, 1 >= 1), AXIS(1).Item(0).Item(4).HIERARCHY_UNIQUE_NAME,
        IIF(VBAMDX!INSTR(AXIS(1).Item(0).Item(3).HIERARCHY_UNIQUE_NAME, ‘[Dim Access Method]’, 1 >= 1), AXIS(1).Item(0).Item(3).HIERARCHY_UNIQUE_NAME,
        IIF(VBAMDX!INSTR(AXIS(1).Item(0).Item(2).HIERARCHY_UNIQUE_NAME, ‘[Dim Access Method]’, 1 >= 1), AXIS(1).Item(0).Item(2).HIERARCHY_UNIQUE_NAME,
        IIF(VBAMDX!INSTR(AXIS(1).Item(0).Item(1).HIERARCHY_UNIQUE_NAME, ‘[Dim Access Method]’, 1 >= 1), AXIS(1).Item(0).Item(1).HIERARCHY_UNIQUE_NAME,
        IIF(VBAMDX!INSTR(AXIS(1).Item(0).Item(0).HIERARCHY_UNIQUE_NAME, ‘[Dim Access Method]’, 1 >= 1), AXIS(1).Item(0).Item(0).HIERARCHY_UNIQUE_NAME, NULL)))))))),

        …… this continues…….. till

        IIF(AXIS(1).Item(0).Item(1).HIERARCHY_UNIQUE_NAME NULL,
        IIF(VBAMDX!INSTR(AXIS(1).Item(0).Item(1).HIERARCHY_UNIQUE_NAME, ‘[Dim Access Method]’, 1 >= 1), AXIS(1).Item(0).Item(1).HIERARCHY_UNIQUE_NAME,
        IIF(VBAMDX!INSTR(AXIS(1).Item(0).Item(0).HIERARCHY_UNIQUE_NAME, ‘[Dim Access Method]’, 1 >= 1), AXIS(1).Item(0).Item(0).HIERARCHY_UNIQUE_NAME, NULL)),

        IIF(AXIS(1).Item(0).Item(0).HIERARCHY_UNIQUE_NAME NULL,
        IIF(VBAMDX!INSTR(AXIS(1).Item(0).Item(0).HIERARCHY_UNIQUE_NAME, ‘[Dim Access Method]’, 1 >= 1), AXIS(1).Item(0).Item(0).HIERARCHY_UNIQUE_NAME, NULL), 0
        )))))))),
        VISIBLE = 1;

        Then this [Measures].[Dim Access Method CHECK] gets used in the following calculated member

        CREATE MEMBER CURRENTCUBE.[Measures].[Method Measure 1]
        AS IIF([Measures].[Dim Access Method CHECK] = NULL, ([Dim Access Method].[Dim Access Method Name].[Dim Access Method Name].&[1], [Measures].[Method-Row Count]),
        IIF([Dim Access Method].[Dim Access Method Name].CurrentMember.MEMBER_KEY = “1”, [Measures].[Method-Row Count], NULL)),
        FORMAT_STRING = “#,#”,
        VISIBLE = 1;

        This is all okay if I have [Dim Access Method].[Dim Access Method Name].[Dim Access Method Name] somewhere in the Row Axis. But as I mentioned the contents of entire Row Axis is now in the WHERE slicer. Hence want to check if the AXIS exists so that I can pass the null value to the [Measures].[Method Measure 1] calculated member.

        This is currently throwing #Error
        CellOrdinal 0
        VALUE #Error The slicer axis cannot be referenced. Query (3, 5) Execution of the managed stored procedure INSTR failed with the following error: Microsoft::AnalysisServices::AdomdServer::AdomdException.
        FORMATTED_VALUE #Error The slicer axis cannot be referenced. Query (3, 5) Execution of the managed stored procedure INSTR failed with the following error: Microsoft::AnalysisServices::AdomdServer::AdomdException.

        P/S Its been a long time since I created these calculations and to redesign this now will involve a lot of regression. Hence need to work around this for now.

        Thanks once again
        Gautham

Leave a ReplyCancel reply

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