Stairway to MDX - STEP 1: Getting Started with MDX

  • Comments posted to this topic are about the item Stairway to MDX - STEP 1: Getting Started with MDX

  • Brilliant Read!!!

    Not many articles on MDX. Great Job!!:-)

    Raunak J

  • MDX is something I have not had the opportunity to delve in, but wanted to know more about. This is a great introduction and I look forward to the rest of the series.

  • Great article, i am looking forward for the series

  • "MDX query can have up to 128 axes"… So I tried this:

    SELECT {[Measures].[Internet Sales Amount],

    [Measures].[Internet Order Count]} on axis(0)

    , {[Date].[Fiscal Year].members} on axis (1)

    , [Customer].[Country].members on axis(2)

    FROM [Adventure Works]

    And received an error: Results cannot be displayed for cellsets with more than two axes.

    So, are there additional rules for more-than-2-axis queries? I'd like visualize such a query...

    Thank you, -

    Tatyana

  • This is a great introduction to MDX world.

  • yanaty999 (3/2/2011)


    "MDX query can have up to 128 axes"… So I tried this:

    SELECT {[Measures].[Internet Sales Amount],

    [Measures].[Internet Order Count]} on axis(0)

    , {[Date].[Fiscal Year].members} on axis (1)

    , [Customer].[Country].members on axis(2)

    FROM [Adventure Works]

    And received an error: Results cannot be displayed for cellsets with more than two axes.

    So, are there additional rules for more-than-2-axis queries? I'd like visualize such a query...

    Thank you, -

    Tatyana

    Good observation. SSMS has a limitation. It cannot visualize cell sets beyond 2 axes. So, if the need arises, you will have a build your own application consuming the ADOMD.NET assemblies. This is the the only approach I can think of, until unless the MS itself starts offering a custom solution. All the best!!!

    Raunak J

  • I think what you are trying to do is getting [Internet Sales Amount] and [Internet Order Count] breaking down by all [Fiscal Year] and [Customer].[Country], so

    Try this:

    SELECT {[Measures].[Internet Sales Amount], [Measures].[Internet Order Count]} on axis 0,

    {[Date].[Fiscal Year].members} * {[Customer].[Country].members} on axis 1

    FROM [Adventure Works]

    If this is not what you are trying to do let me know.

  • Thank you, Alvaro. Yes, your query gives me a result I wanted. I just thought that this could be done, as well, with the "pages" axis (mentioned in the article). Now I see, from the Raunak's answer to my question, that SSMS's limitations would not allow to explicitly use the third (and up) dimension... Oh well 🙂

    Thank you!

    Tatyana

  • Looking for next in the series. Nice introducton to MDX. It helps a lot

  • I've relied on Mr. Pearson's Database Journal MDX and SSRS series for a while now, so I'm thrilled to see this series here - especially since it is targetted to 2008R2. About 90% of our reporting system uses SSRS to query cubes and I end up having to troubleshoot and clean up a lot of MDX that comes out of the GUI query designer in SSRS. I've love to see an article that takes on that topic.

    Thanks,

    MWise

  • Great article! I am looking forward to the future articles in the series. I wonder if power-pivot deals adequately with rendering axis beyond the second in excel. I worked with Proclarity back in 2005 and it had great rendering and building capability. I wrote a lot of MDX back then with functions, named sets, calculated members etc. However, being that I had to figure it out on my own I didn't do a great job of classifying it in my mind in a way that I could remember. I remember some of the functions being very counter intuitive and I am hopeful that this series will give me a better way to think about them.

  • Thanks for the series Bill.

    Thanks,

    Thomas LeBlanc

    TheSmilingDBA

    Thomas LeBlanc, MVP Data Platform Consultant

  • Just about to cube a new warehouse, so this couldn't have come at a better time! Thanks!

    -------------------------------------------------------------------------------------------------
    My SQL Server Blog

  • Nice intro.. good to read.

Viewing 15 posts - 1 through 15 (of 33 total)

You must be logged in to reply to this topic. Login to reply