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.
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 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
Entity ID (Primary
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)
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
2 Mount Lebanon
So as you can see, we have 5 members.
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.
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.