The basic concepts of OLAP include:
- Cube
- Dimension table
- Dimension
- Level
- Fact table
- Measure
- Schema
Cube
The basic unit of storage and analysis in Analysis Services is the cube. A cube
is a collection of data that’s been aggregated to allow queries to return data quickly.
For example, a cube of order data might be aggregated by time period and by title,
making the cube fast when you ask questions concerning orders by week or orders
by title.
Cubes are ordered into dimensions and measures. Dimensions come from dimension tables,
while measures come from fact tables.
Dimension table
A dimension table contains hierarchical data by which you’d like to summarize. Examples
would be an Orders table, that you might group by year, month, week, and day of
receipt, or a Books table that you might want to group by genre and title.
Dimension
Each cube has one or more dimensions, each based on one or more dimension tables.
A dimension represents a category for analyzing business data: time or category
in the examples above. Typically, a dimension has a natural hierarchy so that lower
results can be “rolled up” into higher results. For example, in a geographical level
you might have city totals aggregated into state totals, or state totals into country
totals.
Level
Each type of summary that can be retrieved from a single dimension is called a level.
For example, you can speak of a week level or a month level in a time dimension.
Fact table
A fact table contains the basic information that you wish to summarize. This might
be order detail information, payroll records, drug effectiveness information, or
anything else that’s amenable to summing and averaging. Any table that you’ve used
with a Sum or Avg function in a totals query is a good bet to be a fact table.
Measure
Every cube will contain one or more measures, each based on a column in a fact table
that you’d like to analyze. In the cube of book order information, for example,
the measures would be things such as unit sales and profit.
Schema
Fact tables and dimension tables are related, which is hardly surprising, given
that you use the dimension tables to group information from the fact table. The
relations within a cube form a schema. There are two basic OLAP schemas: star and
snowflake. In a star schema, every dimension table is related directly to the fact
table. In a snowflake schema, some dimension tables are related indirectly to the
fact table. For example, if your cube includes OrderDetails as a fact table, with
Customers and Orders as dimension tables, and Customers is related to Orders, which
in turn is related to OrderDetails, then you’re dealing with a snowflake schema.