July 1, 2008

Why do we not report by 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 Calednar

In this posting, 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 A>

February 15, 2008

Data Warehouse Basics

My Data Warehouse Presentation.

January 28, 2008

Essbase and IBM DB2

I read an interesting article, IBM DB2 Minus OLAP from the SQL Server magazine. Essbase used to be OEM-ed and re-branded by IBM as IBM DB2 OLAP server for ten years. The relationship stopped two yeas ago.


Many DB2 customers actually built their custom analytics applications on the top of Essbase.


December 5, 2007

Oracle BI Applications and Embedded BI, Part II

This is a topic I wrote in six month ago. In the Part I of this series of articles, I mentioned that a warehouse like architecture is required in a heterogeneous environment. I want to elaborate more about this. In the future posts, I will also describe the integration technology I learned for supporting the embedded BI.

Read the rest of this entry >>

November 30, 2007

Key Roles involved in a BI Data Warehouse Project

To develop or deploy a BI solution for your organizations, you need to have the right people involved in the time time. Here are typical roles involved in a BI data warehouse project.

  • Project Sponsor
  • Project Manager
  • Functional Analyst
  • SME
  • BI Architect
  • ETL Developers
  • DBA

The job description and responsibilities are listed in this table: Read the rest of this entry >>

DSS and BI

I found a very old book, called Decision Support Systems: An Organizational Perspective, in a library last weekend. It was written by Peter Keen, an author of several popular books, which help many business managers and users understand the value of information technology. His DSS book draw my attention because he is also the author of my textbook Network in Actions.

More...The DSS book uses a very typical and conventional categorization system which puts the IT systems into three types:

Transactional System, Structure Decision system, and Decision Support System.

These categories are created based on the classification of decisions into structured, unstructured, and partially structured decision. His focus is the 3rd category, DSS. Peter believes that a DSS should assist in solving the semi-structured problems. A DSS should support, not replace, the managers.

I feel that the above is a very good framework to view the role of an analytics apps. A BI analytics application should be a DSS solution. However, BI analytics apps can do much more then just a decision support system. BI may help the structured decision making.

BI is not just a collection of reports. The design of a BI analytics apps needs to consider what are the business decision need to make and what kind of information is helpful for making the decision.

November 2, 2007

Data Warehouse Project Lifecycle

Here is the typical lifecycle for data warehouse deployment project:

0. Project Scoping and Planning

Project Triangle - Scope, Time and Resource.

  • Determine the scope of the project - what you would like to accomplish? This can be defined by questions to be answered. The number of logical star and number of the OLTP sources
  • Time - What is the target date for the system to be available to the users
  • Resource - What is our budget? What is the role and profile requirement of the resources needed to make this happen.

1. Requirement

  • What are the business questions? How does the answers of these questions can change the business decision or trigger actions.
  • What are the role of the users? How often do they use the system? Do they do any interactive reporting or just view the defined reports in guided navigation?
  • How do you measure? What are the metrics?

2. Front-End Design

  • The front end design needs for both interactive analysis and the designed analytics workflow.
  • How does the user interact with the system?
  • What are their analysis process?

3. Warehouse Schema Design

  • Dimensional modeling - define the dimensions and fact and define the grain of each star schema.
  • Define the physical schema - depending on the technology decision. If you use the relational tecknology, design the database tables

4. OLTP to data warehouse mapping

  • Logical mapping - table to table and column to column mapping. Also define the transformation rules
  • You may need to perform OLTP data profiling. How often the data changes? What are the data distribution?
  • ETL Design -include data staging and the detail ETL process flow.

5. Implementation

  • Create the warehouse and ETL staging schema
  • Develop the ETL programs
  • Create the logical to physical mapping in the repository
  • Build the end user dashboard and reports

6. Deployment

  • Install the Analytics reporting and the ETL tools.
  • Specific Setup and Configuration for OLTP, ETL, and data warehouse.
  • Sizing of the system and database
  • Performance Tuning and Optimization

7. Management and Maintenance of the system

  • Ongoing support of the end-users, including security, training, and enhancing the system.
  • You need to monitor the growth of the data.

PeopleSoft EPM

PeopleSoft Enterprise Performance Management is the pre-packaged BI
applications for PeopleSoft application.  It has been in market for
nearly 10 year.  I recently learned its history, product coverage and
architecture from my colleagues. Read the rest of this entry >>

October 22, 2007

UDML in Oracle BI Server

I recently learned a way to retrieve and manipulate the Oracle BI
repository file without using the Admin Tool. Although they are not
officially documented and supported, they are extensively used by many
savvy users. You can see the following screen shot to get the idea on
how to retrieve the repository definition in a text format.

Read the rest of this entry >>