About Dimension -- OLAP

A dimension in SSAS references a dimension from the Service Manager data warehouse. In System Center 2012 – Service Manager, a dimension is roughly equivalent to a management pack class. Each management pack class has a list of properties, while each dimension contains a list of attributes, with each attribute mapping to one property in a class. Dimensions allow the filtering, grouping, and labeling of data.

For example, you can filter computers by the installed operating system and group people into categories by gender or age. The data can then be presented in a format where the data is categorized naturally into these hierarchies and categories to allow a more in-depth analysis. 

Dimensions may also have natural hierarchies to allow users to “drill down” to more detailed levels of detail. For instance, the Date dimension has a hierarchy that can be drilled down by Year, then Quarter, then Month, then Week, and then Day.

The following illustration shows an OLAP cube that contains the Date, Region, and Product dimensions

Creating Dimensions  using  Dimensions  Wizard

    Use the Dimension Wizard in Business Intelligence Development Studio to create a dimension in Microsoft SQL Server Analysis Services. The Dimension Wizard guides you through the steps to specify the structure of a dimension. 

Note: You can also use the Cube Wizard to quickly create all the dimensions in a cube at the same time that you create the cube.

Create a Data Source

The first step in working with an SSAS project is to create a data source. I use the AdventureWorksDW database, one of the sample databases available for SQL Server 2005 and SQL Server 2008. Next, you need to create a data source view (DSV)

A DSV is a logical representation of a schema and includes tables or views from one or more databases, queries that act like views but only exist in the DSV, and more.

For this example, I’ll add the following tables to the DSV: Dim Product  Dim Product Category, Dim- Product Sub Category  DimTime, and FactInternetSales. This makes a very simple snowflake schema that will have two dimensions: Time and Product.

At this point, you have a choice: You can run either the cube wizard or the dimension wizard. The cube wizard creates one or more of the dimensions if they don’t already exist, then proceeds to create the cube. The dimension wizard walks you through the process of creating dimensions one at a time, and of course doesn't create cubes. To better explain the process and show what’s being created, I use the dimension wizard to create the Product and Time dimensions.  

Create the Product Dimension

Right-click the Dimensions folder in the Solution Explorer and choose New Dimension to launch the dimension wizard. The first page enables users to build a dimension with or without a data source. Normally you build a dimension with a data source, and when you select this option, you’ll see a check box for automatically building attributes and hierarchies (although this can be changed to create just attributes.) Accept the defaults and click the Next button to advance the wizard to the page for selecting a DSV. This project has only one DSV, so you simply click Next.

The wizard then asks you to select the dimension type: Standard, Time, or Server Time. The Time dimension option adds an extra step, which I’ll cover in a moment. The Server Time dimension creates a dimension table based on a start date, end date, and a selection of levels. Standard dimensions, to the wizard, are anything that isn’t a time dimension. Most cubes will have a Time dimension and several standard dimensions, as is the case here. Select the Standard dimension option and click Next.

Choose the main dimension table. After you select the table, the columns are listed and the key column, if it can be determined, is checked. Here you can change the key column as necessary. By changing the column name, the actual value will continue to be the key, but the user will see a more familiar, descriptive value.

After you click Next, you’ll see a screen verifying related tables. This screen appears only when the dimension is made up of multiple tables, as is the case with a snowflake schema.

Click Next to advance to the Select Dimension Attributes page. This page lists all the columns in the table(s) making up the dimension. In SSAS, each column becomes an attribute and can be used for analysis independently from any other attribute. For products, this means that users can analyze by such attributes as size, color, and weight, without the need to create separate dimensions. The ability to analyze by any attribute is extremely powerful but can be confusing for end users faced with dozens of attributes. The cube developer can remove attributes at this stage and also hide attributes in the dimension after the dimension has been created.

The next screen in the dimension wizard asks for a dimension type; most dimensions will work fine as regular dimensions, so select Regular and move to the next screen. This screen asks if the dimension contains a parent-child attribute, which it doesn’t, so it’s safe to continue to the next screen.

The wizard now attempts to detect hierarchies, but fails to find any in this case. That’s too bad because there is a clear hierarchy here (Product Category to Product- Subcategory to Product), but you have to create it manually after the wizard is done. Click the Next button a couple of times until you see the Completing the Wizard screen. This screen shows the completed dimension with the attributes and, if any are found, the hierarchies. Here you can rename the dimension if desired; many users prefer to drop the word “Dim” from the front of the dimension, naming it simply “Product.” Click Finish to create the dimension.

Create the Time Dimension

Now let’s move on to the Time dimension and look at the differences between a time and a non-time, or standard, dimension. The initial process is the same: Right click the Dimensions folder in the Solution Explorer and choose New Dimension, accept the defaults to have the dimension built with a data source, and select the AdventureWorksDW DSV.

The next screen is the Select the Dimension Type page, and this time you click Time dimension and select dbo_DimTime in the drop-down list. The wizard shows an extra screen called Define Time Periods, where you assign various columns in the Time dimension table to time properties. I assigned only four of the columns to keep the example simple.

Now the wizard displays the hierarchies it’s identified. The Time dimension almost always has at least one hierarchy if you’ve assigned columns to the time properties.hows the hierarchy resulting from the four columns I assigned. It contains the unfortunate name “Calendar Year – Calendar Quarter – Month Number Of Year – Full Date Alternate Key.” I’ll change it to just “Calendar” later. You can also change the names of the levels within the hierarchy, remove the entire hierarchy, or remove certain levels within the hierarchy, as needed.

Now our project has two dimensions. We haven’t yet created a cube, but you can still process one or both of the dimensions, which involves reading the data from the dimension tables and building the dimension structure. After processing, verify the dimension structure by browsing the data in the dimension using the Browser tab at the top.