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
