September 4, 2008 at 2:44 am
I have a time dimension that is called “Date” with the hierarchy Year – Month – Date. With a key of YYYY – YYYYMM – YYYYMMDD
When querying a specific date (2/1/2004) in Proclarity, the auto generated MDX query references the date with this syntax – [Date].[Date].&[20040102]
The hierarchy properties are as follows –
AllowDuplicateNames = True
MemberKeysUnique = Unique
MemberNamesUnique = False
However when I set the hierarchy property to MemberNamesUnique = True I get the following MDX - [Date].[Date].[Fri 02/01/2004]. Fri 02/01/2004 is the name column and has a property of MemberNamesUnique = True (it returns the same result even when False).
To be honest I was expecting [Date].[Date].[20040102] as I would have thought the query would run faster on a key rather than the name.
With that in mind does anyone have a reason as to why Proclarity or AS2005 is using the name column in the query rather than the key column and how does this impact performance.
Another question is is there any documentation out there that describes why the “&” is used when MemberNamesUnique = False and the impact on performance in changing it to True.
September 7, 2008 at 11:52 pm
The & denotes that what follows is the key value.
I am guessing that the reason you are seeing [Fri 02/01/2004] is that your attribute has a "name" column, which is the date field. If you would prefer to see the key [20040102] then you should remove the "name" attribute from the definition. If you don't have a name column, then AS will use a character representation of the key value.
Also, I would encourage you to have AllowDuplicateNames=False and MemberNamesUnique=True. This will make your time dimension a little more efficient.
Regards,
Richard
September 8, 2008 at 12:58 am
Thanks for that.
I don’t understand why it would use the “name” attribute [Fri 02/01/2004] as part of the query rather than the actual key [20040102]. I know why it does (MemberNamesUnique=True) but am under the impression using the key would be more efficient.
Do you have any idea of the behind the scenes impact of the “&”. Because [Date].[Date].&[20040102] and [Date].[Date].[Fri 02/01/2004] return the same data does [Date].[Date].&[20040102] have to perform more functions as it cycles through the possible DuplicateNames?
September 8, 2008 at 10:36 pm
That's a good question. And since I have written a web cube browser (ThinSlicer) I can understand that it is easier to use the display name in the where clause. Although, that does not make it right, and as you suggest, even if the name is unique, it is not as efficient as the key.
I think you need to ask "Proclarity" why they use the name and not the key. I have worked around similar issues by creating a key that serves as a key and display. eg [2004-03-01]. You will be told that numeric keys are more efficient than character keys, but there is also some efficiency in using the same column for key and name.
Richard
September 9, 2008 at 1:29 am
Thanks for the info. 🙂
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply