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.
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 :
- 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.
|Total|| Id |
|Category|| Id |
|Product|| Id |
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.
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.
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.
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.
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:
|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.
In a hierarchy <name>, level relationship binding between parent level "total" and child level "channel" does not exist.
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'.