Friday, October 30, 2015

OBIEE 12c - File locations and meanings

The file locations in OBIEE12c are very different from previous versions. I'll update this page when I find something I consider interesting.

First few naming conventions:

I installed OBIEE and Weblogic under d:\MWHOME12.

ORACLE_HOME would be the installation Directory. (In my case d:\MWHOME12\).

BI_DOMAIN, sometimes called DOMAIN_HOME is ORACLE_HOME/user_projects/domains/bi (in my case it is  D:\MWHOME12\user_projects\domains\bi).

 SDD (Singelton Data Directory) is DOMAIN_HOME/bidata (for one host installation). (In my case it is   D:\MWHOME12\user_projects\domains\bi\bidata).

The catalog is located by default at  SDD/service_instances/ssi/metadata/content/catalog
(In my case it is D:\MWHOME12\user_projects\domains\bi\bidata\service_instances\ssi\metadata\content\catalog).

The Repository is (sort of) in SDD/service_instances\ssi\metadata\datamodel\customizations (In my case it is D:\MWHOME12\user_projects\domains\bi\bidata\service_instances\ssi\metadata\datamodel\customizations).

The entire handling of Repository and Catalog is different in 12c and is handled via BAR files. More about it later.

Configuration files are located under BI_DOMAIN/config/fmwconfig/biconfig
(In my case D:\MWHOME12\user_projects\domains\bi\config\fmwconfig\biconfig)
Each in it's folder. For example:
  • instaceconfig.xml is in BI_DOMAIN/config/fmwconfig/biconfig/OBIPS  
  • NQSConfig.ini          in BI_DOMAIN/config/fmwconfig/biconfig/OBIS

LOGS: Are in BI_DOMAIN/servers, each in his folder. For example sawlog.log is under BI_DOMAIN/servers/obips1/logs. All the logs are located in the same place, including Adminserver logs.

Not only the logs are here. You will found tmp, cache and other folders as well.

Most executable scripts are located in BI_DOMAIN/bitools/bin (D:\MWHOME12\user_projects\domains\bi\bitools\bin, in my case).

The start and stop scripts are here and also the script that collects diagnostic for Oracle Support (diagnostic_dump.cmd  for Windows and for Linux).

WebLogic Scripting tool (wlst.cmd / is located in Oracle_Home/oracle_common/common/bin (D:\MWHOME12\oracle_common\common\bin in my case.)

Found this at Oracle support: OBIEE 12c: Quick Reference For Architecture, Lifecycle Management and File Locations (Doc ID 2067527.1)

Thursday, October 29, 2015

OBIEE 12c - Copy - Paste data from Analysis

We had a short discussion about the option to Copy / Paste of data from analysis to anywhere else.
In OBIEE 12c this option exists. There is a difference in it's behavior.

When working with Tables/Pivots in Fixed headers with scrolling content we are limited by OBIEE.

 I could only mark cells from one column and never mark Axis values in Pivot tables.

But the better abilities exist when switching to Content paging.

Now we can mark the Pivot/Table from outside the frame an select multiple values from multiple columns:

This works even in Pivots (bigger picture):


Monday, October 26, 2015

OBIEE - Installing OBIEE 12c on my Windows box

I wanted to install the new OBIEE 12.2.1 on my Windows 7 box. Since I want to test the process of migration of OBIEE 11 to 12, I installed it side by side with my existing installation.

If you want to see Linux installation description, you can see Andrew's post here. My installation seems to be smoother then his, despite the fact that Windows 7 doesn't seem to be supported according to the certification matrix (12.2.1 one).

The installation is of 4 steps:
  1. JDK installation (if needed)
  2. Weblogic Server installation
  3. BI Installation 
  4. Configuration

I downloaded JDK 8, Weblogic server and 2 BI files from here. After unzipping them I had the following:
I installed the JDK. It's better not to accept the default of installing in "Program Files\Java" folder because of the space in the name. I installed it in c:\Java.

Next I opened a Command Prompt as Administrator. Moved to the above folder (where the files are located) and run the Weblogic installation:
c:\Java\jdk1.8.0_65\bin\java.exe -jar fmw_12.
Accepted the defaults and pointed it to a new MWHOME I created (D:\MWHOME12, in my case).

Next I run the BI installation in the same folder:
And selected the same MWHOME for BI installation (D:\MWHOME12, in my case). 
Here are the next screens:

Now I can run the configuration part.
I used the same Command Prompt Window, and run config.cmd from MWHOME\bi\bin

I left the default, including Essbase (note the RTD installation is gone).

passed the prerequests:

Didn't change the location, but selected the domain user and password:

Allowed the wizard to created the RCU schema (note the options are: Oracle DB, Oracle RAC DB and MS SQL server). there is no standalone RCU anymore.

and updated the DB connection details (note, no need to add "as SYSDBA" for Oracle sys user)  :

To be on the safe side, you might prefer to run the RCU separately. The RCU utility is located, in my case at MWHOME\oracle_common\bin.
The new version of RCU allows us to create a script to be used later by DBA, as well.

After you finish the with the RCU, the screen is a bit different:


Next left the defaults:

The options here are new and interesting:

 Saved the response file (PLEASE do it at some point, it's not fun to look for the ports and URLs later).

Now the process is running.

While the configuration passed well, the BI Startup at the bottom stayed for few very long minutes on 0%. Then jumped to 100% and finished. Made me check the log few times, during the period.

Now is the last screen. If you didn't save the URLs before. Do it now:

The OBIEE didn't start on it's own.
It is working:

I don't know if this should be so or it's because I have a previous entry of "Oracle Business Intelligence" in the "All Programs" menu, but nothing was added there.

To stop and start the BI server I can run start.cmd and stop.cmd from MWHOME\user_projects\domains\bi\bitools\bin\ (D:\MWHOME12\user_projects\domains\bi\bitools\bin\start.cmd in my case).

After the installation my default ports are 9502 for BI and 9504 for EM.
For Administration Tool installation, the port 9516 is used in the ODBC settings, in my case.

One first discovery I had after the installation. Since OBIEE 12 is pure HTML, we can copy/paste data from analysis, in all browsers I tested:

One more similar post: Leela Madhav preferred to set up RCU separately.
And another by  Srinivas Malyala: OBIEE12c installation on windows7 machine

To the best of my knowledge the RCU should be installed on UTF8 type DB. Deliver BI try to work around it in OBIEE 12 RCU Avoid Hardstop Pre Reqs for AL32UTF8 Database Char Set.

If you are installing RCU on Multi-Tenant Oracle DB, please read before installation:

If your installation was successful but OBIEE failed to start please read OBIEE 12c: Oracle Business Intelligence has stopped working by Guillaume Slee ( and his great advise of reducing the PATH to eliminate possible contradictions.

 After the installation, the natural step is to upgrade your existing OBIEE 11 to 12. It should be done from latest patches or and described in the post  Migrating from Oracle BI 11g to 12c by Shahed Munir.
And at here by @KeitaFK.
The Oracle migration guide is here.

Note the following from Oracle Support: OBIEE 12c Pre-Upgrade Alert: Before Upgrading To 12c, Ensure your 11g Catalog Is Upgraded To the Correct Version (Doc ID 2068965.1)

Saturday, October 24, 2015

OBIEE 12c (12.2.1) is available

Oracle Business Intelligence 12c is available for download here

It is available for:
  • Windows X86 -64bit
  • Linux X86-64bit
  • Oracle Solaris on SPARC 64-bit
  • Oracle Solaris on x86 64-bit
  • IBM AIX 64-bit
  • HP UX Itanium
System Requirements and Supported Platforms for Oracle Fusion Middleware 12c (12.2.1) is here.

The documentation is here .

What is new in 12c?

 Quite a lot.
First of all,everything regarding data visualization is new. See: User's Guide for Oracle Data Visualization 

I gathered here new features chapters from various 12c Books:

New features in Oracle BI EE 12c (12.2.1) include:
Enhancements to Graphs
In this release graphs have been enhanced as follows:
  • Ability to sort graph views by using a context menu (right-click). A new sort dialog is displayed when you right-click in a graph view in the "Analysis editor: Results tab" or on a dashboard page. You can right-click legend, data marker, and group-level (X axis) items to display a menu of interactions that includes options such as Sort and Drill (see "Right-Click Interactions in Views" for additional information).
Enhancements to Views
This release includes enhancements to various view types that can assist a content designer in creating more effective analyses faster, including those in the following list:
  • A new view type named heat matrix. Heat matrices are two-dimensional representations of data in which values are characterized by a gradient of colors. A simple heat matrix view provides an immediate visual summary of information that is well suited for analyzing large amounts of data and identifying outliers. See "Editing Heat Matrix Views" for additional information.
  • Ability to sort graph, heat matrix, pivot table, table, treemap, and trellis views by using a context menu (right-click). A new sort dialog is displayed when you right-click in a data view in the "Analysis editor: Results tab" or on a dashboard page (see "Right-Click Interactions in Views" for additional information).
Enhancements to Analyses
This release provides, for analyses, the ability to:
Enhancement to Scorecards
In this release, you can style and customize KPI Watchlists and every watchlist that appears in a scorecard. You can set custom styles for each watchlist and rename and change the order and visibility of the watchlist columns. See "Customizing Watchlists."
Enhancements to Dashboards
In this release, you can set advanced page properties to specify incoming navigation parameters you want to set on the current dashboard page. This enables you to control how parameters associated with the navigation actions are applied. See "Setting Advanced Page Properties."

New metadata repository features in Oracle BI EE 12c Release (12.2.1) include:
Logical Level Sequence Numbers for Time Dimensions
The Sequence Numbers tab has been added to the Logical Level dialog. The new tab allows you to add absolute or relative sequence numbers to time dimensions. These mappings provides direct column references in the Time dimension table, which creates a query that is easier for Oracle BI Server to execute against the data source. See "Adding Sequence Numbers to a Time Dimension's Logical Level" for more information.
DISPLAY | SORTKEY Syntax Supported in the SQL ORDER BY Expression
The Oracle BI Server now accepts the DISPLAY and SORTKEY keywords in the SQL ORDER BY expression. You can use the DISPLAY keyword to override a logical column's assigned sort order column. For more information, see "ORDER BY Clause Syntax."
Oracle Database Fast Application Notification and Fast Connection Failover Supported by Oracle BI Server
The Oracle BI Server supports the Fast Application Notification (FAN) event and Fast Connection Failover (FCF) Oracle Database configuration. Fast Connection Failover enables quick failover when the data source's Oracle database is not available. See "Oracle Database Fast Application Notification and Fast Connection Failover" for more information.
Generate Fragmented Aggregates in Aggregate Persistence
The aggregate persistence functionality has been enhanced to generate fragmented aggregates from a manually written aggregate specification. You can generate fragmented aggregates by adding a Where clause to the Logical SQL query's Create statement. See "Writing the Create Aggregates Specification" for more information.
New Command Line Utilities
Several command line utilities have been added. See the following topics for more information:

New system administration features and changes in Oracle BI EE 12c (12.2.1) include:
Invoking WLST From a Single Location
In previous releases, you invoked WLST from different locations, depending on whether you were using the commands for Oracle WebLogic Server, system components, or Java components such as Oracle SOA Suite. In this release, you invoke WLST from:
(UNIX) ORACLE_HOME/oracle_common/common/bin/
(Windows) ORACLE_HOME\oracle_common\common\bin\wlst.cmd
Oracle Home Location Redefined and No Middleware Home
Redefining of the Oracle home and elimination of the Middleware home. See "New And Deprecated Terminology for 12c" in Oracle Fusion Middleware Concepts.
OPMN is No Longer Used in Fusion Middleware
OPMN is no longer used in Oracle Fusion Middleware. Instead, system components are managed by the WebLogic Management Framework, which includes WLST, Node Manager and pack and unpack. See "What Is the WebLogic Management Framework" in Oracle Fusion Middleware Concepts.
Oracle Web Cache Not Part of Fusion Middleware
Oracle Web Cache is no longer part of Oracle Fusion Middleware.
Moving From Test To Production is Carried Out in a Different Way
The test to production operation is still possible however, the process is different from what was available in Oracle Business Intelligence Release 1 (11.1.1) as it applies solely to metadata (content, data model and authorization). For information, see Chapter 22, "Moving Oracle Business Intelligence Between Environments".
New Commands For Process Control
New process control commands replace the old start stop commands. For information, see Section 1.5.3, "Process Control Commands".
Managing Metadata In Business Intelligence Archive Files
All Oracle Business Intelligence metadata, including repository, Presentation Services catalog, and user authentication is stored in BAR archive files. The BAR file is a mechanism for managing or moving a self contained set of Oracle BI metadata between environments. For information, see Chapter 9, "Managing Metadata and Working with Service Instances".
Single Enterprise Install
In this release the Oracle Universal installer offers a single install type for your Enterprise which provides an Administration server, and a Managed server. For information, see Section 1.3, "What Is the Oracle Business Intelligence System Logical Architecture?" and Oracle Fusion Middleware Installation Guide for Oracle Business Intelligence.
Changes to Scaling Out
In this release the scale out procedures for Oracle Business Intelligence have changed. For information, see Chapter 3, "Scaling Your Deployment".
Simplified Configuration
Configuration files are no longer duplicated. Separate configuration files still exist for example, for Oracle BI Presentation Services and BI Server, but they are not duplicated in the case of a cluster. For information, see Section 8.3, "Configuring Oracle Business Intelligence System Settings".
Managing System Component Instances Using Commands
OBIS (BI Server) system component instances are separately managed in BI 12.2.1 using service instance commands. For information, see Section 9.2, "Managing Service Instances".
Collecting Diagnostic Bundles
A new script enables you to collect the diagnostic bundles needed by Oracle Support or Development to help resolve issues. For information, see Section 6.2, "Collecting Diagnostic Bundles".
Synchronizing Mid-Tier Database Connection Details Command
A new command enables you to synchronize mid-tier database connection details when they have changed. For information, see "Synchronize Mid-Tier Database Connection Details Command".

New security features in Oracle BI EE 12c (12.2.1) include:
BISystemUser and BISystem Removed
To simplify administration and configuration in this release Oracle Business Intelligence no longer requires a real user called BISystemUser (or equivalent) for internal communication. The system user concept is now deemed "virtual" and is represented by the credential, for which the values are securely randomly generated by the Configuration Assistant. Oracle BI components continue to use this credential for internal communication, backed by Oracle BI Security. The application role BISystem is also no longer present in the Policy Store, and will be removed from any upgraded 11g environment.
User GUIDs Removed
In this release user GUIDs have been removed to make administration easier. GUIDs are replaced with user names. There is no longer any need to refresh GUIDs as part of lifecycle operations. Your administrator is now responsible for ensuring that users leaving the system are cleaned up from Oracle Business Intelligence.
Database Security Store
In this release the Security Store (Policy and Credential Stores) is configured in a relational database rather than in a file. The database is the same as used by RCU. This change makes scaling easier, and makes clusters more reliable.
Easier SSL Configuration
In this release configuring SSL end to end is now less complex and uses offline commands.
The key differences in SSL support in this release (from 11g) are as follows:
  • SSL uses the WebLogic trust store
    No additional BI-specific trust configuration is required.
  • Offline commands
    There is no need to use Fusion Middleware Control UI to configure processes.
  • Diagnostics for WebLogic certificate issues
  • Higher security - TLSv1.2 only
  • Configuration is central and not intermingled with user configuration.
  • Supports advanced options with no risk of settings being overwritten.
Migrating Catalog Groups to Application Roles
In this release a new process enables you to migrate Catalog groups to application roles.

This section describes new features for Oracle BI EE 12c (12.2.1). It contains the following topics:

New Service

Oracle Business Intelligence 12c (12.2.1) includes the following new service:


New features and changes for Oracle BI Publisher 12c Release 1 (12.2.1) include:

Generate Explain Plan from SQL Data Set

For data sets generated by SQL queries issued against the Oracle Database, you can now generate an Explain Plan to provide valuable information about the efficiency of your query. For more information, see Section 9.8.1, "Generate Explain Plan."

Best Practices Information

Poorly constructed data models can result in out-of-memory exceptions. Use these best practices guidelines to help you tune your data models for more efficient memory usage. See Chapter 9, "Performance Best Practices."

In Release 12c (12.2.1) the look and feel of the user interface is refreshed. You will notice new icons and some changes to the presentation of features on the Home page.

Naturally we can expect, the previously described new features are included in 12c.
(here, here and here)

There are new tutorials:

Thursday, October 22, 2015

OBIEE BUNDLE PATCH AKA (?) is available

OBIEE new general bundle patch (number 11?), is available in Patch 21814325, it can be installed on any previous release.

The Patch is platform specific and is available for:
  • Linux x86
  • Linux x86-64
  • MS Windows (64-bit)
You can read the readme file here.

Oracle Business Intelligence Installer (BIINST) -same as previous patchset
Oracle Business Intelligence Publisher (BIP) -same as previous patchset
Enterprise Performance Management Components Installed from BI Installer (BIFNDNEPM) -same as previous patchset
Oracle Business Intelligence Server (BISERVER)
Oracle Business Intelligence Presentation Services (BIPS)
Oracle Real-Time Decisions (RTD) -same as previous patchset
Oracle Business Intelligence ADF Components (BIADFCOMPS) -same as previous patchset
Oracle Business Intelligence Platform Client Installers and MapViewer
Oracle Business Intelligence Third Party - same as previous patchset

As usual there is a bonus patch: Patch 16569379  (1111770)- Dynamic Monitoring Service patch. It is platform dependent. If you installed it with or later, that's enough. It's the same.

Bugs fixes 

Oracle Business Intelligence Server (BISERVER)

 Fix For Bug  20747758


OBIEE patch numbers do it again.
After vs.;

It is vs.

Thursday, October 15, 2015

OBIEE - Few options to compare data to previous month, Year or something...

I was asked to help with comparing data to previous period, month or year. There was another problem: No time dimension. I will describe here few options to do similar things.
(This post took me long time to finish. It started with and was finished with but should work with both)

Option 1: the correct option, use AGO function

This is the short and default answer. Create a date dimension. Make sure you have chronological key for at least date and month levels (date only might surprise you when comparing shorter and longer month) and use AGO function as described in the post: OBIEE and Time Series Calculations in analysis (Ago‎, Period Rolling‎, To Date‎).
For example:  AGO("Base Facts"."Revenue", 1)

The following options will be without time dimension. All done on analysis based on Sample Sales, with Product_Type, Revenue and some period (Year or Month or both).


Option 2: Calculating previous month using MSUM (relevant for non chronological as well)

The idea behind this option is the calculation: 
Sum(Last 2 Periods) = Previous Period + Current Period. As a result:

Previous Period = Sum(Last 2 periods) - Current Period

How do I calculate Sum(Last 2 Periods)? Using moving Sum. In our case: MSUM(measure,2)

Example 1: I have only Month and Revenue.
To calculate previous Month I add a column with formula MSUS(Revenue,2)-Revenue
Next I can do any calculation I want with the 2 columns.

Example 2: having Product Type, Month and Revenue 
When adding dimensions, things get a little complicated. I'll add "Product Type" column:
We have a problem with the first period. Instead of being zero or Null, it's the last value of the previous Product Type!

Since I'm lazy, I'll just add a case statement, for the first period of each product type, the value is NULL, else the previous calculation (just remember, first periods might be different for various Product Types).
First Period calculation: Min(Month by Product)
The case should be: CASE WHEN (Month=Min(Month by Product) then  NULL ELSE (MSUS(Revenue,2)-Revenue) END

Next I can do any calculation I want with the 2 columns.

By the way, some basic math using msum(Revenue,2):

Prev. period: msum(Revenue,2) - Revenue
diff between 2 periods: 2*Revenue - msum(Revenue,2) (the calculation: Revenue - prev. period)
% of change between periods: 100*(2- msum(Revenue,2)/Revenue) (the calculation: 100*diff/Revenue)

Option 3: Calculating same month of Previous Year

In many cases the data has strong seasonality, so it's logical not to compare and make calculations for the 2 month, but rather the same month of previous year.

To make things easier I used constant Years calculation. You can create a general analysis by replacing it with Presentation Variables or based on Current Year calculation:

Previous Year: YEAR(CURRENT_DATE) - 1

For this option to work I need the Month value to be the same for every year. In Sample Sales the month format is YYYY/MM, so I used the function RIGHT(Month, 2). Please note: in Sample Sales, both Year and Month columns are characters.

I filtered the data to be only Years 2009 and 2010.
Since I don't have a Year column. The initial data is sum of both years.

I'll add a column to calculate year 2010 Revenue only:  FILTER(Revenue USING (Year = '2010')) and one for Year 2009 FILTER(Revenue USING (Year = '2009'))

In case you have a presentation variable, named, for example, P_YEAR1 with the year 2010 selected, you should have the following formula: FILTER(Revenue USING (Year = '@{P_YEAR1}{2010}'))

Now we can remove the Revenue column and do any calculation we want with the 2 columns. (In the example bellow, I'm showing only fist 4 month of year and calculate the difference between the two years):

If you want a nicer column headers, but don't want them to be static... In case you use the analysis in a dashboard, you can use presentation variables as column names, such as: @{P_Year1}{2010} and use the same Variables as the analysis filter.

Option 4: Creating specific calculations using the $number calculated item (relevant for non chronological as well).

Assuming we want to compare specific values, lets say we want to compare 2 first month, 2 last month and last vs. first...
We can create a new calculated item and use $number to point to specific row / columns:
A useful and less known fact $-number would count from the end. 

 For example if we have 12 month in a pivot:

We can add calculated item that returns the difference between first 2 month: $2-$1
the last 2 month (if  I know they are 12 month)

Or if I don't know how many month they are ($-1 - $-2):

last month minus first month would be: $12-$1 (or $-1 - $1)...

Option 5: Using Filter to select specific periods (relevant for non chronological as well). Similar to option 3.

We can use filter function to return specific values.
For example we only want 2 first month of 2009 and the total revenue of 2009. I'll select the filter function:

Filter it by month:

select the condition:

and create the function:
the function is:
FILTER("Base Facts"."Revenue" USING ("Time"."Per Name Month" = '2009 / 02'))

We can replace the specific value (2009/02) with prompt.
Any calculation is now possible between the functions.

This will work if the aggregation level of the data is higher than the filter level. In our case a year:

Not if you add the month to analysis:

Option 6: Using Evaluate with the (Oracle) DB LAG function.

We can use database functions such as Lag and run them from Evaluate function in OBIEE. I leave it to you, if you are interested.