Hello everyone....thanks in advance for your feedback.

I've got a few MDX queries that take into account a mixture of
calculated members, basic dimensions, and basic facts. The cube is not
'big', the dimensions are not 'large' - in fact the largest dimension I
have is the time dimension. But, the query below, for example runs in
about 4 seconds. (dual proc, 8GB RAM, 3GB switch on, etc.)

I think the calculated members in the query, in combination with the
'where' clause are causing the problem, so I was hoping that I could
'prime' the cache in AS to get some of this ready prior to query time.
I've read some things about subcube, scope, etc. but didn't quite feel
they were applicable - maybe they are. Does it make sense in this case
to execute 'something' that will at least get the dimensions into
memory prior to query time? Would that help? Or maybe be query is
just written ultra-inefficiently.



WITH
MEMBER [Measures].[Median] AS 'iif(([Dim KPI].[KPI
Name].currentmember, [Measures].[Is Corp Measure]) = 0,Median([Dim
Date].[Short Date].members, [KPI Actual DASHBOARD]), 1)'
SET [Dim DateShort Date(All)] As 'StrtoSet("[Dim Date].[Short
Date].&[" + cstr(DateDiff("d", "01/01/1997", Now())-32) + "]: [Dim
Date].[Short Date].&[" + cstr(DateDiff("d", "01/01/1997", Now()+31)) +
"]")'
SELECT
NON EMPTY {[Measures].[GraphKPI],[Measures].[Median]} ON COLUMNS,
NON EMPTY {[Dim DateShort Date(All)]} ON ROWS
FROM
[db]
WHERE
([Dim KPI].[KPI Name].&[No % CHK],[Dim
Location].[LocationFilter].[Region].&[02 - REGION],[Dim
Date].[DateFilter].[Year].&[2006].&[2].&[5],[Dim
Product].[PR-SubPr].[All])

Thanks,
Dean

Hi Dean,

Not knowing the complete context of your cube, I'm assuming that
[Measures].[Is Corp Measure] is being used to determine whether the
current KPI falls in the corporate category. If so, you'd be better off
defining a Dim KPI attribute for that purpose. If you can make
[Measures].[Median] a cube calculated measure, then you could possibly
scope on such an attribute in the cube MDX script, rather than using
iif().

Also, though I'm not sure exactly how [Measures].[Median] works, it
looks like it should return the same value across all members of [Dim
DateShort Date(All)]. But maybe the iif() condition is preventing the
caching of this value - someone with deeper knowledge of AS 2005 caching
could perhaps confirm this?


- Deepak

Deepak Puri
Microsoft MVP - SQL Server

*** Sent via Developersdex http://www.developersdex.com ***