Category: OLAP

10, OLAP

Essbase Global Variables and DQM

| cognospaul

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.

Javascript, OLAP, Prompts

Accessing Tree Prompts with Javascript (on Cognos 10.1.1)

| cognospaul

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 ..webcontentpromptingCTreeIE5NS6.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.

OLAP

Quickie: Generate function (first N months of years)

| cognospaul

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.