Re-defining a Dimension

Re-defining a dimension

Selections/options covered in the next few topics are available when you initially build a Cube. We are demonstrating them here in the context of how to modify a relationalDimension, i.e., how to use Xchange to modify the composition of an already-built proCube dimension that has been created via Xchange.

Xchange allows you—even after you have initially built a cube—to customize its dimensional make-up so that it is better suited to your needs. However, keep in mind thatmodifying a Dimension will affect the structure of the Cube(s) that uses the Dimension. Modifying a Dimension can have a serious bearing on your work if you do so after having used the cube in a production environment. Sometimes it may be more appropriate to create a new cube that uses the (same) dimension, but with the modifications you require. Refer to Ramifications of Modifying Relational Dimensions for more information.

In the following steps we proceed under the hypothetical assumption that we have not yet settled on our final Cube design—so we will modify Dimensions created previously in the SalesOrder cube.

We will begin by making several global changes to these Dimensions, re-tracing some steps we followed before, but making new selections. Then we will examine the specific options in the Define Members dialog; in Defining Member Options. Some of these features will be demonstrated by making actual changes/selections; others will be explained, since their effect will not be immediately apparent in the example file, but are no less important.

To modify a relational-based Dimension:

  1. From the Data Ribbon's Xchange Group, click Modify Relational Dimension to open the Modify Dimension from Relational Source Dialog.

modifydimensionfromrelationsourcedialog.png

Figure 1.    Modify Dimension From Relational Source Dialog - SalesOrder

  1. Select Measures, then click OK. A Message box that appears asking if you want to proceed:

modifyingdimensioncubemessage.png

Figure 2.    Modify a Dimension Within a Cube Message

This message box is a reminder that there are ramifications for Modifying a Relational Dimension because it involves a complete rebuild of the Cube.

  1. Another message opens notifying you that the metadata will be rebuilt. Click OK to proceed and open the Define Measures dialog.

definemembersdialogremoveunitprice.png

Figure 3.    Define Members Dialog

For demonstration purposes, we will remove an item, UnitPrice, from the Measures dimension. In fact, there is a good reason to exclude Unit Price from the Members of a Measures Dimension. When Unit Price is used as a Measure, individual prices aggregate in hierarchies—like for 1st Quarter Sales—when in fact unit price is the same across aggregations. This will render Unit Price figures meaningless in aggregate calculations. A more meaningful Measure would be Revenue, since aggregate revenues across hierarchies are meaningful.

  1. Select UnitPrice, then click on the Remove member icon button (shown in the preceding figure).

  2. Click Next.

  3. The Relational Dimension Summary message box appears (detail shown below), indicating the modifications you have made:

relationdimensionsummaryremoveunitprice.png

Figure 4.    Relation Dimension Summary - Remove UnitPrice

  1. Click Finish.

proCube will process the Dimension modification, and there will be a pause while the spinning proCube logo appears. When the process is completed you will see a message box:

removeunitpricedimensionmessage.png

Figure 5.    Remove UnitPrice Dimension Message

  1. Click OK.

At this point you can open a Slice to determine that the modification has been accomplished—in this example, Quantity remains as the only Member in the Measures Dimension.

Adding members to the 2nd and 3rd dimensions

We now want to examine what happens when we modify (or, add) Members to a non-Measures Dimension. This subject was touched upon earlier—the concatenation of field records in each row of the table. These steps will demonstrate the results of using two fields as Members.

  1. From the Data Ribbon's Xchange Group, click Modify Relational Dimension to open the Modify Dimension from Relational Source Dialog.

  2. Select Products, then click on OK.

  3. Click OK in the message box to proceed with modifying the Dimension.

Our aim is to create Members that include both the Product ID and the Product Name, with the ProductID indicated first in the resulting Member names.

  1. Select ProductName, which is currently listed in the Members list box, the right-hand pane.

  2. Click on the Remove member icon at the center of the dialog box.

  3. Then, successively, double-click on ProductID on the left and double-click on ProductName.

  4. The right-hand pane will appear as in the detail figure that follows:

definemembersproductidproductname.png

Figure 6.    Members - ProductID and ProductName

  1. Click Next through the successive dialog boxes; then, in the Relational Dimensional Summary, click Finish.

  2. Click OK in the message box that reports updated points.

Now, to take a look at the results in proCube:

  1. From the Model ribbon's Meta Object Group, click, Dimensions to open the Dimensions Dialog.

  2. Double-click the Products Dimension to open the ‘Products’ Hierarchy dialog. Here you will see the modification reflected in the Member list, on the left, as shown in the detail figure:

producthierarchydialogprodidprodname.png

Figure 7.    'Products' Hierarchy Dialog - Product ID and Product Name

The Product ID is now shown first, followed by the Product Name. We will explain how to reorder these Members shortly, when we discuss Define Member Options.

  1. Click OK to dismiss each of the two dialogs.

Changing aliases

Previously, when we created the Customers Dimension, we created an Alias Group that consisted of Contact Names. We will proceed by changing the Alias Group, by removing Contact Names and substituting Customer ID, the entries for which are also unique for each Customer.

  1. From the Data ribbon's Xchange Group, click Modify Relational Dimension to open the Modify Dimension from Relational Source dialog.

  2. Select Customers, then click OK.

  3. Click OK in the message box to proceed with modifying the Dimension.

  4. Click Next in the Define Members dialog.

  5. Now, in the Define Alias dialog box, select ContactName in the right-hand pane.

  6. Click on the Delete Group button.

  7. Click on the Add Group button.

  8. Select CustomerID from the left-hand pane, then click Add to Alias Group to move it to the right pane.

In the Define Aliases dialog box you can create more than one Alias Group—for example, you could have added Customer ID as Group 2, maintaining ContactName as Group 1. Also, you can add more than one field within a Group by using the Add to Alias Group button—the resulting aliases will be a concatenation of selected fields. REMEMBER, Aliases must be unique for each Member.

  1. Click Next through successive dialog boxes, then click Finish in the Relational Dimension Summary box.

  2. Click OK in the message box that reports updated points.

Now, to take a look at the results in proCube:

  1. From the Model ribbon's Meta Objects Group click Dimensions to open the Dimensions Dialog.

  2. Double-click the Customers Dimension to open the ‘Customers’ Hierarchy dialog.

  3. Click the Alias Groups icon to open the Alias Groups dialog as shown in the following figure. CustomerID is the new Alias Group, and its unique entries in the column below it, alongside the corresponding Members for the Customers dimension.

aliasgroupscustomeridxchange.png

Figure 8.    Alias Groups Dialog

  1. Click OK until you return to proCube Management Studio.

Changing key alias groups

Previously, when we created the Customers Dimension, we selected the Alias Group, “ContactNames” as the key alias group. In this example we will show how you might change this selection.

  1. From the Data Ribbon's Xchange Group, click Modify Relational Dimension to open the Modify Dimension from Relational Source Dialog.

  2. Select Customers, then click OK.

  3. Click Next successively to reach the Define Key Alias Group dialog.

definekeyaliasgroupdialogcontactname.png

Figure 9.    Define Key Alias Group Dialog - ContactName

  1. Select ContactName as the Key Alias Group and click Next until you reach the Relational Dimension Summary.

  2. Click Finish in the Relational Dimension Summary box.

  3. Click OK in the message box that reports updated points.

Changing properties

Currently, the Properties Group for the Customers dimension is Contact Title. We will add a new Properties Group, City. Recall that City is a level within the Customers hierarchy—individual Customer members aggregate into Cities, which aggregate into Countries. We are going to amend this Hierarchy next, so that there is no aggregation for Cities, only for Countries. Thus we will proceed with two “modifications” before re-creating the Dimension in the following steps.

You may in fact use a field for both a Hierarchy and for an Alias or a Property; in other words, a field need not be used exclusively for one or the other.

  1. From the Data Ribbon's Xchange Group, click Modify Relational Dimension to open the Modify Dimension from Relational Source Dialog.

  2. Select Customers, then click OK.

  3. Click OK in the message box to proceed with modifying the Dimension.

  4. Click Next successively to reach the Define Properties dialog.

  5. Click on the Add Group button. Note that Group 2 appears in the Properties drop-down box.

  6. Select City in the fields list on the left, then click Add to Property Group to move it to the right pane.

  7. Click Next to reach the Define Hierarchy dialog.

Changing hierarchies

Now that you have reached the Define Hierarchy dialog:

  1. Expand the hierarchy for the dimension Customers.

  2. Select City, then click the Remove Item icon in the center of the dialog.

You can make selections in the Fields list on the left as well as the Hierarchy on the right and place/remove fields through the use of the icons in the dialog box. Alternately, you can remove all items then begin again to create the Dimension hierarchy.

  1. Click Next. The Relational Dimension Summary sums up the parameters of the Xchange operation, the conversion of the underlying relational table into a proCube dimension. The detail figure below shows the modifications made to change Aliases, Properties and the Hierarchy.

relationalsummarycontacttitlecitycountry.jpg

Figure 10.           Relational Summary - Contact Title, City, Country

  1. Click Finish in Relational Dimension Summary.

  2. Click OK in the message box that reports updated points.

You can now find the new Properties Group, Cities, and the newly configured Hierarchy, in which Country is the only “parent” Member, from the Customers dimension Hierarchy dialog. The two following figures bear this out:

propertygroupsdialogcontacttitlecity.png

Figure 11.           Property Groups Dialog - Contact Title and City

In the figure above, the Property Group City appears in the rightmost column, beside the first Property Group, ContactTitle.

In the figure below, the expanded Mexico Member shows the constituent Members Ana Trujillo Emparedados y helados, Anotono Moreno Taqueria, etc. as children of Mexico (the country)—the City level (previously for these Members, Mexico D.F.) no longer exists. Likewise, Berglunds snabbkop and Folk och fa HB, each of which existed within a City (Luleaand Bracke, respectively) scroll up directly into Sweden.

customershierarchydialogwithoutcity.png

Figure 12.           ‘Customers’ Hierarchy Dialog without City

  1. Close the ‘Customers’ Hierarchy dialog to return to proCube Management Studio.

 

 

Have more questions? Submit a request

0 Comments

Please sign in to leave a comment.
Powered by Zendesk