Showing posts with label defaultmember. Show all posts
Showing posts with label defaultmember. Show all posts

Saturday, February 25, 2012

DefaultMember Slows Down Time Intelligence

I searched for this and found no web hits so I thought I'd post a warning here.

As of SP2, the function Hierarchy.DefaultMember is still not optimized very well in MDX expressions and queries. By replacing it with the literal member equivalent from the hierarchy (eg. Hierarchy.[Current Period]), I've been able to improve performance of my time intelligence queries by a scale of magnitude or more.

As a fair warning about the output from the time intelligence wizard, you need to fix the generated calculations in the following ways:

Get rid of DefaultMember references

Replace * operator for the CrossJoin() function

Hope these hints save others as much time as I've wasted discovering them. Please let me know if anybody else has general hints to improve the performance of time intelligence calculations.

Chris mentioned your post and gave some more guidelines at:

http://cwebbbi.spaces.live.com/Blog/cns!7B84B0F2C239489A!1076.entry

DefaultMember Slows Down Time Intelligence

I searched for this and found no web hits so I thought I'd post a warning here.

As of SP2, the function Hierarchy.DefaultMember is still not optimized very well in MDX expressions and queries. By replacing it with the literal member equivalent from the hierarchy (eg. Hierarchy.[Current Period]), I've been able to improve performance of my time intelligence queries by a scale of magnitude or more.

As a fair warning about the output from the time intelligence wizard, you need to fix the generated calculations in the following ways:

Get rid of DefaultMember references

Replace * operator for the CrossJoin() function

Hope these hints save others as much time as I've wasted discovering them. Please let me know if anybody else has general hints to improve the performance of time intelligence calculations.

Chris mentioned your post and gave some more guidelines at:

http://cwebbbi.spaces.live.com/Blog/cns!7B84B0F2C239489A!1076.entry

DefaultMember of Time Dimension (Analysis Services 2005)

I have a very small time dimension. This time dimension has only Year, Quarter, and Month (plus DateID which is just an identity column). I have one hierarchy for Y-Q-M. The DateID attribute is hidden.

I have the default member of the Date ID attribute set to:

Tail(NonEmptyCrossjoin([Date].[Date ID].Members, 1), 1).Item(0).Item(0)

This works if I do not choose any dates, meaning if my last date is December of 2006, I see the values for December 2006 by default. However, if I add the Month attribute into the browser as a row or a column, I only see October, November, and December (though every month has data)

If I remove the DefaultMember property, I can see all months data when I add the months to the columns or rows, but the data doesn't default to the month (without manually selecting a date) like I want it to. I tried removing the Y-Q-M hierarchy and just making a Y-M hierarchy, but still the same results.

I swear this was working once upon a time (maybe before the latest SP install?) ... I cannot recall exactly.

has anyone else experienced this? am I missing something?

Thanks,

Jason

Hi jsaido,

The behaviour of your solution is right, but unfortunatly is not right for you.

It's named autoexist.

Why you don't use Named sets?

For example:

CREATE SET CURRENTCUBE.[Current Date]

AS Tail(NonEmptyCrossJoin([Date].[Date ID].[Date ID].Members, 1), 1);

CREATE SET CURRENTCUBE.[Current Montht]

AS EXISTS([Date].[Month].[Month].members, [Current Date]);

CREATE SET CURRENTCUBE.[Current Quarter]

AS EXISTS([Date].[Quarter].[Quarter].members, [Current Date]);

CREATE SET CURRENTCUBE.[Current Year]

AS EXISTS([Date].[Year].[Year].members, [Current Date]);

|||I did try named sets - my delivery vehicle for this cube is Excel 2003 pivot tables which do not support named sets.

DefaultMember of Time Dimension (Analysis Services 2005)

I have a very small time dimension. This time dimension has only Year, Quarter, and Month (plus DateID which is just an identity column). I have one hierarchy for Y-Q-M. The DateID attribute is hidden.

I have the default member of the Date ID attribute set to:

Tail(NonEmptyCrossjoin([Date].[Date ID].Members, 1), 1).Item(0).Item(0)

This works if I do not choose any dates, meaning if my last date is December of 2006, I see the values for December 2006 by default. However, if I add the Month attribute into the browser as a row or a column, I only see October, November, and December (though every month has data)

If I remove the DefaultMember property, I can see all months data when I add the months to the columns or rows, but the data doesn't default to the month (without manually selecting a date) like I want it to. I tried removing the Y-Q-M hierarchy and just making a Y-M hierarchy, but still the same results.

I swear this was working once upon a time (maybe before the latest SP install?) ... I cannot recall exactly.

has anyone else experienced this? am I missing something?

Thanks,

Jason

Hi jsaido,

The behaviour of your solution is right, but unfortunatly is not right for you.

It's named autoexist.

Why you don't use Named sets?

For example:

CREATE SET CURRENTCUBE.[Current Date]

AS Tail(NonEmptyCrossJoin([Date].[Date ID].[Date ID].Members, 1), 1);

CREATE SET CURRENTCUBE.[Current Montht]

AS EXISTS([Date].[Month].[Month].members, [Current Date]);

CREATE SET CURRENTCUBE.[Current Quarter]

AS EXISTS([Date].[Quarter].[Quarter].members, [Current Date]);

CREATE SET CURRENTCUBE.[Current Year]

AS EXISTS([Date].[Year].[Year].members, [Current Date]);

|||I did try named sets - my delivery vehicle for this cube is Excel 2003 pivot tables which do not support named sets.