Exercise 2: Enriching the Reseller Sales Cube with Calculations

In this exercise, you will enrich the Reseller Sales cube with various calculations. You will define calculated members, a named set, scoped assignments and a Key Performance Indicator (KPI). Finally, you will review the calculations by returning to SQL Server Management Studio to execute some additional MDX queries.

Task 1 – Opening the Analysis Services Project

In this task, you will open the Analysis Services project.

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 Ex2-EnrichingTheResellerSalesCube\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.

4. In Solution Explorer, if necessary, collapse the Populate DW and Sales Reports projects.

Task 2 – Configuring the Analysis Services Project

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 – Defining the Sales Variance Calculated Member

In this task, you will define the Sales Variance calculated member that includes as part of its definition a color expression to highlight unfavorable variances in red.

1. In Solution Explorer, in the Sales Analysis project, right-click the Reseller Sales cube, and then select Open.
2. In the cube designer, select the Calculations tab (third tab).

3. On the Cube menu, select New Calculated Member.

   Note:

   It is very important that you follow the lab instructions precisely, particularly when naming calculations. This lab uses code snippets that expect calculations have been named correctly.

4. In the Name box, enter [Sales Variance].

5. In the Expression box, enter the following:

   Note:

   To assist you building the expression, you can drag and drop the elements from the Calculations Tool Metadata pane available at the bottom left side of the cube designer.

   MDX

   [Measures].[Sales Amount] - [Measures].[Sales Amount Quota]

6. In the Format String dropdown list, enter "#,#". (Do include the double quotes, but not include the period.)

   Note:

   This will format the values with a comma for the thousands separator.

7. In the Associated Measure Group dropdown list, select Reseller Sales.

8. Expand Color Expressions, and then in the Fore Color box, enter the following expression.

   MDX

   Iif([Measures].[Sales Variance] < 0, 255, 0)
   Note:

   This expression will conditionally format the color of cells. The color definitions are based on RGB additive color model. Black is 0 and red is 255. Notice the button to the right of the box that opens a color palette to help you define the RGB code.

9. To review the cube’s MDX script, on the cube designer toolbar, click the Script View button.

   Figure 2

   Switching to Script View

10. Review the MDX scipt.

    Note:

    With your understanding of MDX you can appreciate that the CREATE MEMBER statement is very similar to the WITH MEMBER clause in a query. The difference is that the CREATE MEMBER statement creates a permanent calculated member in the cube while the WITH MEMBER clause calculation no longer exists after the query has finished running. The calculated member you defined in this task is available for consistent reuse across all queries and does not require that the user understand MDX to define it.

Task 4 – Adding Additional Calculated Members

In this task, you will insert and review four calculated members into the cube’s MDX script.

1. To position the cursor at the immediate end of the MDX script, press Control+End.
2. On the Edit menu, select Insert File as Text.
3. In the Insert File window, navigate to the Assets folder located in the Source folder for this lab, select the Snippets_A_CalculatedMembers.txt file, and then click Open.
4. Review the four calculated members inserted, and notice their similarity to the member defined in the previous exercise.

Task 5 – Defining the Top 5 Salespeople By Sales Named Set

In this task, you will define the Top 5 Salespeople By Sales named set. This set will allow users to easily introduce the set into their queries and reports.

1. To switch back to form view, on the cube designer toolbar, click the Form View button.

   Figure 3

   Switching to Form View

2. To create a named set, in the Script Organizer (located on the left), select the last command in the list.

   Figure 4

   Selecting the Last Command

3. On the Cube menu, select New Named Set.
4. In the Name box, enter [Top 5 Salespeople By Sales].
5. Position the cursor inside the Expression box.
6. On the Edit menu, select Insert File as Text.
7. In the Insert File window, navigate to the Assets folder located in the Source folder for this lab, select the Snippets_B_NamedSet.txt file, and then click Open.

8. Review the expression:

   MDX

   TopCount([Salesperson].[Salesperson].[Salesperson].Members, 5, [Measures].[Sales Amount])

9. To review the cube’s MDX script, on the cube designer toolbar, click the Script View button.

Task 6 – Defining Scoped Assignments

In this task, you will insert and review two scoped assignments that will override the base cube cell values based on an expression.

1. To position the cursor at the immediate end of the MDX script, press Control+End.
2. On the Edit menu, select Insert File as Text.
3. In the Insert File window, navigate to the Assets folder located in the Source folder for this lab, select the Snippets_C_ScopedAssignments.txt file, and then click Open.
4. Review each scoped assignment.

Task 7 – Defining the Sales Performance KPI

In this task, you will create the Sales Performance KPI.

1. In the cube designer, select the KPIs tab.
2. On the Cube menu, select New KPI.

3. Configure the KPI properties based on the following table.

   Property	Value
   Name	Sales Performance
   Associated Measure Group	Reseller Sales
   Value Expression	[Measures].[Sales Amount]
   Goal Expression	[Measures].[Sales Amount Quota]
   Status Indicator	Shapes

4. Position the cursor inside the Status Expression box.
5. On the Edit menu, select Insert File as Text.
6. In the Insert File window, navigate to the Assets folder located in the Source folder for this lab, select the Snippets_D_KpiStatus.txt file, and then click Open.

7. Review the MDX expression.

   Note:

   If the Sales Variance Percent measure is less than or equal to -5% the expression returns -1. Otherwise, if it is less than 0 then the expression returns 0. Otherwise, the expression returns 1. Each return value maps to an indicator: -1 is red; 0 is amber; 1 is green.

8. Position the cursor inside the Trend Expression box.
9. On the Edit menu, select Insert File as Text.
10. In the Insert File window, navigate to the Assets folder located in the Source folder for this lab, select the Snippets_E_KpiTrend.txt file, and then click Open.

11. Review the MDX expression.

    Note:

    A comparison of the Sales Variance Percent measure is made between the current and previous time periods. If the current value exceeds the previous value the expression returns 1. If the previous value exceeds the current value the expression returns – 1. Otherwise, if there is no change the expression returns 0. Each return value maps to an indicator: -1 is a down arrow; 0 is a sideways arrow; 1 is an up arrow.

12. In Solution Explorer, right-click the Sales Analysis project, then select Deploy.
13. If prompted to override the existing database, click Yes.

Task 8 – Browsing the Sales Performance KPI

In this task, you will browse the Sales Performance KPI.

1. In the KPI designer, on the toolbar, click the Browser View button.

   Figure 5

   Selecting the Browser View

2. In the Filter pane, configure the following filter, and then press Enter.

   Figure 6

   Filtering the KPI

3. Notice the updated data, and status and trend indicators.
4. Explore the KPI for all four quarters of CY2007 (be sure to press Enter after each expression change).

Task 9 – Reviewing the Calculations

In this task, you will review and execute some MDX queries in SQL Server Management Studio.

1. Switch to SQL Server Management Studio.
2. On the File menu, select Open | File.
3. In the Open File window, navigate to the Assets folder located in the Source folder for this lab, select the MdxQueries_B.mdx file, and then click Open.
4. In the Connect to Analysis Services window, ensure that the Server Name is correct, and then click Connect.
5. On the toolbar, select the Sales Analysis database.
6. To view the calculated members, in the query window, in the metadata pane, expand Measures, and then expand the Reseller Sales measure group.
7. To view the named set, expand the Salesperson dimension.
8. To see the KPI, expand KPIs, and then expand the Sales Performance KPI to view the four metrics.
9. Follow the instructions described at the top of the script file.

Task 10 – Finishing Up

In this task, you will finish up by closing the all open files.

1. To close SQL Server Management Studio, on the File menu, select Exit.
2. To close SQL Server Business Intelligence Development Studio, on the File menu, select Exit.