Category: OLAP

OLAP, Report Expressions

The importance of using Report Expressions

| cognospaul

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../tempdmbTemp_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.

OLAP

Running-total on a cube

| cognospaul

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, OLAP

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

| cognospaul

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.