About
A dimension in OWB is a metadata construction to be able to:
- automatically build a table with its hierarchy.
- manage the SCD behaviour
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:
- automatic: a table is automatically created
- or manual: you have to map the attributes of the dimension to the understand table.
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.
Articles Related
Create a dimension
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:
- Dimension Attributes
- Level and Level Attributes
- Hierarchies
Dimension's attributes
To view the dimension's attributes, click the Attributes tab in the Dimension Details panel.
Observe that :
- the dimension is assigned a sequence, PROD_DIM_SEQ, to populate a dimension key when loading data.
- one of the attribute IDs has been identified as a Surrogate identifier
- SOURCE_ID attribute is assigned as the Business identifier.
- In the Descriptor column, the dimension attributes marked as Long Description and Short Description can be used to display the dimension members in business intelligence tools.
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
Identifier
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.
Levels tab
To view the dimension levels and level attributes, click the Levels tab in the Dimension Details panel.
Note that :
- there are four levels and each level has an applicable set of attributes, for example PRODUCT level implements all the dimension attributes as level attributes.
- to include for instance the two date attributes as applicable attributes for the PRODUCT level, we must select the Applicable check boxes for VALID_FROM_DATE and VALID_TO_DATE attributes, as shown in the screen-shot.
- In a relational implementation, a level attribute becomes a column in a table. Level attributes do not have to have the same name as the dimension attribute, but Warehouse Builder defaults it as such in the panel.
- When defining the levels you need not worry about the order in which you enter the levels. The levels are only organized in the hierarchies.
- A level is not required to implement all dimension attributes. Implementing a dimension attribute on a level enables you to store this information on a level.
Each level must have:
- a Surrogate attribute,
- a Business attribute
- and an other updatable attribute.
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 |
In the level attribute name of the level attributes of the level tab, you can choose the name of the column. Be conscious that for the surrogate and business attributes, the generator will add the name of the level before. You don't need to do it otherwise you will have it in double.
Dimension Hierarchy
The Hierarchies tab in the Dimension Details panel.
Observe that :
- there is only one hierarchy, PROD_STD, with levels arranged in a particular order (top down in the panel list).
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.
SCD tab (slowly changing dimension)
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.
Type2
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:
- Trigger History: Select this option for an attribute if a change in the attribute's data element requires the record to create a new version of itself.
- Effective Date: Set this option for the single attribute that stores the value of the date/time the record becomes active.
- Expiration Date: Set this option for the single attribute that stores the date/time the record is no longer valid or effective.
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.
Storage tab
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.
Rolap
For a relational storage, you can select one of the following methods to implement the dimension:
- Star schema: Implements the dimension using a star schema. This means that the dimension data is stored in a single database table or view.
- Snowflake schema: Implements the dimension using a snowflake schema. This dimension data is stored in more than one database table or view.
- Manual: You must explicitly bind the attributes from the dimension to the database object that stores their data.
When you perform auto binding, these storage settings are used to perform auto binding.
Molap
The dimension data is stored in an analytic workspace. Enter values for the following fields:
- AW Name: Enter the name of the analytic workspace that stores the dimension data. Alternately, you can click the Ellipsis button to display a list of MOLAP objects in the current project. Warehouse Builder displays a node for each module in the project. Expand a module to view the list of dimensional objects in the module. Selecting an object from list stores the dimension in the same analytic workspace as the selected object.
- AW Tablespace Name: Enter the name of the tablespace in which the analytic workspace is stored.
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.
Name Tab
Dimension Roles
Dimension Roles Use the Dimension Roles section to define dimension roles. You define the following for each dimension role:
- Name: Represents the name of the dimension role.
- Description: Specify an optional description for the dimension role
A dimension role is an alias for a dimension.
Configure a dimensions
When you configure a dimension, you configure both the dimension and the underlying table.
To configure the physical properties for a dimension:
- From the Project Explorer, right-click the dimension name and select Configure. The Configuration Properties window is displayed.
- Configure the following dimension properties:
- Deployable: Select TRUE to indicate if you want to deploy this dimension. Warehouse Builder generates scripts only for table constraints marked deployable.
- Deployment Options: Select one of the following options:
| 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.
- View Name: Specify the name of the view that is created to hide the control rows in the implementation table that stores the dimension data. This is applicable for relational or ROLAP dimensions that use a star schema. The default view name, if you do not explicitly specify one, is the dimension name suffixed with “_v”.
- Visible: This property is not used in code generation.
For a dimension that uses a relational or ROLAP implementation, you can also configure the implementation tables.
Support
VLD-0350
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
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:
- add a description attribute in your dimension level
- and update it in your mapping with a constant value such as 'TOTAL'.