Using SSAS Formula Engine Cache with Non-Deterministic Functions

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