Stairway to MDX - STEP 1: Getting Started with MDX

  • Bill Pearson

    Ten Centuries

    Points: 1359

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

  • Raunak Jhawar

    SSCoach

    Points: 15701

    Brilliant Read!!!

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

    Regards/Raunak
    Now a member of Linkedin

    Please visit the all new Performance Point Forum
    Please visit the all new Data Mining and Business Analytics Forum

  • Dwayne Dibley

    SSCrazy

    Points: 2350

    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.

  • sharath.chalamgari

    SSCertifiable

    Points: 5680

    Great article, i am looking forward for the series

  • yanaty999

    SSC Enthusiast

    Points: 114

    "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

  • Alvaro Reis

    Valued Member

    Points: 72

    This is a great introduction to MDX world.

  • Raunak Jhawar

    SSCoach

    Points: 15701

    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!!!

    Regards/Raunak
    Now a member of Linkedin

    Please visit the all new Performance Point Forum
    Please visit the all new Data Mining and Business Analytics Forum

  • Alvaro Reis

    Valued Member

    Points: 72

    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.

  • yanaty999

    SSC Enthusiast

    Points: 114

    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

  • nmrchalla

    SSC Rookie

    Points: 41

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

  • MWise

    SSCarpal Tunnel

    Points: 4074

    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

  • Toby White

    SSCertifiable

    Points: 6127

    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.

  • Thomas LeBlanc

    SSCertifiable

    Points: 7750

    Thanks for the series Bill.

    Thanks,

    Thomas LeBlanc

    TheSmilingDBA

    Thomas LeBlanc, MVP Data Platform Consultant

  • amenjonathan

    SSCrazy

    Points: 2422

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

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

  • Anipaul

    SSC-Insane

    Points: 24681

    Nice intro.. good to read.

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

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