BI Apps 7.9.6.1 is released

Here is the press release from Oracle:

Oracle Introduces New Release of Oracle® Business Intelligence Applications

This change over the release 7.9.6 is that it is certified with Teradata, MS SQL, and DB2. Also, it includes the performance tuning of several ETL process.

BI Apps 7.9.6 is out

Here is the press release.

This is a very important release for customers.  Several new features are introduced:

Informatica ETL Tuning Steps

First, we need to identify where the performance problem come from.   The bottleneck can be:

• Source Extract
• Transformation
• Lookup
• Target Write

I will talk about how to isolate the problems and go over each about how to investigate the problem in each of these area.

Data Security in Oracle BI Apps

I am describing how data security works in Oracle BI Apps.

Use ANSI SQL for Outer Join

The OLTP source applications like PeopleSoft and Siebel applications can run on many different databases including Oracle, MS SQL, or DB2. The target data warehouse can also run on different database platforms, incluidng the above databases, plus Teradata.

Various technologies can be used to enable the cross database platform support in the pre-packaged BI apps. Oracle BI Enterprise Edition allows you configure the database connections using the native drivers. ODBC can also be used to access different databases.

In order to deliver to deliver the pre-packaged ETL adapters, two technologies can be used : ANSI SQL and ETL tool specific SQL. It is preferable to avoid the dependency on the ETL plarform and use ANSI SQL92 syntax. When we move from ETL to ELT,

Use SQL*Loader with Named Pipe

Typically using SQL*Loader assumes that a flat file will be used as the input. The file will need to be created and generated before the SQL*Loader can take the data from the file and load the data into Oracle. The performance can be improved and the disk space can be saved if you use named pipe with SQL*Loader.

Hyperion Essbase Dimension Terminology

I found the the language used in Hyperion Essbase documentation is very useful for describing the dimension hierarchy. To communicate effectively, sometime we need precise teams to describe things. The terms defined in the Hyperion Essbase documentation helps.

Changed Data Capture in ODI

ODI is the tool will also be used to develop the ETL (or ELT) for Oracle BI Applications. ODI supports Changed Data Capture by its journalizing module.

My understanding of CDC flow in ODI is as follows:

Oracle BI Applications 7.9.5 is out

Oracle BI Applications 7.9.5 is released early this month. Here is a quick summary of the features introduced in this release and where you can get more information about it.

Credit Management in OLTP

Account receivables can have a big impact to the cash flow of an organization. Bad credit can hurt your company’s bottom line. Manging customer credits help the company to manage the risk and avoid the issues.

In this post, I will touch credit check and credit limit, centralized and decentralized credit management, and credit limit currency.  This is a a result of studying various credit management features in the OLTP system. The objective is to understand how the these various system works and understand how the credit management process looks like, and how BI can help in these processses.

ABC Analysis in Inventory Management

 I did some study on the ABC Analysis in Inventory Management.  It is also useful in business analytics.  I will cover what it is and how it is supported in various ERPs. Finally, how it may be used in analytics application.

RFM Analysis

RFM (recency, frequency, monetary) analysis is used by marketing to determine their campaign targets. It is based on three metrics from the previous sales records:

Why do people not report by Month in the Gregorian calendar?

1. The number of days is different in each calendar month.
2. The week and month cannot be aligned.  The number of weekends is different in each calendar month.
3. The number of working days is different in each calendar month.  It ends up that the number of days in each quarter is also different.
4. The period closing day will fall into different days in each period.  The accounting department prefers always close the period by a given day in a week, such as Wednesday or Friday.

13 Period Calendar

I will discuss the following topics:

• What is the 13 period calendar?
• Who uses the 13 period calendar?
• How is it different from the 4-4-5 calendar?

Data Warehouse Basics

Here is a link to my Data Warehouse Basics presentation slides. I explain what is a data warehouse, what is characteristics of a data warehouse, and what the data warehouse deployment looks like.

A good OBIEE web demo from New York City’s City Wide Performance Reporting

New York City now enables the public to monitor the city’s performance by making the OBIEE based dashboard and report online.

Here is the link to their CPR (City Wide Performance Reporting) site : http://www.nyc.gov/html/ops/cpr/html/home/home.shtml

Database Tuning – drop unused indexes

Index is very useful during query, especially in data warehouse.   However, it adds the overhead in the storage and ETL process.

I found an excellent article about how to monitor  the index usage in Oralce.   You should periodically run the index monitoring process and pro-actively drop those un-used indexes.

Bitmap Index – when to use it?

I will cover how Bitmap index work, when to use it and how to use it in this article.

How does it work?

The bitmap index stores the column values in bits. Each bit represents a single value. For example, the gender column has two possible values: Male and Female. three bit will be used in the bitmap to capture the index on the gender column. A good example can be seen in reference 1. So the more distinct value is, the more space is required to store the bitmap.

Internally, the database engine, like Oracle, uses a map function to converts the bit location to the distinct value. (See reference #2) Many bitmap indexes can be used together since database can merge it, so this can improve the response time. (See Reference #3 for the example of merging the index on Marital Status and Region)

When to use it?

BI Applications and Embedded BI, Part 4

This is my 4th post about the embedded BI. The key is that a OBIEE and OBIA warehouse based solution is embeddable to the OLTP system as long as the OLTP system can provide the basic support.

In this article, I will discuss one of the key enabling technology – integrated authentication.

BI Applications and Embedded BI, Part 3

I would like to go over some of the embedded BI related technologies you can use to make the data from BI Apps embedded in the OLTP systems.

In this post I will describe the Micro ETL feature from DAC.