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:

Here is the result (once with 'This Month', that works on Day level data and once with 'This Year', with Month Level data):

If you want to, you can further complicate it with additional options such as Custom Dates (with extra Prompt for the Custom Values)...

OBIEE - Selecting columns dynamically - part5, the single prompt solution

If you read posts 2,3,4 of this "OBIEE - Selecting columns dynamically" saga, you can create a set of prompts where each prompt controls a column and enables the user to select the column formula or decide not to show it at all. 

Something like this:

But the users might ask you to have a single prompt with friendly column names, in which they can select all the relevant columns for the analysis (or 2 prompts, one for attributes and one for measures).
Something like one of those 2:

This is what this post talks about.

What I intend to do?

1. Create the above prompt to allow the user to select desired columns.
2. Create a hidden set of prompts where I split the above prompt to column names and have additional hidden prompts for their actual formula.
3. Create an analysis where the column values are either the selected column or the None / Hidden  column from solution 2 of post 4 of this series.  

As a result while user doesn't select any columns, the analysis is mostly empty and as columns are selected it grows.  

For example in my analysis there is only one default measure column, so all I see is:

But if I select few values in the prompt:

I get the following result of the same analysis:

How to achieve it?

To get such a list I will create a table of prompt values, in this case, column names. Since I was lazy, I created it by a set of "select from dual", instead of actual table (don't do it in real life):

With 4 columns:

• Column Name - A user friendly representation of the column (example 'Year')
• Column Code - The formal Column definition in Analysis formula (example ( "Time"."T05 Per Name Year")
• Column Type - In case I decide to split the prompt between attributes and measures and deal with special formats such as date.
• Column Sort  - This will allow me to control the selected values in prompt. (better explanation later in this post).
  

Next I created a specific Business Mode just for the Prompt table and use one of the options for single table Model described here.  In my case, I duplicated the table at the Business layer and defined the copy as Dimension (after adding a key). Later I used only the "Fact" part in the Subject Area (See presentation layer on the left, Business Model on the right):

With that, I can create a prompt based on the column col_name. It can be a check boxes:

Or  a choice list:

  
Now I have a Presentation Variable (P_col_list) with all the column "friendly" names I want.
It's value might be something like this:'Company, Customer Name, Department'. 
What I need is to find columns that are in that string and control how do I get them in some controlled method.

The naive approach is treating the P_col_list Variable as one long string and cut it to peaces. I don't recommend it. The functions are long and unfriendly. It's much easier to filter the column table by this presentation Variable and then select a specific row, using the Rank function (with different value of Rank for each final Column).
So I need to check for each potential column, it's in the P_Col_list and select it's rank to get them in an orderly manner.

To check if the column is in the P_col_list I will use the function Locate: LOCATE("Prompt List"."Col_name",'@{P_col_list}') .To get the Ranking I will use RANK("Prompt List"."col_sort") . (The "col_sort' column from above allows me to control the columns. I can set numeric values there and use them. You might notice my values of col_sort column goes from higher to lower. Since the Ranking goes, by default, from larger to smaller values, that is the way to control the order of columns.)

(There is another / better option instead of the Locate above. See P.S 2 why I didn't use it)  

For example, the first selected column formal column name is:


SELECT "Prompt List"."Col_code" saw_0
FROM "ZZ_Prompt_list"
WHERE (LOCATE("Prompt List"."Col_name",'@{P_col_list}') > 0)
 AND RANK("Prompt List"."col_sort") = 1


and the first "friendly" name is:

SELECT "Prompt List"."Col_name" saw_0
FROM "ZZ_Prompt_list"
WHERE (LOCATE("Prompt List"."Col_name",'@{P_col_list}') > 0)
AND RANK("Prompt List"."Col_sort") = 1



In a similar way the second pair would be: 

SELECT "Prompt List"."Col_code" saw_0
FROM "ZZ_Prompt_list"
WHERE (LOCATE("Prompt List"."Col_name",'@{P_col_list}') > 0)
 AND RANK("Prompt List"."col_sort") = 2


SELECT "Prompt List"."Col_name" saw_0
FROM "ZZ_Prompt_list"
WHERE (LOCATE("Prompt List"."Col_name",'@{P_col_list}') > 0)
AND RANK("Prompt List"."Col_sort") = 2

 


So I decided that my case would have up to 5 dynamic columns (you can do as many as you like) and my second (soon to be hidden) prompt is, named one_list_actual:

Where P_C1A is the friendly Prompt name and P_C1 is the formal formula.

P_C1:

P_CA1: 

The analysis would be:
1. A set of 5 columns with formula like:IFNULL(@{P_C1}, "None"."None") and Column Header like: @{P_C1A}:

2. The original '@{P_col_list}' prompt, defined as hidden in the column properties. (I need it so the analysis "knows" that prompt changed values and refresh).
3. As many columns I want that would be constant in the analysis. It should be at least one measure from a fact for the system to always work properly. It's "Base Facts"."2- Billed Quantity" in my case.

This is how the criteria looks:

 

The last step i did was to put the 2 Dashboard Prompts and the analysis in a single dashboard and see it all works fine and then hide the second prompt (one_list_actual) with all the P_C... variables.

 



After selecting few values:

Last part is to hide the second prompt.I already explained this step in the post: OBIEE - Creating dependent - implicitly declared presentation variable: In Dashboard Properties, under Filters and Variables I'll add the Dashboard Prompt one_list_actual.

and delete it from the dashboard.
this is how it looks with no values selected.

thi sis after selecting several values in the prompt:

You might want to split the Prompt that select column to 2 prompts, one for measures and one for attributes. The "Null" column for measures should probably be default as 0.0, to force it to be float.


   

P.S. 1

In case I wanted the Prompt solution to be reusable in many dashboards, I don't need many tables of column lists. I could add a column / columns with filtering values and filter the prompt by it, each time.

P.S. 2

LOCATE("Prompt List"."Col_name",'@{P_col_list}') will return numbers greater than zero if the column name is in the Presentation Variable. So we can use the condition:  (LOCATE("Prompt List"."Col_name",'@{P_col_list}') > 0). It is problematic in cases where one value is contained in another, for example 'Product' and 'Product Type'. To prevent this problem you might add a comma string before and after the P_col_list an search for values with comma before and after.
(LOCATE(','||"Prompt List"."Col_name"||',',','||'@{P_col_list}'||',') > 0

 So the select statement for P_C1 should be actually:
SELECT "Prompt List"."Col_code" saw_0
FROM "ZZ_Prompt_list"
WHERE locate(','||"Prompt List"."Col_name"||',', ','||'@{P_col_list}'||',')>0
AND (RANK("Prompt List"."Col_sort") = 1)

Why did I use it and not a better option of looking for specific value in multivalued Presentation variable,cover  here by Deepak:
If I want to check whether the column "Prompt List"."Col_name" is part of the values in presentation Variable P_col_list, I can run the following: WHERE "Prompt List"."Col_name" IN (@{P_col_list}['@']) .
Well I had some voodoo problem with. When I didn't select any value in the initial P_col_list variable I had an error:

Error Codes: OPR4ONWY:U9IM8TAC:OI2DL65P:OI2DL65P

State: HY000. Code: 10058. [NQODBC] [SQL_STATE: HY000] [nQSError: 10058] A general error has occurred. [nQSError: 43113] Message returned from OBIS. [nQSError: 27002] Near <,>: Syntax error [nQSError: 26012] . (HY000)

SQL Issued: {call NQSGetQueryColumnInfo('SELECT IFNULL(, "None"."None") FROM "A - Sample Sales"')}

SQL Issued: SELECT IFNULL(, "None"."None") FROM "A - Sample Sales"

It happens only if no columns at all where selected. One default was enough to prevent this error.   

P.S. 3

I would like to thank Rakefet Barad from Oracle ACS and Dov Ziskin from Vision BI for the chats we had on the issue.

OBIEE - Selecting columns dynamically - part4, working with and selecting none

In this part I will talk about how to remove column from analysis with column selector or prompt. That means, selecting a column I don't want to present (or actually influence the analysis at all). Why do I want such a stupid thing? When working with dynamic columns, I want the ability to control the number of columns. So if the user wants only 3 columns, out of possible 5, only 3 columns would appear in the analysis. The other 2 columns will not show.

How do I add such "None" column?
I will describe 2 options. First is created at the User Interface level, the second in RPD only and works better with session variables. Usually the second should be your default option and it is the one to be used in the next post.

Solution 1 (see update bellow)

I add a column in the repository with empty value and default this column to be hidden, system wide. Now I can use the column anywhere I want. Lets have a detailed explanation.

I'm working with the extended sample available as part of the VM here. So I'm working with real Oracle DB tables in this case. (The following doesn't work well with the XML files of the default Sample).
In the Business layer I randomly selected the Time dimension and added a column with '' value. I named it None.

As a next step I created a new Presentation Table under the Sample Sales and named the table None, then dragged the none column under it.
Now I have the None column. OBIEE enables me to create analysis with this column:

What is nice here, is the fact that the SQL doesn't join unnecessary tables:
This is the left one (only the fact table in the original From):

WITH 
SAWITH0 AS (select sum(T42433.Revenue) as c1,
     '' as c2
from 
     BISAMPLE.SAMP_REVENUE_F T42433 /* F10 Billed Rev */ ),
SAWITH1 AS (select D1.c1 as c1,
     D1.c2 as c2,
     D1.c3 as c3
from 
     (select 0 as c1,
               D1.c2 as c2,
               D1.c1 as c3,
               ROW_NUMBER() OVER (PARTITION BY D1.c2 ORDER BY D1.c2 ASC) as c4
          from 
               SAWITH0 D1
     ) D1
where  ( D1.c4 = 1 ) )
select D1.c1 as c1, D1.c2 as c2, D1.c3 as c3 from ( select D1.c1 as c1,
     D1.c2 as c2,
     D1.c3 as c3
from 
     SAWITH1 D1
order by c1, c2 ) D1 

In a similar way, the right one is correct as well, with no unnecessary joins to date dimension.
 SAWITH0 AS (select sum(T42433.Units) as c1,
     T42412.Department as c2,
     '' as c3,
     T42412.Dept_Key as c4
from
     BISAMPLE.SAMP_OFFICES_D T42412 /* D30 Offices */ ,
     BISAMPLE.SAMP_REVENUE_F T42433 /* F10 Billed Rev */
where  ( T42412.Office_Key = T42433.Office_Key )
group by T42412.Department, T42412.Dept_Key)...

Next step, as administrator I will create an Analysis with the None column and select it's Column Properties.

In Column Format I'll select "Hide".

And at the bottom of that screen I will select Save As default / Save as system-wide default for "None"."None": 

the same with Zoom-In:

That's all. Now I can use the None column in any other analysis as Column Selector, described in the first post, or as Presentation variable, described in second and third.

For example the same analysis with Column selector, selecting once the None column and then some other column:

When None is in the column of Column selector:

When Per Name Year is in the column of Column selector:

Solution 2 (see update bellow)

Just as in solution 1, I add a column in the repository with empty value and default this column to be hidden, system wide. Now I can use the column anywhere I want. I'll name it None1. The process of creating this table is the same as in Solution 1, with the formula '':

 

With one critical difference, the hiding will be done at the repository (presentation level) and not UI, as solutions 1.

At the presentation level I will go to the None properties and set the "Hide object if "condition to 1=1. This way the column is always hidden.

Now when going to Analysis we don't see the column None1:

But it doesn't mean it's not there; You just have to know it's name.

For example an analysis with Revenue and the new None1 column:

 Results:

(Thank You Dov Ziskin, from VisionBI for reminding me the second solution). 

Now we are ready to do some real Dynamic Columns example, in the next post.

Late Update for solutions 1&2:

From OBI 11.1.1.9 this feature does not work by default (Thank You Yaniv Kisler for pointing me to it). 

See OBIEE 11g/12c-Hidden Table Using 'Hide object if' in RPD is Displayed in Analysis (Doc ID 2464635.1) at oracle Support site.

From version 11g9 you need to enable this feature by adding a tag to instanceconfig.xml

<WebConfig>

<ServerInstance>

<SubjectAreaMetadata>

<HideHiddenMetadataInViews>true</HideHiddenMetadataInViews>

</SubjectAreaMetadata>

</ServerInstance>

</WebConfig>

Solution 3

 This solution is similar to solution 2, but you can hide the None 1 column by preventing access to it in the permissions.

Just remember to change the parameter PROJECT_INACCESSIBLE_COLUMN_AS_NULL  in nqsconfig.ini to Yes, or you will get an Error.

P.S.

Never test the above with administration user like weblogic. Unless you want to spend hours testing it and questioning the idiot who wrote the above.
User weblogic can see everything, including the hidden columns.