SQL Server Analysis Services - Dimensional Modeling Concepts
page 2 of 5
by Nidal Arabi
Average Rating: This article has not yet been rated.
Views (Total / Last 10 Days): 27364/ 58


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.

View Entire Article

User Comments

No comments posted yet.

Community Advice: ASP | SQL | XML | Regular Expressions | Windows

©Copyright 1998-2023 ASPAlliance.com  |  Page Processed at 2023-09-25 6:55:16 PM  AspAlliance Recent Articles RSS Feed
About ASPAlliance | Newsgroups | Advertise | Authors | Email Lists | Feedback | Link To Us | Privacy | Search