Derived Fact table with additional measures / foreign keys ... ?
2 posters
Page 1 of 1
Derived Fact table with additional measures / foreign keys ... ?
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)
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- Posts : 57
Join date : 2010-05-31
Age : 44
Location : South Africa
Re: Derived Fact table with additional measures / foreign keys ... ?
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.
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.
Similar topics
» How to create fact table with measures derived from comparing two fact table rows
» Factless fact table with null foreign keys
» Fact Table with huge number of Blank (or Empty) foreign keys
» Business keys or Natural keys in the Fact table
» Should rule-derived columns go into the fact table?
» Factless fact table with null foreign keys
» Fact Table with huge number of Blank (or Empty) foreign keys
» Business keys or Natural keys in the Fact table
» Should rule-derived columns go into the fact table?
Page 1 of 1
Permissions in this forum:
You cannot reply to topics in this forum