1 Reply Latest reply: Nov 29, 2012 1:09 AM by Maqs-Oracle RSS

    Modeling issue

    Ric79
      Hi all,
      the business that I need to model is this:
      - every 15 minutes, error-alarms-summaries are collected in a fact table. Each error-alarm-summary belongs to a network node and report the number of errors by severity (A1,A2,A3)
      - every 15 minutes, in another facts are collected general KPI (Network QoS, bandwidth, etc)
      F1: Dim_Dates, Dim_Times, Dim_NetworkNode
      Date   | Times | NetworkNode | Num_Err_A1 | Num_Err_A2 | Num_Err_A3
      29/Nov | 18:00 | Nodes1      | 1          | 0          | 0
      29/Nov | 18:15 | Nodes1      | 2          | 0          | 0
      29/Nov | 18:15 | Nodes2      | 1          | 1          | 0
      29/Nov | 18:30 | Nodes3      | 0          | 0          | 3
      29/Nov | 18:30 | Nodes4      | 0          | 1          | 3
      29/Nov | 18:45 | Nodes1      | 1          | 0          | 1
      F2: Dim_Dates, Dim_Times
      Date   | Times | QoS_Num | Qos_Denom | Bytes 
      29/Nov | 18:00 | 80      | 100       | 20
      29/Nov | 18:15 | 99      | 100       | 30
      29/Nov | 18:30 | 89      | 100       | 40
      29/Nov | 18:45 | 79      | 100       | 50
      I need, based on date grouping (hour quarter, hour, day, week, month, year), to report like this
      Report1
      Date   | Times | QoS_Perc | Sum_Bytes | Distinct_NetworkNodes | Sum_Err_A1 | Sum_Err_A2 | Sum_Err_A3
      29/Nov | 18:00 | 80%      | 20        | 1                     | 1          | 0          | 0
      29/Nov | 18:15 | 99%      | 30        | 2                     | 3          | 1          | 0
      29/Nov | 18:30 | 89%      | 40        | 2                     | 0          | 1          | 6
      29/Nov | 18:45 | 79%      | 50        | 1                     | 1          | 0          | 1
      Report2
      Date   | TimesHour | QoS_Perc | Sum_Bytes | Distinct_NetworkNodes | Sum_Err_A1 | Sum_Err_A2 | Sum_Er_A3
      29/Nov | 18        | 86%      | 140       | 4                     | 5          | 2          | 7
      In this case, in BMM, do I need to create just one logical fact table (joining dates and times) and setting aggregate measures?

      Could someoune provide me feedback or links for Obiee 11g?

      Thanks!
      Riccardo