SQLU SSAS Week: Cube Construction 101

May 12, 2011

This is the third post for SSAS week at SQL University. If you’re just joining the class, refer to Why Do I Need a Cube & How Do I Get Started and Dimension Design 101 to get caught up.  At this point, my expectation is that you have an Analysis Services project with at least two dimensions designed—Date and Product. Now you’re ready to build the cube itself. In a later post, I’ll discuss various cube enhancements that you can make, security measures to implement, and tools that enable user access to cube data.

SNAGHTML476c410 Step 1: Cube Wizard

By now, you can probably guess how to launch the cube wizard. Right-click the Cubes folder in Solution Explorer, and click New Cube. For creation method, select the Use existing tables option. Then you need to specify the measure group tables. Measure group tables are equivalent to fact tables because, after all, fact tables are simply tables that contain groups of measures (the values that people will analyze). There are two measure group tables for this cube: InternetSales and ResellerSales.On the next page of the wizard, you see a list of all columns with numeric values which are presented as candidate measures. Some of them are just foreign key columns which you can ignore. (If the wizard could figure everything out, why would we need you?)

Clear the Measures checkbox at the top, and then select the following measures from the Internet Sales measure group: Order Quantity (which you rename as Internet Order Quantity), Total Product Cost (which you should rename here as Internet Cost), Sales Amount (which you should rename as Internet Sales Amount). To rename measures here, just right-click the measure name and type in the revised name.

Next, select the following measures from the Reseller Sales measure group: Order Quantity (which you rename as Reseller Order Quantity), Total Product Cost – Reseller Sales (which you should rename as Reseller Cost), Sales Amount – Reseller Sales (which you should rename as Reseller Sales Amount).

Continuing through the wizard, you get a chance to select existing dimensions. These are the dimensions that you’ve already created—Date and Product. The wizard detected the foreign key relationships in the DSV and the existence of the dimension objects in the project.

On the next page of the wizard, it volunteers to create two more dimensions for you—Internet Sales and Reseller Sales. Clear the Dimension checkbox at the top to skip this step. The technical term for these two dimensions is degenerate dimension. There are a lot of steps required to properly configure a degenerate dimension in a cube which I won’t cover this week, and it’s not commonly used (at least not in the projects I’ve done over the past 10 years, so maybe it’s just not commonly used by me…).

At the end of the wizard steps, you get the chance to name the cube. Let’s keep the name simple: Sales. And now, when you complete the wizard, you have a cube!

SNAGHTML5271b09 Step 2: Format Strings

The first thing I always do after I create a cube is configure the format strings for the measures, so that’s what I want you to do next. It’s a good habit to develop because you’ll be browsing the cube to test it soon and it will be easier to review values if they’re already formatted.

On the Cube Structure tab of the cube designer, you can see your measures in the top left corner. Actually, you first see the measure groups, Internet Sales and Reseller Sales, which you can expand to see the measures. You can select each measure one by one and set the FormatString property individually, or you can apply the format string in bulk. Let’s do that.

Click the fifth button from the left  to show measures in a grid. Then, while you press and hold the Ctrl key, select Internet Order Quantity and Reseller Order Quantity. In the Properties window, locate the FormatString property and type #,#. That will set the quantities to display as integers with thousands separators and no decimal places. Now select the remaining measures and select Currency in the drop-down list for the FormatString property.

SNAGHTML5266226 Step 3: Calculations

In the first post, I mentioned that one benefit of cubes is the centralization of business logic. You’ll need to learn MDX to create complex calculations, but I’ll show you how to build a simple one to whet your appetite for more! If you like to learn from books, check out MDX Step by Step. Otherwise, a good online resource begins at MDX at First Glance: Introduction to SQL Server MDX Essentials by William Pearson. Pearson has a whole series of articles that follow this introduction to MDX. Although it’s a bit dated because the language has changed a bit since the article was written (and you can  now use Management Studio to write MDX), most of the principles still apply. The main thing to know is that instead of referencing a dimension member like [Date].[CY 2005] in Dimension.Member format, you use [Date].[Calendar Year].[CY 2005] in Dimension.Hierarchy.Member format.

For your Sales cube, add a simple calculation to combine the sales for Internet and reseller sales. In the Cube Designer that displays when you close the wizard (or if necessary, double-click Sales.cube in Solution Explorer), click the Calculations tab which is the third tab from the left. Click the New Calculated Member button (the fifth button from the left). In the form that displays, type a name—[Sales Amount]—and then add the following expression:

[Measures].[Internet Sales Amount] + [Measures].[Reseller Sales Amount]

This expression does what it looks like it does – adds together the two sales amount to produce a grand total, no matter how much you are slicing and dicing the cube. (If you don’t know what that means, hang on – I’ll show you later!)

Set a format string for the calculation by selecting “Currency” in the drop-down list. Now create two more measures, Order Quantity and Cost, to combine order quantities and costs from the two measure groups like you did for sales amounts. Remember to set the format strings appropriately. Tip: You’ll need to use “#,#” instead of #,# here for Order Quantity.

Step 4: Browse

Now is a good time to do some good ol’ slice and dice to see if the cube behaves as expected and returns correct results. But first you need to deploy the changes that you made just like we did in the Dimension Design 101 lesson. Then when deployment completes successfully, click the Browser tab (the very last one) in the cube designer. From the metadata tree  on the left, drag Measures to the area of the grid labeled Drop Totals or Detail Fields Here. All the measures from the fact tables and the calculations appear in the grid.

Next, expand Order Date and drag Order Date.Calendar to the area of the grid labeled Drop Row Fields Here. That’s adding a slice to the measures. The totals for each measure are now broken up into values by year. If you expand one of the years, you’ll see more detailed slices for the quarters in the selected year.

image

Check out the values for your three calculations to confirm that they properly sum up the base measures for overall sales, costs, and order quantities. An MDX calculation is similar to an Excel formula because it uses relative references to determine its current value cell by cell. The difference is that you define the calculation once and you don’t have to copy it every cell – it just happens. It looks to see what’s on rows, what’s on columns, and also what’s on the filter (which is the grid area currently labeled Drop Filter Fields Here) to come up with the relative reference for each cell in the grid.

Now “dice” the data by dragging Products from the Product dimension to Drop Column Fields Here. To make it easier to see the categories, remove some measures. For example, drag Internet Order Quantity off the grid, along with the other internet and reseller measure groups so that you’re left with only Sales Amount, Cost, and Order Quantity like this:

SNAGHTML5380b8f

By using hierarchies in the browser, you can drill down to month or to product – but the problem is that category and subcategory keys are shown instead of names. Oops! You’ll need to fix that to improve the cube. That’s why we browse in BIDS before we deliver the cube to users…so that we can uncover problems like this.

Your Assignment

Try to solve the problem on your own. I’ll reveal the solution in the next post, Cube Enhancements 101, and as the name implies, I’ll also show you some other enhancements we can make to the cube. Meanwhile, post a comment if you have questions or need help.

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

  1. Check out Bill Pearson’s new MDX series, “Stairway to MDX,” at SQLServerCentral.com:

    http://www.sqlservercentral.com/stairway/72404/

  2. In the following paragraph

    Next, select the following measures from the Reseller Sales measure group: Order Quantity (which you rename as Internet Order Quantity), Total Product Cost – Reseller Sales (which you should rename as Reseller Cost), Sales Amount – Reseller Sales (which you should rename as Reseller Sales Amount).

    I believe it should be “Reseller Order Quantity” instead of “Internet Order Quantity”.

  3. Thanks for the tip for the MDX series, Lazarus! And thanks also to Reuvain for pointing out the typo which I’ve corrected.

  4. [...] Cube Construction 101 [...]

Leave a Reply