Adding Functionality to Your Cube - Getting Started with SQL Server 2012 Cube Development - page 157

Adding dimensions to the FirstCube

project

A dimension in Analysis Services is a group of characteristics that describes a fact
row. Previously, when working with the FirstCube project in this topic, you have
worked with dimensions such as date and product. These have all been dimensions
with a direct relationship to the fact table. However, several other types of dimensions
can be used in Analysis Services. In this chapter, we will add dimensions of different
kinds to the cube.

Adding referenced dimensions

Sometimes, you do not have a direct relationship between the fact table and a
dimension. An example of this is the Geography dimension. There is no Geo-
graphyKey column in the FactResellerSales table, instead, this column exists in
the DimReseller table. Analysis Services solves this using something called a ref-
erenced dimension . To create a referenced dimension, perform the following steps:

1. Right-click on the Dimension folder in the Solution Explorer and choose to
add a new dimension.
2. Select DimGeography as the main table and click on Next .
3. Deselect the DimSalesTerritory table and click on Next .
4. Add the following attributes:

• Geography Key
• City
• State Province Code
• Country Region Code

5. Click on Next ; change the name to Geography , and then click on Finish .
6. Right-click on the Country Region Code attribute and click on Properties .
Add EnglishCountryRegionName as the value for NameColumn .
7. Perform the same for the State Province Code attribute, but in this case, add
the StateProvinceName as the the value for NameColumn .
8. In the Solution Explorer , double-click on the Adventure Works

DW2012.cube cube. Click on the Dimension Usage tab.