Last time when we built our Contoso cube, we included the Product table as a dimension off the Fact Sales table, but we failed to included the product category and product subcategory tables. Therefore, it is not possible to aggregate sales values by product category or subcategory. You might wonder whether it is possible to rebuild the cube adding these two dimensions without having to start over with a new cube. Depending on the amount of formatting we did, this could cause quite a concern.
Fortunately, there is a way for us to go back and not only add these two tables, but also define the product – subcategory – category hierarchy while preserving all the other work we may have done. Let start by opening the Contoso Retail DW project from last time and displaying the Data Source View as shown in the following figure.
We need to add the Product Category and the Product SubCategory tables to our existing data source view. To do this, right click anywhere on the background of the view diagram. A popup menu appear and in that menu, select Add/Remove Tables. The assumption is that you want to add a table from the same data source connection as the rest of the view, so when you click on this option the following dialog appears.
Note that the data source is preselected. I can then click on the tables I want to add to the current view. I can either click on the table to select it and then click the move button with the greater than sign indicating a move from the Available Objects list to the Included List or I can double click on the table name. I will move both the DimProductCategory and DimProductSubcategory tables to the Included objects list. With my tables selected, I can click OK to close the Add/Remove Tables dialog. This action adds the tables to the view diagram. However, objects may appear to overlay each other or overlay the connecting lines between tables. To clean up the diagram, click in an open area of the diagram again and this time select Arrange Tables. Even after choosing this option, you may still want to arrange manually the tables.
I now have the tables in my view, but I still need to create dimensions for them. I could create separate dimensions for each of these tables, but I know that they form a hierarchy with Dim Product. Therefore, open Dim Product. In the Data Source View, right click in an open area of the background and select Show Tables from the dialog that appears. In this dialog, select both the DimProductCategory and DimProductSubcategory tables. You can do this by clicking first on either one and then press and hold the Ctrl key while you click on the second table. Then click the OK button to complete the selection.
Arrange the tables manually in the Data Source View panel or use the Arrange Tables option in the popup menu that appears when you right-click on the background area. You should have something that looks like the following figure.
Click on the fields from these tables that you want to include as attributes in the dimension. For example, let’s include Manufacturer, ColorName, Size, UnitCost, UnitPrice, Status, ProductCategoryKey, and ProductSubCategorykey.
Next select ProductKey in the Attributes panel and then in the Properties window, locate the property NameColumn, click in the value column to display the button with the ellipsis, click on the button to display a list of available columns from the DimProduct table and select ProductName. This selection serves as an alias. When I build my cube and drag Product Key into either the rows or columns, ProductName will appear rather than the value stored in ProductKey. Therefore, I would not want to include ProductName as one of my attributes for this dimension. While in the property dialog, find the OrderBy property and change it to Name.
In a similar fashion, I assigned the field ProductCategoryName to the NameColumn property of Product Category Key and ProductSubcategoryName to the Name Column property of Product Subcategory Key.
Next, drag product Category Key over to the Hierarchies panel. This field will form the top level of a new hierarchy. Right click on the header of this new hierarchy and change the label from Hierarchy to Product. Now drag Product Subcategory Key beneath Product Category Key. This indicates that categories consist of subcategories. Finally drag Product Key to a position beneath Product Subcategory Key. Save your changes. Then click on the Process button in the Dimension Structure toolbar.
You should next see the Process Dimension dialog. Click Run. This step could take a few second. When the step completes successfully, click the Close button to close the Process Progress dialog and click the Close button again to close the Process Dimension dialog.
Now open the cube definition again by double clicking on it. Right click on the project name in the Solution Explorer and click on Deploy. When the Deploy completes successfully, open the Browser tab. Drag one of the measures from the Fact Sales Measure Group to the data area (such as Sales Amount). Then drag the hierarchy Product from the Dim Product dimension to the row fields area. Notice that the Product Category names appear with a small box containing a plus sign to the left of each value. Click the plus sign in one of the boxes to drill down to the next level, subcategories. Again you see a list of all the subcategories in the chosen category and each has another small box with a plus sign to the left of its name. Click one of these plus signs to drill down to the next level, product.
You can see that the hierarchy automatically appears exactly as we would want it. There is no need to ask the user to define the hierarchy manually by dragging the individual product dimension attributes into the cube. Furthermore, it guarantees that the user does not accidentally try to build the hierarchy beginning with subcategories, then categories, and finally products.
One last point, after building the hierarchy that you want to use, you can and probably should remove the individual attributes (Product Key, Product Subcategory Key, and Product Category Key) from the DimProduct dimension.
Next time I’ll look at way to add fact tables and dimension tables to an existing cube.
C’ya later.
SharePointMike's Blog 