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.
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..
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.
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.
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
Quote from: blom0344 on 25 Mar 2013 02:42:17 PM
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.
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?
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.
Quote from: sthabinash on 26 Mar 2013 09:52:12 PM
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..