Hi,
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?
Thanks a lot
Paco
