After introducing the UDM, we need to go more into the details of the Dimensional Modeling in order to understand how to build our UDM.

We are going to talk about two main concepts: Dimensions and Measures.

Dimensions are usually business objects or entities that contain a list of attributes that describe the object (dimension itself) and they are usually derived from some code tables. For example, we may have a product, employee, location or even branch dimension. There are four things to clarify about dimensions.

Dimensions Tables

As I have said before, if you start with a basic code you will end up with the simplest form of dimension. For example, in your HR system you have a table that designates the codes of functions assigned to employees. That very simple function table may contain a function code and description, that table would certainly serve as a dimension table. As we progress, you will see that we can produce dimensions from several related tables.

Dimensions Attributes

Dimensions attributes tables are compared as columns in a table. They are simply used to provide more details about the dimension itself. I will illustrate the idea by the use of an example of an entity table. Suppose that you are working in a bank where we have departments as well as branches. Our example dimension table will have the following attributes:

Dimension Table:      Dimension Entity

Dimension Attributes:

                             Entity ID                           (Primary Key)

                             Entity Description

                             Entity Type                        (Describe whether it is a department/branch)

                             Entity Region                     (Describe Entity Region Like North, South, …)

You should notice that the entity is really categorized using the type and region attributes.

Also, you can see that the attributes can be simply classified as one of the following:

·         Key à      Entity ID (the Primary key of a table sometimes called the logical primary key)

·         Parent à  Entity Type, Entity Region (we should have a type table as well as region table)

·         Regular à Entity Description  (a simple description)

Dimensions Members

They are simply the list of possible values for the dimension itself. If you compare to a table, it would be the values of rows or records inside the table. For Example, taking Lebanon as an example and the region table as example, we would end with the following.

Dimension: Dimension Region

Region Code                      Region Description

1                                     Beirut

2                                     Mount Lebanon

3                                     North

4                                     South

5                                     Bekaa

So as you can see, we have 5 members.

Dimensions Hierarchies

Sometimes, you want to filter data by region first than by entities, so you are talking about merging two dimensions into one. The region dimension would be the higher level and its sub categories would be the entities that fall under these regions.

You can simply join the two dimensions into one using the Entity Region field in the Dimension Entity with the Region Code in Dimension Region. Voila! You have created a new dimension hierarchy.

Dimensions hierarchy allows you to create a drill through on your data. Viewing a total by all regions, then by a specific region and at the end you can see your data by selection a single entity within a specific region.

So dimensions filter your selection, however, there is no reporting unless you are showing values. Measures are simply the data values that you want to report on. For example, you can report on the product sales, transaction count, or even gross sales.

They can be divided into qualitative and quantitative. For example, if you are reporting on the gender or the function of the employees in the fact table of employee, then you are using qualitative since it is a quality of the object.

If you are reporting on the amount of sales generated by a sales person, then we are talking about a quantitative measure (it is mostly the case when dealing with measures).

Measures are most of the time numeric, are mostly additive, can be aggregated and are stored in fact tables.

Figure 1: Sample picture illustrating dimensions and measures. The measure is simply the gross sales amount

The best way to start implementing your dimensions and identifying your fact tables is a question usually raised by the business user. An example of a question would be, "What if I would like to have all customer sales by date and region?" There your dimension is the by clause (Sales date and region in this case). Your fact table then would be the customer sales amount.

We have two design approaches to creating our dimensions. The star and the snow flake schema.

Star Schema

Let us start with the simpler one, the start schema. There are certain rules that apply.

Each dimension is represented by a single dimension table.

Each dimensional table is related to or linked to a fact table (sometimes, in the case of date dimension it may be a good idea to take the attribute from the fact table itself).

You can see in the picture below two examples for a star schema where the fact table would be the employee table and the two dimensions as linked are region as well as the entity.

Figure 2: Sample of creating dimensions with star schema

Please note that as you start adding more dimensions tables to the picture, you are going to arrive at a star shape with the fact table in the middle. That is why the name is start schema. Another interesting fact is that the relation is merely between a master and a detail table (Primary Key (Being a dimension) and a reference key in the fact table).

Snow Flake Schema

Figure 3: Snow Flake representation

I will begin by showing a sample snow flake representation in picture 3 and then explain using example the analogy.

You can see from the picture that at each branch we can have multiple branches. The same case applies to the dimension table as you will see in the analogy presented afterwards.

The other type of dimension creation is the snow flake schema. The snow starts like a star schema. However, the dimensions are represented by a more than one dimensional table. Sometimes, the dimension table is not even related directly to the fact table. In the picture you can see that the employee table is connected to the entity table directly through the entity ID. You can also see that the employee table can be linked directly to the region using the employee region. However, we can still treat the region as a super dimension of the employee by using the entity as an intermediate dimension. In the case of filtering employees by entity type, there is no direct connection as you can see. The only way to go about it is to use the snow flake schema to filter the data through the use of the entity table. The picture shows only a branch of a snow flake schema.

Figure 4: A snow flake schema with one branch

In this article, an overview of dimensions and measures was provided. Some general concepts were introduced. The concepts introduced here are illustrated with a sample picture from Excel (very simplified view). The idea is that you have the capability to filter by region or by sales person and have the sales amount changed. We also showed the two way of creating dimensions schema: Star and Snow Flake. In the next article we will begin the practical implementation of an Analysis Services project, stay tuned and happy BI.