Concatenated table

[karensmith]

After so many problems with trying to created link table to link mulitiple fact tables, I have decided to concate all fact tables into one large table. For each fact table, I added a field to identify the type of data. Currently all fact types use the same qty and value fields. This okay when comparing activity among the different types in a line chart or bar chart, but I don't know how to calculate variances between the different types. Example:

Sales vs Forecast variance. Sales and Forecast was loaded from the same fact table in concatenated with other fact tables. Other fact tables are Open orders, booked orders, etc.. How can I do calculations on the same fields?

Your help is greatly appreciated.

[disqr_rm]

You can create multiple flag in the same fact table. Fir example

for sales data you would:

FACT:
LOAD
'Sales' as AmountType,
100 as Amount,
'M1' as Material,
.....
.....
1 as Flag_Sales,
0 as Flag_Frcst
FROM......

And for Forecast data you would:

FACT:
LOAD
'Forecast' as AmountType,
200 as Amount,
'M1' as Material,
.....
.....
0 as Flag_Sales,
1 as Flag_Frcst
FROM......

Then on the chart you would multiple with the corresponding flag to get that value. So to get just sales amount you would multiply Amount with Flag_Sales and you would get sum of Sales amount --> sum(Amount * Flag_Sales).

Hope I answered what you are looking for.