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
Original Message:
Sent: Wed June 09, 2021 12:15 AM
From: Pavithra N
Subject: Aggregate tables
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