It's all about Business Intelligence

March 21, 2011

Aggregator Transformation

Aggregator Transformation

Let us explore how to use Aggregator Transformation while building ETL mapping using Informatica.

It helps us to perform aggregate calculations such as SUM, AVG etc.  Here is the flow how it works

1. Reads the data from input ports

2. Perform the calculation

3. Store the index and data in cache for further aggregation

4. Performs the steps 1-3 till the last row

5. Gives the data to output ports

By sorting the aggregator input we can increase the performance. Also make sure we limit the number of input ports which will help us to use our cache space efficiently.

We can specify conditional expressions like which will help to reduce the number of rows to be processed.

For example, use the following expression to calculate the total sales where the quantity is greater than 10


We can also configure the Integration service the way it handles the NULL values. By default NULL is treated as NULL only.

You can also use nested aggregate functions but in that case all the expressions in the transformation should be nested ones. You cannot use single level and nested in the same transformation.

Each transformation has certain properties.

1.       Cache Directory: Path where the cache files will be stored

2.       Tracing Level:  Need to set the log level. We have simple logging to most detailed log level which includes each and every row which passes through.

3.       Sorted Input: When the input is sorted, aggregator job becomes easy and it increases the performance. Check this option when the input is sorted

Do not use sorted input if expression uses nested aggregate functions and when you use incremental aggregation.

If the input is not sorted and still if you use sorted input option then the session will fail.

4.       Data and Index cache size: set the cache sizes depends on the requirement.

5.       Transformation Scope: This specifies the boundary for applying the calculation logic which is based on the commit interval level of the session. Since aggregation involves handling rows which are spreaded across table it is better to keep this as “All Input” rather than “Transaction”.

We can perform Incremental Aggregation which can be configured while creating task in workflow design. It will utilize the historical cache to perform the incremental aggregation.

To Use Sorted Output option:

Add a custom property to the integration service from Admin console as shown below.

August 19, 2010

Best Practices!!! No Thank You

Filed under: Uncategorized — Santhosh @ 6:55 pm

Its wonderful message that I get know from Frank Buytendijk VP and Fellow at Oracle Corp recent video blog post which I would like to share here.

This could be a rational approach of looking how to adopt best practices.

What do we mean by saying Best Practices, It just worked best for you and may or may not give the same results for others.

In terms of business processes & standards blindly adopting best practices may not yield best results. But mostly it can yield average to good results.

But it doesn’t mean that we don’t want best practices and then it could be bad one too.

Strategically saying Best Practices could be your starting point and thinking in terms of how it will fit for your business and analyzing them properly may lead to revise the same or you may end up innovating a better practice.

You could check more thoughtful business articles in following links

Frank’s Blog

Harvard Business Review

There is one wonderful section in Oracle’s website about thought leadership. Please check that too.


August 18, 2010

Hierarchies in OBIEE 11g – Part 2

As a continuation with my previous blog post on Value Based Hierarchies, Let us see how to model Ragged (Unbalanced) and Skip level hierarchies in OBIEE.

In this sample implementation we will consider the Products table which comes with the sales history (SH) schema which comes with Oracle Database. Products entity has hierarchy like below.

For demonstration purpose I had removed a level (Skip Level) for a particular product and also its similar to Ragged since the height of the hierarchy is not same (not all the leaf nodes are at last level).

Problems with Ragged & Skip Level:

In case of Ragged we cannot define fixed number of levels as for certain rows don’t have all the levels. In this example product Mini Camcorder don’t have a subcategory.

In case of skip level again we have to handle the NULL values in between levels which lead to removal of the row which is part of the skip level and won’t be displayed in the output. In this example same Mini Camcorder will have NULL value for sub category.

OBIEE 11g has a way to handle NULL problems easily.

It’s just we have to mark those dimensions as Ragged or Skip level and rest will be taken care of.

Import the Product table and Sales table into physical layer. For demonstration purpose I had removed all other columns except product ID from the Sales table.

If not already then create physical join between Products and Sales table

(If you are not sure how to import tables from Oracle database then refer my previous blog post on Value Based Hierarchies )

Create BMM layer with Product as dimension with the sales fact. After pulling the logical tables right click on Products to create a level based hierarchy.

After creating the dimension we have to create Product Category and Product Sub Category levels. Right click on Product Details to create parent level.

Enter the Level name and number of elements and click ok. Number of elements is a parameter which will be used by BI server to estimate the number of rows in each level. This need not to be an accurate one. In our case we can think of ratio like 1:10:40:100 (Product Total {default one for all hierarchies} ->Category->Sub Category->Product).

Drag Sub Category columns to the level which got created now and double click on the Level Name and create keys like below. We need to define key for each level so we chose the Sub Category ID and for display purpose we use the Sub Category Name (Use for Display option).

Same way create one more parent level for Sub Category called Category(as exists in our scenario) and pull the category columns to the parent level , define the keys as we did for sub category.

Go back to the Product Detail level and create one more key for description since Product Identifier is a ID column. Same like above.

Overall dimension should like below.

Now let us set the dimension type as Ragged & Skip Level both like below. Double click on ProductDim to get the below window. This is the extra step we do to take care of Ragged & Skip Levels.

Define the measure in the Sales fact as shown below.

Pull the BMM model and create the presentation layer (Just drag and drop the BMM model). Remove the identifier columns.

Now let’s go to answers and verify out work.

After logging into answers choose the product Hierarchical column and a measure and see how that product “Mini DV Camcorder with 3.5″ Swivel LCD” which don’t have sub category level got reported.

Now we are done with hierarchies. Next let’s see some of the new enhancements which were not there till OBIEE 10g. byeee

August 17, 2010

Hierarchies in OBIEE 11g Part 1

Hierarchies in OBIEE 11g – Part 1

It’s extremely exciting to see the breadth and depth of OBIEE 11g features. There are many things which were constrained till previous release, 11g fixed most of them.

Let us start with Hierarchies and see how to implement different types of hierarchies in BI 11g with step by step example. This article is specific to Value Based Hierarchies.


There are different types of hierarchy which exists in an Organization data which gives more insight for our analysis.

1. Level Based Hierarchy:

Basic type of hierarchy which has fixed number of levels. For example Time Dimension hierarchy is a good example for this one.

Year->Quarter -> Month

We have two special cases of level based.

· Ragged Hierarchy

Members of certain levels don’t have child members.

· Skip Level Hierarchy

Level itself will not be present in case of certain hierarchies. In the above diagram if we consider “direct” as a delivery mode under “wholesale” it becomes skip level hierarchy.

1. Value Based Hierarchy

This will be called as parent child hierarchy too. Typical reporting structure in an organization is a good example for this one.

In this article let us see how to create a value based hierarchy step by step.

Believe me creating value based hierarchy in 11g is like walking on ice J

Let us consider employees table which has Manager -> Employee relationship and model the same.


1. Importing the source table(Employees)

2. Creating value based hierarchy in BMM layer

a. Define parent(Manager_ID) & child(Employee_ID) columns

b. Generate parent -child hierarchy table DDL statements

c. Generate parent – child hierarchy table DML statements

d. Execute DDL & DML and create hierarchy table and insert data into it

3. Pull the BMM model into presentation layer

4. Run answers and check the output.

Make sure Employees table has below structure.

1. Importing Employees table into Physical layer

Finally update the row count and test the database connectivity

2. Create value based hierarchy in BMM layer

Drag employee table twice to the BMM (OBIEE requires star model) as shown below

Rename the columns and do the basic cleanup

Right click on the BMM model and join the Employee and Salary Information.

Now right click on Employees logical table and choose for new parent child hierarchy

Choose the member key (by default it will take the primary key) and parent column as shown in the below screenshot.

Then click ok parent- child settings. This is the place where we are going to generate the DDL & DML scripts which we can use to create and populate the hierarchy table which will be used by BI server to report parent child hierarchies.

Click on new hierarchy table (middle one – yellow arrow). If you have already generated hierarchy table then click on the select (red arrow) and choose the table.

After clicking new give the name for the DDL and DML scripts. This wizard will create the SQL scripts.

Give name for the hierarchy table

Click next and verify the scripts

After finishing you can see the details are mapped now

After finishing the wizard you can see the HierarchyTable got imported automatically.

Now go to the scripts location and run the DDL & DML scripts and commit the changes via SQL command prompt

Update the row count and make sure table got created properly

3. Pull the BMM model to presentation layer and create presentation folders.

Make sure that Hierarchy inside the Employees folder is visible.

Now we are done with the metadata definition. Save the RPD and make sure there are no consistency errors.

Start the WLS and BI services and make sure all of them are running fine.

4. Running answers and verifying the hierarchy

Before logging into answers lets enable the logging so that we can check the physical SQL’s that are getting generated while performing drill down’s through hierarchy.

Open the RPD in online mode and navigate to Manage->Identity and set the log level for Administrator.

Check in the changes and close the RPD.

Login to URL http://localhost:9704/analytics using Administrator/Admin123

After logging in since we changed the log level in online mode we have to reload the metadata.

Click on Administration on the top right

Then click on Reload files and Metadata

Then go to our Analysis like below

Click ok ValueBasedHierarchy

Select the Hierarchy column from Employees and the salary measure from the fact.

Then click on results.

We are done and can see the hierarchy information like below.

This output just shows the salary for each employee and their manager information. This result set is not an aggregated salary for each level. Since salary information pertains to each employee salary is not aggregated.

If you check the Nquery log BI server generates only one query for the any number of operations. It’s fully in memory operation.

Let’s continue to see Ragged and Skipped level hierarchy in my next post.

Happy Learning BI J

June 20, 2010

Come Back :)

Filed under: Uncategorized — Santhosh @ 4:42 pm
Tags: ,

I had been quite busy this year with my Busienss Trip,Siebel Server Architect certification & most of all working on my first product release in Real Time Reporting Soltuion area which is getting into good shape.So very much excited about that.

Also working on widening my knowledge portfolio by learning Golden Gate(Real Time Data Integration product),Informatica(very popular ETL tool which mingles a lot with OBIEE in Oracle’s BI Applications) & core database related stuff(Architecture,Performance tuning etc).Soon you will get to see posts from these areas including OBIEE

Bye for now and see you in my next post on Golden Gate Introduction.

January 9, 2010

Oracle Database Resource Manager and OBIEE

Filed under: Uncategorized — Santhosh @ 9:21 pm
Tags: ,

Resource allocation/management is one of the main topic for BI application.Since it has been queried by variety of users for variety of purposes all the time, Application should know how to handle the request and it should process them based on priority attached to the querying user.

OBIEE has a good amount of flexibility in this area like you can specify the max no of rows for a user/group or issue a warning message/stop querying when the user’s query exceeds some fixed amount of rows.

But in a day-to-day business operations its important to get this resource management into more detail by limiting some types of user’s queries some time and for executive users we can give more importance and so on.

I came through an excellent article written by Venkat in rittmanmead’s blog which explain resource management in Oracle databases  in detail and the way we can implement them.Please go through it to learn how we can do better resource utilization in Oracle BI environment.

Also wish you all a very very happy new near 🙂

April 5, 2009


Filed under: Uncategorized — Santhosh @ 4:56 am

This is going to be a random writing on Business Intelligence.

My work is primarily in and around Oracle BI technologies.

So I thought of creating a collection of information which I read from other sources(blogs,pages etc) here.

Please feel free to leave your comments which will help me in improving this blog.

Thanks for visiting my blog 🙂 Please keep visiting..

Create a free website or blog at