6 Replies Latest reply: Jul 5, 2012 8:55 AM by Srini VEERAVALLI RSS

    BIEE 10g combine month and day data in one report

    Andy Nierhaus
      Hi

      I am trying to build a simple model to report employee absence per day against number of employees per month.

      There are two challenges:
      a) not setting content level of No_of_employees, drilling down along calendar to day level shows the Absence_day numbers, but not the number of employees for the month
      b) setting the content level of No_of_employees to month repeats the numbers correctly for each day in a month, but does not aggregate No_of_employees at a higher level (quarter or year)


      How do I achieve a report like this without loosing the ability to aggregate numbers at year level?

      Report sample

      Year     Month     Date     No_of_employees     Absence_day
      2012     June     06/01     15000               10
      2012     June     06/02     15000               8
      2012     June     06/03     15000               14
      2012     June     06/04     15000               5
      2012     June     06/05     15000               6
      ...
      ...


      OBIEE Version: 10.1.3.4.1 (not patched in a while)

      The calendar dimension has been build using OWB. Year, quarter and month level do contain negative dimension keys for values at these levels and f_employee joins to the negative values. The lowest level is day (positive dimension key values).

      Month June 2012; D_calender.dim_key = -50 (negative value !)
      Dates in June 01 - 30, D_calender.dim_key = 100 - 130 (positive values)

      Dimension: Calendar
      logcial levels: year - Quarter - Month - Detail (= date)
      logical key: Date


      LTS Fact 1: employees
      at month level
      physical join: f_employee.cal_fk = D_calender.dim_key
      measure: No_of_employees
      aggregation: sum
      logical:
      LTS content level: Calender dimension set to Month

      LTS Fact 2: absence
      at day level
      physical join: f_absence.cal_fk = D_calender.dim_key
      measure: Absence_day
      aggregation: sum
      logical:
      LTS content level: Calender dimension set to Detail (= date)

      Both facts are LTS for the same Logical Fact table.

      Thanks for your help

      Regards
      Andy
        • 1. Re: BIEE 10g combine month and day data in one report
          Dhar
          Hi Andy,

          I am sorry, If I misinterpreted some details, but how about having a 'Level based measure' for employees absent by month. In the analysis, with this level based measure you could report both employees absent by day and month.

          Hope this helps.

          Thank you,
          Dhar
          • 2. Re: BIEE 10g combine month and day data in one report
            Andy Nierhaus
            Hi Dhar,

            thanks for your answer. It would work if I only had to report on absence per month.
            The sample I described is a reduced model to make sure that I set up the two facts and one dimension correct before adding additional dimensions. In the end it should be possible to use No_of_employees and Absence_day in different reports, aggregating up to higher levels, drilling down to day level without loosing information or the ability to aggregate on year level. Also if someone would need to do some calculations on report level (Absence_days / employees_per_month).
            Basically what I am trying to achieve is a model that allows my to combine measures at different levels of the same dimension in one report avoiding NULL values for the measure at month level when drilling down to day level. At the same time being able to aggregate to higher levels.

            Andy
            • 3. Re: BIEE 10g combine month and day data in one report
              Andy Nierhaus
              The SQL I get when executing a request:
              If I could get Answers to display values of c9 instead of c5 (last few lines of the SQL), my problem may be solved


              WITH
              SAWITH0 AS (select D1.c1 as c1,
              D1.c2 as c2,
              D1.c3 as c3,
              D1.c4 as c4
              from
              (select sum(nvl(T116806.FRAVAERSDAG , 0)) as c1,
              T116670.DATO as c2,
              T116670.AAR_NR as c3,
              T116670.MAANED_NAVN as c4,
              ROW_NUMBER() OVER (PARTITION BY T116670.DATO ORDER BY T116670.DATO ASC) as c5
              from
              DM2.D_KALENDER_HIER T116670 /* Aggregation_D_Kalender_Hier */ ,
              DM2.F_FRAVAERSPERIODE_DAG_V2 T116806 /* Aggregation_F_FRAVAERSPERIODE_DAG_V2 */
              where ( T116670.AAR_NR = 2012 and T116670.DIMENSION_KEY = T116806.D_KALENDER_FK and T116670.MAANED_NAVN = 'April' )
              group by T116670.AAR_NR, T116670.DATO, T116670.MAANED_NAVN
              ) D1
              where ( D1.c5 = 1 ) ),
              SAWITH1 AS (select D1.c1 as c1,
              D1.c2 as c2,
              D1.c3 as c3
              from
              (select sum(T116663.ANSAT) as c1,
              T116670.AAR_NR as c2,
              T116670.MAANED_NAVN as c3,
              ROW_NUMBER() OVER (PARTITION BY T116670.MAANED_NAVN ORDER BY T116670.MAANED_NAVN ASC) as c4
              from
              DM2.D_KALENDER_HIER T116670 /* Aggregation_D_Kalender_Hier */ ,
              DM2.F_ANSAT_I_MAANED_LOENUDB_V2 T116663 /* Aggregation_F_ANSAT_I_MAANED_LOENUDB_V2 */
              where ( T116663.KALENDER_DIM_HIER = T116670.DIMENSION_KEY and T116670.AAR_NR = 2012 and T116670.MAANED_NAVN = 'April' )
              group by T116670.AAR_NR, T116670.MAANED_NAVN
              ) D1
              where ( D1.c4 = 1 ) )
              select case when SAWITH1.c2 is not null then SAWITH1.c2 when SAWITH0.c3 is not null then SAWITH0.c3 end as c1,
              case when SAWITH0.c4 is not null then SAWITH0.c4 when SAWITH1.c3 is not null then SAWITH1.c3 end as c2,
              SAWITH0.c2 as c3,
              SAWITH0.c1 as c4,
              cast(NULL as INTEGER ) as c5,
              SAWITH0.c1 as c6,
              SAWITH1.c1 as c9
              from
              Sawith0 Full Outer Join Sawith1 On Nvl(Sawith0.C4 , 'q') = Nvl(Sawith1.C3 , 'q') And Nvl(Sawith0.C4 , 'z') = Nvl(Sawith1.C3 , 'z')
              order by c1, c2
              • 4. Re: BIEE 10g combine month and day data in one report
                Dhar
                Hi Andy,

                I guess, you are coming from the point that "Measures at Month level tend to NULL" when drilling down to "Day level" in the report. In that case, I could suggest an approach of navigating across reports instead of drilling.

                For Ex:

                Parent report:

                Month, Absence_Day

                Drilled down report:

                Month, Day, Absence_Day (Absence _day was split by Day now) which I guess is not your intention.

                Navigated report:

                Month, Month_Absence_Day(Absence_day still at month level), Day, Absence_day(absence_day at day level).

                Hope I was clear.

                Thank you,
                Dhar
                • 5. Re: BIEE 10g combine month and day data in one report
                  Andy Nierhaus
                  Hi Dhar,

                  I'll give it a thought.

                  Any idea why a level measure at month level wouldn't aggregate into a higher level (year)?


                  Regards

                  Andy
                  • 6. Re: BIEE 10g combine month and day data in one report
                    Srini VEERAVALLI
                    Level based measures are group by that particular level, these values are not interactive by drill down.