Creating the Third Dimension

Creating the Third Dimension

Having created the Measures and Products dimensions, you now want a third dimension for the Sales Order Cube—a Customers dimension.

We will demonstrate the use of a snowflake schema, as well as create an Alias Group and a Properties Group.

  1. Click Add in the Dimensions dialog to open the Select Dimension dialog.

  2. With New Dimension selected, click Next to proceed to the Define Links dialog.

Defining links — snowflake schema

You will now create a Dimension from a simple snowflake schema—getting to a table through another table to define a link. For more information, refer to Preparing Relational Data for Use with Xchange.

  1. Double-click the first (or intermediary) table (e.g., Orders ),  then double-click the second table—the one that contains the data you need for the Dimension (e.g.,Customers). Note that in the example, Customers now appears in the Dimension Name text box at the top right.

  2. Define the link between the first and the second table as in the example:

  • Drag OrderID in the Order Details table to the same table in the Orders table.

  • Drag CustomerID in the Orders table to the same table in the Customers table.

  1. The Define Links dialog will appear as in the following figure:


Figure 1.    Define Links Dialog - Three Links

The above is an example of a snowflake schema.

Note that the Dimension will be created from the rightmost table and only from that table, so all information needed to construct the Dimension—information for Hierarchies, Aliases and Properties—must be contained in that table. If this information is not contained in this table, you will have to manually define these Dimension characteristics through proCube. When you click Preview, you will see the top few rows for the last Table selected.

Defining members

Click Next. The Define Members dialog opens. Select a field(s) in the Fields for (Dimension Name) list box, and double-click the field, so it appears in the Members list — for the example, CompanyName. Yo can also move fields to the Members list by clicking Add.

Creating an alias group

  1. Click Next. The Define Aliases dialog opens.

  2. Now, for the sake of illustration, we are going to create an Alias Group, whose individual elements will be the Contact Name for each Customer.

  3. Note that our assumption is that each Customer has a unique contact name—i.e., each alias name must be unique within a Dimension. This is a pre-requisite for creating the Alias Group. [If each name is not unique to a field element, you will receive an error message.]

  4. Click Add Group in the Define Alias dialog.

  5. Select ContactName in the Fields for [Customers] list box, then click Add to Alias Group (shown along with Remove from Alias Group, in the following figure).

The Define Aliases dialog opens.


Figure 2.    Define Aliases Dialog - Contact Name

The resulting Alias Group you have created will be shown later, when you look over the Cube.

Defining a key alias group

  1. Click Next. Because we have specified an Alias Group, the Define Key Alias Group dialog appears:


Figure 3.    Define Key Alias Group Dialog

This dialog enables you to specify that a particular alias group is to be regarded as the unique identifier for the dimension. This will help prevent duplicates from being added to the cube when incidental data changes occur in the source data.

For instance, when the member name changes in the source system, instead of adding a new member, the system will rename the existing member based on having the same unique alias. Consider an employee dimension where the member names are created from the first and last name but the EMPLOYEE ID has been specified as the alias that will be the unique identifier. If the employee last name changes but the employee ID remains the same, Xchange will modify the employee name in proCube instead of creating an additional employee. This is helpful when there is a combination of actual and planning data in the same model.

  1. Select the alias group that you would like to be used as the key alias group or select "(None)."

Creating a properties group

  1. Click Next. The Define Properties dialog opens (see next figure).

For the sake of illustration, we are going to create a Properties Group—whose individual elements will be the Contact Title for each Customer.

Note that in a Properties Group, the records that correspond to each unique Member may be repeated—i.e., the Properties records do not have to be unique within the Dimension. For example, the items in this example Group will be Contact Titles as Owner, Marketing Manager, Sales Representative, etc. and they will occur more than once, for differentCustomer Company Names.

  1. Click the Add Group button in the Define Properties dialog box. The Alias Group name Group 1 appears in the Aliases combo box.

  2. Select ContactTitle in the Fields for [Customers] list box, then press the Add to Property Group button (shown along with the Remove from Property Group button, in the following figure). The Define Properties dialog now looks like this:


Figure 4.    Define Properties Dialog - Contact Title

The resulting Properties Group created here will be shown later, when you look over the Cube.

Creating dimension hierarchies

  1. Click Next. The Define Hierarchy dialog box appears.

  2. Assume that you do want to create a Hierarchy for this Dimension. If you had previewed the Customer table in the Define Links dialog box (you can backtrack to do so) you would have noticed a Country field as well as a City field—thus there is a City and Country record for each Customer. Each City uniquely belongs to a Country (e.g., Berlin will always be recorded with Germany), so we can create a hierarchy of Cities by Country.

You will not drag Company Name, which supplies the actual Member names, to the bottom of the Hierarchy—these elements are understood to be the Detail Members in the Hierarchy.

To create such a hierarchy in the Define Hierarchy dialog box:

  1. Select and Drag Country under Customers in the Hierarchy window on the right; then select and drag City under Country. You can also use the buttons in the middle of the dialog to position Fields elements as a child or sibling of a selected item on the right, in the Hierarchy window.

  2. Select Create Aggregate Total Member in order to create a Total Customers member in the dimension.

  3. The dialog box, showing the Hierarchy you just created, will appear as follows.


Figure 5.    Define Hierarchy Dialog - Create Aggregate Total Member

“Ragged” and multiple hierarchies can also be created via Xchange. For more information, go to Creation of Ragged and Multiple Hierarchies. If ragged or multiple hierarchies are created, though, DO NOT use the Create Aggregate Member feature as this will create a useless aggregation of hierarchies.

  1. Click Next to show the Relational Dimension Summary of the Customers dimension you just created.


Figure 6.    Relational Dimension Summary - Customers

  1. Click Finish to return to the Dimensions dialog box, where Products is now listed.


Figure 7.    Dimensions Dialog - Three Dimensions

Although for this exercise you will not create any further Dimensions, the option exists for you to do so (by clicking Add, as shown previously).

  1. Leave the Dimensions dialog active and continue on to the next topic (Saving Your Work *.olt file).




Please sign in to leave a comment.
Powered by Zendesk