Creating the Measures Dimension

Next, we need to create all the Dimensions, beginning with the Measures dimension. To create the Measure dimension:

1.   Click Add.
NOTE: DO NOT type a name into the Dimensions text box.

Notes


The first Dimension you are required to create in OLAP Exchange^® is the Measures dimension —this is done because the Measure dimension when coming from OLAP Exchange represents the table where numeric values are found, which typically is a transaction table or data warehouse table. Links from that table will be shared to auxiliary tables to make up the rest of the Dimensions.

The Measure dimension needs to be the table that contains the numerical data that will be brought in the PowerOLAP®.

Important


The Measure Dimension is selected by default in the Select Dimension dialog box when you create your first Dimension for the Cube (circled, following).

2.   Click Next.

Define Measures Table dialog box will be displayed (see next figure). This will give the user a list of all the tables that can be selected to be the Measure dimension. Each table is predefined with the schema that it belongs to. (Take note that the tables that will be displayed in left hand pane of the succeeding dialog box are dependent on the selected data source.)

3.   Click on one of the listed tables (e g., Order Details).

The Fields in that table appear in the list box on the right.

Note that in this dialog box you can name the Dimension in the text box at the bottom—here, Measures has been typed in.

While you are creating Dimensions, PowerOLAP allows you to "preview" the first few rows of data or records within selected source tables in the relational system:

4.   Click the Preview button (beside the Dimension Name text box) to preview the fields in the table that you expect contains Fact Data.

Important


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.

This view shows a subset of the data in this table: we confirm that it contains the field(s) we want for Measures—Quantity and UnitPrice.

5.   Close this Preview window.

6.   Click Next back in the Define Measures Table dialog box.    
The Define Members dialog box appears.

7.   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.)

8.   Click on the Add Member button (plus icon).
The Fields appear in the Members list, on the right:

Note: Refer to the buttons at the middle of the screen: the Add Member button=; while the Remove Member button=.

Important


Note the checkboxes Allow Duplicates; Define Rollups, Partial Members, Define Order, Name Blank Members as _________; are not available option when creating the Measures dimension. Only Item Count, Distinct Item Count, Define Drill Through , Conditional Retrieval and Define Indexed Field for Incremental Load checkboxes are active when creating the Measures dimension. These check boxes, which are very important for the manner in which you "bring over" data from relational tables, are discussed further under the Section on, Modify Relational Dimension. 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.

Options available in Define Member dialog box (Measures dimension):

• Item Count
  - Allows a user to create a new Member aside from the selected Members for the Measure dimension and it will create an incrementing index which keeps track of the number of records that are brought into PowerOLAP^® [Not enabled for this demonstration].
• Distinct Item Count
  - Distinct item count has the same concept as Item Count, however it does it distinctly. In other words, if there are numerous quantities or unit prices for a particular Customer for a particular Product the item count will count how many there are. The Distinct item count will simply set the value to 1 if there are any and 0 if there are none. [Not enabled for this demonstration.]
• Define Drill Through
  - Allows user to define additional elements coming from the table selected when doing a drill through. If enabled, when Next button is selected, another screen will appear that will allow user to pick the elements that will appear in the drill through column [Enabled for this demonstration.]

Allows administrator or whoever building the OLAP Exchange cube to determine what fields from the measure table can be accessed by users and restrict them from viewing sensitive information whenever doing a drill through of elements. Only fields that were defined in this interface will be available to be selected as additional drill through values by users.

• Conditional Retrieval
  - Allow user to specify the Where clause or Where condition of the OLAP Exchange query that is built using this technique. Users can bring back particular data based on a condition. Example of condition can be based on time, certain ID range, groupings or attributes of the table and other things. [Not enabled for this demonstration.]
• Define Indexed Field for Incremental Load
  - This feature allows users to specify a field within the table that access the index field. The index field can later be used in the OLAP Exchange Process Cube option to only build or bring in data from a certain time period or from the last time OLAP Exchange rebuild occur.

PowerOLAP^® will only grab the latest transactions which are usually much smaller than an entire transactional table. This has an effect on the speed of the performance when rebuilding/processing. [Not enabled for this demonstration.]

To complete Measure dimension creation, continue with the succeeding topic:

• Defining Drill Through