Monday, January 27, 2014

OBIEE Prompts - 2 prompts on same column - Part 2

This is the second part of the post. In the first part, with the long name: OBIEE Prompts - 2 prompts on same column, protect Filter and more - Part1 I was talking about the difference between Column Prompt and Variable Prompt. In this part, we will start dealing with the 2 prompts on same column issue.

Why is it a problem? Because when we try to create a Dashboard Prompt and attempt to use the same column as Column Prompt, you will see the message "This column exists in prompts. This action will be ignored."
In most cases we need both columns for Presentation Variable.


In OBIEE 10g there were 2 main option to solve this issue:
A. The terrible option of creating another column in the Presentation Variable.
B. The less demanding creation of a column with same content but different function. For example:
  1. String: "Time"."Year" vs. "Time"."Year"||''
  2. Number: "Base Facts"."Revenue" vs. "Base Facts"."Revenue"+0
  3. Date: "Time"."Calendar Date" vs. TIMESTAMPADD(SQL_TSI_DAY, 0, "Time"."Calendar Date")
In 11g (as it's clear in the first Part 1 of this post) you can simply use Variable Prompts to create Presentation Variables.

For example:



EXAMPLE: Comparing two dates.

If all we want to do is to place 2 date values side by side,


we don't need all the complexity and can create a simple Column prompt with "is between" Operator (or let the  user select several dates):
 
 And simple "Is Prompted" filter:

Even when we need some complex calculations we can do it on Pivot Level.
For Example lets calculate change as Second Date minus First Date and % Change as 100*Change/First Date:

 All we need to do is to create "New Calculated Items" For Calendar Date:


One with Formula $2-$1, (that means second date value Minus the first)


And the other  100*($2-$1)/$1

We can see it's quite resilient to Null and division by Null problems.





But when we require some extra complexity, we need to have 2 Presentation Variables on Dates:
Since we are dealing with Dates here, I want to select Dates from calendar, but I don't see a Calendar option in User Input:
There is a little voodoo here: At first glance we might suspect there is no such an option. Even after I select User Input as "Choice List" and select "All Column Values" so I'm able to select the relevant Column ("Time"."Calendar Date") the User Input option doesn't have a "Calendar" as an option

Only after I press OK and edit the row again,the Calendar option appears:


Now we have 2 date Presentation Variables: P_First_D and P_Second_D

Lets use them in an analysis.

I created an Analysis with analysis Filter where DATE=first_Prompt or DATE=second_Prompt.

There is no date Column in the analysis. Instead I created two columns based on Revenue with Filter Function:
FILTER("Base Facts"."Revenue" USING ("Time"."Calendar Date" IN (date '@{P_First_D}{2010-01-16}')))
and
FILTER("Base Facts"."Revenue" USING ("Time"."Calendar Date" IN (date '@{P_Second_D}{2010-01-17}')))
Now I can subtract, divide or create any calculation I prefer, based on the existing calculation.
Please note, the performance of the first option described above (using calculated Items) is much better. 



In next part, i will do an example about 2 Prompts with multiple values in Character Variable. 




But before the end, a tip about Prompt Based Filter in Formula creation:

When we create a Filter in Formula

We get a very nice wizard that enables us to create a condition that includes Variables:

 While it seems OK:
After we press OK, the Variable disappears and must be returned manually:

What we actually get in this case is:
 FILTER("Base Facts"."Revenue" USING ("Time"."Calendar Date" IN (date '2010-01-16')))

While I expected:

FILTER("Base Facts"."Revenue" USING ("Time"."Calendar Date" IN (date '@{P_First_D}{2010-01-16}')))

You need to fix it manually.
Please note the nonstandard format of the date. My original date format was 01/16/2010. The filter wizard changed it. Trust it.







Just a little followup. I've seen here someone asking for validating of dates, so the second Presentation Variable in prompts is later than the first.

I'll create an analysis (condtion1), that returns values only if it's true:

column: date '@{P_First_D}{2010-01-16}'
Filter (after convert to SQL): date '@{P_First_D}{2010-01-16}' <= date '@{P_Second_D}{2010-01-16}'

Now all is left to do is put a condition on the relevant section in the dashboard with
requirement that row count of analysis condition1 is greater than 0.

Last part is to create a section with condition that requires the row count on analysis condition1 = 0. In this section I'll place a warning message.

Now when the prompt is empty or correct I see the regular dashboard, when the dates are wrong I see only the message. 


Tuesday, January 21, 2014

OBIEE Prompts - 2 prompts on same column, protect Filter and more - Part1

This post is about using OBIEE 11g Prompts and was created on OBIEE 11.1.1.7. The main intention was to write about "creating 2 prompts on the same column". As background, it also covers how "Is Prompted" works, why do we need the "protect filter" option, as a basics.
At the end there will be few other "prompt related" issues.
I split the post to Part 1, the relevant background and Part 2, creating 2 prompts on same column.  

I didn't intend to write about creating 2 prompts on same column, since I was sure it was very well covered by others. Few days ago a business partner (Rotem, enjoy your honeymoon at Galapagos) told me, all she could find were tips guiding her to create 2 copies of the relevant column in RPD and then base the Presentation Variable on them... So I will cover few more options, but first lets ask ourselves why do we need few prompts on the same column?
Few use cases such as:
  1. Selection values that should be compared (Customer name in prompt1 compared to other customer or customers in prompt2).
  2. From Date, To Date prompts.

Lets start with the basic. What happens when we create a Dashboard Prompt?

We can create:
  • Column Prompt
  • Variable Prompt
  • Image Prompt
 What is the difference between Column and Variable Prompt?  
 Column Prompt is based on a Real Column or a Formula, while Variable Prompt has no actual column behind and is aimed to create presentation or request variables only. You can create those variables with Column Prompt as well.
Variable Prompt enables us to create the variables, in a simple a quick way and doesn't have all the extra definitions of Column Prompt.

Column Prompt, "Is Prompted" and "Protect Filter"
As I said, Column Prompt is based on a Real Column or a Formula. When we create a Column Prompt we are required to select a Column.

Later we can edit or change the formula:


Why is the formula important? Because when the formula in the prompt and formula in analysis are the same you can use "Is Prompted" in Analysis filters:


Why do I insist on talking about "formula" and not "column"? Because we can edit the formula both in analysis and in prompts. So, when the formula in both places are the same "the magic" works.

EXAMPLE1: Lets create the above example (Prompt on the column "Time"."Per Name Year") and 2 sample Analysis with a single column and filter "Is Prompted".
In the first Analysis the column would be "Time"."Per Name Year" and in the second "Time"."Per Name Year"||'' (the Year string concatenated with empty string. Exactly the same result, but different formula).


As expected the prompt influences only the first analysis where the formulas are the same:



EXAMPLE2: Lets do the same but change the Column Prompt Formula to "Time"."Per Name Year"||'' (the one from Year2 analysis):

As expected only the Year2 Analysis is influenced by the prompt:


EXAMPLE3: Now to see why we need "Protect Filter". We will set the prompt back to "Time"."Per Name Year" and create a third analysis with a column "Time"."Per Name Year" and a filter "Time"."Per Name Year"='2007'.

With the Filter:

The result is: !!!

The filter was for 2007, but we see the prompt overpowered it and the analysis return 2008!
Why? OBIEE is somewhat enthusiastic about prompts. Whenever the prompt and filter have the same formula, the prompt is in control. You want to prevent it? Select any of the check boxes in the prompt definition:

The logical default is "Protect Filter", but "Convert this filter to SQL" will do the trick as well:

Last part of this OBIEE Prompts 101 part will be, "List Values".
Prompts List Values
By default, in Column Prompt, the default List Values are all the column values, while Variable Prompt has no default selection and you must define it, if you want the user to have values to select from (unless it's plain text).
What are the options?
Both For Column Prompt:
And Variable Prompt:
We can select:
  • None (for Calendar and Text user input)
  • Specific Values (for Calendar and Text user input)
  • All Column Values
  • Specific Column Values
  • Custom Values (Variable prompts only)
  • SQL Results
  • Members of Group
  • All Column Values and Specific Groups (Column Prompt Only)

Lets talk about few of them:
All Column Values: The default for Column Prompt. Can be used in Variable Prompt. Then you have to select a specific column. You might ask, "Isn't it what a Column Prompt does?"

Two differences: 
  • "Is Prompted" doesn't work on such Variable Prompts. 
  • You can create as many Variables you want on the same column.
  

We will not talk about Groups related option here and most of other are self explanatory, but lets see a very simple (and almost full proof) way to create an SQL Results.
EXAMPLE4: I want the prompt to suggest only Years before 2008. I'll create an Analysis where I select the relevant data:
 In Advanced Tab we can see the issued SQL. You will see a cleaner version if you press "New Analysis":

Copy the content:

And paste it as the SQL Result:



How to create 2 Prompts on Same Column from the above is trivial, but we will see few less trivial examples.




Friday, January 17, 2014

OBIEE - Installing OBIEE 11.1.1.7.140114 patch set on Windows

Please note OBIEE Bundle Patch 11.1.1.7.151020 is available. You might prefer it.

The process s very similar to the 11.1.1.7.171013 patchset installation.

As described in OBIEE BUNDLE PATCH 11.1.1.7.140114 AKA 11.1.1.7.4 (?) is available, first download the single big patch file of   Patch 17886497 and the small file of  Patch 16569379 (you need 16569379 only if you didn't install it with 11.1.1.7.1 / 11.1.1.7.131017, I’ll refer to it in the description, but you can disregard it if you already installed it).

Don't know why, but I had trouble downloading the WIN64 patch with Firefox, while Chrome did the job well.

Unzip the patches. You should have 2 directories named by the patch number.
Now in the directory 17886497, you have 8 zip files and a useful readme.htm. You are advised by the readme to move the 17886497 directory to  prod_mwhome\Oracle_BI1. Unzip all 8 zip files. At the moment you should have 8 directories under prod_mwhome\Oracle_BI1\17530796.

PLEASE read carefully section 3.1 if you are upgrading from 11.1.1.6.X. Especially 11.1.1.6.12.

In section 3.2 we are advised to check conflicts. I promise to do it later, after I set the path and Oracle_Home.

Next we are advised to do is to back up the Oracle BI system, including the following:
The <Oracle BI Home>\bifoundation\server directory.   
The <Oracle Instance>\bifoundation\OracleBIServerComponent\coreapplication_obis1\repository directory.
The <Oracle BI Home>\bifoundation\jee\mapviewer.ear\web.war\WEB_INF\conf\mapViewerConfig.xml file, if you have modified it.
There is a tip: The location of the Oracle BI Home directory and the Oracle Instance directory depend on where Oracle BI was originally installed, and on the platform. For example:
        On UNIX, the Oracle BI Home directory might be prod_mwhome/Oracle_BI1
        On Windows, the Oracle BI Home directory might be
c:\prod_mwhome\Oracle_BI1
In my case it's D:\or\MWHOME\Oracle_BI1\bifoundation\server and D:\or\MWHOME\instances\instance1\bifoundation\OracleBIServerComponent\coreapplication_obis1\repository.
I prefer to backup D:\or\MWHOME\instances\instance1\bifoundation\OracleBIPresentationServicesComponent\coreapplication_obips1\catalog as well.
Please note: If you changed configuration files or added pictures not in AnalyticsRes folder (as described here, back the changes as well. They will be erased by the patch).

In point 3 you are advised to stop the BI System and told how to do it, step by step. It's all very nice but to me it looks simpler just to run "Stop BI Services" from the Start/All Programs/Oracle Business Intelligence menu. I guess we see the full details due to Linux needs (and anyway, it's very educational).

Point 4 advises to clear cache: from prod_mwhome\Oracle_BI1\bifoundation\web\catalogmanager\configuration\ (in my case D:\or\MWHOME\Oracle_BI1\bifoundation\web\catalogmanager\configuration\) you should delete 2 files (if they exist) org.eclipse.osgi  &  org.eclipse.equinox.app.

As you remember we have directory
17886497 with 8 sub-directories in  prod_mwhome\Oracle_BI1 (in my case D:\or\MWHOME\Oracle_BI1).
make sure the extracted directory 16569379 is under prod_mwhome\Oracle_BI1 as well, only if you didn't install this patch before (you did, if you installed 11.1.1.7.1 / 11.1.1.7.131017).

Open CMD prompt as Administrator on  prod_mwhome\Oracle_BI1. (Right Click with Shift pressed on the CMD or Right Click and CMD here as Administrator in the File Browser or check the start menu).

The readme states:

On UNIX: If you are using a command shell derived from the C shell, then set the environment variables by entering the following:
setenv ORACLE_HOME $PWD
setenv PATH $ORACLE_HOME/bin:$PATH
setenv JAVA_HOME $ORACLE_HOME/jdk
setenv PATH $JAVA_HOME/bin:$PATH
setenv PATH $ORACLE_HOME/OPatch:$PATH
 
On Windows: If the Oracle BI Home directory is C:\prod_mwhome\Oracle_BI1, then set the environment variables by entering the following:
set ORACLE_HOME=C:\prod_mwhome\Oracle_BI1
set PATH=%ORACLE_HOME%\bin;%PATH%
set JAVA_HOME=%ORACLE_HOME%\jdk
set PATH=%JAVA_HOME%\bin;%PATH%
set PATH=%ORACLE_HOME%\OPatch;%PATH%
 (in my case the first line is set ORACLE_HOME=D:\or\MWHOME\Oracle_BI1)
DON'T leave empty spaces in the set, such as set ORACLE_HOME=   D:\or\MWHOME\Oracle_BI1. 
Right Click does the paste action in CMD window.

 Now if you remember we need to conflict detection from section 3.2.
Assuming we are in directory Oracle_BI1, run:

cd 17886497

cd 16913445
opatch prereq CheckConflictAgainstOHWithDetail -ph ./
cd..\17922352
opatch prereq CheckConflictAgainstOHWithDetail -ph ./
cd..\17300417
opatch prereq CheckConflictAgainstOHWithDetail -ph ./
cd..\17922552
opatch prereq CheckConflictAgainstOHWithDetail -ph ./
cd..\17922596
opatch prereq CheckConflictAgainstOHWithDetail -ph ./
cd..\17300045
opatch prereq CheckConflictAgainstOHWithDetail -ph ./
cd..\16997936
opatch prereq CheckConflictAgainstOHWithDetail -ph ./
cd..\17922577
opatch prereq CheckConflictAgainstOHWithDetail -ph ./

In all cases you should see:
Invoking prereq "checkconflictagainstohwithdetail"
Prereq "checkConflictAgainstOHWithDetail" passed.
OPatch succeeded.
 In point 10 we have 2 options: Run Once all 8 patches or do each manually.
Since I don't enjoy tedious manual labor, I preferred the first option.

We will return to the basic prod_mwhome\Oracle_BI1\17886497 directory and run opatch.

Enter the following commands:
 
cd ..
opatch napply –skip_duplicate

 

My system is 11.1.1.7.131017 so I was asked twice "Do you want proceed?" 4 of the patches are identical to 11.1.1.7.131017 and are skipped on my machine.  

And was asked to be sure "the local system is ready for patching" (shut down).

My installation finished with warning:


From what I see in the log, it's all because of previous patches (11.1.1.7.131017  and 11.1.1.7.1) components that are the same in this bundle.


In case you don't trust opatch or simply enjoy unnecessary work you can run each patch separately with opatch apply. PLEASE note, if you installed 11.1.1.7.131017, there are only 4 new patches out of 8 you see. You need only them. Wait for the  "Opatch Succeeded" and repeat in the next directory. (Repeat the Conflict Resolution list, just with opatch apply, instead of  opatch prereq CheckConflictAgainstOHWithDetail -ph ./)
As I said twice before, only if you didn't install 16569379 before,  go to directory 16569379 and Run opatch apply (in both types of installation).


You are advised to check all patches were applied using opatch lsinventory.

The next part has some manual labor (I use the readme numbers starting with 14):

14. Copy the BIPublisherDesktop32.exe and BIPublisherDesktop64.exe files from the prod_mwhome\Oracle_BI1\clients\bipublisher\repository\Tools directory to the prod_mwhome\user_projects\domains\bifoundation_domain\config\bipublisher\repository\Tools directory.
In my case, from D:\or\MWHOME\Oracle_BI1\clients\bipublisher\repository\Tools to D:\or\MWHOME\user_projects\domains\bifoundation_domain\config\bipublisher\repository\Tools.
(Should I copy ExcelAnalyzer.exe as well?)

15. Copy the msmdacc64.dll file from the <Oracle BI Home>\clients\bipublisher\repository\Admin\DataSource directory to the prod_mwhome\user_projects\domains\bifoundation_domain\config\bipublisher\repository\Admin\DataSource directory.
There seems to be a problem here since the source does not exist. I don't know if it's a documentation bug or missing file. Had the same thing in 11.1.1.7.1 and 11.1.1.7.131017.

16.  Remove the jsp_servlet subdirectory associated with Oracle BI Publisher (if it exists), as follows:

  A.  Go to the directory containing the jsp_servlet subdirectory (using a file manager application or the command line). If it exists, then the jsp_servlet subdirectory is located in one of the directories below the <Middleware Home>/<Domain Home>/servers/bi_server1/tmp/_WL_user/bipublisher_11.1.1 directory.

    For example:
        On UNIX: If the Middleware Home directory is /prod_mwhome and the Domain Home directory is /user_projects/domains/bifoundation_domain, then go to the following directory:
        /prod_mwhome/user_projects/domains/bifoundation_domainservers/bi_server1/tmp/_WL_user/bipublisher_11.1.1
        On Windows: If the Middleware Home directory is C:\prod_mwhome and the Domain Home directory is \user_projects\domains\bifoundation_domain, then go to the following directory:
        C:\prod_mwhome\user_projects\domains\bifoundation_domain\servers\bi_server1\tmp\_WL_user\bipublisher_11.1.1

B.  Look in each directory below <Middleware home>/<Domain home>/servers/bi_server1/tmp/_WL_user/bipublisher_11.1.1, and remove any jsp_servlet subdirectory that you find.

  17. If you backed up mapViewerConfig.xml because it had been modified, then copy the backed-up file to its original location at <Oracle BI Home>\bifoundation\jee\mapviewer.ear\web.war\WEB_INF\conf\.

Now you should be able to start the server, but before you do, there is one more part not in the readme file.

 You might remember the post of Abhinav Agarwal  Smart View Version to use with OBIEE 11.1.1.7.1.
You don’t have to repeat it if you have done it with 11.1.1.7.1 or 11.1.1.7.131017 patches. Backup  <Oracle BI Home>\clients\epm\SmartView (In my caseD:\or\MWHOME\Oracle_BI1\clients\epm\SmartView),  extract the content of the Zip file to that directory. 

You might want to delete the patch installation files from <Oracle BI Home> and free the 2.5GB of disk space.

Now you can start up the server. Before you do you might want to have a look at section 5.2 bellow (it requires you to stop the serer again). Bon Voyage.


Section 5.1: Copying BICharts.swf to Custom Directories

The Oracle white paper "Customizing Oracle Business Intelligence Enterprise Edition 11g" describes how to customize Oracle BI Presentation Services by storing custom skins, styles, and messages in a custom directory.

If the Oracle BI system has been customized as described in the white paper and custom skins are stored in one or more custom directories, then follow these additional instructions to complete the Suite Bundle Patch installation:

1.Copy the BIChart.swf file from the <Oracle BI Home>\bifoundation\web\appv2\res\sk_blafp\dvt directory to each custom directory in the BI System, replacing the existing file of that name.

    For example, on Windows, you might copy the BIChart.swf into the following custom directory:

    c:\prod_mwhome\instances\instance1\bifoundation\OracleBIPresentationServicesComponent\coreapplication_obips1\analyticsRes\sk_mylookandfeel\dvt

 2. Stop and Start the Oracle BI Presentation Services system component using EM or by running:
opmnctl stopproc ias-component=coreapplication_obips1
opmnctl startproc ias-component=coreapplication_obips1

3. Clear the browser cache.




Section 5.2: Editing Settings in OracleBIJavahostComponent

If you installed (and followed instructions) 11.1.1.7.131017, you already have done this part.

For customers exporting data into Microsoft Excel: To improve the functionality of exporting data from analyses, dashboards, and other Oracle BI Presentation Catalog objects into Microsoft Excel, edit settings in OracleBIJavahostComponent by performing the following steps to complete the Suite Bundle Patch installation:

    1. Stop the server, (including Weblogic)

    2. Navigate to the /instances/instance1/config/OracleBIJavaHostComponent folder.

    3. Perform the following actions:

       a. In the config.xml file, configure the XMLP tag for large data as follows:

        <XMLP> <InputStreamLimitInKB>0</InputStreamLimitInKB>

        <ReadRequestBeforeProcessing>false</ReadRequestBeforeProcessing></XMLP>

        b . In the xdo.cfg file, change the setting for xlsx-keep-values-in-same-column to True.(See note
1632002.1 at Oracle Support if you don't know what they are talking about).

    4. Start the WebLogic Administration Server (and Managed Server and BI System).

Also see document "OBIEE 11g: How To Improve the Functionality of Exporting Data from Analyses, Dashboards, and other Oracle BI Presentation Catalog Objects into Microsoft Excel with OBIEE 11.1.1.7.131017 and Later (Doc ID 1595671.1)" at Oracle Support Site, about changing the value value="-server -Xmx2048M from value="-server -Xmx1024M in the file config.xml of opmn.

For Oracle Business Intelligence Client Tools only (not the Windows server): Having applied the Oracle BI EE Suite Bundle Patch 11.1.1.7.140114, you must also update each desktop installation of the Oracle Business Intelligence Client Tools (Oracle BI Administration Tool, Job Manager, and Catalog Manager) as follows:

Deinstall the Oracle Business Intelligence Client Tools from every computer where the client tools have been installed by following the instructions in Chapter 5 of Oracle Fusion Middleware Installation Guide for Oracle Business Intelligence.

Reinstall the Oracle Business Intelligence Client Tools on every computer where the client tools have been deinstalled by following the instructions in Chapter 5 of Oracle Fusion Middleware Installation Guide for Oracle Business Intelligence.

You didn’t do it? You might get OBIEE - NQSError 46036 in BI administration after patching BI Server