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

Two quick answers on RPD design: Foreign Keys

Why must line must be drawn from fact to dim in physical diagram join?

  When you create the join between the two tables, the table at the start point of the drawn line will be where the foreign key object will be created that describes the reference from the fact to the dim.  If one does not already exist, a key object will also be created on the join column(s) on the table at the end point of the drawn line (the dimension). So foreign key on fact, key on dimension as would be expected in a 1:M join.

Why should you uncheck foreign keys when importing tables?

The only references I could find to unchecking this box during import suggested that including the foreign keys might make the import take longer.    If the foreign keys are imported, it would still be a good idea to review all join conditions between the tables after importing.

Error when attempting to schedule an agent that has a calculation in an IS PROMPTED filter

When attempting to schedule an agent for an analysis that has IS PROMPTED filters, an error occurs if any of the filters are based on a calculation (for example, a case statement).  
You will need to save a copy of the analysis without those calculated filters in order to schedule the agent.

OBIEE Favorites Menu privileges

Came across this issue today.   Users with only BI Consumer application role were unable to see the Favorites link on their dashboards even though the privilege had been granted to BI Consumer on the Home and Header->Favorites Menu option on the Manage Privileges page in OBIEE.

The reason?  Because they also needed to have the Personal Storage (My Folders and My Dashboard) privilege granted as well under the Catalog section as well.   In this case, that privilege had only been granted to BI Authors.