Procedure B.3. To define an Application:

1. On the [Warehouse Explorer], select the Applications node, or select a node corresponding to a defined application.

2. Double-click on the selected node,

   –or–

   Click on the Properties toolbar button on the [Warehouse Explorer]. This opens a form through which you can enter the necessary application information.

   • Name. . The name of the Application.

   • Caption. . The caption of the Application.

   • Description. . A textual description of the Application.

   • Short Name. . A short name for the Application; this name is used in the SQL DDL generation routines.

   • RDBMS. . The target RDBMS that will be used for the warehouse schema. The demo installation is limited to the Oracle RDBMS only.

3. Use the toolbar or menu to edit, save, or delete records as needed.

4. Once the Application properties have been defined, click on the Dimensions or Schemas command buttons to define the Dimensions and Schemas that belong to this Application.

Procedure B.4. To Define a Dimension:

1. From the [Warehouse Explorer], navigate to the desired Application node, and select the Dimensions node of the application. Once the Dimensions node has been selected, click on the Properties button on the [Warehouse Explorer] toolbar.

   –or–

   If the Application definition form is currently open, select the desired application and click on the Dimensions command button.

2. In the data form that appears, enter the following information to define a Dimension:

   • Name. . The physical table name to be used in the database. Follow the naming guidelines suggested by the RDBMS vendor.

   • Caption. . The business or logical name of the Dimension, used by warehouse users to refer to the Dimension.

   • Description. . The standard definition of the Dimension.

   • Short Name. . A short name for the Dimension, used in the generation of the primary key column and primary key constraint definitions for the Dimension.

   • Tablespace. . The name of the Tablespace where the physical table will reside. This is an Oracle-specific requirement.

   • Summary Of. . Specifies the Base Table reference of a Summary Table. This is a noneditable property.

   • Custom Of. . Specifies the Core Table reference of a Custom Table. This is a noneditable property.

   • Usage Type. . Indicates if a warehouse object is used as a Fact or as a Dimension. This is a noneditable property.

   • Implementation Type. . Indicates how the Dimension is implemented. Valid values are Table, View, or Synonym, with Table as the default value.

     The two other implementation types are used for the Core and Custom Dimensions or for Multiple-Referenced Dimensions.

   • Key Option. . Indicates how keys are to be managed for this Dimension. Valid values are Overwrite, Generate New, and Create Version.

   • Source. . Indicates the data source for this Dimension. This field is provided for documentation purposes only.

   • Aggregate?. . Indicates whether or not the physical table is a summary. Used only by the R/OLAPXL front-end product.

   • Online?. . Indicates whether the physical table is actually populated correctly and is available for use by the users of the data warehouse. Used only by the R/OLAPXL front-end product.

3. Click on the Fields button to enter the Dimension Attributes. For each attribute, specify the following information:

   • Seq. . If you want the fields in the generated SQL DDL to follow a particular order, specify a sequence number for the fields.

   • Field Name. . The name of the field as will be used in the physical table.

   • Caption. . A descriptive name of the field.

   • Data Type. . The appropriate data type for the field, as supported by the target RDBMS.

   • Index Type. . Specify the type of index to be used on this attribute. If this attribute will not be indexed, leave it blank.

   • Key?. . Indicate if this is a Key field in the Dimension table.

   • Add?. . Indicate if this field is an Additive Fact. This is not applicable to Dimensional Attributes.

   • Hide?. . Indicate if this field is to be hidden from warehouse users. This property is used for common audit columns (e.g., last update date and last update user).

   • UnGrp?. . Used only by R/OLAPXL, this indicates if the field is to excluded from the normal grouping of rows in the query results. In SQL terms, it has the effect of removing the field from the Group-By clause; instead, the MIN function is applied to the field.

   • Format. . The format of this field.

   • Description. . A description of this field.

   Note

   Due to the number of properties required to fully define a field, you will have to scroll to the right of the Field table to see all properties.

   To scroll down the list of fields, use the Page Up and Page Down keys on your keyboard. You can also click on the leftmost column of the field list and use the Up and Down Arrows to navigate up and down the list.

4. Click on the Hierarchies button to define the drill hierarchies for the Dimension. If the fields within a dimension have a hierarchy, define the hierarchy level for each field, where Level 1 is the most detailed level. This definition is not necessary for generating the SQL DDL but is required to create the metadata used by the R/OLAPXL front-end product.

Procedure B.5. To Define a Schema

1. From the [Warehouse Explorer], navigate to the desired Application node, and select the Schemas node of the application. Once the Schemas node has been selected, click on the Properties button on the [Warehouse Explorer] toolbar.

   –or–

   If the Application definition form is currently open, select the desired application and click on the Schemas command button.

2. In the data form that appears, enter the following information to define a Schema:

   • Name. . The physical fact table name to be used in the database. Follow the naming guidelines suggested by the RDBMS vendor.

   • Caption. . The business or logical name of the Schema as the data warehouse users know it.

   • Description. . The standard definition for the Schema.

   • Short Name. . Used in the generation of the primary key column and primary key constraint definitions for the Schema Fact table.

3. In the table provided, specify the Dimension tables that will be used with this Schema.

   • Use the Table drop-down list box to select a table.

   • Use the Seq column to specify the sequence in which these tables will appear in the generated SQL DDL.

     Note

     Foreign key constraints in the Fact tables will be implemented based on the specified Schema Dimensions.

     Similarly, primary key constraints will be based on the Dimensions that are specified to be part of the granularity of the Schema.

   • Enable the Grain? checkbox if the Dimension table influences the grain of the Fact table.

4. A default Fact table definition (with the same name, caption, description, and short name as the schema) is automatically created after a Schema is successfully defined.

5. Click on the Facts button to enter the Schema facts and modify the Default Fact Table Definition. Fill up the field table to specify the attributes of the Fact table. Note that the process for defining a Fact field is similar to the process for defining a Dimension field.

Procedure B.6. To Define a Custom Dimension:

1. From the [Warehouse Explorer], navigate to the desired Dimension node. Select the Custom Dimensions node for that Dimension, and click on the Properties button in the [Warehouse Explorer] toolbar.

2. In the data that appears, define the Custom Dimension as you would a Core Dimension.

   Note that WAREHOUSE DESIGNER will automatically enter the appropriate Core Dimension table reference in the Custom Of field.

Procedure B.7. To Define a Custom Schema:

1. From the [Warehouse Explorer], navigate to the desired Schema node. Select the Custom Schemas node for that Schema, and click on the Properties button in the [Warehouse Explorer] toolbar.

2. In the data form that appears, define the Custom Schema as you would a Core Schema.

   Note that at least one Custom Dimension must be selected as part of the Schema definition.

3. Click on the FACTS command button to open the DW Field window. On this new window, modify the table definition as needed, then define the facts for this Custom Schema.

Procedure B.8. To Define an Aggregate Dimension:

1. From the [Warehouse Explorer], navigate to the desired Dimension node. Select the Aggregate Dimensions node for that Dimension, and click on the Properties button in the [Warehouse Explorer] Toolbar.

2. Enter data as you would for a Base Dimension definition. The Summary Of table reference will be populated automatically by WAREHOUSE DESIGNER.

Procedure B.9. To Define an Aggregate Schema:

1. From the [Warehouse Explorer], navigate to the desired Schemas node. Select the Aggregate Schemas node for that Schema, and click on the Properties button in the [Warehouse Explorer] Toolbar.

2. Enter data as you would for a Base Schema definition. Use either an Aggregate Dimension to define the Schema, or use base-level Dimension tables if applicable.

3. Click on the FACTS command button to open the DW Field window. On this new window, modify the table definition as needed, then define the facts for this Aggregate Schema.