MDX query to retrive dimension properties

  • I was hoping someone out there could help.  I have a parent/child dimension that has 3 columns - company_id, parent_id, company_name.  I have chosen company_id as my member key to join to my fact table.  I have also chosen company_name as my dimension display name. Parent_id is of course the parent key.

    I am trying to write an MDX query that will retrieve the company_id (i.e. the member key) into a recordset.  I have played with the "DIMENSION PROPERTIES" keywords, but seem to only be able to return the member key display name (i.e. company_name)  Any ideas?  Any help would be greatly appreciated:

     

    SELECT { [Measures].[FACT] } ON COLUMNS , { [dim_company].[All dim_company].CHILDREN } ON ROWS  FROM [MYCUBE]

     

    What can I add to the above MDX to get the dim_company company_id value?  Right now, it returns the company_name which is my display member.

  • A few things you can try.  The following is soem MDX that works for me (i translated back to your dim & measure names).  Alternatively, you can try out the 'hidden' function described here.  I think this is from BOL also (here).

    WITH

       MEMBER [Measures].[MyKeys] AS

       'Val([dim_company].CurrentMember.Properties("KEY"))'

    SELECT

       {[Measures].[MyKeys]} ON COLUMNS,

       {[dim_company].[All dim_Company].Children} ON ROWS

    From [MYCUBE]

    Steve.

  • Steve, thank you for your help.  Your suggested solution is definitely getting me closer.  However, I am receiving unexpected results.  I used your MDX as the basis for the following:

    WITH

       MEMBER [Measures].[MyKeys] AS

       'Val([dim_company].[(A1930)].Properties("ID"))'

    SELECT

       {[Measures].[MyKeys]} ON COLUMNS,

       {[dim_company].[(A1930)]} ON ROWS

    From [MyCube]

     

    A1930 is a company_id.  This is the company_name which is what I chose to display in the dimension.  The actual unique company_id (which is the member key) is something like 'STGC1930'.  When I put in "Key" in :

       'Val([dim_company].[(A1930)].Properties("key"))'

    I get zero.  I have tried "key", "name","uniquename", "uniquekey" and all return zero.  The only value that I get back is zero (I am using the MDX sample application to test this).  Only when I put in "ID" do I get a value - which is 4 and I have no idea where it is coming from.

    Thoughts?

  • Have you tried using the currentmember in the calculated member rather than explicitly naming the member?  This still works for me (naming the member) but when returning >1 dimension mmbers on the rows, they all get the same emasure value (the key integer from the selected member).

    This may have something to do with your dimensions uniqueness of keys.  What are the current settings?

    I think KEY is definitely what you're after.  The ID (in my cube data) appears to be a unique integer identifier for eveery member from within the dimension (ie 3 level dim, 3 members in each and an All  member results in the All Member being #1, the 3 members in levl1 being 2,3 & 4 respectively and so on down the dimension).

     

     

    Steve.

  • I modified and tried this:

    WITH

       MEMBER [Measures].[MyKeys] AS

       'Val([dim_company].CurrentMember.Properties("KEY"))'

    SELECT

       {[Measures].[MyKeys]} ON COLUMNS,

       {[dim_company].CurrentMember} ON ROWS

    From [MyCube]

    This also seems to return 0 for the dim_company key.

    As far as uniqueness properties, I have the following:

    Member Keys Unique = TRUE

    Memeber Names Unique = FALSE

    Allow Duplicate Names = TRUE

  • Hi Aneal,

    Are you able to test the following (basically the same mdx) against the Foodmart2000-Sales cube?  If this works then the issue is related your cube rather than the mdx.  As long as you haven't made any change to the sample cube then Audrey Osborn should be returned in the first 8 rows with a measure value of 50.  A quick check through the 'browse data' on the customers shared dim shows that is her actual key value.

    WITH

       MEMBER [Measures].[MyKeys] AS

       'Val([Customers].CurrentMember.Properties("KEY"))'

    SELECT

       {[Measures].[MyKeys]} ON COLUMNS,

      {[Customers].[All Customers].[USA].[CA].[Altadena].CHILDREN} ON ROWS

    From [Sales]

     

    Steve.

  • OK, I think I have it sorted.  The problem is using the 'Val' function tries to convert the property to a number, which i don't think your keys are (ie they aren't integers, they're concatenated ints with strings).  If you use the same MDX but change the Val function to CStr then it should work a treat.

    So, I'm guessing the following will work....

    WITH

       MEMBER [Measures].[MyKeys] AS

       'CStr([dim_company].CurrentMember.Properties("KEY"))'

    SELECT

       {[Measures].[MyKeys]} ON COLUMNS,

       {[dim_company].CurrentMember} ON ROWS

    From [MyCube]

    Steve.

  • WORKS LIKE A CHARM!!!

     

    Thanks for all of your help Steve!

    Regards,

    Aneal

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

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