July 14, 2005 at 8:53 am
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.
July 14, 2005 at 5:52 pm
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.
July 18, 2005 at 9:32 am
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?
July 18, 2005 at 5:22 pm
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.
July 19, 2005 at 8:25 am
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
July 19, 2005 at 6:05 pm
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.
July 19, 2005 at 6:07 pm
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.
July 20, 2005 at 8:20 am
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
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy