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.
Tip:
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...
Tip:
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.
Tip:
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.
Subscribe to:
Post Comments (Atom)
Posts
Member Unique Names (MUNs) are also generated in a very unfortunate way in a DMR model.
ReplyDeleteEven if you set the businesskey as "uniquely identified by" the MUN will still contain IDs of the higher levels.
e.g.
Product dimension has two levels: Product Type and Product Number.
The Product Number is unique ID.
the MUN will still look like this:
[Product Dimension][Product Type Hierarchy][Product]->[all][type_1][product_number_1]
instead of
[Product Dimension][Product Type Hierarchy][Product]->[all][product_number_1]
Why is this bad? Try constructing a MUN from a prompt.
You get the product number, but what about the product type? You have no idea what's the product type. you cannot construct the MUN....
pretty bad...
I figured out a way to do it:
ReplyDeleteinstead of constructing the MUN to de3fine the member you can find it by business key using filter()
filter([Product Dimension].[Product Type Hirarchy].[Product], roleValue('_businessKey', currentMember([Product Dimension].[Product Type Hierarchy]) ) = ?param1?)
I just wanted to leave an encouraging comment - I think it's FABULOUS that you are doing this site. I don't think I've seen one like it anywhere else. I hope you keep doing it. I've been looking for information on DMR for awhile now, and your site is the only place that even comes close to covering it. I'm wondering how to alert more Cognos developers about the site.
ReplyDeleteThis information regarding Cognos DMR technique was really useful and thanks Simon for this great work.
ReplyDeleteit has been of good use to me, thankyou
ReplyDeleteReally nice work Tamas keep it up
ReplyDelete