Framework Manager DMR model with “external rollup” for non-additive measure

[Andrei I]

Do you know if a Framework Manager DMR model can handle "external rollup" for non-additive measures?

This a common requirement when you have higher level aggregations computed in a third party software (e.g. statistically adjusted metrics)
PowerPlay Transformer can use "externally rolled up" measures (though not really straightforward). But I'd like to achieve similar functionality in FM DMR.

Consider simple example.
2 dimension tables:

DimTime(month, year)
2010, 201001
2010, 201002
...
DimGeography (country, city)
Canada, Toronto
Canada, Montreal
USA, NewYork
USA, Buffalo

2 fact tables

FactCity ( month, country, city, FactValue )
201001, Canada, Toronto, 100
201001, Canada, Montreal, 200
201001, USA, NewYork, 500
201001, USA, Buffalo, 20

FactCountry ( month, country, FactValue)
201001, Canada, 111
201001, USA, 222

Clearly you can't use any regular Aggregate function to rollup FactValue from City to Country:
201001 Canada, Toronto, 100
201001 Canada, Montreal, 200
=======================
Total for Canada 111

Is there any way to make DMR to use different tables (query subjects) to retrieve values on different levels of Geography hierarchy?

This problem relates to thread
http://www.cognoise.com/community/index.php?topic=3424.0

[blom0344]

My 2 cents: Not possible. You can handle multifact as long as the shared dimensions are the same set.

You can set an 'aggregate rule' for a certain measure dimension against a regular dimension and choose a certain rollup behaviour. We tested this for cumulative figures in a larger fact table (as cumulatives are not additive)

Pro: I actually did what it supposed to do..

Con: Analysis performance dropped like a stone. The generated SQL on the database was both inefficient , returned non aggregated data and left the cognos server to handle things locally.

[Andrei I]

In this particular case the performance is not an issue  (all data metrics pre-calculated and stored in DB table with total row count < 100,000 )
The problem is how to make Cognos Engine to pull 111 for Canada (when you drilling up from City to Country)

>Pro: I actually did what it supposed to do..
Can you please publish a screes-shot of FM manager screen where you can choose a certain rollup (other than standard Aggregate Rules) ?

[blom0344]

We haven't found a dynamic solution to your problem yet.  We store multiple set of data in one fact table and then - as a workaround - ask the user to apply a context filter to choose from one of the sets.  This way one can keep the standard measure and adjust the filter setting.

Example:

City,201001, Canada, Toronto, 100
City,201001, Canada, Montreal, 200
City,201001, USA, NewYork, 500
City,201001, USA, Buffalo, 20
Country,201001, Canada,Canada, 111
Country,201001, USA, USA,222

The context filter is applied on the first column (seperate filter dimension) to select the desired set.

[Andrei I]

Thank you for the prompt response. Actually any manual filtering is exactly what I need to get rid of :-)

[ykud]

What you want to achieve is called "aggregate awareness" and Cognos is well-known for lacking it ( Gartner report says this functionality is under development right now.

There are 3 possible workarounds that I know:
1) Use DBMS functionality (MVs for Oracle, MQs for DB2) if you are on appropriate DBMS Server. You just add MVs for your aggregate tables and get seamless query rewrite.
The easiest and cleanest variant.
2) Put all data (or union it) in one table and flatten the hierarchy.
Crude variant, if you can live without hierarchies -- you just don't do aggregations.
3) Add dummy elements to hierarchy to achieve correct result when rolling up. You can just add an additional element to each hierarchy level that will make sure that sum(all_normal_elements) + dummy_element = the aggregated value you desire.
Then you'll have hierarchies, but will have to ask users to ignore dummy elements.

- Yuri

[PRIT AMRIT]

Correctly said Yuri.

Could you please elaborate bit more on Point 1?

1) Use DBMS functionality (MVs for Oracle, MQs for DB2) if you are on appropriate DBMS Server. You just add MVs for your aggregate tables and get seamless query rewrite.
The easiest and cleanest variant.

Specivo bam bolsoi ;)

Prit

[Andrei I]

There are 3 possible workarounds that I know:

Thank you for the suggestions. Unfortunately none of these  workarounds can help in this case. Aggregated values calculated using custom statistical distribution models.

1) Use DBMS functionality (MVs for Oracle, MQs for DB2) if you are on appropriate DBMS Server. You just add MVs for your aggregate tables and get seamless query rewrite.
The easiest and cleanest variant.

Oracle MVs can be used only with simple SQL aggregate functions
(http://download.oracle.com/docs/cd/B19306_01/server.102/b14223/basicmv.htm#i1006519)

2) Put all data (or union it) in one table and flatten the hierarchy.
Crude variant, if you can live without hierarchies -- you just don't do aggregations.

The idea is to get for end users of DMR package the same functionality in Cognos Studios (especially Analysis Studio) without any special filtering as PP packages with external aggregates  can do.

3) Add dummy elements to hierarchy to achieve correct result when rolling up. You can just add an additional element to each hierarchy level that will make sure that sum(all_normal_elements) + dummy_element = the aggregated value you desire.
Then you'll have hierarchies, but will have to ask users to ignore dummy elements.

Same as above.

[ykud]

Thank you for the suggestions. Unfortunately none of these  workarounds can help in this case. Aggregated values calculated using custom statistical distribution models.
Oracle MVs can be used only with simple SQL aggregate functions
(http://download.oracle.com/docs/cd/B19306_01/server.102/b14223/basicmv.htm#i1006519)

AFAIK, you can register any table as MV and get query rewrites in oracle with
'create materialized view on prebuilt table' clause.
See   documentation for details.

- Yuri

[ykud]

Correctly said Yuri.

Could you please elaborate bit more on Point 1?
Specivo bam bolsoi ;)

Prit

Hi, Prit.

Both Oracle and DB2 have built-in mechanisms for aggregate navigation, called Materialized Views and Materialized Query Tables. It's just like computing aggregate views for some levels. Using this mechanism all queries at given level of aggregation are rewritten to to these precomputed values. And AFAIK both DBMS allow you to register your own table as such aggregate, maintaining custom aggregates on desired levels.

- Yuri.

[PRIT AMRIT]

Ohh I see.... never worked on such requirements yet though :)....Specivo Yuri......

BTW, I heard Business Object deals handy with "aggregate awareness" concept?

[ykud]

Ohh I see.... never worked on such requirements yet though :)....Specivo Yuri......

BTW, I heard Business Object deals handy with "aggregate awareness" concept?

Yep, BO, MSTR and Oracle BI all have this feature. I'm very much unconvinced about it usefulness, mostly due to about a couple years of using Oracle BI for real projects. It tends to overly complicate logical models and is pretty hard to debug at some point of complexity.
I wrote about it in OraBI vs Cognos BI comparison.
Maybe in BO it's way easier. Maybe I'll see it in action someday.

[learner185]

Hi Yuri/ everyone,

I am facing same issue in my hierarchy.

Please can you explain how to implement option 3, as I would like to keep the hierarchy and not flatten it out.
3) Add dummy elements to hierarchy to achieve correct result when rolling up. You can just add an additional element to each hierarchy level that will make sure that sum(all_normal_elements) + dummy_element = the aggregated value you desire.
Then you'll have hierarchies, but will have to ask users to ignore dummy elements.

Please help. Its urgent.

Thanks,
Gauri

[cognostechie]

I hadn't read this post earlier and now that I read this I find that most people who commented that BO, MVs have aggregate awareness and Cognos does not didn't know much about the technology. It's like saying 'I don't know how to drive and that means the car doesn't exist' ..

Both Cognos (the Canadian company) and IBM had clearly declared that Cognos software is not a single product. It's a suite of products and depending on your requirement, you have to choose the correct tool. Most people don't know how to make cubes and their knowledge is limited to Framework Manager and since they couldn't find the functionality in FM they passed a verdict saying 'Cognos doesn't have this feature'. The OP himself said that Transformer has this feature but he didn't want to use Transformer. WHY did he/she say so is anybody's guess ! What if I say 'I don't want to write a query in SQL Server because I don't know how to write a query so show me a way to query the data and if you can't then SQL Server is useless' !

Just using common sense will indicate that aggregate awareness is only possible when you store the aggregated data which is something FM cannot do because it is not a physical OLAP engine ! It's well documented that FM will not store any data. It will only convert the data into hierarchies when you run the report so how will it have aggregate awareness. They do have Transformer, TM1 and dynamic cubes for this.

Gauri - Transformer and Cube Designer both allow you to add manual levels in a hierarchy which can be used as dummy levels.

[learner185]

Hi All,

my exact issue is as below:

We have data pre-aggregated on hourly and daily
When drilling from hour to day, we were not able to make cognos seamlessly pull data from hourly table , then move on to daily table
Without this, the report user need to duplicate the work for daily and hourly as they are coming from different query subject/item.
Question is how to achieve this through Cognos model

Pls help.