Aggregation Rules and Functions

The following list describes the aggregation rules that are available for measure columns in the “Layout pane” and for columns in the “Edit Column Formula dialog: Column Formula tab”. The list also includes functions that you can use when creating a calculated item.

Default — Applies the default aggregation rule as in the Oracle BI repository or by the original author of the analysis. Not available for calculated items.
Server Determined — Applies the aggregation rule that is determined by the Oracle BI Server (such as the rule that is defined in the Oracle BI repository). The aggregation is performed within Presentation Services for simple rules such as Sum, Min, and Max. Not available for measure columns in the Layout pane or for calculated items.
Sum — Calculates the sum obtained by adding up all values in the result set. Use this for items that have numeric values.
Min — Calculates the minimum value (lowest numeric value) of the rows in the result set. Use this for items that have numeric values.
Max — Calculates the maximum value (highest numeric value) of the rows in the result set. Use this for items that have numeric values.
Average — Calculates the average (mean) value of an item in the result set. Use this for items that have numeric values. Averages on tables and pivot tables are rounded to the nearest whole number.
First — In the result set, selects the first occurrence of the item for measures. For calculated items, selects the first member according to the display in the Selected list. Not available in the Edit Column Formula dialog.
Last — In the result set, selects the last occurrence of the item. For calculated items, selects the last member according to the display in the Selected list. Not available in the Edit Column Formula dialog.
Count — Calculates the number of rows in the result set that have a nonnull value for the item. The item is typically a column name, in which case the numbers of rows with nonnull values for that column are returned.
Count Distinct — Adds distinct processing to the Count function, which means that each distinct occurrence of the item is counted only once.
None — Applies no aggregation. Not available for calculated items.
Server Complex Aggregate — Applies the aggregation rule that is determined by the Oracle BI Server (such as the rule that is defined in the Oracle BI repository). The aggregation is performed by the Oracle BI Server, rather than within Presentation Services. Not available for calculated items.
Report-Based Total (when applicable) — If not selected, specifies that the Oracle BI Server should calculate the total based on the entire result set, before applying any filters to the measures. Not available in the Edit Column Formula dialog or for calculated items. Only available for attribute columns.

Examples are:

Level Based Measure and Report Totals in OBIEE 11G

A level-based measure is a column whose values are always calculated to a specific level of aggregation.

The calculation of this measure is independent of the query grain and used always the aggregation grain of the logical column.

Level-based measures allow:

  • to return data at multiple levels of aggregation (the query grain and the level-based column grain) with one single query
  • to create share measures (percentage), that are calculated by taking some measure and dividing it by a level-based measure. For example, you can divide salesperson revenue by regional revenue to calculate the share of the regional revenue each salesperson generates.

The new OBIEE function in 11G AGGREGATE AT can leverage the same functionality.

Create Level Based Measure

In this scenario column “Amount Sold by Region” is a level based measure. In the column “Amount Sold by Region”, amount sold is calculated at the region level. In the below figure even though the country name is changing, the region is Europe (constant) and hence Europe will have the same “Amount Sold by Region” for different country names.

To create a level based measure as the amount sold by region, you can:

  • make a copy of the Amount Sold logical column
  • and then
  • drag the logical column into its hierarchy level
  • or set the aggregation grain in the level tab of the logical column property.

Method 1 (with a lowest query grain)

With a lowest query grain, each query that requests these columns will return the amount aggregated to its associated levels (in our case by region).

To obtain the good total, you have to uncheck the aggregate option : Report-Based Total (when applicable).

Method 2 (with a higher query grain)

When a query includes a level-based measure column and the query grain is higher than the level of aggregation specific to the column, the query results return null. Note that in releases previous from 11g, results were returned for this situation, but they were not deterministic.

How Will Report-Based Totals Created in Previous Releases Be Upgraded?

In previous releases, you had the ability to create report-based totals in table views. Because report-based totals are handled slightly differently in this release, you might notice a difference in totals as follows:

  • If the previous table included all report-based totals, then all measure columns and attribute columns in the upgraded table will use the Default option with the Report-Based Total option.
  • If the previous table view included a mix of report-based totals and non-report-based totals, then all measure columns and attribute columns in the upgraded table will use the Default option with the Report-Based Total option.
  • You can work around the upgraded totals manually. If you want to use the same measure value as in the previous release, then duplicate the measure column in the table and use the Aggregation Rule menu to specify a non-report-based total.
  • If the previous table view included all non-report-based totals, then all measure columns and attribute columns in the upgraded table will continue to use non-report-based totals.

Query for Reference


SELECT Data1.c4 AS column1,
Data1.c2 AS column2,
SUM(Data1.c1) OVER (partition BY Data1.c4) AS column3,
Data1.c1 AS column4
FROM
(SELECT SUM(T245.AMOUNT_SOLD) AS column1,
T175.COUNTRY_NAME AS column2,
T175.COUNTRY_REGION AS column4
FROM
SH.COUNTRIES T175,
SH.CUSTOMERS T186,
SH.SALES T245
WHERE ( T175.COUNTRY_ID = T186.COUNTRY_ID AND T175.COUNTRY_REGION = 'Europe' AND T186.CUST_ID = T245.CUST_ID )
GROUP BY T175.COUNTRY_NAME, T175.COUNTRY_REGION
) Data1
ORDER BY column1, column2

Note:

By default, each level of a dimension hierarchy shows both:

  • dimension columns that are assigned to that level,
  • And level-based measures that have been fixed at that level. If you don’t want this behavior, you can check the “Hide Level Based Measures” options (Tools/Options in the Administration Tool).

The query results return null because the query grain is higher than the level of aggregation specific to the level-based measure column.

How to Start AdminTool in OBIEE 10G / OBIEE 11G

admintool.exe is the Administration tool to manage OBIEE server and have some capabilities to automate some repetitive tasks and principally his embedded tools.

Oracle Business Intelligence Administration Tool is available only for Windows operating systems.

The Administration Tool is a Windows application that allows the BI Server administrator to create and edit repositories.

Repositories can be edited with the administration tool in either online or offline mode.

The Administration Tool also includes a session manager and a cache manager which are available in online mode. The Cache Manager displays what queries have been cached and how frequently they have been accessed. You can also select and delete items in cache.

Start in OBIEE 10G

You can start it in two ways:

  • with the Windows Menu : Start > All Programs > Oracle Business Intelligence > Administration
  • by starting the executable file admintool.exe (only for the version 10g)

The admintool is located in this directory

BI_ORACLE_HOME/server/Bin

Start in OBIEE 11G

You can start it in two ways :

  • with the Windows Menu : Start > All Programs > Oracle Business Intelligence > Administration
  • launch the Administration Tool from the command line using bi-init.cmd.

Do NOT open the RPD by simply double clicking on the RPD file or typing Admintool.exe in a command line. The resulting Administration Tool window is not initialized to your Oracle instance, and errors will result later in your session. Ignoring this will lead to creating inconsistencies in the RPD file.

The admintool is located in this directory.

BI_ORACLE_HOME/bifoundation/server/bin (\MiddelwareHome\instances\instance1\bifoundation\server\bin

LOG

In 11g Client, log file is located at:

ORACLE_BI_HOME\oraclebi\orainst\diagnostics\logs\OracleBIServerComponent\coreapplication\Administrator_NQSAdmin

Total in Report OBIEE

If the Report-Based Total option is not selected, then the Oracle BI Server will calculate the total based on the entire result set, before applying any filters to the measure columns.

From OBIEE 11g: BI Design Best Practices, this option did not work in 10g and is fixed in 11g.

Table View

Pivot View

When “Report-based total” is checked, BI Answer issues “REPORT_…” functions when subtotals are requested such as REPORT_AGGREGATE.

The REPORT_… functions must use additive measures (Not a count distinct for instance).

Upgrade

In previous releases, you had the ability to create report-based totals in table views. Because report-based totals are handled slightly differently in this release, you might notice a difference in totals as follows:

  • If the previous table included all report-based totals, then all measure columns and attribute columns in the upgraded table will use the Default option with the Report-Based Total option.
  • If the previous table view included a mix of report-based totals and non-report-based totals, then all measure columns and attribute columns in the upgraded table will use the Default option with the Report-Based Total option. You can work around the upgraded totals manually. If you want to use the same measure value as in the previous release, then duplicate the measure column in the table and use the Aggregation Rule menu to specify a non-report-based total.
  • If the previous table view included all non-report-based totals, then all measure columns and attribute columns in the upgraded table will continue to use non-report-based totals.

Grain in OBIEE and Level of Aggregation

What is Grain in the context of OBIEE?

Grain describes the level of detail.

It can be also see as:

  • The unique key of a table of a SQL Statement
  • Of the unique identifier of a hierarchy level in a dimension.

At the top level, there are two main options in choosing the level of granularity:

  • Summarized/Atomic (transaction level granularity): this is the highest level of granularity where each fact table row corresponds to a single transaction or line item
  • Summarized: transactions may be summarized by a subset of dimensions or dimensional attributes. In this case, each row in the fact table corresponds to multiple transactions

The most granular or atomic data (atomic as an indivisible unit of work) has the most dimensionality. Atomic data is highly dimensional. Preferably, you should develop dimensional models for the most atomic information captured by a Event (Timed Measure). Atomic data is the most detailed information collected: such data cannot be subdivided further.

Example: If a high grain is the month whereas a low or detail grain can be the day

A data warehouse almost always demands data to the lowest possible grain of each dimension not because queries want to see individual low level rows but because queries need to cut through the details in very precise ways.

The lower the level of granularity (or conversely, the higher the level of summarization), the less storage space required and the faster queries will be executed.

Types of Grain

The following list describes the different grains in navigating a query:

  • Query grain. The grain of the request.
  • Aggregation grain. The grain of the aggregate source of from a level based metrics
  • Time Series grain. The grain at which the time series function aggregation is requested.
  • Storage grain. The query in the example can be computed from daily sales or from monthly sales, or from quarterly sales.

Query Grain

The grain of the request (logical sql) is the level of summarization. The grain is determined through the baseline columns. In the query below, the query grain is Quarter.

For Example:

SELECT quarter, myMetrics FROM MySubjectArea

In 11g, the hierarchical column determines the grain of the report.

Aggregation

The aggregation grain is defined in the content tab of the logical table source.

When the query grain matches the aggregation grain of a logical table source, this one is used to create the physical query.

You can also define aggregation grain calculation on a measure. This kind of calculation creates level-based measure.

Data Mismatch Issue After Up gradation from OBIEE 10G to OBIEE 11G

How to Fix Data Mismatch Issue After Up gradation from OBIEE 10G to OBIEE 11G

Hi, there is no clear cut method (I found yet) to fix this issue. The tips and method I mention in this post are tried, tested and worked for me, so I thought to share it with you , hope it helps you.

Issue: Data Mismatch

Method 1:

Most of the data mismatch issues can be resolved by doing the following:

Solution:Apply the aggregation rule in the report level.ie,Instead of the default aggregation given, apply aggregation rule based on the column.

eg: For the Revenue metric you can apply Sum aggregation rule instead of Default.

Reason: The default in 11g is not same as 10g.

Method 2:

Almost 80% of the data mismatch issues will get fixed doing this.

IMPORTANT TIP:

Solution: Try unchecking Report Based Total and checking Server Complex Aggregate in certain cases.

Reason: Server Complex aggregate will take the aggregation rule applied in the rpd. Report based total should be unchecked on a level based measure column to get the correct total.

If report based total is checked then it will show the aggregate result(ie total,count…) after applying all the filters and aggregation rule).

If it is not checked then it will show the results before applying the filters and aggregation rule (eg:there is no meaning in taking the total for a level based measure.The total should be taken before applying aggregation rule.hence uncheck report based total here)

NOTE1: In 11g we can apply Report based total for each column in the report whereas in 10g we can apply it only for the complete report.

NOTE2: While migrating 10 to 11g, report based total and default is checked for all columns in the report level if any of the view was using the report based total.

EXPLANATION:

Report-based Aggregate When using report-based aggregates, you might obtain different results in 10g and 11g. You might see the different results in 11g when the following are true:

  • When a column has an aggregation rule of “Server Complex Aggregate”.
  • When a pivot table includes sub-totals, grand totals, or other columns in the Excluded columns section.
  • When a column has Report-based Total (when applicable) checked.

The difference occurs because the data for this column for sub-totals was produced, for example, by the aggregate() function in 10g. In 11g, this issue is resolved by using the report_aggregate() function to accurately reflect the Report-based Total (when applicable) setting.

Method 3:

Possible cause: Basically now all aggregation needs to be applied at criteria level and not at report level.Because in 11g what is set in criteria is not overridden even though we change it in report level.

Solution: Apply aggregation in criteria.

Method 4:

IMPORTANT TIP:

Check whether any column is hidden in criteria. This may be causing a data mismatch. Unhide that column in criteria.

EXPLANATION:

Hidden but included data is not displayed In 10g, if a column is hidden but included in a pivot table, the data is displayed in the pivot table. In 11g, if the column is hidden at the criteria level, then the data is not displayed.

Method 5:

Scenario: Number of rows is less in 11g compared to 10g.

Possible cause: In 11g when we apply filter on a hidden column it will be filtered but in 10g it won’t be filtered if that column is hidden.

Solution: 11g is correct.

Method 6:

Scenario: Decimal places mismatch or very slight mismatch.

Possible cause: (integer value) divided by (integer value) will give an integer value.

Solution: Convert the integer value to float and then divide.

Cast( “int x” as float) / “int y” = “float z”

In some cases rounding will work out.

Integer division returns integer in 11g Integer division that returns double point data in 10g returns an integer in 11g. You might notice these return values for a column in the following cases:

  • The results of the analysis lose the decimal points.
  • Data order is slightly different for the column. Because data is sorted without decimal points, some rows might contain the same values.
  • Formatting of the column is changed because the default formatting for integer (if configured) is used. (In 10g, the default format is used for double data types.)

To force integer division to return double point results, cast the numerator to a double point data type before the division. For example:

“int x” / “int y” = “int z”

Cast( “int x” as float) / “int y” = “float z”

Cast( “int x” as double) / “int y” = “double z”

If this is different behavior than you saw in 10g, then it is because a known issue caused integer division to incorrectly produce a double point result when certain physical data sources where used.

Method 7:

Scenario: Decimal places missing.

Solution: Set decimal places to the desired value.

EXPLANATION:

Integer data types now double data types You can override the default data format for columns that were integer data types in 10g, and are now double data types in 11g. Without this workaround, the data for this column displays as a decimal number with two digits to the right of the decimal point.

You might notice this change for a column in the following cases:

The results of the analysis show decimal points where integers were shown in 10g.

Formatting of this column is changed because the default formatting for double (if configured) is used in 11g.

You can using the following options to maintain the same result as 10g:

Use the Cast function to cast the values to the appropriate data type in the metadata repository.

Change the formatting for the column to display only integers and save that specification as the default format.

Above mentioned tips will help you in fixing 90% of the data mismatch issues. If you are not able to resolve the issues even after trying out the above methods then feel free to post the issues here so that i can help you out.

Happy Reading

 

RPD, Webcatalog Deployment and Start OBIEE 10G

How to Depoly RPD, Webcatalog and Start OBIEE 10G

Follow the screenshots below:

Click on the folder OracleBI

Place your repository in the below path. Here my repository name is Test10g.

Open NQSConfig.

Include the repository name as shown in the highlighted section.Now save it.

Click OracleBIData

Place the web catalog in the below shown path.My web catalog name is Test10g.

Right click instanceconfig and open it using notepad.

Place the webcatalog path as shown below.Save it.

Type services.msc in Run.

Start all the services highlighted below.

Start OC4J now.Wait till the command prompt screen turns to initialized.

Now click on the icon shown below for opening the rpd in online mode.NOTE: Only one rpd can be opened in online mode.Online mode means that it is loaded to the BIServer.In online mode, the administrator edits a live repository that the server has in memory; that is, users can still access the repository while changes are being made. Changes to the repository are immediately applied to the server when they are checked in. Enter the username ie,Administrator and its password.

And

 

Thank you for reading  🙂

OBIEE Security ( Oracle BI Publisher )

Security in OBIEE 11g ( also comparison with OBIEE 10g ) 

Key Security Changes for Release 11g:

Some of the key changes in OBIEE security in 11g are

1. User and Groups are no longer defined in RPD

2. User Profile is derived from LDAP server

3. RPD is protected by RPD Password

4. RPD is encrypted

5. Introduction of Applications Roles

6. User Administrator and Group Administrators not hard-coded in RPD

7. Administrator user not used for Inter-Process Communication (component to component)

8. Credential Store storage mechanism

OBIEE 11g provides a scalable default security mechanism available for immediate implementation after installation. The default security mechanism provides controls to manage users and groups, permission grants and credential store. Following are the security controls that are available after the installation.

1. An embedded LDAP server in WebLogic available to store users and groups known as “Identity Store”.

2. A file to store the permission grants information known as the “Policy Store”

3. A file to store user and system credentials for inter process communication known as the “Credential Store”.

Let’s look at the differences based on some of the common security concepts, Authentication and Authorization.

Authentication:

In 10g default Authentication is RPD based. In 11g, the user and group definitions are moved to a LDAP server embedded with WebLogic server known as the “Identity Store”. Users and Groups can no longer be created in the RPD. Creation of Users and Groups and the association of members to groups are managed in the WebLogic administration console. WebLogic provides the default authentication provider for OBIEE 11g. Users are authenticated by the WebLogic server based on the credentials in the embedded WebLogic LDAP server. The embedded LDAP server is default Authentication provider for WebLogic and hence OBIEE.

OBIEE 11g gets user, groups and other user attributes from the WebLogic LDAP server. This also eliminates the limitation we had with previous versions of OBIEE where only one Group for a user can be read directly from an LDAP server.

WebLogic supports integration with commercial identity management products (also known as Authentication providers). OBIEE 11g certification matrix provides a list of all supported Authentication Providers.

At this time, the following Authentication providers are supported by OBIEE 11g.

· Active Directory 2003, 2008

· SiteMinder 6

· OpenLDAP 2.2.x

· Sun Java System Directory Server version 6.3

· eDirectory 8.8

By default users and groups are created using Oracle WebLogic Server Administration Console.

The users and groups created in the WebLogic administration console can be viewed in the OBIEE administration console. Before looking at the users in the RPD, since we are discussing about the changes in Authentication, I would like to cover the RPD password.

In OBIEE 11g, every RPD is protected by an RPD password. Remember, there are no “Administrator” user and “Administrators” group in OBIEE 11g. The RPD creation utility, requests a password to protect the RPD. The same password is also used to encrypt the password. In 10g only a few critical elements in the RPD were encrypted. In 11g, the entire RPD is encrypted.

The OBIEE administration tool only displays users defined in the WebLogic embedded LDAP server. There is a new menu item “Application Roles”. I will cover this when discussing the changes in Authorization.

Even though the underlying embedded WebLogic identity store is a LDAP server, OBIEE server does not use the “Authentication” initialization block for the default LDAP server embedded within the WebLogic server. The default WebLogic authenticator is a replacement for the OBIEE authentication for users defined in the RPD in 10g. This gives us two options to integrate an external LDAP server with OBIEE for authentication. The external LDAP server can be integrated with WebLogic server as an additional authentication provider or by integrating the LDAP server with OBIEE like in 10g by registering the LDAP server in the RPD and creating an “Authentication” initialization block based on the registered LDAP server. The recommended approach going forward is to integrate all authentication providers at the WebLogic level.

In my next blog entry I will discuss about the changes to “Authorization” in OBIEE 11g, the applications roles, policy store and the credential store.

Authorization:

Authorization in 10g was achieved using a combination of Users, Groups and association of privileges and object permissions to users and Groups. Two keys changes to Authorization in OBIEE 11g are:

  1. Application Roles
  2. Policies / Permission Groups

Application Roles are introduced in OBIEE 11g. An application role is specific to the application. They can be mapped to other application roles defined in the same application scope and also to enterprise users or groups, and they are used in authorization decisions. Application roles in 11g take the place of Groups in 10g within OBIEE application. In OBIEE 10g, any changes to corporate LDAP groups require a corresponding change to Groups and their permission assignment. In OBIEE 11g, Application roles provide insulation between permission definitions and corporate LDAP Groups. Permissions are defined at Application Role level and changes to LDAP groups just require a reassignment of the Group to the Application Roles.

Permissions and privileges are assigned to Application Roles and users in OBIEE 11g compared to Groups and Users in 10g. Note that the Groups shown in the diagram refer to LDAP Groups (WebLogic Groups by default) and not OBIEE application Groups.

Note that the Groups in the OBIEE 10g are replaced with Application Roles in OBIEE 11g. The same is applicable to OBIEE web catalog objects.

The default Application Roles available after OBIEE 11g installation are BIAdministrator, BISystem, BIConsumer and BIAuthor.

Application policies are the authorization policies that an application relies upon for controlling access to its resources. An Application Role is defined by the Application Policy.

Note that the permission for impersonation is granted to BISystem Role. In OBIEE 10g, the permission to manage repositories and Impersonation were assigned to “Administrators” group with no control to separate these permissions in the Administrators group. Hence user “Administrator” also had the permission to impersonate. In OBI11g, BIAdministrator does not have the permission to impersonate. This gives more flexibility to have multiple users perform different administrative functions.

Application Roles, Policies, association of Policies to application roles and association of users and groups to application roles are managed using Fusion Middleware Enterprise Manager (FMW EM). They reside in the policy store, identified by the system-jazn-data.xml file.

Note: Object level permission association to Applications Roles resides in the RPD for repository objects. Permissions and Privilege for web catalog objects resides in the OBIEE Web Catalog. Wherever Groups were used in the web catalog and RPD has been replaced with Application roles in OBIEE 11g.

Following are the tools used in OBIEE 11g Security Administration:

  • Users and Groups are managed in Oracle WebLogic Administration console (by default). If WebLogic is integrated with other LDAP products, then Users and Groups needs to managed using the interface provide by the respective LDAP vendor – New in OBIEE 11g
  • Application Roles and Application Policies are managed in Oracle Enterprise Manager – Fusion Middleware Control – New in OBIEE 11g
  • Repository object permissions are managed in OBIEE Administration tool – Same as 10g but the assignment is to Application Roles instead of Groups
  • Presentation Services Catalog Permissions and Privileges are managed in OBI Application administration page – Same as 10g but the assignment is to Application Roles instead of Groups

Credential Store: Credential Store is a single consolidated service provider to store and manage the application credentials securely. The credential store contains credentials that either user supplied or system generated. Credential store in OBIEE 10g is file based and is managed using cryptotools utility.

In 11g, Credential store can be managed directly from the FMW Enterprise Manager and is stored in cwallet.sso file. By default, the Credential Store stores password for deployed RPDs, BI Publisher data sources and BISystem user. In addition, Credential store can be LDAP based but only Oracle Internet Directory is supported right now.

As you can see OBIEE security is integrated with Oracle Fusion Middleware security architecture. This provides a common security framework for all components of Business Intelligence and Fusion Middleware applications.

Connect WAMP server with Oracle Database

Note:

Path enviornment variable should not contain any other oracle instant client
or php path . Install VC++ 2010,2012 and 2013 before installing wamp.(on 64 bit OS)

 

1- install wamp server ( 64 bit edition ) on directory ( download latest version from website )

C:\wamp

2- from wamp server control pannel , goto

php -> php extension -> enable (select) php_oci8_11g

3- stop all services of wamp

4- put wamp offline

5- download and install oracle instant client ( must be 32 bit) these two files in our case
as our database is oracle 11g release 2 64 bit , so files are

—- instantclient-basic-win32-11.2.0.1.0

and

—– instantclient-odbc-win32-11.2.0.1.0

6- copy extracted folder of oracle instant client to this location

— C:\wamp\tools\oracle\instantclient_11_2

creating a folder or directory : oracle

so copy the folder : instantclient_11_2

to this location :

C:\wamp\tools\oracle

 

7- add enviornment variable Path , to this ( confirm the php file version with your installed wamp , php version)

C:\wamp\bin\php\php5.5.12\ext;C:\wamp\tools\oracle\instantclient_11_2

 

8- restart system

 

9- start wamp server

10- start all services

11- put online

 

12- go to

http://localhost/

Oracle BI Publisher Installation on Windows

Note: This guide covers installation steps for Windows 7, 8, 8.1 and Windows Server 2012 (Tried and Tested). Make sure you have “Microsoft Visual C++ Redistributable “installed on your system.

Prerequisite:

  1. Java SDK 6 or higher
  2. Loopback Adapter
  3. Oracle 11g 11.2

Tried and Tested OBIEE installation on these Configurations:

OS: Windows 7, 8, 8.1, Server 2012 64-bit

RAM: 4GB (minimum)

CPU: Intel Core Dual Core, i3, i5, i7, 2.20 GHz

Minimum configuration needed for OBIEE 11G:

• Disk Space (HARD DISK): 20GB (or above)

• Available Memory (RAM): 4GB (or above)

• Temp Space: 950MB (or above)

• Swap Space: 3GB (or above)

• CPU: dual-core Pentium, 1.5GHz or above

Note: Make Sure port 7001 and 9704 are not in use or there was no previous version of OBIEE installed on your system, if so you have to uninstall previous version and clear all BI Publisher previous version files from Windows registry then follow installation steps in this post.

Downloads For OBIEE 11g:

  1. RUC ( Repository Creation Utility of Oracle ) 11.1.1.7.0 – 32 bit

Download it from oracle website. We must make sure that its version is compatible with OBIEE, also its 32 bit only not 64 bit (no matter your OS or DB is 32bit or 64bit )

  1. OBIEE ( Oracle Business Intelligence Enterprise Edition) 11.1.1.7.0 – 64 bit

Download it from here its license is free but you need it if you are using test or production environment.

Note: If your DB is 32 bit then OBIEE must be 32 bit or if DB is 64 bit then OBIEE must be 64 bit. Both must be 32 bit or 64 bit.

Installation Part 1 (Creating Schema for OBIEE through RUC):-

The RCU is simply the “Repository Creation Utility”. Simply put, the RCU lets you install certain schemas in a database that are required by Fusion Middleware products like Oracle BI EE. These schemas can be installed onto any certified version of database like Oracle Database, IBM DB2, or Microsoft SQL Server. There are schemas that are required by the core components of Oracle Fusion Middleware, while there are schemas that Oracle BI EE requires, like for the Scheduler, for BI Publisher, for the new Scorecard product.

Step 1: Unzip the downloaded RCU to a folder.

Step 2: Navigate to the rcuHome\BIN folder and run the rcu.bat file.

Step 3: Select “Create” from the option radio-button, and click “Next”.

Step 4: Here you can select an Oracle Database, or click the dropdown to select another database. I will use Oracle Database, obviously.

Step 5:

Host Name: Name or ip of database server

Port: It is by default 1521 provided by oracle for oracle database.

Services Name: Check the service name of your database in tnsname file and give it to service name field.

User name & Password: provide the User name & Password which has dba privileges.

Role: select Role depends upon the user you have given.

Step 6:

TNS:

C:\app\user\product\11.2.0\dbhome_5\NETWORK\ADMIN

The below error can be ignored.

Reference : https://forums.oracle.com/forums/thread.jspa?threadID=2244561

Step 7:

Two metadata repositories are required:

  • the Metadata Services (MDS)
  • And Business Intelligence Platform (BIPLATFORM)

Select Metadata Services and Business Intelligence Platform only.

Component Detail ->Select the Oracle Business Intelligence Component. This action automatically selects the Metadata Services (MDS) schema (under the AS Common Schemas group), which is also required by Oracle Business Intelligence.

Step 8:

Schema Passwords -> Use Same Password for all schemas. I used sys as the password for both the schemas.

Step 9 :

Map Table space

After the schemas are created with no errors, the Completion Summary screen opens. In the Completion Summary screen, click Close.

Congratulations!!! Now you have metadata schema required for OBIEE installation.

Sort of a post-wrap exercise may be, for those so interested, to launch Oracle SQL Developer, and peek a look at what sort of tables are created by the RCU. If you choose to do so, this is what you would see.

The “ANNOT*” tables are used by the Scorecard product.

“QRTZ*” tables are used by BI Publisher, “CALC*”, “HSS*” by Essbase and Oracle Hyperion products.

Installation Part 2 (Installing OBIEE):-

After RCU we will go forward with installing OBIEE 11G.

Step 1: Extract the obiee11g setup. Run the installation setup from Disk 1.

The below screen appears. Follow the screen-shots given below.

Step 2: Select Enterprise install because it gives all options to customize.

Step 3: Follow the screen-shots given below.

Step 4:

Here we can specify a user name and password. Unlike OBIEE 10g where the user Administrator used to have all the administrative privileges, in OBIEE 11g we can specify the name of the user who will have Administrative rights. The user name will be weblogic by default. We can change it to any desired name.

The option Scale Out BI System will be used for vertical clustering which is not required here.

Step 5: Next step would be to give Oracle Middleware Home Location. Click Browse and select any location.

OBIEE11g will be installed into this location. The remaining fields will be populated automatically once you select the Middleware Home path.

Step 6:

I got the below error..no problem..click Yes if you get the same error…

Step 7: Just click Next and keep going…

Step 8:

Wow…now is the time you have to enter the name of the 2 RCU schemas DEV_BIPLATFORM and DEV_MDU we created earlier.

Also mention the connection string in this format

hostname:portname:servicename

Note here I have used the port 1521.

Step 9: Use Option Auto Port Configuration.

Step 10: Just click Next and keep going…

Step 11: Click on Install and it will start installing OBIEE.

Step 12: Wait almost 2 hours…don’t put your laptop in sleep mode…

If you got an error…error is starting opmnctl coreapplication_obips1 failed…ignore this error…click next..

Hope you didn’t sleep 😀 ..it took around 3 hours for the OBIEE installation to complete on Windows 7 and less than an hour on Windows Server 2012 ( of course it have high specs ) .

Starting OBIEE Services and Using it Part 3 :-

Now we will try starting OBIEE and playing around with it.

Step1: Click Start BI Services

Note Perform this step only if you got this error:

Error in command line…unable to get file lock..

Note: You may got this error the first time you restart Weblogic …

Note: We got the above error because the file is locked. So we will have to go to the path and delete the lock file so that we can start the presentation services.

Navigate to the path : <OBIEE HOME>/C:\Middleware\user_projects\domains\bifoundation_domain\servers\AdminServer\tmp

and delete the “AdminServer.lok” file .

Wow..its done…now we can start enjoying OBIEE…

Type the following paths in your browser:

Component Web Link
Presentation http://machine_name:7001/analytics
Enterprise Manager http://Machine_name:7001/em
Weblogic (Console) http://Machine_Name:7001/console

Enterprise Manager:

Presentation:

Weblogic Console: