The first Dimension you are required to create in Xchange is the Measures dimension—typically, the source table where numeric information [Fact Data] is located—i.e., the figures that you want to analyze, report on, use for budgeting work-ups, etc. It is worthwhile to note that this source table is typically a transaction table or data warehouse table.
Figure 1. Dimensions Dialog
Do not enter a name into the Dimensions text box. The purpose of the Save and Load buttons will be covered shortly.
Figure 2. Select Dimension Dialog
Click Next. The Define Measures Table dialog box appears (see next figure).
Click on one of the listed tables (e g., Order Details) we will investigate to determine if it contains the Fact Data we are looking. The Fields in that table appear in the list box on the right.
Figure 3. Define Measures Table
Note that in this dialog you can name the Dimension in the text box at the bottom—here, Measures has been typed in.
While you are creating Dimensions, proCube allows you to “preview” the first few rows of data or records within selected source tables in the relational system:
Click the Preview button (shown in previous figure) to preview the fields in the table that you expect contains Fact Data.
A point to emphasize here is that the person creating Cubes must know and understand the underlying relational data, including where to look for Fact Data; and what tables to use in the creation of Dimensions.
Figure 4. Preview Table Order Details
Close this Preview window and click Next back in the Define Measures Table dialog. The Define Members dialog opens.
Select the item(s) in the Field for (Measures) on the left that you want as Member(s) for the Measures dimension—e.g., Quantity and UnitPrice. (Shift-select to select a consecutive group of members or Ctrl-select to select non-consecutive members.)
Click on the Add member button in the center of the dialog box (shown in the following figure; the Remove member button is below it). The Fields appear in the Members list, on the right:
Figure 5. Define Members Dialog
Allow Duplicates; Use Partial Field Member Name; Save Blank Members as _________; Conditional Retrieval; Define Roll ups; Define Drill Through; and Define Order. [Only the Conditional Retrieval and Define Drill Through check boxes are active when creating Measures.] are very important for the manner in which you “bring over” data from relational tables. More information can be found at Modifying Relational Dimensions. If you are building a cube in your own production environment, you should jump ahead to that point—use these features when you are initially creating Dimensions, not “after the fact,” when Modifying a Relational Dimension.
Click Next. The Relational Dimension Summary screen appears, encapsulating the choices you made in creating the Dimension—here, Measures.
Click Finish to return to the Dimensions dialog box, where the dimension Measures is now listed.
Figure 7. Dimensions Dialog - Measures
Note that for the Measures dimension, the field names of the chosen fields will become the Member names of the dimension—in this example, Quantity and UnitPrice. (For other Dimensions, the records contained in the fields will become the Member names.) Also be aware that the field names may not be as “obvious” as in this example—that is, rather than Quantity, the field name containing quantity figures in the Measures Table may be XYZ_Prod, or some other inscrutable name. Again, you must have some knowledge of the underlying tables.
Creating dimensions overview
Below is a schematic showing the succession of dialogs for the creation of the Measures and subsequent Dimensions, provided all check boxes in the Define Members dialog box have been enabled (those indicated by the parenthetical “if checked” in the figure). Before proceeding to create a 2nd, then a 3rd Dimension, you can look it over. (You have already completed the top loop in the preceding steps, i.e., creation of the Measures dimension.) This schematic will be useful for future reference in your own production environment.
Figure 8. Dimension Creation Process Diagram