0 Replies Latest reply on Mar 7, 2013 4:15 PM by metalray

    Star schema design, metrics dimension or not.

      Hello Guys,
      I just heard from one of my colleagues that its wise to
      have an "KPI" or "metrics" dimension in my DWH star schema (later used in OBIEE).
      Now, we have quite a lot of data 100 000 rows per day (botton leve, non-aggregated, the aggregations are obviously far less then that, lets say 200 rows per day) and
      we have build pre-aggregated data marts for each of the 5 very static reports (OBIEE Publisher).
      The table structure is very simple
      Date,County,NumberofCars,RevenuePerCar, ExpensesPerCar, BreakEvenPerCar, CarType

      One could exclude the metrics "NumberofCars","RevenuePerCar", "ExpensesPerCar", "BreakEvenPerCar"
      and put them into a metrics dimension.

      MetricID Metric
      1 NumberofCars
      2 RevenuePerCar
      3 ExpensesPerCar
      4 BreakEvenPerCar

      and hence the fact table design would be simpler.
      Date,County,MetricID,Metric, CarType

      Disadvanatages: A join is required
      We would have to redesign our tables
      tables are not aggregated anymore for specific metric types
      if we notice performance is bad, we would need to go back to the old design

      Advantages : Should new metrics appear, we dont have to change the design of the tables
      its probably best practice

      Note: date, country and cartype are already dimensions. we are just missing one to differentiate the metrics/KPI's

      So I struggle a bit, what should I do? Redesign, or stick to the way I have done it, having
      performance optimization in mind.