About Retrieving Information Stored in Flexfield Columns
Flexfields are customer-configured fields that are used to store additional information that is not stored in the standard OLTP tables. This information may be mapped to fixed or placeholder attributes in some warehouse dimensions.
Fixed attributes in Oracle Business Analytics Warehouse, such as Job Code, Job Family, Job Function, Job Level, Pay Level, may be used in pre-defined reports. Placeholder attributes are available for ad-hoc reporting, and once mapped can be re-labelled to describe the data that they contains. The mapping and re-labelling can be done by configuring domains and domain mappings using Configuration Manager.
Optional or Mandatory
This configuration is optional. If this configuration is not done, then the fixed columns dependent on flexfield data will not have any data in them. In addition, no placeholder columns will be available, as they are hidden in Oracle BI Presentation Catalog based on whether or not the configuration is done.
Applies to
E-Business Suite source systems.
Background
Configuration Manager is used to configure flexfields. The following table shows which flexfields are supported as a source and which target dimensions they map to.
Dimension | Target Domain (Dimension Attributes) | Source Domain (Flexfields) |
---|---|---|
HR Assignment |
HR Assignment Dimension Attributes (W_FLEX_ASSIGNMENT_ATTRIBUTES) |
People Group Key Flexfield (KEY:801:GRP) |
HR Assignment |
HR Assignment Dimension Attributes (W_FLEX_ASSIGNMENT_ATTRIBUTES) |
Assignment Descriptive Flexfield (DESCRIPTIVE:800:PER_ASSIGNMENTS) |
HR Person |
HR Person Dimension Attributes (W_FLEX_PERSON_ATTRIBUTES) |
Person Descriptive Flexfield (DESCRIPTIVE:800:PER_PEOPLE) |
HR Person Legislation Note: HR Person Legislation placeholders will be partly filled by the mapping for HR Person, from the Person Descriptive Flexfield. |
HR Person Legislation Dimension Citizenship Attributes (W_FLEX_CITIZENSHIP_ATTRIBUTES) |
Person Extra Information Descriptive Flexfield (DESCRIPTIVE:800:PER_PEOPLE_EXTRA_INFO) |
HR Person Legislation* |
HR Person Legislation Dimension Ethnic Attributes (W_FLEX_ETHNIC_ATTRIBUTES) |
Person Extra Information Descriptive Flexfield (DESCRIPTIVE:800:PER_PEOPLE_EXTRA_INFO) |
HR Person Legislation* |
HR Person Legislation Dimension Visa Attributes (W_FLEX_VISA_ATTRIBUTES) |
Person Extra Information Descriptive Flexfield (DESCRIPTIVE:800:PER_PEOPLE_EXTRA_INFO) |
HR Position |
HR Position Dimension Attributes (W_FLEX_POSITION_ATTRIBUTES) |
Position Key Flexfield (KEY:800:POS) |
HR Position |
HR Position Dimension Attributes (W_FLEX_POSITION_ATTRIBUTES) |
Position Descriptive Flexfield (DESCRIPTIVE:800:PER_POSITIONS) |
Job |
Job Dimension Attributes (W_FLEX_JOB_ATTRIBUTES) |
Job Key Flexfield (KEY:800:JOB) |
Job |
Job Dimension Attributes (W_FLEX_JOB_ATTRIBUTES) |
Job Descriptive Flexfield (DESCRIPTIVE:800:PER_JOBS) |
Pay Grade |
Pay Grade Dimension Attributes (W_FLEX_GRADE_ATTRIBUTES) |
Grade Key Flexfield (KEY:800:GRADE) |
Pay Grade |
Pay Grade Dimension Attributes (W_FLEX_GRADE_ATTRIBUTES) |
Grade Descriptive Flexfield (DESCRIPTIVE:800:PER_GRADES) |
All target domains support the following attributes. Not all the placeholder attributes are pre-seeded, however the domain can be extended up to the supported limit and the ETL will automatically pick up any that are added:
Dimension | Domain Members |
---|---|
HR Assignment |
Placeholders (30 Character, 20 Number, 10 Date) |
HR Person |
Placeholders (30 Character, 20 Number, 10 Date) |
HR Person Legislation |
Placeholders (from HR Person Attributes – first 15 Character, 9 Number, 6 Date) Placeholders (from Citizenship Attributes – next 5 Character, 3 Number, 2 Date) Placeholders (from Ethnic Attributes – next 5 Character, 3 Number, 2 Date) Placeholders (from Visa Attributes – next 5 Character, 3 Number, 2 Date) |
HR Position |
Placeholders (30 Character, 20 Number, 10 Date) Position Number |
Job |
Placeholders (30 Character, 20 Number, 10 Date) Job Code Job Family Job Function Job Level |
Pay Grade |
Placeholders (30 Character, 20 Number, 10 Date) Pay Level |
Task Flow
-
Identify the flexfield structures and columns that store the information for the fixed data warehouse columns such as Job family, Job Function.
Additionally identify the flexfield structures and columns that store information that needs to be included in the warehouse for ad-hoc reporting.
-
Decide which placeholder target domain members will hold which source flexfield columns.
Override the default target domain member name by specifying a custom name for the target column. Where a value set is used to translate the source flexfield column provide a target domain member description which will become the label for the translated target column.
-
In the domain mapping screen, map the source domain members (flexfield structure and column) to the target domain member (warehouse dimension column).
Example
For example, the following domain mapping may be done for the Job Dimension, assuming there are two structures defined in the key flexfield that contain the job family for different legislations, and there are three attributes of the global descriptive flexfield that are required for ad-hoc reporting:
Source Domain | Source Member | Target Domain | Target Member |
---|---|---|---|
Job Key Flexfield |
Job Family (1:SEGMENT3) |
Job Dimension Attributes (W_FLEX_JOB_ATTRIBUTES) |
Job Family (JOB_FAMILY) |
Job Key Flexfield |
Job Family (2:SEGMENT5) |
Job Dimension Attributes (W_FLEX_JOB_ATTRIBUTES) |
Job Family (JOB_FAMILY) |
Job Descriptive Flexfield |
Job Type 1 (Global:ATTRIBUTE8) |
Job Dimension Attributes (W_FLEX_JOB_ATTRIBUTES) |
Job Type 1 (JOB_ATTR1_CHAR) |
Job Descriptive Flexfield |
Job Type 2 (Global:ATTRIBUTE13) |
Job Dimension Attributes (W_FLEX_JOB_ATTRIBUTES) |
Job Type 2 (JOB_ATTR2_CHAR) |
Job Descriptive Flexfield |
Job Type 3 (Global:ATTRIBUTE19) |
Job Dimension Attributes (W_FLEX_JOB_ATTRIBUTES) |
Job Type 3 (JOB_ATTR3_CHAR) |
The ETL will then populate the corresponding dimension columns with the data stored in the mapped flexfield columns from the source.
If the flexfield is defined with a value set, then the ETL will also extract the values from this value set into a domain, which can be used to translate/lookup the code. These lookup domains are named after the target domain member, for example the domain JOB_ATTR1_CHAR:W_FLEX_JOB_ATTRIBUTES would contain the code-name pairs required to translate the values in the Job Dimension Job Type 1 field. The translated values are also presented in Oracle BI Answers using the description for the target member. For example, the target column JOB_ATTR1_CHAR is given the name 'Job Type 1' and description 'Job Type 1 Name'. Then, in Oracle BI Answers the column 'Job Type 1' will contain the codes from the value set and the column 'Job Type 1 Name' will contain the translated names from the value set.
Dependency
None.