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	select EPM Foundation, then select Business Metadata, then select OW-E Dimension Maintenance, then select Common, then select Department	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	select EPM Foundation, then select Business Metadata, then select OW-E Dimension Maintenance, then select Common, then select Geography	Create geography identification codes, which are geographical regions, which your locations roll up to.
Job Code	JOBCODE_D00	select EPM Foundation, then select Business Metadata, then select OW-E Dimension Maintenance, then select HRMS, then select Employee and Job, then select Job Code	View and edit data in JOBCODE_D00, and provide the details for the JOBCODE tree.
Job Code Set	WA_JOBCDSET_D00	select EPM Foundation, then select Business Metadata, then select OW-E Dimension Maintenance, then select HRMS, then select Employee and Job, then select Job Code Set	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	select EPM Foundation, then select Business Metadata, then select OW-E Dimension Maintenance, then select HRMS, then select Employee and Job, then select Job Earnings Distribution	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	select EPM Foundation, then select Business Metadata, then select OW-E Dimension Maintenance, then select HRMS, then select Employee and Job, then select Job Tasks	View and edit job codes for which sets of job tasks are available.
Personal Data	PERSONAL_D00	select EPM Foundation, then select Business Metadata, then select OW-E Dimension Maintenance, then select HRMS, then select Employee and Job, then select Personal Data	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	select EPM Foundation, then select Business Metadata, then select OW-E Dimension Maintenance, then select HRMS, then select Benefits, then select Dependent Data	View and edit information about dependents and beneficiaries.
Position	POSITION_D00	select EPM Foundation, then select Business Metadata, then select OW-E Dimension Maintenance, then select HRMS, then select Employee and Job, then select Position	View and edit position data if Position Management is implemented in the source HRMS system.
Salary Grade Data	WA_SAL_GRADE_D00	select EPM Foundation, then select Business Metadata, then select OW-E Dimension Maintenance, then select HRMS, then select Salary, then select Grade	View and edit a salary grade structure for your salary administration plan.
Salary Increase Matrix	SAL_MTRXTBL_D00	select EPM Foundation, then select Business Metadata, then select OW-E Dimension Maintenance, then select HRMS, then select Salary, then select Increase Matrix	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	select EPM Foundation, then select Business Metadata, then select OW-E Dimension Maintenance, then select HRMS, then select Salary, then select Matrix Percent	View and edit the acceptable percentage ranges for salary increases.
Salary Rate Code	SAL_RATECD_D00	select EPM Foundation, then select Business Metadata, then select OW-E Dimension Maintenance, then select HRMS, then select Salary, then select Rate Code	View and edit compensation rate code information.
Variable Comp Plan (variable compensation plan)	VC_PLAN_D00	select EPM Foundation, then select Business Metadata, then select OW-E Dimension Maintenance, then select HRMS, then select Compensation, then select Variable Comp Plan	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	select EPM Foundation, then select Business Metadata, then select OW-E Dimension Maintenance, then select HRMS, then select Benefits, then select Stock Plan	View and edit stock plan and type information.
Training History	WA_TRN_HST_D00	select EPM Foundation, then select Business Metadata, then select OW-E Dimension Maintenance, then select HRMS, then select Training, then select Training History	View and edit employee training history along with related course cost information.
Training Session	WA_TRNSESSN_D00	select EPM Foundation, then select Business Metadata, then select OW-E Dimension Maintenance, then select HRMS, then select Training, then select Training Session	View and edit training session expenses.
Training Course 1	COURSE_1_D00	select EPM Foundation, then select Business Metadata, then select OW-E Dimension Maintenance, then select HRMS, then select Training, then select Courses	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	select EPM Foundation, then select Business Metadata, then select OW-E Dimension Maintenance, then select HRMS, then select Training, then select Course Session	View and edit session information for training courses.
Training Program Job Code	JOBCD_TRNPR_D00	select EPM Foundation, then select Business Metadata, then select OW-E Dimension Maintenance, then select HRMS, then select Training, then select Training Program Job Codes	View and edit information about training program assignments for jobs in your organization.
Course Goal Competency	COURSE_COMP_D00	select EPM Foundation, then select Business Metadata, then select OW-E Dimension Maintenance, then select HRMS, then select Training, then select Course Goal-Competency	View and edit the competencies, and their associated proficiency levels, that are associated with a particular training course.
Account Codes	ACCT_CD_D00	select EPM Foundation, then select Business Metadata, then select OW-E Dimension Maintenance, then select HRMS, then select Common, then select Account Codes	View and edit the account code information from general ledger and payroll.
Compensation Code	COMPCODE_D00	select EPM Foundation, then select Business Metadata, then select OW-E Dimension Maintenance, then select HRMS, then select Compensation, then select Compensation Code	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	select EPM Foundation, then select Business Metadata, then select OW-E Dimension Maintenance, then select HRMS, then select Benefits, then select Coverage Code	View and edit benefits coverage codes.
Financial Code	WA_FINCODE_D00	select EPM Foundation, then select Business Metadata, then select OW-E Dimension Maintenance, then select HRMS, then select Common, then select Financial Code	Create the nodes for the FINCODE tree.
Industry Code	WA_INDUSTRY_D00	select EPM Foundation, then select Business Metadata, then select OW-E Dimension Maintenance, then select HRMS, then select Common, then select Industry Code	Create the nodes for the INDUSTRY tree.
Unit Code	WA_UNITCODE_D00	select EPM Foundation, then select Business Metadata, then select OW-E Dimension Maintenance, then select HRMS, then select Common, then select Unit Code	Create the nodes for the UNITCODE tree.
School Codes	SCHOOL_D00	select EPM Foundation, then select Business Metadata, then select OW-E Dimension Maintenance, then select HRMS, then select Competencies, then select School	View and edit codes to schools, colleges, and universities.
School Type	SCHOOL_TYPE_D00	select EPM Foundation, then select Business Metadata, then select OW-E Dimension Maintenance, then select HRMS, then select Competencies, then select School Type	View and edit codes for school types.
Accomplishments	ACCOMP_D00	select EPM Foundation, then select Business Metadata, then select OW-E Dimension Maintenance, then select HRMS, then select Competencies, then select Accomplishments	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	select EPM Foundation, then select Business Metadata, then select OW-E Dimension Maintenance, then select HRMS, then select Salary, then select Review Rating	View and edit rating models, consisting of review ratings.
Competencies	COMPETENCY_D00	select EPM Foundation, then select Business Metadata, then select OW-E Dimension Maintenance, then select HRMS, then select Competencies, then select Competencies	View and edit competencies organized by the following general categories: ability, knowledge, skill, salary planning, and NVQ unit.
Competency Type	CM_TYPE_D00	select EPM Foundation, then select Business Metadata, then select OW-E Dimension Maintenance, then select HRMS, then select Competencies, then select Competency Types	View and edit codes for competency types.
Competency Cluster	CM_CLUSTER_D00	select EPM Foundation, then select Business Metadata, then select OW-E Dimension Maintenance, then select HRMS, then select Competencies, then select Competency Cluster	View and edit codes for competency clusters.
Vacation Plan	ABSV_PLAN_D00	select EPM Foundation, then select Business Metadata, then select OW-E Dimension Maintenance, then select HRMS, then select Vacations/Absences, then select Vacation Plan	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	select EPM Foundation, then select Business Metadata, then select OW-E Dimension Maintenance, then select HRMS, then select Vacations/Absences, then select Absence Code	View and edit absence codes for your absence types.
Absence Type	ABS_TYPE_D00	select EPM Foundation, then select Business Metadata, then select OW-E Dimension Maintenance, then select HRMS, then select Vacations/Absences, then select Absence Type	View and edit the type of absences employees can take.
Absence Class	ABS_CLASS_D00	select EPM Foundation, then select Business Metadata, then select OW-E Dimension Maintenance, then select HRMS, then select Vacations/Absences, then select Absence Class	View and edit the codes for absence classes.
Health and Safety Data	WA_INJ_ILL_D00	select EPM Foundation, then select Business Metadata, then select OW-E Dimension Maintenance, then select HRMS, then select Employee and Job, then select Health and Safety Data	View and edit employees' health and safety incident data.
Applicant Disability	APP_DIS_D00	select EPM Foundation, then select Business Metadata, then select OW-E Dimension Maintenance, then select HRMS, then select Applicants, then select Applicant Disability	View and edit information on an applicant's disability.
Disability	DISABILITY_D00	select EPM Foundation, then select Business Metadata, then select OW-E Dimension Maintenance, then select HRMS, then select Employee and Job, then select Disability	View and edit information about an employee's disability.
Department Budget Earn Dist (department budget earnings distribution	DEPT_BUDERN_D00	select EPM Foundation, then select Business Metadata, then select OW-E Dimension Maintenance, then select HRMS, then select Compensation, then select Department Budget Earn Dist	View and edit the earnings distributions to accounts for each of the specified department budget levels.
Duration Group Definition	WA_DUR_GRP_DFN	select EPM Foundation, then select Business Metadata, then select OW-E Dimension Maintenance, then select HRMS, then select Common, then select Duration Group Definition	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:

Monthly

The annual rate divided by the pay months per year.

Hourly

The monthly rate prorated to 12 pay periods, divided by 52 weeks in the year, divided by 40 hours per week.

Midpoint

The maximum plus the minimum divided by two.

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.