A dimension is a key component of a SQL Server Analysis Services (SSAS) database. In the 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.
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:
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.
This article is also available in the following languages:
yottun8 edited Revision 9. Comment: Removed "(en-US)" in the title.
Fernando Lugão Veltem edited Revision 6. Comment: added portuguese version
Matt Burr MSFT edited Revision 2. Comment: I think I'm done with edits on this for now. Feel free to add or adjust. I may revisit this later after I've worked on documenting other parts of the UDM. I don't think this article should be too technical; it should present the components of a dimension as a user will experience them, so I don't think we need to get into a discussion of attribute relationships or the distinction between "natural" and "unnatural" hierarchies. If we do want to discuss those topics, I think they should be moved into separate articles branched from this one.
Matt Burr MSFT edited Revision 1. Comment: Simplifying the language. Still more to do.
Matt Burr MSFT edited Original. Comment: Expanded the article a bit with information on dimension hierarchies. I intend to add a bit more, but I've covered the primary elements of a dimension as a user would experience them.