Friday, November 15, 2013

OBIEE - Calculation between results of a union





Some times the data model does not allow you to create a single query to get the results you need. then we are forced to do a Union. (actually they are 4 possible operations Union, Union All, Minus and Intersect). The default is Union. I seldom prefer Union All over Union, since it's a little faster in the DB (doesn't check the intersect).


This post is about additional column that does calculations between measures from 2 parts of the Union. In this case division.
Just as an example I will create an unnecessary Union based on SampleSales. One query with "Product Type" and "Revenue" and the other with "Product Type" and "Billed Quantity".
What I want to get is a result of "Revenue" divided by "Billed Quantity" when each of them comes from a different part of the Union.
I will show 2 ways to do it. (I personally prefer the second, but the first is considered more general and intuitive than the other). 

Option 1: separate columns and zeros in the other part of the union.

I created a union where: 
The first part is       "Product Type", "Revenue" and zero. 
The second part is "Product Type" , zero          and "Billed Quantity".

Why in the picture my Zero is Sum(0) and not just 0? Because the parallel column in the analysis is "Billed Quantity" and it's defined as aggregated measure (Sum) by default. It's always best to unite apples with apples and not oranges.
Same goes with the other half of the union:
"Product Type", sum(0) and "Billed Quantity".
 
Now I selected the "Result Columns" part and pressed on Add result Column on the left:

I was a bit lazy and named the "Billed Quantity" column B.Q. sorry.
You might think that is enough to add a column that divides the second column (Revenue) by the third column (Billed Quantity). But it doesn't work and the division result is 0 both in Table and in Pivot Table:
I'll have to do some extra work.

I added 3 result columns that exist only on the union result level. Actually only the last one was enough, but doing all 3 will explain the process.  
 I add a column that will return a total Revenue on the Union level. While I select from the column selector Sum("Revenue" by "Product Type").


what I actually see in the formula is Sum(saw_1 by saw_0). saw_0 is the first column and so on... i'll call the column "R".


Similarly, the "Q"  column is Sum("Billed Quantity" by "Product Type"), that is translated to Sum(saw_2 by saw_0).
What I actually wanted is to divide "R" by "Q". actually this column alone is sufficient. the Formula is: Sum(saw_1 by saw_0)/Sum(saw_2 by saw_0)
 Why to add the division (or multiply) by 1.0 as you see in the picture? To hint OBI it's not an Integer.
The result is (left is Table and right is Pivot):

Pivot presentation eliminates the double rows, but the division result is doubled!
You might be inclined to divide the formula by 2, so it's Sum(saw_1 by saw_0)/Sum(saw_2 by saw_0)/2.0, but the calculation in the Table will be wrong.

What is better it to change, in pivot, the aggregation rule of the division column from default  to "Server Complex Aggregate".  Now all is perfect: 

Naturally you will leave the Pivot presentation only:

 

Option 2: same column measures and description column

In this option, the measure in both parts of the union are at the same place and I have an additional column that describes the origin of the data. In my case 'R' for "Revenue" and 'Q' for "Billed Quantity" (later they will act as column names) I names this column "value".
The above is the first query in Analysis.
The above is the second query in Analysis.

The result in Pivot is:
Now for the "value" column I added a "New Calculated Item" I named 'div":
The calculated Item is $2/$1 (second value divided by first, in this case R/Q).
This is the desired result:
Now I just want to remove the misleading name OBI automatically gave to the combined Revenue and Billed Quantity (Revenue).
There are few options, in this case I will just move the "Measure Labels" to the Excluded section.

Now it's perfect.
By the way, the calculation will remain correct even if I change the sort order of the "value" column:



One more example of what can be done with calculations between result of a union, you can see it the post "OBIEE with Key / Name / Value table, the Union solution".

11 comments:

  1. Hi Mate,
    Can i have your email id?

    ReplyDelete
  2. how can i sort by div?

    ReplyDelete
  3. When you want to Invoice Line numbers, how do you do it dummy? If it is populated in one line and not in the other?

    ReplyDelete
    Replies
    1. Not sure what your problem is.
      Option 1 of your case: You can create a calculation between columns above the union level.
      Option 2: you don't need to do anything, if it's the same column, it's aggregated by default.

      Delete
  4. Thanks for your reply. I am continuing the same topic here. I have a report which has Invoice #, Line #, Check Amount, Check Number, Invoice Line Number, PO Number fields. Check Amount and Invoice Amount are coming in two different line when i do union, so i followed the trick above to get it in one line. As I am filtering on the check date for this report(check date is populated for the lines which have Check Amount), the Distribution Line #'s are null for the lines which have Check Date, So it is not showing If I add Invoice Line #,PO # which are populated. Other fields also which have data where the check date is NULL is not showing up in the report. Hope it is clear now. Please advise.

    ReplyDelete
  5. Hi. How do we replicate the result in a performance tile? Struggling as I can't change the aggregation rule. Please advise.

    ReplyDelete
  6. Fantastic. I was struggling with a requirement for days and your article helped resolve part of it in 5 minutes.

    ReplyDelete
  7. Very helpful. Thanks a lot..!!

    ReplyDelete