OAC - BI with MDX

At KScope15 I attended a session presented by Mike Nader titled "Finding a Needle in a Haystack - Aggregate to Transaction-Level Financial Reporting Using Essbase and OBIEE"

In this session he talked about using a Driver query as a source for a filter.  The driver query made use of a MDX function and the EVALUATE clause within the Criteria section of an Analysis.  The slide from his session that this post is based on is shown here.

 
This presentation and concept has lingered in the dark recesses of my brain and recently when trying to create a filter for a range of members in my Time Dimension it bubbled back up.

So a bit of set up is needed.  First I have an OAC BI sourcing data from OAC Essbase.  My cube has a unified Time Dimension...That means my Year and Period are one dimension instead of 2 separate so it looks like this:

While for many Essbase developers this is not a typical design; it is how OBIEE likes to look at Time and makes for Time Series reporting a lot easier.


If this was a relational database I could easily get the a list of past 6 quarters via SQL.  But how can i get a range of months from an Essbase Cube?

EVALUATE('{[FY17_Q3].lag(5):[FY17_Q3]}/*%1*/',"Time"."Gen3,Time")

Formula syntax is invalid.
[nQSError: 10058] A general error has occurred. (HY000)
[nQSError: 43113] Message returned from OBIS. EVALUATE_SUPPORT_LEVEL inside NQSConfig.INI is not set to support EVALUATE. (HY000)
SQL Issued: SELECT EVALUATE('{[FY17_Q3].lag(5):[FY17_Q3]}/*%1*/',"Time"."Gen3,Time") FROM "ESSB_MapTest"
OK (Ignore Error)

 

Make a back up of the NQSConfig.INI and download a copy

Edit the entry EVALUATE_SUPPORT_LEVEL. 

Set the value equal to 2 so that all users can use the results of the function call.  Upload the revised NQSConfig.INI and restart the BI Services via the scripts provided in your OAC instance.

So now when I re-run the query I get my current Quarter and past 5.

This will be useful when combined with a Substitution Variable for Current Quarter.