Viewing and Editing Data in Dimension and Reference Tables
To view and edit data in workforce-related dimension and reference tables, use the GEOGRAPHY_D00.GBL, WA_JOBCDSET_D00.GBL, WA_UNITCODE_D00.GBL, WA_INDUSTRY_D00.GBL, WA_FINCODE_D00.GBL, and COMPCODE_D00.GBL components.
This section provides an overview of the review and edit of dimension and reference data and discusses how to:
View and edit departments.
Set up geography IDs.
View and edit job codes and job code sets.
View and edit personal information.
View and edit position data.
View and edit location data.
View and edit salary grades and steps.
Set up compensation codes.
Set up industry codes.
Set up financial codes.
Set up unit codes.
Define age, service, and other duration groups.
Pages Used to View and Edit Data in Dimension and Reference Tables
Page Name |
Definition Name |
Navigation |
Usage |
---|---|---|---|
Department |
DEPARTMENT_TBL |
|
View and edit departments, which can be used for financial related accounting purposes or to provide an organizational hierarchy for your company. |
Geography |
GEOGRAPHY_D00 |
|
Create geography identification codes, which are geographical regions, which your locations roll up to. |
Job Code |
JOBCODE_D00 |
|
View and edit data in JOBCODE_D00, and provide the details for the JOBCODE tree. |
Job Code Set |
WA_JOBCDSET_D00 |
|
Create the nodes that define the structure of your JOBCODE tree. You must enter this information for each node using this page. A job code set is a collection of related job codes. |
Job Earnings Distribution |
JOB_EARNDST_D00 |
|
View and edit how to post an employee's earnings for accounting purposes. For example, earnings can be posted to a job code, a department, a location, a general ledger account, a position, or distributed among several categories. |
Job Tasks |
JOB_TASK_D00 |
|
View and edit job codes for which sets of job tasks are available. |
Personal Data |
PERSONAL_D00 |
|
View and edit personal information about your employees in the PERSONAL_D00 table. Rows from the HRMS tables PS_PERSONAL_DATA, PS_EMPLOYMENT and PS_EMPLOYEES are intended to be brought in to the PERSONAL_D00 table with ETL. |
Dependent Data |
WA_DEP_BEN_D00 |
|
View and edit information about dependents and beneficiaries. |
Position |
POSITION_D00 |
|
View and edit position data if Position Management is implemented in the source HRMS system. |
Salary Grade Data |
WA_SAL_GRADE_D00 |
|
View and edit a salary grade structure for your salary administration plan. |
Salary Increase Matrix |
SAL_MTRXTBL_D00 |
|
View and edit the identifier for each set of salary increase guidelines, the name of the matrix, and what rating scale is used for the ranges. |
Salary Matrix Percent |
SAL_MTRXPCT_D00 |
|
View and edit the acceptable percentage ranges for salary increases. |
Salary Rate Code |
SAL_RATECD_D00 |
|
View and edit compensation rate code information. |
Variable Comp Plan (variable compensation plan) |
VC_PLAN_D00 |
|
View and edit data for a variable compensation plan that is not likely to change over the life of the plan. |
Stock Plan |
STOCK_PLAN_D00 |
|
View and edit stock plan and type information. |
Training History |
WA_TRN_HST_D00 |
|
View and edit employee training history along with related course cost information. |
Training Session |
WA_TRNSESSN_D00 |
|
View and edit training session expenses. |
Training Course 1 |
COURSE_1_D00 |
|
View and edit training course information. |
Training Course 2 |
COURSE_2_D00 |
Select the Course 2 tab |
View and edit additional training course details. |
Course Session |
CRSE_SESSION_D00 |
|
View and edit session information for training courses. |
Training Program Job Code |
JOBCD_TRNPR_D00 |
|
View and edit information about training program assignments for jobs in your organization. |
Course Goal Competency |
COURSE_COMP_D00 |
|
View and edit the competencies, and their associated proficiency levels, that are associated with a particular training course. |
Account Codes |
ACCT_CD_D00 |
|
View and edit the account code information from general ledger and payroll. |
Compensation Code |
COMPCODE_D00 |
|
Create nodes for the COMPCODE tree. Also, use this page to view and edit compensation code data after you have run ETLs to import compensation data from your HRMS system. |
Coverage Code |
WA_COVG_CD_R00 |
|
View and edit benefits coverage codes. |
Financial Code |
WA_FINCODE_D00 |
|
Create the nodes for the FINCODE tree. |
Industry Code |
WA_INDUSTRY_D00 |
|
Create the nodes for the INDUSTRY tree. |
Unit Code |
WA_UNITCODE_D00 |
|
Create the nodes for the UNITCODE tree. |
School Codes |
SCHOOL_D00 |
|
View and edit codes to schools, colleges, and universities. |
School Type |
SCHOOL_TYPE_D00 |
|
View and edit codes for school types. |
Accomplishments |
ACCOMP_D00 |
|
View and edit the accomplishments that your employees, applicants, or contractors may achieve. Accomplishments are languages, degrees, licenses, certificates, honors, awards, professional memberships, tests, or NVQs (National Vocational Qualifications). |
Review Rating |
WA_REVWRTG_R00 |
|
View and edit rating models, consisting of review ratings. |
Competencies |
COMPETENCY_D00 |
|
View and edit competencies organized by the following general categories: ability, knowledge, skill, salary planning, and NVQ unit. |
Competency Type |
CM_TYPE_D00 |
|
View and edit codes for competency types. |
Competency Cluster |
CM_CLUSTER_D00 |
|
View and edit codes for competency clusters. |
Vacation Plan |
ABSV_PLAN_D00 |
|
View and edit information about vacation benefit plans—including the accrual frequency, maximum balance and maximum carryover—for employees in various groups. |
Absence Code |
ABS_CODE_D00 |
|
View and edit absence codes for your absence types. |
Absence Type |
ABS_TYPE_D00 |
|
View and edit the type of absences employees can take. |
Absence Class |
ABS_CLASS_D00 |
|
View and edit the codes for absence classes. |
Health and Safety Data |
WA_INJ_ILL_D00 |
|
View and edit employees' health and safety incident data. |
Applicant Disability |
APP_DIS_D00 |
|
View and edit information on an applicant's disability. |
Disability |
DISABILITY_D00 |
|
View and edit information about an employee's disability. |
Department Budget Earn Dist (department budget earnings distribution |
DEPT_BUDERN_D00 |
|
View and edit the earnings distributions to accounts for each of the specified department budget levels. |
Duration Group Definition |
WA_DUR_GRP_DFN |
|
Define duration groups based upon time duration (such as age, service, job, grade, and so on) which can be used in analysis templates. |
Understanding Review and Edit of Dimension and Reference Data
You can view and edit the data in certain data warehouse dimension and reference tables at any time using the pages in the OW-E Dimension Maintenance, HRMS menu. The main point to remember about these pages is that their intended purpose is for you to monitor and perform slight edits on the data in the data warehouse tables. Do not use them in the traditional, transactional sense to manually add rows of data to the system. Many of the edits and checks that are built into the HRMS system to ensure data integrity are not present in the data warehouse tables. This means that you could create data that would not be valid in the source system. The vast majority of the data in your data warehouse tables should be imported using ETL.
Several dozen dimension and reference table access pages are in the OW-E Dimension Maintenance, HRMS menu. The fields on these pages and in the underlying tables are mostly a mirror representation of, or a selection of, those in the HRMS system. The pages themselves are fairly simple setup pages. For these reasons, this topic does not address these pages in any great detail, except to make note of any important differences in how they are applied in Workforce Analytics.
Viewing and Editing Departments
Load HRMS departments into the Department table (DEPARTMENT_TBL). Use them to create a financial-related Department tree to provide a hierarchy for departmental accounts. Also use the same departments to create an Organization (Department) tree to provide a hierarchy for all departments in your organization. A secondary purpose of the Organization tree is to create row-level group security.
Setting Up Geography IDs
The Geography dimension in Workforce Analytics helps provide a hierarchical structure to your organization's locations, using the Geography tree. Prior to loading locations from HRMS, use the Geography page (GEOGRAPHY_D00) to create geographical regions, which logically group together locations in your organization. Map locations to the geographical regions using the Location Mapping page (WA_LOCN_MAP_DFN).
Viewing and Editing Job Codes and Job Code Sets
The job code dimension in Workforce Analytics uses the Job Code tree to provide a hierarchical structure to your organization's jobs. Job code sets are the nodes of the tree. They are logical groupings of job codes that you create using the Job Code Set page (WA_JOBCDSET_D00). Job codes provide the details of the tree. Load job codes into the Job Code table (JOBCODE_D00), prior to building the tree.
Compensation Values Associated with Job Codes
If you are using an external market compensation survey to compare your organization's salaries with industry averages, the salary midpoint for the equivalent job code goes in the Survey Salary field. These are usually annualized values.
Note: When you import market compensation survey data into the Operational Warehouse (OW), the data-loading process annualizes the compensation values. All salary survey data that is processed in the Define Market Compensation module of Workforce Rewards is standardized to annual values.
Viewing and Editing Personal Information
When you have loaded personal data from HRMS into the PERSONAL_D00 table, you can use the pages in the Personal Data component to view and edit the information.
Running Application Engine PER099 Prior to Importing Personal Data
Remember that the PS_EMPLOYEES table is used in HRMS to improve the efficiency of report processing. The table is not updated dynamically by the HRMS system; you must run the Application Engine process PER099 (Refresh Employees Table) to update the PS_EMPLOYEES table. To ensure that you are loading the most up-to-date information into the EPM tables, run the Application Engine process PER099 immediately prior to importing personal data into the OWS staging tables.
Clarifying Row-level Security Issues About Personal Data
The PERSONAL_D00 and JOB_F00 tables are intended to be populated with data using the delivered ETL process. If you enter new rows of employee data manually using the Personal Data page, and group security is in effect based on JOB_D00_VW (which is a view of JOB_F00) then you may not see this data displayed on pages having row-level security based on employees. Two additional steps are required to view the data. You must first add a corresponding row of data to JOB_F00, and then you must rerun the PF_SECURITY process for security to be enabled.
If, after you use the data migration process to load data into PERSONAL_D00, the data does not appear in the online pages such as Personal Data, then the problem may have to do with implementation of group (row-level) security based on employees. The search record for PERSONAL_D00 is PERSONAL_SRCH. PERSONAL_SRCH is a join of PERSONAL_D00 and PF_SY_CLASS_OBJ. PF_SY_CLASS_OBJ is populated by running the PF_SECURITY process. If, in your implementation, row-level security is enabled at the employee level, then the security groups are likely built referencing JOB_F00 (or a view of JOB_F00). Both JOB_F00 and PERSONAL_D00 must be populated with corresponding rows of data, and then the PF_SECURITY process must be run, before the data appears online.
Note: If your implementation includes row-level security at the employee level, based on JOB_F00 and PERSONAL_D00, then load the JOB_F00 and PERSONAL_D00 tables before setting up security.
Importing Personal Names
During data migration, the system populates the Personal Name field in the PERSONAL_D00 table with the data from the Name field in the HRMS Personal Data table. You can view and edit this data using the Personal Data component. During data loading, the system populates the Personal Name field with the name of the individual from the HRMS Names table.
The Last Name, First Name, and Middle Name fields separate the person's personal name into its components. These fields are also populated by HRMS source fields during data loading.
The Last Name Search and First Name Search fields are populated from the HRMS source fields during data loading. These are versions of the names that have no punctuation (for example, apostrophes are removed) and are in all capital letters. These versions of the names are used by the HRMS system as search values.
Viewing and Editing Position Data
The POSITION_D00 page and underlying table are provided for those sites that use the Position Management feature of HRMS. Use of this page and table is not necessary if Position Management is not used at your site.
The position dimension in Workforce Analytics uses the Position tree to provide a hierarchical structure for positions within an organization. Position data is loaded into the Position table (POSITION_D00). Position data and job requisition data are aggregated into a single structure, centering on the Staffing table (STAFFING_F00).
Viewing and Editing Location Data
You import locations into the Location table, and map them to geography IDs. The geography dimension uses the GEOGRAPHY tree to provide a hierarchical structure to your locations and geographical regions.
During initial setup, first determine what geographical regions you want to map your HRMS locations to. Enter the geography IDs for those regions using the Geography page. Create a GEOGRAPHY tree using these geography IDs. Then map your locations to the geography IDs using the Location Mapping (WA_LOCN_MAP_DFN) page. If you have done all of this correctly, then during the ETL process, for each location imported to the LOCATION_D00 table, the value from the WA_LOCN_MAP_DFN.GEOGRAPHY_ID field is used to populate the LOCATION_D00.GEOGRAPHY.ID field.
Viewing and Editing Salary Grades and Steps
After you have loaded the salary administration plan, salary grade, and salary step information from HRMS into the data warehouse tables, you can use the Salary Grades component to view and edit the grades and steps that make up salary administration plans.
Understanding Salary Value Calculations
After you have imported salary data, you can review the data and correct it manually using these pages. When you change a value using one of the online pages, the EPM system does not recalculate any of the other related numbers automatically.
Remember that in the HRMS system, the salary terms are defined in the following way:
Note: In the Workforce Rewards Define Base Pay Structure module, the system defines and calculates midpoints as the maximum plus the minimum divided by two. In your implementation of HRMS, you may have chosen to define compensation midpoints differently. If this is the case, then keep this difference in mind when comparing compensation midpoints from your imported data with compensation midpoints created using Workforce Rewards.
Setting Up Compensation Codes
The compensation dimension in Workforce Analytics uses the Compensation Code tree to provide a hierarchical structure to the earnings, deductions, benefits values, and PIN codes loaded from the HRMS system.
You create compensation codes using the Compensation Code page (COMPCODE_D00). Then you map the earnings, deductions, benefits values, and PIN codes from HRMS to these compensation codes using a series of mapping pages.
Compensation Classes
Compensation classes are from the translate table. The valid values correspond to the delivered nodes on the model COMPCODE tree.
Account Field
The account field is used for tracking financial data. Prompt values are from the GL_ACCOUNT_TBL table. You load values for the GL_ACCOUNT_TBL table while setting up basic EPM infrastructure and EPM Foundation. This information can be found in the Enterprise Performance Management Fundamentals.
Setting Up Industry Codes
The industry code dimension is used primarily in the Workforce Rewards Market Compensation module to deal with loading external survey data. You use the Industry Dimension page to enter industry codes that define the industry that an organization is in. These codes categorize data from external survey sources and validate comparison to data for your organization. Industry codes are the nodes on the Industry tree.
Setting Up Financial Codes
The financial code dimension is used primarily in the Workforce Rewards Market Compensation module to deal with loading external survey data. You use the FinCode Dimension page to enter financial codes that define the relative financial size of your organization. These codes categorize data from external survey sources and validate comparison to data for your organization. Financial codes are the nodes on the FINCODE tree.
The From Value and To Value fields indicate the value range for the approximate size of an organization's financial worth.
Setting Up Unit Codes
The unit code dimension is used primarily in the Workforce Rewards Market Compensation module to deal with loading external survey data. You use the Unit Code page to enter unit codes that define the relative size of an organization (using an alternate criteria other than a financial one). These codes categorize data from external survey sources and validate comparison to data for your organization. Unit codes are the nodes on the UNITCODE tree.
Note: The purpose of the unit code dimension is to provide an alternate means of measuring the relative size of companies participating in external surveys, as opposed to using the relative financial size of the companies. A typical unit of measure would be the number of employees in a company. The concept of unit is generic enough that the units can be other measures besides number of employees. For example, in the hospital industry the unit could be the number of hospital beds. Or in the hotel industry the unit could be the number of rooms.
The From Value and To Value fields indicate the value range for the approximate size of an organization based on this unit of measure.
Defining Age, Service, and Other Duration Groups
Because durations are not part of the HRMS system, you can define them online using the Duration Group page to provide time dimension for analysis templates (WA_DUR_GRP_DFN).
You can define duration groups based on age, department, grade, job, pay change, promotion, and service. If the group is Service, you can map your service group ID to a benchmark survey service ID.
You can specify whether to measure your group duration in days, months, or years. This defines the frequency for the values that you enter in the Group Low Value and Group High Value fields. For example, if you select a frequency of Months for the group type Promotion, then the system measures the duration since a person's last promotion in months. If you select Days, then the duration since the person's last promotion is measured in days, and so on.
The Group Low Value and Group High Value fields enable you to define the range of values for a duration group. If you create a group ID such as No Service or No Age, enter a value of 0 (zero) in both the Group High Value and Group Low Value fields.