I work for a manufacturing company and I am creating a datamart for tracking inventory of products. As part of this datamart I also need to analyse activity based costing of these products .A semifinished product is also a product for the company and we need to keep track of the cost that has occurred till that stage of production. Each time a product undergoes a value addition process - it becomes a new product.
Design Issue 1
================================
I have designed two transaction level fact tables to analyse these two acitvities at the most grainular level.
(1) Inventory Transaction fact table - For a product - each activity that results in invenotry change .
(2) Cost Transaction fact table - For a product each acitivity that results in the cost addition to that product.
Here is the example in its simplest case - say
(1) P1 is the raw material product with cost C1.
(2) P1 undergoes some process and becomes P2. Cost of the process is C2.
(3) P2 undergoes some process and becomes P3. Cost of the process is C3.
Inventory Transaction Table would be..
T1 | Purchased P1 | C1
T2 | Manufactured P2 from P1 | C2
T3 | Manufactured P3 From P2 | C3
Cost Transaction Table would be
P1 | C1 | T1
P2 | C1 | T1
P2 | C2 | T2
P3 | C1 | T1
P3 | C2 | T2
P3 | C3 | T3
Here we can have few Inventory Transactions (20 %) that do not result in Cost Transactions. Also we can have few cost Transactions ( may be 5 %) that are not related to the Inventory transactions.
As both these fact tables are huge and with the intention of avoiding join betweent the two fact tables, I intend to copy all about transaction details that I need for cost analysis ( and they are most of them) in Cost transaction, rather then referring to a row in the inventory transaction table from the cost transaction table. With confirmed dimensions these transaction details consists of mostly dimension keys and there would be around 12 to 13 of these keys. I know query performance would be good, but I am not feeling comfortable with so much of duplication. Do you think this is a good design ?? What alternatives exist ??
Design Issue 1
================================
I have designed two transaction level fact tables to analyse these two acitvities at the most grainular level.
(1) Inventory Transaction fact table - For a product - each activity that results in invenotry change .
(2) Cost Transaction fact table - For a product each acitivity that results in the cost addition to that product.
Here is the example in its simplest case - say
(1) P1 is the raw material product with cost C1.
(2) P1 undergoes some process and becomes P2. Cost of the process is C2.
(3) P2 undergoes some process and becomes P3. Cost of the process is C3.
Inventory Transaction Table would be..
T1 | Purchased P1 | C1
T2 | Manufactured P2 from P1 | C2
T3 | Manufactured P3 From P2 | C3
Cost Transaction Table would be
P1 | C1 | T1
P2 | C1 | T1
P2 | C2 | T2
P3 | C1 | T1
P3 | C2 | T2
P3 | C3 | T3
Here we can have few Inventory Transactions (20 %) that do not result in Cost Transactions. Also we can have few cost Transactions ( may be 5 %) that are not related to the Inventory transactions.
As both these fact tables are huge and with the intention of avoiding join betweent the two fact tables, I intend to copy all about transaction details that I need for cost analysis ( and they are most of them) in Cost transaction, rather then referring to a row in the inventory transaction table from the cost transaction table. With confirmed dimensions these transaction details consists of mostly dimension keys and there would be around 12 to 13 of these keys. I know query performance would be good, but I am not feeling comfortable with so much of duplication. Do you think this is a good design ?? What alternatives exist ??