| Oracle® Warehouse Builder User's Guide 11g Release 1 (11.1) Part Number B31278-01 |
|
|
View PDF |
| Oracle® Warehouse Builder User's Guide 11g Release 1 (11.1) Part Number B31278-01 |
|
|
View PDF |
Warehouse Builder is also a design tool that enables you to design your data warehouse. Target schemas contain all the necessary data objects in your data warehouse such as tables, views, dimensions, and. In a traditional data warehousing implementation, there is typically only one target schema, which is the data warehouse target. You can design target schemas, both relational and dimensional, using the Data Object Editor.
This chapter includes the following topics:
The Oracle module contains nodes for each type of data object that you can define in Warehouse Builder. In the Project Explorer, under the Oracle node, expand the module node to view all the supported data objects.
Warehouse Builder supports relational and dimensional data objects. Relational objects, like relational databases, rely on tables and table-derived objects to store and link all of their data. The relational objects you define are physical containers in the database that are used to store data. It is from these relational objects that you run queries after the warehouse has been created. Relational objects include tables, views, materialized views, and sequences. You can also create optional structures associated with relational objects such as constraints, indexes, partitions, and attribute sets. For more information about these structures, refer to the online help.
Dimensional objects contain additional metadata to identify and categorize your data. When you define dimensional objects, you describe the logical relationships that help store the data in a more structured format. Dimensional objects include dimensions and cubes. This chapter provides specific information about each type of dimensional object and how they are used in Warehouse Builder.
In addition to relational and dimensional objects, Warehouse Builder supports intelligence objects. Intelligence objects are not part of Oracle modules. They are displayed under the Business Intelligence node in the Project Explorer. Intelligence objects enable you to store definitions of business views. You can deploy these definitions to analytical tools such as Oracle Discoverer and perform ad hoc queries on the warehouse. For more information about intelligence objects, see "Defining Business Intelligence Objects" in the Warehouse Builder Online Help.
Table 6-1 describes the types of data objects you can use in Warehouse Builder.
Table 6-1 Data Objects in Warehouse Builder
| Data Object | Type | Description |
|---|---|---|
|
Tables |
Relational |
The basic unit of storage in a relational database management system. Once a table is created, valid rows of data can be inserted into it. Table information can then be queried, deleted, or updated. To enforce defined business rules on a table's data, integrity constraints can be defined for a table. See "Using Tables" in the Warehouse Builder Online Help for more information. |
|
External Tables |
Relational |
External tables are tables that represent data from non-relational flat files in a relational format. Use an external table as an alternative to using a flat file operator and SQL* Loader. See "Using External Tables" in the Warehouse Builder Online Help for more information. |
|
Views |
Relational |
A view is a custom-tailored presentation of data in one or more tables. Views do not actually contain or store data; they derive their data from the tables on which they are based. Like tables, views can be queried, updated, inserted into, and deleted from, with some restrictions. All operations performed on a view affect the base tables of the view. Use views to simplify the presentation of data or to restrict access to data. See "Using Views" in the Warehouse Builder Online Help for more information. |
|
Materialized Views |
Relational |
Materialized views are pre-computed tables comprising aggregated or joined data from fact and possibly dimension tables. Also known as a summary or aggregate table. Use materialized views to improve query performance. See "Using Materialized Views" in the Warehouse Builder Online Help for more information. |
|
Sequences |
Relational |
Sequences are database objects that generate lists of unique numbers. You can use sequences to generate unique surrogate key values. See "Using Sequences" in the Warehouse Builder Online Help for more information. |
|
Dimensions |
Dimensional |
A general term for any characteristic that is used to specify the members of a data set. The three most common dimensions in sales-oriented data warehouses are time, geography, and product. Most dimensions have hierarchies. See "About Dimensions" for more information. |
|
Cubes |
Dimensional |
Cubes contain measures and links to one or more dimension tables. They are also known as facts. See "About Cubes" for more information. |
|
Advanced Queues |
Relational |
Advanced Queues enable message management and communication required for application integration. Currently, you cannot create advanced queues using Warehouse Builder. You can only import advanced queues that were exported into an .mdl file using a previous version of the product. |
|
Queue Tables |
Relational |
Queue tables are tables that store queues. Each queue table contains a payload whose data type can be an object type or You cannot create a queue table using Warehouse Builder. A queue table is imported as part of an advanced queue payload. |
|
Object Types |
Relational |
An object type is made up of one or more user-defined types or scalar types. See "About Object Types" in the Warehouse Builder Online Help for more information. |
|
Varrays |
Relational |
A varray is an ordered collection of elements. See "About Varrays" in the Warehouse Builder Online Help for more information. |
|
Nested Tables |
Relational |
A nested table complements the functionality of the varray data type. A nested table permits a row to have multiple 'mini-rows' of related data contained within the one object. See "About Nested Tables" in the Warehouse Builder Online Help for more information. |
Table 6-2 displays the data types you can use to create and edit columns.
Table 6-2 Data Types
| Data Type | Description |
|---|---|
|
Stores double-precision IEEE 754-format single precision floating point numbers. Used primarily for high-speed scientific computation. Literals of this type end with |
|
|
Stores single-precision IEEE 754-format single precision floating point numbers. Used primarily for high-speed scientific computation. Literals of this type end with |
|
|
Stores large binary objects in the database, in-line or out-of-line. Every |
|
|
Stores fixed-length character data to a maximum size of 4000 characters. How the data is represented internally depends on the database character set. You can specify the size in terms of bytes or characters, where each character contains one or more bytes, depending on the character set encoding. |
|
|
Stores large blocks of character data in the database, in-line or out-of-line. Both fixed-width and variable-width character sets are supported. Every |
|
|
Stores fixed-length date times, which include the time of day in seconds since midnight. The date defaults to the first day of the current month; the time defaults to midnight. The date function |
|
|
Stores a single-precision, floating-point, number. |
|
|
A |
|
|
Stores intervals of days, hours, minutes, and seconds. |
|
|
Stores intervals of years and months. |
|
|
Stores fixed-length character strings. The |
|
|
Stores the geometric description of a spatial object and the tolerance. Tolerance is used to determine when two points are close enough to be considered as the same point. |
|
|
Stores an array of type |
|
|
Stores the dimension name, lower boundary, upper boundary and tolerance. |
|
|
Stores an array of type |
|
|
Stores Geographical Information System (GIS) or spatial data in the database. For more information, refer to the Oracle Spatial Users Guide and Reference. |
|
|
Stores the list of all vertices that define the geometry. |
|
|
Stores two dimensional and three dimensional points. |
|
|
Stores fixed-length (blank-padded, if necessary) national character data. Because this type can always accommodate multibyte characters, you can use it to hold any Unicode character data. How the data is represented internally depends on the national character set specified when the database was created, which might use a variable-width encoding (UTF8) or a fixed-width encoding (AL16UTF16). |
|
|
Stores large blocks of |
|
|
Stores real numbers in a fixed-point or floating-point format. Numbers using this data type are guaranteed to be portable among different Oracle platforms, and offer up to 38 decimal digits of precision. You can store positive and negative numbers, as well as zero, in a |
|
|
Stores variable-length Unicode character data. Because this type can always accommodate multibyte characters, you can use it to hold any Unicode character data. How the data is represented internally depends on the national character set specified when the database was created, which might use a variable-width encoding (UTF8) or a fixed-width encoding (AL16UTF16). |
|
|
Stores binary data or byte strings. For example, a |
|
|
An Oracle-supplied type that can contain an instance of a given type, with data, plus a description of the type. ANYDATA can be used as a table column data type and lets you store heterogeneous values in a single column. The values can be of SQL built-in types as well as user-defined types. |
|
|
This type represents a data manipulation language (DML) change to a row in a table. This type uses the LCR$_ROW_LIST type. |
|
|
Extends the |
|
|
Extends the |
|
|
Extends the data type |
|
|
Stores a length-value data type consisting of a binary length subfield followed by a character string of the specified length. The length is in bytes unless character-length semantics are used for the data file. In that case, the length is in characters. |
|
|
Stores variable-length character data. How the data is represented internally depends on the database character set. The |
|
|
This is an object type that is used to specify formatting arguments for SYS_XMLGEN() and SYS_XMLAGG() functions. |
|
|
An Oracle-supplied type that can be used to store and query XML data in the database. It has member functions you can use to access, extract, and query the XML data using XPath expressions. XPath is another standard developed by the W3C committee to traverse XML documents. |
The rules for naming data objects depend on the naming mode you set for Warehouse Builder in the Naming Preferences section of the Preferences dialog box. Warehouse Builder maintains a business and a physical name for each object stored in a workspace. The business name for an object is its descriptive logical name and the physical name is the name used when Warehouse Builder generates code. See "Naming Preferences" for details on how to specify a naming mode.
When you name or rename data objects, use the following naming conventions.
Naming Data Objects
In the physical naming mode, the name can be from 1 to 30 alphanumeric characters. Blank spaces are not allowed. Do not use any of the reserved words as a name of an object.
In the business naming mode, the limit is 200 characters. The name should be unique across the object category that owns the object. For example, since all tables belong to a module, table names should be unique across the module to which they belong. Similarly, module names should be unique across the project to which they belong.
Describing Data Objects
Edit the description of the data object as necessary. The description can be between 2 and 2,000 alphanumeric characters and can contain blank spaces. Specifying a description for a data object is optional.
The Data Object Editor provides a centralized interface to create, edit, configure, validate, and deploy Oracle data objects. You can use the Data Object Editor with relational, dimensional, and business intelligence objects. You can also view the data stored in these objects.
The Data Object Editor enables you to build your warehouse schema designs. It also provides an intuitive user interface that supports fast entry of design details. The Data Object Editor contains a menu bar, multiple toolbars, and multiple panels. All the panels are dockable. You can resize the panels or relocate them anywhere in the editor window. You can also choose to display or hide any of the panels. For more information about the Data Object Editor components, refer to the online help.
To relocate a panel, hold down the mouse button on the panel title, drag to the new location and release the mouse button. Resize a panel by placing your mouse on the panel border, pressing the mouse button when the double sided arrow appears, and dragging your mouse to indicate the desired size.
Figure 6-1 displays the Data Object Editor.
Use the Data Object Editor to:
Create, edit, and delete relational and dimensional objects.
Create, edit, and delete the following business intelligence objects: Business Areas and Item Folders.
Define relationships between Oracle data objects.
Validate, generate, and deploy Oracle data objects.
Define and edit all aspects of a data object such as its columns, constraints, indexes, partitions, data rules, and attribute sets.
View impact analysis and lineage information for a data object.
Define implementation details for dimensional objects with a relational implementation.
View the data stored in a data object.
Starting the Data Object Editor
Use one of the following methods to start the Data Object Editor:
Select a data object in the Project Explorer. From the Design Center menu select Edit, then Open Editor.
Right-click a data object in the Project Explorer and select Open Editor.
Double-click a data object in the Project Explorer.
The Data Viewer enables you to view the data stored in the data object. For example, the data viewer for a table enables you to view the table data. You can access the Data Viewer using one of the following methods:
From the Project Explorer, right-click a data object and select Data.
In the Data Object Editor for the data object, navigate to the Data Viewer tab of the Details panel. Click Execute Query.
The Data Viewer tab contains the following buttons: Execute Query, Get More, Where Clause, and More. The More button is displayed at the bottom of the tab.
Click Execute Query to execute a query on the data object and fetch its data.
By default, the Data Viewer displays the first hundred rows of data. To retrieve the next set of rows, click Get More. Alternatively, you can click More to perform the same action.
Click Where Clause to specify a condition that is used to restrict the data displayed by the Data Viewer. Clicking this button displays the Where Clause dialog box. Use this dialog box to specify the condition used to filter data. You can use this option for tables and views only.
The columns and column names displayed in the Data Object Editor are taken directly from the location in which the actual table is deployed. If the table definition in the Data Viewer does not match with what you see in the Data Object Editor, it is because the changes you made in the editor have not yet been deployed.
Use the Data Object Editor to create relational, dimensional, and certain business intelligence objects. There are multiple methods of creating data objects using the Data Object Editor.
Use one of the following editor components to create a data object:
Menu Bar
Canvas
Data Object Editor Palette
See Creating a Data Object Using the Data Object Editor Palette.
To create a data object using the menu bar:
If it is not already open, open the Data Object Editor.
Navigate to the tab that corresponds to the type of data object that you want to create.
For example, to create a table, select the Relational tab. To create a business area, select the Business Intelligence tab. To create dimensions and cube, select the Dimensional tab.
From the Diagram menu, select Add, then select the type of data object to create.
Warehouse Builder displays the Add a New or Existing <Object> dialog box. For more information about this dialog box, click Help.
Notice that the list of data objects in the Add menu contains some disabled items. Only the data objects that you can create from the current editor context are enabled.
Select the Create a new <object> option.
For example, to add a table, select the Create a new Table option.
Specify the name of the data object using the New <Object> Name field.
The New <Object> Name field displays a default name for the object. You can choose to retain this default name or specify a different name.
Click OK.
Warehouse Builder adds a node for the new data object to the canvas.
Use the tabs of the Details panel to define the data object.
To create a data object using the canvas:
If it is not already open, open the Data Object Editor.
Navigate to the tab that corresponds to the type of data object that you want to create.
For example, to create a materialized view, select the Relational tab. To create a dimension, select the Dimensional tab.
Right-click whitespace (blank area) on the canvas.
Warehouse Builder displays a shortcut menu containing the types of data objects you can create.
Select the option corresponding to the type of object you want to create.
For example, to create a materialized view, select the Add a Materialized View option.
Warehouse Builder displays the Add a New or Existing <Object> dialog box. For more information about this dialog box, click Help.
Select the Create a new <object> option.
For example, to add a cube, select the Create a new Cube option.
Specify the name of the data object using the New <Object> Name field.
The New <Object> Name field displays a default name for the object. You can choose to retain this default name or specify a different name.
Click OK.
Warehouse Builder adds a node for the new data object to the canvas.
Use the tabs of the Details panel to define the data object.
To create a data object using the Palette:
If it is not already open, open the Data Object Editor.
Navigate to the tab that corresponds to the type of data object that you want to create.
For example, to create a view, select the Relational tab. To create a cube, select the Dimensional tab.
Drag and drop the operator that corresponds to the type of object that you want to create on to the canvas.
For example, to create a view, drag and drop the View operator from the palette on to the canvas.
Warehouse Builder displays the Add a New or Existing <Object> dialog box. For more information about this dialog box, click Help.
Select the Create a new <object> option.
For example, to add a cube, select the Create a new Cube option.
Specify the name of the data object using the New <Object> Name field.
The New <Object> Name field displays a default name for the object. You can choose to retain this default name or specify a different name.
Click OK.
Warehouse Builder adds a node for the new data object to the canvas.
Use the tabs of the Details panel to define the data object.
This section describes the basic concepts related to dimensional objects. If you are familiar with dimensional objects concepts and the types of implementations for dimensional objects in Warehouse Builder, skip the next few sections and continue with "Designing the Target Schema".
Objects that contain additional metadata to identify and categorize data are called dimensional objects. Warehouse Builder enables you to design, deploy, and load two types of dimensional objects: dimensions and cubes. In this chapter, the word dimensional object refers to both dimensions and cubes.
Most analytic queries require the use of a time dimension. Warehouse Builder provides tools that enable you to easily create and populate time dimensions by answering simple questions.
Design versus Implementation
Warehouse Builder separates the logical design of dimensional objects from their storage. The logical design (business rules) allow you to focus on the structure and the content of the dimensional object first. You can then choose a relational, ROLAP, or MOLAP implementation for the dimensional object.
ROLAP and relational implementations store the dimensional object in a relational schema in the database.
A MOLAP implementation stores the dimensional object in analytic workspaces in the database.
Warehouse Builder enables you to use the same metadata to create and manage both your relational and multidimensional data stores. Separating the design from the implementation has the following advantages:
Implementation is easier, because you first design and then implement.
ETL is transparent as it is always the same for any type of implementation.
Uses of OLAP
Business organizations typically have complex analytic, forecast, and planning requirements. Analytic Business Intelligence (BI) applications provide solutions by answering critical business questions using the data available in your database.
Dimensional objects provide complex analytic power to your data warehouse. After you load data into dimensional objects, you can use tools and applications to run complex analytical queries that answer your business questions. These analytic queries include time-series analysis, inter-row calculations, access to aggregated historical and current data, and forecasts. Multidimensional objects are more effective in answering these types of queries quickly.
About Creating Dimensional Objects
Creating dimensional objects consists of four high-level tasks:
When you define dimensional objects, you describe the logical relationships that help store data in a more structured format. For example, to define a dimension, you describe its attributes, levels, and hierarchies.
Warehouse Builder provides the following two methods to define dimensional objects:
Wizards: Use wizards to create dimensional objects easily. The wizard creates a fully functional dimensional object along with the implementation objects that store the dimensional object data. Many options are defaulted to the most common settings. You can change these settings later using the editors.
You use the Create Dimension Wizard to create dimensions, the Create Time Dimension Wizard to create time dimensions, and the Create Cube Wizard to create cubes.
Editors: Use editors to create or edit dimensional objects. Use editors to create a dimensional object when you want to specify settings that are different from the default settings used by the wizards. Also use editors to create dimensional objects that use certain advanced options that are not available when you use wizards. For example, to create a relational dimension that uses a snowflake schema implementation, you must use the editor. When you use the wizard, the default implementation method used is the star schema. However, you can edit a dimension that you created using the Create Dimension Wizard and modify it to use a snowflake schema implementation.
To implement a dimensional object is to create the physical structure of the dimensional object. Warehouse Builder provides the following implementations for dimensional objects:
Note:
To use a MOLAP implementation, you must have the following:Oracle Database 10g Enterprise Edition with the OLAP option
OLAP 10.1.0.4 or higher
You set the Deployment Option configuration property to specify the type of implementation for a dimensional object. For more information on setting this property, see "Configuring Dimensions" and "Configuring Cubes" in the Warehouse Builder Online Help.
A relational implementation stores the dimensional object and its data in a relational form in the database. The dimensional object data is stored in implementation objects that are typically tables. Any queries that are executed on the dimensional object obtain data from these tables. Warehouse Builder creates the DDL scripts that create the dimensional object. You can then deploy these scripts to the database using the Control Center.
When you use the wizard to define dimensional objects, Warehouse Builder creates the database tables that store the dimensional object data. When you define a dimensional object using the Data Object Editor, you can decide whether you want Warehouse Builder to create the implementation tables or you want to store the dimensional object data in your own tables and views. The following section on binding describes how you specify the relationship between the dimensional object and its implementation objects.
For a relational implementation, you cannot use the Data Viewer to view the data stored in the dimensional object. You can however view the data stored in the implementation tables of the dimensional object using the Data Viewer.
Binding is the process of connecting the attributes of the dimensional object to the columns in the table or view that store their data. You perform binding only for dimensional objects that have a relational implementation. For multidimensional objects, binding is implicit and is resolved in the analytic workspace.
For dimensions, you connect the level attributes and level relationships to the columns in the implementation objects. For cubes, you connect the measures and dimension references to implementation table columns.
Warehouse Builder provides two methods of binding:
Auto binding
Manual binding
Auto Binding In auto binding, Warehouse Builder creates the implementation tables, if they do not already exist. The attributes and relationships of the dimensional object are then bound to the columns that store their data. You can perform auto binding using both the wizards and the editors.
In the case of a dimension, the number of tables used to store the dimension data depends on the options you select for the storage. For more information on these options, see "Relational and ROLAP Implementation of a Dimension".
When you use the editors to create dimensional objects, you can perform both auto binding and manual binding.
To perform auto binding:
In the Project Explorer, right-click the dimensional object and select Open Editor.
The Data Object Editor for this dimensional object is displayed.
On the Dimensional tab, right-click the dimensional object node and select Bind.
Alternatively, select the dimensional object node on the canvas and from the Object menu select Bind.
If the Bind option is not enabled, verify if the dimensional object uses a relational or ROLAP implementation. In the case of dimensions, ensure that the Manual option is not set in the Implementation section of the Storage tab.
Manual Binding In manual binding, you must explicitly bind the attributes of the dimensional objects to the database columns that store their data. You use manual binding when you want to bind a dimensional object to existing tables or views.
To perform manual binding for a dimensional object:
Create the implementation objects (tables or views) that you will use to store the dimensional object data.
In the case of relational or ROLAP dimensions, create the sequence used to load the surrogate identifier of the dimension. You can choose to use an existing sequence.
In the Project Explorer, right-click the dimensional and select Open Editor.
The Data Object Editor for the dimensional object is displayed. On the canvas, the Dimensional tab is active.
Right-click the dimensional object and select Detail View.
Warehouse Builder opens a new tab that has the same name as the dimensional object.
From the palette, drag and drop the operator that represents the implementation object onto the canvas.
Warehouse Builder displays the Add a New or Existing <Object> dialog box. For example, if the dimension data is stored in a table, drag a Table operator from the Palette and drop it onto the canvas. The Add a New or Existing Table dialog box is displayed.
Choose the Select an existing <Object> option and then select the data object from the list of objects displayed in the selection tree.
Click OK.
A node representing the object that you just added is displayed on the canvas.
For dimensions, if more than one data object is used to store the dimension data, perform steps 4 to 6 for each data implementation object.
For dimensions, map the attributes in each level of the dimension to the columns that store their data. Also map the level relationships to the database column that store their data.
For cubes, map the measures and dimension references to the columns that store the cube data.
To map to the implementation object columns, hold down your mouse on the dimension or cube attribute, drag, and then drop on the column that stores the attribute value.
For example, for the PRODUCTS dimension described in "Dimension Example", the attribute NAME in the Groups level of the PRODUCTS dimension is stored in the GROUP_NAME attribute of the PRODUCTS_TAB table. Hold down the mouse on the NAME attribute, drag, and drop on the GROUP_NAME attribute of the PRODUCTS_TAB table.
Unbinding Warehouse Builder also enables you to unbind a dimensional object. Unbinding removes the connections between the dimensional object and the tables that store its data.
To unbind a dimensional object from its current implementation, right-click the dimensional object on the Relational tab of the Canvas and select Unbind. Unbinding removes the bindings between the dimensional object and its implementation objects. However, it does not modify the implementation objects.
A ROLAP implementation, like a relational implementation, stores the dimensional object and its data in a relational form in the database. In addition to creating DDL scripts that can be deployed to a database, a ROLAP implementation enables you to create CWM2 metadata for the dimensional object in the OLAP catalog.
In a MOLAP implementation, the dimensional object data is stored in an analytic workspace in Oracle Database 10g. This analytic workspace, in turn, is stored in the database.
An analytic workspace is a container within the Oracle Database that stores data in a multidimensional format. Analytic workspaces provide the best support to OLAP processing. An analytic workspace can contain a variety of objects such as dimensions and variables.
An analytic workspace is stored in a relational database table, which can be partitioned across multiple disk drives like any other table. You can create many analytic workspaces within a single schema to share among users. An analytic workspace is owned by a particular user and other users can be granted access to it. The name of a dimensional object must be unique within the owner's schema. For more information about analytic workspaces, see Oracle OLAP User's Guide.
The OLAP catalog is the metadata repository provided for the OLAP option in the Oracle Database. This metadata describes the data stored in both relational tables and in analytic workspaces.
When you deploy a dimensional object using Warehouse Builder, you can specify if the dimensional object metadata should be stored in the OLAP catalog.
OLAP metadata is dynamically projected through a series of views called the active catalog views (views whose names begin with ALL_OLAP2_AW).
In Oracle Database 10g, the OLAP catalog metadata is used by OLAP tools and applications to access data stored in relational star and snowflake schemas. External application such as Discoverer use the OLAP catalog to query relational and multidimensional data. The application does not need to be aware of whether the data is located in relational tables or in analytic workspaces, nor does it need to know the mechanism for accessing it.
Figure 6-2 describes how the OLAP catalog enables applications to access data stored in relational tables and analytic workspaces.
Figure 6-2 Using the OLAP Catalog to Access Dimensional Objects
The OLAP catalog uses the metadata it stores to access data stored in relational tables or views. The OLAP catalog defines logical multidimensional objects and maps them to the physical data sources. The logical objects are dimensions and cubes. The physical data sources are columns of a relational table or view.
To instantiate the dimensional objects in the database, you must deploy them. To specify the type of implementation for dimensional objects, you set the deployment option. The configuration parameter Deployment Options enables you to set the deployment option.
Warehouse Builder provides the following deployment options for dimensional objects.
Deploy All: For a relational or ROLAP implementation, the dimensional object is deployed to the database and a CWM definition to the OLAP catalog. For a MOLAP implementation, the dimensional object is deployed to the analytic workspace.
Deploy Data Objects Only: Deploys the dimensional object only to the database. You can select this option only for dimensional objects that use a relational implementation.
Deploy to Catalog Only: Deploys the CWM definition to the OLAP catalog only. Use this option if you want applications such as Discoverer for OLAP to access the dimensional object data after you deploy data only. Use this option if you previously deployed with "Data Objects Only" and now want to deploy the CWM Catalog definitions without re-deploying the data objects again.
Deploy Aggregation: Deploys the aggregations defined on the cube measures. This option is available only for cubes.
Deploying Dimensional Objects that Use a MOLAP Implementation
Dimensional objects that use a MOLAP implementation can be deployed just after you define them. You can use the Design Center or the Control Center Manager to deploy a dimensional object.
Deploying Dimensional Objects that Use a Relational or ROLAP Implementation
Before you deploy a relational or ROLAP dimensional object, ensure that the implementation details are specified. This means that the dimensional object should be bound to its implementation objects. Also ensure that the dimensional object is valid. For more information on implementing dimensional objects, see "Relational Implementation of Dimensional Objects". For more information on performing binding, see "Binding".
After you perform binding, deploy the dimensional object. Before you deploy a dimensional object, ensure that all its implementation objects are deployed. For a dimension, this includes the sequence that is used to generate the surrogate identifier of the dimension levels. Alternatively, you can deploy the implementation objects together with the dimensional object.
After you deploy a dimensional object, you load data into it by creating a mapping. Use the Mapping Editor to create the mapping that loads data from the source objects into the dimensional object. You then deploy and execute this mapping.
For more information on loading dimensions, see "Dimension Operator as a Target" in the Warehouse Builder Online Help. For information on loading cubes, see "Cube Operator" in the Warehouse Builder Online Help.
A dimension is a structure that organizes data. Examples of commonly used dimensions are Customers, Time, and Products.
For relational dimensions, using dimensions improves query performance because users often analyze data by drilling down on known hierarchies. An example of a hierarchy is the Time hierarchy of year, quarter, month, day. The Oracle Database uses these defined hierarchies by rewriting queries that retrieve data from materialized views rather than detail tables.
Typical relational dimension tables have the following characteristics:
A single column primary key populated with values called warehouse keys.
Warehouse keys that provide administrative control over the dimension, support techniques that preserve dimension history, and reduce the size of cubes.
One or more hierarchies that are explicitly defined as dimension objects. Hierarchies maximize the number of query rewrites by the Oracle server.
When you create a dimension object using Warehouse Builder, the dimension must conform to the following rules:
A dimension must have a surrogate identifier and a business identifier.
The surrogate identifier can consist of only one attribute. However, the business identifier can consist of more than one attribute.
Every dimension level must have at least one attribute.
A dimension attribute can be either a surrogate identifier, a business identifier, a parent identifier, or a regular attribute.
A regular attribute can also play only one of the following roles at a time: effective date, expiration date, or triggering attribute.
A dimension that uses a relational or ROLAP implementation must have at least one level.
Any database table or view that implements a dimension that uses a relational or ROLAP implementation must have only one LONG, LONG RAW, or NCLOB column.
For a dimension that uses a relational or ROLAP implementation, all level attributes must bind to database tables or views only.
A dimension that uses a relational or ROLAP implementation must be associated with a sequence that is used to load the dimension key attribute.
The dimension key attribute of a dimension that uses a relational or ROLAP implementation must bind to the primary key of a table.
A Type 2 SCD must have the effective date, expiration date, and at least one triggering attribute.
A Type 3 SCD must have the effective date and at least one triggering attribute.
For dimensions with a ROLAP implementation, there are implications and limitations related to the various dimension structures when either reporting on the underlying tables or deploying to the OLAP catalog. Although the dimension may be successfully deployed, errors could occur when other applications, such as Oracle Discoverer access the OLAP catalog.
The following are items that are affected by this limitation:
No reporting tool has metadata about all aspects of dimensional metadata we capture, so this must be incorporated into the query/reports. Otherwise you will see odd information because of the way the data is populated in the implementation tables.
The dimension and cube implementation tables store solved rows which contain negative key values. You can filter out these rows in your queries or reports. When you create a query or report, use the view that is associated with a dimension instead of the dimension itself. Each dimension has a view that is associated with it. The view name is specified in the configuration property View Name of the dimension or cube.
Skip-level hierarchies and ragged hierarchy metadata not deployed to the OLAP catalog.
If you create a dimension that contains skip-level or ragged hierarchies, the metadata for these is stored in the Warehouse Builder repository but is not deployed to the OLAP catalog.
Dimensions with multiple hierarchies must have all dimension attributes mapped along all the hierarchies.
A dimension consists of a set of levels and a set of hierarchies defined over these levels. To create a dimension, you must define the following:
Dimension Attributes
Levels
Level attributes
Hierarchies
A dimension attribute is a descriptive characteristic of a dimension member. It has a name and a data type. A dimension attribute is applicable to one or more levels in the dimension. They are implemented as level attributes to store data.
In Warehouse Builder, you define dimension attributes when you define a dimension. The list of dimension attributes must include all the attributes that you may need for any of the levels in the dimension. Dimension attributes are the only attributes that are visible in Discoverer and other OLAP tools.
For example, the Products dimension has a dimension attribute called Description. This attribute is applicable to all the levels Total, Groups, and Products and stores the description for each of the members of these levels.
The levels in a dimension represent the level of aggregation of data. A dimension must contain at least one level, except in the case of a dimension that contains a value-based hierarchy. Every level must have level attributes and a level identifier.
For example, the dimension Products can have the following levels: Total, Groups, and Product.
Surrogate, Business, and Parent Identifiers
Every level must have two identifiers: a surrogate identifier and a business identifier. When you create a dimension, each level must implement the dimension attributes marked as the surrogate identifier and business identifier (attributes, in the case of a composite business identifier) of the dimension.
A surrogate identifier uniquely identifies each level record across all the levels of the dimension. It must be composed of a single attribute. Surrogate identifiers enable you to hook facts to any dimension level as opposed to the lowest dimension level only.
For a dimension that has a relational or ROLAP implementation, the surrogate identifier should be of the data type NUMBER. Because the value of the surrogate identifier must be unique across all dimension levels, you use the same sequence to generate the surrogate identifier of all the dimension levels.
For a relational implementation, the surrogate identifier serves the following purposes:
If a child level is stored in a different table from the parent level, each child level record stores the surrogate identifier of the parent record.
In a fact table, each cube record stores only the surrogate identifier of the dimension record to which it refers. By storing the surrogate identifier, the size of the fact table that implements the cube is reduced.
A business identifier consists of a user-selected list of attributes. The business identifier must be unique across the level and is always derived from the natural key of the data source. The business identifier uniquely identifies the member. For example, the business identifier of a Product level can be its Universal Product Code (UPC), which is a unique code for each product.
Note:
For a dimension that has a MOLAP implementation, the business identifier can consist of only one attribute.The business identifier does the following:
Identifies a record in business terms.
Provides a logical link between the fact and the dimension or between two levels.
Enables the lookup of a surrogate key.
When you populate a child level in a dimension, you must specify the business identifier of its parent level. When you populate a cube, you must specify the business identifier of the dimension level to which the cube refers.
A parent identifier is used to annotate the parent reference in a value-based hierarchy. For more information on value-based hierarchies, see "Value-based Hierarchies".
For example, an EMPLOYEE dimension with a value-based hierarchy, has the following dimension attributes: ID, FIRST_NAME, LAST_NAME, EMAIL, PHONE, JOB_ID, HIRE_DATE, and MANAGER_ID. In this dimension, ID is the surrogate identifier and MANAGER_ID is the parent identifier.
A level attribute is a descriptive characteristic of a level member. Each level in the dimension has a set of level attributes. To define level attributes, you select the dimension attributes that the level will implement. A level attribute has a distinct name and a data type. The data type is inherited from the dimension attribute that the level attribute implements. The name of the level attribute can be modified to be different from that of the dimension attribute that it implements.
Every level must implement the attribute marked as the surrogate identifier and the business identifier in the set of the dimension attributes.
A dimension hierarchy is a logical structure that uses ordered levels or a set of data values (for a value-based hierarchy) as a means of organizing data. A hierarchy describes parent-child relationships among a set of levels. A level-based hierarchy must have at least one level. A level can be part of more than one hierarchy.
For example, the Time dimension can have the following two hierarchies:
Fiscal Hierarchy: Fiscal Year > Fiscal Quarter > Fiscal Month > Fiscal Week > Day
Calendar Hierarchy: Calendar Year > Calendar Quarter > Calendar Month > Day
All hierarchies must be strict 1:n relationships. One record in a parent level corresponds to multiple records in a child level. But one record in a child level corresponds to only one parent record within a hierarchy.
A dimension role is an alias for a dimension. In a data warehouse, a cube can refer to the same dimension multiple times, without requiring the dimension to be stored multiple times. Multiple references to the same dimension may cause confusion. So you create an alias for each reference to the dimension, thus allowing the joins to be instantly understandable. In such cases, the same dimension performs different dimension roles in the cube.
For example, a sales record can have the following three time values:
Time the order is booked
Time the order is shipped
Time the order is fulfilled
Instead of creating three time dimensions and populating them with data, you can use dimension roles. Model one time dimension and create the following three roles for the time dimension: order booked time, order shipped time, and order fulfillment time. The sales cube can refer to the order time, ship time, and fulfillment time dimensions.
When the dimension is stored in the database, only one dimension is created and each dimension role references this dimension. But when the dimension is stored in the OLAP catalog, Warehouse Builder creates a dimension for each dimension role. Thus, if a time dimension has three roles, three dimensions are created in the OLAP catalog. However, all three dimensions are mapped to the same underlying table. This is a workaround because the OLAP catalog does not support dimension roles.
Note:
Dimension roles can be created for dimensions that have a relational implementation only.A level relationship is an association between levels in a dimension hierarchy. Level relationships are implemented using level attributes that store the reference to the parent level in the hierarchy.
For example, the Products dimension has the following hierarchy: Total > Groups > Product. Warehouse Builder creates two level relationships: Product to Groups and Groups to Total. Two new attributes implement this level relationship: one in the Product level and one in the Groups level. These attributes store the surrogate ID of the parent level.
An example of a dimension is the Products dimension that you use to organize product data. Table 6-3 lists the levels in the PRODUCTS dimension and the surrogate identifier and business identifier for each of the levels in the dimension.
Table 6-3 Products Dimension Level Details
| Level | Attribute Name | Identifier |
|---|---|---|
|
Total |
ID |
Surrogate |
|
Name |
Business |
|
|
Description |
||
|
Groups |
ID |
Surrogate |
|
Name |
Business |
|
|
Description |
||
|
Product |
ID |
Surrogate |
|
UPC |
Business |
|
|
Name |
||
|
Description |
||
|
Package Type |
||
|
Package Size |
The PRODUCTS dimension contains the following hierarchy:
Hierarchy 1: Total > Groups > Product
Warehouse Builder creates control rows that enable you to link fact data to a dimension at any level. For example, you may want to reuse a Time dimension in two different cubes to record the budget data at the month level and the actual data at the day level. Because of the way dimensions are loaded with control rows, you can perform this without any additional definitions. Each member in a dimension hierarchy is represented using a single record.
All control rows have negative dimension key values starting from -2. For each level value of higher levels, a row is generated that can act as a unique linking row to the fact table. All the lower levels in this linking or control rows are nulled out.
Consider the Products dimension described in "Dimension Example". You load data into this dimension from a table that contains four categories of products. Warehouse Builder inserts control rows in the dimension as shown in Table 6-4. These rows enable you to link to a cube at any dimension level. Note that the table does not contain all the dimension attribute values.
Table 6-4 Control Rows Created for the Products Dimension
| Dimension Key | Total Name | Categories Name | Product Name |
|---|---|---|---|
|
-3 |
TOTAL |
||
|
-9 |
TOTAL |
Hardware |
|
|
-10 |
TOTAL |
Software |
|
|
-11 |
TOTAL |
Electronics |
|
|
-12 |
TOTAL |
Peripherals |
To obtain the real number of rows in a dimension, count the number of rows by including a WHERE clause that excludes the NULL rows. For example, to obtain a count on Products, count the number of rows including a WHERE clause to exclude NULL rows in Product.
A value-based hierarchy is a dimension in which hierarchical relationships are defined by a parent dimension attribute and a child dimension attribute. This is different from a level-based hierarchy, referred to as a hierarchy in this chapter, in which the hierarchical relationships are defined between levels.
You create a value-based hierarchy when the parent-child relationships cannot be grouped into meaningful levels. A value-based hierarchy has no levels. When you create the dimension attributes, you must specify which dimension attribute is the parent attribute.
For example, consider an EMPLOYEE dimension that has the following dimension attributes: ID, FIRST_NAME, LAST_NAME, EMAIL, PHONE, JOB_ID, HIRE_DATE, DESCRIPTION, and MANAGER_ID. This dimension contains a parent-child relationship in which the MANAGER_ID attribute identifies the manager of each employee. But these relationships may not form meaningful levels across the organization. This is because the number of levels between an employee and the CEO is not the same for all employees. There may be four levels between employee A and the CEO, whereas, there may be six levels between employee B and the CEO. In such cases, you create a value-based hierarchy with MANAGER_ID as the parent identifier.
You can create value-based hierarchies using the Data Object Editor only. For more information about specifying a parent attribute, see "Attributes Tab" in the Warehouse Builder Online Help.
Note:
Value-based hierarchies can be created only in dimensions that use a MOLAP implementation.Implementing a dimension consists of specifying how the dimension and its data are physically stored. You can choose either a relational implementation, ROLAP implementation, or MOLAP implementation for a dimension. For more information about setting the implementation method, see "Implementing Dimensional Objects".
When you store dimension data in a relational form, you can implement the dimension using one of the following methods:
In a star schema implementation, Warehouse Builder stores the dimension data in a single table. Because the same table or view stores data for more than one dimension level, you must specify a dimension key column in the table. The dimension key column is the primary key for the dimension. This column also forms the foreign key reference to the cube.
Each level implements a subset of dimension attributes. By default, the level attribute name is the same as the dimension attribute name. To avoid name conflicts caused by all level data being stored in the same table, Warehouse Builder uses the following guidelines for naming in a star table:
If the level attribute name is not unique, Warehouse Builder prefixes it with the name of the level.
If the level attribute name is unique, Warehouse Builder does not use any prefix.
Note:
To ensure that no prefixes are used, you must explicitly change the level attribute name in the Create Dimension wizard or the Data Object Editor.For example, if you implement the Products dimension using a star schema, Warehouse Builder uses a single table to implement all the levels in the dimension.
Figure 6-3 displays the star schema implementation of the Products dimension. The attributes in all the levels are mapped to different columns in a single table called PRODUCTS. The column called DIMENSION_KEY stores the surrogate ID for the dimension and is the primary key of the table.
Figure 6-3 Star Schema Implementation of Products Dimension
For relational or ROLAP dimensions that use a star implementation, you can bind attributes from more than one levels to the same database column. A database column that is bound to attributes from more than one dimension levels is referred to as a shared column. For a Type 2 Slowly Changing Dimension (SCD), you cannot set the level attributes that are bound to a shared column as triggering attributes.
In a snowflake schema implementation, Warehouse Builder uses more than one table to store the dimension data. Separate database tables or views store the data pertaining to each level in the dimension.
Figure 6-4 displays the snowflake implementation of the PRODUCTS dimension. Each level in the dimension is mapped to a different table.
Figure 6-4 Snowflake Schema Implementation of the Products Dimension
When you perform binding, you specify the database columns that will store the data of each attribute and level relationship in the dimension. You can perform either auto binding or manual binding for a dimension. For more information about binding, see "Binding".
Auto Binding When you perform auto binding, Warehouse Builder binds the dimension object attributes to the database columns that store their data. When you perform auto binding for the first time, Warehouse Builder also creates the tables that are used to store the dimension data.
When you perform auto binding on a dimension that is already bound, Warehouse Builder uses the following rules:
If the implementation method of the dimension remains the same, Warehouse Builder rebinds the dimensional object to the existing implementation objects. The implementation method can be either Star or Snowflake. For more information on implementation methods, see "Relational and ROLAP Implementation of a Dimension".
For example, you create a Products dimension using the star schema implementation method and perform auto binding. The dimension data is stored in a table called Products. You modify the dimension definition at a later date but retain the implementation method as star. When you now auto bind the Products dimension, Warehouse Builder rebinds the Products dimension attributes to the same implementation tables.
If the implementation method of a dimension is changed, Warehouse Builder deletes the old implementation objects and creates a new set of implementation tables. If you want to retain the old implementation objects, you must first unbind the dimensional object and then perform auto binding. For more information on implementation methods, see "Relational and ROLAP Implementation of a Dimension".
For example, you create a Products dimension using the star schema implementation method and bind it to the implementation table. You now edit this dimension and change its implementation method to snowflake. When you now perform auto binding for the modified Products dimension, Warehouse Builder deletes the table that stores the dimension data, creates new implementation tables, and binds the dimension attributes and relationships to the new implementation tables.
For information about how to perform auto binding, see "Auto Binding". Auto binding uses the implementation settings described in "Relational and ROLAP Implementation of a Dimension".
Manual Binding You would typically use manual binding to bind existing tables to a dimension. Use manual binding if no auto binding or rebinding is required.
For information about how to perform manual binding, see "Manual Binding".
When a dimension is implemented in a MOLAP environment, the dimension definition and data are stored in an analytic workspace. This is done using analytic workspace objects such as dimensions, relationships, and so on. You can store multiple cubes in the same analytic workspace. For more information on MOLAP implementation, see "MOLAP Implementation of Dimensional Objects".
A Slowly Changing Dimension (SCD) is a dimension that stores and manages both current and historical data over time in a data warehouse. In data warehousing, there are three commonly recognized types of SCDs.
With the appropriate licensing, you can use Warehouse Builder to define, deploy, and load all three types of SCDs. You can create slowly changing dimensions only for dimensions that use a relational implementation.
Note:
Type 1 does not require additional licensing; however, type 2 and type 3 SCDs require the Warehouse Builder Enterprise ETL Option.Table 6-5 describes the three types of SCDs.
Table 6-5 Types of Slowly Changing Dimensions
| Type | Use | Description | Preserves History? |
|---|---|---|---|
|
Type 1 |
Overwriting |
Only one version of the dimension record exists. When a change is made, the record is overwritten and no historic data is stored. |
No |
|
Type 2 |
Creating a new version of a dimension record |
There are multiple versions of the same dimension record, and new versions are created while the old ones are still kept upon modification. |
Yes |
|
Type 3 |
Creating a current value field |
There is one version of the dimension record. This record stores the previous value and current value of selected attributes. |
Yes |
To create a Type 2 SCD or a Type 3 SCD, in addition to the regular dimension attributes, you need additional attributes that perform the following roles:
Triggering Attributes: These are attributes for which historical values must be stored. For example, in the PRODUCTS dimension, the attribute PACKAGE_TYPE of the Product level can be a triggering attribute. This means that when the value of this attribute changes, the old value needs to be stored.
Effective Date: This attribute stores the start date of the record's life span.
Expiration Date: This attribute stores the end date of the record's life span.
An attribute can play only one of the above roles. For example, an attribute cannot be a regular attribute and an effective date attribute. When you use the wizard to create a Type 2 SCD or a Type 3 SCD, Warehouse Builder creates the required additional attributes.
In a Type 1 Slowly Changing Dimension (SCD), the new data overwrites the existing data. Typically, this type is not considered an SCD and most dimensions are of this type. Thus the existing data is lost as it is not stored anywhere else. This is the default type of dimension you create. You need not specify any additional information to create a Type 1 SCD. Unless there are specific business reasons, you must assume that a Type 1 SCD is sufficient. For more information on how to define and implement a Type 1 SCD, refer to the following:
A Type 2 Slowly Changing Dimension (SCD) retains the full history of values. When the value of a triggering attribute changes, the current record is closed. A new record is created with the changed data values and this new record becomes the current record. Each record contains the effective date and expiration date to identify the time period for which the record was active. Warehouse Builder also enables you to set a specific non-null date value as the expiration date. The current record is the one with a null or the previously specified value in the expiration date.
All the levels in a dimension need not store historical data. Typically, only the lowest levels is versioned.
Note:
Be aware of the impact that all levels in a dimension not storing historical data has on query tools.To define a Type 2 Slowly Changing Dimension (SCD):
For the level that stores historical data, specify the attributes used as the effective date and the expiration date.
Choose the level attribute(s) that will trigger a version of history to be created.
You cannot choose the surrogate ID, effective date attribute or expiration date attribute as the triggering attribute.
Each version of a record is assigned a different surrogate identifier. The business ID connects the different versions together in a logical sense. Typically, if there is a business need, Type 2 SCDs are used.
Type 2 SCD Example
Consider the Customers Type 2 SCD that contains two levels, Household and Customer. Table 6-6 lists level attributes of the Customers Type 2 SCD.
Table 6-6 Customers Type 2 SCD Attributes
| Attribute Name | Identifier |
|---|---|
|
|
Surrogate identifier |
|
|
Business identifier |
|
|
|
|
|
|
|
|
|
|
|
|
|
|
Effective Date |
|
|
Expiration Date |
Customer is the leaf level and Household is the non-leaf level.
The Household level implements the following attributes: ID, BUSN_ID, ADDRESS, ZIP, EFFECTIVE_DATE, and EXPIRATION_DATE. The Customer level implements the following attributes: ID, BUSN_ID, MARITAL_STATUS, HOME_PHONE, EFFECTIVE_DATE, and EXPIRATION_DATE.The table that implements this Type 2 SCD (for a relational or ROLAP implementation) contains the following columns: DIMENSION_KEY, H_ID, H_BUSN_ID, H_ADDRESS, H_ZIP, H_EFFECTIVE_DATE, H_EXPIRATION_DATE, C_ID, C_BUSN_ID, C_MARITAL_STATUS, C_HOME_PHONE, C_EFFECTIVE_DATE, and C_EXPIRATION_DATE.
To create the CUSTOMERS Type 2 SCD:
Specify that the ZIP attribute of the Household level and the MARITAL_STATUS attribute of the Customer level are the triggering attributes.
Use two additional attributes to store the effective date and the expiration date of the level records. When you use the Create Dimension wizard, Warehouse Builder creates these additional attributes for the lowest level only. If you use the Data Object Editor, you must explicitly create these attributes and apply them to the required levels.
When the non-leaf level of a dimension contains versioned attributes, the versioning of this non-leaf level results in the versioning of its corresponding child records, if they have effective date and expiration date attributes. For example, when the value of the H_ZIP is updated in a particular Household level record, the child records corresponding to this Household level are automatically versioned.
Hierarchy versioning is not enabled by default for Type 2 SCDs. When you create a Type 2 SCD using the Create Dimension Wizard, hierarchy versioning is disabled. You must use the Data Object Editor to enable hierarchy versioning.
To enable hierarchy versioning:
Right-click the Type 2 SCD in the Project Explorer and select Open Editor.
The Data Object Editor is displayed.
Navigate to the SCD tab.
Click Settings to the right of the Type 2: Store the Complete change history option.
The Type 2 slowly changing dimension dialog box is displayed. The attributes of each level are displayed under the level node.
In the child level that should be versioned when its parent attribute changes, for the attribute that represents the parent attribute of this child level, select Trigger History in the Record History column.
For example, you create the Customers Type 2 SCD using the Create Dimension Wizard. Open the Data Object Editor for this Type 2 SCD and navigate to the Type 2 slowly changing dimension dialog box as described in steps 1 to 3. The Customer level has an attribute called HOUSEHOLD_ID. This attribute represents the parent attribute of each Customer record. For the HOUSEHOLD_ID attribute, select Trigger History in the Record History column.
All the levels in a dimension need not store historical data. Typically, only the lowest level, also called the leaf level, stores historical data. However, you can also store historical data for other dimension levels.
When a record in a Type 2 Slowly Changing Dimension (SCD) is versioned, the old record is marked as closed and a new record is created with the updated values. The expiration date of the record is set to indicate that it is closed. The new record is referred to as the current record and, by default, has a default expiration of NULL. While loading data into the Type 2 SCD, you can set the expiration date by using the configuration parameters for the Dimension operator. For more information, see "Dimension Operator" in the Warehouse Builder Online Help.
You can update the following in a Type 2 SCD:
Leaf level attribute
Leaf level versioned attribute
Non-leaf level attribute
Non-leaf level versioned attribute
Leaf level parent attribute
The following sections describe the Warehouse Builder functionality for these update operations.
Updating a Leaf Level Attribute
When you update a leaf level attribute, the value of this attribute is updated in the corresponding record.
For example, if you update the value of C_HOME_PHONE in a Customer level record, the record is updated with the changed phone number.
Updating a Leaf Level Versioned Attribute
When you update a leaf level versioned attribute, the current record is marked as closed. A new record is created with the updated value of the versioned attribute.
For example, if you update the marital status of a customer, the current record is marked as closed. A new record with the updated marital status is created for that customer.
Updating a non-leaf Level Attribute
When you update an attribute in a non-leaf level, the open records of the non-leaf level and the child records corresponding to this non-leaf level are updated with the new value.
For example, when you update the H_ADDRESS attribute in a Household level record, the current open record for that household is updated. All open child records corresponding to that particular household are also updated.
Updating a non-leaf Level Versioned Attribute
The update functionality depends on whether hierarchy versioning is enabled or disabled.
Hierarchy Versioning Disabled
The non-leaf level record corresponding to the versioned attribute is closed and a new record is created with the updated value. The child records of this non-leaf level record are updated with the changed value of the non-leaf level versioned attribute.
For example, when the value of H_ZIP in a Household level record is updated, the current open record for that household is closed. A new record with the updated value of H_ZIP is created. The value of H_ZIP is updated in all the child records corresponding to the updated household record.
Hierarchy Versioning Enabled
The non-leaf level record corresponding to the versioned attribute is closed and a new record is created with the updated value. Child records corresponding to this non-leaf level record are also closed and new child records are created with the updated value.For example, when the value of H_ZIP in a Household level record is updated, the current open record for that household and its corresponding child records are closed. New records are created, with the updated value, for the household and for the child records corresponding to this household.
Updating the Leaf Level Parent Attribute
In addition to updating the level attributes in a Type 2 SCD, you can also update the parent attribute of a child record. In the Customers Type 2 SCD, the attribute H_BUSN_ID in a Customer record stores the parent attribute of that customer. The update functionality for the leaf level parent attribute depends on whether hierarchy versioning is enabled or disabled.
Hierarchy Versioning Disabled
The child record is updated with the new parent attribute value.
For example, when you update the value of the H_BUSN_ID attribute representing the parent record of a Customer record, the Customer record is updated with the new values.
Hierarchy Versioning Enabled
The child record is closed and a new record with the changed parent attribute value is created.
For example, when you update the H_BUSN_ID attribute of a customer record, the current customer record is closed. A new customer record with the updated H_BUSN_ID is created.
A Type 3 Slowly Changing Dimension (SCD) stores two versions of values for certain selected level attributes. Each record stores the previous value and the current value of the versioned attributes. When the value of any of the versioned attributes changes, the current value is stored as the old value and the new value becomes the current value. Each record stores the effective date that identifies the date from which the current value is active. This doubles the number of columns for the versioned attributes and is used rarely.
To define a Type 3 Slowly Changing Dimension (SCD):
For each level, specify which attributes should be versioned. That is, which attributes should store the previous value as well as the current value.
For each versioned attribute, specify the attribute that stores the previous value.
The following restrictions apply to attributes that can have a previous value.
An attribute specified as a previous value cannot have further previous values.
The surrogate ID cannot have previous values.
For each level that is versioned, specify the attribute that stores the effective date.
Warehouse Builder recommends that you do not include previous value attributes in the business identifier of a Type 3 SCD.
Type 3 SCD Example
The PRODUCTS dimension described in "Dimension Example" can be created as a Type 3 SCD. The attributes PACKAGE_TYPE and PACKAGE_SIZE of the Product level should be versioned. You define two additional attributes to store the previous values, say PREV_PACK_SIZE and PREV_PACK_TYPE in the Product level. Suppose the value of the PACKAGE_TYPE attribute changes, Warehouse Builder stores the current value of this attribute in PREV_PACK_TYPE and stores the new value in the PACKAGE_TYPE attribute. The effective date attribute can be set to the current system date or to any other specified date.
A time dimension is a dimension that stores temporal data. Time dimensions are used extensively in data warehouses. Warehouse Builder enables you to create and populate time dimensions. You can use Warehouse Builder to create both fiscal and calendar time dimensions.
When you create a time dimension using the wizard, Warehouse Builder creates the mapping for you to execute to populate the time dimension. Also, the data loaded into the time dimension conforms to the best practices recommended by Warehouse Builder for a time dimension.
This section contains the following topics:
Warehouse Builder provides an accelerator to create time dimensions. It also specifies a set of rules as best practices for defining a time dimension. Warehouse Builder enforces these rules when you use Create Time Dimension wizard to create a time dimension.
The rules are as follows:
The time dimension can contain only a subset of the predefined levels specified by Warehouse Builder.
Each level in a time dimension must have attributes for the time span and ending date.
A time dimension can have one or more hierarchies. Each hierarchy should be either a fiscal hierarchy or a calendar hierarchy.
When you deploy a time dimension to the OLAP catalog, you must attach the time span and end date descriptors related to the levels to the dimension and its levels. When you create a time dimension using the Create Time Dimension wizard, Warehouse Builder performs this for you.
If you find these rules too restrictive for your business environment, you can create your own time dimension by setting the time attributes in the Data Object Editor. Ensure that you set the descriptors when you create a time dimension using the Data Object Editor.
A time dimension consists of a set of levels and a set of hierarchies defined over these levels. Dimension roles are used extensively in time dimensions. For more information about dimension roles see "Dimension Roles". To create a time dimension you must define the following:
Levels
Dimension Attributes
Level Attributes
Hierarchies
A level represents the level of aggregation of data. A time dimension must contain at least two levels. You can use a level only once in a time dimension. For example, a time dimension can contain only one Calendar Month level. Each level must have a surrogate identifier and a business identifier. The surrogate identifier should be the ID level attribute.
A Warehouse Builder time dimension can contain only a subset of the following levels:
Day
Fiscal week
Calendar week
Fiscal month
Calendar month
Fiscal quarter
Calendar quarter
Fiscal year
Calendar year
A dimension attribute is an attribute that is implemented by more than one level in the time dimension. Table 6-7 describes the dimension attributes of the Warehouse Builder time dimension.
Table 6-7 Dimension-level Attributes of the Time Dimension
| Dimension Attribute | Description |
|---|---|
|
ID |
The ID attribute is implemented as level ID in all the levels. |
|
Start Date |
The start date for the period. It always starts at 00:00:00 of the first day of the period. |
|
End Date |
The end date for the period. It always ends on 23:59:59 of the last day of the period. |
|
Time Span |
Number of days in the period. |
|
Description |
Description of the level record. |
A level attribute is a descriptive characteristic of a level value. Warehouse Builder creates level attributes for the time dimension based on the levels that you decide to implement for the time dimension.
Table 6-8 lists the attributes of each level in the Warehouse Builder time dimension. For a description of each attribute, refer to Appendix B.
Table 6-8 Time Dimension Level Attributes
| Level Name | Attribute Name |
|---|---|
|
DAY |
ID, DAY, START_DATE, END_DATE, TIME_SPAN, JULIAN_DATE, DAY_OF_CAL_WEEK, DAY_OF_CAL_MONTH, DAY_OF_ CAL_ QUARTER, DAY_OF_CAL_YEAR, DAY_OF_FISCAL_WEEK,DAY_OF_FISCAL_MONTH, DAY_OF_FISCAL_ QUARTER, DAY_OF_FISCAL_YEAR. DESCRIPTION. |
|
FISCAL WEEK |
ID, WEEK_NUMBER, WEEK_OF_FISCAL_MONTH, WEEK_OF_FISCAL_QUARTER, WEEK_OF_FISCAL_YEAR, START_DATE, END_DATE, TIME_DATE, DESCRIPTION. |
|
CALENDAR WEEK |
ID, START_DATE, END_DATE, TIME_SPAN, DESCRIPTION. |
|
FISCAL MONTH |
ID, MONTH_NUMBER, MONTH_OF_QUARTER, MONTH_OF_YEAR, START_DATE, END_DATE, TIME_SPAN, DESCRIPTION. |
|
CALENDAR MONTH |
ID, MONTH_NUMBER, MONTH_OF_QUARTER, MONTH_OF_YEAR, START DATE, END_DATE, TIME_SPAN, DESCRIPTION. |
|
FISCAL QUARTER |
ID, QUARTER_NUMBER, QUARTER_OF_YEAR, START_DATE, END_DATE, TIME_SPAN, DESCRIPTION |
|
CALENDAR QUARTER |
ID, QUARTER_NUMBER, QUARTER_OF_YEAR, START_DATE, END_DATE, TIME_SPAN, DESCRIPTION. |
|
FISCAL YEAR |
ID, YESR_NUMBER, START_DATE, END_DATE, TIME_SPAN, DESCRIPTION. |
|
CALENDAR YEAR |
ID, YEAR_NUMBER, START_DATE, END_DATE, TIME_SPAN, DESCRIPTION |
A hierarchy is a structure that uses ordered levels to organize data. It 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. A single time dimension cannot contain both fiscal and calendar hierarchies.
Calendar Hierarchy A calendar hierarchy must contain at least two of the following levels: DAY, CALENDAR_WEEK, CALENDAR_MONTH, CALENDAR_QUARTER, CALENDAR_YEAR.
There is no drill-up path from CALENDAR_WEEK to any other levels. Thus, if a calendar hierarchy contains CALENDAR_WEEK level, it cannot contain either the CALENDAR_MONTH, CALENDAR_QUARTER, or CALENDAR_YEAR levels.
Fiscal Hierarchy A fiscal hierarchy should contain at least two of the following levels: DAY, FISCAL_WEEK, FISCAL_MONTH, FISCAL_QUARTER, FISCAL_YEAR.
When you create a fiscal hierarchy, you must specify the following:
Start month of the fiscal year
Start date of the fiscal year
Start day for the fiscal week
Fiscal Convention used by the time dimension.
The options that you can select for fiscal convention are:
455: Select this option if the first month in the quarter has 4 weeks, the second month in the quarter has 5 weeks, and the third month in the quarter has 5 weeks.
544: Select this option if the first month in the quarter has 5 weeks, the second month in the quarter has 4 weeks, and the third month in the quarter has 4 weeks.
When you implement a time dimension, you specify how the time dimension and its data are physically stored. You can store the time dimension data either in a relational form or multidimensional form in the database.
The implementation of a time dimension is similar to the implementation of a regular dimension. For more information on implementing a dimension, see "Implementing a Dimension".
A time dimension created using the Create Time Dimension wizard uses the attribute ID as the surrogate identifier and the attribute CODE as the business identifier. The data type of both these attributes is NUMBER. When you create a cube that references a time dimension, the cube contains attributes that pertain to the surrogate identifier and the business identifier of the lowest level of the time dimension. Both these attributes have a data type of NUMBER.
When loading a cube, if you use a Warehouse Builder created time dimension as the source, both the source attributes and the cube attributes are of data type NUMBER. For example, consider a cube ALL_SALES that references two dimensions PRODUCTS and TIME_FISCAL. TIME_FISCAL is a calendar time dimension created using the Time Dimension wizard and it contains the levels Year, Month, and Day. When you create a map to load the ALL_SALES cube, you can directly map the attribute DAY_CODE of the Day level of TIME_FISCAL to the attribute ALL_SALES_DAY_CODE in the cube ALL_SALES. The data type of both these attributes is NUMBER.
Consider a scenario where you load data into the ALL_SALES cube from a source object in which the time data is stored as a DATE attribute. In this case, you cannot directly map the DATE attribute from the source to the attribute ALL_SALES_DAY_CODE of the ALL_SALES cube. Instead, you use an Expression operator in the mapping to convert the input DATE attribute to a NUMBER value and then load it into the ALL_SALES cube. In the Expression operator you convert the input using the following expression:
TO_NUMBER(TO_CHAR(input,'YYYYMMDD'))
where input represents the DATE attribute from the source object that needs to be converted to a NUMBER value. For information on using the Expression operator, see "Expression Operator" in the Warehouse Builder Online Help.
You populate a time dimension by creating a mapping that loads data into the time dimension. When you create a time dimension using the Create Time Dimension wizard, Warehouse Builder creates a mapping that populates the time dimension. The time dimension is populated based on the values of the following parameters:
Start year of the data
Number of years of the data
Start day and month of fiscal year (only for fiscal time dimensions)
Start day of fiscal week (only for fiscal time dimensions)
Fiscal type (only for fiscal time dimensions)
The values of these attributes are initialized at the time of creating the time dimension using the Create Time Dimension wizard. You can alter the values of these parameters using the Data Object Editor. To change the values of the start date of the calendar year and the number of calendar years, use the Name tab of the Data Object Editor. To change the values of the parameters pertaining to fiscal time dimensions, use the Fiscal Settings button on the Hierarchies tab of Data Object Editor.
Note:
When you alter the values of any of the parameters pertaining to the data to be loaded into the time dimension, you must re-create the map that loads the time dimension. For more information on re-creating the map, see "Hierarchies Tab" in the Warehouse Builder Online Help.Figure 6-5 displays a mapping to load a calendar time dimension. The Mapping Input operator DATE_INPUTS represents the attributes needed to populate the time dimension.
Figure 6-5 Mapping that Populates a Time Dimension
You can run a map that populates the time dimension multiple times. During each run you specify the attributes required to populate the time dimension. It is possible that a run of the mapping may overlap with the previous runs, meaning you may attempt to load data that already exists in the time dimension. In such a case, if a record was populated by a previous run, Warehouse Builder does not populate the data again.
For example, in the first run, you populate the time dimension with data from the year 2000 for 5 years. In the second run, you populate the time dimension with data from 2003 for 3 years. Since the records from beginning 2003 to end 2004 already exist in the time dimension, they are not created again.
Cubes contain measures and link to one or more dimensions. The axes of a cube contain dimension members and the body of the cube contains measure values. Most measures are additive. For example, sales data can be organized into a cube whose edges contain values for Time, Products, and Promotions dimensions and whose body contains values from the measures Value sales, and Dollar sales.
A cube is linked to dimension tables over foreign key constraints. Since data integrity is vital, these constraints are critical in a data warehousing environment. The constraints enforce referential integrity during the daily operations of the data warehouse.
Data analysis applications typically aggregate data across many dimensions. This enables them to look for anomalies or unusual patterns in the data. Using cubes is the most efficient way of performing these type of operations. In a relational implementation, when you design dimensions with warehouse keys, the cube row length is usually reduced. This is because warehouse keys are shorter than their natural counterparts. This results is lesser amount of storage space needed for the cube data. For a MOLAP implementation, OLAP uses VARCHAR2 keys.
A typical cube contains:
A primary key defined on a set of foreign key reference columns or, in the case of a data list, on an artificial key or a set of warehouse key columns. When the cube is a data list, the foreign key reference columns do not uniquely identify each row in the cube.
A set of foreign key reference columns that link the table with its dimensions.
A cube consists of the set of measures defined over a set of dimensions. To create a cube, you must define the following:
A measure is data, usually numeric and additive, that can be examined and analyzed. Examples of measures include sales, cost, and profit. A cube must have one or more measures. You can also perform aggregation of measures. Only numeric measures can be aggregated.
A cube is defined by a set of dimensions. A cube can refer to a level that is not the lowest level in a dimension.
For cubes that use a pure relational implementation, you can reuse the same dimension multiple times with the help of dimension roles. For more information on dimension roles, see "Dimension Roles".
Before you validate a cube, ensure that all the dimensions that the cube references are valid.
To define a dimension reference, specify the following:
The dimension and the level within the dimension to which the cube refers.
For a cube that uses a relational implementation, you can refer to intermediate levels in a dimension. However, for cubes that use a MOLAP implementation, you can only reference the lowest level in the dimension. Warehouse Builder supports a reference to the non surrogate identifier of a level, for example, the business keys.
For dimensions that use a relational or ROLAP implementation, a dimension role for each dimension to indicate what role the dimension reference is performing in the cube. Specifying the dimension role is optional.
When you define a MOLAP cube, the order in which you define the dimension references is important. The physical ordering of dimensions on disk is the same as the order in which you define the dimension references. The physical ordering is tightly coupled with the sparsity definition. Define the dimension references in the order of most dense to least dense. Time is usually a dense dimension, and listing it first expedites data loading and time-based analysis. For more information on defining dimension references, see "Dimensions Page" or "Dimensions Tab" in the Warehouse Builder Online Help. For more information on sparsity, see Advanced Dialog Box" in the Warehouse Builder Online Help.
You can define aggregations that should be performed on the cube. For ROLAP cubes, you can only define a single aggregation method for the cube. For MOLAP cubes, you can define a different aggregation method for each dimension of each measure. Warehouse Builder enables you to use the same aggregation function for all the cube measures or specify different aggregate functions for each measure.
Warehouse Builder supports the following default aggregation methods: SUM, SSUM (scaled SUM), AVERAGE, HAVERAGE (hierarchical average), MAX, MIN, FIRST, LAST, AND, OR, HIERARCHICAL_FIRST and HIERARCHICAL_LAST. If you do not want to perform aggregation, select NOAGG. The methods AND and OR are not applicable for cubes that use a multidimensional implementation.
Note:
You cannot define aggregation for pure relational cubes.The Sales cube stores aggregated sales data. It contains the following two measures: Value_sales and Dollar_sales.
Value_sales: Stores the amount of the sale in terms of the quantity sold.
Dollar_sales: Stores the amount of the sale.
Table 6-9 describes the dimensionality of the Sales cube. It lists the name of the dimension and the dimension level that the cube references.
When you implement a cube, you specify the physical storage details for the cube. You can implement a cube in a relational form or a multidimensional form in the database.
The types of implementation you can use for cubes are:
Relational implementation
ROLAP implementation
MOLAP implementation
To set the type of implementation for a cube, use the Deployment Option configuration property. For more details on setting this option, see "Configuring Cubes" in the Warehouse Builder Online Help.
The database object used to store the cube data is called a fact table. A cube must be implemented using only one fact table. The fact table contains columns for the cube measures and dimension references. For more information on setting the implementation option for a cube, see "Implementing Dimensional Objects".
To implement a cube:
Select a table or materialized view that will store the cube data.
For each measure, select a column that will store the measure data.
For each dimension reference, select a column that will store the dimension reference.
Each dimension reference corresponds to a column on the fact table and optionally a foreign key from the fact table to dimension table. The 1:n relationships from the fact tables to the dimension tables must be enforced.
Figure 6-6 displays the bindings for the relational implementation of the SALES cube. The data for the SALES cube is stored in a table called SALES.
Figure 6-6 Implementation of the Sales Cube
When you perform binding, you specify the database columns that will store the data of each measure and dimension reference of the cube. You can perform auto binding or manual binding for a cube. For more information on binding, see "Binding".
Auto Binding When you perform auto binding, Warehouse Builder creates the table that stores the cube data and then binds the cube measures and references to the database columns. For detailed steps on performing auto binding, see "Auto Binding".
When you perform auto binding for a cube, ensure that you auto bind the dimensions that a cube references before you auto bind the cube. You will not be able to deploy the cube if any dimension that the cube references has been auto bound after the cube was last auto bound.
For example, you create the SALES cube that references the TIMES and PRODUCTS dimensions and perform auto binding for the cube. You later modify the definition of the PRODUCTS dimension. If you now attempt to auto bind the SALES cube again, Warehouse Builder generates an error. You must first auto bind the PRODUCTS dimensions and then auto bind the cube.
Manual Binding In manual binding, you must first create the table or view that stores the cube data and then map the cube references and measures to the database columns that store their data. Alternatively, you can use an existing database table or view to store the cube data.
For information about how to perform manual binding, see "Manual Binding".
Storing the cube and its data in an analytic workspace is called a MOLAP implementation. You can store multiple cubes in the same analytic workspace. For more information on OLAP implementation, see "MOLAP Implementation of Dimensional Objects".
Certain business scenarios may require the dimensions in a cube to be evaluated in a particular order. The order in which the dimensions are evaluated is called the solve dependency order of the cube. For example, in the Sales cube, the Time dimension may need to be evaluated before the Products dimension. For each dimension of the cube, you can specify a dependency on another dimension of the cube.
The advantage of specifying the dependency order is that it enables Warehouse Builder to optimize the query speed of calculating the joins of the dimension and cubes. For example, retrieving results from the sales cube based on Time criteria may be more selective than retrieving result based on Products criteria. In this case, you can specify that for the Sales cube, the Products dimension depends on the Time dimension.
Specifying the solve dependency order is optional. If you do not specify a dependency order, the optimizer determines the solve-order with additional flexibility.
To create a target schema, you create any of the dimensional or relational objects listed in Table 6-1. You can design a relational target schema or a dimensional target schema. In this section, the term dimensions refers to both regular dimensions and slowly changing dimensions.
A relational target schema is one that contains relational data objects such as tables, views, materialized views, and sequences. All the warehouse data is stored in these objects.
To design a relational target schema:
If you have not already done so, create an Oracle module that will contain the objects for your target schema. Ensure that the location associated with this module refers to the target schema.
Create the relational data objects.
You may have already imported some existing target objects. To creates additional data objects, refer to "Creating Relational Data Objects".
Note that this step only creates the definitions of the objects in the workspace. To create the objects in the target schema, you must deploy these objects.
Configure the data objects.
In this step, you set the physical properties of the data objects. For example, you specify the name of the tablespace in which a table should be created. Each data object has a set of default configuration properties. You can choose to modify these default values.
Validate the data objects.
Validation verifies the metadata definitions and configuration properties of data objects. Correct any errors that are encountered during the validation.
Generate code that will create these data objects in the target schema.
Generation produces code that is required to create the data objects created in step 2 in the target schema.
A dimensional target schema uses dimensional objects to store the data warehouse data. Dimensional objects include dimensions and cubes. Dimensional objects transform the visualization of the target schema from a table-oriented environment to a more business-focussed environment. This helps you obtain answers to complex analytical queries quickly and more efficiently.
To design a dimensional target schema:
If you have not already done so, create the Oracle module that will contain your dimensional objects. Ensure that the location associated with this module refers to the target schema.
Create the dimensions required in your target schema.
See "Creating Dimensions". Note that this step only creates the definitions of the dimensions in the workspace. To create the objects in the target schema, you must deploy these dimensions.
Create time dimensions.
Data warehouses use time dimensions extensively to store temporal data. See "Creating Time Dimensions".
Create the cubes required for the target schema.
See "Creating Cubes".
Configure the dimensions and cubes.
Configure the dimensional objects you created in steps 2, 3, and 4 to set physical properties for these objects. You can accept the default properties or modify them.
Validate the dimensions and cubes.
In this step, you verify the metadata definitions and configuration properties of the dimensional objects created in steps 2, 3, and 4. Correct any errors resulting from the validation.
Generate code that will create these dimensions and cubes in the target schema.
To create data objects, you can either start the appropriate wizard or use the Data Object Editor. Some objects, such as dimensions and cubes, can be created using a wizard or the Data Object Editor. Some objects, such as tables, can be created using the Data Object Editor only.
For objects that can be created using a wizard or the Data Object Editor, you right-click the node for the object, select New, and then Using Wizard or Using Editor.
After using a wizard, you may want to modify the object in the editor. In that case, right-click the object and select Open Editor.
Relational data objects include tables, views, materialized views, and sequences. To create tables, views, and materialized views, use the Data Object Editor. Use the Create Sequence dialog box to create sequences.
You can create additional structures pertaining to relational objects such as constraints, indexes, and partitions. For more information about how to create these structures, see "Reference for Using Oracle Data Objects" in the Warehouse Builder Online Help.
To create relational data objects:
In the Project Explorer, expand the Oracle node that corresponds to the target schema.
Right-click the node that represents the type of data object you want to create and select New.
For example, to create a table, right-click the Tables node and select New. The Data Object Editor is displayed.
Navigate to the Details panel of the Data Object Editor.
Use the tabs in the Details panel to define the data object.
For more information about the details to be entered on each tab, click the arrow at the top of the Details panel and select Help.
You can create dimensions using the Create Dimension Wizard or the Data Object Editor. Use the wizard to create a fully functional dimension object quickly. If you choose a relational implementation for the dimension, the wizard creates the implementation tables in the target schema using auto binding.
The Data Object Editor provides maximum flexibility to create a dimension. You can perform certain advanced tasks only by using the Data Object Editor.
To create a dimension using the Create Dimension Wizard:
In the Project Explorer, expand the Oracle node that corresponds to the target schema.
Right-click the Dimensions node, select New, then Using Wizard.
The Welcome Page of the Create Dimension Wizard is displayed.
Click Next.
The Name and Description page is displayed.
Enter a name and an optional description for the dimension.
Dimension names should follow the rules specified in "Naming Conventions for Data Objects".
Enter details on the following wizard pages.
Storage Type page
Dimension Attributes page
Levels page
Level Attributes page
Slowly Changing Dimension page
For additional information about the information to be provided on each wizard page, click Help on the page.
Click Next.
The Pre Create Settings page is displayed. This page lists the objects created to implement the dimension. Review the entries on this page.
Click Next.
The Dimension Creation Progress page is displayed. The progress bar displays the progress of the dimension creation. Wait till the progress bar reaches 100%.
Click Next.
The Summary page is displayed. This page lists the details of the dimension created in the previous step.
Click Finish.
The definition of the dimension and its implementation objects, if any, are created. For a relational or ROLAP dimension, the implementation tables and the sequence used to load the surrogate identifier of the dimension are created. For MOLAP dimensions, the analytic workspace used to store the dimension is created.
To create a dimension using the Data Object Editor:
In the Project Explorer, right-click the Dimensions node in the target module, select New, then Using Editor.
The Data Object Editor is displayed.
Use the following tabs on the Dimension Details panel to define the dimension.
Name
Dimension names should conform to the rules specified in "Naming Conventions for Data Objects".
Storage
Attributes
Levels
Hierarchies
SCD
For more information about the details to be entered on each tab, click the arrow at the top of the Dimension Details panel and select Help.
When you use the Data Object Editor to create dimensions, the implementation objects are not automatically created. For relational and ROLAP dimensions, you can create the implementation tables that store the dimension data by performing Auto Binding.
You can create a fully functional time dimension using the Create Time Dimension Wizard. If you need more flexibility in defining your time dimension, use the Data Object Editor to create a dimension that stores temporal data. For information about using the Data Object Editor to create time dimensions, see "Creating Dimensions".
To create a time dimension using the Create Time Dimension Wizard:
In the Project Explorer, expand the Oracle node that corresponds to the target schema.
Right-click the Dimensions node, select New, then Using Time Wizard.
The Welcome Page of the Create Time Dimension Wizard is displayed.
Click Next.
The Name and Description page is displayed.
Enter a name and an optional description for the time dimension.
Time dimension names should follow the rules specified in "Naming Conventions for Data Objects".
Enter details on the following wizard pages. For information about the options on each wizard page, click Help.
Storage Type page
Data Generation page
Specify the range of data to be stored in the time dimension. Also indicate the type of data stored in the time dimension, fiscal or calendar.
Levels page
Select the levels in the time dimension. The levels displayed on this page depend on the option you chose on the Data Generation page.
Click Next.
The Pre Create Settings page is displayed. This page lists the objects created to implement the dimension. Review the entries on this page.
Click Next.
The Dimension Creation Progress page is displayed. The progress bar displays the progress of the dimension creation. Wait till the progress bar reaches 100%.
Click Next.
The Summary page is displayed. This page lists the details of the dimension being created.
Click Finish.
The definition of the time dimension and its implementation objects, if any, are created. A mapping that loads the time dimension is also created.
For a relational or ROLAP time dimension, the implementation tables and the sequence used to load the surrogate identifier of the time dimension are created. For MOLAP dimensions, the analytic workspace used to store the time dimension is created.
Use the Create Cube Wizard or the Data Object Editor to create cubes.
To create a cube using the Create Cube Wizard:
In the Project Explorer, expand the Oracle node that corresponds to the target schema.
Right-click the Cubes node, select New, then Using Wizard.
The Welcome Page of the Create Cube Wizard is displayed.
Click Next.
The Name and Description page is displayed.
Enter a name and an optional description for the cube.
Cube names should follow the rules specified in "Naming Conventions for Data Objects".
Enter details on the following wizard pages. For information about the options on each wizard page, click Help.
Storage Type page
Dimensions page
Measures page
See "Cube Measures"
Click Next.
The Summary page is displayed. This page lists the details of the cube being created.
Click Finish.
The definition of the cube and its implementation objects, if any, are created. For a relational or ROLAP cube, the implementation tables are created. For MOLAP cubes, the analytic workspace used to store the time dimension is created.
Configuration defines the physical characteristics of data objects. For example, you can define a tablespace and set performance parameters in the configuration of a table. Or you can specify the type of implementation for dimensional objects. You can change the configuration of an object any time prior to deployment.
You can define multiple configurations for the same set of objects. This feature is useful when deploying to multiple environments, such as test and production. For more information, see "Creating Additional Configurations".
All objects have a Deployable parameter, which is selected by default. To prevent an object from being deployed, clear this parameter.
You can configure objects using the Data Object Editor or the Project Explorer. To configure an object using the Data Object Editor, use the Configuration panel of the editor. This panel displays the configuration details for the object currently selected on the canvas. You can even drill down to, say and index in a table in the Selected Objects tab of the Explorer panel to see those configuration details.
To configure an object using the Project Explorer:
In the Project Explorer, select the object and click the Configure icon.
or
Right-click the object and select Configure.
The Configuration Properties dialog box is displayed.
Select a parameter to display its description at the bottom of the right panel. Click Help for additional information.
Enter your changes and click OK.
Validation is the process of verifying metadata definitions and configuration parameters. These definitions must be valid before you proceed to generation and deployment of scripts.
Warehouse Builder runs a series of validation tests to ensure that data object definitions are complete and that scripts can be generated and deployed. When these tests are complete, the results display. Warehouse Builder enables you to open object editors and make corrections to any invalid objects before continuing. In addition to being a standalone operation, validation also takes place implicitly when you generate or deploy objects.
To detect possible problems and deal with them as they arise, you can validate in two stages: after creating data object definitions, and after configuring objects for deployment. In this case, validating objects after configuration is more extensive than validating object definitions.
Tip:
Validate objects as you create and configure them to resolve problems as they arise. The same error-checking processes are run whether you are validating the design or configuration.When you validate an object after it has been defined, the metadata definitions for the objects you have designed are checked for errors. For example, if you create a table, Warehouse Builder requires that columns be defined. When this object is validated, Warehouse Builder verifies that all components of the table have been defined. If these components are missing, validation messages display in the Validation Results window.
If you validate an object after it has been configured, metadata definitions are re-checked for errors and configuration parameters are checked to ensure that the object will be generated and deployed without any problems. You can then make edits to invalid objects.
You can validate a single object or multiple objects at a time. You can also validate objects that contain objects, such as modules and projects. In this case, all data objects contained by that object are validated. Use the Project Explorer or the Data Object Editor to validate data objects.
When you validate objects, Warehouse Builder displays the Validation Results window that contains the results of the validation. For more information about this dialog box, click Help and then Topic.
Validating Data Objects Using the Project Explorer
In the Project Explorer, select the data object and click the Validate icon. You can select multiple objects by holding down the Ctrl key while selecting objects.
or
In the Project Explorer, select the data object or data objects. To select multiple objects, hold down the Ctrl key while selecting objects. Right-click the data object and select Validate. If you selected multiple objects, ensure that the Ctrl key is pressed when you right-click.
Validating Data Objects Using the Data Object Editor
Right-click the icon representing the data object on the Data Object Editor canvas and select Validate.
or
Select the object on the canvas and either click the Validate icon or select Validate from the Object menu.
The results of validating data objects are displayed in the Validation Results window. From this window, you can access the editors for objects and rectify errors in their definition, if any.
To edit invalid definitions:
In the Validation Results window, double-click an invalid object from the tree or from the validation messages grid.
An editor for the selected object is displayed.
Edit the object to correct problems.
Close the editor when you are finished and re-validate.
When you generate data objects, Warehouse Builder produces the code required to create the data objects in the target schema. Warehouse Builder generates the following types of scripts:
DDL scripts: Creates or drops database objects.
SQL*Loader control files: Extracts and transports data from file sources.
ABAP Scripts: Extracts and loads data from SAP systems.
You can view the generated scripts and also store them to a file system.
When you generate code for a data object, Warehouse Builder first validates the object and then generates code.You may skip the validation step and directly generate code for your data objects. However, it is recommended that you validate objects before you generate them. This enables you to discover and correct any errors in data object definitions before the code is generated.
Use the Project Explorer or the Data Object Editor to generate code for data objects. When you generate objects, Warehouse Builder displays the Generation Results window that contains the results of the generation. For more information about this window, click Help and then Topic.
Generating Data Objects Using the Project Explorer
To generate a single data object, select the data object and click the Generate icon. Or right-click the data object and select Generate.
To generate code for multiple objects, select the objects by holding down the Ctrl key and click the Generate icon. Or select the data objects and, while continuing to hold down the Ctrl key, right-click and select Generate.
Generating Objects Using the Data Object Editor
Open the Data Object Editor for the data object by right-clicking the object and selecting Open Editor. The canvas displays a node that represents the data object.
Right-click the data object node on the canvas and select Generate.
or
Select the data object node on the canvas. Click the Generate icon or select Generate from the Object menu.
To view the generated scripts:
From the Generation Results window, select an object in the navigation tree on the left of the Generation Results window.
Select the Scripts tab on the right of this window.
The Scripts tab contains a list of the generated scripts for the object you selected.
Select a specific script and click View Code.
The selected script displays in a code viewer, which is read-only.
To save generated scripts:
From the Generation Results window, select an object from the navigation tree on the left.
Select the Scripts tab from the bottom section of the window.
The Scripts tab contains a list of the generated scripts for the object you selected.
Select a specific script and click Save As.
The Save dialog box opens and you can select a location where you want to save the script file.
Warehouse Builder enables you to derive business intelligence objects from your existing relational and dimensional data objects. When you derive intelligence objects, Warehouse Builder tailors existing definitions to match the definitions used by the Oracle Discoverer End User Layer.
You can deploy intelligence objects derived from data warehouse design definitions directly to Oracle Discoverer.
To derive intelligence objects:
If you have not already done so, create a business definition module that will contain the derived business intelligence objects.
To create a business definition module, expand the Business Intelligence node in the Project Explorer, right-click Business Definitions and select New. The Create Business Definition Module Wizard displays. Specify a name and an optional description for the business definition module. Ensure that the location associated with this module refers to the Discoverer EUL to which the derived business definitions will be deployed.
To derive all the objects in an Oracle module, right-click the Oracle module in the Project Explorer and select Derive.
To derive a particular object, right-click that object in the Project Explorer and select Derive.
The Welcome page of the Perform Derivation Wizard is displayed. Click Next to proceed with the derivation.
On the Source Objects page, the Selected section displays the objects you selected in step 2. To derive additional objects, select the objects and move them from the Available list to the Selected list.
On the Target page, select the business definition module or business area that will contain the derived objects.
On the Rules page, specify the rules and parameters for the derivation.
For more information about the rules and parameters, click Help on this page.
On the Pre Derivation page, review the selections you made. Click Back to modify selected values. Click Next to proceed.
The Derivation page displays a progress bar that indicates the progress of the derivation. Wait until the progress reaches 100% and click Finish to complete the derivation.
Once you derive business definitions, you can directly deploy them to Oracle Discoverer. For information about deploying to Discoverer, see "Deploying Business Definitions to Oracle Discoverer".
