How to Use Aggregate Navigation With Cognos and How to design it in FM model

[dmk.3678]

Hi Everyone.

I have 10 Dimention and 2 Fact Tables.
First Fact table lets say Fact1 is a summarized and aggregated data of detail fact table Fact2.
How do fit this in Start Schema in FM model. and also when report run, it should pull the data from Fact1 table first, but when user decide to drill-down more in detail then data should be displayed using the Fact2 table which is a detail table.

Some says Aggregate Navigation is not possible from withing the cognos or FM model. but can be done at RDBMS level bu creating the Materialized Views  and Query Re-Writing. I am using the Oracle as my RDBMS.

But i am not sure how to create this and hows the FM model gonna be. Should i create start schema using Fact1 (Summary Table) or using the Fact2 table.

Please help.. Thanks in advance.

[cognostechie]

Hi Everyone.

I have 10 Dimention and 2 Fact Tables.
First Fact table lets say Fact1 is a summarized and aggregated data of detail fact table Fact2.
How do fit this in Start Schema in FM model. and also when report run, it should pull the data from Fact1 table first, but when user decide to drill-down more in detail then data should be displayed using the Fact2 table which is a detail table.

Some says Aggregate Navigation is not possible from withing the cognos or FM model. but can be done at RDBMS level bu creating the Materialized Views  and Query Re-Writing. I am using the Oracle as my RDBMS.

But i am not sure how to create this and hows the FM model gonna be. Should i create start schema using Fact1 (Summary Table) or using the Fact2 table.

Please help.. Thanks in advance.

Don't believe anyone who says that. If that would have been the case then Cognos would have failed in the market.

You can join both the Fact tables to the same dimensions provided you have the keys in both of them. For the drill-downs, cannot say without knowing the columns in those tables as well as the dimension tables. You can definitely create  a physical cube once the modelling has been done in FM which will allow you to drill down to the lowest level regardless of which Fact table it is in.

Don't try to learn the subject simply by facing a scenario, asking questions and learning as you go on. You will never learn properly this way. Go thru the user guide and complete the cycle of making a model from scratch without paying attention to your current scenario. Once you are familiar with all functionalities available and how the modelling is done then look at your current scenario and you will probably get the answer yourself, not only to this issue, but also to issues that would come later on.

[bdbits]

Don't try to learn the subject simply by facing a scenario, asking questions and learning as you go on. You will never learn properly this way. Go thru the user guide and complete the cycle of making a model from scratch without paying attention to your current scenario. Once you are familiar with all functionalities available and how the modelling is done then look at your current scenario and you will probably get the answer yourself, not only to this issue, but also to issues that would come later on.

This is good advice.  8)

[dmk.3678]

Don't believe anyone who says that. If that would have been the case then Cognos would have failed in the market.

You can join both the Fact tables to the same dimensions provided you have the keys in both of them. For the drill-downs, cannot say without knowing the columns in those tables as well as the dimension tables. You can definitely create  a physical cube once the modelling has been done in FM which will allow you to drill down to the lowest level regardless of which Fact table it is in.

Don't try to learn the subject simply by facing a scenario, asking questions and learning as you go on. You will never learn properly this way. Go thru the user guide and complete the cycle of making a model from scratch without paying attention to your current scenario. Once you are familiar with all functionalities available and how the modelling is done then look at your current scenario and you will probably get the answer yourself, not only to this issue, but also to issues that would come later on.

Thanks.

But Both Fact Tables should have Keys of all 10 dimensions ? Correct?

[cognostechie]

 Not necessarily. It depends on what is the granularity of Facts. It is fine for Fact1 to have keys of 2 dimensions and Fact2 to have keys of 10 dimensions. The detail Fact should have keys of all dimensions in order to join

[dmk.3678]

Cool. Thanks.

[fry]

When you say aggregate navigation I believe you are referring to aggregate awareness. Cube Designer in Dynamic Cubes is aggregate aware, FM is not.

Drill through can be achieved at the reporting layer using an FM package. 

[en1]

Don't believe anyone who says that.

But it is reality. How cognos take appropriate measure from one of fact tables?

[dmk.3678]

Not necessarily. It depends on what is the granularity of Facts. It is fine for Fact1 to have keys of 2 dimensions and Fact2 to have keys of 10 dimensions. The detail Fact should have keys of all dimensions in order to join

Okey so i did and moved forward with what u said and explained. So first i have joined 7 Dimensions to my Fact1 (Aggregate table of Fact2) and same 7 dimentions+3 Dimentions to my Fact2 (Detail Table).

Then i have created the star schema grouping, resulting in to 2 new query subjects, Fact1 and Fact2, which has shortcuts of all required dimentions and fact table.

Now i have created a dimention called Year in which i have heirarchy of Year-to-Quarter-to-Month. i have created this using the Fact1 query subject (created above in star schema grouping).  having join between shortcut of Year dim to shortcut of Fact1.

Then i have created 2 measure dimention. Measure-Fact1 has Total Cost from Fact1 quaery subject, and Measure-Fact2 has Total Cost from Fact2 query subject.

When use this on report and run the report, the summary work fine, but then i defined the drill through (Created Target Report) on Total Cost.

In Target report i have Year and Month column and Total Cost column. Defined Slicer '[Year Dim].[Year].[Year]->?'year'?
(?year? is the data element i am passing to my target report)

So here the issue. when i try to do Drill Through on Cost, it gives me error that Local processing is not enabled. Below are the exact error i receive.

UDA-SQL-0458 PREPARE failed because the query requires local processing of the data. The option to allow local processing has not been enabled.
UDA-SQL-0462 The OLAP function 'sum' is not supported by the database. This operation requires
local processing of the data.

And i really dont want to enable local processing at FM level, it will cost me a lot in performance.

I believe i am getting this error because i have used the same Hierarchy of Year(Which comes from Fact1 query subject) in Target report, but measure is from Fact2 query subject.

Can you please suggest, how should i proceed further for creating hierarchy. DO i have to create 2 hierarchy of Year, one for Fact1 Measures and 1 for Fact2 measures.  I have tried searching the solution on google, but and also tried the guides but every body talks about untill creating star schema grouping, but no one mentioned how to proceed further if the Parent Report need to use Fact1 table measure and Target report need to use Fact2 (detail table) measures.

Thanks in advance.

[bdbits]

In my opinion you are going about this all wrong. You only need the detail table and Cognos can do the aggregation.

Your date dimension should be based off a separate table, not off of a fact. In a purely dimensional design, which is what you appear to be striving for, fact tables normally have foreign keys and measures - no dates or other attributes. Dimensions like date should be represented once, then joined with whichever fact tables you have.

Are you using DMR, or a relational model? Slicers and detail filters are very different things and should only be used with the appropriate model type.

[dmk.3678]

In my opinion you are going about this all wrong. You only need the detail table and Cognos can do the aggregation.

Your date dimension should be based off a separate table, not off of a fact. In a purely dimensional design, which is what you appear to be striving for, fact tables normally have foreign keys and measures - no dates or other attributes. Dimensions like date should be represented once, then joined with whichever fact tables you have.

Are you using DMR, or a relational model? Slicers and detail filters are very different things and should only be used with the appropriate model type.

There are over 40 millions record in detail table. If we use that table only as 1 fact table in model and create the reports, the reports will be slow as hell. so thats why we decided to aggregate the 40 millions raws in another fact table at some level.

I am using the DMR model. and using slicer. And year is altogether a different Dimension table (a lookup table) and Fact1 and Fact2 is also a altogether 2 Separate tables.

[bdbits]

Apologies on "detail", I confused the term referring to your secondary table with a detail filter.

What happens when you run your target report by itself and supply the prompt values you think you are passing over to it? If the target works as expected, you can inspect the drill-through parameters before they are passed to the target if you have admin permissions, or can get someone to help who does.

I disagree that database aggregation will necessarily be "slow as hell" if you have decent hardware, a solid design and model, proper indexes, etc. I have worked with tables as large as that and larger with perfectly acceptable response times. Another option might be to use a cube, if resources allow.

[Samrat]

hi dbbits,

Did you find any solution on "how to use aggregate Navigation with Cognos".

I have similar scenario where I have 4 fact tables, holds details to summary data.

Please let me know how did you implement solution in Cognos.

Appreciate any help on this

Thanks
Sam