0 Replies Latest reply: May 4, 2017 11:05 AM by FRANCISCO JAVIER MASIP SOLER RSS

    Doubts creating properly a Data Model?

    FRANCISCO JAVIER MASIP SOLER

      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 IDPlantNamePlant TypeCountryRegion
      1Plant A Type 1 Italy Europe
      2Plant BType 1FranceEurope
      3Plant CType 2BelgiumEurope

       

      Second dimensional table is related with the metrics calculated per site

      Metric IDMetricNameMetric TypeMetric UnitsPlant ID
      1Metric A Type 1Units A1
      2Metric BType 2Unit B1
      3Metric BType 2Unit B2

       

      The fact table contains the daily value for each metric for all production sites

      Metric IDMetricValuePlant IDDate
      1Metric A 101-01-17
      2Metric B 101-01-17
      3Metric B 201-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