Thursday, January 31, 2008

How list works

  1. runs the query, gets the reultset
  2. looks at all the columns (query items) and check the usage attribute
  3. it will distinguish between facts and everything else: attributes and identifiers
  4. makes sure that there is only one row for each unique combination of attributes and identifiers
  5. when multiple rows are found these are groupped by aggregating all the facts

This means you have to be extra careful to make sure that the usage attribute is set properly.
Usually it is... but there's no guarantee.
If you see too many rows or values being summed up then keep this in mind.

List vs Crosstab

It's easy. But I haven't found it mentioned clearly in Cognos documentation or training:
Use List for relational model, Crosstab for dimensional model.

This means that if you are using Cognos in a real data warehouse scenario you can most certainly forget about lists.

The good news:
  • Everything you can do with list can be done with crosstab (so you won't miss it)
    including having only one dimension
  • if you can get it working in crosstab then you can plot it on a graph
    graphs work exactly like crosstabs

DMR feature

DMR model stands for Dimensionally Modelled Relational model.

It's a new feature that was added in the latest version of Cognos I believe. As such... it's immature.

When should you use it?
DMR can be useful when you have a phisical star schema but you don't have an OLAP cube yet.
It allows you to build a "virtual cube" in Framework Manager.
From the report's perspective the model behaves like a "normal" dimensional model.

There are a couple of things wrong with it though...

The order of members in leves (of hierarchies within dimensions) is not guaranteed. this is documented in the know issues.
What this means is that e.g. there's no guarantee that dates are coming out in order of the time dimension. They can get mixed up. Certain functions that depend on the order of members - such as nextMember() - are not suggested for use.
I found that you can work around this issue by explicitly setting the order of element in the report by setting sorting on the dimensions in crosstabs.

Even though it supposed to look like a dimensional model from a report you don't see any MDX. When you try to look at the generated query in Report Studio it shows you SQL.
This makes it very confusing. I've seen lot of developer still applying the "relational thinking" when working with a DMR model. And that doesn't work...
Remember that you are working with a dimensional model. The queries that get generated by Report Studio are not relational queries - even though they look like that. they are really just collections of dimensional expressions that say how to get values from the "virtual cube" to populate the axes of crosstabs or graphs and how to get values for measures.
In a later post I'll explain how to work with dimensional model from reports.
Don't work with the queries... don't UNION them, JOIN them or anything like that. Relational thinking just won't work.

It is quite tricky to set up dimensions in the model - Cognos calls it "regular dimension".
When you set up levels the underlying query subject needs to have determinants, otherwise it will get screwed up.
Determinants are the most horribly documented feature of Cognos IMO...

Another weird thing is that there's no guarantee that the lowest level is the same when you set up multiple hierarchies. What the heck? I think it always whould be the same.

e.g. product has size and color
Size hierarchy: Size level and Product level
Color hierarchy: Color level and Product level

There is no guarantee that the two product levels are the same. In fact I'm not sure Cognos even knows these two are the same...
It's weird to me. I think the lowest level should always be the same and the editors used t set it up should enforce it.

That's about DMR for now. I hope it helps.