OBIEE same dimension different value measures

I was inspired by this question on OTN forum http://forums.oracle.com/forums/thread.jspa?threadID=930469&tstart=15 and want to resolve this in Administrator by using one of data moddeling techniques.

To repeat, the problem was how we can put 2 parameters to the user (month one, month two) so after selecting values from them the report need to be like this:

Name Month1 Month2 Delta
Mark 6 4 -2
Paul 7 8 +1
Ken 10 2 -8

while we have, of course, detailed data in table.

Now, to show the solution how to resolve this in Administrator I'll use only TIMES, PRODUCTS and SALES table from SH user.

The first step is to create aliases for TIMES table in the physical layer. Why? Because we need to have two TIMES logical tables, each one is filtered by one parameter (dashboard prompts, month one and month two). We create alias for SALES table to, to add second logical table source into SALES logical fact table in the BMM to allow OBIEE server to generate second query for second filter.

This is our physical diagram:



We add PRODUCTS table as this is required for joining these two fact tables, because we will retrieve information from both fact tables (both measures) and for group by we use only PRODUCTS attributes.

Now, after we implement our model in the BMM with complex joins and three separate logical dimensions (TIMES, TIMES2 and PRODUCTS) and one logical fact table (SALES) we put two logical table sources inside it (SALES and SALES2) and each has own measures (AMOUNT_SOLD1 and AMOUNT_SOLD2). With this model OBIEE server will generate two queries, one is SALES-TIMES and the second is SALES2-TIMES2. Each combination is for one value from the filter (dashboard prompt, presentation variable, etc).

Logical diagram with model:



AMOUNT1_AMOUNT2_DIFF logical column:



You can just put ABS(SALES.AMOUNT_SOLD1-SALES.AMOUNT_SOLD2).

Add tables to the presentation area:



Make two dashboard prompts for each month. Can be with TIMES.CALENDAR_MONTH_DESC and TIMES2.CALENDAR_MONTH_DESC in the column editor and with these columns set to IS PROMPTED in the report filter.



Or can be like this. In this case you need to use presentation variables in the report filter.



Report:



or, if you use presentation variables:



Result:



NQQuery.log:





*Note
If you drop PRODUCTS.PROD_CATEGORY column OBIEE cannot join these two measures no mather if you include different dimensions (TIMES, TIMES2) in the filter and we get an error: The query does not reference any tables.

We could get the same report at the beginning of this post, without our new data model (TIMES-SALES and TIMES2-SALES2), with just using TIMES-SALES, and this we only aply in Answers:

PRODUCTS:PROD_CATEGORY

FILTER(SALES.AMOUNT_SOLD1 USING (TIMES.CALENDAR_MONTH_DESC = '@{pv_month_one}'))

FILTER(SALES.AMOUNT_SOLD1 USING (TIMES.CALENDAR_MONTH_DESC = '@{pv_month_two}'))

ABS
(
FILTER(SALES.AMOUNT_SOLD1 USING (TIMES.CALENDAR_MONTH_DESC = '@{pv_month_one}'))
-
FILTER(SALES.AMOUNT_SOLD1 USING (TIMES.CALENDAR_MONTH_DESC = '@{pv_month_two}'))
)

The result is the same:



The NQQuery.log is slightly different:



FILTER USING is also a gut solution.