Star Schema v/s OLAP Cube

In the first couple of weeks, we have recognized the importance of dimensional modeling in the design of a data warehouse. Dimensional model simplifies the database and allows the BI software to present the data to end-users in a meaningful and efficient way. Dimensional model can be implemented using Star schemas or OLAP cubes. 

Star Schema:

A star schema is the simplest form of dimensional modeling which consists of one or more fact tables in the center surrounded by dimension tables (providing a context to the facts). This structure resembles a star and hence the name ‘Star Schema’. These star schemas are implemented on a relational database management system.

OLAP Cube:

An OLAP (Online Analytical Processing) cube is multi-dimensional and consists of aggregated and pre-calculated summary data. As a result, an OLAP cube provides superior performance and enables the business users to slice and dice along the various dimensions, drill up and drill down through hierarchies.

Slice-and-Dice operation

By slicing across the product dimension in the cube, the product manager will be able to analyze the sale of a product while the store manager can observe the sales for a particular store by slicing across the location dimension.

A “slice” means a cut across the cube and “dice” means a small cube which is produced by the intersection of slices.

Drill-up and Drill-down operation

To analyze the data at different levels, the user is provided with three hierarchies:

• Time Hierarchy
• Product Hierarchy
• Location Hierarchy

 

In this scenario, the manager will be able examine the data for different product types in various cities of a particular state for all the quarters in a particular year. This can be possible through the drill-up and drill-down functionality in multidimensional cubes.

Both star schemas and OLAP cubes offer their own benefits and shortcomings. Therefore, based on a good understanding of requirements, an efficient star schema or/and an OLAP cube can be designed to answer difficult business questions!

More to follow in my next blog. Stay tuned!

References:

• The Data Warehouse Toolkit, Ralph Kimball and Margy Ross
• Database Systems – Design, Implementation and Management, Peter Rob and Carlos Coronel
• Wikipedia