Essbase Global Variables and DQM

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.

Advertisements

Accessing Tree Prompts with Javascript (on Cognos 10.1.1)

There are many reasons why you may want to interact with a Tree Prompt with JavaScript. Maybe you want to enable the finish button if a member on the bottom level is selected, or to select the last member, or to ensure only 5 members are selected.

This post isn’t to detail every possible scenario, but to detail the some functions available and how to use them. It’s important to note that I am hardly a JavaScript expert, so there may be better ways to do anything I say here.

First you need to identify your tree prompt. Unlike most prompt controls, where the identifier changes based on the type viewer being used, the tree prompt can be called using window.treePROMPTNAME. Unfortunately we can’t apply an onmousedown event to the tree prompt, so we have to wrap it in a div.

Createa tree prompt and give it the name “Time”. Drag an HTML item to the left of the tree prompt

<div id='myTree'>

and an HTML item to the right

</div>

.

Now we can attach an event to capture the clicks:

<script>
document.getElementById('myTree').onmousedown=function(){runTree('Time')};
</script>

Any click inside that div will now trigger the runTree function passing ‘Time’ as an argument.

Because there are a number of JavaScript functions are run upon selecting an element we can’t immediately get the value of the element. So we can use the setTimeout function to wait 200 milliseconds before getting the data.

<script>
function runTree(id)
{
 t=setTimeout('checkTree("'+id+'")',200);
}
</script>

After 200 milliseconds the checkTree function will run, also passing Time as the argument.

<script>
function checkTree(id)
{
  selectedTreeNode  = window['tree'+id].getLastSelectedNode();
  if(!selectedTreeNode) {return}
  alert(selectedTreeNode.getName());
  alert(selectedTreeNode.getValue());
  alert(selectedTreeNode.getLevel());

}
</script>

The checkTree function will now alert the selected elements Name, MUN, and Tree level. Note the Tree Level is from the tree prompt, not the member’s hierarchy level. But knowing these, we can then call other functions. You could check the level number of the selected element and enable or disable the tree prompt while popping up a message.

You can programmatically set the default value of the tree prompt using JavaScript. Unfortunately it appears it is only possible to do this on the first level.

<script>
var node = window.treeTime.getRootNode().getChildren()[window.treeTime.getRootNode().getChildren().length-1];
node.setSelected(true);
node.updateNodeSelection();
node.updateParent();
window.treeTime.setLastSelectedNode(node);
</script>

This will only effect the prompt after the page has been loaded. Prompt pages should be fine, but prompts on the report page will need to have a default value set in the prompt macro.

I learned about these functions by going through the js file associated with tree prompts. Check out ..\webcontent\prompting\CTreeIE5NS6.js for more Tree Prompt functions.

It worth noting that these functions are written by IBM, and are liable to change on upgrade. I’d be interested in hearing if these work in any of the previous versions of Cognos.

Quickie: Generate function (first N months of years)

Problem: How do you return the first N months of each year in the time hierarchy? It must be dynamic (no static sets, because who wants to go back every year and fix it?), and the user wants to be able to select the number of months that are displayed.

Solution:

generate
(
   [great_outdoors_company].[Years].[Years].[Year]
   , head
     (
        descendants
        (
           currentMember([great_outdoors_company].[Years].[Years])
           , [great_outdoors_company].[Years].[Years].[Month])
     , #prompt('n','integer')#
     )
)

The generate function will evaluate the head(descendants(currentMember)) expression for each member in the years level.

The importance of using Report Expressions

Today I was asked to analyze a series of reports to improve performance. All of the queries were built on a very simple Power Cube. Three dimensions and two measures. Dimensions are: Time, Plants, KPIs. Measures: Actual, Target

One of the things that struck me was the rampant use of string functions on attributes. The attributes of each KPI were used, and since it’s a Power Cube they couldn’t add any more. An example Long Name: 001 – Items Sold. In some reports they needed to show the entire Long Name, some they needed to show only the number, and some only the name itself.

A simple crosstab might have KPIs in the rows, plants in the column, actual as the measure and a specific month in the slicer. The MDX would appear as:

SELECT [Plant]..[Plant 2].MEMBERS DIMENSION PROPERTIES PARENT_LEVEL ON AXIS(0), [KPI]..[KPI 2].MEMBERS DIMENSION PROPERTIES PARENT_LEVEL ON AXIS(1), {[MEASURES]..[Actual Value]} DIMENSION PROPERTIES PARENT_LEVEL ON AXIS(2) FROM [Global_Operations_KPIs] WHERE ([Date].[Current Month].[@MEMBER].[Current Month])

Simple and to the point. The problem happens when they want to truncate the Long Name of the KPI to show only the first 3 characters. They use the expression: substring([Cube].[KPI].[KPI].[KPI1].[KPI – Long Name],1,3) and the resulting MDX is:

SELECT [Plant]..[Plant1].MEMBERS DIMENSION PROPERTIES PARENT_LEVEL ON AXIS(0), [KPI]..[KPI1].MEMBERS DIMENSION PROPERTIES PARENT_LEVEL, [KPI].[KPI1].[substring(Cube_KPI_KPI_KPI1_KPI – Long Name from 1 for 3)0] ON AXIS(1), {[MEASURES]..[Actual Value]} DIMENSION PROPERTIES PARENT_LEVEL ON AXIS(2) FROM [F:/Program Files/cognos/c8\./bin\../temp\dmbTemp_2712_0002_12910169730.dmc] WHERE ([Date]..[Current Month (Root)].[Current Month])

Power Cubes don’t support string operations, so Cognos is dumping the resultset into a temp file. After it’s done dumping it into the file it runs Cognos SQL on top of it:

with
Query1_0_tab_oqpTabStream4 as
(select
Query1_0_tab_oqpTabStream.”Current Monthkey”  as  Current_Monthkey,
Query1_0_tab_oqpTabStream.”Current Month0″  as  Current_Month0,
Query1_0_tab_oqpTabStream.Plant0key  as  Plant0key,
Query1_0_tab_oqpTabStream.Plant1  as  Plant1,
Query1_0_tab_oqpTabStream.Plant1key  as  Plant1key,
Query1_0_tab_oqpTabStream.Plant10  as  Plant10,
Query1_0_tab_oqpTabStream.KPI0key  as  KPI0key,
Query1_0_tab_oqpTabStream.KPI1  as  KPI1,
Query1_0_tab_oqpTabStream.KPI1key  as  KPI1key,
Query1_0_tab_oqpTabStream.KPI10  as  KPI10,
Query1_0_tab_oqpTabStream.”substring(Cube_KPI_KPI_KPI1_KPI – Long Name from 1 for 3)01″  as  c11,
Query1_0_tab_oqpTabStream.”Actual Value”  as  Actual_Value
from
TABLE(_ROWSET(“Query1.0_tab_oqpTabStream”)) Query1_0_tab_oqpTabStream
)
select
Query1_0_tab_oqpTabStream4.Current_Monthkey  as  Current_Monthkey,
Query1_0_tab_oqpTabStream4.Current_Month0  as  Current_Month0,
Query1_0_tab_oqpTabStream4.Plant0key  as  Plant0key,
Query1_0_tab_oqpTabStream4.Plant1  as  Plant1,
Query1_0_tab_oqpTabStream4.Plant1key  as  Plant1key,
Query1_0_tab_oqpTabStream4.Plant10  as  Plant10,
Query1_0_tab_oqpTabStream4.KPI0key  as  KPI0key,
Query1_0_tab_oqpTabStream4.KPI1  as  KPI1,
Query1_0_tab_oqpTabStream4.KPI1key  as  KPI1key,
Query1_0_tab_oqpTabStream4.KPI10  as  KPI10,
substring(Query1_0_tab_oqpTabStream4.c11 from 1 for 3)  as  c11,
XSUM(Query1_0_tab_oqpTabStream4.Actual_Value  for Query1_0_tab_oqpTabStream4.Current_Monthkey,Query1_0_tab_oqpTabStream4.Current_Month0,Query1_0_tab_oqpTabStream4.Plant0key,Query1_0_tab_oqpTabStream4.Plant1,Query1_0_tab_oqpTabStream4.Plant1key,Query1_0_tab_oqpTabStream4.Plant10,Query1_0_tab_oqpTabStream4.KPI0key,Query1_0_tab_oqpTabStream4.KPI1,Query1_0_tab_oqpTabStream4.KPI1key,Query1_0_tab_oqpTabStream4.KPI10,substring(Query1_0_tab_oqpTabStream4.c11 from 1 for 3) )  as  Actual_Value
from
Query1_0_tab_oqpTabStream4
group by
Query1_0_tab_oqpTabStream4.Current_Monthkey,
Query1_0_tab_oqpTabStream4.Current_Month0,
Query1_0_tab_oqpTabStream4.Plant0key,
Query1_0_tab_oqpTabStream4.Plant1,
Query1_0_tab_oqpTabStream4.Plant1key,
Query1_0_tab_oqpTabStream4.Plant10,
Query1_0_tab_oqpTabStream4.KPI0key,
Query1_0_tab_oqpTabStream4.KPI1,
Query1_0_tab_oqpTabStream4.KPI1key,
Query1_0_tab_oqpTabStream4.KPI10,
substring(Query1_0_tab_oqpTabStream4.c11 from 1 for 3)

That’s a lot of effort for a simple substring.

Instead they could have used a Report Expression. On the crosstab node change the Source Type from Data Item Value to Report Expression. Use the expression: substring([Query1].[KPI – Long Name],1,3) remembering to add Long Name to the properties of that row.

When Cognos executes the report the MDX is untouched. Cognos will only execute the substring function when it draws the results on the crosstab. This allows for much more efficient reports, while still providing you with the results you expect.

Running-total on a cube

The concept of a running-total is relational concept.  Since a relational table can be visualized in only two dimensions it is very easy to understand and visualize running and moving aggregates.

A multidimensional data source, on the other hand, is more complex.  You might want the running aggregate on one dimension but not another. When attempting to use the running aggregate functions on a cube you cannot guarantee consistent results, drilldowns might behave unexpectedly, and the entire processing time is increased as the resultset must be processed locally.

Fortunately running and moving aggregates can be handled with purely dimensional functions.

Consider the following request. A line graph that shows the running total of sales for the current and previous years.  The ordinal axis shows all months of the current year.

To start create a new line graph report.  Create a Query Calculation in the Category area with the following expression (replacing with correct values from your own cube):

descendants ([Cube].[Date].[Date].[Year]->:[PC].[@MEMBER].[20100101-20101231],[Cube].[Date].[Date].[Month] )

This will return the Month descendants of 2010  (see a previous post on a better way of finding the current year).

Create a Query Expression in the series with the following expression:

total([Cube].[Measures].[Sales] within set periodsToDate([Cube].[Date].[Date].[Year],currentMember([Cube].[Date].[Date])))

Run the report. If all goes well you should see the line growing as expected.

It is important to understand the mechanism. The expression in the series is processed for each item in the ordinal axis.  The currentMember function will take the member that’s being evaluated. So far January 1 the expression would appear as:

total([Cube].[Measures].[Sales] within set periodsToDate([Cube].[Date].[Date].[Year],[Cube].[Date].[Date].[Month]->:[PC].[@MEMBER].[20100101-20100131]))

When it gets to the October category it will appear as:

total([Cube].[Measures].[Sales] within set periodsToDate([Cube].[Date].[Date].[Year],[Cube].[Date].[Date].[Month]->:[PC].[@MEMBER].[20101001-20101031]))

The periodsToDate function will take the member and create a set of members starting with the first member in the specified level to the member being evaluated.  October/2010 would return the set: January/2010, February/2010, March/2010, April/2010, May/2010, June/2010, July/2010, August/2010, September/2010, October/2010.

The next demand is to see the running total for the previous year.  In a relational database you might do this with an outer join. Here it’s just a logical extension of the same expression.

total([Cube].[Measures].[Actual] within set periodsToDate([Cube].[Date].[Date].[Year],parallelPeriod([Cube].[Date].[Date].[Year],1,currentMember([Cube].[Date].[Date]))))

This is essentially the same expression, with the parallelPeriod function wrapping the currentMember.

The same concept can be used for moving totals. Instead of the periodsToDate function, you would use lastPeriods().

total([Cube].[Measures].[Sales] within set lastPeriods (5, currentMember([Cube].[Date].[Date])))

This will always create a total for a set of 5 members ending with the member being evaluated.

Here’s a small challenge. Knowing this, how do you do a running-total in reverse?

Macros (Part 2 of ?): Timestamp of a dimensional twist.

One of the problems often faced when working with true dimensional sources (as opposed to DMR which will not be discussed here, ever) is working with dates. You can very easily make Current Year or Previous Year members, but those will not allow you to use the family functions. How then can you make a query default to a specific year?

To begin you must first understand the structure of your cube. Different cubes have different ways of building their members. The Member Unique Name (MUN) for a specific year might look like:

[Cube].[Date].[Date].[Year]->:[PC].[@MEMBER].[20100101-20101231]

You can use

children([Cube].[Date].[Date].[Year]->:[PC].[@MEMBER].#sb(timestampMask($current_timestamp,’yyyy’)+’0101-‘+timestampMask($current_timestamp,’yyyy’)+’1231′)#)

Cognos will resolve that statement to children([Cube].[Date].[Date].[Year]->:[PC].[@MEMBER].[20100101-20101231])

You can also use timestampMask with prompt macros:

#timestampMask(toUTC(prompt(‘pDate’,’date’) +’ 00:00:00.000000000-00:00′),’yyyy’)#

The toUTC command will convert a string 2010-09-26 00:00:00.00000000-00:00 to a timestamp, allowing it to be used with the timestampMask function.