Use Dimension Editor to create a dimension from the start, or to edit a previously created dimension.
Oracle Fusion Middleware Developing Applications with Oracle JDeveloper
Set values for the following parameters that define the Dimension:
| Parameter | Description |
|---|---|
| Name | Enter the name of Dimension. |
| Description | Briefly describe the Dimension. |
| Pattern Name | By default, the Dimension pattern is automatically set.
If you want a specific pattern to be applied to the Dimension, select a pattern name. |
| Binding Datastore | Select the datastore that the Dimension will be bound to. |
| Binding Surrogate Key Sequence | If you want to use a surrogate key for the Dimension, select an ODI sequence. |
Oracle Fusion Middleware Developing Applications with Oracle JDeveloper
Level represents a collection of dimension values that share similar characteristics. The Levels tab displays a table at the top, listing levels of the Dimension.
Levels can be created in any order. Levels can be re-ordered either top-to-bottom or vise versa for better viewing/reporting. It is recommended to create levels in a top-to-bottom manner, that is level without any parent level in Dimension first.
Levels can be created or removed, using the "+" or "x" buttons.
Set values for the following parameters in the table:
| Parameter | Description |
|---|---|
| Name | Enter the name of the level. |
| Description | Enter description of the level. |
| Datastore | The datastore used to store the data of this level.
This datastore will be the Dimension Binding Table, and can not be edited. |
| Staging Datastore | The datastore used to stage the incoming data for this level, before loading into the actual binding table.
Select a value from a drop-down box. All the datastores in all the models in ODI repository listed in the drop-down box are valid values. This excludes those datastores that are already selected as the binding or error table of the same level. |
The Level Attributes table displays the attributes of the level selected in the Levels table. Level Attributes can be:
Created or removed using the "+ "or "x" buttons.
Reordered within the current level using the up or down buttons.
Set values for the following parameters in the table:
Note:
Within the same level, only one level attribute can act as the Surrogate Key, Type 2 Setting Start Date, Type 2 Setting End Date.Dimension Validation has multiple rules to make sure that Level Attributes properties are set correctly.| Parameter | Description |
|---|---|
| Name | Enter the name of the level attribute. |
| Surrogate Key | Select the check box to activate the Surrogate Key. |
| Datatype | Select a value from the drop-down box.
Any datatypes in the Generic SQL technology defined in ODI repository, listed in the drop-down box are valid. |
| Type 2 Setting | Select a value from the drop-down box:
|
| Type 3 Previous Attribute | Select a value from the drop-down box that lists the level attributes of the current level.
Setting this value implies that the current level attribute is SCD3-enabled. When the incoming data contains change to this attribute, its value will be first moved to the SCD3 previous attribute, before being overridden by the incoming data. |
| Type 3 Start Date | For Type 3 Start Date, create level attribute of a Date/Timestamp data type, and assign this level attribute to Type 3 Start Date. |
| Attribute | Select a value from the drop-down box that lists the datastore columns, which store the data for this level attribute.
The columns of the dimension table listed in the drop-down box are valid values. |
| Staging Attribute | Select a value from the drop-down box that lists the datastore columns, which store the data for this level attribute.
The columns of the staging datastore selected as the binding of the current level are valid values. |
The Natural Key Members table displays the key members for the Natural key of the level selected in the Levels table. The Natural key is also called ”Business Key” or ”Application Key.” This key is usually used to store the key value(s) that can uniquely identify a particular record in the source systems.
The Natural Key Members can be created or removed using the "+" or "x" buttons.
Set values for the following parameter in the table:
| Parameter | Description |
|---|---|
| Level Attribute | Constitutes the Natural Key of the selected level.
Select a value from the drop-down box that lists the level attributes of the current level. |
The Parent Level References table defines the list of references from the selected level in the Level table to some other parent level. This table is driven from the Level table. The Parent Level Reference can be created or removed using the "+" or "x" buttons.
Functionally, a Parent Level Reference defines how to retrieve the parent level record from the current record. One or more attributes in the current record will be used as a foreign key to match the corresponding application key attributes in the parent level.
Set values for the following parameters in the table:
| Parameter | Description |
|---|---|
| Name | Enter the name of the Parent Level Reference. |
| Description | Enter the details of the Parent Level Reference. |
| Parent Level | Select the Parent Level that this Reference is pointing to, from the drop-down box.
The valid values in the drop-down list are the levels in the Dimension that is higher than the currently selected level. |
| Type 2 SCD Setting | Select one of the following from the drop-down box:
If Trigger History is selected, a new row will be created during Dimension loading, if any one of the Parent Level Reference Key Member columns is not the same as the incoming data. |
This table is driven from the parent level reference that is selected in the Parent Level References table. Each row in this table represents how a key member of the natural key of the parent level matches with the columns in the current level record. The rows in this table are automatically populated with the key members of the parent level.
Set values for the following parameters in the table:
| Parameter | Description |
|---|---|
| Parent Key Attribute | The level attribute that is part of the natural key of the parent level.
The value of this column is automatically populated and cannot be changed. |
| Foreign Key Attribute | The datastore column of the current level that is used to match the corresponding natural key member of the parent level.
Select a value from a drop-down box. The valid values of the drop-down box are all the columns of the datastore of the currently selected level. |
| Foreign Key Staging Attribute | The datastore column of the current level that is used to match the corresponding natural key member of the parent level.
Select a value from a drop-down box. The valid values of the drop-down box are all the columns of the staging datastore of the currently selected level. |
Note:
For a star dimension, the key members of a parent level reference do not need to be specified, because a dimension record is stored in the same database row, which includes all information of the parent level.Hierarchy defines how dimension data is summarized and rolled up in BI reporting tools.
The Hierarchies table shows the list of hierarchies that are defined for this dimension.
Hierarchy can be created or removed using "+" or "x" buttons.
Set values for the following parameters in the table:
| Parameter | Description |
|---|---|
| Name | Enter the name of the Hierarchy. |
| Description | Enter a short note about the Hierarchy. |
| Default | If a Dimension has multiple hierarchy, query tools show the default hierarchy.
Only one hierarchy can be defined as default. |
The Hierarchy Members table shows the list of level members in the Hierarchy selected in the Hierarchies table.
Hierarchy Members can be:
Added or removed using the +" or "x" buttons.
Sorted using the up and down buttons, for better viewing/reporting.
Set values for the following parameters in the table:
| Parameter | Description |
|---|---|
| Level | Select a level in the Hierarchy from the drop-down box.
Valid values in the drop-down box are the list of levels in the Dimension that are not a member of the currently selected hierarchy, which are of lower level than any preceding hierarchy members. For example, if we have a dimension with 3 levels called BRAND, CATEGORY and PRODUCT, if the first hierarchy member is BRAND, the valid levels of the second hierarchy member is CATEGORY and PRODUCT. |
| Parent Level Reference | Use to navigate from the selected level to the level of the immediately preceding hierarchy members.
Select a value from the drop-down box. The parent level references need to created first in the Levels tab. Valid values are the parent level references between the current level and the level of the immediately preceding hierarchy member. |
The Skip Levels table shows the list of skip level members in the hierarchy member selected in the Hierarchy Members table. This allows level data to have multiple paths to roll up to different parent levels, where some paths may skip one or more parent levels in the same hierarchy.
For example, assume that we have a hierarchy STORE -> CITY -> REGION -> STATE -> COUNTRY in a STORE Dimension of a retail company. In this company, some stores are more important and they report directly to the regional office. As a result, a store can have two parent levels, CITY and REGION, where CITY is optional. To describe this hierarchy, we have a hierarchy member for STORE level and its parent level is CITY. This hierarchy member also has a skip level and its parent level reference is pointing to REGION level.
As there may be multiple paths that parent levels can be skipped, the + or – buttons can be used to describe these different roll-up paths. The Skip Levels table contains only one column:
Parent Level Reference: The parent level reference that is used to skip to some preceding level in the hierarchy, listed in a drop-down box. Its valid values include:
Parent level references defined for the level of the currently selected hierarchy member.
Those parent level references that do not point to the immediately preceding level.