Designing the Relational Target Warehouse

Designing a relational target warehouse in Warehouse Builder constitutes modeling relational (from 3rd normal form to dimensional) schemas that provide power to your data warehouse. Alternatively, you can also design a multidimensional system providing high-end analytics.

In this lesson, you model a small data warehouse consisting of a cube and two dimensions. The source of data for the target warehouse is flat files.

Approximately 60 minutes

Topics

This tutorial covers the following topics:

Designing Dimensions

Designing Cubes

Place the cursor over this icon to load and view all the screenshots for this tutorial. (Caution: Because this action loads all screenshots simultaneously, response time may be slow depending on your Internet connection.)

Note: Alternatively, you can place the cursor over each individual icon in the following steps to load and view only the screenshot associated with that step.

Overview

Oracle Warehouse Builder provides advanced relational and dimensional modeling capabilities. It enables you to create definitions for relational objects such as Tables, Views, Materialized Views, Sequences, and External Tables and dimensional target objects such as Dimensions and Cubes.

Oracle Warehouse Builder explicitly separates dimensional design from physical implementation. You can choose either a relational implementation or a multidimensional implementation for the dimensional objects using a simple click operation. Therefore, the same dimensional object design can be implemented as a relational target warehouse or a multidimensional (implemented in OLAP) warehouse.

In this lesson, you design a dimensional model implemented as a relational target warehouse. For relational modeling, Warehouse Builder supports the design and implementation of both Star and Snowflake schemas.

You also model a simple star schema consisting of a cube with foreign key references to two dimensions.

Back to Topic List

Before starting this tutorial, you should have:

1.

Downloaded the owbdemo_files.zip file and extracted the files to the directory on your computer's hard drive

2. Completed lessons 1 and 2. Continue logged in as rep_owner/rep_owner.

Back to Topic List

Defining a Target Warehouse Module

To begin designing the model of your target warehouse, you can start by defining a target module. Target modules contain the metadata definition of the target objects you design. Every target module must be mapped to a target user schema.

Therefore, before you create a Warehouse Builder target module, you need to create a target schema that will physically store your target objects on deployment. Then, you make sure that the target module references this target schema by assigning an appropriate location.

To set up your target warehouse module, you perform the following steps:

Create a Target User
Create a Target Module

Create a Target User

When you log in to Warehouse Builder Design Center as repository owner, Warehouse Builder displays the Global Explorer in the lower-right corner of the console. You can define the metadata security strategies according to your implementation requirements in this Global Explorer panel.

You will now create a target user by performing the following steps:

Note: Alternatively, you can also use the Repository Assistant to create this target user.

1.

In the Global Explorer panel, expand the Security node. Right-click the Users node and select New.

Note: You must save or revert your changes before creating a new user. To save previous changes, select Save All from the Design menu or click on the toolbar.

Place the cursor over this icon to see the image

The Register Warehouse Builder Users Wizard is launched. Click Next on the Welcome page.

 

2.

On the Select DB user to register page, you can select from the available list of database users or create a new one that will be automatically registered as Warehouse Builder target user.

Click on Create DB User to create a new target user.

Place the cursor over this icon to see the image

 

3.

To be able to create a new target user, you need to have SYSDBA privileges. In the Create Database User dialog box, enter sys as username and sys or <your sys account password> as the password.

Enter EXPENSE_WH/EXPENSE_WH as the username/password for the new DB user. Accept the defaults for the tablespace properties. Click OK.

Place the cursor over this icon to see the image

Observe EXPENSE_WH has been added to the Selected Users list. Click Next.

 

4.

On the Check user as target schema page, ensure that Used as target schema is selected for the EXPENSE_WH user. Click Next.

Place the cursor over this icon to see the image

The Target User Password dialog box launches. Re-enter the same password, expense_wh that you specified in the Create Database User dialog box for new DB user. Click OK.

Place the cursor over this icon to see the image

On the Summary page, examine the details and click Finish. The Register Users Progress window will show the task progress.

 

5.

Examine the Users node. You find the EXPENSE_WH user added to the list of existing target users.

Also, in the Connection Explorer panel, expand the Oracle Locations node and note that a new location, EXPENSE_WH_LOCATION, has been added.

Note: Locations define information about the database schema or target tool where you will be deploying objects. Locations are specific to a type of module, such as Oracle Database, SAP, or flat file.

Place the cursor over this icon to see the image

 

You have created a new target schema, EXPENSE_WH, which will physically store your target warehouse objects.

Back to Topic

Create a Target Module

You now create a target module that points to the new target schema.

1.

In the OWB_DEMO project, expand the Databases node, right-click the Oracle node, and select New.

Place the cursor over this icon to see the image

The Create Module Wizard is launched. Click Next on the Welcome page.

 

2.

On the Name and description page, enter EXPENSE_WH as the name of the module. Accept Development as the module status and ensure that Warehouse Target is selected as the module type. Click Next.

Place the cursor over this icon to see the image

 

3.

On the Connection information page, from the Location drop-down list, select EXPENSE_WH_LOCATION as the location of the module. Observe that all the relevant connection details are already specified. This is because Warehouse Builder derives the location information directly from the user you created. Click Next.

Note: Before you selected EXPENSE_WH_LOCATION from the Location drop-down list, EXPENSE_WH_LOCATION1 was selected by default. This is because whenever you try to create a module, Warehouse Builder automatically assigns a location with _LOCATION<no> suffixed to the module name.

You have selected the location, EXPENSE_WH_LOCATION, which was created when you created the EXPENSE_WH target user.

Place the cursor over this icon to see the image

On the Summary page, examine the details and click Finish.

 

4.

Observe that the EXPENSE_WH target module has been added under the Oracle node of the OWB_DEMO project. Expand EXPENSE_WH and examine its contents.

Place the cursor over this icon to see the image

Back to Topic

Until now, you have created an empty target module EXPENSE_WH that maps to an empty target schema EXPENSE_WH. Now, you start designing your target objects in this target module.

Back to Topic List

Creating External Tables

In the previous lesson, you created a flat file module in which you imported and sampled two flat files. To move and transform data from flat files into relational or dimensional targets, you can use either a flat file operator or an external table. External tables enable you to represent data from file sources in relational table format.

External tables allow you to directly query flat files from the Oracle database and also query, join, transform, and constrain flat files.

Using external tables to access source data from nonrelational sources, offers a number of advantages:

SQL Support and Heterogeneous Joins: When you use external tables, you can query and transform data contained in the flat files directly; SQL code is generated by Warehouse Builder. In addition, heterogeneous joins between flat files and relational tables are enabled.

 

Enabling Parallel SELECT Statements: You can get parallel access to the file through the table. For example, SELECT statements can be run in parallel thereby optimizing performance.

 

Eliminating Flat-File Staging Tables: When you use a flat file operator, Warehouse Builder generates SQL*Loader code and you need a relational staging table to load the data. External tables eliminate the need for a staging table thus reducing the processing time to load flat-file data and the need for extra storage space.

In Warehouse Builder, you can define external tables on top of flat-file structures, deploy external tables to the database, and use external tables in mappings.

Now, you create two external tables that will point to the two previously sampled flat files.

1.

In the Project Explorer panel, expand OWB_DEMO. Expand Databases > Oracle > EXPENSE_WH. Right-click External Tables and select New.

Place the cursor over this icon to see the image

The Create External Table Wizard is launched. Click Next on the welcome page. The wizard guides you through the creation of an external table.

 

2.

On the Name and Description page, enter EXPENSE_CATEGORIES as the name of the external table. Click Next.

Place the cursor over this icon to see the image

 

3.

On the File Selection page, select EXPENSE_CATEGORIES_CSV and click Next. This file, which you sampled in the previous lesson, contains the metadata for the external table.

Place the cursor over this icon to see the image

 

4.

On the Locations page, you select the default location that is used by the external table. Select SOURCE_LOCATION from the location drop-down box. Click Next.

Place the cursor over this icon to see the image

On the Summary page, review the details and click Finish. The wizard creates the external table. In Project Explorer, you see EXPENSE_CATEGORIES added to the External Tables node.

Now, you repeat the same steps to create an external table for the export_csv file.

5.

Right-click External Tables and click New. Click Next to skip the welcome page of the Create External Table Wizard. On the Name and Description page, enter EXPENSE_DATA as the name of the external table. Click Next.

Place the cursor over this icon to see the image

 

6.

On the File Selection page, select EXPORT_CSV and click Next.

Place the cursor over this icon to see the image

 

7.

On the Locations page, you select the same default location that you selected for the previous external table. Select SOURCE_LOCATION from the location drop-down box. Click Next. On the Summary page, review the details and click Finish. The wizard creates the external table.

Place the cursor over this icon to see the image

Observe, that both external tables are added to the target module.

Place the cursor over this icon to see the image

 

8.

You need to configure the physical file system details for the two external tables. Right-click EXPENSE_CATEGORIES and select Configure.

Place the cursor over this icon to see the image

In the Configuration Properties dialog box, right-click Data Files and select Create. Accept the default name, NEW_DATAFILE_1.

Place the cursor over this icon to see the image

 

9.

On the right side in the Configuration Properties dialog box, select SOURCE_LOCATION for the Data File Location property. For the Data File Name property, enter expense_categories.csv. Click OK.

Place the cursor over this icon to see the image

 

10.

Repeat the same steps to configure the EXPENSE_DATA external table. The configuration should look as shown in the screenshot.

Place the cursor over this icon to see the image

Click OK.

You have finished creating two external tables pointing to the two sampled flat files.

Back to Topic List

Designing Dimensions

Dimensions are the primary organizational unit of data in a star schema. Examples of some commonly used dimensions are Customer, Product, and Time.

A dimension consists of a set of levels and a set of hierarchies defined using these levels. Users often analyze data by drilling down on known dimension hierarchies. The query performance is improved because the query optimizer makes smart decisions about choosing what data to use.

To create a dimension, you must define the following:

In this section, you perform the following:

Examine the Predefined PRODUCTS Dimension in the Data Object Editor
Create REL_TIME Dimension Using the TIME Dimension Wizard
Create REL_CATEGORY Dimension Using the Wizard

Examine the Predefined PRODUCTS Dimension in the Data Object Editor

This topic introduces you to a basic example of a dimension. Before creating dimensions, you examine an existing one to understand the basic design of a dimension.

Let us examine the predefined PRODUCTS dimension for its dimension attributes, levels, and hierarchies.

1.

In the Project Explorer panel, expand OWB_DEMO > Databases > Oracle. Expand SALES_WH, and then expand Dimensions. Right-click PRODUCTS and select Open Editor.

Note: Alternatively, you can also double-click PRODUCTS.

Place the cursor over this icon to see the image

The Data Object Editor is launched. The Data Object Editor is the single interface where you can easily design, create, and manage a variety of database or dimensional objects. The Data Object Editor is a central place where you can build or modify various warehouse schema designs through the use of diagrams and property sheets. This is how the interface looks:


Place the cursor over this icon to see the image

As you observe there are numerous panels, but you focus on the Dimensions Details panel to understand the details of a dimension.

 

2.

To view the dimension's attributes, click the Attributes tab in the Dimension Details panel.

Observe that the dimension is assigned a sequence, PROD_DIM_SEQ, to populate a dimension key when loading data.

Also, note that one of the attribute ID has been identified as a Surrogate identifier and SOURCE_ID attribute is assigned as the Business identifier.

Note: The dimension key column is the primary key for the dimension and implements the surrogate identifier of each level. Using a sequence, you can populate the dimension key with unique values.

A surrogate identifier uniquely identifies each level record across all the levels of the dimension and is loaded from a sequence, as specified in the dimension key field.

A business identifier is an attribute that is always derived from the natural key of the data source.

A parent identifier is also available when you want to use value-based hierarchies.

In the Descriptor column, the dimension attributes marked as Long Description and Short Description are the ones used to display the dimension members in BI Beans.

Click in the Name column and add two DATE attributes, VALID_FROM_DATE and VALID_TO_DATE, with the following details, as shown in the screenshot.

Place the cursor over this icon to see the image

 

3.

To view the dimension levels and level attributes, click the Levels tab in the Dimension Details panel. Observe that there are four levels and each level has an applicable set of attributes, for example PRODUCT level implements all the dimension attributes as level attributes, except the two date attributes that you added in the previous step.

To include the two date attributes as applicable attributes for the PRODUCT level, select the Applicable check boxes for VALID_FROM_DATE and VALID_TO_DATE attributes, as shown in the screenshot.

Note: A level is not required to implement all dimension attributes. Implementing a dimension attribute on a level enables you to store this information on a level.

In a relational implementation, a level attribute becomes a column in a table. Level attributes do not have to have the same name as the dimension attribute, but Warehouse Builder defaults it as such in the panel.

When defining the levels you need not worry about the order in which you enter the levels. The levels are only organized in the hierarchies.

Place the cursor over this icon to see the image

 

4.

To view the dimension hierarchy, click the Hierarchies tab in the Dimension Details panel. Observe that there is only one hierarchy, PROD_STD, with levels arranged in a particular order (top down in the panel list).

The Default check box allows you to select a hierarchy that will display when a user queries. So it is important to pick the most commonly used hierarchy for this.

Place the cursor over this icon to see the image

 

5.

When you define a dimension, you can also specify the slowly changing dimension strategy.

Slowly Changing Dimensions determine how you store historical changes to your dimension values. You can choose between the three strategies, as shown in the screenshot.

Click the SCD tab to view the slowly changing dimension strategy for the PRODUCTS dimension.

Place the cursor over this icon to see the image

Note: SCD Type 2 and 3 are available with the Enterprise ETL option of OWB 10gR2. With Core ETL features, SCD Type 1, that is, Do not keep history option, is only available. If you are using the Core ETL features package and you are not able to perform this step, you can still continue with the next steps in this lesson and later lessons. This step is only for your examination and does not impact the hands-on steps going forward.

If you want to store the complete change history, you would use a type 2 implementation. Select Type2: Store the complete change history. Notice that the Settings button is enabled. Click Settings.

In the Type 2 slowly changing policy dialog box, in the Record History drop-down list, you need to specify the attributes as:

For example, to record the change history whenever the LIST_PRICE, NAME and PACK SIZE of a product changes, set them as Trigger History attributes, as shown in the screenshot. Also, VALID_FROM_DATE is set as Effective Date and VALID_TO_DATE is set as Expiration Date attributes.

Place the cursor over this icon to see the image

Click Close in the Type 2 slowly changing policy dialog box.

 

6.

You design dimensional objects using Warehouse Builder, and then deploy them either in a relational form or in a multidimensional form to the database.

On the Storage tabbed page, you decide whether the designed dimension is implemented as relational or multidimensional and based on the type, Warehouse Builder generates appropriate storage type code.

Click the Storage tab to view the selected implementation for the PRODUCTS dimension. For a relational implementation, you can choose between Star, Snowflake, or Manual.

Place the cursor over this icon to see the image

The Data Object Editor also provides a Data Viewer that enables you to view and drill down into a dimension's data.

From the Diagram menu, select Close Window to exit the Data Object Editor.

In the following steps, you create two simple dimensions related to the Expenses scenario. You could create the dimensions in the Data Object Editor, but in this lesson you use wizards to create dimensions. This gives you a set of defaults and enables you to create a dimension with a set of easy-to-understand steps.

Back to Topic

Create REL_TIME Dimension Using the TIME Dimension Wizard

Time dimensions are used extensively in data warehouses to address time series, such as queries (for example: What is the expense for the quarter as compared to last year's quarter?).

A time dimension also consists of a set of levels and a set of hierarchies defined over these levels. You can use Warehouse Builder to create both fiscal and calendar time dimensions.

Warehouse Builder enables you to not only create but also populate time dimensions. When you create a time dimension using the wizard, Warehouse Builder automatically creates the mapping for you to populate the time dimension.

Also, the data loaded into the time dimension conforms to the OLAP standards regarding attributes for a time dimension.

Now, you perform the steps to create the REL_TIME dimension using the Time Dimension Wizard.

1.

You were previously examining the SALES_WH module. Now, expand EXPENSE_WH, right-click Dimensions and select New > Using Time Wizard.

Place the cursor over this icon to see the image

The Create Time Dimension Wizard is launched. Click Next on the Welcome Page.

 

2.

On the Name and Description page, enter REL_TIME as the name of the dimension. Click Next.

Place the cursor over this icon to see the image

 

3.

On the Storage page, select Relational Storage (ROLAP) to specify relational implementation of the dimension. Click Next.

Place the cursor over this icon to see the image

 

4.

On the Data Generation page, you specify the range of time data that is required for your warehouse. This information is used to generate a mapping that populates the time dimension. Within this mapping, the dates you enter are added as parameters, allowing you to rerun this mapping with dates at a later stage.

For Start Year, enter 2003 and for Number of Years enter 3. Ensure that Calendar is selected, as shown in the screenshot.

Note: A hierarchy defines hierarchical relationships between adjacent levels in a time dimension. A time dimension can have one or more hierarchies. Each hierarchy must be either a fiscal hierarchy or a calendar hierarchy.

If your time dimension contains fiscal data, you must specify the start month and day for the fiscal year and start
day for the fiscal week. You also need to specify month and quarter boundary convention that can be 445 or 544.

Place the cursor over this icon to see the image

 

5.

On the Levels page, select Normal Hierarchy and select all the levels: Calendar Year, Calendar Quarter, Calendar Month and Day. Click Next.

Place the cursor over this icon to see the image

 

6.

On the Pre Create settings page, examine the details and click Next.

Place the cursor over this icon to see the image

A progress bar shows the progress as the wizard creates the necessary objects.

Place the cursor over this icon to see the image

On successful completion, click Next. On the Summary page, examine the details again and click Finish.

Observe that the wizard has generated four objects necessary for a fully functional TIME dimension: REL_TIME dimension object, REL_TIME_SEQ sequence that populates the surrogate ID of the time dimension levels, REL_TIME table to support the relational implementation of the time dimension that physically stores the time data, and most importantly the REL_TIME_MAP mapping to populate the time dimension.

Place the cursor over this icon to see the image

If you are curious to know how REL_TIME_MAP looks like, double-click REL_TIME_MAP under the Mappings node to launch the Mapping Editor.

From the View menu, select Auto Layout. You can see the expanded mapping. You can scroll or select the zoom options from the View menu to view the mapping appropriately. Warehouse Builder creates this complex mapping automatically for you, there by enhancing the productivity and saving your time.

Place the cursor over this icon to see the image

From the Mapping menu, select Close to exit the Mapping Editor.

You have finished creating the REL_TIME dimension. Next, you will create REL_CATEGORY dimension using the wizard.

Back to Topic

Create the REL_CATEGORY Dimension Using the Wizard

1.

In the EXPENSE_WH module, right-click Dimensions and select New > Using Wizard.

Place the cursor over this icon to see the image

The Create Dimension Wizard is launched. Click Next on the Welcome Page.

 

2.

On the Name and Description page, enter REL_CATEGORY as the name of the dimension. Click Next.

Place the cursor over this icon to see the image

 

3.

On the Storage Type page, select Relational Storage (ROLAP) to specify the relational implementation of the dimension. Click Next.

Place the cursor over this icon to see the image

 

4.

On the Dimension Attributes page, you find three predefined columns, ID, NAME, and DESCRIPTION. Note that for the ID attribute, the identifier is set as Surrogate and for the Name attribute the identifier is set as Business.

You can change these attributes to your liking, but must have a surrogate and a business identifier in the list. The surrogate identifier must have the NUMBER data type.

For the NAME attribute, change Length to 30. Click Next.

Place the cursor over this icon to see the image

5.

On the Levels page, specify the following levels for the default hierarchy in the dimension:

Place the cursor over this icon to see the image

Click Next.

 

6.

On the Level Attributes page, for each level that you have defined, you select the dimension attributes that apply to that level. Accept the default selection and click Next.

Place the cursor over this icon to see the image

 

7.

On the Slowly Changing Dimension page, leave the default slowly changing dimension option selected. Click Next.

Place the cursor over this icon to see the image

Note: SCD Type 2 and 3 are available with the Enterprise ETL option of OWB 10gR2. With Core ETL features, SCD Type 1, that is, Do not keep history option, is only available.

If you select any other policy, Warehouse Builder defaults the implementation to the lowest level of the hierarchy and creates the date attributes for you.

Click Next on the Pre Create Settings Page. The Dimension Creation Progress page appears. On successful completion, click Next. Click Finish on the Summary page.

Observe that REL_CATEGORY dimension has been added to the EXPENSE_WH target module. With the dimension, Warehouse Builder also creates a table that implements the dimension in a star schema and also creates a sequence to load the ID column of the dimension.

Place the cursor over this icon to see the image

Back to Topic

Back to Topic List

Designing Cubes

Cubes contain measures and link to one or more dimensions. The axes of a cube contain dimension values and the body of the cube contains measure values. Most measures are numeric and additive. For example, sales data can be organized into a cube whose edges contain values for Time, Product, and Customer dimensions and whose body contains values from the measures Value sales and Dollar sales.

In a relational implementation, a cube is linked to dimension tables by foreign key constraints and consists of a set of measures. To create a cube, you must define the following:

You perform the following steps:

Let us examine the predefined SALES cube for its measures and dimensionality.

Examine the Predefined SALES Cube in the Data Object Editor

This topic introduces you to an example of a cube. By examining an existing cube that has been predefined for you, you will understand the basic design of a cube. Later in the topic, you create a simple cube with reference to expenses scenario using the wizard.

1.

In the Project Explorer panel, expand OWB_DEMO > Databases > Oracle. Expand SALES_WH, and then expand Cubes. Right-click SALES and select Open Editor.

Note: Alternatively, you can also double-click SALES.

Place the cursor over this icon to see the image

The Data Object Editor launches.

 

2.

In the Dimensional panel in the middle, scroll up or down to understand the design diagram. You will observe that the SALES cube is linked to five dimensions: PRODUCTS, CHANNELS, CUSTOMERS, PROMOTIONS, and TIMES.

Place the cursor over this icon to see the image

 

3.

To examine the dimensionality of the SALES cube, click the Dimensions tab in the Cube Details panel in the lower-right corner of the Data Object Editor. Observe that the SALES cube is linked to levels within the five dimensions.

For example, SALES cube refers to PRODUCT level of the PRODUCTS dimension. The Role column displays in a drop-down list, the dimension roles (if defined previously) that the selected dimension contains.

Place the cursor over this icon to see the image

 

4.

To examine the measures of the SALES cube, click the Measures tab in the Cube Details panel. Note that there are three measures and all of them are numeric. These measures can be aggregated to determine the total sales cost, amount, or quantity across a period of time or for a particular customer or for a particular product.

Place the cursor over this icon to see the image

 

5.

Click the Aggregation tab to define the aggregations that must be performed for each dimension that the cube references.

You select the aggregate function that is used to aggregate data. You can also precompute measures along each dimension that the cube references. The default aggregate function is SUM.

You specify the following:

Place the cursor over this icon to see the image

 

6.

You can implement a cube in a relational form or a multidimensional form in the database. In relational implementation, a relational table stores the cube data. When a cube is implemented in a multidimensional environment, the cube data is stored in an analytic workspace.

If you want to modify the physical implementation of the cube, click the Storage tab in the Cube Details panel. You can select between relational or multidimensional implementation.

For relational implementation of the cube, you can opt to create bitmap indexes. Having these indexes on the cube improves your performance at query time.

Place the cursor over this icon to see the image

As for dimensions, you can view the cube data in the Data Viewer. From the Diagram menu, select Close Window to exit the Data Object Editor.

Back to Topic

Create REL_EXPENSE Cube Using the Wizard

Now, you design a simple relational cube, REL_EXPENSE. This cube references the two dimensions, REL_TIME and REL_CATEGORY, which you created in previous steps. The wizard helps you create a cube in a few easy steps.

1.

You were previously examining the SALES_WH module. Now, switch to the EXPENSE_WH module, right-click Cubes, and select New > Using Wizard.

Place the cursor over this icon to see the image

The Create Cube Wizard is launched. Click Next on the Welcome Page.

 

2.

On the Name and Description page, enter REL_EXPENSE as the name of the cube. Click Next.

Place the cursor over this icon to see the image

 

3.

On the Storage Type page, select ROLAP: Relational Storage to specify relational implementation of the cube. Click Next.

Place the cursor over this icon to see the image

 

4.

On the Dimensions page, you select the dimensions that the cube will refer to. In the Available Dimensions list, expand Dimensions, and then expand EXPENSE_WH. Select EXPENSE_WH, and then click > to move the two dimensions REL_CATEGORY and REL_EXPENSE to Selected Dimensions list. Click Next.

Place the cursor over this icon to see the image

 

5.

On the Measures page, enter EXPENSE in the Name field. Accept the default data type. Click Next.

Place the cursor over this icon to see the image

On the Summary page, review the cube details and click Finish.

Observe that REL_EXPENSE cube is added under the Cubes node of the EXPENSE_WH module. To view the cube in the Data Object editor, right-click REL_EXPENSE and select Open Editor or double-click REL_EXPENSE. Examine the cube, and then close the Data Object Editor.

Place the cursor over this icon to see the image

From the Design menu, select Save All to commit your work.

In this lesson, you created two external tables mapped to the two previously sampled source flat files, two dimensions, REL_TIME and REL_CATEGORY, and a cube REL_EXPENSE linked to the two dimensions.

Place the cursor over this icon to see the image

Back to Topic

Back to Topic List

Summary

In this lesson, you designed a small relational star schema constituting a cube linked to two dimensions.

In this lesson, you've learned how to:

Create a target user schema and module

Create external tables pointing to sampled flat files

Create dimensions including TIME dimension using the wizard

Create a Cube using the wizard

A point to note is that you have designed the dimensional objects, their implementing objects and related sequences, and even some data loading programs (mappings), but they do not exist physically in the database as yet. This is a major advantage that Warehouse Builder provides by separating the logical or design model from the actual physical implementation.

To actually create or build the target warehouse, you need to go a step further and deploy the dimensions and the cube. You also need to design, deploy, and execute the ETL mappings that will extract data from the relational/flat file sources, transform the data in a desirable manner, and finally load the data in the dimensions and the cube tables. You will perform all this in the following lessons.

Back to Topic List

Place the cursor over this icon to hide all screenshots.