| Oracle® OLAP Application Developer's Guide 10g Release 1 (10.1) Part Number B10333-02 |
|
|
View PDF |
| Oracle® OLAP Application Developer's Guide 10g Release 1 (10.1) Part Number B10333-02 |
|
|
View PDF |
This chapter describes methods of creating a logical multidimensional model. It includes the following sections:
Metadata is used throughout Oracle OLAP to define a logical multidimensional model:
To describe the source data as multidimensional objects for use by the analytic workspace build tools.
There are several methods of creating this type of metadata, as described in this chapter.
To identify the components of logical objects in an analytic workspace for use by the refresh, aggregation, and enablement tools.
Database standard form describes this metadata, which is generated by the workspace creation tools. Refer to Appendix A for a description of standard form.
To describe relational views of analytic workspaces as multidimensional objects for use by OLAP applications.
The application determines the type of metadata that is needed. The BI Beans require OLAP Catalog metadata, which is described in this chapter.
You only need to describe your source data; the OLAP tools can generate the equivalent metadata for the analytic workspace and the workspace views. The logical model is transformed along with the data. Figure 5-1 shows the metadata transformations performed by the OLAP tools. These metadata types are discussed in this chapter.
Defining the logical model is the first stage of metadata creation; the second stage is mapping the logical objects to physical data sources. Different types of metadata have different requirements for the storage format of the source data; you must choose the method that is appropriate for your data source. Moreover, there are multiple methods of creating metadata, including graphical user interfaces and PL/SQL APIs.
The CWM1 write APIs, which are used by the OLAP Management tool, create a database dimension object for each logical OLAP dimension. The database dimension object imposes the following restrictions on dimension tables and the related fact tables of a star or snowflake schema:
All hierarchies must be level-based; the schema cannot use parent-child dimension tables.
Multiple hierarchies defined for a dimension must have the same base level.
Level columns cannot contain NULLs.
Fact data must be unsolved, that is, it is stored only at the lowest level of the hierarchy, and all the data for a cube must be stored in a single fact table.
If your source data is a star or snowflake schema and conforms to these additional requirements, then you can use either Oracle Enterprise Manager or the CWM2 APIs, depending on your personal preference. The OLAP Management tool in Oracle Enterprise Manager provides a graphical user interface. The CWM2 APIs enable you to generate a SQL program that you can easily modify and port to other databases.
If your source data is a star or snowflake schema that does not conform with these requirements, then use the CWM2 APIs.
Figure 5-2 shows the tools for creating OLAP Catalog metadata.
Figure 5-2 Tools for Creating OLAP Catalog Metadata for Source Data
This chapter introduces the OLAP Management tool in Oracle Enterprise Manager and the CWM2 APIs.
|
See Also: Oracle OLAP Reference for complete syntax and descriptions of theCWM2 APIs |
If your source data is a star or snowflake schema, but the dimension tables include any of the following variations, then use the CWM2 APIs:
Level columns containing NULLs, such as skip-level hierarchies
Multiple hierarchies with different base levels (sometimes called ragged hierarchies)
Multiple hierarchies with values mapped to different levels
Embedded total dimensions
Parent-child dimensions
If your schema contains parent-child dimension tables, then you must convert them to level-based dimension tables. The CWM2 write APIs include a package for this transformation.
If you are using Oracle Warehouse Builder already to transform your data, then generating an analytic workspace takes only a few additional steps. Warehouse Builder provides a graphical interface for designing a logical model, and deploys the model as metadata. When you use the OLAP Bridge in Warehouse Builder, it generates CWM1 metadata from its Design Repository. Warehouse Builder also creates and populates an analytic workspace, and enables it for use by the BI Beans.
If your data is stored in flat files or SQL tables, then you can use a manual method described in this guide. This method enables you to use the OLAP Catalog, but requires you to write data loading programs in the OLAP DML.
If you are upgrading from Oracle Express, then you may be able to automate the conversion process.
|
See Also:
|
The tools for creating analytic workspaces comply with the requirements of database standard form, and transform the source metadata into standard form metadata. You do not need to perform any extra steps to maintain the standard form metadata when you use the OLAP tools to maintain the analytic workspace. You can make changes to the logical model in the metadata for the data source, and the refresh tool makes the appropriate changes to the standard form metadata.
However, if you make manual changes to your analytic workspace, such as adding a measure, then you are responsible for making the appropriate changes to the standard form metadata. Standard form is described in Appendix A.
Applications that use the BI Beans require OLAP Catalog metadata, and those that use Discoverer require an End User Layer. Both types of metadata require the data source to be in relational tables or views for SQL access. Thus, the enablers in Analytic Workspace Manager for these types of applications generate views of analytic workspace objects in the format required by the metadata, and then generate the metadata itself. The enablers transform the standard form metadata provided in the analytic workspace; you do not need to redefine the logical model. Instructions for enabling an analytic workspace are provided in Chapter 6.
The OLAP Catalog defines logical multidimensional objects and maps them to physical data sources. The logical objects are cubes, measures, dimensions, and so forth as described in "The Logical Multidimensional Data Model". The physical data sources are the columns of a relational table or view. A number of different warehouse configurations can be represented by OLAP Catalog metadata.
The OLAP Catalog serves these distinct functions for analytic workspaces:
Describes the relational tables of a star or snowflake schema so that the data can be fetched into an analytic workspace. This metadata is used only when building or refreshing the analytic workspace.
Describes the relational views of an analytic workspace so that the data can be queried by the BI Beans. This metadata is used only at runtime so that applications have access to the workspace data.
Thus, when you are developing an analytic workspace, you may create two sets of OLAP Catalog metadata: one for the source schema, and the other for the analytic workspace. If your analytic workspace is used by another application, such as Oracle Discoverer, then you only define OLAP Catalog metadata for your source schema. For your analytic workspace, you create an End User Layer (EUL), which is the type of metadata required by Discoverer.
The OLAP Catalog is also used to describe the relational tables of a star schema so that the data can be queried by the BI Beans. In this type of scenario, no analytic workspace is used; aggregate data is stored in materialized views, as described in Chapter 13.
The BI Beans query metadata stored in the OLAP Catalog. Your data, whether it is stored in relational tables or in an analytic workspace, is inaccessible to applications based in these technologies unless the data is identified in the OLAP Catalog. The OLAP Catalog is also available to any other applications that want to use it.
The OLAP Catalog includes the following:
Metadata model tables: A set of relational tables within the database that instantiate the OLAP metadata model. These tables define all the OLAP metadata objects: dimensions, measures, cubes, measure folders, and so on. Within the metadata definitions are references to the actual data sources.
Write API: A set of PL/SQL packages for creating and editing OLAP metadata. These packages contain procedures for inserting, updating, and deleting rows in the model tables.
Read API: A set of relational views within the database that provide information about the metadata registered in the model tables.
Two versions of the OLAP Catalog are currently in use, CWM1 (also called CWM-Lite) and CWM2. Each version has its own metadata model tables, write API, and read API. However, applications can query a set of union views that contains all of the OLAP Catalog metadata, regardless of the write API used to generate it.
CWM1 is available through the OLAP Management tool of Oracle Enterprise Manager. You can use CWM1 only to describe a schema that complies with the requirements listed in "Choosing a Tool for Creating OLAP Catalog Metadata". You can then use the OLAP Catalog to create an analytic workspace or to access the relational schema directly through the BI Beans.
You can view CWM1 metadata in the OLAP Management tool of Enterprise Manager, or in the OLAP Catalog View of Analytic Workspace Manager.
CWM2 is available through the BI Beans enabler in Analytic Workspace Manager and as a set of PL/SQL packages. You can use CWM2 to describe a star or snowflake schema that does not comply with the requirements for CWM1. You can use only CWM2 to define the metadata for an analytic workspace; you cannot use CWM1 for this purpose.
You can view CWM2 metadata in the OLAP Catalog View of Analytic Workspace Manager.
Whether you create OLAP metadata programmatically or by using a graphic interface, you follow the same basic steps.
To create OLAP metadata:
Create logical dimensions. Specify the levels, attributes, and hierarchies associated with each one. ("Procedure: Defining a Logical Dimension in the OLAP Catalog")
Create logical cubes and specify their edges (dimensions). ("Procedure: Defining a Logical Cube in the OLAP Catalog")
Create logical measures that represent the fact data. Associate each measure with a cube. ("Procedure: Defining a Logical Cube in the OLAP Catalog")
Map the logical entities to the source data. ("Procedure: Defining a Logical Cube in the OLAP Catalog")
The tools for creating OLAP Catalog metadata depend on whether you are creating the metadata for a relational schema or for an analytic workspace. Some tools have specific prerequisites.
When you create OLAP Catalog metadata for the data stored in an analytic workspace, you define it against relational views of the multidimensional objects in the workspace. These views emulate a star schema, but are different in the way they expose dimensions. Instead of dedicating a separate column to each level of a dimension hierarchy, these views list all dimension members at all levels in a single column. For this reason, views of this type are called embedded total views. The views display the dimensions in the same format in which they are stored in the analytic workspace. You can create CWM2 metadata for embedded total views; you cannot create CWM1 metadata for them.
You can choose among three tools for creating OLAP Catalog metadata for an analytic workspace:
Analytic Workspace Manager
Oracle Warehouse Builder
CWM2 APIs
Figure 5-3 shows the relationships among these tools and an analytic workspace. When an analytic workspace is enabled for use by the BI Beans, relational views are created that can access workspace objects in response to a query. The CWM2 write APIs store metadata about the logical model represented by the views in the CWM2 read APIs. This metadata is automatically available through the Union views. You must run a PL/SQL procedure to make the metadata available to the Metadata Refresh Views, which are the views that provide the best performance when queried by the OLAP API.
Figure 5-3 Tools for Creating Metadata for Analytic Workspaces
If you have a standard form analytic workspace, then use the BI Beans enabler in Analytic Workspace Manager to generate the relational views and the CWM2 metadata in a single step.
If you use Oracle Warehouse Builder to generate your analytic workspace, then it also creates the views and the CWM2 metadata for access by the BI Beans.
If your analytic workspace includes objects that do not comply with database standard form, or you wish to generate the relational views manually, then write your own CWM2 script. You may wish to start by modifying a script generated by Analytic Workspace Manager.
Chapter 6, explains how to use the BI Beans enabler in Analytic Workspace Manager.
|
See Also: Oracle OLAP Reference for complete syntax and descriptions of theCWM2 APIs |
If your data warehouse complies with the requirements listed in "For Source Data in a Basic Star or Snowflake Schema", you can create OLAP metadata using the OLAP Management tool in Oracle Enterprise Manager.
You generate the SQL statements that create the metadata primarily by following the steps presented by a wizard or by completing a property sheet. If you wish, you can display the SQL statements before executing them.
Follow these steps to start Oracle Enterprise Manager and access OLAP Management:
Open Oracle Enterprise Manager 10g Grid Control in your browser.
The login page is displayed.
Enter a user name and password for Enterprise Manager.
The Grid Control home page is displayed.
Click the Targets tab.
The Hosts page is displayed.
Click the Database tab.
The Databases page is displayed.
Click the link for the database you want to manage.
The Oracle Database home page is displayed.
Click the Administration link.
The Database Administration page is displayed.
Look for the Warehouse heading. Links in the left column are used for Oracle OLAP. (The other Warehouse links are used only for relational warehouses that do not use the OLAP option. Do not use those links.)
You see the types of objects that you can create: Cubes, OLAP dimensions, and measure folders. These links are for OLAP Management.
When creating OLAP metadata, you must first define the metadata objects for the dimension tables. These metadata objects are logical dimensions based on database dimension objects. You can use the Dimension Creation wizard or supply information directly in the Create Dimension dialog box.
To define a dimension, you provide all the information that will be needed to label and aggregate the measures dimensioned by it, including:
The name of the dimension
The tables that contain the data for the dimension
The name of each level, and the columns that contain the data for each level
The number and order of levels in each hierarchy
Join keys for levels that are stored in separate tables
The columns that contain attributes for the levels
A display name and description for the dimension and each of its hierarchies, levels, and attributes
Business analysis is performed on historical data, so fully defined time periods are vital. Your time dimension table must have columns for period end dates and time span. This information supports time-series analysis, such as comparisons with earlier time periods. If your schema does not have these columns, then you can define time as a normal dimension, but it will not support time-based analysis.
Typical levels and hierarchies for Time dimensions are suggested by the Dimension wizard, but you do not have to use them.
Follow these steps to create a dimension and its associated levels, hierarchies, and attributes:
Start Oracle Enterprise Manager and access OLAP Management, as described in "Procedure: Accessing OLAP Management".
Click the OLAP Dimensions link under Warehouse.
If you have not already logged into the database, the Database Login page is displayed. Enter your login name and password for the database.
The Search Objects page is displayed.
Click Create.
The Create Dimension page is displayed.
Choose Help if you need further information.
After you have defined the metadata objects for the dimension tables, you can create metadata objects for the fact tables. These metadata objects are measures and cubes. A cube is a collection of identically dimensioned measures. Cubes and measures are defined entirely in the OLAP metadata; there are no corresponding database objects.
|
Tip: If you plan to calculate and store custom measures such as forecasts as a permanent part of your analytic workspace, you can add empty columns to the fact tables and define logical measures from those columns. Then the analytic workspace creation process will create and register all of the objects associated with the custom measures, so you only need to populate them. Refer to Chapter 11 for more information about this method of creating custom measures. |
When you define a cube, you identify information such as the following:
The name of the cube and the fact table associated with it. All measures in a cube must be from a single fact table.
The names of the dimensions and the levels in the dimension hierarchies that will be used in the cube.
The names of the measures and the columns in the fact table where the values for each measure is stored.
Default aggregation operators for each dimension of each measure (such as sum or average).
Any calculation dependencies.
Follow these steps to create a cube:
If you have not done so already, start Oracle Enterprise Manager and access OLAP Management, as described in "Procedure: Accessing OLAP Management".
Click the Cubes link under Warehouse.
If you have not already logged into the database, the Database Login page is displayed. Enter your login name and password for the database.
The Search Objects page is displayed.
Click Create.
The Create Cube page is displayed.
Choose Help if you need further information.
|
Note: If you are creating OLAP Catalog metadata for use by the BI Beans running directly against a relational schema (that is, with no analytic workspace, then your last step is to open SQL*Plus Worksheet and issue this command:EXECUTE CWM2_OLAP_METADATA_REFRESH.MR_REFRESH For relational source data, be sure to create materialized views as described in Chapter 13. |
The Global star schema conforms to all of the requirements of CWM1, so you can use the OLAP Management tool in Oracle Enterprise Manager.
If you have installed the Global schema, the OLAP Catalog metadata may be already defined. However, you can follow this example by creating the metadata in a different schema. All of the mappings between logical objects and source columns are described in Chapter 3. The following procedures explain how to define just one dimension and one cube.
The TIMES_DIM table supports a single Calendar hierarchy with three levels (Month, Quarter, and Year) as described in "Dimension Table: TIME_DIM". These are the steps to define a logical Time dimension using the Create Dimension wizard.
On the Add Dimension page, do the following:
For Name, type TIME.
For Schema, choose GLOBAL.
For Type, select Time.
On the Add Level page, do the following
For Name, type YEAR.
For Type, choose Year.
For Table, choose TIME_DIM.
Click Populate Columns.
Move YEAR_ID from Available Columns to Selected Columns.
Click OK.
Repeat these steps for the Quarter and Month levels. Map Quarter to QUARTER_ID and MONTH to MONTH_ID.
On the Add Hierarchy page, do the following:
For Name, type Calendar.
Choose Move All.
Use the arrow keys to order the levels like this:
Year Quarter Month
On the Create Dimension page, choose Attributes. Edit the Long_Description and Short_Description attributes and create the Time_Span and End_Date attributes. Map the attributes to the columns shown in "Global Time Dimension Mapping".
On the Create Dimension page, choose OLAP Options. Type whatever descriptions you want to add.
When you have successfully created a dimension, it appears on the Dimensions page.
The UNITS_HISTORY_FACT table has a multi-column primary key, composed of four surrogate keys from the four dimension tables, and one measure (UNITS). These are the steps to define a logical Units cube. If you have installed the Global schema, this cube may be defined already. However, you can follow these steps by creating the cube under a different name or in a different schema.
On the Create Cube page, do the following:
For Name, type UNITS_CUBE.
For Display Name, type Units Cube.
For Schema, choose GLOBAL.
For Description, type your own description.
For Fact Type, choose Table.
For Fact Schema, choose GLOBAL.
For Fact Table, choose UNITS_HISTORY_FACT.
Use the Add Dimension page to add each dimension (CHANNEL, CUSTOMER, PRODUCT, and TIME). Use the default properties and identify the appropriate foreign key columns in the fact tables.
The CWM2 PL/SQL packages contain stored procedures that can create OLAP metadata for a variety of schema designs, as described in "Choosing a Tool for Creating OLAP Catalog Metadata".
Before using these packages, make sure that you have performed any required preprocessing steps.
|
See Also:
|
The following packages contain procedures that create metadata for dimension tables:
CWM2_OLAP_DIMENSION contains procedures for creating dimensions.
CWM2_OLAP_HIERARCHY contains procedures for creating hierarchies for dimensions.
CWM2_OLAP_LEVEL contains procedures for creating levels for dimensions and for associating levels with hierarchies.
CWM2_OLAP_LEVEL_ATTRIBUTE contains procedures for creating level attributes and associating them with levels.
CWM2_OLAP_DIMENSION_ATTRIBUTE contains procedures for creating dimension attributes and associating them with dimensions.
The following packages contain procedures that create metadata for fact tables:
The CWM2_OLAP_TABLE_MAP package contains procedures that map logical metadata entities to their physical data source. The data may be stored in relational tables, or it may be represented by relational views. When the dimension tables and fact tables are defined as views, the actual data may reside in analytic workspaces.
The CWM2_OLAP_PC_TRANSFORM package contains a procedure for transforming parent-child dimension tables to level-based dimension tables. This conversion is necessary if the dimension will be accessed by the BI Beans.
|
 Copyright © 2002, 2003 Oracle Corporation All Rights Reserved. |
|
