Dimension
Overview
A dimension is a key component of a SQL Server Analysis Services (SSAS) database. In the [[articles:Unified Dimensional Model]] that is the data model of an Analysis Services database, dimensions describe the data, and users browse, filter, and aggregate data by manipulating dimensions.
Understanding Dimensions
A dimension describes a business transaction. A business transaction may be described by many dimensions. For example, we can describe a retail sales transaction by the following dimensions:
- Time (when the sale occurred)
- Product (what was sold)
- Customer (who bought it)
- Reseller (who sold it)
- Geography (where the sale occurred)
Dimensions contain members, which are specific instances of the dimension. For example, a specific customer is a member of the Customer dimension, and a specific product is a member of the Product dimension. All members of a dimension share common characteristics; these are attributes of the dimension. Attributes of a Customer dimension might be the customer's name, age, gender, and income. Attributes have a finite set of possible values; the possible values are members of the attribute. A "Gender" attribute, for example, has the members "Male" and "Female."
Users use attributes to query the data in an Analysis Services database. They choose the attribute members they want, and Analysis Services returns only data related to dimension members that have the chosen attribute values. For example, if a user chose the year 2010 of the Time dimension's Year attribute, Analysis Services would return only data about sales from the year 2010. This is like executing a query with a filter in a relational database, with one key difference: in a relational database, when a query filters data, the database returns each row meeting the filter's criteria, but in an OLAP database, the rows meeting the filter's criteria are grouped together into an aggregate row. This distinction is a value of OLAP solutions.
Users looking at aggregated results from an OLAP query usually want to understand what values make up the aggregate, so a common task in Analysis Services is to "drill down" into a value: to look at the component parts that make up an aggregate value. In a Time dimension, for example, a user might begin looking at data aggregated by Year, but then they drill down to Quarter, and then Month, to see the data aggregated at those levels. Dimensions contain hierarchies that assist users in drilling down through the dimension's attributes.
Users use hierarchies to navigate dimension data. A dimension can contain many hierarchies. Each hierarchy contains one or more *levels *built from the dimension's attributes. Consider a dimension called "Product" whose members are products a business sells. Businesses often categorize their products, so the Product dimension contains attributes related to categorization, such as Category and Subcategory, and a Product attribute that identifies individual products. Users often want to look at data aggregated by Category, and then drill down into Subcategory and then individual Products. An OLAP database developer builds a hierarchy from the Category, Subcategory, and Product attributes, with Category as the top level, Subcategory as the intermediate level, and Product as the lowest level; this hierarchy helps users drill down into the dimension's data.
The image below shows the components of a dimension. The dimension is called Product and contains several attributes. It also contains a hierarchy called Product Categories, that has two levels: Category, and Subcategory. OLAP developers build a hierarchy's levels from attributes, so the level contains the attribute's members; in the image, the Category level is built from the Category attribute and contains the Category attribute's members: Accessories, Bikes, Clothing and Components.
See Also
Other Languages
This article is also available in the following languages: