Putting Horizontal and Vertical Records together in an OBIEE Report

Few days back one of friends came up with a very special requirement. He had 2 tables let's call them SALES_BUDGET_F, which stores the month wise budgeted sales, where the months are as the columns.

and SALES_ACTUAL_F, which stores the moth wise actual sales data, and this table is like a normal fact.

These 2 facts have joined by conformed dimensions Customer and Product. For the ease of our purpose in this case I have single dimension and let me call it CUSTOMER_PRODUCT_D. However having a single or multiple dimension will not cause any difference.

The objective is to show both the data together so that we can compare the Budget vs Actual.

Now before we proceed, I would like to mention this can be achieved through multiple ways, one of them can be creating an Opaque View in the  RPD or Materialized View. However in this case I will try to achieve the same through OBIEE.

In the logical layer I create a single fact with 2 LTS one for each of the physical facts. And the conformed dimensions are also mapped.

In the Fact - Sales, Year is mapped from both the facts. Actual Sales is computed from SALES_ACTUAL_F.

Month wise Budgeted Sales columns are calculated from month wise columns from the SALES_BUDGET_F. Like Jan Budgeted Sales = SALES_BUDGET_F.JAN

Now I start creating the report. First just to test if my tables are working fine, I create a simple report with Month ID (hard coded as 1 for Jan. I will be using it later for sorting) and Jan Budgeted Sales.

And it is working as desired.

Next I add columns for Month Name and Actual Sales (hard coded as 0)

I repeat it 11 more times in form of union for 12 months. And 1 additional criteria for Actual Sales, with Budgeted Sales column hard coded as 0.

Now let's check the result.

We need to get rid of these duplicate rows, and for that I use a Pivot view, with aggregation added on the measures.

And the net output looks like,

I put it in a dashboard with Customer, Product and Year prompt.

Before I conclude I would like to mention again that, this might be achieved through other ways too.