Modelling Question

[Sam2011]

Hi All,
I have a Fact table that has transaction detailes for Actual and Budget data for daily Sales. I also have a dimension table that holds the definition of record type.
I called the dimension table  'scenario' it has 'A' for Actual and 'B' for Budget. This column is linked in the backend to a similar Column in the Fact Table.

My question is if I added another set of transaction data to the Fact Data that are not necessary Actual or Budget, let's assume 'overseas Cost' and I created an entry 'C' in the scenario table for oversees Cost.

So I will end up having 'A' for Actual, 'B' for Budget, 'C' for oversees Cost.

Now how I can create a Scenario let's say 'D' that is equal to 'A' + 'C' ( Actual +overseas Cost) without having to create a set of data in the Fact table for 'D'.

I mean I need Cognos to add 'A' +'C' internally.


Fact table
SCenario    Sales_id     Amount
A                  100            200K
B                  100             170K
C                  100             2K
D                  100             202K             ( I don't want to have to add A+C in the Fact table)
Dimension Table 'Scenario'
Scenario      Name
A                  Actual
B                  Budget
C                  Overseas Cost
D                  Actual+Overseas Cost

[cognostechie]

Multiple ways to do this.. One way would be -

You already have a column in the Fact table that has the codes A, B and you are going to add C.

So once you bring this in FM as a Query subject, you can create a calculated column with the expression -

If ([Scenario] = 'A' or [Scenario] = 'C') then ('D') else ([Scenario]).

Now you have a column parallel to Scenario with the code 'D'.

In the report, you can set a filter to read only D and the agrregated result would be A + C. You have to be a little creative in the Report making process so as to be able to set the filter in the individual column itself if required.

[jive]

You can also modify the query subject definition, that way the value will be always calculate.