Sunday, January 22, 2017

OBIEE 11.1.1.9.170117 bundle patch is available

OBIEE 11.1.1.9 bundle patch is available:
Patch 25189841: BI BUNDLE PATCH 11.1.1.9.170117 is available from Oracle.
It is available for:
  • Linux x86
  • Linux x86-64
  • Microsoft Windows 64bit
  • HP-UX Itanium
  • IBM AIX on POWER system (64-bit)
  • Oracle Solaris on SPARC (64-bit)
  • Oracle Solaris on x86-64 (64-bit)

The readme is here.
The Oracle BI EE Suite Bundle Patch 11.1.1.9.170117 under the top-level patch 25189841 consists of the following component patches:
Patch Abstract
25054309 Oracle BI Mobile App Designer (BIMAD)
25214874 Oracle Business Intelligence Server (BISERVER)
25217499 Oracle Business Intelligence Presentation Services (BIPS)
24533980 Oracle Enterprise Performance Management Components Installed from Oracle BI Installer 11.1.1.9.0 (BIFNDNEPM)
25214935 Oracle Business Intelligence Publisher (BIP)
25266214 Oracle Business Intelligence Platform Client Installers and MapViewer
24346370 Oracle Business Intelligence ADF Components (BIADFCOMPS)
21517672 Oracle Business Intelligence Third Party  (same as last one)



The patch includes the following bug fixes:

Oracle Business Intelligence Presentation Services (BIPS)


23569560     QA: ERROR - DUPLICATE ID: 'NULL' FOUND ON DASHBOARD `CLOSED ISSUE ANALYSIS¿
23528459     ACCESS REL12: JAWS CAN'T READ VALUES IN UNLABELED TABLES IN TAX AUTHORITY REPORT
23502030     VPAT - DUPLICATE ID ERROR ON MANY DASHBOARDS WITH OGHAG STRUCTURE RUN
21192948     QA:ACC:ERROR:DUPLICATE ID AT 'PERMISSIONS' AND 'ABOUT' DIALOG WITH OGHAG>STRUCTU
23643225     REPORT PREFIX CAUSES FAILURE WHEN SINGLE QUOTE IS PRESENT INSIDE OF VARIABLE
22384242     NEED A WORK-AROUND/SOLUTION FOR DISABLING FAULTY JOBS IN AN AUTOMATED FASHION
19568323     CONDITION FORMATTING OPERATOR ¿IS NOT NULL¿ ON A GRAPH.
21265568     SUPPORT ICAP BASED VIRUS SCANNING IN SAWSERVER
20258650     THE REPORT LINKS WITH MULTIPLE PARAMETERS ARE NOT WORKING.
23254924     USERS ARE INTERMITTENTLY LOGGED OUT
21674920     OBIEE TABLE SCROLLING SNAPS BACK TO FIRST ROW SECTION CONDITION IS USED
21347308     PRINTING TO PDF OR HTML DISPLAYS SESSION VARIABLE, NOT ITS CONTENT
24623887     Fix for Bug 24623887
22382923     CASCADED DASHBOARD PROMPT DOES NOT WORK ON IE11
21979698     NEW DMS METRICS REGARDING BI USAGE
24362101     Fix for Bug 24362101
21259784     NEED TO ADD "CONTAINS HTML MARKUP" IN NO RESULT VIEW MESSAGE EDITOR
25203978     EXPORT MENU ITEM NOT WORKING IN EPM WORKSPACE
21178536     Fix for Bug 21178536
25070137     TRYING TO REMOVE GROUP-FATAL:AN XML INSTANCE WAS INVALID AND UPGRADE NOT ALLOWED
23569936     ER: REL13: RESYNCREPOSITORY FAILS "ORACLE INSTANCE LOCATION MUST BE PROVIDED"
24973040     CHANGE IN FILTERS FOR HIERARCHIAL COLUMNS BETWEEN 12C AND 11G ENVIRONMENTS
23148164     SERVICE/API FOR EXPORTING BULK DATA FROM ANSWERS
21215489     ACC: ERROR:" MULTIPLE LABELS FOUND: UNDEFINED" IS THROWN WITH OGHAG>FORM
19608547     QA:ERROR OR MISSING LABEL SHOULD'NT OCCUR FOR WATER FALL REPORT WITHIN DB PAGE.

Oracle Business Intelligence Server (BISERVER)


24836865     RPD PROVISIONING FAILED WITH BISERVERXMLEXEC ERROR 25
24703052     REL11 : CSAEXTENDER  WITH NEW UTILITY THROWS FALSE SEVERE MESSAGES
24428269     REL11BP160711 UNABLE TO SET PREFERRED_CURRENCY VARIABLE
24343818     BISERVERPROVISIONBIAPPSRPD TO SKIP EXTRA CONNECTION POOLS VARIABLE PROJS
24343815     REL11BP160711 BISERVERPROVISIONBIAPPSRPD TO ACCEPT NAME AND PARENTNAME
21630121     BICS VA PROJECTS SHOWS NQS ERRORS AFTER UPGRADE TO 15.2.6
24831219     R12PREFLIGHT: INACCESSIBLE COLUMN: "AUXILIARY CLASSIFICATION 1". "BASE LEVEL AUXI
24570785     MANY CORE* FILES ARE CREATED UNDER INSTANCES FOLDER AFFECTED OBIEE IN REL10
24966137    TRACKING BUG TO PULL OUT REGRESSION CAUSED BY BUG 22903243
24651853    NOT STRESS:FA: FIN:QA: CRASH IN CATALOGCRAWLER ON 11.1.1.9.160912
24715052    TRACKING BUG FOR SERVER CHANGES REQUIRED FOR BUG 22384242
24413997     FAILED TO INVOKE EXTRACTCUSTOMSUBJECTAREAS_CPP FROM BI PATCHING ORCHESTRATION
25248099     NEED TO CHECK THE RETURN CODE OF BUILDROLEHIERARCHY
23579474     CALL TO NQSMODIFYMETADATA BRINGS DOWN BI SERVER
25229711     REL12:BP170117:EXTRACTPROJECT FAILS TO EXTRACT WITH -U OPTION
25063469     BISERVERRNRPATCHER NEEDS OPTION TO VALIDATE ONLY MODIFIED SUBSET
25209112     QA: BISERVERAPPLYVERTICALRULES COMMAND FAILING IF PASSWORD IS PROMPTED
24504161     BIEXTENDER -F OPTION SHOULD HANDLE THE OBIEEBROKER FAILURE
25182868     QA:BISERVERRNRPATCHER EXITING IF THERE IS NO SA WITH PREFIX CUSTEXT
25167669     REL11 RPD RIP AND REPLACE : ERROR , EXCEPTION  MESSAGES IF NO VERTICAL CONTENT
25167629     REL11 : BI HEALTH CHECK FAILING AT VALIDATERPD
25142292     RNR PATCHER: CORE DUMP IF ANY UTILITY MISSPELT (LINUX)
24622664     FIX COMPLEX JOINS (SWITCHING POSITIONS)
24312833     FEW DFF VO'S NOT IMPORTED CAUSING THEM TO NOT BE MAPPED
25065583     BI JDBC IS NOT USING OPTIMIZED RECORD FORMAT
24794615     VALIDATERPD DOESN'T WORK WHEN PLUGGED IN BISERVERRNRPATCHER
24387222     ER TO HAVE BI EXTENSION RUN IN FULL ALL THE TIME
20781025     BI EXTENDER ENHANCEMENTS AFTER THE ATG EQUALIZATION PROCESS ENHANCEMENT
25041330     RNR PATCHER: CORE DUMP IF ANY UTILITY MISSPELT (WINDOWS)
24973824     VALIDATION TAKING TOO MUCH TIME IN MAIN
24823241     RNR PATCHER : EXITS AFTER STARTING EXTRACTVCUSTOMIZATION
24823166     RNR PATCHER : CORE DUMP IF INPUT XML IS NOT FOUND IN PLACE
24808824     EXTRACTCUSTOMIZATION INTRODUCES DIFF DURING SINGLE UTILITY RUN
24760194     UPDATE EXTRACTCUSTOMIZATION UTILITY AS A CONSEQUENCE TO BUG 24740744 FIX
24757097     VALIDATERPD CONSEQUENCE TO BUG 24740744 FIX
24748849     ADD AUTOMATED TEST CASE FOR SINGLE PATCHING UTILITY FRAMEWORK
24740744     SINGLE PATCHING UTILITY SHOULD PROVIDE OPTION TO DISABLE RPD CHECK
24677401    SINGLE PATCHING UTILITY FRAMEWORK AND PROVISIONING UTILITY INTEGRATION CHANGES
24664928     MODIFY VALIDATERPD UTILITY FOR RNR SINGLE PATCHING UTILITY
24664909     MODIFY BISERVERAPPLYVERTICALRULES UTILITY FOR RNR SINGLE PATCHING UTILITY
24664888     MODIFY BISERVEREXTENDER UTILITY FOR RNR SINGLE PATCHING UTILITY
24664862     MODIFY CSA UTILITY FOR RNR SINGLE PATCHING UTILITY
24664827     MODIFY BISERVERPROVISIONING UTILITY FOR RNR SINGLE PATCHING UTILITY
24657032     RNR:IMPROVEMENT ON OBJ CREATION IN-MEMORY WITHOUT UDML IN BIEXTENDER AND VERTICA 
21942801     OTBI-E V3:PSR:FA REL9.2:LOT OF SPACE CONSUMED BY EXTRACTOR QUERY.
24792199     REL11: VALIDATERPD SCRIPT FAILS ON REL11 SAAS POD RPDS

Oracle Business Intelligence Publisher (BIP)


24554203     FIX FOR USING <DEFINE LEVEL> WITHOUT <GROUPING CRITERIA>
24662344     "USE OF XPATH EXPRESSION IN SORT AND GROUPING CRITERIA SUMMARY:     REQUIRED"
19524875     CAN ENTER ANY ALPHA NUMERIC VALUE FOR MEMORY GUARD RUNTIME PROPERTIES
23030301     Fix for Bug 23030301
23038891     Fix for Bug 23038891
23180883     NLS: FRENCH: BURSTING: UNABLE TO VIEW SPLIT-BY.DELIVER-BY VALUES
22252794     PARAMETER PASSING DEFAULT VALUE RATHER THAN NULL
23322845     OPTIMIZE_BURST FLAG SHOULD HAVE DEFAULT VALUE SET TO TRUE
22898301     STRESS: AUTENTICATION ERROR.INVALID SESSION ID OR SESSION EXPIRED
23172894     OUTPUT FORMAT DROPDOWN LIST OPTIONS IS NOT DISPLAYING CORRECTLY ON UI
23152011     FUSION- BIP ORA-01841: (FULL) YEAR MUST BE BETWEEN -4713 AND +9999, AND NOT BE 0
23271391     ENABLE XSL TEMPLATE REPORTS CANCELLABLE FROM SERVER SIDE
24749837     QA: 12C: 23508707: MEMGRD CMD LINE TOOL GENERATES UNNECESSARY FILES UNDER /TMP
22938413     ADD BOM FOR ETEXT OUTPUT WHEN OUTPUT ENCODING IS UTF-8
23319129     BIP CLIENT API ENHANCEMENT TO SUPPORT HIGH VOLUME HCM PAYROLL PROCESSING
21135840     BI PUBLISHER CANNOT DELIVERY ON WEBDAV USING DIGEST AUTHENTICATION
25170389     "IGNORE USER TIMEZONE" CHECKBOX DOES NOT STICK AFTER NOV-2016
23124602     PRINT LOG MESSAGE FOR IGNORED EXCEPTION
25065019     FIX RETRY OF ESS JOBS IN CASE OF NON-SYSTEM ERROR
22865151     BIP ETEXT OUTPUT LENGTH MODE CANNOT CONTROL DOUBLE BYTE CHARACTER
22729509     NOTSTRESS: SAW SERVER-INVALID SESSION ID OR SESSION EXPIRED ERROR
21098317     RE-INDEXING OF METADATA TAKES TOO LONG AND BLOCKS OTHER REPORT EXECUTIONS
23016235     Fix for Bug 23016235
22554427     NEW PDF FORM FIELD FOR DOCUMENT-REPEAT-ELEMENTNAME
23233792     FUSION APPLICATIONS - BI PUBLISHER - FTP -  ORACLE.XDO.DELIVERY.SSH2.SSHEXCEPTIO

Oracle BI Mobile App Designer (BIMAD)


24675627     Fix for Bug 24675627
22601855     MOBILE APP COMPOSER REPORT - NO DATA FOUND ISSUE
24762294     Fix for Bug 24762294
24605653     Fix for Bug 24605653

Wednesday, December 28, 2016

Playing with Alcohol and Oracle DV Desktop 2 for some basic data science

I will cover here few nice option to investigate and prepare data with the Oracle Data Visualization Desktop 2.
In the spirit of New Year, lets talk about alcohol.

For some time I had a file of Alcohol consumption for each country in my Big data Virtual Machine. I believe the origin is from Wikipedia. From HDFS It can be accessed by Hive / Impala / Spark... I decided to use Impala.
There are few data sets I downloaded from the World Health Organization GISAH section as Excel and csv files.
The Alcohol prices file has data about 2012 / 2015 prices in part of the countries, I don't remember where did I get it from 😱.

So I have:
  • Country information (XLXS)
  • Alcohol Dependency in countries (XLXS)
  • AVG Alcohol prices in USD (XLXS)
  • Percent of death by alcohol by country (CSV)
  • Alcohol Consumption by Country (Impala)

The first problem is country names. While countries like Israel or Greece have simple names, they are many ways to write the name of Iran (The Islamic Republic of) or even USA or GB. My Implala data might not fit the formal naming in the data from World Health Organization. How can I check?

First I need the files in the Data Visualization Desktop.
Importing the csv and Excel files is mostly trivial. Create a new data source, that is based on a File. After importing I can also define what columns are measures or Attributes, the Data type and, if measure, the Aggregation:


what do I do with Impala?
I defined a new connection:
And  created a new data source based on that connection:

I could import a table and select columns,


but I preferred to write my own Select statement, and on the way control the data format:
select country, cast(total as double) as total_cons,cast(__beer as double) as beer_perc, cast(__other as double) as other_perc,cast( __spirits as double) as spirits_perc, cast(__wine as double) as wine_perc from default.alcohol_consumption 

You might notice I added "where rand()<1", just as a reminder to myself. When working with real big data we might like something like "where rand()<0.1" so we don't bring too large data set to the desktop tool. (Actually you want to do something a bit trickier since Impala rand function has a static nature).


Now I have my data sources in the Data Visualization Desktop and I can create my first data flow that compares country name:
I selected the Country data source
In the data flow I want only the Country name column. So I right Click on Country data source in the flow, Add a step:

And pick the Select Columns:


The system Automatically adds all the columns, so i select "Remove All" pick the "Country" column and press "Add selected":

In "Preview" I can see the data:


Now I want to bring the Impala source and do the same:
Another Right Click on the last step (Select Columns), 'add step' and selecting 'Add Data', does that:
 
I have 2 data flows I want to join. The trick is select one and press Ctrl+Right Click on the other. Now we have the join option:




Our case is simple (country = Country), in other cases we can select the columns and pres the Plus mark on the right to add one more pair.

To check the country names that don't fit we want a full outer join. For non technical people it's "All rows" vs. "Matching rows":



 Now I can add the filter (country is Null or the other country is Null):


Last part of the data flow is to save the data:

 I didn't bother saving the data flow. What I did next is to export the thing to excel and create if-then-else statement. Something like this:

CASE Country
WHEN 'Bolivia (Plurinational State of)' THEN 'Bolivia'
WHEN 'Brunei Darussalam' THEN 'Brunei'
WHEN 'Democratic People''s Republic of Korea' THEN 'South Korea'
WHEN 'Iran (Islamic Republic of)' THEN 'Iran'
WHEN 'Micronesia (Federated States of)' THEN 'Federated States of Micronesia'
WHEN 'Lao People''s Democratic Republic' THEN 'Laos'
WHEN 'Syrian Arab Republic' THEN 'Syria'
WHEN 'United Kingdom of Great Britain and Northern Ireland' THEN 'United Kingdom'
WHEN 'The former Yugoslav republic of Macedonia' THEN 'Macedonia'
WHEN 'Venezuela (Bolivarian Republic of)' THEN 'Venezuela'
WHEN 'Congo' THEN 'Republic of the Congo'
WHEN 'Republic of Korea' THEN 'North Korea'
ELSE Country
END


I used it in the next part, where I combined all the data sources.
I could create a project and do the joins there, but I preferred to have a data flow that creates a reusable single data source of all the data.

The only new things I did in the second data flow, was to create a column (based on the above case statement) and renamed few columns.

 


Lets talk about the last steps of this data flow:


After the join and selecting only the relevant columns, we have to define the Aggregate function of the measures. We often use sum. In our case, there is no point in aggregating alcohol consumption in countries so I mostly use Avg:


Now I have a new data source, named United Alcohol. Lets have fun.
 


Can you guess what region consumes the largest amount of alcohol? Europe of course, when Eastern Europe is on your side you will always win this "game". Not surprisingly, the Eastern Mediterranean countries (that are mostly Muslims) consume very little alcohol. (The number are amount of pure alcohol per person a year).   

If you wanted to know what is the most popular alcoholic type of drink around the world, we have the answer:
 


Looking at maps of Total Alcohol Consumption, 2 types of Alcohol prices, % of Alcohol dependence and Alcohol attributable fractions, all causes deaths (%) shows me few interesting things:
1. The situation in Eastern Europe is scary.
2. Norway alcohol prices are very high, but it didn't help them with Alcohol dependence and slightly with it's consumption comparing to other Scandinavian countries.

So lets see whats going on in Europe.

Here is a chart of Total Alcohol Consumption vs. Alcohol dependency in European Countries with Polynomial Trend Line.


When I check for Outliers,  I'm not really surprised:

Belarus, Russia and Hungary on one side and Tajikistan, Turkey and Azerbaijan on the other. Other Outlier model include Israel as well (the regions are not geographical). 

Last thing I will do with Europe, is to set the size of the bubble to show the percent of deaths attributable to Alcohol. The strange thing I see, is that the high percent of deaths attributable to Alcohol is not directly connected to consumption. It seems that the geography is the main factor. If you are from ex-USSR republic, you have high chance to die with something related to alcohol. Belarus is leading with 34.7%! Hungary that is the same cluster of Alcoholism and Consumption, reports 6.7%, by far less then Ukraine, Latvia, Lithuania, Moldova, Estonia...


Lets see it on the map:

It's not directly related to Consumption of Alcohol:


The part that really surprised me was when I split the percent of deaths attributable to Alcohol by gender. In countries where the percentage  is highest more Woman than Men death is  attributable to Alcohol!

  


I could continue on and on, but lets stop here. Wish you a happy new year and "С новым годом" if you prefer Russian. Lets finish with this Merry Christmas video using Oracle Data Visualization:https://youtu.be/NTtIsifOJoE



Friday, November 11, 2016

OBIEE - Selecting date range by selecting a column

Customers often work with specific date ranges: "last 3 days", "last week", "this month", "last 7 working days"... While it's easy to create a filter that does this and reuse it, they prefer an easier method.
Adding those option in the tool by default is problematic, since users always invent new range needs. We need something the local IT can manage without too much work for each new idea.
 
I covered how to do it with prompts in the post: "OBIEE Prompts - Last Year, Last Month and Custom Dates", but the request is to give easy access to date range selection in Answers (and VA) as well.
Sorry, it's going to be a long post.

My basic assumption is: you have a date dimension based on dates table. I will cover the following options:
  • Reusing a filter.
  • Adding date range columns to date table.
  • Create a date_range table and joining it to date dimension.   
  • Having multiple data sources on the same physical table with various "where" conditions in the content as a date dimension and specific column.

In all examples I will use 3 ranges "last 3 days", "last month", "this year". Assuming today is 11-Nov-2016:
last 3 days is 10-Nov-2016, 9-Nov-2016, 8-Nov-2016
last month is all dates from 11-Oct-2016 till 10-Nov-2016
this year are all the dates from 1-Jan-2016 till 11-Nov-2016. 

I'll describe the pros and cons of each method with the following criteria:
  • Ease of use for customer.
  • Requires RPD change for each additional date range.
  • Requires daily ETL.
  • Supports easy reverse selection and combinations (such as: last month and not last 3 days).
  • Works with VA as well or Answers only.
  • Has performance impact.
I use very simple model with measure value 1 in each day of fact.

Reusing Filter

I can create a filter from within an analysis or from menu New / Filter.

 For last 3 days I create a filter "DATE_D"."DATEVALUE" BETWEEN TIMESTAMPADD(SQL_TSI_DAY, -3,CURRENT_DATE) AND TIMESTAMPADD(SQL_TSI_DAY, -1,CURRENT_DATE)
I can save the filter for reuse

 or save the entire analysis.
To use the filter, the user has to select it from the filters list of the Catalog (left panel):





Some users find it easier to use the analysis itself as filter, they believe they have better control this way:
 In a similar manner I can create the other date range filter / Analysis:
Last Month: "DATE_D"."DATEVALUE" BETWEEN TIMESTAMPADD(SQL_TSI_MONTH, -1,CURRENT_DATE) AND TIMESTAMPADD(SQL_TSI_DAY, -1,CURRENT_DATE)
 This Year: "DATE_D"."DATEVALUE" BETWEEN TIMESTAMPADD(SQL_TSI_DAY, (1-DAYOFYEAR(CURRENT_DATE)) ,CURRENT_DATE) AND CURRENT_DATE
Only the analysis based filter let us create reverse selection:






Summary:

The best option for IT and OK but not so great for Users.
  • Ease of use for User is OK but not great.
  • Requires RPD change for each additional date range?  NO.
  • Requires daily ETL? NO.
  • Supports easy reverse selection and combinations (such as: last month and not last 3 days)? Yes, with filter based on analysis only.
  • Works with VA? NO.
  • Has performance impact? NO.



Adding date range columns to date table

This option requires change in the dates table. I'll add 3 columns (last_3_days, last_month, this_year). For each column I have to update daily the Boolean value (in my case Yes/No).


This is the Subject area:


The user has very simple task; Filtering Yes/No on the column:





The reverse option is easy to create:

It works with VA as well:


Summary:

The worst option for IT and great for Users.
  • Ease of use for User? YES.
  • Requires RPD change for each additional date range?  YES.
  • Requires daily ETL? YES.
  • Supports easy reverse selection and combinations (such as: last month and not last 3 days)? YES.
  • Works with VA? YES.
  • Has performance impact? NO.





Create a date_range table with line for each range and joining it to date dimension

The simple option here is to create a table with 3 columns: Range_name, From_date, Till_date and join them to the Date dimension. This sort of join has bad impact on performance. The table also requires ETL for updating the date_range table. We can have a work around for the ETL by creating a dynamic view (Oracle SQL code):
create or REPLACE view date_range_line as
select 'Last 3 days' as Range, trunc(sysdate-3) as from_date, trunc(sysdate-1) as till_date from dual
Union ALL
select 'Last month' as Range, trunc(add_months(sysdate,-1)) as from_date, trunc(sysdate-1) as till_date from dual
Union ALL
select 'This Year' as Range, TRUNC(TO_DATE(sysdate),'YEAR') as from_date, trunc(sysdate) as till_date from dual
This is easy to update for each change or new requirement, in the future. 
So I created it as a View, imported into repository and joined it to date table. 

"ORCL".""."SH"."DATE_RANGE_LINE"."FROM_DATE" <=  "ORCL".""."SH"."DATE_D"."DATEVALUE"   AND   "ORCL".""."SH"."DATE_RANGE_LINE"."TILL_DATE" >= "ORCL".""."SH"."DATE_D"."DATEVALUE"




Now it's working:
Unfortunately, the reverse option returns wrong results (the last 3 days were not removed):


(Actually it can be done using minus option that is under the union option in the criteria, but that's a bit advanced).

For performance we see the select statement from the log:
WITH 
SAWITH0 AS (select sum(T909.MES) as c1
from 
     DATE_RANGE_LINE T1138,
     DATE_D T895,
     TEST_FACT T909
where  ( T895.DATEKEY = T909.DATEKEY and T1138.RANGE = 'Last 3 days' 
and T895.DATEVALUE between T1138.FROM_DATE and T1138.TILL_DATE ) )
select D1.c1 as c1, D1.c2 as c2 from ( select distinct 0 as c1,
     D1.c1 as c2
from 
     SAWITH0 D1 ) D1 where rownum <= 65001
As expected we have 3 tables joined instead of 2.

Summary:

Good option for everyone, if you don't mind the performance problem and the wrong results of reverse or multiple range selection.
  • Ease of use for User? YES.
  • Requires RPD change for each additional date range?  NO.
  • Requires daily ETL? NO (for the view option).
  • Supports easy reverse selection and combinations (such as: last month and not last 3 days)? NO.
  • Works with VA? YES.
  • Has performance impact? YES.But can be solved with driving table as described bellow, in the update.

For this option I also considered creating a date_range table with a line for each date and range combination and joining it to date dimension as driving table. 
This is ETL intensive option where for example for last 3 days I have 3 rows of data, the range column is "last 3 days" and the date column has each of the 3 days that fit the range. For last month, up to 31 rows... We can see that dates such as '10-Nov-2016' appear both in 'last 3 days' and 'last Month' :

This way the join with date table is simple.



UPDATE 

To solve the performance problem we can use a feature named "driving table" in OBIEE. The idea of driving table described here and here. This way OBIEE runs a separate query for the date_range table and places the result into the date table filter. Since the date ranges are usually small (if you have a year, then you should change MAX_PARAMETERS_PER_DRIVE_JOIN parameter). Unfortunately since driving table makes it own optimization, it didn't act in my tests. 
OBIEE prefers to use driving table only with "cross-database joins ".
If you can't beat them, join them.To overcame the optimizer I duplicated my physical data source (now it's cross-database join):

And used the date_range table from the second source (ORCL#1). Set this date_range as driving table in the join at the business model:
Now the performance is fine!

This is from the log:

select T1287.RANGE as c1,
     T1287.DATES as c2
from 
     DATE_RANGE T1287
where  ( T1287.RANGE = 'last 3 days' ) 
order by c2

]]

select  /*+ no_merge */  sum(T909.MES) as c1,
     T909.DATEKEY as c2,
     T895.DATEVALUE as c3
from 
     TEST_FACT T909,
     DATE_D T895
where  ( T895.DATEKEY = T909.DATEKEY and (T895.DATEVALUE in 
(:PARAM1, :PARAM2, :PARAM3, :PARAM4, :PARAM5, :PARAM6, :PARAM7, :PARAM8, :PARAM9, 
:PARAM10, :PARAM11, :PARAM12, :PARAM13, :PARAM14, :PARAM15, :PARAM16, :PARAM17, 
...
:PARAM100)) ) 
group by T895.DATEVALUE, T909.DATEKEY 
group by T895.DATEVALUE, T909.DATEKEY
order by c3




Having multiple data sources on the same physical table with various "where" conditions in the content as a date dimension and specific column

This is a nice option for performance and demands no ETL.
For each range I duplicate the date data source and add a column that is specific for this data source. Selecting that column or filtering on it, enables the filter.

The example:

In the Business Model, in the date dimension table I duplicate the data source (it is still the same physical date_d table):


Rename the duplicate data surce to "last 3 days" and use a where condition, very similar to the one in the first example of "Reusing a filter" in Content Tab:

 "ORCL".""."SH"."DATE_D"."DATEVALUE"  BETWEEN TIMESTAMPADD(SQL_TSI_DAY, -3,CURRENT_DATE) AND TIMESTAMPADD(SQL_TSI_DAY, -1,CURRENT_DATE)

Now I add a new Logical data column to the Date, rename it

 
Set the column source to be from physical mapping from the "last 3 days" data source. It is critical that no other data source will be mapped to this column.


I enter the data source and Map this column:

We don't need a Physical Table, I just write 'Last 3 Days' (or whatever I want) in the Expression. We do need to mark "Show unmapped columns".

The order of data sources is important. We want the default date to be without constraints and place that data source first (by default OBIEE starts with the first data source and goes top down). If you want extra caution, you can work with data source priority group as well.  
Oracle help: "To assign priority group numbers, rank your logical table sources in numeric order, with 0 being the highest-priority source. You can assign the same number to multiple sources. For example, you can have two logical table sources in priority group 0, two logical table sources in priority group 1, and so on. Often, only two priority groups are necessary (0 and 1)."

Here in a similar way I add "Last Month" data source add it's condition in content tab and add the "Last Month column" for that data source:


I map the column to physical source in "Last Month" data source only. In a similar way you can see "Last 3 days" has no mapping in the "Last Month" data source.

After I complete the set this is what I have:
 
When working with Analysis, not using any of the range column returns the usual data:

In our case all the dates:

Adding the "Last 3 Days" column, activates the filtering. Same would happen if it's added hidden or used as filter.

Attempt of reverse option wouldn't work.
In this case when I attempt to add the filter "Last 3 Days is not equal / is not in Last 3 Days" it returns empty set as expected (once I use the "last 3 days" data set, the "Last 3 Days" column value is always 'Last 3 Days').


Summary:

  • Ease of use for User? YES.
  • Requires RPD change for each additional date range?  YES, but it's rather easy.
  • Requires daily ETL? NO.
  • Supports easy reverse selection and combinations (such as: last month and not last 3 days)? NO.
  • Works with VA? YES.
  • Has performance impact? NO.



Final Summary:

The easiest option for IT is the first, "Reusing a filter". It's also the worst for the User.
The best for the user seems to be the "Adding date range columns to date table" option, it's also the worst for IT.
 You can consider the other options as well.