Designing OLAP Data Cubes
OLAP Data cubes represent the primary logical object for providing critical, multidimensional analysis common to business intelligence which may be viewed using a variety of tools such as BIDS or Excel.
Cubes contain data from two types of tables – a fact or “measure” table (typically one) and multiple dimension tables.
Measures contain data to be analyzed, such as amounts from sales and dimensions illuminate the facts.
For example, to report on measures within the fact table FactCallCenter, the dimension DimDate is leveraged as evidenced by its PK-FK relationship (DateKey-FactCallCenterID).
Therefore, dimensions are used to provide context to facts/measures when displayed to the user.
In a normal data cube, a single fact table is supported by multiple dimensions – represented by a star schema as described in OLAP Schemas.
The cube’s design should reflect current requirements, modeled and tested in a tool (Erwin, Visio, etc.), and accepted by stakeholders.