Learn more about SQL Server tools

mssqltips logo
 

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Whitepapers          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

 

Creating a Dimension



By:

Overview

Dimensions are of two types: database dimension and cube dimension. The dimensions that are defined at the solution level can be termed as a database dimension and the ones defined inside the cube are termed as a cube dimension. Dimension Wizard is the primary means of creating a dimension. We will create a dimension using the three dimension tables which we have included in our schema.

Explanation

Right-click the Dimensions folder and select “New Dimension”, this will invoke the Dimension Wizard. The first screen should look like the below screenshot. You have the options of using an existing table, creating a table in the data source and using a template. We already have the dimension table in our schema and we will use this, so select “Use an existing table” and click “Next”.



Select the DSV we created earlier in the DSV selection. We intend to create a dimension from the DimSalesTerritory table, so select the same table. Every dimension table needs to have a key attribute, and in this table SaleTerritoryKey is the primary key column which is guaranteed to identify each record uniquely. It would not make sense to browse this attribute using the Key, instead SalesTerritoryRegion field has unique values. We can also use this field as the key as well as name column. But for the purpose of our exercise, we will use the SaleTerritoryKey field as the key column and SalesTerritoryRegion as the name column. Though it looks inappropriate to use the key field, but when you are starting to develop an understanding of dimensions, this will help to set a rule in your mind that the key field is always required, mostly a surrogate key and you can set a name column to any field to facilitate a convenient browsing mechanism.



In the next screen, you need to make a selection of the attributes that will be present in the dimension. If you uncheck the “Enable Browsing” button, they won’t be visible to client applications when they browse the dimension. Attributes can be of different types and you can specify the type in the Attribute Type field. The Dimension Wizard removes the Name column you set from the key column as that is available due to the key column. So you won’t find that field in this list of available attributes.



Now the next step is to give a name to the dimension, name it “Cube Dim Sales Territory” or anything appropriate. After this step you have completed creating your first dimension.



In a similar manner create Product and Date dimension using the Dimension Wizard.


Last Update: 5/3/2011




More SQL Server Solutions











Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Email me updates 


Signup for our newsletter
 I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.



    



Learn more about SQL Server tools