| Oracle® Warehouse Builder User's Guide 10g Release 2 (10.2.0.2) Part Number B28223-05 |
|
|
View PDF |
| Oracle® Warehouse Builder User's Guide 10g Release 2 (10.2.0.2) Part Number B28223-05 |
|
|
View PDF |
Target schemas include all the necessary data objects such as tables, views, dimensions, and cubes. In a traditional data warehousing implementation, there is typically only one target schema, which is the data warehouse target. In Warehouse Builder, you design target schemas using the Data Object Editor.
This chapter includes the following topics:
In previous steps, you may have already imported existing target objects. For new target objects, design any of the dimensional or relational objects listed in Table 4-1.
To create data objects, you can either launch the appropriate wizard or use the Data Object Editor. To use a wizard, right-click the node for the desired object and select New. 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.
The Oracle Database module contains nodes for each type of data object 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, as shown in Figure 4-1.
Figure 4-1 Data Objects within a Warehouse Module
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. This chapter provides specific information about each type of relational object and how it is used in Warehouse Builder.
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 Explorer. Intelligence objects enable you to store definitions of business views and report structures. You can deploy these definitions to analytical tools such as Oracle Discoverer and BI Beans and perform ad hoc queries on the warehouse. For more information about intelligence objects, see Chapter 15, "Defining Business Intelligence Objects".
Table 4-1 describes the types of data objects you can use in Warehouse Builder.
Table 4-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" 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" 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" 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" 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" for more information. |
|
Dimensions |
Dimensional |
A general term for any characteristic that is used to specify the members of a data set. The 3 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 a .mdl file using a previous version of Warehouse Builder. |
|
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 RAW. 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" for more information. |
|
Varrays |
Relational |
A varray is an ordered collection of elements. See "About Varrays" 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" for more information. |
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. Warehouse Builder maintains a business and a physical name for each object stored in the repository. 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.
Table 4-2 shows the data types you can use to create and edit columns.
Table 4-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 BLOB variable stores a locator, which points to a large binary object. The size of a BLOB cannot exceed four gigabytes. |
|
|
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 CLOB variable stores a locator, which points to a large block of character data. The size of a CLOB cannot exceed four gigabytes. |
|
|
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 SYSDATE returns the current date and time. |
|
|
Stores a single-precision, floating-point, number. FLOAT can be loaded with correct results only between systems where the representation of a FLOAT is compatible and of the same length. |
|
|
A NUMBER subtype that stores integer values with a maximum precision of 38 decimal digits. |
|
|
Stores intervals of days, hours, minutes, and seconds. |
|
|
Stores intervals of years and months. |
|
|
Stores fixed-length character strings. The LONG datatype is like the VARCHAR2 datatype, except that the maximum length of a LONG value is 2147483647 bytes (two gigabytes). |
|
|
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 MDSYS.SDO_DIM_ELEMENT. |
|
|
Stores the dimension name, lower boundary, upper boundary and tolerance. |
|
|
Stores an array of typeMDSYS.SDO_ORDINATE_ARRAY. |
|
|
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 NCHAR data in the database, in-line or out-of-line. |
|
|
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 NUMBER column. |
|
|
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 RAW variable might store a sequence of graphics characters or a digitized picture. Raw data is like VARCHAR2 data, except that PL/SQL does not interpret raw data. |
|
|
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 DATE data type and stores the year, month, day, hour, minute, and second. The default timestamp format is set by the Oracle initialization parameter NLS_TIMESTAMP_FORMAT. |
|
|
Extends the TIMESTAMP data type and includes a time-zone displacement. The time-zone displacement is the difference (in hours and minutes) between local time and Coordinated Universal Time (UTC)—formerly Greenwich Mean Time. You can also use named time zones, as with TIMESTAMP WITH TIME ZONE. |
|
|
Extends the data type TIMESTAMP and includes a time-zone displacement. The time-zone displacement is the difference (in hours and minutes) between local time and Coordinated Universal Time (UTC)—formerly Greenwich Mean Time. |
|
|
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 VARCHAR2 data type takes a required parameter that specifies a maximum size up to 4000 characters. |
|
|
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 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.
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.
Launching the Data Object Editor
You can use one of the following methods to launch the Data Object Editor:
Select a data object in the Project Explorer and choose Edit, then Open Editor from the Design Center menu.
Right-click a data object in the Project Explorer and select Open Editor.
Double-click a data object in the Project Explorer.
Figure 4-2 displays the Data Object Editor.
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.
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.
The Data Object Editor has the following components:
The title bar is located at the top of editor window. It displays the title Data Object Editor.
The menu bar, situated below the title bar, provides access to the editor commands. You can access the editor commands either by clicking the menu bar options or by using hot keys. For example, to access the Diagram menu, you can click Diagram or type Alt+D.
The menu bar options enable you to create, edit, validate, and deploy data objects. The menu bar contains the following menu items:
The Diagram menu provides options to create, validate, generate, and deploy relational, dimensional, and business intelligence objects. You can also perform binding and unbinding for dimensional objects. The menu items in the Diagram menu apply to the currently selected tab on the canvas.
The Diagram menu contains the following menu options:
Close Window: Closes the Data Object Editor.
Export Diagram: Saves the current diagram displayed on the canvas as a file on your machine either as an SVG file or JPEG file.
Save All: Saves changes made to the repository.
Add: Enables you to add data objects to the editor canvas. Use the options in the Add menu item to select the type of object that you want to add. The options in the Add menu item are Table, View, Materialized View, Object Type, Varray, Nested Table, Dimension, Cube, Item Folder, and Business Area.
Notice that only some of the options in the Add menu item are enabled. The Add menu options that are enabled depend on the canvas tab that is currently selected. For example, when the Dimensional tab is selected on the canvas, the options Dimension and Cube of the Add menu are enabled. For more information on the Data Object Editor canvas, see "Canvas".
Bind All: Performs binding for all the dimensional objects on the canvas. This option is only enabled when the Dimensional tab is selected.
Binding is applicable only to dimensional objects that have a a relational implementation. For more information on binding, see "Binding".
Unbind All: Unbinds all the dimensional objects on the canvas. Unbinding removes the mapping between a dimensional object and the database objects that store its data. This option is enabled only when the Dimensional tab of the canvas is selected.
Unbinding is applicable only to dimensional objects that have a relational implementation.
Validate All: Validates all the data objects on the canvas. The results of the validation are displayed in the Generation panel.
Generate All: Generates all the data objects on the canvas. The generation results are displayed in the Generation panel.
Derive All: Derives business intelligence objects for all the relational or dimensional objects on the canvas.
Deploy All: Deploys all the data objects on the canvas.
Print Preview: Provides a preview of the contents of the canvas before printing.
Print: Prints the contents of the canvas.
The Object menu contains options to validate, generate, and deploy the currently selected data object(s). You can also bind and unbind dimensional objects. You can select multiple data objects by holding down the Shift key.
The Object menu contains the following options:
Bind: Binds the dimensional objects selected on the canvas to the database tables that store its data. Warehouse Builder creates the database tables that store the dimensional object data.
You can perform binding only for dimensional objects that use a relational implementation.
Unbind: Removes the bindings between the selected dimensional objects and the database tables that store their data. Warehouse Builder does not delete the tables that have been unbound. You will need to explicitly delete the tables.
You can perform unbinding only for dimensional objects that use a relational implementation.
Validate: Validates the data object selected on the canvas. The results of the validation are displayed in the Generation window.
Generate: Generates the data object selected on the canvas. The results of the generation are displayed in the Generation window.
Derive: Derives a business intelligence object from the selected data object.
Deploy: Deploys the data object selected on the canvas.
Show Related: Displays the data objects to which the selected data object is related on the canvas. For example, for a cube, selecting this option displays the dimensions that the cube references on the canvas.
The Edit menu enables you to remove, hide, copy, and paste data objects on the canvas. The Edit menu contains the following options:
Copy: Copies the selected object.
Paste: Pastes the data object from the clipboard on to the canvas.
Delete: Deletes the data object from the repository.
Hide: Removes a data object from the canvas without deleting it from the repository.
Select All: Selects all the data objects on the editor canvas.
Refresh: Refreshes the data displayed on the editor panels. For example, you open the Data Object Editor for the table EMPL. In the Design Center, you rename the view EMP_VIEW to EMPL_VIEW. When you refresh the editor, the new name for the view is displayed in the Explorer panel of the Data Object Editor.
The View option enables you to change viewing preferences in the editor such as the zoom setting, show or hide toolbars, layout of the objects in the editor (center, fit in window, auto layout). You can also maximize or minimize data objects on the canvas.
Toolbars: Enables you to select the types of toolbars that should be displayed in the editor. The options you can select are Generic and Graphic. For more information on toolbars, see "Toolbar".
Zoom: Increases or decreases the size of the objects displayed in the canvas. The options you can select to zoom the canvas are 400%, 200%, 100%, 75%, 50%, and 25%.
Fit in Window: Arranges the objects on the canvas so that they fit on the editor canvas. The size of the objects could be increased or decreased depending on the number of objects on the canvas.
Auto Layout: Displays the data objects on the canvas in their default size and layout.
Center: Centers the data objects on the canvas.
Minimize: Minimizes the selected data object on the canvas.
Restore Size: Restores the size of a minimized data object and displays it on the canvas.
Maximize: Maximizes the selected data object on the canvas.
Set Size to Default: Displays the selected data object in its default size. For example, you may have resized the data object using the double-sided arrow that appears when you position the cursor at the edge of a data object. This option displays the data object in its default size.
Clipboard Contents: Displays the Clipboard Contents dialog that displays details about the contents of the clipboard.
Enable Horizontal Scrolling: Enables or disables horizontal scrolling for all the data objects displayed on the canvas.
Use the Window menu to show or hide the panels in the Data Object Editor. The window menu contains the following options:
Explorer: Shows or hides the Explorer panel of the Data Object Editor.
Palette: Shows or hides the Data Object Editor Palette.
Bird's Eye View: Shows or hides the Bird's Eye View panel of the Data Object Editor.
Generation Results: Shows or hides the Generation Results panel of the Data Object Editor.
Object Details: Shows or hides the Object Details panel of the Data Object Editor.
Configuration Panel: Shows or hides the Configuration panel of the Data Object Editor.
Arrange All: Arranges all the panels in the editor window.
All the options are toggle options. For example, select Window and then Object Details to show or hide the object details panel.
The Help menu options provide access to the online help, Oracle Technology Network, and information about the version of Warehouse Builder. Use the About Warehouse Builder option to view the version of the Warehouse Builder client and the repository version.
Use the Session Properties option to display the Session Properties dialog. This dialog contains information about the current Warehouse Builder session such as the user details, version of the Oracle Database, and roles granted to the currently logged in user.
The toolbar, located below the menu bar, provides icons for the commonly used commands. It provides shortcuts to access the editor menu items. For example, the toolbar contains an icon to deploy a data object.
The Data Object Editor contains two tool bars: Generic and Graphic. The generic toolbar enables you to invoke common operations such as validation, generation, deployment, and synchronization of data objects. The graphic toolbar enables you to navigate the canvas and change the magnification of objects on the canvas.
You can hide the toolbars using the View option of the editor. You can also move the toolbars to any part of the editor window by clicking the drag handle on the left of the toolbar and dragging to the new position.
When you first open the Data Object Editor, the Explorer displays on the top left corner. You can relocate the Explorer anywhere on the Data Object Editor.
The Explorer provides a view, similar to a folder structure, of the data objects that are available to be viewed or edited on the canvas. The Explorer is synchronized with the currently active tab of the Data Object Editor canvas. Thus the objects displayed by the Explorer depend on the tab that is selected on the canvas. For more information on the different tabs on the canvas, see "Canvas".
The Explorer also enables you to view the data objects that are currently displayed on the canvas along with details of their definition. For example, a table node can be expanded to display details of the constraints, indexes, and so on.
The Explorer contains two tabs: Available Objects and Selected Objects. Data objects that are selected on the Selected Objects tree are also selected on the canvas.
Available Objects This tab displays the data objects that you can view or edit on the canvas. You can view or edit a data object by dragging the object from this tab and dropping it on to the canvas. Warehouse Builder displays a node for each module. Expand the module node to view the data objects in that module.
Figure 4-3 displays the Available Objects tab of the Explorer panel.
Figure 4-3 Available Objects Tab of the Explorer Panel
The data objects displayed on this tab depend on the tab that is currently selected on the canvas. For example, a module called MOD1 contains three tables, four dimensions, and a cube. When the Relational tab is selected on the canvas, the Available Objects tab displays the three tables under the MOD1 node. when the Dimensional tab is selected in the canvas, the Available Objects tab displays the four dimensions and the cube under the MOD1 node of the Explorer.
Selected Objects The Selected Objects tab displays a node for each data object displayed on the canvas. Expand the node to view the details of the object definition. For example, when you expand a node for a database table, you will see the columns, keys, partitions, and indexes defined on the table.
When you open the Data Object Editor, the Palette is displayed on the left side of the editor window. You can relocate it anywhere in the editor window.
The Data Object Editor Palette displays icons for the type of data objects that you can drag and drop onto the canvas. Some icons in the palette may be disabled. The Palette, like the Explorer, is synchronized with the canvas. The data objects that are enabled in the Palette depend on the currently active tab on the canvas. For example, when the Relational tab is currently active in the canvas, all the relational data objects in the Palette are enabled.
At the top of the palette is a drop-down list that you can use to display only a particular type of data objects in the palette. The options in the drop-down list are: All, Relational, Dimensional, and Business Definition. Selecting a type from this drop-down list displays the icons for the objects belonging to that type only.
For example, when you select Dimensional from this drop-down list, only icons for dimensional objects are displayed in the palette.
The Configuration panel displays the configuration properties of the data object selected on the canvas or the Selected Objects tab of the Explorer. Use this window to set or edit configuration properties for the selected data object. When you first open the Data Object Editor, the Configuration panel is displayed on the top left. You can relocate it anywhere on the editor window.
The Bird's Eye View enables you to move the view of the canvas with a single mouse dragging operation. You can thus reposition your view of the canvas without using the scroll bars.
The Bird's Eye View displays a miniature version of the entire canvas. It contains a blue colored box that represents the portion of the canvas that is currently in focus. In the case of mappings that span more than the canvas size, you can click the blue box and drag it to the portion of the canvas that you want to focus on.
The canvas is the central graphical area that displays relational, dimensional, and business intelligence objects. The canvas is the area where data objects and their associations, if any, are represented graphically. You can also display two objects on the canvas that have no relationship to each other.
When you open the Data Object Editor for a particular object, the canvas displays the data object along with the other data objects that are related to it and represents the objects and their relationship graphically. For example, when you open the Data Object Editor for a cube, the canvas displays the cube and the dimensions that the cube references.
Each object on the canvas is represented by a node. Figure 4-4 displays the canvas for the EMPLOYEES table. The node that represents this table contains two groups: Columns and Keys. The columns group represents the columns in the table. For each column, the column name and the data type are displayed on the canvas. The Keys group displays the primary, foreign, and unique keys in the table along with the columns that make up the key.
The canvas also contains nodes for the tables DEPARTMENTS and JOB_HISTORY. The foreign key relationship between these tables can be seen.
The canvas uses the following tabs to group data objects of similar type:
Relational
Dimensional
Business Definition
All these tabs are displayed when the editor is first opened. Each tab is used to display information for the type of object that it represents. For example, when you are creating or editing a dimension, the dimension is represented graphically on the Dimensional tab. The Relational tab is used to represent tables, views, or materialized views.
In addition to these tabs, the canvas uses an additional tab that displays a detailed representation for a data object. You can view a detailed representation for any data object on the canvas. On the canvas, right-click the node that represents the data object and select Detail View.
Figure 4-5 displays the detail view of the CHANNELS dimension. The dimension data is stored in the table CHANNELS. The relationship depicts the table column that stores the dimension attribute data.
At the bottom of the Canvas panel is an icon that you use to display the graph. In addition to this, the detail view contains two icons to display the lineage and impact analysis.
The shortcut menu on the canvas enables you to perform operations such as creating, validating, generating, and deploying data objects. The Menu Bar section described how you perform the same operations using the menu bar.
Right-click the title bar of the node that represents the data object. Warehouse Builder displays a shortcut menu with the following options:
Generate: Generates the selected data object. The results are displayed in the Generation panel.
Validate: Validates the selected data object. The results are displayed in the Generation panel.
Deploy: Deploys the selected data object.
Derive: Derives a business intelligence object using the selected data object. For more information on deriving intelligence objects, see "Deriving Business Intelligence Objects".
Copy: Copies the selected data object. You can then paste this data object in a different module or project. You can then paste this object on the canvas thus creating a new object in the same schema as the original.
Hide Object: Hides the data object on the canvas. The data object is not deleted.
Remove from repository: Deletes the data object from the repository.
Detail View: Creates a new tab in the canvas that contains a detail view of the selected data object. For dimensional objects with a relational implementation, this tab displays the implementation details. Use this tab to perform manual binding for dimensional objects.
Show Related: Displays objects related to the selected data object on the canvas.
Auto Bind: Performs auto binding for dimensional objects. This options is enabled only for dimensional objects that use a relational implementation. For more information on auto binding, see "Binding".
Unbind: Removes the bindings between the selected dimensional object and the data object that stores the dimensional object data. For more information on auto binding, see "Binding".
You can also use the shortcut menu of the canvas to add data objects to the canvas. Right-click any blank area (whitespace) on the canvas. This displays a list of objects that you can add to the canvas in the current context. The list of items differs based on the tab that is currently selected on the canvas. For example, when you right-click a blank area on the Dimensional tab, you see options to add cubes and dimensions. The shortcut menu of the Relational tab provides options to add tables, views, materialized views, object types, varrays, and nested tables.
The Details panel contains a set of tabs that you use to define the data object. The title of the Details panel contains the type, module name, and name of the data object. These details are suffixed by the mode in which the data object is open. For example, if you open the Data Object Editor for a table called DEPT which is stored in the HR module, the name of the window is Table Details: HR.DEPT "Read/Write". If no object is selected on the canvas, the Details panel displays "Details: No object selected".
The tabs displayed in the Details panel depend on the type of data object selected on the canvas. For example, the number and names of tabs for a table are different from that for a dimension. Use the tabs on the Details panel to define or edit the data object definition. Any changes you make on the Details panel are stored in the repository. The canvas and Explorer are also refreshed to reflect the changes.
For more information on the tabs for each data object, refer to the following sections:
The Generation panel displays the generation and validation results for a data object. This panel is hidden when you first open the editor window. It is displayed the first time you generate or validate a data object. You can to show or hide the Generation panel by selecting Window and then Generation Results from the editor menu.
The Generation window contains two tabs: Script and Message. The Script tab displays the scripts generated by Warehouse Builder to implement the data object selected in the canvas. The Message tab displays the validation messages for the selected data object. Double-click a message to view the complete message text.
Along the lower edge of the editor you see mode icons and indicators as shown in Figure 4-6. The left corner contains the Naming Mode and the Rename Mode icon indicators. Position your mouse on an icons to display the current setting for the mode. The right side displays the zoom indicator and the navigation mode.
Figure 4-6 Indicator Bar of the Data Object Editor
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 of the data object, navigate to the Data Viewer tab of the Details panel. Click the Execute Query button.
Figure 4-7 displays the data viewer for the COUNTRIES table.
The Data Viewer tab contains three buttons: Execute Query, Get More, and Where Clause. When you navigate to this tab, it displays only these buttons. Click the Execute Query button to execute a query on the data object and fetch its data.
Use the Get More button to fetch more data from the data object. By default, the Data Viewer displays the first hundred rows of data. To see the remaining data use the Get More button. Alternatively, you can use the More button at the bottom of the Data Viewer to perform the same action.
The Where Clause button is used to specify a condition that restricts the data displayed by the Data Viewer. This option is applicable for tables and views only. Clicking this button displays the Where Clause dialog. Use this dialog to specify the condition used to filter data.
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. The Data Object Editor should be open to use any of these methods.
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:
Open the Data Object Editor.
Use any of the methods described in "Launching 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.
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. For details on this dialog, see "Add a New or Existing Data Object Dialog".
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:
Open the Data Object Editor.
Use any of the methods described in "Launching 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 that contains the type of data objects you can create. For details on this dialog, see "Add a New or Existing Data Object Dialog".
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.
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:
Open the Data Object Editor.
Use any of the methods described in "Launching 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 a shortcut menu that contains the type of data objects you can create. For details on this dialog, see "Add a New or Existing Data Object Dialog".
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.
The Add a New or Existing <data object> dialog enables you to add new or existing data objects to the Data Object Editor canvas. The new data objects added using this dialog are stored in the repository.
Select one of the following options:
Create a New <data object> Use this option to create a new Oracle data object such as a table, dimension, or item folder. Enter the name of the data object in the New <data object> Name field. Warehouse Builder displays <data object type>_1 as the default name for the data object. You can change this default name by selecting the name and entering the new name.
Use the Oracle Module drop-down list to select the Oracle module to which the data object should belong. For relational and dimensional objects, Warehouse Builder initially displays the module to which the data object that is currently open in the editor belongs. You can change this selection. For business intelligence object, Warehouse Builder displays the business definition module to which the data object belongs.
For Item Folders, an additional drop-down list called Item Folder Type is displayed. Use this drop-down list to indicate whether you want to create a simple or a complex item folder.
After specifying these details for the data object, click OK. Warehouse Builder creates the data object and adds a node for this data object on the editor canvas. Use the tabs in the Details window to define the data object.
Select an existing <data object> Use this option to add an existing repository data object to the editor canvas. You may want to do this to define relationships between data objects. For example, you may want to manually bind the dimension attributes to the database columns that store their data.
To search for a data object by name, type the name of the data object in the Search For field and click Go. Alternatively, you can select the name of the data object from the selection tree displayed below the Search For field.
After you select the data object, click OK. Warehouse Builder adds a node for this data object on the editor canvas.
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.
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.
All objects have a Deployable parameter, which is selected by default. To prevent an object from being deployed, clear this parameter.
To configure an object:
In the Project Explorer, select the object and click the Configure icon.
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.
An attribute set contains a chosen set of columns in the order you specify. Attribute sets are useful while defining a mapping or during data import and export. Warehouse Builder enables you to define attribute sets for tables, views, and materialized views. For the sake of brevity, in the following sections, the word table refers to all objects for which you can define attribute sets
For each table, Warehouse Builder generates a predefined attribute set containing all the columns in that table. In addition, Warehouse Builder generates predefined attribute sets for each defined constraint. Predefined attribute sets cannot be modified or deleted.
For more information about creating and editing attribute sets, refer to the following:
Constraints are used to enforce the business rules you want to associate with the information in a database. Constraints prevent the entry of invalid data into tables. Business rules specify conditions and relationships that must always be true, or must always be false. In Warehouse Builder, you can create constraints for tables, views, and materialized views.
For example, if you define a constraint for the salary column of the employees table as Salary < 10,000, this constraint enforces the rule that no row in this table can contain a numeric value greater than 10,000 in this column. If an INSERT or UPDATE statement attempts to violate this integrity constraint, then Oracle displays an error message. Keep in mind that constraints slow down load performance.
You can define the following constraints for tables, views, and materialized views:
Unique Key (UK): A UNIQUE key constraint requires that every value in a column or set of columns (key) be unique. No two rows of a table can have duplicate values in a specified column or set of columns. A UK column can also contain a null value.
Primary Key (PK): A value defined on a key (column or set of columns) specifying that each row in the table can be uniquely identified by the values in the key (column or set of columns). No two rows of a table can have duplicate values in the specified column or set of columns. Each table in the database can have only one PK constraint. A PK column cannot contain a null value.
Foreign Key (FK): A rule defined on a key (column or set of columns) in one table that guarantees that the values in that key match the values in a PK or UK key (column or set of columns) of a referenced table.
Check Constraint: A user-defined rule for a column (or set of columns) that restricts inserts and updates of a row based on the value it contains for the column (or set of columns). A Check condition must be a Boolean expression which is evaluated using the values in the row being inserted or updated. For example, the condition Order Date < Ship Date will check that the value of the Order Date column is always less than that of the Ship Date column. If not, there will be an error when the table is loaded and the record will be rejected. A check condition cannot contain subqueries and sequences or SYSDATE, UID, USER, or USERENV SQL functions. While check constraints are useful for data validation, they slow down load performance.
For information on using constraints in a table, refer to the following sections:
Use indexes to enhance query performance of your data warehouse. In Warehouse Builder, you can create indexes for tables, materialized views, dimensions, and cubes. For the sake of brevity, in the following sections, the word table refers to all objects for which you can define indexes.
Indexes are important for speeding queries by quickly accessing data processed in a warehouse. You can create indexes on one or more columns of a table to speed SQL statement execution on that table. Indexes have the following characteristics:
Indexes provide pointers to the rows in a table that contain a given key value.
Index column values are stored presorted.
Because the database stores indexes in a separate area of the database, you can create and drop indexes at any time without effecting on the underlying table.
Indexes are independent of the data in the table. When you delete, add, or update data, the indexes are maintained automatically.
To learn more about indexes and indexing strategies, see the Oracle 10g Data Warehousing Guide.
In general, you define indexes by using the Indexes tab in the Data Object Editor. To launch the Data Object Editor, navigate to the table or other data object on the Project Explorer and double-click it or right-click and select Open Editor. When you select an index type, Warehouse Builder displays the appropriate template enabling you to define the index.
For all types of indexes except bitmap indexes, you can determine whether or it inherits the partitioning method of the underlying table. An index that inherits its partitioning method is known as a local index while an index with its own partitioning method is known as a global index. For additional information, see "Index Partitioning".
You can create the following types of indexes in Warehouse Builder:
Unique: These indexes ensure that no two rows of a table have duplicate values in the key column or composite key columns.
Normal: Also known as non unique indexes, these are b-tree type indexes that do not impose restrictions against duplicate values in the key column or composite key columns.
Bitmap: These indexes are primarily used for data warehousing applications to enable the querying of large amounts of data. These indexes use bitmaps as key values instead of a list of row ids. Bitmaps are effective when the values for the index key comprise a small list. For example, AGE_GROUP could be a good index key but AGE would not.
Bitmaps enable star query transformations which are cost-based query transformations aimed at efficiently executing star queries. A prerequisite of the star transformation is that a bitmap index must be built on each of the foreign key columns of the cube or cubes.
When you define a bitmap index in Warehouse Builder, set its scope to LOCAL and partitioning to NONE.
Function-based: These indexes compute and store the value of a function or expression you define on one or more columns in a table. The function can be an arithmetic expression that contains a PL/SQL function, package function, C callout, or SQL function.
Composite: Also known as concatenated indexes, these are indexes on multiple columns. The columns can be in any order in the table and need not be adjacent to each other.
To define a composite index in Warehouse Builder, create the index as you would any other index and assign between 2 and 32 index columns.
Reverse: For each indexed column except for the rowid column, this index reverses the bytes in the columns. Since rowid is not reversed, this index maintains the column order.
To define a reverse index in Warehouse Builder, create the index as you would any other index and then go to the configurations window and set Index Sorting listed under the Performance Parameters to REVERSE.
Partitions enable you to efficiently manage very large tables and indexes by dividing them into smaller, more manageable parts. Partitions improve query and load performance because operations work on subsets of data. Use partitions to enhance data access and improve overall application performance- especially for applications that access tables and indexes with millions of rows and many gigabytes of data.
In Warehouse Builder, you can define partitions for tables, indexes, materialized views, and MOLAP cubes. For the sake of brevity, in the following sections, the word table refers to all objects for which you can define partitions. The following sections discuss partitioning for all the objects previously listed with the exception of partitions MOLAP cubes which is described separately.
You define partitions for these objects by using the Partitions tab in the Data Object Editor. To launch the Data Object Editor, navigate to the object on the Project Explorer and double-click it or right-click and select Edit. Depending on the type of partition you create, you may also need to configure tablespaces for the partitions in the configuration window.
You can create the following types of partitions:
Range Partitioning: Use range partitioning to create partitions based on a range of values in a column. When you use range partitioning with a date column as the partition key, you can design mappings that instantly update target tables, as described in "Improved Performance Through Partition Exchange Loading".
Hash Partitioning: Use hash partitioning to direct the Oracle Database to evenly divide the data across a recommended even number of partitions. This type of partitioning is useful when data is not historical and there is no obvious column or column list.
Hash By Quantity Partitioning: As a means of quickly defining hash partitioning, use Hash by Quantity partitioning. This the same as hash partitioning except that you specify only a partition key and the number of partitions and Warehouse Builder creates and names the partitions for you. You can then configure the partitions to share the same tablespace list.
List Partitioning: Use list partitioning to explicitly assign rows to partitions based on a partition key you select. This enables you to organize the data in a structure not available in the table.
Composite Partitioning: You can use Warehouse Builder to specify a composite of either range-hash, range-hash by quantity, or range-list partitioning. The Oracle Database first performs the range partitioning and then further divides the data using the second partitioning you select. For example, in range-list partitioning, you can base partitions on the sales transaction date and then further divide the data based on lists of states where transactions occurred.
Index Partitioning: You can define an index that inherits the partitioning method of its underlying table. Or, you can partition an index with its own partitioning strategy.
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.
To implement a dimensional object is to create the physical structure of the dimensional object. Warehouse Builder provides the following implementations for dimensional objects:
You set the Deployment Option configuration property to specify the type of implementation for a dimensional object. For more setting this property, see "Configuring Dimensions" and "Configuring Cubes".
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.
Binding is the process of connecting the attributes and relationships 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.
Warehouse Builder provides two methods of binding:
Auto binding
Manual binding
Auto Binding In auto binding, Warehouse Builder binds the attributes and relationships of the dimensional object to the database columns that store their data. You can perform auto binding using both the wizards and the editors.
When you use the wizard to create dimensional objects, Warehouse Builder creates the implementation tables and then performs auto binding. 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.
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.
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.
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. For more information about how you specify the relationship between the dimensional object and its implementation objects, see "Binding".
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.
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
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, refer to Chapter 6, Understanding Data Storage, of the Oracle OLAP Application Developer's Guide 10g Release 2(10.2).
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 BI Beans and 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 4-8 describes how the OLAP catalog enables applications to access data stored in relational tables and analytic workspaces.
Figure 4-8 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. 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 BI Beans or Discoverer for OLAP to access the dimensional object data after you deploy data only.
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". For information on loading cubes, see "Cube Operator".
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 play only one of the following role at a time: effective date, expiration date, triggering attribute, surrogate identifier, business identifier, or parent identifier.
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.
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 just 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 fulfilment time. The sales cube can refer to order time, ship time and payment time dimension.
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 4-3 lists the levels in the Products dimension and the surrogate identifier and business identifier for each of the levels in the dimension.
Table 4-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
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 relationship 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 4 levels between employee A and the CEO, whereas, there may be 6 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".
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".
Note:
For information about certain limitations of deploying dimensions to the OLAP catalog, see the Oracle Warehouse Builder Release Notes.When you store dimension data in a relational form, you can implement the dimension using one of the following two 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 4-9 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 4-9 Star Schema Implementation of Products Dimension
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 4-10 displays the snowflake implementation of the Products dimension. Each level in the dimension is mapped to a different table.
Figure 4-10 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.
To perform auto binding:
In the Project Explorer, right-click the dimension and select Open Editor.
The Data Object Editor for this dimension is displayed.
On the Dimensional tab, right-click the Dimension node and select Bind.
Alternatively, select the dimension node on the canvas and from the Object menu choose Bind.
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.
To perform manual binding for a dimension:
Create the implementation objects (tables or views) that you will use to store the dimension data.
In the case of relational or ROLAP dimensions, create the sequence used to load the surrogate identifier of the dimension. You can also choose to use an existing sequence.
In the Project Explorer, right-click the dimension and select Open Editor.
The Data Object Editor for the dimension opens. On the canvas, the Dimensional tab is active.
Right-click the dimension and select Detail View.
Warehouse Builder opens a new tab that has the same name as the dimension.
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. 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 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.
If more than one data object is used to store the dimension data, perform steps 4 to 6 for each data object.
Map the attributes in each level of the dimension to the columns that store their data. To do this, hold down your mouse on the dimension attribute, drag, and then drop on the column that stores the attribute value.
Also map the level relationships to the database column that store their data.
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.
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 3 SCDs require the Warehouse Builder Enterprise ETL Option.
Table 4-4 describes the three types of SCDs.
Table 4-4 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 attribute for which historical values need to 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 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 do not need to 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 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 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.
Example
To create the Products dimension described in "Dimension Example" as a Type 2 SCD:
Specify that the PACKAGE_TYPE and the PACKAGE_SIZE attributes of the Product level are the triggering attributes.
Use two additional attributes in the Product level, to store the effective date and the expiration date of the level records. When you use the Create Dimension wizard to create a Type 2 SCD, Warehouse Builder creates these additional attributes for the lowest level only. If you use the Data Object Editor to create a Type 2 SCD, you must explicitly create these attributes and apply them to the level.
When the value of any of the triggering attributes changes, Warehouse Builder performs the following:
Marks the original dimension record as expired. The expiration date of this record is set to the current time or any value that you specify. For more information on setting the expiration date, see "Dimension Operator".
Creates a new record using the changed attribute values. The expiration date of this new record can be set to NULL or a predefined value. For more information on setting the value for the expiration date attribute, see "Dimension Operator".
A Type 3 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 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.
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.
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 Customers 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 multidimensional 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". For more information on sparsity, see "Advanced Dialog".
You can define aggregations that should be performed on the cube for ROLAP cubes or cube measures for MOLAP cubes. 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 4-5 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. Storing the cube data in an analytic workspace in the database is called as MOLAP implementation.
The types if implementation you can use for a cube are as follows:
Relational implementation
ROLAP implementation
MOLAP implementation
To set the type of implementation for a cube, you use the Deployment Option configuration property. For more details on setting this option, see "Configuring Cubes".
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 4-11 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 4-11 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.
To perform manual binding for a cube:
Create the table or view that stores the cube data.
In the Project Explorer, right-click the cube and select Open Editor.
The Data Object Editor for the cube opens. On the canvas, the Dimensional tab is active.
Right-click the cube and select Detail View.
Warehouse Builder opens a new tab that has the same name as the cube.
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. For example, if the cube 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 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.
Map the measures and dimension references of the cube to the columns that store the cube data. To do this, hold down your mouse on the measure or dimension reference, drag, and then drop on the data object attribute that stores the measure or dimension reference.
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.
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 4-6 describes the dimension attributes of the Warehouse Builder time dimension.
Table 4-6 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 4-7 lists the attributes of each level in the Warehouse Builder time dimension. For a description of each attribute, refer to Appendix B.
Table 4-7 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 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".
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.Figure 4-12 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 4-12 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.
|
 Copyright © 2000, 2009, Oracle and/or its affiliates. All rights reserved. Legal Notices |
|
