BRS_2012
asked on
Fact Table design problem
I am having fact table with multiple measures. Some of these measures are derived from other measures. The fact data is stored in the lowest level granularity of each dimension.
Sample data is given below:
Fact_Id Dimension1_key Dimension2_Key Measure1 Measure2 Measure3
1 1 1 10 5 20
2 1 2 20 10 40
Here,
“Measure2” derived from “Measure1” as (Measure1/2) (some kind of expression)
And “Measure3” derived from “Measure1” as (Measure1*2) (another kind of expression).
In the system, we need to implement the ability to modify the Measure1 value and verify the impact on other measures (Measure2 and Measure3).
At any given point of time 5000 (max) such modifications are allowed on Billion row fact table.
In this case what kind of design you guys suggest which will be performant for both transactions and reporting?
Sample data is given below:
Fact_Id Dimension1_key Dimension2_Key Measure1 Measure2 Measure3
1 1 1 10 5 20
2 1 2 20 10 40
Here,
“Measure2” derived from “Measure1” as (Measure1/2) (some kind of expression)
And “Measure3” derived from “Measure1” as (Measure1*2) (another kind of expression).
In the system, we need to implement the ability to modify the Measure1 value and verify the impact on other measures (Measure2 and Measure3).
At any given point of time 5000 (max) such modifications are allowed on Billion row fact table.
In this case what kind of design you guys suggest which will be performant for both transactions and reporting?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
My suggestion for the dependent measures is if you are loading the fact table from ETL then add derived column to the ETL and do the calculation for measure2 and 3 over there.
OR
You can also set the computed column in the Fact Table structure what that will do is when any of the fact record will be inserted the rest tow measure columns will be calculated automatically.