The 'cube' is just a metadata construct.
You use the Cube operator to source data from or load data into cubes.
When you load a cube, you map the data flow from the source to the attribute that represents the business identifier of the referencing level. Warehouse Builder performs a lookup and then stores the corresponding surrogate ID in the cube table. For example, when you map the attributes from the dimension operator to the cube operator, a Key Lookup operator is created in cases where it is needed to lookup the surrogate identifier of the dimension.
Cubes contain measures and link to one or more dimensions. The axes of a cube contain dimension values and the body of the cube contains measure values. Most measures are numeric and additive. For example, sales data can be organized into a cube whose edges contain values for Time, Product, and Customer dimensions and whose body contains values from the measures Value sales and Dollar sales.
In a relational implementation, a cube is linked to a dimension tables by foreign key constraints and consists of a set of measures.
To create a cube, you must define the following:
In the Project Explorer panel, expand MyProject > Databases > Oracle. Expand the Name of the database, and then expand Cubes. Right-click on your cube and select Open Editor. (Note: Alternatively, you can also double-click on it.)
If you take a look at the generated SQL, you will find for each dimension, this selection :
( "DIMENSION_X"."DIMENSION_KEY" = "DIMENSION_X"."DIMENSION_ID" ) AND
( "DIMENSION_X"."DIMENSION_ID" IS NOT NULL )
This selection ensure that you select only the detail level and not the hierarchie rows.
Really important if you want to deploy the aggregation
Additionnal from Metalink : 'Deploy to catalog only' for dimensional objects means the CWM2 ROLAP catalog will be used if it is a relational implementation. 'Deploy data objects only' means DDL objects will be used; tables (for cube and dimension) plus relational dimension. You do not need 'Deploy All' for building an EUL on top of the relational tables, however if you want to use the OLAP integration in D4O then the CWM2 metadata would be used at this point.
The cube operator has the following properties that you can use to load a cube :
General Properties
Cube AW Properties
Cube Policies
Cube Load Properties
To examine the dimensionality of the cube, click the Dimensions tab in the Cube Details panel of the Data Object Editor.
Observe that the SALES cube is linked to levels within the five dimensions.
For example, SALES cube refers to PRODUCT level of the PRODUCTS dimension. The Role column displays in a drop-down list, the dimension roles (if defined previously) that the selected dimension contains.
The table on the Dimensions tab contains the following columns:
To examine the measures of the cube, click the Measures tab in the Cube Details panel. Note that there are three measures and all of them are numeric.
These measures can be aggregated to determine the total sales cost, amount, or quantity across a period of time or for a particular customer or for a particular product.
Use the Aggregation tab to define the aggregations that must be performed for each dimension that the cube references. You select the aggregate function that is used to aggregate data. You can also precompute measures along each dimension that the cube references. By default, aggregation is performed for every alternate level starting from the lowest level. The default aggregate function is SUM.
You specify the following:
Summary Strategy for Cube Use this section to define levels along which data should be precomputed for each dimension. The Dimension column lists the dimensions that the cube references. To select the levels in a dimension for which data should be precomputed, click the Ellipsis button in the PreCompute column to the right of the dimension name. The PreCompute dialog box is displayed. Use this dialog box to select the levels in the dimension along which the measure data is precomputed. You can specify the levels to be precomputed for each dimension hierarchy. By default, alternate levels, starting from the lowest level, are precomputed.
Some of the aggregate data is generated during deployment and the rest is aggregated on the fly in response to a query, following the rules defined in the Aggregation tab.
For MOLAP implementations, the aggregate data is generated and stored in the analytic workspace along with the base-level data. You cannot define aggregations for pure relational cubes (cubes implemented in a relational schema in the database only and not in OLAP catalog). For more details on the strategies for summarizing data, see the chapter about summarizing data in the Oracle OLAP User's Guide.
For ROLAP cubes, aggregation is implemented by creating materialized views that store aggregated data. These materialized views improve query performance.
The materialized views created to implement ROLAP aggregation are not displayed under the Materialized Views node in the Project Explorer.
You can implement a cube :
In relational implementation, a relational table stores the cube data. When a cube is implemented in a multidimensional environment, the cube data is stored in an analytic workspace.
If you want to modify the physical implementation of the cube, click the Storage tab in the Cube Details panel. You can select between relational or multidimensional implementation.
For relational implementation of the cube, you can opt to create bitmap indexes. Having these indexes on the cube improves your performance at query time.
The loading process in a cube do a join with the dimensions.
"D_DIMENSION_X"."DIMENSION_CODE" = "INGRP1"."D_DIMENSION_CODE" )
Thus, you can have no problem even if a dimension code is missing in the dimension table. I had the problem with the time dimension and it's very paintfull to find.
Deployment of cube fails with the following errors:
ORA-06510: PL/SQL: unhandled user-defined exception
ORA-06512: at "OLAPSYS.CWM2_OLAP_CUBE", line 99
ORA-06512: at line 2
See Metalink Note.
To debug this kind of issue, save the generated OWB CWM2 script to a file, then before you run on the target schema;
C:\Documents and Settings\Nicolas>sqlplus targetuser@database
SQL*Plus: Release 10.2.0.4.0 - Production on Fri Jan 9 12:03:35 2009
Copyright (c) 1982, 2007, Oracle. All Rights Reserved.
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining Scoring Engine and Real Application Testing options
SQL> set serveroutput on size 999999
SQL> exec cwm2_olap_manager.set_echo_on
PL/SQL procedure successfully completed.
SQL> @location/your_file
AMD-00101 cannot create Cube "QS_DWH.C_SOR_CONSUMERS_SALES"; metadata entity
already exists
AMD-00101 cannot create Cube "QS_DWH.C_SOR_CONSUMERS_SALES"; metadata entity
already exists
AMD-00101 cannot create Cube "QS_DWH.C_SOR_CONSUMERS_SALES"; metadata entity
already exists
BEGIN
*
ERROR at line 1:
ORA-06510: PL/SQL: unhandled user-defined exception
ORA-06512: at "OLAPSYS.CWM2_OLAP_CUBE", line 99
ORA-06512: at line 2
Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining Scoring Engine and Real Application Testing options
Optionnaly, you can find the list of the AMD message on Metalink