A dimension in OWB is a metadata construction to be able to:
When you have finished to define the hierarchy a bind (to a table) must be perform to map the attributes of the levels to the columns of a table. This task can be:
Because the construction of the dimension is done in a semi-logical way, you can choose the destination (ROLAP of MOLAP) of the storage that feed the best to your business requirements.
A dimension consists of a set of hierarchies defined using levels.
Users often analyze data by drilling down on known dimension hierarchies. The query performance is improved because the query optimizer makes smart decisions about choosing what data to use.
To create a dimension, you must define the following screens:
To view the dimension's attributes, click the Attributes tab in the Dimension Details panel.
Observe that :
The dimension key column is the primary key for the dimension and implements the surrogate identifier of each level. Using a sequence, you can populate the dimension key with unique values
A surrogate identifier uniquely identifies each level record across all the levels of the dimension and is loaded from a sequence, as specified in the dimension key field.
A business identifier is an attribute that is always derived from the natural key of the data source.
A parent identifier is also available when you want to use value-based hierarchies.
To view the dimension levels and level attributes, click the Levels tab in the Dimension Details panel.
Note that :
Each level must have:
For instance in the table below, the TOTAL level has a Description attribute.
Level | Attribute Name | Identifier |
---|---|---|
Total | Id Name Description | Surrogate Business |
Category | Id Name Description | Surrogate Business |
Product | Id UPC Name Description Package Type Package Size | Surrogate Business |
The Hierarchies tab in the Dimension Details panel.
Observe that :
The Default check box allows you to select a hierarchy that will display when a user queries. So it is important to pick the most commonly used hierarchy for this.
Specify the slowly changing dimension strategy
Slowly Changing Dimensions determine how you store historical changes to your dimension values. You can choose between the three strategies, as shown in the screenshot.
Since OWB 10gR2 slowly changing dimension has been incorporated into the dimension operator.
SCD Type 2 and 3 are available with the Enterprise ETL option of OWB 11g. With Core ETL features, SCD Type 1, that is, Do not keep history option, is only available.
If you want to store the complete change history, you would use a type 2 implementation.
In the Type 2 slowly changing dimension dialog box (edit settings), in the Record History drop-down list, you need to specify the attributes as:
For example, to record the change history whenever the LIST_PRICE, NAME and PACK SIZE of a product changes, set them as Trigger History attributes, as shown in the screenshot. Also, VALID_FROM_DATE is set as Effective Date and VALID_TO_DATE is set as Expiration Date attributes.
You design dimensional objects using Warehouse Builder, and then deploy them either in a relational form or in a multidimensional form to the database.
On the Storage tabbed page, you decide whether the designed dimension is implemented as relational or multidimensional and based on the type, Warehouse Builder generates appropriate storage type code.
For a relational storage, you can select one of the following methods to implement the dimension:
When you perform auto binding, these storage settings are used to perform auto binding.
The dimension data is stored in an analytic workspace. Enter values for the following fields:
Dimensions with multiple hierarchies can sometimes use the same source column for aggregate levels (that is, any level above the base). In such cases, you select the Generate surrogate keys in AW option. During a load operation, the level name is added as a prefix to each value. It is recommended that you select this option unless you know that every dimension member is unique.
Dimension Roles Use the Dimension Roles section to define dimension roles. You define the following for each dimension role:
A dimension role is an alias for a dimension.
When you configure a dimension, you configure both the dimension and the underlying table.
To configure the physical properties for a dimension:
Options | Description |
---|---|
Deploy All | For a relational or ROLAP implementation, the dimensional object is deployed to the database and a CWM definition to the OLAP catalog. For a MOLAP implementation, the dimensional object is deployed to the analytic workspace. |
Deploy Data Objects Only | Deploys the dimensional object only to the database. You can select this option only for dimensional objects that use a relational implementation. |
Deploy to Catalog Only | Deploys the CWM definition to the OLAP catalog only. Use this option if you want applications such as Discoverer for OLAP to access the dimensional object data after you deploy data only. Use this option if you previously deployed with “Data Objects Only” and now want to deploy the CWM Catalog definitions without re-deploying the data objects again. |
Deploy Aggregation | Deploys the aggregations defined on the cube measures. |
This option is available only for cubes.
For a dimension that uses a relational or ROLAP implementation, you can also configure the implementation tables.
In a hierarchy <name>, level relationship binding between parent level "total" and child level "channel" does not exist.
When you using auto-binding, you may have this error when the binding is out of sync. Perform it again.
VLD-5005: No updatable inputs connected for dimension level DIMENSION.TOTAL
At least one updatable input must be connected for level DIMENSION.TOTAL, or the generated code will fail.
Parent reference key and level natural key inputs are not updatable attributes in the target.
This mapping error validation message say that for all level (including the TOTAL level), you must have one updatable column.
If you have added on the level (the TOTAL most of the time) only the attribute of the surrogate key (Parent reference key) and business key (level natural key) this is not enough because they are not updatable attributes.
To overcome this error, you must: