The next 6 tutorials explains Building Dimensions using Dimensions Editor. Dimensional modeling is the conceptualization and visualization of numerical data models as a set of measures that are derived from the common parameters used in a business. It summarizes and rearranges data and presents views of data to support data analysis. Dimensional modeling focuses on data such as counts, weights, balances and occurrences.

Understanding Dimensions Basics

Dimensional modeling revolves around facts and dimensions. These are the basic components of cube structures in Analysis services. Facts are collections of data that are related and consists of measures and context data. Each fact table represents a business item, a business transaction or an event that can be used in the analysis of a business. For instance the revenue of a store relates to total revenue and total profits. These would be facts. Facts are core tables in OLAP database. The data in these tables are usually raw data.

Analysis of facts is conducted across a collection of properties. These properties are known as dimensions. Using these dimensions we can view facts in different contexts and perspectives. For instance the sales fact can be examined with reference to State, city or region. These would be members of the geographical dimension. Every point in the fact table is associated with only one member of the multiple dimension in the dimensional model. In the example below the value in a cell is associated with unique dimensions. A time dimension, a product dimension and a place dimension.

Types of Dimensions

A shared dimension is a dimension stored in the library and can be accessed by multiple cubes in the database.

A private dimension is a dimension that can be used only in cubes in which they are defined. A virtual dimension is created from a member property. It can be browsed like a regular dimension but has no aggregations calculated for it. A member property associates additional information with the members in one of the levels of the dimension.

A time dimension is a specialized dimension used to represent standard time. All other dimensions are called standard dimensions

Dimension members: Dimension members are values of the elements in the dimensions. The members are organized in levels within a dimension. For instance the time dimension consists of several levels. Day, week, months, year are levels in the time dimension. The members of the day would be Monday, Tuesday, Wednesday…and so on. When these concepts are combined with fact table records, it is clear that the dimension member determines the position in the dimension with which the record in the fact table is associated.

Dimension Hierarchies: The members of a dimension can be arranged in one or more hierarchies. Each hierarchy can have multiple levels. A member of a dimension may be located on more than one hierarchy structure. In the time dimension the year member may be linked to quarters represented by three months each or to two halves of a year and so on.

Time dimension is a good example of multiple hierarchies in a dimension