Post reply

fairly new to MDX: how to return a list of dimension members (one attribute)

  • July 23, 2010 at 10:41 am

    #141475

    Dear all,

    I did a bit of MDX a long time ago but I'm afraid it's pretty much all gone and I struggle a bit to get started...

    I am trying to return a list of (leaf) dimension members from a cube to store into a SQL Server table via SSIS.

    I suspect I should be able to fire an MDX within XMLA but can't even work out the MDX...

    Could you give me a hint or two, please?

    For instance, say I try to retrieve all the Customer FullNames from the Adventure Works Cube (ssas 2005 or 2008)...

    Thanks

    Eric

  • July 23, 2010 at 1:56 pm

    #1197745

    it has been a while since i have fired up AdventureWorks, but

    select

    [Customer].[Customer].Children on 0

    from [Adventure Works]

    will return the customer name for all customers in this rather flat hierachy, exluding the "ALL" customer. It appears in the 2008R2 release, Last Name is not part of the Customer dimension.

    Additionally, the query above will return for each customer, the default measure for this cube, which is Reseller Sales Amount. Because Reseller Sales Amount has no relation to the Customer dimension, and we have specified no other dimensional constraints, we get the Reseller Sales Amount value constrained by the default member of all dimensions related to that measure (measure group, actually).

    If we were to select a measure related to Customers, such as below, we would see the Internet Sales for each Customer.

    select [Measures].[Internet Sales Amount] on 0,

    [Customer].[Customer].Children on 1

    from [Adventure Works]

    Welcome back to Analysis Services and MDX!

Viewing 2 posts - 1 through 1 (of 1 total)

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