filter vs tuple

why should you use tuple() and not filters.

First of all

you can only use one filter... but you can write as many tuple expressions as you want.

e.g. Sales is a measure and you want to get its value on 1/1/2008 and 3/1/2008 

How would you do it with a filter? One would make the second impossible.

With tuple expressions you can create calculated data items and call them something like Sales_on_1/1/2008 and Sales_on_3/1/2008

tuple([Sales], [Time Dimension].[date hierarchy].[date]->[2008-01-01])

But I have an even better reason!

Cognos user interface does not distinguish between relational and dimensional or DMR model.

You should!

What does a filter mean when working with a dimensional model?

Who the hell knows.

I think with DMR they (Cognos) add a where clause to the generated SQL... so something will happen... it's just ugly. See how your higher level aggregates will behave...

using tuples() is way more clear!

TUPLE RULEZ!

good nite!

Macro functions - undocumented feature in Report Studio

Did you know that you can use the same macro functions that you have in Framework Manager in Report Studio?

It's not documented in the Report Studio users guide and the GUI which lists the available functions doesn't suggest it either.

Yet, they are there!

Try writing an expression where instead of ?Param1? you write #prompt('Param1')# ... there you go.

This lets you use some very powerful tricks when working with a dimensional model.
Some of which I'll describe later...

How do charts work like crosstabs

I found the graphical interface of charts rather challenging to capture but once you understand that they work exactly like crosstabs you can figure it out:

measure (y-axis) = Deafult Measure
Series = vertical axis
Category (x-axis) = horizontal axis

note that just like a crosstab axis can nest one dimension into another, or add measures on the axis you can do the same in the chart

example crosstab:
note the nesting of Revenue and Gross Profit under Retailer
and
Product type under Month(Time)


the corresponding chart:

note: we have the same nesting

















when we run the report the chart will look like this:

note: the two measures on the legend of the vertical axis

















note: the legend explains the color coding for each combination of retailer and revenue or retailer and profit













whether this complex chart makes sense is a good question... sometimes it does, sometimes it doesn't... the point is: you could if you wanted!

How to use prompt values in a tuple expression

This golden nugget can save you 2 days of work - that's how long it took me to figure out the syntax.

In the tuple() you can use a member to specify which value of the measure you want. This will work much like a filter... just better.

Lest say you have a Sales measure and a Time dimension with Date at the lowest level.

You can write something like this:

tuple([Sales], [Time Dimension].[Date hierarchy].[Date]->[all].[1/1/2008])

this will get you the Sales on 1st Jan. 2008

This is great, but you want to make this a parameter, so the user can specify the date.
Maybe you have a reporting interval called ?ReportStart? and ?ReportEnd? and you want to get the Sales on the last day of the reporting interval.

You can do this:

tuple([Sales], [Time Dimension].[Date hierarchy].[Date]->[all].#sb(prompt('ReportEnd','date'))#

You can use this expression in a crosstab or graph to give you the Sales measure on the end of the reporting interval.

In more generic terms this is the syntax to use prompt parameters in tuple() expressions.

Cognos UI

I found that Cognos uses the same user interface when working on dimensional (including DMR) and relational model.
This causes a lot of confusion because the two work totally different.
To further worsen things there is no error message when you do something that... you just should not do. Even worse... you get some results. But who know what those are!

How to write a report on a dimensional model - part3

Today (tonight actually) I'll attempt to make some further clarifications about how crosstabs work.

For simplicity's sake in part2 I mentioned that along the axes of the crosstab we place ``dimensions''.

In fact these are member sets.

A member set is simply a set of members.
When you drag a dimension - Cogno calls it "regular dimension" - what really happens is that it will be coerced into its members. Search the Cognos documentation for Coersion Rules.

The point I'm trying to make is that you are not limited to working with dimensions. You can use 

• expressions that return a set of members
  
• a certain member (e.g. a given month in the Time hierarchy)
this member will be implicitly casted to a member set with one element only

• memberset that is defined by you by picking any set of members you want
Search the Cognos documentation for Defining Member Sets

Another clarification about measures.

I think Cognos uses values for measures and these values are evaluated for every cell using tuple() function.

In other words you can have calculated measures by doing some arithmetic on tuples.

A usual scenario is a "smart" average calculation :

average_daily_something:= value(tuple(some_value_measure)) / value(tuple(some_days))

For example you want to get the average daily sales amount for a time period but the store is not open on weekends.

Sales - measure

Time - dimension

on weekends Sales will be equal to 0 but you don't want to count this into your daily average

you could do something like

Add a Workday measure that is 1 for workdays and 0 for weekends.

avg_daily_sales := value(tuple(Sales)) / value(tuple(Workday))

You'll come up with measure calculations that make sense in your business... the point is that in your forulas you can use value(tuple(...)) as operands and then use this "calculated measure" in the crosstab.

Thinking out louad about Cognos Framework Manager

I've been thinking about Cognos Framework Manager.
I think it's anoher overhyped thing.

Sure it can store meta-data that tells Cognos how to join tables, how to generate queries.
But wait a sec!

What are the options here?

1. Relational model
   Do you really want to create a data warehouse from a relational model? Come on!
   
2. DMR model
   This would make some sense... if it wasn't such a poorly implemented feature of Cognos that I can only recommend to avoid it if possible.
   
3. OLAP cubes
   In this case the framework doesn't do much, just connects to the cube.
   

So what's the big deal then?