Different dimensional data results in DQM and CQM

[KatyB]

We have a model that pulls in data for an 'Organization Hierarchy'.  The model is created in Dynamic Query Mode (DQM).  The physical layer has a data source query subject that is simply a 'select * from database.someviewflattened'.  From this we created a regular dimension that has levels such as level00, level01, level02, etc up to level11.  The hierarchy is not ragged, but is unbalanced. 

Here's the problem.  When pulling this hierarchy into a report with an associated measure (that is in scope) the hierarchy shows each of the NULL levels.  However, if you save the same model and convert it to Compatible Query Mode (CQM)  and make no other changes and publish the package, the hierarchy displays as expected showing only the levels that have associated data and not the NULL levels. 

In Framework Manager I have tried adjusting the property settings for:  Balanced, Ragged, Parent Child, Wide Member Tree to no avail.  The package allows for NULL suppression.  And, I have tried suppressing nulls in the report itself.   

Any ideas how to fix this?  Thanks for the help!

Please note:  there is a PDF attached that shows the DQM and CQM hierarchy results.  Thanks!

[MFGF]

We have a model that pulls in data for an 'Organization Hierarchy'.  The model is created in Dynamic Query Mode (DQM).  The physical layer has a data source query subject that is simply a 'select * from database.someviewflattened'.  From this we created a regular dimension that has levels such as level00, level01, level02, etc up to level11.  The hierarchy is not ragged, but is unbalanced. 

Here's the problem.  When pulling this hierarchy into a report with an associated measure (that is in scope) the hierarchy shows each of the NULL levels.  However, if you save the same model and convert it to Compatible Query Mode (CQM)  and make no other changes and publish the package, the hierarchy displays as expected showing only the levels that have associated data and not the NULL levels. 

In Framework Manager I have tried adjusting the property settings for:  Balanced, Ragged, Parent Child, Wide Member Tree to no avail.  The package allows for NULL suppression.  And, I have tried suppressing nulls in the report itself.   

Any ideas how to fix this?  Thanks for the help!

Please note:  there is a PDF attached that shows the DQM and CQM hierarchy results.  Thanks!

Hi,

There isn't a fix, per se. DQM and CQM are different, and IBM does not (and never has, to my knowledge) guarantee you will get the same results from each. DQM behaves like a pure OLAP engine should, and adheres to the normal OLAP rules (eg you will see all edge members from a set even where there are no associated measures). CQM behaves in its own way - and my observation is it's neither true OLAP nor true Relational, but rather a bizarre mix of the two. With a relational report using inner join between dimension and fact, you only see edge items where the fact (ie measure) exists. Of the two, DQM is giving what I'd view as "expected" behaviour. CQM is giving you a sort of OLAP/relational mashup. One of the reasons Lifecycle Manager came into being (I believe) was to assist with migrations from CQM to DQM, because they behave differently, which tells us IBM expect to see different results from each in some situations.

My approach to solving this particular issue would be to balance the hierarchy - either in Framework Manager (not ideal as it's truly complex to do) or ideally in the data warehouse.

Cheers!

MF.

[AnalyticsWithJay]

Echoing MFGF...

CQM was/is too forgiving - not strict enough. This "leniency" led to a lot of issues (performance and other issues) that were due to bad report/model design, but the Cognos application failed to point these bad practices out to the user. It's funny that MF referred to it as "bizzare", because that's exactly the behavior we've seen over the years. With too much leniency, you get unpredictability.

DQM was designed to be much more strict partly for this reason, and it will dynamically switch between SQL and MDX depending on the situation. You're using a crosstab in your example, which as you may know will generate a temporary cube to process your result. DQM is behaving as expected since you're technically in the OLAP world!

When working with unbalanced or ragged hierarchies, it's best to stick to OLAP. You can avoid this behavior in OLAP by using ancestors(), descendants() and children() functions.

CQM and DQM results are not guaranteed to be identical.

Interesting note: Lifecycle manager was never meant to be a product. It was initially created internally as a SDK application for developers to test changes. It was almost decommissioned a few years ago, but ultimately proved to be invaluable to clients.

Cheers,
Jay