Hi,

We have a scenerio that need to create aggregate tables for fact table.
Say for example fact1 has more records so when we try to find measures for month or qtr level it will take more time instead need to create aggregate tables for higher level (ie : year, qtr, month).
when we use year and measure query should hit aggregate table if we  use day level detail then it should hit main fact table (fact1) to fetch records. How we can achieve this framework level.

Please pour your suggestion on this.

Thanks.

------------------------------
Pavithra N
------------------------------

#CognosAnalyticswithWatson

Hi Pavithra,

are we talking about a relational or dimensional model? If you want a cube I recommend IBM Cognos Dynamic Cubes. They support the creation of aggregate cubes via aggregate tables from the underlaying database.
If you want to achieve this with a Framework Manager model I guess a good approach is to use SQL rewrite in the DB.

------------------------------
Robert Dostal
Team Leader BI
GEMÜ
Ingelfingen
------------------------------

Hi Robert,

Thanks for the suggestion. 

We need to achieve this in FM model .If we can do it in DB then creating aggregate tables for detail fact and creating copy of time dimension and pulling this is FM and creating join between this will work to improve the performance.

eg: Fact1-->Detail data , Fact2-->aggregated data, time dimension  can join with fact1 to get low level data and fact2 with copy of time dimension(month,qtr,year) to get high level/aggregated data.

And i can consider two reports parent report uses fact2 and copy of time dimension to get high level and using drill  through child report i can get low level detail which fetch records from fact1 table.

This is my understanding or is there any other way to do it?

Please suggest.

Thanks
Pavithra.

------------------------------
Pavithra N
------------------------------

Original Message:
Sent: Wed June 09, 2021 11:37 AM
From: Robert Dostal
Subject: Aggregate tables

Hi Pavithra,

are we talking about a relational or dimensional model? If you want a cube I recommend IBM Cognos Dynamic Cubes. They support the creation of aggregate cubes via aggregate tables from the underlaying database.
If you want to achieve this with a Framework Manager model I guess a good approach is to use SQL rewrite in the DB.

------------------------------
Robert Dostal
Team Leader BI
GEMÜ
Ingelfingen
------------------------------

Hi Pavithra,

of course you can do it the manual way by integrating both tables into your FM model including all joins to the core tables and then create two reports where report A gets data from the aggregate table and report B from the fact table. With a drill-through definition, both reports are linked together and the user can switch between the level of detail.

I don't know your data structure but keep in mind that you can also work with parameters even in the DB layer in FM. So instead of creating two table query subjects you can create just one and replace the table name with a prompt makro or something else (Select * from #prompt('LevelOfDetail','string','NameOfDefaultTable')#). If this suits your needs there could be a small query in the report or in the FM model that checks what the consumer wants to see and sends the corresponding parameter value to the query accordingly.
To do so both tables have to have the exact same structure.
​

------------------------------
Robert Dostal
Team Leader BI
GEMÜ
Ingelfingen
------------------------------

Original Message:
Sent: Thu June 10, 2021 12:48 AM
From: Pavithra N
Subject: Aggregate tables

Hi Robert,

Thanks for the suggestion. 

We need to achieve this in FM model .If we can do it in DB then creating aggregate tables for detail fact and creating copy of time dimension and pulling this is FM and creating join between this will work to improve the performance.

eg: Fact1-->Detail data , Fact2-->aggregated data, time dimension  can join with fact1 to get low level data and fact2 with copy of time dimension(month,qtr,year) to get high level/aggregated data.

And i can consider two reports parent report uses fact2 and copy of time dimension to get high level and using drill  through child report i can get low level detail which fetch records from fact1 table.

This is my understanding or is there any other way to do it?

Please suggest.

Thanks
Pavithra.

------------------------------
Pavithra N
------------------------------


Original Message:
Sent: Wed June 09, 2021 11:37 AM
From: Robert Dostal
Subject: Aggregate tables

Hi Pavithra,

are we talking about a relational or dimensional model? If you want a cube I recommend IBM Cognos Dynamic Cubes. They support the creation of aggregate cubes via aggregate tables from the underlaying database.
If you want to achieve this with a Framework Manager model I guess a good approach is to use SQL rewrite in the DB.

------------------------------
Robert Dostal
Team Leader BI
GEMÜ
Ingelfingen

Hi Robert,

Thank you so much for the suggestion.

It helps me a lot!

Regards,
Pavithra.

------------------------------
Pavithra N
------------------------------

Original Message:
Sent: Thu June 10, 2021 02:46 AM
From: Robert Dostal
Subject: Aggregate tables

Hi Pavithra,

of course you can do it the manual way by integrating both tables into your FM model including all joins to the core tables and then create two reports where report A gets data from the aggregate table and report B from the fact table. With a drill-through definition, both reports are linked together and the user can switch between the level of detail.

I don't know your data structure but keep in mind that you can also work with parameters even in the DB layer in FM. So instead of creating two table query subjects you can create just one and replace the table name with a prompt makro or something else (Select * from #prompt('LevelOfDetail','string','NameOfDefaultTable')#). If this suits your needs there could be a small query in the report or in the FM model that checks what the consumer wants to see and sends the corresponding parameter value to the query accordingly.
To do so both tables have to have the exact same structure.
​

------------------------------
Robert Dostal
Team Leader BI
GEMÜ
Ingelfingen
------------------------------


Original Message:
Sent: Thu June 10, 2021 12:48 AM
From: Pavithra N
Subject: Aggregate tables

Hi Robert,

Thanks for the suggestion. 

We need to achieve this in FM model .If we can do it in DB then creating aggregate tables for detail fact and creating copy of time dimension and pulling this is FM and creating join between this will work to improve the performance.

eg: Fact1-->Detail data , Fact2-->aggregated data, time dimension  can join with fact1 to get low level data and fact2 with copy of time dimension(month,qtr,year) to get high level/aggregated data.

And i can consider two reports parent report uses fact2 and copy of time dimension to get high level and using drill  through child report i can get low level detail which fetch records from fact1 table.

This is my understanding or is there any other way to do it?

Please suggest.

Thanks
Pavithra.

------------------------------
Pavithra N


Original Message:
Sent: Wed June 09, 2021 11:37 AM
From: Robert Dostal
Subject: Aggregate tables

Hi Pavithra,

are we talking about a relational or dimensional model? If you want a cube I recommend IBM Cognos Dynamic Cubes. They support the creation of aggregate cubes via aggregate tables from the underlaying database.
If you want to achieve this with a Framework Manager model I guess a good approach is to use SQL rewrite in the DB.

------------------------------
Robert Dostal
Team Leader BI
GEMÜ
Ingelfingen

I suggest you review your database vendor documentation regarding their support for materialized views.

Many vendors support them and automatic query re-write etc.

------------------------------
NIGEL CAMPBELL
------------------------------