Derived Fact table with additional measures / foreign keys ... ?

View previous topic View next topic Go down

Derived Fact table with additional measures / foreign keys ... ?

Post  ian.coetzer on Tue Dec 07, 2010 2:55 pm

Hi,

Let's say we have a fact table: FactReceipt
which contains all our receipting transactions that occur during the month,
we may have 5 receipts of different types during one month for one contract.

Now let's say we build another fact table that contains aggregations (derived from FactReceipt):
FactMonthReceipt
which contains aggregated receipts per month, per contract
BUT that we have additional foreign keys in this fact table like a contract status for the month etc.
Is this allowed?

In other words may we have a derived Fact table with additional measures and foreign keys that are not present in the base fact table?
Anyone got a link to an example dimension model with this scenario?

FactReceipt
==================
*FactReceiptKey
*TransactionTypeKey
*TransactionDateKey
*TransactionTimeKey
*ContractKey
*TransactionAmount

FactEDIReceipt
=================
*FactEDIReceiptKey
*MonthDateKey (will be linked to our date dimensions but this will always be linked to the 1st of every month.)
*ContractKey
*EDIAmount (Total of all transaction amounts for this contract for the month - from FactReceipt)
*EDIExpectedAmount (something coming in from another source system - that is not stored in our base fact table since it is at monthly granularity)
*ContractStatusKey (foreign key to the current status of the contract)

avatar
ian.coetzer

Posts : 57
Join date : 2010-05-31
Age : 36
Location : South Africa

View user profile

Back to top Go down

Re: Derived Fact table with additional measures / foreign keys ... ?

Post  ngalemmo on Tue Dec 07, 2010 3:08 pm

What you are trying to do is fine. There is nothing wrong with enhancing an aggregate with new facts or deriving new dimension.

For measures, the only issue is if the new measures are atomic in the context of the aggregate fact. As it is the case in your example, fine. If, however, the new measures are received at a lower grain, you would be better off creating a new atomic fact for those measures proir to incorporating summarized measures into the aggregate. This would allow the new measures to be used for other purposes without having to go back to the source.
avatar
ngalemmo

Posts : 3000
Join date : 2009-05-15
Location : Los Angeles

View user profile http://aginity.com

Back to top Go down

View previous topic View next topic Back to top

- Similar topics

 
Permissions in this forum:
You cannot reply to topics in this forum