If you have complex MDX calculations which take long time to
execute, you might be able to exploit formula engine (FE) cache and get good
performance.
The following diagram from “Microsoft
SQL Server Analysis Services Multidimensional Performance and Operations Guide”
gives overview of query processing.
If calculation results are available in formula engine
cache, the values are returned immediately.
If calculation results are available in formula engine
cache, the values are returned immediately.
The use on Non-Deterministic functions (e.g. Now() ), causes FE cache to be not used. Instead, storage
engine cache is used instead and calculations are re-evaluated.
As a work around, you can create a static named set using
the Non-Deterministic function and use the named set in the calculation inside
MDX script.
// Create Static Named Set.
CREATE SET CurrentCube.[Last
Completed Month TY] as
(StrToMember("[Date of
Service].[Calendar].[DateID].&[" +
Format(DateAdd("d",-Day(Now()),Now()),
"yyyyMMdd") + "]"));
// Use the named set in calculation.
CREATE MEMBER CURRENTCUBE.measures.[office
visits new - ty]
AS (
[Measures].[Office Visits - New],
[Date of Service
Comparison].[Aggregation].[Year To Date] ,
[Last
Completed Month TY].Item(0)
),
VISIBLE = 1 ;
The above calculation uses a date-tool
dimension called “Date of Service Comparison” with a member “Year To
Date”
// Using date-tool dimension.
SCOPE (
[Date of Service].[Calendar].MEMBERS,
[Date of Service].[DateID].MEMBERS );
///////////////////////////////////////////////////////////////////////////////////////
[Date of Service
Comparison].[Aggregation].[Year To Date]
= Aggregate(
{ [Date of Service
Comparison].[Aggregation].DefaultMember } *
PeriodsToDate(
[Date of
Service].[Calendar].[Year Number],
[Date of
Service].[Calendar].CurrentMember
) );
END SCOPE;
Note: To use FE cache the calculations should be done in MDX
Script in the cube, If the Calculations are done in MDX Query (using WITH
Clause), they are re-evaluated every time.
To get the calculation results into FE cache, execute an MDX
query which uses the calculation (after cube is processed).
SELECT measures.[My Complex Calculated
Measure] FROM MYCUBE
