Union, UNION ALL and Intersect in OBIEE

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).

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:

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: