Dylan’s BI Study Notes

My notes about Business Intelligence, Data Warehousing, OLAP, and Master Data Management

BI Apps 7.9.6.1 is released

Posted by Dylan Wan on October 13, 2009

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.

Posted in Business Intelligence | Leave a Comment »

BI Apps 7.9.6 is out

Posted by Dylan Wan on May 15, 2009

Here is the press release.

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

Read the rest of this entry »

Posted in Business Intelligence | Leave a Comment »

Informatica ETL Tuning Steps

Posted by Dylan Wan on March 14, 2009

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.

Read the rest of this entry »

Posted in Business Intelligence, ETL, Infomatica, OBIA | 1 Comment »

Data Security in Oracle BI Apps

Posted by Dylan Wan on July 15, 2008

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

Read the rest of this entry »

Posted in BI, BI Application, BI Work, Business Intelligence, OBIA, OBIEE, Oracle, Oracle BI Suite EE, Siebel Analytics | Tagged: | 1 Comment »

Use ANSI SQL for Outer Join

Posted by Dylan Wan on July 4, 2008

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, Read the rest of this entry »

Posted in BI, BI Application, BI Work, ETL, Infomatica, Oracle, Oracle Data Integrator | Leave a Comment »

Use SQL*Loader with Named Pipe

Posted by Dylan Wan on June 24, 2008

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.

Read the rest of this entry »

Posted in BI, BI Work, Business Intelligence, Data Warehouse, ETL, Infomatica, Oracle, Oracle Data Integrator, Sunopsis | Tagged: , , | 1 Comment »

Hyperion Essbase Dimension Terminology

Posted by Dylan Wan on June 20, 2008

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.

Read the rest of this entry »

Posted in BI, Business Intelligence, Oracle, PeopleSoft, essbase, hyperion | Tagged: , , , | 1 Comment »

Changed Data Capture in ODI

Posted by Dylan Wan on June 9, 2008

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: Read the rest of this entry »

Posted in BI, BI Application, BI Work, Oracle, Oracle Data Integrator, Sunopsis | Tagged: | Leave a Comment »

Oracle BI Applications 7.9.5 is out

Posted by Dylan Wan on May 30, 2008

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.

Read the rest of this entry »

Posted in BI, BI Application, BI Work, Business Intelligence, Data Warehouse, ETL, Infomatica, OBIA, OBIEE, Oracle, Oracle BI Suite EE, PeopleSoft, Siebel Analytics | Tagged: | Leave a Comment »

ABC Analysis in Inventory Management

Posted by Dylan Wan on March 11, 2008

 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. Read the rest of this entry »

Posted in BI, BI Application, BI Work, Business Intelligence, Data Warehouse, EBS, Oracle, PeopleSoft, Supply Chain Intelligence | Leave a Comment »

RFM Analysis

Posted by Dylan Wan on March 9, 2008

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: Read the rest of this entry »

Posted in BI, BI Application, BI Work, Business Intelligence, Marketing Intelligence, Sales Intelligence | Leave a Comment »

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

Posted by Dylan Wan on February 26, 2008

  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.

Posted in BI, Business Intelligence, Data Warehouse | Leave a Comment »

13 Period Calendar

Posted by Dylan Wan on February 26, 2008

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?

Read the rest of this entry »

Posted in BI, BI Application, BI Work, Business Intelligence, Data Warehouse, Financial Intelligence, Sales Intelligence | 2 Comments »

Data Warehouse Basics

Posted by Dylan Wan on February 15, 2008

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.

Read the rest of this entry »

Posted in BI Work, Data Warehouse | 3 Comments »

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

Posted by Dylan Wan on February 15, 2008

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

Read the rest of this entry »

Posted in BI, BI Links, Business Intelligence, OBIEE, Oracle, Oracle BI Suite EE, Siebel Analytics | 2 Comments »

Database Tuning – drop unused indexes

Posted by Dylan Wan on February 8, 2008

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. Read the rest of this entry »

Posted in Data Warehouse | Leave a Comment »

Bitmap Index – when to use it?

Posted by Dylan Wan on February 1, 2008

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?

Read the rest of this entry »

Posted in BI, Business Intelligence, Data Warehouse, Oracle, Siebel Analytics | 3 Comments »

BI Applications and Embedded BI, Part 4

Posted by Dylan Wan on January 29, 2008

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.

Read the rest of this entry »

Posted in BI, BI Application, BI Work, Business Intelligence, EBS, OBIA, OBIEE, Oracle, Oracle BI Suite EE, Siebel Analytics | 2 Comments »

BI Applications and Embedded BI, Part 3

Posted by Dylan Wan on January 28, 2008

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.

Read the rest of this entry »

Posted in BI, BI Application, BI Work, DAC, Data Warehouse, OBIA, OBIEE, Oracle, Oracle BI Suite EE | 1 Comment »