SQLU SSAS Week: Cube Enhancements 101

May 15, 2011

This is the fourth post for SSAS week at SQL University. Here’s the coursework so far:

In today’s post, I will show how you to correct problems that you discover in dimensions after you deploy the database and how to enhance the cube by adding another measure group as well as adding special features, such as key performance indicators and actions.

Step 0: Dimension Cleanup

In the previous lesson, you deployed the cube and browsed the results. Everything looked good except for category and subcategory attributes, whether individually or as levels in the Products hierarchy of the Product dimension. Your task was to figure out why you the labels didn’t display correctly and to fix it.

Now for the solution. Remember that each attribute has a KeyColumns property and a NameColumn property. Unless you specify the NameColumn explicitly, the dimension will display the value in the KeyColumns when you’re browsing that attribute in a metadata list or in the cube. To fix the problem, just set the NameColumn property to EnglishProductCategoryName for the Category attribute and set the same property for the Subcategory attribute to EnglishProductSubcategoryName.

Deploy the project, and then browse the cube again to check the results. You need to click the Reconnect button to see the labels change. You can also use the Browser tab in the dimension designer to view labels in each attribute hierarchy or user-defined hierarchy if you prefer.

imageStep 1: Additional Measure Group

It’s a common practice to build a cube incrementally. That means you don’t need to have all your measure groups available at the same time before you can design the cube or even to make it available to users. Today you’re going to add a third measure group for Sales Amount Quota so that the cube supports comparisons of actual sales to the quotas.

Nothing makes it into a cube or dimension without coming through the DSV, so open the DSV file in your project. Right-click on the DSV background and select Add/Remove Tables. Locate the FactSalesQuota table and add it to your DSV. The foreign key relationship to the Date dimension is added automatically. The fact table also has a relationship to the Employee dimension which we’re ignoring in this week’s lesson. After we add the fact table as an additional measure group to the cube, we’ll be able to slice and dice the quota data only by date. Be sure to rename the table to SalesQuota before continuing, just like you did for InternetSales and ResellerSales.

To add the quota data to the cube, you need to open the cube designer. In the Measures pane in the top left corner of the designer, right-click the Sales cube, and select New Measure Group. Then you can add SalesQuota.However, adding the entire measure group adds all the numeric columns as measures, so you can switch to the grid view to multi-select the unneeded columns. When finished, the Sales Quota measure group should have only one measure: Sales Amount Quota. Tip: An alternative approach would be to use the New Measure command when you right-click the cube, and then use a dialog box to select the measure that you want to add.

Be sure to set the format string to Currency. Deploy the project, and then browse the cube by adding the following measures to the grid: Sales Amount and Sales Amount Quota. Then add Date.Calendar to rows like this:

image

Notice anything strange here? Such as the repeating values in the Sales Amount columns? Remember from the previous lesson that Sales Amount combines values from the Reseller Sales and Internet Sales measure groups. But, you can’t slice that value by date without making another change to the cube.

You also have some other date dimensions available – Order Date, Due Date, and Ship Date – which allow you to slice by Sales Amount, but not by Sales Amount Quota. In order to calculate the variance between Sales Amount and Sales Amount Quota, you need to be able to slice both measures at once.

SNAGHTML60251e Step 2: Dimension Usage

The ability to slice and dice a measure by a dimension member is determined by the relationship that exists between measure groups and dimensions. That type of relationship is managed on the Dimension Usage tab of the cube designer. It’s usually inherited from the foreign key relationship found in the DSV when you use the cube wizard, but it’s ignored when you manually add a measure group. The gray box at the intersection of a dimension and measure group indicates a relationship does not exist.

SNAGHTML70e457

To add a missing relationship, click the gray box (such as the one at the intersection of Internet Sales and Date), and click the ellipsis button that appears there. Most of the time, you define the relationship here as the Regular relationship type. The other types are for advanced scenarios that we won’t be covering in this course, but if you’re really curious, you can learn more at Dimension Relationships.

Then you specify the level of detail in the dimension that relates to the measure group. For example, the Internet Sales measure group relates to the Date dimension at the Date level. So set Granularity Attribute to Date and set Measure Group Columns to OrderDateKey. In this cube, once its deployed after adding this relationship, the use of either the Date dimension or the Order Date dimension will yield similar results.

image

Next, repeat the process above to add a relationship between Reseller Sales and Date. Then add a third relationship between Sales Amount Quota and Date (Order Date). (You’ll use DateKey for the Measure Group Columns value in the latter case.) That way, whether users select Date or Order Date for slicing, they can compare values between actual sales and the quotas.

Why not eliminate all the extra dates? Aren’t they confusing? Well, it depends. If users will never analyze data using Ship Dates or Due Dates, then you can safely delete the three role-playing dimensions from the cube on the cube designer page (in the bottom left corner on the Cube Structure tab). You’ll still have the Date dimension available for time-series analysis and time-based slice-and-dice. On the other hand, you might want to retain Order Date along with Date in the cube if there’s a possibility that you will add another measure group that relies on Date but has no relationship whatsoever to Order Date. (That scenario doesn’t exist in the Adventure Works sample data, incidentally.)

SNAGHTML34c12d Step 3: Key Performance Indicator

Another way to enhance a cube is to add key performance indicators (KPIs). A KPI is a group of calculations that you use to compare a value to a target. Client tools for Analysis Services can display the results of this comparison using different images that are associated with a positive, neutral, or negative status. In addition, the KPI includes a trend calculation used to show whether the value is increasing or decreasing as compared to a previous point in time. For example, you can set up a KPI to compare sales amounts to quotas by adding the expressions on the KPIs tab of the cube designer.

Click the KPIs tab which is the fourth tab from the left. Click the New KPI button (the fifth button from the left). In the form that displays, type a name—Quota—and then add the following expression in the Value Expression box:

[Measures].[Sales Amount]

The Value Expression can also be an MDX expression that resolves to a number, but this is a simple example that relies on values found in the fact tables. Next, add the Goal Expression like this:

[Measures].[Sales Amount Quota]

In the Status Indicator drop-down list, you choose an image to represent the current result of comparing Sales Amount to Sales Amount Quota. For this cube, use Shapes. Then enter the Status expression which is used to determine which shape to display. There are three possible shapes, so you need an expression that resolves as one of three possible values which effectively mean “meeting goal”, “progressing towards goal”, and “not meeting goal.” We use 1, 0, and –1 to reflect those three values respectively. For the purposes of this lesson, assume that 1 means the sales are equal to or better than 90% of the target. Otherwise, 60% of the target is represented by a value of 0, and everything else is –1. The expression to produce these results looks like this:

iif([Measures].[Sales Amount] >= [Measures].[Sales Amount Quota] * .9, 1, iif ([Measures].[Sales Amount] >= [Measures].[Sales Amount Quota] * .6, 0, -1))

The Trend expression is similar to status because it is used to determine a shape to display, but in this case the shape is an arrow and the expression requires a comparison between one time period and another. The result of the Trend expression determines what direction the arrow points—up (or 1) for upward trends, flat (or 0) for no change in trend, and down (or –1) for downward trends. The following trend expression compares the current variance to the variance for the previous year.

Case
When IsEmpty(ParallelPeriod([Order Date].[Calendar Year].[Calendar Year],
1, [Order Date].[Calendar Year]))
Then 0
When ([Measures].[Sales Amount] – [Measures].[Sales Amount Quota]) >
((ParallelPeriod([Order Date].[Calendar Year].[Calendar Year],
1, [Order Date].[Calendar Year]), [Measures].[Sales Amount]) -
(ParallelPeriod([Order Date].[Calendar Year].[Calendar Year],
1, [Order Date].[Calendar Year]), [Measures].[Sales Amount Quota]))
Then 1
When ([Measures].[Sales Amount] – [Measures].[Sales Amount Quota]) =
((ParallelPeriod([Order Date].[Calendar Year].[Calendar Year],
1, [Order Date].[Calendar Year]), [Measures].[Sales Amount]) -
(ParallelPeriod([Order Date].[Calendar Year].[Calendar Year],
1, [Order Date].[Calendar Year]), [Measures].[Sales Amount Quota]))
Then 0
Else -1
End

We’ll see how to view KPIs in the next post in this course.

imageStep 4: Action

Another type of enhancement you can add to a cube is an action. There are three types of actions:

  • URL action – to navigate to a specific Web page, such as a Product Catalog page for a product selected in a cube.
  • Reporting action – to open a Reporting Services report, such as a Sales Order Details report for a customer selected in a cube.
  • Drillthrough action – to display in an HTML page the detailed data associated with a selected measure in a cube. (The measure must come from a measure group, and cannot be a calculation.)

Go to the Actions tab of the cube designer to define actions and related properties. Click the New Drillthrough Action button in the toolbar (the sixth one from the left). Name it Internet Sales Details and select Internet Sales as the action target. In the Drillthrough Columns section, select the following columns by dimension:

  • Order Date: Date
  • Product: Product, Subcategory, Category
  • Measures: Internet Sales Amount, Internet Order Quantity

image

Deploy your cube. You’ll see how to use the action in the next post.

What’s Next?

Now you have a basic but fully functioning cube. In our next lesson of the week, Cube Deployment 101, I’ll explain how to implement security and how to provide users access to your cube.

4 Responses to “SQLU SSAS Week: Cube Enhancements 101”

  1. Im encountering error in the KPI part. Am I the only one here who is having a problem?

  2. I’m sorry that you encountered a problem. I was able to successfully deploy the project with the KPI. Can you be more specific about the error you received? That way I can try to help you resolve it.

  3. hi. it’s working now, i just have to manually type all of the “-” part in the trend expression.Thanks for this great tutorial. Im waiting for your next post.

  4. Wonderful – glad to hear you have everything working!

Leave a Reply