Error messages in MDX SELECT statements and what they mean

Anyone that has tried to learn MDX will know that, when you make a mistake somewhere in your code, the error messages that Analysis Services gives you are pretty unhelpful. It was suggested to me recently while I was teaching an MDX course that I should blog about common error messages and what they actually mean; so here’s a list of a few example queries using Adventure Works that return confusing errors, the error messages themselves, and details on how to solve the problems. I’ve deliberately concentrated on query-related errors rather than calculation-related errors (that can be a future blog post); if you can think of any more errors that I should cover please leave a comment.

1) Query causing error:

SELECT
{[Measures].[Internet Sales Amount]} ON COLUMNS 
[Date].[
Calendar Year].MEMBERS ON ROWS
FROM [Adventure Works]

Error message: Query (3, 1) Parser: The syntax for ‘[Date]’ is incorrect.

The first step to solving this fairly simple syntax error is understanding the values in brackets in the error message. (3,1) indicates that the error is at character 1 on the third line of the query, where we have the expression [Date].[Calendar Year].MEMBERS; we should also see a red squiggly underneath this text in SQL Management Studio. There’s nothing wrong with this expression though, apart from the fact that it’s in the wrong place: what has happened is that we’ve forgotten to include a comma after COLUMNS immediately beforehand. If we put one in, the query runs.

Solution:

SELECT
{[Measures].[Internet Sales Amount]} ON COLUMNS, 
[Date].[
Calendar Year].MEMBERS ON ROWS
FROM [Adventure Works]

 

2) Query causing error:

SELECT
{[Measures].[Internet Sales Amount]} ON COLUMNS,
[Date].[Calendar].[Calendar Year].MEMBERS.CHILDREN ON ROWS
FROM [Adventure Works]

Error message: Query (3, 1) The CHILDREN function expects a member expression for the 1 argument. A tuple set expression was used.

This is a very common error that people encounter while learning MDX, and it all comes down to understanding the difference between sets, tuples and members. In a lot of situations Analysis Services is very forgiving: if it expects a set and you give it a single member, then it will cast that member into a set with one item in it for example. It can’t do this for you all the time, though, and you do need to understand what kind of object each function returns and/or expects for a parameter. In this case, the problem is that the .CHILDREN function needs to be passed a member and the .MEMBERS function returns a set (strictly speaking, as the error says, it’s a set of tuples); therefore we can’t use the two functions together. If we want to find all of the children of all years, we can use the DESCENDANTS function instead, which can accept a set as its first parameter.

Solution:

SELECT
{[Measures].[Internet Sales Amount]} ON COLUMNS,
DESCENDANTS(
[Date].[Calendar].[Calendar Year].MEMBERS
, [Date].[Calendar].[Calendar Semester])
ON ROWS
FROM [Adventure Works]

 

3) Query causing error:

SELECT
[Measures].[Internet Sales Amount], [Measures].[Internet Tax Amount] ON COLUMNS,
[Date].[Calendar].[Calendar Year].MEMBERS
ON ROWS
FROM [Adventure Works]

Error message: Parser: The statement dialect could not be resolved due to ambiguity.

Analysis Services supports no less than three query languages: MDX, DMX and a very limited subset of SQL. As a result, when you run a query it needs to work out what query language you’re using and can easily get confused if you make a mistake. In the query above we’ve given a list of the two measures we want to see on the columns axis, but we’ve forgotten to surround this list in braces to turn it into a set – and it’s a set that is required for the axis definition. This is an error that is commonly made by people with a background in SQL, and indeed the problem here is that the error has made the query look a bit too much like SQL or DMX. Putting in braces where they’re needed fixes the problem and removes the ambiguity.

Solution:

SELECT
{[Measures].[Internet Sales Amount], [Measures].[Internet Tax Amount]} ON COLUMNS,
[Date].[Calendar].[Calendar Year].MEMBERS
ON ROWS
FROM [Adventure Works]

4) Query causing error:

SELECT
{[Measures].[Internet Sales Amount1], [Measures].[Internet Tax Amount]} ON COLUMNS,
[Date].[Calendar].[Calendar Year].MEMBERS
ON ROWS
FROM [Adventure Works]

Error message: Query (2, 2) The member ‘[Internet Sales Amount1]’ was not found in the cube when the string, [Measures].[Internet Sales Amount1], was parsed.

A fairly straightforward error this: we’ve tried to reference a member that doesn’t exist in our query – it’s the extra 1 on the end of the name that’s the problem. The way to avoid this is to always let Analysis Services generate unique names for you, and you can do this by dragging the member (or any other object) from the metadata pane in SQL Management Studio into the MDX query pane when you’re writing queries. Here, using the correct member unique name solves the problem.

Solution:

SELECT
{[Measures].[Internet Sales Amount], [Measures].[Internet Tax Amount]} ON COLUMNS,
[Date].[Calendar].[Calendar Year].MEMBERS
ON ROWS
FROM [Adventure Works]

Note that for dimensions other than the Measures dimension, what happens in this scenario depends on how you’ve set the MDXMissingMemberMode property. By default if you write something that looks like it could be an MDX unique name, but which isn’t actually the unique name of a member on a hierarchy, Analysis Services will simply ignore it. So the following query returns nothing on rows because the year 2909 doesn’t exist in our Calendar hierarchy:

SELECT
{[Measures].[Internet Sales Amount], [Measures].[Internet Tax Amount]} ON COLUMNS,
{[Date].[Calendar].[Calendar Year].&[2909]}
ON ROWS
FROM [Adventure Works]

And worse, the in this query a genuine syntax error is completely ignored too:

SELECT
{[Measures].[Internet Sales Amount], [Measures].[Internet Tax Amount]} ON COLUMNS,
[Date].[Calendar].[Calendar Year].MAMBERS
ON ROWS
FROM [Adventure Works]

35 thoughts on “Error messages in MDX SELECT statements and what they mean

  1. Hi Chris, my MDX is as follows:
    Sum
    (PeriodsToDate
    ([Date].[Financial].[Financial Year],[Date].[Financial].CurrentMember),
    [Measures].[Budget]
    )

    And I have got err as follows:
    “The Form View cannot be displayed because the MDX script has the following syntax error: ‘Parser: The end of the input was reached. [Line:0; Column:0]’ To correct this error, click Script View on the toolbar, and then edit the MDX script to correct the syntax.”

    Do you have any idea what that would be? Thank you in advance for your help, Chris.
    Cheers, Shirley

    • Thank you! Query / solution #3 fixed my problem – that’s a gotcha that I won’t forget. Appreciated.

  2. Hi Chris,
    I am trying to filter on a date range and getting the following error.
    Filter:

    SET [FilterDate] as
    filter ([Claims Due Date].[Date].[Date].Members,[Claims Due Date].[Date].MemberValue>=cDate(“04/01/2014”) : [Claims Due Date].[Date].MemberValue<=cDate("11/20/2014")
    )
    Error:
    The Range function expects a member expression for the 1 argument. A string or numeric expression was used.

    Any ideas on what I am doing wrong.

    Regards, Janet

    • Hi Janet,

      I think you need an AND condition here, not a range. Try
      SET [FilterDate] as
      filter ([Claims Due Date].[Date].[Date].Members,[Claims Due Date].[Date].MemberValue>=cDate(“04/01/2014″) and [Claims Due Date].[Date].MemberValue<=cDate("11/20/2014")
      )

      The range operator : takes two members on either side, for example something like this
      [Claims Due Date].&[Date].[01042014] : [Claims Due Date].&[Date].[20112014]
      … which is likely to be a more efficient way of solving the problem than using the filter() function as in the previous example.

      • Thank you for your quick reply, Chris.
        When I use the AND I receive no results yet, when I just use the following I get results that would fall between those two dates.
        SET [FilterDate] as
        filter ([Claims Due Date].[Date].[Date].Members,[Claims Due Date].[Date].MemberValue>=cDate(“04/01/2014”) )

        Your suggestion above is to use the range but as a where clause instead of a function?

      • I tried using this [Claims Due Date].&[Date]>=[20140401] : [Claims Due Date].&[Date]<=[20141120] and received an error " The dimension '[20140401]' was not found in the cube when the string, [20140401], was parsed.

        This however does receive results:
        [Claims Due Date].[Date].&[20140401]
        as does this:
        [Claims Due Date].[Date].&[20141120]

        It seems determined to not allow me to use a range.

      • I think you’re getting confused here between what’s part of the unique name of a member and what’s a value. Can you try something for me? Open a new MDX query window in SQL Server Management Studio, expand the Date hierarchy on the Claims Due Date dimension and drag the member for 1st January 2014 into the query window. Does it display [Claims Due Date].[Date].&[20140401] ? If so, then try
        SET [FilterDate] as [Claims Due Date].[Date].&[20140401] : [Claims Due Date].[Date].&[20141120]

        In this example [Claims Due Date].[Date].&[20140401] is the unique name of the member 1st January 2014, and when you are using : you can only put member unique names on either side. The : gets a set of members from the member on the left hand side to the member on the right hand side. You can’t use operators like > or = with member unique names.

    • I’m sorry. I have been trying so many different scenario’s I wound up giving you some bad information. The actual format is [Claims Due Date].[Date].&[20140401]. One thing I had tried was this SET [FilterDate] as { [Claims Due Date].[Date].&[2014-04-11] : [Claims Due Date].[Date].&[2014-11-20] }, which I see my error from what you sent, I had { }. Removing those brackets works.

      The reason I initially sent this format “04/01/2014” is because I am using parameters that are to be set by the date picker. Now that I have the range piece working, I will work on converting the dates.

      I thank you very much for your assistance and patience. I am VERY new to this and have had no training.

      Regards and thank you, Janet

  3. Hi Chris,
    I am a beginner in MDX ..
    select {([Date].[Calendar].currentmember.name)}on 0
    from [Adventure Works]
    Above query gives me error “Query (1, 8) The function expects a tuple set expression for the 1 argument. A string or numeric expression was used.”

    I can see that it is fixed when I re-frame it as below:
    with member A
    as [Date].[Calendar].currentmember.name
    select A on 0
    from [Adventure Works]
    But I am not able to grasp the difference! If it lets me select the round-about way, why doesn’t it let me select the direct way?

  4. Hi Chris,
    In database, there are many hierarchies we removed one hierarchy and when processed the cube getting the below error in MDX calculations.

    MdxScript ( Data Base Name ) The [ Level] plane was ‘ [ Dimension ] . [Hierarchy ] . [ Level] ‘ not found while parsing the string you see in the cube .
    The END SCOPE statement does not match the opening SCOPE statement .
    MdxScript ( Data Base Name ) END SCOPE statement does not match the opening SCOPE statement match .
    The MDX script contains at least one error

    Is it because of data issue?

    • No, it sounds like you have an MDX calculation (probably a SCOPE statement) that references the hierarchy you have deleted. You either need to put the hierarchy back or rewrite the MDX expression that references it.

  5. Dear Chris,

    We are currently facing low performance when using the below MDX Query.
    I tried to rewrite the query by removing the FILTER functions and used respective member for ‘E6’ from attribute hierarchy but still no improvements.
    Could you suggest to improve the performance for the below queries.

    In Attribute Hierarchy, there are two different ways we get the dimension member.
    [Measure Item].[Measure Item].MEMBERS and [Measure Item].[Measure Item].[Measure Item].MEMBERS which one will give better performance

    SCOPE([Date Dynamic].[Date Dynamic].&[Q]);
    SCOPE(FILTER([Measure Item].[Measure Item].MEMBERS,
    [Measure Item].[Measure Item].Properties(“Measure Item Characteristic”) = “E6”));
    SCOPE([Date Of Posting].[Quarter].members);
    THIS=([Date Dynamic].[Date Dynamic].&[C],
    [Date Of Posting].[Quarter].Currentmember)-
    ([Measures].[Value],[Date Dynamic].[Date Dynamic].&[C],
    [Date Of Posting].[Quarter].currentmember.prevmember);
    END SCOPE;
    END SCOPE;
    END SCOPE;

    Thanks in advance

  6. Hi Chris,
    For quarterly calculations currently we are facing low performance while using the below MDX Query.
    Tried replacing Filter functions with the attribute member itself for example rather than “Measure Item Characteristic”) = “E6” replaced with &[E6].
    Could you please suggest me improve the performance in the below query.
    And also which attribute hierarchy will have high performance.

    [Date Of Posting].[Quarter].currentmember or [Date Of Posting].[Quarter].[Quarter]currentmember

    SCOPE([Date Dynamic].[Date Dynamic].&[Q]);
    SCOPE(FILTER([Measure Item].[Measure Item].MEMBERS,
    [Measure Item].[Measure Item].Properties(“Measure Item Characteristic”) = “E6”));
    SCOPE([Date Of Posting].[Quarter].members);
    THIS=([Date Dynamic].[Date Dynamic].&[C],
    [Date Of Posting].[Quarter].Currentmember)-
    ([Measures].[Value],[Date Dynamic].[Date Dynamic].&[C],
    [Date Of Posting].[Quarter].currentmember.prevmember);
    END SCOPE;
    END SCOPE;
    END SCOPE;

  7. with member A
    as [Date].[Calendar].currentmember.name
    select A on 0
    ,[Measures].[Gross Profit Margin] on 1
    from [Adventure Works]

    when I am executing above mdx query ,I got below error. why this is happening
    The Measures hierarchy already appears in the Axis0 axis.

    • Your calculated member definition doesn’t specify a hierarchy, so by default A becomes a calculated measure – and you can’t put members from the same hierarchy on two different axes. The solution is to put A on the Calendar hierarchy of the Date dimension, something like this:

      with member [Date].[Calendar].A
      as [Date].[Calendar].currentmember.name
      select {[Date].[Calendar].A} on 0,
      {[Measures].[Gross Profit Margin]} on 1
      from [Adventure Works]

  8. Hi Chris

    Today I read some information about you on net.
    so, I am requesting to you,Kindly provide solution of below problem.
    I am confused because,don’t know difference between Members,All and AllMembers.
    with example.

    • Hi Narendra, the AllMembers function returns every member from a hierarchy including any calculated member. Members.All doesn’t look like real MDX to me – are you talking about the All Member? The All Member is the artificial member that is created at the top of every hierarchy by default.

  9. Hi Chris

    Can you explain difference between Conformed dimension and Role playing dimension and both dimension can be same.? and what is use of Degenerate dimension in real environment.?

  10. Hello, I have an Excel issue that no one I know is able to solve. I just upgraded to using Office 365 with which my Excel got upgraded to Excel 2016. A power pivot report that used to run fine before, suddenly has error in 2 columns of the power pivot in the back end. It has an extra bracket. Earlier, I would just erase the extra bracket, press enter and whole column would clean up but now the error ‘The end of column was reached’ appears followed by text telling me that the other column has an error. After which I can’t even click on any other column and yet it doesn’t let me correct, remove or improve its own formula either. I don’t know what’s wrong. Any help will be much appreciated.

    The formula in the cell of the Key column is [date]&”_”&[country]&”_”&[os_name]&”_”&[network]&”_”&[group_network]

  11. Hi Chris,

    We are trying to execute the MDX query BUT we are getting the below error:

    Query (44, 33) A table of multiple values was supplied where a single value was expected.

    BUT once we remove one of the dimension it is successfully executing. What does that mean?

    And also if i seperately execute the dimension i see no error.

    Regards,
    Nitin

    • That looks like an error message coming from a measure you’re using in the query. Try to add/remove measures from the query to find out which measure is causing the problem, then post what the definition of the measure is.

  12. WITH
    MEMBER [Account].[Calculation_581527319921311744] AS
    ‘([Account].[Sales Margin] / [Sales Margin].[Sales])’,
    SOLVE_ORDER = 127
    SET [Tableau Set 0] AS
    ‘[Total Major Customer].Generations(4).Members’
    MEMBER [Total Major Customer].[Tableau Slice Member 1] AS
    ‘Sum([Tableau Set 0])’,
    SOLVE_ORDER = 127
    SELECT
    {[Sales Margin].[Sales]} DIMENSION PROPERTIES [MEMNOR],[ANCESTOR_NAMES] ON COLUMNS, NON EMPTY Generate( [Entity].Generations(3).Members, Descendants([Entity].CurrentMember, [Gen5,Entity], LEAVES) ) DIMENSION PROPERTIES [MEMNOR], [ANCESTOR_NAMES] ON ROWS FROM [MMSales] WHERE ([Total Major Customer].[Tableau Slice Member 1])

    when I run this MDX I receive an error saying syntax error near WHERE. I need help. I don’t know what is wrong with the query.

    • As far as I can see, this is a problem with a circular reference: you have a calculated member that references a named set and this measure is used in the Where clause – but the named set has to be referenced in the context of the Where clause. I don’t get a syntax error when I do something similar to this though, I get an infinite recursion error, so there could be some other problem here too but the query as it stands will never work. Is this MDX generated by Tableau? If so, they they will need to fix it.

Leave a Reply

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

WordPress.com Logo

You are commenting using your WordPress.com 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 )

Google+ photo

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

Connecting to %s