Dim design / MDX help

I have two queries one runs very fast and other very slow. The difference is very little is both. I must be doing some thing wrong is dimension design but not able to solve it.

[Eq up 2sd %] - is a calculated member which I bring to Pivot table to test fast and slow running queries.

When I look into profiler fast query has some events "Query Subcube" and then end query but for slow one I get "Query Subcube" then there is pause with Notification - Flight Recorder Snapshot begin then Server state discover begin/end and same group again and again.

My Instrument Sector Dim has attributes (attributes -> <relation>)

Instrument Desc -> Issuer Desc, Issuer Desc -> Sub Ind cd, Sub Ind cd -> Ind Cd, Ind Cd -> Instrument Sector.

Instrument Desc is Key with Instrument ID as Key field

Query is not only slow it max out cpu 100% too.

Fast Query

SELECT {[Measures].[Gghm Hldng],

[Measures].[Eq dn 50p],

[Measures].[Eq up 2sd %]}

DIMENSION PROPERTIES PARENT_UNIQUE_NAME ON COLUMNS ,

NON EMPTY CROSSJOIN(HIERARCHIZE(AddCalculatedMembers({DrillDownLevel({[Instrument Sector].[Sector Cd 1].[All]})})), HIERARCHIZE(AddCalculatedMembers({DrillDownLevel({[Instrument Sector].[Issuer Desc 1].[All]})})))

DIMENSION PROPERTIES PARENT_UNIQUE_NAME ON ROWS

FROM [Position Scenario P and L]

WHERE ([Instrument].[Asset Type Cd].[All], [Investment].[Investment].[Investment Cd].&[228], [Date].[Date].[Year].&[2007].[Quarter 1].[February].[02/21/2007])

Slow Query

SELECT {[Measures].[Gghm Hldng],

[Measures].[Eq dn 50p],

[Measures].[Eq dn 2sd %]}

DIMENSION PROPERTIES PARENT_UNIQUE_NAME ON COLUMNS ,

NON EMPTY CROSSJOIN(CROSSJOIN(HIERARCHIZE(AddCalculatedMembers({DrillDownLevel({[Instrument Sector].[Sector Cd 1].[All]})})),

HIERARCHIZE(AddCalculatedMembers({DrillDownLevel({[Instrument Sector].[Issuer Desc 1].[All]})}))),

HIERARCHIZE(AddCalculatedMembers({DrillDownLevel({[Instrument Sector].[Instrument Desc 1].[All]})})))

DIMENSION PROPERTIES PARENT_UNIQUE_NAME ON ROWS

FROM [Position Scenario P and L]

WHERE ([Instrument].[Asset Type Cd].[All], [Investment].[Investment].[Investment Cd].&[228], [Date].[Date].[Year].&[2007].[Quarter 1].[February].[02/21/2007])

Thanks guys - Ashok

--

Just to add more in my question, when l look into new performance guide there is example of dim like following

Product Key (PK) -> SubCategory -> Category
-> Size -> Size Range
-> Color
-> Description

In my case I am selecting in my query Category, Product Name (Name of key field), and Size Range. This is creating CROSSJOIN(CROSSJOIN 2 of them in MDX for Category and Size Range.

In my cube I have Issuer Desc 1, Instrument Desc 1 in place of Category and Size Range

Hi Ashok,

Could you clarify the following points, to help better understand your scenario?

What's the MDX expression for [Measures].[Eq dn 2sd %] - this could be crucial?|||

Thanks Deepak.

I think the problem was I didn't set "Non-empty behavior" in calculated member.

"Eq dn 2sd %" is a calculated member. I did notice some issues in my Dim design. I am in the middle of final testing I will confirm it has fixed or not today.

Based in my experience the use of "Non-empty behavior" in calculated member should be highlighted in Performance document for OLAP. It worked like magic in query difference but again allow me to confirm that by end of today.

-Ashok

confirmed ! it's "Non-empty behavior" in calculated member