Tuesday, September 23, 2014

Level based measure example - #OBIEE #RPD

Here’s an example of level based measure.   I created a new measure on the Booking Line Amount
that set the content level on the measure to Year on the Date dimension.  When I run a query with Year, Booking Line Amount, and Booking Line Amount Year  I get the same value for both metrics:




Now, if I add in the Month, I will get the Booking Line Amount for the Month but the Booking Line Amount Year will remain the same across all months:



So I can now create a calculation that shows the percentage of the year’s total for each month:




The only change on the logical column is this:


 









And now that metric shows up under the Date dimension hierarchy Year level:






The SQL for the query with Year and Month looks like this.. the red highlighted part is where it is rolling up the amount by year for that metric:

WITH
SAWITH0 AS (select sum(case  when T405715.BOOKING_ORD_QTY_LBS * T405715.BOOKING_UNIT_PRICE_LBS is null then T405715.BOOKING_ORD_QTY_EA * T405715.BOOKING_UNIT_PRICE_EA else T405715.BOOKING_ORD_QTY_LBS * T405715.BOOKING_UNIT_PRICE_LBS end ) as c1,
     T333493.PER_NAME_YEAR as c2   -- this C2 is referenced below for the rollup by year,
     sum(case  when T405715.BOOKING_ORD_QTY_LBS * T405715.BOOKING_UNIT_PRICE_LBS is null then T405715.BOOKING_ORD_QTY_EA * T405715.BOOKING_UNIT_PRICE_EA else T405715.BOOKING_ORD_QTY_LBS * T405715.BOOKING_UNIT_PRICE_LBS end ) as c3,
     T333493.PER_NAME_MONTH as c4,
     T333493.X_MONTH_SORT as c5,
     T333493.X_YEAR_SORT as c6
from
     W_DAY_D T333493 /* Dim_W_DAY_D_Common */ ,
     WC_QBBPF_F T405715 /* Fact_WC_QBBPF_F */
where  ( T333493.ROW_WID = T405715.DATE_WID )
group by T333493.PER_NAME_MONTH, T333493.PER_NAME_YEAR, T333493.X_YEAR_SORT, T333493.X_MONTH_SORT),
SAWITH1 AS (select sum(D1.c1) over (partition by D1.c2)  as c1,
     D1.c2 as c2,
     D1.c3 as c3,
     D1.c4 as c4,
     D1.c5 as c5,
     D1.c6 as c6
from
     SAWITH0 D1)
select D1.c1 as c1, D1.c2 as c2, D1.c3 as c3, D1.c4 as c4, D1.c5 as c5, D1.c6 as c6, D1.c7 as c7 from ( select distinct 0 as c1,
     D1.c4 as c2,
     D1.c2 as c3,
     D1.c5 as c4,
     D1.c6 as c5,
     D1.c1 as c6,
     D1.c3 as c7
from
     SAWITH1 D1
order by c5, c4 ) D1 where rownum <= 65001



No comments:

Post a Comment