Tag: olap

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.