I have SSAS and SSRS 2008R2. The end goal is to get the report with Daily MarketValue for each Portfolio and Security Combination. MarketValue has SCOPE calculation to Select Last Existing for Date Dimension. If the SCOPE is removed the query still takes 6 min t complete. and with SCOPE statement it timeout after 1 hour. here is my query
SELECT
NON EMPTY
{[Measures].[MarketValue]} ON COLUMNS
,NON EMPTY
{
[Portfolio].[PortfolioName].[PortfolioName].ALLMEMBERS*
[Effective Date].[Effective Date].[Effective Date].ALLMEMBERS*
[Security].[Symbol].[Symbol].ALLMEMBERS
}
DIMENSION PROPERTIES
MEMBER_CAPTION
,MEMBER_UNIQUE_NAME
ON ROWS
FROM EzeDM
WHERE
(
[AsOn Date].[AsOn Date].&[2014-06-17T06:32:41.97]
,[GoldenCopy].[Frequency].&[Daily]
,[GoldenCopy].[GoldenCopyType].&[CitcoPLExposure]
,[GoldenCopy].[PointInTime].&[EOP]
,[GoldenCopy].[PositionType].&[Trade-Date]
);
The SCOPE statement I have for MarketValue Measure is
SCOPE
[Effective Date].[Effective Date].MEMBERS;
THIS =
Tail
(
(EXISTING
[Effective Date].[Effective Date].MEMBERS)
,1
).Item(0);
END SCOPE;
Security DIM has around 4K values. Portfolio DIM has around 100 Values and EffectiveDate DIM has around 400 values.
If I remove the EffectiveDate from the cross join the query is taking less than 2 seconds.
So far I have tried different combinations and found that the slowness is due to the cross join between DIM with large values in them. but then I am thinking is 4000 values in DIM is actually large? people must have done the same reporting efficiently right?
Is this a SCOPE calculation? If so why does it get slower only when EffectiveDate is in the cross join?
Appreciate any help.
EDIT:1 Adding some more details about the current environment if that helps : We do not have Enterprise version and currently we do not have any plans to ask our clients to upgrade to Enterprise version.
Security Dimension has around 40 attribute but 2 of them will always have data and at most "up to 6" may have any data. not sure if Attribute being not used in MDX query still affects the query performance "regardless it has data or not"
After reading the "Chris Webb" blog on MDX query improvements I notice the property is true for ALL Attributes in ALL Dimension.
"AttributeHierarchyEnabled = True"
For testing I have marked FALSE to all except currently I am using.
I do not have any aggregations defined on cube and I have started with building Aggregations using "Design Aggregations" wizard. after that I profile the same reporting query and didn't see any tick for "get data from Aggregations" event.
So currently I am working on preparing/testing "Usage Based Aggregation"
EDIT:2
So I created the log table with 50% logging sampling and ran 15-20 different reporting queries Client is expecting to run and saw some data in log table. I used the Wizard for Usage Based Aggregation and let SSAS finds out Estimated Row Count. it was strange that it did not generate any aggregations.
I also tried the approach of changing the Aggregation property to LastChild As Frank suggested and it worked great but then I realize I can not pick LastChild Value for MarketValue for all Dimension. it is Additive across Security Dimension but not across Time.
