Return to Newsletter Contents...

Performance Management

by: Aaron Zechman, BI Consultant, CRCP, BOCP, MCSE

“Look before you leap.”

“Don’t put all your eggs in one basket.”

“Look both ways before you cross the street.”

What is the common theme between all these quotes?  No, it’s not things my mom said to me growing up.  It is that all these quotes provide words of caution.  Caution that should be taken into consideration when developing universe objects especially when using the WHERE clause.  Now you might say why do I need to use caution using the WHERE clause, let me explain.

Now let us say that the SALE_TYPE in the SALE table contains ‘S’ and ‘R’ where S is for Sales and R is for Rental.  We want to calculate the revenue for both sales and rentals.

 The sales revenue is calculated as:

SUM(SALES_MODEL.SALE_QTY * MODEL.MODEL_PRICE * ((100 – SALE.SALE_SALE_DISCOUNT)/100))

The rental revenue is calculated as:

SUM(SALE.SALE_RENTAL_PERIOD * SALE_MODEL.SALE_QTY * MODEL.MODEL_DAYRENT * ((100 – SALE.SALE_SALE_DISCOUNT)/100))

Now if we create a measure, for Sales Revenue, using the where clause it may look like the following:

Now the Rental Revenue would have its calculation and the only things that are changed is the sales revenue and in the where clause there will be SALE.SALE_TYPE = ‘R’.

The problem with this is not when you use either the Sales Revenue or the Rental Revenue individually; it is when you drag both of the measures onto the query panel.  It would give you the following code:

SELECT DISTINCT

  SUM(SALE.SALE_RENTAL_PERIOD*SALE_MODEL.SALE_QTY*MODEL.MODEL_DAYRENT*((100-SALE.SALE_SALE_DISCOUNT)/100)),

  SUM(SALE_MODEL.SALE_QTY*MODEL.MODEL_PRICE*((100-SALE.SALE_SALE_DISCOUNT)/100))

FROM

  SALE INNER JOIN SALE_MODEL ON (SALE.SALE_ID=SALE_MODEL.SALE_ID)

   INNER JOIN MODEL ON (SALE_MODEL.MODEL_ID=MODEL.MODEL_ID)

WHERE

( SALE.SALE_TYPE='R'  )

  AND  ( SALE.SALE_TYPE='S'  )

Now we can see the inherent problem with this is the WHERE clause if both measures are on the report.  The SALE.SALE _TYPE will never be both ‘R’ and ‘S’ at the same time so this will bring us back an empty result set or zero records.

Now you are asking how we might solve this problem.  It is not too difficult using the case statement.  Now the case statement is formed like the following:  CASE WHEN THEN END.  Let us redo the measure object for Sales Revenue using the case statement instead of the WHERE clause.  It should look something like the following:

Now notice I removed the where clause and used it in the case statement.  When the SALE.SALE_TYPE is ‘S’ it is going to perform the calculation otherwise it is going to add zero to the total giving us the total sales revenue.  Now we will need to repeat this same process for the Rental Revenue.  Now when this is done if both measures are dragged out into the query panel we will get the following SQL statement that is created:

SELECT DISTINCT

  SUM(

  CASE

    WHEN SALE.SALE_TYPE = 'R' THEN

      SALE.SALE_RENTAL_PERIOD*SALE_MODEL.SALE_QTY*MODEL.MODEL_DAYRENT*((100-SALE.SALE_SALE_DISCOUNT)/100)

    ELSE

        0

    END

),

  SUM(

  CASE

    WHEN SALE.SALE_TYPE = 'S' THEN

       SALE_MODEL.SALE_QTY*MODEL.MODEL_PRICE*((100-SALE.SALE_SALE_DISCOUNT)/100)

  ELSE

       0

  END

)

FROM

  SALE INNER JOIN SALE_MODEL ON (SALE.SALE_ID=SALE_MODEL.SALE_ID)

   INNER JOIN MODEL ON (SALE_MODEL.MODEL_ID=MODEL.MODEL_ID)

Now we can see that this query does not produce a where clause and that it is taken care of in the CASE statement and now the measures can coexists peacefully together.

So remember when you are designing you next universe, it is WHAT you know and not WHERE you know.

Go to Top  |  Return to Newsletter Contents