One of my clients was running into a problem with migrating their reports to the DQM. The reports took 15 seconds to run in Compatible Mode, but in DQM failed with the XQE-PLN-0001 error:
Even by Cognos standards this is a bit obscure.
After a bit of troubleshooting I narrowed the problem to how they were dealing with Essbase Global Variables. When global variables were placed in the slicers, they functioned correctly and the mdx looked clean. Cognos put them into tuples exactly as I would expect. When nested in crosstabs they also worked. They can’t be put into tuple expressions. tuple([cube].[Measure],[cube].[Global Variable]) will return an error.
In the compatible mode the client found a work-around with item([cube].[Global Variable],0). This essentially turns the variable into a member. This worked and the report took 15 seconds.
Dynamic Query Mode has a much stricter parser. Since the global variables are neither sets or members, it is very difficult to use them. We can, however, trick Cognos into using them as members by converting them to calculated members with a specified hierarchy.
Changing
tuple(
[Fm4].[Scenario].[Scenario]->:[DO].[Scenario].[Actual]
, item([Fm4].[CED_ActualVer_Reports],0)
, item([Fm4].[CED_ActCurrentYear],0)
, item([Fm4].[CED_ActualRecentlyClosedQtr],0)
)
to
tuple(
[Fm4].[Scenario].[Scenario]->:[DO].[Scenario].[Actual]
, member(total(currentMeasure within set [Fm4].[CED_ActualVer_Reports]), 'a','a',[Fm4].[Version].[Version])
, member(total(currentMeasure within set [Fm4].[CED_ActCurrentYear]), 'b','b',[Fm4].[Year].[Year])
, member(total(currentMeasure within set [Fm4].[CED_ActualRecentlyClosedQtr]), 'c','c',[Fm4].[Period].[Period])
)
completely fixed the problem. As a benchmark test, the client took the changes I made and applied them to the Compatible Query Mode. In the CQM the report took 10 seconds (from 15). The DQM ran the report in under a second. It took a few trials to convince some people that the report was actually running and not being retrieved from cache.
The member function takes the parameters
1. value expression (a number, a measure, a total within a set.
2. ID (must be unique in the entire query)
3. Caption (what is displayed if the calculated member is used in an edge)
4. Hierarchy (generally optional, this explicitly sets the hierarchy of the calculated member. If no hierarchy is selected it wil default to the measures.
It’s worth noting the summary function I used was total(). When working with non-additive measures, it is recommended to use the aggregate function or the explicit summary function for the particular measure.
When using this method to create tuples, it is very important to correctly plan the query. A tuple cannot have two parameters from the same hierarchy, this includes measures. Conceptually it is easy to understand that, as you cannot find the intersection between two points on the same line. As mentioned before if no hierarchy is selected in the member function, it will default to the measures. In the case of a tuple, measures are considered part of a hierarchy and the same limitation applies. You cannot find the intersection between Quantity of Sales and Profit Margin, for instance. As such, some analysis of the query and Global Variables must be done before you can create the calculated member.
I created a test crosstab with the various Global Variables, and looked at the MDX (Tools–>Show Generated SQL/MDX). The crosstab has Actual Results (a member), CED_ActualVer_Reports (Global Variable), CED_ActCurrentYear (Global Variable), and CED_ActualRecentlyClosedQtr (Global Variable) nested in the columns and the measure Net Revenue in the rows.
SELECT
{([Scenario].[Actual], [Version].[OutsidePresentations], [Year].[FY11], [Period].[YearTotal].[Q4])} DIMENSION PROPERTIES [Original], LEVEL_NUMBER, GEN_NUMBER, MEMBER_ALIAS, ANCESTOR_NAMES ON AXIS(0),
{[Account].[Chart of Accounts].[ac_NET_INCOME].[ac_INC_AFTER_TAX].[ac_INC_BEFORE_TAX].[ac_INC_OPERATING].[ac_GROSS_PROFIT].[ac_SALES]} DIMENSION PROPERTIES [Original], LEVEL_NUMBER, GEN_NUMBER, MEMBER_ALIAS, ANCESTOR_NAMES ON AXIS(1)
FROM [CED.Fm]
WHERE ([ActivityType].[at_TotalActivityTypeDim], [Geography].[mk_TotalMarketDim], [Entity].[Global BUs], [Company].[co_GP_CFO])
Using this I can see that CED_ActualVer_Reports comes from the Version hierarchy, CED_ActCurrentYear from the Year and CED_ActualRecentlyClosedQtr from the Period. The calculated members are then created accordingly and Cognos treats them as proper members from the hierarchy.
The same crosstab, when using the member function, looks like this:
WITH
MEMBER [Version].[XQE_V5M_a_CM2] AS '([Version].[OutsidePresentations])', SOLVE_ORDER = 4
MEMBER [Year].[XQE_V5M_b_CM1] AS '([Year].[FY11])', SOLVE_ORDER = 4
MEMBER [Period].[XQE_V5M_c_CM0] AS '([Period].[YearTotal].[Q4])', SOLVE_ORDER = 4
MEMBER [Scenario].[XQE_V5M_Act Current Year_CM4] AS '([Scenario].[Actual], [Version].[XQE_V5M_a_CM2], [Year].[XQE_V5M_b_CM1], [Period].[XQE_V5M_c_CM0])', SOLVE_ORDER = 4
SELECT
{[Scenario].[XQE_V5M_Act Current Year_CM4]} DIMENSION PROPERTIES [Original], LEVEL_NUMBER, GEN_NUMBER, MEMBER_ALIAS, ANCESTOR_NAMES ON AXIS(0),
{[Account].[Chart of Accounts].[ac_NET_INCOME].[ac_INC_AFTER_TAX].[ac_INC_BEFORE_TAX].[ac_INC_OPERATING].[ac_GROSS_PROFIT].[ac_SALES]} DIMENSION PROPERTIES [Original], LEVEL_NUMBER, GEN_NUMBER, MEMBER_ALIAS, ANCESTOR_NAMES ON AXIS(1)
FROM [CED.Fm]
WHERE ([Company].[co_GP_CFO], [ActivityType].[at_TotalActivityTypeDim], [Geography].[mk_TotalMarketDim], [Entity].[Global BUs])
It is important to note that family functions will not work correctly with calculated members. If they work at all, without returning an error, you may get unpredictable results.
