2 Fact tables

[Anonymous]

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

[DataInsights · Accepted solution]

@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.