Oracle Business Intelligence and Data Warehousing Practice Notes and Knowledge Repository

  • About Me

    Hello Friends,

    This is Santosh Kumar Gidadmani, a Business Intelligence and Data Warehouse Enthusiast passionate about blogging articles in the BI, Data warehousing, space. This is my attempt to share my experience and knowledge on Oracle BI & Data Warehousing Subjects.

  • OBIEE 11g Certified Implementation Specialist

  • Oracle Partner Network Certified Specialist

  • Visit My Profiles

  • Enter your email address to subscribe to this blog and receive notifications of new posts by email.

    Join 73 other followers

  • Blog Hits Since Nov 2010

    • 364,321 Hits
  • Live Traffic

  • Visitors Country

    Since-Mar'12Free counters!

Archive for November, 2010

Dimensional Hierarchy

Posted by Santosh Kumar Gidadmani on November 18, 2010

Dimensional Hierarchy – Preferred Drilldown – Number of elements at this level

A hierarchy is a set of parent child relationships between attributes within a dimension.

Dimension hierarchical levels are used to perform the set up aggregate navigation, configure level-based measure calculations and drill down from one parent to a child level.

Creation of Dimensional hierarchy.

There are two methods of creating dimensional hierarchies.

1. Automatic

2. Manual

Automatic Method:

Right click on the dimension and click on Create Dimension. This will create two levels total and detail level by default. You can change it to any way you want.

Manual Method: We will create Time dimension

1. On offline & online mode, Right click on the business model and select, New Object – Dimension.

2. Right click on the new dimension and select New Object – Logical Level, type as “Time total” for the Name and check the option, Grand Total Level and save it.

3. Right click on the “Time Total” level, and select the option, New Object – Child Level, name as Year and save it and to the same for Month.

4. Right click on the “Month” level, and select the option, New Object – Child Level, name as Time Detail and save it.

5. Drag year from logical table and drop it on Year level, drag month from logical level and drop it on month level and drag date from logical table and drop it on Time detail level.

6. Drag and drop the primary key of the time logical table to the time detail level.

7. Right click on the year logical column properties, on the levels tab, select year and click ok, do the same for month and time detail levels.

8. Select on year, month, time detail levels, select keys and add the same keys (check the option use for drilldown option).

9. Save and check on answers.

For time dimensional hierarchy, it is necessary to check the option “Time Dimension” on the general tab to support for time series calculation.

Preferred Drilldown

You will see a tab in the logical level called Preferred Drilldown

This option is used to force BI to move to any level specified in preferred drill tab. It is most commonly used to drill from one dimension to another.

Number of elements at this level

This number is used by the Oracle BI Server when picking aggregate sources. The number does not have to be exact, but ratios of numbers from one logical level to another should be accurate.

These Level Counts can be automatically known for one or more dimensional hierarchies using Estimate Levels.

Open the RPD in online mode, right click on any dimensional hierarchy or any BMM model, select estimate level, click yes for “Do you want to check them out?”. It will automatically update the level counts in the dimensional hierarchy.

– Santosh

Posted in Dimensional Hierarchy, OBIEE 10g, OBIEE 10g RPD | Tagged: | Leave a Comment »

OBIEE Administration Tool

Posted by Santosh Kumar Gidadmani on November 15, 2010

 The OBIEE Administration tool is the main engine to develop RPDs and manage major OBIEE configurations. It provides a graphical representation of the three layers of a repository namely;

1 Physical Layer

2 Business Model and Mapping layer and

3 Presentation Layer

1. Physical Layer

The Physical layer contains information about the physical data sources to which Oracle BI Server submits queries. There can be one or more data sources in the Physical layer. It consists of Connection pool, Schema folder, Tables, Columns keys.

2. Business Model and Mapping Layer (BMM)

The BMM layer is used to simply and reorganizes the data to present a meaningful data model to form the basis for presentation layer. All the major calculations are made in this layer as per the customer requirements. This layer contains more business models to support the organization strategic goals. It consists of Business models, dimensional hierarchies, logical dimension tables, logical fact tables, logical table source, logical columns, and logical measures. The dimensional modeling star schemas are built using dimensions and facts. BMM layer objects map to source data objects in the Physical layer. Business models may map to multiple data sources, Logical tables may map to multiple physical tables, Logical columns may map to multiple physical columns etc. Some of the Major configurations like, time series, level based measures, MUDE, additional calculations, aggregations, fragmentations and partitions are made here.

3. Presentation Layer.

Presentations layer provides customized view of a business model to users. The columns are more meaningful in this layer as these are represented to end users for ad hoc reporting. Presentation layer objects map to objects in the BMM layer. A Model in the presentation layer is called presentation catalog and same model in the answers is called subject area. A presentation catalog can map to only a single business model. However, multiple presentation catalogs can reference the same business model. It consists of presentation catalog, presentation folder, and presentation columns.

– Santosh

Posted in Administration Tool, OBIEE 10g, OBIEE 10g RPD | Tagged: | Leave a Comment »

Act As (Proxy User) – OBIEE 10g

Posted by Santosh Kumar Gidadmani on November 14, 2010

What is Proxy User or Act As Function

The Act As or Proxy functionality authorizes a user to Act As another user, Once you are logged into OBIEE dashboards, you can see list of target users in the Act As function. This function is useful to move my dashboard data to shared, check the dashboard look and feel, etc. without having to ask other users.


1. Create Proxy Table to associate Proxy ID to Target ID and provide the level of access required.
2. Import the tables to Physical Layer
3. Create Variables
4. Modify Instanceconfig.xml file and
5. Create a Custom Message Template
6. Enable the Proxy Function in Manage Privilege
7. Restart the services
8. Check the Act As Functionality

Key Points

1. The Proxy Id and Target Ids are case sensitive, ensure the same case if these user are coming from RPD or from LDAP or from external Authentication. I have used external DB authentication for this example.

2. The code VALUEOF(NQ_SESSION.RUNAS) should be same case in all variables.

1. Create Proxy Table as below (Ensure these users are part of your users (as per your user configuration from LDAP or RPD)

Import these tables into the physical layer of Admin tool.

3. Create three Session Variables as below


On the Edit Data Source provide as select ‘something’ from dual and on edit data target, create variable as RUNAS (caps)



And variable as PROXY (caps)

Click on Edit execution precedence and add Set_RUNAS variable.

Proxy Level


On the edit data target as PROXY_LEVEL (caps)

And on the edit execution precedence, add the proxyblock variable.

Ensure there are no errors when you test these variables.

4. Modify Instanceconfig.xml file

Open the file from D:OracleBIDatawebconfig

Add the below code just after the <ServerInstance> and save it





Note: LogonParamSQLTemplate is the new template which you will create in the below step, ensure you give the same name for the new template xml file.

200 is the max number of proxy users you can Act as.

5. Create a Custom Message Template

You have to create a custom message template for the proxy functionality to get the list of target users that a proxy user can Act as.

Create a XML file and place this file in \\OracleBIData\web\msgdb\customMessages. Give the name as LogonParamSQLTemplate.xml

Below is an example to create the code

<?xml version=”1.0″ encoding=”utf-8″ ?>

<WebMessageTables xmlns:sawm=””>

<WebMessageTable system=”SecurityTemplates” table=”Messages”>

<WebMessage name=”LogonParamSQLTemplate”>


<logonParam name=”RUNAS”>


EXECUTE PHYSICAL CONNECTION POOL SH_Santosh.SH select targetId from OBIEE_PROXY_USER where proxyId =’@{USERID}’ and targetId=’@{VALUE}’

EXECUTE PHYSICAL CONNECTION POOL SH_Santosh.SH select proxyId, proxyLevel from OBIEE_PROXY_USER where targetId =’@{USERID}’






6. Enable the Proxy Function in Manage Privilege

Logon to Dashboard using Administrator user, go to settings – administration – click on manage priviledges.

Search for Proxy and enable it to every one.

7. Restart the Oracle Server and Presentation services.

8. Check the Act As Functionality.

Logon Dashboard using Administrator, click on settings – Act As, you would see the below screen

Click on any of the user and click OK. You would see the dashboards for that person.

You can go stop and can switch to another user.

– Santosh

Posted in Act As (Proxy User), OBIEE 10g, OBIEE 10g RPD | Tagged: | Leave a Comment »

%d bloggers like this: