Hitting appropriate fact tables

[sthabinash]

I have a fact table at sku/loc/day level, aggregate tables at class/loc/day and dept/loc/day level. I need to build a DMR package by using which if I am viewing report at dept/loc/day, I hit dept/loc/day table and not rollup all the way from the sku/loc/day table. How can I achieve this? Do I need to define fact measures derived from each fact table? Please list the steps in short.

[blom0344]

You are basically asking whether you can build-in aggregate awareness into DMR? Cognos in this respect  relies heavily on the underlying database to use an existing aggregate.  The question is whether using an aggregate table is really needed.  You do not specify the database you are working with, but using partitioning / proper indexes may be all you need.  Every analysis through DMR is likely to draw part of the facttable anyhow..

[Lynn]

I think that dynamic cubes (introduced in version 10.2) would accommodate this. I have not ventured there yet myself but thought I'd mention it in case it is a more suitable approach for your requirements.

[MFGF]

That's a good shout! Dynamic cubes include aggregate awareness, allowing you to utilise your in-database aggregates. You don't design them in Framework Manager, though - there is a separate modelling environment for them, called Cube Designer.

Cheers!

MF.

[blom0344]

All 3 tables share  loc and day level, which is why I enquired whether an aggregate would matter that much. If dept and class are  sku attributes, then I can imagine you can build a very effective starschema on the finest grain of data. Or otherwise look into materialized views - if supported - to allow the database to choose the best access to the data

[Lynn]

All 3 tables share  loc and day level, which is why I enquired whether an aggregate would matter that much. If dept and class are  sku attributes, then I can imagine you can build a very effective starschema on the finest grain of data. Or otherwise look into materialized views - if supported - to allow the database to choose the best access to the data

Very good point to investigate. I suppose it would depend on the distribution of skus within departments and classes.

[sthabinash]

I am currently using Cognos 10.1 and so I am not able to use the aggregate awareness :'(. As for the fact table, I have the seperate aggregated fact tables for dept and class along with fact table for sku. Currently, the package extract the aggregated fact from the sku level and rollup/groups for the dept level and class level. Since there are millions of data in the sku level and rolling up take a long time, I wanted to implement the aggregate awareness concept and hit the respective aggregate table. I did try refining the star schema but it didnot help much. So, is there any way to manually replicate the concept of aggregate awareness in Cognos 10.1?

[cognostechie]

Yes, create a Transformer cube.

By the way, if you created an aggregated table which contains data at the dept/loc level then why didn't it help much? Have you indexed it properly? An aggregated table can be used to make the main  report and a drill thru can be passed to the detail report (at the sku level) and this method works. The detailed report can use the table which has sku level data.

[blom0344]

I am currently using Cognos 10.1 and so I am not able to use the aggregate awareness :'( . As for the fact table, I have the seperate aggregated fact tables for dept and class along with fact table for sku. Currently, the package extract the aggregated fact from the sku level and rollup/groups for the dept level and class level. Since there are millions of data in the sku level and rolling up take a long time, I wanted to implement the aggregate awareness concept and hit the respective aggregate table. I did try refining the star schema but it didnot help much. So, is there any way to manually replicate the concept of aggregate awareness in Cognos 10.1?

Not in the way Business Objects used aggregate awareness within Universes. You will not find the solution within a relational model within a framework..