working on applications and technology

Data model for business intelligence

The most important component of business intelligence is the concept of 'data model'. data model determinde what kind of analysis that end user could do with the data. It is also an independant concept that could span across different vendor 's product.

Unlike data model of the transaction system, the typical analytic data model is often denormalized and store extra data for analytic query and better query performance while transaction data model use is often normalized and optimized for a few data read and write, which is implemented by jointing many tables.

Star Schema and dimension

The most common used analytic data model in business intelligence is call the 'Star schema' data model.

For example, for the above sales data analysis scenario, We will place the sales transaction record in the center table which is also called the sales 'Fact' table. The Fact table usually contain numeric value (e.g. sales revenue, units shipped etc). And we will add dimension table to the Fact table. The dimension table is often contain data that describe the sales facts(e.g. the date of the sales, the city the sales happen etc). each transaction record in the Fact table will be joint to all the dimension table that provide more detail for the transaction.

Although dimension table could be normal relational table, the data stored in the dimension table is often redundant and organized by hierarchy. and here 's an exmple of the time dimension which have a month-quarter-year hierarchy.

These kind of hierachy is important in business intelligence cause it is the basic of the business intelligence 's 'drill down' or 'roll up' operations. and here 's an simple example for data analysis of sales revenue over the date dimension:

'The first interactive business intelligence report describe the sales revenue for 2009 is 10000'

Then We could just click the year column, and the year collaspe into sales revenue by quarter

This is where the dimension hierarchy takes effect.

With the star schema and dimension data model, we could answer question like

'Quartly sales for each city'

'Compare sales revenue with last year'

...

OBIEE - data model in logical layer

With the Oracle business intelligence system, We could define star schema and dimension data model in the 'logical layer' in the admin tools. The data model in OBIEE could span different data source in physical layer, which means OBIEE have the capability of building one logical data model which is actually connect to different database in the backend, We could also define dimension table and their hierarchy in the logical layer. Logical layer is where We  spent most of our time doing data modeling!