Forum Discussion
2 Fact tables
Hi All
Can anyone help me to figure out how to deal with 2 Fact tables, one being "actuals", and the other one being "forecast". Both tables I can link via Material+period, but for the forecast I realized that I have different versions of it, so F1, F2, F3... So when I merge those tables, my actuals are added to all lines which is linked to different forecast versions, but in reality I want to compare the actuals with only 1 version. Any solution someone here can propose?
Exemple: Material 123456 in Jan/2022
Forecast 1 = 50 Actuals: 50
Forecast 2 = 55
Forecast 3 = 45
Anonymous,
It's best to keep fact tables separate instead of merging them. Each fact table should have relationships with dimension tables, resulting in a star schema. Typically, actuals has day grain and forecast has monthly grain. If this is the case with your data, create a calculated column in the forecast table that is a date (use the first day of the month). This will enable you to create a relationship between the forecast table and date table.
1 Reply
Anonymous,
It's best to keep fact tables separate instead of merging them. Each fact table should have relationships with dimension tables, resulting in a star schema. Typically, actuals has day grain and forecast has monthly grain. If this is the case with your data, create a calculated column in the forecast table that is a date (use the first day of the month). This will enable you to create a relationship between the forecast table and date table.