As described in Star and snowflake schemas, a star schema has a fact table at the center and one or more dimension tables joined to the fact table, and a snowflake schema is an extension of a star schema such that one or more dimensions are defined by multiple tables. A cube model that is based on a simple star schema is built around a central facts object. The facts object contains a set of measures that describe how to aggregate data from the fact table across dimensions. Measures describe data calculations from columns in a relational table and are joined to create the facts object. Figure 1 shows how measures and a facts object relate to relational data.
Dimensions are connected to the facts object in a cube model like the dimension tables are connected to the fact table in a star schema. Columns of data from relational tables are represented by attributes that are organized to make a dimension.
Figure 2 shows how dimensions are built from relational tables. Hierarchies store information about how the levels within a dimension are related to each other and are structured. A hierarchy provides a way to calculate and navigate across the dimension. Each dimension has a corresponding hierarchy that contains levels with sets of related attributes. In a cube model, each dimension can have multiple hierarchies.
All of the dimensions are connected to a facts object in a cube model based on a star schema or snowflake schema. Joins can connect tables to create a facts object or a dimension. In a cube model, joins can connect facts objects to dimensions. The dimensions reference their corresponding hierarchies, levels, attributes, and related joins. Facts objects reference their measures, attributes, and related joins. Figure 3 shows how the metadata objects fit together in a cube model and map to a relational snowflake schema.
You can reuse the components of a cube model to create more precise cubes for specific applications. A cube is the most precise metadata object and is the closest object to an OLAP conceptual cube. A cube is a specific instance or subset of a cube model. A cube has a specific set of similar but more restrictive metadata objects derived from the parent cube model, including cube dimensions, cube hierarchies, cube levels, and a cube facts object. A cube can have only one cube hierarchy defined for each cube dimension, but a dimension can have many hierarchies that are defined for the cube model. Because of this structural difference between a cube and a cube model, you can retrieve most cubes with a single SQL statement.