Hands-On Lab

Building Your First Cube with
SQL Server 2008 R2 Analysis Services

Lab version:1.0.0

Last updated:10/15/2018

Contents

Overview

Exercise 1: Developing the Reseller Sales Cube

Task 1 – Creating the Analysis Services Project

Task 2 – Configuring the Analysis Services Project

Task 3 – Creating the AdventureWorksDW2008R2 Data Source

Task 4 – Creating the Reseller Sales Data Source View

Task 5 – Configuring the Reseller Sales Data Source View

Task 6 – Creating the Date Dimension

Task 7 – Creating the Product Dimension

Task 8 – Creating the Salesperson Dimension

Task 9 – Assembling the Reseller Sales Cube

Task 10 – Browsing the Reseller Sales Cube

Task 11 – Finishing Up

Summary

Overview

This lab will create an Analysis Services project that consists of a data source, data source view, cube and three dimensions.

Note: Before you start with this exercise you must ensure that your machine meets the system requirements detailed in the next section. Additionally, you must complete the setup steps described in the next section.

Objectives

The objectives of this exercise are to:

  • Create and configure an Analysis Services project
  • Create a data source
  • Create a data source view
  • Create dimensions
  • Assemble and format a cube
  • Browse the cube

System Requirements

You must have installed the following items to complete this lab:

  • Microsoft SQL Server 2008 R2:

◦Database Engine

◦Analysis Services

◦SQL Server Business Intelligence Development Studio

  • SQL Server AdventureWorks2008 R2 sample databases

◦AdventureWorksDW2008R2

Setup

All the requisites for this lab are verified using the Configuration Wizard. To make sure that everything is correctly configured, follow these steps.

Note: To perform the setup steps you need to run the scripts in a command window with administrator privileges.

  1. Launch the Configuration Wizard for this lab by double-clicking the Dependencies.dep file located under the Source\Setup folder of this lab. Install any pre-requisites that are missing (rescanning if necessary) and complete the wizard.

Cleanup

There is no need to cleanup if you intend to continue the sequence of labs in this training kit.

  1. To restore the original state of the AdventureWorksDW2008R2SQL Server database and remove the Sales Analysis Analysis Services database, execute the Cleanup.cmdscript located under the Setup folder in the Source folder of this lab.

Exercises

This Hands-On Lab comprises the following exercise:

  1. Developing the Reseller Sales Cube

Estimated time to complete this lab: 45minutes.

Exercise 1: Developing the Reseller Sales Cube

In this exercise, you will commence by creating and configuring an Analysis Services project. You will then create a data source to reference the AdventureWorksDW2008R2 database. Based on this data source, you will create and configure data source view based on the FactResellerSales and FactSalesQuota tables, and some associated dimension tables. You will then create three dimensions: Date, Product and Salespeople, and then use these to assemble a cube. Finally you will deploy the project and browse the cube.

Task 1 – Creating the Analysis Services Project

In this task, you will open an existing solution that consists of the completed labs. You will then add an Analysis Services project to the solution.

  1. Open SQL Server Business Intelligence Development Studio from Start | All Programs | Microsoft SQL Server 2008 R2 | SQL Server Business Intelligence Development Studio.
  2. To open the AdventureWorksBI solution, on the File menu, select Open | Project/Solution.
  3. In the Open Project window, navigate to the Ex1-DevelopingTheResellerSalesCube\Begin folder located in the Source folder for this lab, select the AdventureWorksBI.sln file, and then click Open.

Note: This solution consists of all completed labs that precede this lab.

  1. In Solution Explorer, if necessary, collapse the Populate DW and Sales Reports projects.
  2. In Solution Explorer, right-click the AdventureWorksBI solution, and then select Add | New Project.
  3. In the Add New Project window, in the Project Types pane, ensure Business Intelligence Projects is selected, and then in the Templates pane, select Analysis Services Project.
  4. In the Name box, replace the text with Sales Analysis, and then click OK.
  5. To save the solution, on the File menu, select Save All.

Task 2 – Configuring the Analysis ServicesProject

In this task, you will configure the TargetServerURL property in preparation for the deployment task later in this lab.

  1. In Solution Explorer, right-click the Sales Analysis project, and then select Properties.
  2. In the Sales Analysis Property Pages window, select the Deployment page, set the Server property to <servername>, and then click OK.

Note: You will need to substitute <servername> for the name of the machine that hosts Analysis Services.

Task 3 – Creating the AdventureWorksDW2008R2 Data Source

In this task, you will create the AdventureWorksD2008R2 data source.

  1. In Solution Explorer, in the Sales Analysis project, right-click the Data Sourcesfolder, and then select New Data Source.
  2. In the Data Source Wizard, read the welcome message, and then click Next.
  3. In the Select How to Define the Connection step, in the Data Connections list, select the SqlServerTrainingKitAlias.AdventureWorksDW2008R2 connection.

Note:SQL Server Business Intelligence Studio caches previously defined connections. If this data source is not listed, complete these steps to create it:

Click the New button.

In the Connection Manager window, in the Server Name box, type SqlServerTrainingKitAlias, and then in the Select or Enter a Database Name dropdown list, select AdventureWorksDW2008R2.

Click OK.

  1. Click Next.
  2. In the Impersonation Informationstep, select Use the Service Account, and then click Next.

Note: A preferred practice is to use a dedicated domain account. For simplicity you will use the service account in this lab.

  1. In the Completing the Wizardstep, accept the default Data Source Name, and then click Finish.
  2. To save the solution, on the File menu, select Save All.

Task 4 – Creating the Reseller Sales Data Source View

In this task, you will you create the Reseller Sales data source view. The data source view is the foundation upon which dimensions, cubes and data mining models are developed.

  1. In Solution Explorer, in the Sales Analysis project, right-click the Data Source Views folder, and then selectNew Data Source View.
  2. In the Welcome to the Data Source View Wizard, read the welcome message, and then click Next.
  3. In the Select a Data Sourcestep, notice that the data source you created in Task 4is selected, and then click Next.
  4. In the Select Tables and Viewsstep, in the Available Objects list, while pressing the Control key, select the following eightobjects.

Note:To fully display the object names you may need to expand the size of this dialog box by dragging any of the window’s corners.

Object
DimEmployee
DimProduct
DimProductCategory
DimProductSubcategory
DimSalesTerritory
FactResellerSales
FactSalesQuota
vDateFeed
  1. Click the arrow to add the selected tables to the Included Objects list.

Figure 1

Adding the Objects to the Included Objects List

  1. Click Next.
  2. In the Completing the Wizardstep, in the Name box, replace the text with Reseller Sales, and then click Finish.
  3. When the wizard completes, in Solution Explorer, notice the addition of the Reseller Sales data source view, and that the data source view designer opens automatically.
  4. To save the solution, on the File menu, select Save All.

Task 5 – Configuring the Reseller Sales Data Source View

In this task, you will refine the design of the data source view.This will involve providing friendly names for each of the tables, creating named columns to produce date keys in each fact table, establishing relationships between the fact tables and the Date table (which is based on a view), and replacing the Employee table with a named query that restricts the columns and rows to those relevant to salespeople.

  1. To rename the tables, in the data source view designer, in the Tables pane (located in the bottom left corner), select the DimEmployee table, and then in the Properties window, modify the FriendyName property to Employee.

Note: If the Properties window is not visible, on the View menu, select Properties Window.

  1. Repeat this step for each table, except vDateFeed, to remove the Dim or Fact prefix.
  2. For the vDateFeed table, modify the FriendlyName property to Date.

Note:The purpose of this step is to create a user-friendly data model. It is important to configure friendly names at the data source view level so that they are consistently inherited throughout the objects (cubes, dimension and, mining models) created upon this view.

  1. To create a named calculation in the ResellerSales table, in the Tables pane, right-click the ResellerSales table, and then select New Named Calculation.
  2. In the Create Named Calculation window, in the Column Name box, type MonthKey.
  3. In the Expression box, enter the following expression, and then click OK.

T-SQL

OrderDateKey / 100

Note: The ResellerSales table data is stored by month not day. This expression removes the date portion from the OrderDateKey. For example, the OrderDateKeyvalue for 1 January 2008 is 20080101. The MonthKey derived from this value is 200801. The MonthKey column will relate to the MonthKey column in the Date table.

  1. Repeat the last three steps to create a new named calculation in the SalesQuota table named CalendarQuarterKey based on the following expression.

T-SQL

(CalendarYear * 10) + CalendarQuarter

Note: The SalesQuota table data is stored at calendar quarter granularity.

  1. To establish a relationship between the ResellerSales table and the Date table, in the Tables pane, expand the ResellerSales table, right-click the MonthKey column (located at the end of the column list), and then select New Relationship.
  2. In the Specify Relationship window, in the Destination Table dropdown list, select Date.
  3. In the Destination Columns column, ensure thatMonthKeyis selected, and then click OK.

Figure 2

Creating the ResellerSales to Date Table Relationship

  1. Repeat the last three steps to create a relationship between the SalesQuota table’s CalendarQuarterKey column and the Date table’s CalendarQuarterKeycolumn.
  2. To delete the relationship between the ResellerSales table and the SalesTerritory table, in the Tables pane, in the ResellersSales table, expand the Relationships folder (located at the end of the column list), right-click the SalesTerritory relationship, and then select Delete Relationship.
  3. When prompted to delete the relationship, click OK.

Note: The relationship between the ResellerSales and SalesTerritory tables is valid, but is not required in this project.

  1. To replace the Employee table with a named query, in the Tables pane, right-click the Employee table, and then select Replace Table | With New Named Query.
  2. In the Create Named Query window, in the Name box, replace the text with Salesperson.
  3. In the lower pane, replace the query text with the following.

Note:The query required in the following step may be copied from the Assets\Snippets.txt file in the Source folder of this lab.

T-SQL

SELECT EmployeeKey, SalesTerritoryKey,

LastName + N', ' + FirstName AS SalespersonName, EmailAddress, Phone

FROM dbo.DimEmployee

WHERE SalesPersonFlag=1;

  1. On the toolbar, click the Run button, and verify that 17 rows are returned.

Figure 3

Executing the Salesperson Query

  1. Click OK.
  2. In the data source view designer, to arrange the tables, right-click in a blank area of the diagram, and then select Arrange Tables.

Note: To facilitate exploring the data source view diagram, click on the four-headed arrow located in the bottom right corner. While continuing to hold the click, drag about the thumbnail view to navigate across the diagram.

Figure 4

Navigating the Data Source View Diagram

  1. To explore the data in the Salesperson table, in the Tables pane (or the diagram), right-click the Salesperson table, and then select Explore Data.
  2. Notice the four tabs in the window that allow exploring data in Table, PivotTable, Chart and PivotChart views.

Note:It is also possible to click column headers to sort the rows by that column order, ascending or descending.

  1. To close the explorer window, on the File menu, select Close.
  2. On the File menu, click Save All.
  3. To close the data source view designer, on the File menu, select Close.
  4. To save the solution, on the File menu, select Save All.

Task6 – Creating the Date Dimension

In this task, you will create the Date dimension. This dimension will consist of four attributes to define calendar date periods. To support efficient and convenient navigation, you will also define a user-defined Calendar hierarchy to deliver drilldown and drillup paths. These paths will permit users to summarize cube measures at different levels of granularity.

  1. In Solution Explorer, in the Sales Analysis project, right-click the Dimensions folder, and then selectNew Dimension.
  2. In the Welcome to the Dimension Wizard, read the welcome message, and then click Next.
  3. In the Select Creation Methodstep, ensure theUse an Existing Tableoption is selected, and then click Next.
  4. In the Specify Source Informationstep, in the Main Table dropdown list, select Date.
  5. In the Name Column dropdown list, select DateLabel, and then click Next.
  6. In the Select Dimension Attributes page, check the following attributes, and rename them as follows.

Attribute / Rename Attribute To
Date Key / Date
Month Key / Month
Calendar Quarter Key / Calendar Quarter
Calendar Year Key / Calendar Year

Figure 5

Selecting the Date Dimension Attributes

  1. Click Next.
  2. In the Completing the Wizardstep, click Finish.
  3. InSolution Explorer, notice the addition of the Date dimension, and that the dimension designer opens automatically.
  4. To save the solution, on the File menu, select Save All.
  5. In the dimension designer,in the Data Source View pane, right-click the Date table, and then select Explore Data.

Figure 6

Exploring the Date Data

  1. Review the data and formats in each column.

Note: Pay particular attention to pairs of columns suffixed with Key and Label. The Key columns are unique numeric representations of the date parts while the Label columns are user-friendly formats of the date parts. During the wizard creation of this dimension you selected attributes from the Key columns. The following steps will configure the Label columns to provide a friendly name (NameColumn) for each member in the attribute.

  1. To close the explore window, on the File menu, select Close.
  2. In the Attributes pane (located on the left), select the Calendar Year attribute.

Figure 7

Selecting the Calendar Year Attribute

  1. To configure theNameColumn, in the Properties window, select the NameColumn, and then click the correspondingellipsis.

Figure 8

Clicking the NameColumn Property Ellipsis

  1. In the Name Column window, in the Source Column list, select the CalendarYearLabel column, and then click OK.
  2. Repeat the last three steps to configure the following attributes NameColumn bindings.

Attribute / NameColumn Binding
Calendar Quarter / CalendarQuarterLabel
Month / MonthLabel

Note: The Date attribute’s NameColumn propertywas already configured during the wizard configuration.

  1. To assign properties to the dimension and attributes, in Solution Explorer, right-click the Datedimension, and then select Add Business Intelligence.
  2. In the Welcome to the Business Intelligence Wizard, read the welcome message, and then click Next.
  3. In the Choose Enhancementstep, select theDefine Dimension Intelligence enhancement, and then click Next.
  4. In the Define Dimension Intelligencestep, in the Dimension Type dropdown list, select Time.
  5. In the Dimension Attributeslist, check the Year attribute type, and then in the corresponding row in the Dimension Attribute column, select the Calendar Year attribute.
  6. Repeat the last step to assign types to the following attributes, and then click Next.

Attribute Type / Dimension Attribute
Quarter / Calendar Quarter
Month / Month
Date / Date
  1. In the Completing the Wizardstep, review the type assignments, and then click Finish.

Note: Mappingdimension attributes to types is especially important for the Date (Time) dimension. This metadata is required to distinguish the dimension and its attributes, and is referred to by certain time features, for example semi-additive cube measures, and certain MDX time functions.

  1. To create a hierarchy, from the Attributes pane, drag the Calendar Year attribute into the Hierarchies pane.