How to write a report on a dimensional model - part1

by using crosstabs and the tuple() function of course!

Let me show you how...

First of all: what is a tuple?
Tuple is basically the way to address a value in an n-dimensional array, the OLAP cube.
It's basic form is simple:
tuple(measure, dimension1, dimension2,...)

Tuple is really a coordinate.
To get the value of the cube at this coordinate we need to use the value() function.
value(tuple(measure, dimension1, dimension2,...)
Cognos does this automatically. Search for Coersion Rules in the Report Studio users guide.
It's good and bad... makes things easier, but causes confusion too... anyways.

In order to specify our coordinate we need to specify a member in each and every dimension that applies to the measure.

We don't need to write it all however. Cognos fills in the missing values but it is very important to understand how...

• measure: defaults to the "default measure" - see upper left corner of crosstab

• dimension: defaults to the current member of the dimension coming from the context - i.e. if the dimension appears on one of the axes in the crosstab, otherwise defaults to the default member
  The default member is the root "(All)" member. By defaulting to this we are in effect rolling up that dimension. In other words dimensions that are not in the context of the crosstab are rolled up. Makes perfect sense.

If you don't want this default behavior you can just edit the expression and specify exactly what you want. This is very cool, it's the most powerful tool a report writer has.

example
Sale is a measure that is associated with three dimensions:
- Time
- Product
- Branch

to fully specify a sale we need to supply a member of all three dimensions:
Sale(1/23/2008, 'ford fusion', 'Richmond Automall')

tuple(Sale) will add up all the sales

tuple(Sale, 1/23/2008) will add up all the sales on Jan 23 for all products at all branches 

tuple(Sale, 'ford fusion') will add up all the sales where the product was a Ford Fusion 
etc.

We need to understand that the tuple is executed in the context of a crosstab.

Dimensions that are not explicitly mentioned will be searched for among the axes to see if they have a current member.

This gives a whole new meaning to the examples above... 

Taking the last example for instance:

tuple(Sale, 'ford fusion') | Time

---------------------------+------

Branch                     | <1234>

This crosstab has Branch and Time along the axes which will suply the context for the tuple expression. This crosstab will display the Ford Fusion sales broken down per day and per branch.

If we don't want our expression to depend on the context of the crosstab we can use the completeTuple() function.

Every dimension that is not explicitly defined will default to its default member - meaning it will be rolled up.

In the next part I'll write about crosstabs. 

Using tuple() expressons in crosstabs you can do pretty much anything.

And here is the good news. Charts work exactly like crosstabs.

If you can get the data to show up in a crosstab you can create a chart plotting the same data by setting it to the same query.

By the way... forget about list. When you work with a dimensional model you don't need list.