I am creating a data model but I have a complication, let me explain.
The DB source is very simple has one fact table and two dimensional tables.
First dimensional table is related with production sites
Plant ID
PlantName
Plant Type
Country
Region
1
Plant A
Type 1
Italy
Europe
2
Plant B
Type 1
France
Europe
3
Plant C
Type 2
Belgium
Europe
Second dimensional table is related with the metrics calculated per site
Metric ID
MetricName
Metric Type
Metric Units
Plant ID
1
Metric A
Type 1
Units A
1
2
Metric B
Type 2
Unit B
1
3
Metric B
Type 2
Unit B
2
The fact table contains the daily value for each metric for all production sites
Metric ID
MetricValue
Plant ID
Date
1
Metric A
1
01-01-17
2
Metric B
1
01-01-17
3
Metric B
2
01-01-17
In the load script all map is done so the IDs columns are replaced by the names
Unfortunately the fact table can not be used as it is due to several reasons. As example the agregation rules can vary by metric.
Therefore I use the generic statement to get a table per metric . When doing so the system create a syntethic key composed by Date+PlantName+MetricName. This key works well to create apps as value tables and dimension tables (production sites table and metric dimensional table) are linked, but I can not create any calculated measure (e.g. Metric A + Metric B) in an app as they do not share any comom key. I can overcome the problem by creating a shorther Key Date+PlantName which will allow me to create calculated measures (e.g. Metric A + Metric B). Unfortunately then the link between the values tables and metric dimensional table is nonexisting.
Is there a way to have all functionalities available?
