Classic Star Schema as Data Model of Data Warehouse

A Star Schema is a multi dimensional data model that organizing information or data in a data warehouse. It is based on a central fact table surrounded by several dimensional tables in shape of a star. Using star schema, we can view business information from many perspectives. SAP calls this schema as Classic Star Schema to differentiate with SAP BW data model developed by SAP.

Fact Table

Fact is the focus of the analysis of business activity (sales value or quantity, for example). It is a measure that is normally (cumulative) and answer the question “how much?” or “how many?”

The fact data are stored in a highly normalized fact table. In a star schema, typically the fact table is very large with small dimensional tables. The fact tables has a relatively small number of columns (attributes) and a large number of rows (records) where associated dimension tables to have a large number of columns (attributes) and small number of rows.

Dimension tables

A dimension is a textual description of the dimension or feature of business. The dimension answer the questions “who? what? when? where?”

Dimension data are stored in dimension table. It link to the fact table has a group of similar characteristics. For example, a customer dimension table may contain three characteristics: customer name, address and sales organization. There will be one customer dimension record for each unique combination of these three values.  For example, each record in customer dimension may represent a specific customer.

The graphic below show how the dimension tables and fact table are arranged in a star formation. It also shows the connection between the denormalized dimensions tables and the highly normalized fact table.

Classic star schema as data model of data warehouse has some disadvantages (I will explain it next time). To Response the problems with classic star schema, SAP refined this data model and result SAP BW Star Schema.

You can follow any responses to this entry through the RSS 2.0 feed. You can leave a response, or trackback from your own site.