Implementation of Dimensional Model
there are two options to implement the dimension model to see how it gets physically represented in the database.
- relational implementation - is implemented in the database with tables and foreign keys
- multidimensional implementation - requires a special feature in a database that allows defining cubes directly as objects in the database
Relational implementation (star schema)
the ERD of relational data warehousing would be shaped somewhat like a star, and thus the term star schema is used to refer this kind of implementation.
The main table in the middle is referred to as the fact table because it holds the facts, or measures.This represents the cube. The tables surrounding the fact table are known as dimension tables. These are the dimensions of our cube.
Oracle Warehouse - Multidimensional Implementation (OLAP)
A multidimensional implementation or OLAP (online analytic or analytical processing) requires a databasewith special features that allow it to store cubes as actual objects in the database, and not just tables that are used to represent a cube and dimensions. It also provides advanced calculation and analytic content built into the database to facilitate advanced analytic querying. Oracle's Essbase product is one such database and was originally developed by Hyperion. Oracle recently acquired Hyperion, and is now promoting Essbase as a tool for custom analytics and enterprise performance management applications. The Oracle Database Enterprise Edition has an additional feature that can be licensed called OLAP that embeds a full-featured OLAP server directly in an Oracle database.
Oracle Warehouse - Designing the Cube
A dimensional model is naturally able to handle this concept of the different levels of data by being able to model a hierarchy within a dimension. The time/date dimension is an easy example of using of various levels. Add up the daily totals to get the totals for the month, and add up 12 monthly totals to get the yearly sales.
The time/date dimension just needs to store a value to indicate the day, month, and year to be able to provide a view of the data at each of those levels. Combining various levels together then defines a hierarchy