Create New Attributes within the Product Dimension upon Which to Establish Unknown Member Management within the Supporting Properties
In the next procedural steps, we will add two new attributes, Product Category and Product Subcategory, to the Product dimension, basing these attributes upon tables that we will add into the data source view that supports our cube. We will then establish Unknown Member properties to meet the expressed business requirements of our client, among other structural adjustments we will perform for this purpose.
1. Click the Browser tab within the Dimension Designer for the Product dimension.
2. As necessary, select Product Model Lines within the Hierarchy list atop the Browser tab, as shown in Illustration 10.
Illustration 10: Selecting the Product Model Lines Hierarchy ...
3. Expand All Products within the Browser by clicking the “+” sign that appears to its immediate left.
We note the appearance of five children of the All Products level of the Product Model Lines hierarchy.
4. Expand the Components member of the Product Line level.
The children of the Components Product Line level (themselves representing the Model Name level of the hierarchy), including an unlabeled member, appear as depicted in Illustration 11.
Illustration 11: The Model Name Level, Including Unlabeled Member
5. Expand the unlabeled member, appearing among the Components children (itself a member of the of the Model Name level of the Products Model Lines hierarchy).
The children of the unlabeled member, representing assembly components used to build our clients products, appear as partially shown in Illustration 12.
Illustration 12: Children of the Unlabeled Member (Partial View)
Having reviewed the currently existing Product dimension, Product Model Lines hierarchy, we are ready to add the new Product Category and Product Subcategory attributes requested by our client colleagues. To begin, we will transit to the Data Source View underlying our cube.
6. Click the Dimension Structure tab within the Dimension Designer for the Product dimension.
7. Right-click an unoccupied area within the Data Source View pane of the Dimension Structure tab.
8. Select Edit Data Source View from the context menu that appears, as depicted in Illustration 13.
Illustration 13: Editing the Data Source View ...
The Data Source View Designer opens.
9. Select Reseller Sales within the Diagram Organizer pane in the upper left corner of the Data Source View Designer tab.
10. From the main menu, select Data Source View.
11. Select Add/Remove Tables ... from the menu that appears, as shown in Illustration 14.
Illustration 14: Adding Tables to the Data Source View ...
12. Within the Add/Remove Tables dialog that opens next, select dbo.DimProduct within the Included objects list on the right side of the dialog.
13. Click the Add Related Tables button underneath the Included objects list, as depicted in Illustration 15.
Illustration 15: Adding DimProduct-related Tables ...
14. Leaving the newly added dbo.DimProductSubcategory table selected (it was selected by default), click Add Related Tables again.
Both the dbo.DimProductSubcategory and the dbo.DimProductCategory tables are now added to the Included objects list of the Add/Remove Tables dialog, as shown in Illustration 16.
Illustration 16: The Newly Added Tables in the Included Objects List
15. Click OK to accept the newly added tables, and to dismiss the Add/Remove Tables dialog.
16. On the main menu, select Format.
17. Select Auto Layout from the Format menu.
18. Click Diagram on the cascading menu that appears next, as depicted in Illustration 17.
Illustration 17: Select Format -> Auto Layout -> Diagram
The tables appear in diagram format, where we can see the joins that are established between the dbo.DimProductSubcategory and dbo.DimProductCategory tables. The newly added tables are also joined, via the Product table, to the ResellerSales table. The diagram appears similar to that shown in Illustration 18.
Illustration 18: The Tables in Diagram Format
19. Return to Dimension Designer for the Product dimension by clicking the Product tab atop the design environment.
20. Click the Dimension Structure tab, if necessary.
21. Right-click an unoccupied point within in the Data Source View pane at the right of the tab.
22. Click Show All Tables on the context menu that appears, as depicted in Illustration 19.
Illustration 19: Select Show All Tables ...
The tables appear in diagram format, where we can see the joins once again, within another view, as shown in Illustration 20.
Illustration 20: Another Diagram View of the (Relevant) Tables