I have a performance problem with the following query. I'm using [Twelve Months to Date] on on my columns and also as a filter for NONEMPTY on my rows. It takes about 15 secs to run on AW and much longer on the cube that I'm developing. Any ideas on how I can speed this up?
Code Snippet
WITH MEMBER
[Date].[Fiscal Date Calculations].[Twelve Months to Date]
AS
Aggregate(
{ [Date].[Fiscal Date Calculations].DefaultMember } *
{
ParallelPeriod(
[Date].[Fiscal].[Month],
11,
[Date].[Fiscal].CurrentMember
) : [Date].[Fiscal].CurrentMember
}
)
SELECT
(
{
{[Product].[Category].&[1]} *
{[Product].[Subcategory].&[3]}
}
,
[Date].[Fiscal Date Calculations].[Twelve Months to Date]
)
+
(
{
{[Product].[Category].&[2]} *
{[Product].[Subcategory].&[5]}
}
,
[Date].[Fiscal Date Calculations].[Twelve Months to Date]
)
ON 0,
NONEMPTY
(
[Customer].[Customer].children,
[Date].[Fiscal Date Calculations].[Twelve Months to Date]
)
ON 1
FROM
[Adventure Works]
WHERE
(
[Date].[Fiscal].[Date].&[1078],
[Measures].[Internet Sales Amount]
)
This version of the Adventure Works query runs in 6 seconds (I used [Day Of Week] to hold the calculated member, since [Fiscal Date Calculations] isn't a standard attribute in Adventure Works):
Code Snippet
WITH MEMBER
[Date].[Day Of Week].[Twelve Months to Date]
AS
Aggregate(
{ [Date].[Day Of Week].DefaultMember } *
{
ParallelPeriod(
[Date].[Fiscal].[Month],
11,
[Date].[Fiscal].CurrentMember
) : [Date].[Fiscal].CurrentMember
}
)
SELECT
{(
[Product].[Category].&[1],
[Product].[Subcategory].&[3]
)
,
(
[Product].[Category].&[2],
[Product].[Subcategory].&[5]
)}
*
{[Date].[Day Of Week].[Twelve Months to Date]}
ON 0,
NONEMPTY
(
[Customer].[Customer].children,
{ParallelPeriod(
[Date].[Fiscal].[Month],
11,
[Date].[Fiscal].CurrentMember
) : [Date].[Fiscal].CurrentMember} *
{(
[Product].[Category].&[1],
[Product].[Subcategory].&[3]
)
,
(
[Product].[Category].&[2],
[Product].[Subcategory].&[5]
)}
)
ON 1
FROM
[Adventure Works]
WHERE
(
[Date].[Fiscal].[Date].&[1078],
[Measures].[Internet Sales Amount]
)
|||Thanks Deepak - this did give me a pointer which has meant that I've been able to reduce the time for the query I'm developing.
No comments:
Post a Comment