Creating the Second Dimension
The preceding section included the creation of the first dimension—the Measures dimension (recall that you can name it anything you like), which contains the Fact Data you want to analyze, report on, use for budgeting, etc.
You will want to add additional dimensions to your cube from underlying tables in the source relational database. These may be tables of items that have been ordered or have a unit price, or are in some way are described by the Measures dimension. As a simple example, working with the sample database in the preceding steps, you brought over Quantityand Unit Price as Members of the Measures dimension. What, in this database, are you likely to know the quantity of? How about Products? And since this will ultimately be aSalesOrder cube, you would want to know the quantity of products ordered by your customers—thus, you would also want a Customers dimension in the cube. These are the questions you will logically want to consider in the creation of your cube.
We will proceed with this example database, adding other Dimensions—Products (shown in the following steps) and then Customers.
To create the second dimension:
Click Add in the Dimensions dialog. Note a slight difference in the Select Dimension dialog, as shown in the following figure.
Figure 1. Select Dimension Dialog
There now appears a radio button for New Dimension, which is the default selection. Notice, too, the radio button beside it named Shared Dimension. This radio button appeared as an option, too, when you began creating the Measures dimension. A “Shared Dimension” is a Dimension that has already been defined using Xchange when building a cube, but which you now want to use in creating another cube.
For example, if you had created a Sales Order Cube using Customers as a dimension—which is shown in the following steps—and, later, you want to create an Accounts ReceivableCube which also uses Customers as a dimension from the same relational source database, you would click Shared Dimension when creating Customers for the second (etc.) Cube.
The following concerns a rather simple star schema—one link that will take you from a “central” table out to another table (refer to Preparing Relational Data).
Using the example database, with New Dimension checked, proceed as follows:
Click Next. The Define Links dialog box appears.
Figure 2. Define Links Dialog
On the right is a depiction of the Measures table, with its fields listed. You will make a link from a field in this Measures table (Order Details, in the example) to a field in the table you select as the basis of a new dimension.
Along the toolbar there is a Preview button, , which enables you to examine the underlying table to see if it contains the data you want for this Dimension.
Double-click on a table listed on the left— it will appear in the right pane, beside the Measures table. [Alternatively, single-click on a table and use the Add Selected Tables button, the Star button in the top left.] For the example file, double-click on Products.
Note that the Table name (e.g., Products) now appears in the Dimension Name text box at the top right; this name can be changed by typing another name into the text box.
Define the link between the two tables by clicking on a field in the Measures table and dragging it to the same field in the new Dimension’s table: a line appears, representing the link. For the example, click on ProductID in the Order Details table and drag it to ProductID in the Products table.
To avoid errors, all the records in the Measures table must contain names that appear in the Dimension table you are building. In this example, the Order Details table should not contain products that do not exist in the Products table. Otherwise, there will be errors when Xchange tries to bring in the data. These errors will be reported in an Error File. For more information, go to Error File Reporting.
The Define Links dialog — with the link “drawn” between the Measures table and the table selected for the new Dimension—will appear as follows:
Figure 3. Define Links Dialog - with Links Drawn
The linked fields in the two tables may have different names. You may have to return to Preview the Measures table to see the information in particular fields—do so by “deleting” all but the Measures table, selecting the Measures table, then clicking on the Preview button. You can then repeat the process so that the table(s) appear again on the right. FURTHER, you can easily redraw a link: simply draw a new one between tables, and the incorrectly drawn one will disappear.
Click Next. The Define Members dialog opens.
Figure 4. Define Members Dialog
Now, each unique record in the listed field will be a Member in the Dimension you are creating. If you select more than one Field for inclusion in the Members list box for non-Measures dimensions (2nd, 3rd, etc. dimensions), the resulting Members names will form a concatenation of the field records in each row of the table, separated by a space. For example, if ProductName and ProductID (which logically would be a number) were selected, you might see resulting member names like 55 chili or 73 pasta, etc.
Note that now all check boxes are active at the bottom right. Again, the use of these check boxes will be discussed in the next Section; if you are consulting this guide to proceed in a real production environment, you will want to use these check boxes now.
Click Next. The Define Aliases dialog opens.
Figure 5. Define Aliases Dialog
The Define Aliases dialog allows you to create Alias Groups from fields within the relational table for the Members in this Dimension. (The meaning and use of Aliases and Properties, which follows, are discussed in the proCube User Guide .)
Aliases must be unique within a Dimension—i.e., the selected Alias field must have unique values for each record. In addition, an Alias name for a Member can not be the same name as a different Member in the Dimension.
For present purposes, we will not create an Alias Group for Products, though we will demonstrate Defining Aliases for the 3rd Dimension (Customers).
Click Next. The Define Properties dialog box appears.
Figure 6. Define Properties Dialog
For present purposes, we will not create a Properties Group for Products, though Defining Properties is demonstrated in the creation of the 3rd Dimension (Customers).
Figure 7. Define Hierarchy Dialog
Other fields can be used in creating a Hierarchy—for present purposes, if you are working with the example file, do not create a Hierarchy. This too will be demonstrated for the creation of a Customers dimension.
DO consider whether it is logical in your Dimension to have an Aggregate Total Member. There are instances when a Total Member makes no sense— for example, in a Versiondimension whose Members are Actual, Budget1, Budget2, etc., a total of all these will be meaningless.
Click Next to show the Relational Dimension Summary of the Products dimension you just created.
Figure 8. Relational Database Summary - Products Dimension
The Relational Dimension Summary message box summarizes the options you selected in the creation of the Dimension.
Click Finish to return to the Dimensions dialog, where Products is now listed.
Figure 9. Dimensions Dialog - Measures and Products
After clicking Finish, proCube automatically determines whether the dimension is “valid”. For example, if Allow Reserved Characters has not been checked, and it is determined that Reserved Characters exist in one of the Members of a Dimension, the following message box appears:
Figure 10. Prompt for Reserved Characters