Kimball Forum
Would you like to react to this message? Create an account in a few clicks or log in to continue.

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

2 posters

Go down

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

Post  ian.coetzer 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)

ian.coetzer
ian.coetzer

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

Back to top Go down

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

Post  ngalemmo 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.
ngalemmo
ngalemmo

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

http://aginity.com

Back to top Go down

Back to top

- Similar topics

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