Tuesday, March 27, 2012

Additional features of the Physical and Business Model Diagrams

1) All toolbar options for the diagram, such as Select, New Table, and New Join, are
also available from the Diagram menu.\

2)   Moving the mouse over a table causes the fully-qualified name for that table to
appear in the status bar.

3) You can have both the Physical Diagram and Business Model Diagram windows
open at the same time.

4)  Any customizations you have made to the layout (by manually moving individual
tables) are lost after you close the diagram or choose Auto Layout.

5)  You can cause fully-qualified table names to appear in diagrams by setting a
preference in the Options dialog.

6)  You can use the Print and Print Preview options on the File menu to managePrint option on the toolbar.
printing options for the diagrams. You can also use the

Source : oracle Documentation.



Thursday, March 22, 2012

Connection Failed Error in OBIEE 11 g Admin tool

Hi Friends,

when you create a New RPD and try to import the metadata  you might get the following error.

Solution : Copy your tnsnames.ora file form



and  Stop and start BI services.

Now you will be able to see the next screen.


Tuesday, March 20, 2012

How to Upgrade from OBIEE 10g to 11g

1) Download the sample data set from the Oracle Technology Network (OTN).
2) Download the 10g Sample Sales data set on a server on which Oracle BI 11g is installed.
3) Unzip the Sample Sales data set. You should see four new files:
  • Sample_Sales_V1.3_Dataset.zip
  • Sample_Sales_V1.3_Readme.txt
  • Sample_Sales_V1.3_Webcat.zip
  • samplesales.rpd
4) Create a subfolder in the samples sales folder and name it as “Sample_Sales_V1.3_Webcat”
5) Now copy the “Sample_Sales_V1.3_Webcat.zip” in to “Sample_Sales_V1.3_Webcat” subfolder and extract all. You will have 10 g sample sales catalog in the folder.
6) Create a new directory named "deliveries" in “SampleSales\Sample_Sales_V1.3_Webcat”. In 10g, the deliveries directory is a special directory used for data such as Dashboard snapshots used by Briefing Books. The directory is called deliveries by default and is located in the OracleBIData\web\catalog directory. In many cases, the 10g directory is empty. If the 10g deliveries directory is not available, any empty directory named deliveries can be given for upgrade. If Upgrade Assistant finds a directory called deliveries in the same folder as the catalog, it is selected by default.
7) Sample_Sales_V1.3_Dataset.zip contains a sample set of data in XML format for display in Oracle BI dashboards. To make this data available for queries, extract Sample_Sales_V1.3_Dataset.zip to the following location:
For Ex:
Note:when I installed the OBIEE 11.1.16 on my laptop and navigated to the below folder, as said in the documentation path below, I didn’t find the server folder
%MIDDLEWARE_HOME%\instances\instance1\bifoundation\OracleBIServerComponent\ coreapplication_obis1\server\Sample
. It is just
So what I did was created a folder called server and dragged and dropped the sample folder inside server folder. Now I have the files in the specified path in the oracle Documentation.
Upgrading the Oracle BI Repository and Catalog
1) Navigate to the following folder
2) Double-click ua.bat to open Upgrade Assistant in the graphical user interface mode and follow the screen shots below.
You can check the log file @ C:\BIHOME\Oracle_BI1\upgrade\logs. If any errors occur during upgrading

Verifying the Upgrade:
1) Login to Oracle Enterprise Manager http://localhost:7001/em
2) In the left pane, expand Business Intelligence > coreapplication.
3) In the right pane, select the Overview page and confirm that all system components are available.
4) Select the Deployment page.
5) Select the Repository subtab. This page shows the current installed repository. Notice that the Upgrade Assistance automatically uploaded the upgraded repository to the BI Server domain. In this example, the repository is named as samplesales_BI0002.
Verifying Repository Metadata:
1) Open the repository in the offline mode
2) Please give your Repository password which you have mentioned in screen 3 of 8 in the upgrading process.
You will see screen as below

3) Double-click Connection Pool A and Click yes

4) Set the value to the OB_EE_HOME variable
5) Go to Select Manage > Variables to open Variable Manager

6) Double click on variable BI_EE_HOME. Change the Default initializer to the Folder directory where you have copied the data.zip in the earlier step.

7) Click ok to close.
8) Now check in the changes. Do not Do any global consistence check at this point just save.
Note : nQSError: 37005 Transactional Update Failed
If you get this error do the following

This will resolve your problem.

Verifying Reports and Dashboards
1)      Login to  OBIEE Home Page and Login


4) Click on Analysis

5) Select sample sales subject area

6)You will see the below screen where you can build the sample report

17) Now build Sample report


Source : Oracle's website.
I just followed the above document and did the upgrade from 10G to version of OBIEE.

Monday, March 19, 2012

Design Tips for the Presentation Layer- OBIEE

                                  Design Tips for the Presentation Layer
In this layer, you can do the following:

1.       You can show fewer columns than exist in the Business Model and Mapping layer.
2.       For example, you can exclude the key columns because they have no business
3.       meaning.
4.        You can organize columns using a different structure from the table structure in
the Business Model and Mapping layer.
5.        You can display column names that are different from the column names in the
Business Model and Mapping layer.
6.       You can set permissions to grant or deny users access to individual subject areas,
tables, and columns.
7.        You can export logical keys to ODBC-based query and reporting tools.
8.        You can create multiple subject areas for a single business model.
9.        You can create a list of aliases (synonyms) for presentation objects that can be used
in Logical SQL queries. This feature lets you change presentation column names
without breaking existing reports.

Design Tips : 
  1. Because there is no automatic way to synchronize all changes between the
    Business Model and Mapping layer and the Presentation layer, it is best to wait
    until the Business Model and Mapping layer is relatively stable before adding
    customizations in the Presentation layer.
  2. There are many ways to create subject areas, such as dragging and dropping the
    entire business model, dragging and dropping incremental pieces of the model, or
    automatically creating subject areas based on logical stars or snowflakes.Dragging
    and dropping incrementally works well if certain parts of your business model are
    still changing
  3. It is a best practice to rename objects in the Business Model and Mapping layer
    rather than the Presentation layer, for better maintainability. Giving user-friendly
    names to logical objects rather than presentation objects ensures that the names
    can be reused in multiple subject areas. Also, it ensures that the names persist even
    when you need to delete and re-create subject areas to incorporate changes to your
    business model.
  4. Be aware that members in a presentation hierarchy are not visible in the
    Presentation layer. Instead, you can see hierarchy members in Answers.
  5. You can use the Administration Tool to update Presentation layer metadata to give
    the appearance of nested folders in Answers. See "Nesting Folders in Answers" for
    more information.
  6. When setting up data access security for a large number of objects, consider setting
    object permissions by role rather than setting permissions for individual columns.
  7. When setting permissions on presentation objects, you can change the default
    permission by setting the DEFAULT_PRIVILEGES configuration setting in the
    NQSConfig.INI file.
Reference : Oracle metadat Repository Building Guiding.


Thursday, March 15, 2012

Design Tips for the BMM Layer- OBIEE

                                        Design Tips for the BMM Layer
1)  Create the business model with one-to-many logical joins between logical
dimension tables and the fact tables wherever possible. The business model
should ideally resemble a simple star schema in which each fact table is joined
directly to its dimensions.
2)Every logical fact table must join to at least one logical dimension table. Note that
when the source is a fully denormalized table or flat file, you must map its
physical fact columns to one or more logical fact tables, and its physical dimension
columns to logical dimension tables.
3) Every logical dimension table should have a dimensional hierarchy associated
with it. This rule holds true even if the hierarchy has only one level, such as a
scenario dimension {actual, forecast, plan}.
4) When creating level-based measures, make sure that all appropriate fact sources
map to the appropriate level in the hierarchy using aggregation content. You set
up aggregation content in the Levels tab of the Logical Column dialog for the
measure. Note that this is different from the Content tab of the Logical Table
Source dialog, which is used to specify the grain of the source tables to which it
 You only need to set up aggregation content in the Levels tab of the Logical
Column dialog for level-based measures. For measures that are not level based,
leave the Logical Level field blank
5) Typically, logical fact tables should not contain any keys. The only exception is
when you need to send Logical SQL queries against the Oracle BI Server from a
client that requires keys. In this case, you need to expose those keys in both the
logical fact tables, and in the Presentation layer.
6) Normally, all columns in logical fact tables are aggregated measures, except for
keys required by external clients, or dummy columns used as a divider. Other
non-aggregated columns should instead exist in a logical dimension table.
7) In some situations, you might want to have multiple logical fact tables in a single
business model. For Logical SQL queries, the multiple logical fact tables behave as
if they are one table.
Reasons to have multiple logical fact tables include:
– To assign projects. See "Setting Up Projects" for more information.
– To automatically create small subject areas in the Presentation layer. See
"Automatically Creating Subject Areas Based on Logical Stars and Snowflakes"
for more information.
– For organization and simplicity of understanding.
Unlike relational fact tables, logical fact tables can contain measures of different
grains. Because of this, grain is not a reason to split up logical fact tables.
8) You can define calculations in either of the following ways:
– Before the aggregation, in the logical table source. For example:
sum(col_A *( col_B))
– After the aggregation, in a logical column derived from two other logical
columns. For example:
sum(col A) * sum(col B)
You can also define post-aggregation calculations in Answers or in Logical SQL
9) If you plan to use Oracle Scorecard and Strategy Management, it is a best practice
to implement at least one time dimension in the Oracle BI repository you are using
for your KPIs. This action is necessary because you use KPIs in scorecards to
measure progress and performance over time. Note that an individual scorecard
automatically picks up any dimension used by KPIs in that scorecard.
10) Aggregate sources should be created as separate logical table sources. For fact
aggregates, use the Content tab of the Logical Table Source dialog to assign the
correct logical level to each dimension.
11) Each dimension level in a hierarchy must have a unique level key. Also, each
logical dimension table must have a unique primary key. Normally, this key is also
used as the level key for the lowest hierarchy level.
12) Renaming columns in the Business Model and Mapping layer automatically
creates aliases (synonyms) for Presentation layer columns that have the property
Use Logical Column Name selected.
13)prevent problems with aggregate navigation, ensure that each logical level of a
dimension hierarchy contains the correct value in the field named Number of
elements at this level. Fact sources are selected on a combination of the fields
selected as well as the levels in the dimensions to which they map. By adjusting
these values, you can alter the fact source selected by the Oracle BI Server
Modeling Outer Joins

The following guidelines provide tips on how to model outer joins:
1) Due to the nature of outer joins, queries that use them are usually slower. Because
of this, define outer joins only when necessary. Where possible, use ETL
techniques to eliminate the need for outer joins in the reporting SQL.
2) Outer joins are always defined in the Business Model and Mapping layer. Physical
layer joins do not specify inner or outer.
3) You can define outer joins by using logical table joins, or in logical table sources.
Which type of outer join you use is determined by whether the physical join maps
to a business model join, or to a logical table source join.
4) Be aware that outer joins in logical table sources are always included in a query,
even if the none of the columns in one of the mapped physical tables are used.
For example, assume that a logical table source is mapped to physical tables A and
B. When no outer joins are defined, if physical table A is not required to satisfy a
request, it is not included in the physical query. However, if the logical table
source has an outer join defined to table A, table A is still included in
5) If you must define an outer join, try to create two separate dimensions, one that
uses the outer join and one that does not. Make sure to name the dimension with
the outer join in a way that clearly identifies it, so that client users can use it as
little as possible.

Reference : oracle repository building documentation


Design Tips for the Physical Layer- OBIEE

                                              Design Tips for the Physical Layer
The following is a list of tips to use when designing the Physical layer:

1) It is recommended that you use table aliases frequently in the Physical layer to
eliminate extraneous joins, including the following:

a) Eliminate all physical joins that cross dimensions (inter-dimensional circular Joins) by using aliases.
b)Eliminate all circular joins (intra-dimensional circular joins) in a logical table
source in the Physical Model by creating physical table aliases.

For example, say you have a Customer table that can be used to look up ship-to addresses, and using a different join, to look up bill-to addresses. Avoid the circular joins by aliasing the table in the Physical layer so that there is one instance for each purpose, with separate joins.

If you do not eliminate circular joins, you might get erroneous report results. In
addition, query performance might be negatively impacted.

2) You might import some tables into the Physical layer that you might not use right away, but that you do not want to delete. To identify tables that you do want to  use right away in the Business Model and Mapping layer, you can assign aliases to physical tables before mapping them to the business model layer.

3) An opaque view (a Physical layer table that consists of a SELECT statement) should
be used only if there is no other solution to your modeling problem. Ideally, a
physical table should be created, or alternatively a materialized view. Opaque
views prevent the Oracle BI Server from generating its own optimized SQL,
because they contain fixed SQL statements that are sent to the underlying data

Reference : Oracle Repository building Docoumentation

Tuesday, March 6, 2012

OBIEE installation on Windows Xp(32-BIT)


In the OBIEE Certification,  I didn't see windows 32-Bit as supporting Operating system. Still I gave a shot to see whether it will work or not.

It works!!!.

My System Config :
1)RAM - 8 GB..
2)  4 GB I used for Virtual Machine.

 For Installing a Loop back Adapter on Windows XP-32 Bit , 11g R2 oracle database refer my earlier blog entry.



OBIEE Installation:

Please feel free to post your comments.