Creating dimensions in SSAS 2005 and 2008 -- differences

Click Next using the first option with the auto build ticked. The next screen shows the default data source view specified by the project.

In the next screen (dimension type screen) it allows you to choose from the following:

• Standard dimension
• Time dimension
• Server time dimension

If you choose server time and click next -- this will allow you to choose the first calendar day and last calendar day, first day of the week and also the time periods (year, half year, quarter, month, week, date etc)

If we go back and choose a standard dimension for our purpose and click next, the main dimension table screen appears -- choose the main dimension table (eg: dim_region) and choose the Key Columns (dim_region_key) .

Also choose the Column containing the member name.  This is optional but choosing this will help users get more meaningful data.  This is because usually Key columns represent some kind of Id values and does not mean anything to the user.  But if you choose meaningful attributes like full Name the user will be seeing the Region name instead of the Key column value.

Click next after choosing the member column name.

In the next screen select dimension attributes, the wizard automatically selects all the attributes for both the tables.  But you can unselect some of the attributes based on your requirement.

For our dimregion dimension we can choose the attributes that represent the region such as the Region Manager.   Click next

In the specify dimension Type screen -- Usually Regular is chosen

Define Parent-Child Relationship -- This is mainly used if you have a hierarchical structure for example an employee reporting to a manager.  Click next without choosing anything for our example.

In the detecting hierarchies screen -- click next if you agree with the hierarchies that the wizard finds. 

2008 dimension wizard is simpler than the 2005 dimension wizard

• The wizard will only add the primary keys as attributes by default
• It can build a dimension from a table, create a time dimension table in the source, create a time dimension without a table (server type dimension) or create a non-time table.

Creating a dimension in SSAS 2008

The main differences are as follows:

 When you right click on the Dimension in the Solution Explorer and click New dimension, the Dimension wizard appears with the same Welcome screen.  When you click next, the screen now shows 4 options to build the dimension instead of just 2 as seen in SSAS 2005 (screenshot below)

·         Use an existing table

·         Generate a time table in the data source (Should have write permissions to do this)

·         Generate a time table on the server

·         Generate a non time table in the data source Template  (there are some existing templates)

For the example purpose I am choosing the existing table option. Click Next

Choose the Main Table – DimRegion Key Columns and also Choose the Name Column (Region Name)

Click Next.  In the next screen select Related Tables.  Click Next.  Choose the Dimension Attributes or select all and Click Next as shown below.

This screen is a bit different from the SSAS 2005 screen in that it does not show the Member name column that we have chosen (Region Name) as we have chosen that column to be the name.

Click Next after selecting the attribute names (if you want to change from what was already selected). 

Here the wizard goes directly to the last screen as below

The wizard does not go into the steps of – Parent Child, dimension types, Hierarchies as in 2005.

You can now name the dimension and click finish. This creates the dimension.

Click Next and the wizard will take you to the last screen.  You can now name the dimension and complete the wizard.