OBIEE in IL: OBIEE - Selecting Date range from a Prompt Without breaking aggregation awareness

In the past I wrote about "OBIEE Prompts - Last Year, Last Month and Custom Dates", it works fine and eventually passes date-based filters.

OBIEE developers often use aggregation awareness.

My customer wanted to combine the two methods, having prompt for selecting various date ranges (last month, last year, last 7 days, Yesterday...), but to use the aggregated data when possible.
In our example I will have two tables of fake data, with similar structure. One will have the daily data and the other aggregated by month. Both will share the same logical table and each will be used based on the content of the query.

In aggregation awareness case, each time I pass a date parameter, the lower, less efficient, level of date fact will be used, so I can't simply use my above mentioned post.

This post is written using OBIEE 12c, but should work with all previous versions. I'd be happy to hear if you have a better, more elegant, solution.

Starting point:

I have six tables:

F_DAY - Daily Fact (Sales of 10 of product x, each day between 1-Oct-2015 and 4-Nov-2015. That 310 in October and 40 in November).

F_Month - Monthly Fact, (wrong summary of F_Day. The wrong data lets me see easier what table is used, without checking logs). I use first day of month to describe month.

D_Prods - Product dimension with one row of data (surprisingly, it's x)
DDATES - Date Dimension that include a row for each day with various column such as DDATE (the date), MMONTH (number in format YYYYMM), Day_Order (Number in format YYYMMDD) and many others. The last 2 are important.
Month - subset of DDATES that has only the month, and above level data.
BI_Time_Filter - Filter table (actually a view) with various date ranges and start date, start month, end date, end month columns in each. A column named History, when it's value is 'Y', we should use the F_Month fact.

-------------------------------------------------------------

The following section simply covers basic aggregation awareness. The only non-trivial part is setting various columns from dates tables (such as Day_Order) on the relevant dates hierarchy level.

To make it work I create a dates logical table and Dimension.

In the table there are two data sources, both date tables.

the Month table does not have date specific columns, all the rest are common.

Note, both DDATE and Day_order column are defined as day level.

For each source, I assign the relevant level of data based on the hierarchy, in the content tab. For example the Month source:

Similar will be done with the Fact tables. 2 sources mapped to same columns and different levels of data for dates dimension of the Table F in the Business Model:

Here is the F_day Content tab set to Dates level of hierarchy (the F_Month has Month level) :

As a result, Analysis on month level return the "wrong" results from F_Month, as desired:

When introducing date level the system switches to F_Day table, even for such a "minor" changes as adding filter DDates >= 1-Jan-1900 and, in my case, returns different results:

-------------------------------------------------------------

End of aggregation awareness section.

Now I want to create a prompt on the periods selection table (BI_Time_Filter) and based on that selection create 3 hidden prompts. Those prompts should be dynamic in setting the filter for the relevant aggregation level. It should be similar to the methods used in "OBIEE Prompts - Last Year, Last Month and Custom Dates" mentioned above.

So I will create 3 prompts:

P_1 that has 2 possible values. Those are column formulas from dates Dimension: "DDATES"."MMONTHFULL" (the number of the month) and "DDATES"."DAY_ORDER" (the number of the day).
P_From that will accept starting number of month or Starting number of date from the prompt.
P_Till that will accept ending number of month or ending number of date from the prompt.

Now I just need to use a single filter in all my reports (I can save it and reuse it):

@{P_1}{"DDATES"."MMONTHFULL"} between @{P_From}{201510} and @{P_Till}{201511}

Or without default values:

@{P_1} between @{P_From} and @{P_Till}

for example for values

P_1= "DDATES"."MMONTHFULL"

P_From =201510

P_Till= 201511

I get:

While for

P_1="DDATES"."DAY_ORDER"

P_From =20151020

P_Till= 20151104

I get:

The last part would be creating the above mentioned prompts.

I need the periods selection table (BI_Time_Filter) available as a single, not joined table in the presentation level. Covered in "OBIEE - Model based on one table" post before, I'll fast forward it.

(I duplicate the BI_Time_Filter table in Business Model, and set a join between the duplicates on Business level only, no need for physical, it's the same table. Next move to the presentation only the dimension copy of the two).

Create a simple Period selection prompt that updates a P_Period variable.

Based on that value create the hidden prompt with the above mentioned 3 lines.

Here is the first line:

With default selection:

SELECT case when "BI_TIME_FILTER"."HISTORY"='Y' then '"DDATES"."MMONTHFULL"' else '"DDATES"."DAY_ORDER"' end saw_1 FROM "test_algo" WHERE "BI_TIME_FILTER"."DISPLAY_MSG" = '@{P_Period}{Previous Month}';

I hide it in the dashboard properties, under Dashboard Filters and Variables: