Dimensions Modelling
Dimensions
Dimensions are object or business entities in your database are a group of related attributes about that object. So they are based on the tables that you have already defined in your database for example you may have a calls table in your database that describes all the companies calls therefore you could create a calls dimension based of that table, some other common dimensions you may find is an employee dimension, location dimension or even a product dimension. Dimensions are represented with dimension tables, usually a dimension can be represented with one dimension table but sometimes you can have multiple dimensional tables to represent a single dimension. And will be looked at in the article.
A dimension describes an object such as a product and additional information is required about that product such as colour and cost and these are called dimension attributes. As a table in your database has columns that describe each record in that table dimensions have attributes. So the dimension attributes for the product dimension would be product colour, cost and may be category and sub category, attributes help describe the items in that dimension and any column in the dimension can be promoted as a dimension attribute. Now these dimension attributes themselves have interesting characteristics each attribute can be either a regular attribute a parent attribute or a key attribute. We have already said that the columns in your dimensions table are the dimension attributes so one of these columns or attributes needs to be designated as the Key or the primary key for that dimension. When we’re creating our unified dimensional model schema this attribute is called the logical primary key, it’s the logical primary key remember because the unified dimensional model is a logical data model that represent your underlying data source.
Dimension Members
Dimensions also have members that are the values of that dimension so just like your product table has records for each product A, B, or C, using the dimensional model you would called those records dimension members. So the product dimension has dimension members that represent each member of that dimension product A, B or C. You can think of the dimension members as the actual records or rows that exist in the dimension tables.
Dimension Review
- Dimensions are represented by a dimension table.
- Dimensions have dimension attributes which are the columns in the dimension table.
- Dimension attributes are one of the following a key, parent or regular attribute.
- Dimension members are the records in the dimension table.
You will need to be comfortable with these concepts to get started with analysis services and will continue to build on these concepts throughout the series.
Dimension Hierarchies
Dimensions can also have hierarchies and are defined for the drill down functionalities, you define your hierarchies to let your report data be viewed at multiple levels, you provide a drill down of sales information by all products then product category, the product subcategory then the individual product those are called hierarchies and there are two type within analysis services.
The first are attribute hierarchies are based off the attributes defined for a dimensions, so each attribute dimension is automatically considered an attribute hierarchy. The next are user defined hierarchies and they must be created or specified manually using those same dimension attributes.
Attribute hierarchies
Attribute hierarchies are based off the attributes in a dimension table so remember dimension attributes are just the columns in your dimension. Any one of the attributes can be an attribute hierarchy, so look at the product dimension and the attributes for the product dimension.
We know the dimension attributes are the columns in the dimension table the ProductID, Name, Size and Colour any of these can become an attribute hierarchy. Let’s take size for example there could be three product sizes that describe all products sizes small medium and large we could prove product sales regardless of product size and we can see the sales number for all products or we could create an attribute hierarchy and drill into the sales of each product by that hierarchy so we can sales by small medium or large depending on that size. Remember these attributes are defined by default by analysis services and each one of them can exist for any of your dimension attributes.
User defined hierarchies
Next you have what is called user defined hierarchies now these are not created by default but rather can be defined by you using the dimension attributes. This allows you to create your own hierarchies that aren’t represented by the attributes dimension. For instance a good example using the adventureworks database is the product category hierarchy, you can essentially create a hierarchy for the dimension attributes using other dimension attributes so you can see below.
I have taken the category attribute out and then defined other attributes that are included in that user defined hierarchy and you can see the subcategory and the productid, those are all different dimension attributes that are included now in one user defined attribute. Now I can view the sales information based on either all categories or then drill down into subcategories or even a particular ProductID.
Measures
Measures are the data values you want to report on so product sales, transaction count or gross sales. Measures are almost always numeric and as such can be aggregated or totalled based on your dimensions for example we mentioned viewing product sales by product category while the product sales number is your measure and the product category is your dimension, the product sales number can be aggregated across that dimension so if your viewing product sales for all products the total would be added for all products whereas if you were just viewing the product sales for a particular product category the product sales would be totalled for just that product category. So measures that can be aggregated across all dimensions are called additive measures, some measures however are either semi additive and those are measures that can’t be aggregated across all dimensions and some are actually non-additive that can’t be aggregated at all by any dimensions.
It’s important to note within your dimensional model measures are stored in fact tables, so with analysis services you can and will probably have more than one fact table in your unified dimensional model. The fact tables are then related to your dimension table which is what allows you to drill down by dimensions to your aggregated measures.
If you’ve ever worked with excel pivot tables then you already understand many of the key concepts above, now excel doesn’t call them dimensions and facts that’s what they are.
Conclusion
- We’ll continue to discuss dimensions and measures through the series while introducing additional concepts as we begin to work more closely with analysis services.
- Next article we will be put fact tables and dimension tables together and talk about the two types of schemas used for dimensional modelling, the star and the snowflake schemas.