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
“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?