Rewardts.ehr.com



REWARDTM

Survey Import

Process Documentation

November 15, 2005

Table of Contents

Topic Page

Preparing the Survey Import File 3

Naming Conventions 5

Import Process 7

Survey Mailbox 9

Survey Importers 10

Appendix 11

The purpose of this document is to establish a standard procedure for importing data into the database in order to minimize duplication of records and make the database easier for all users.

Adding Data to a Survey Already in REWARDTM

1. If the survey is already in the database and you’re simply adding or replacing data (e.g. new jobs, additional scopes, or corrections), copy the original file from the FTP site and add the new/updated data to the existing file.

2. Once done, resave the file under its original name. Follow standard conventions for describing the title, level, and scope to avoid confusion and duplicate records (see below).

a. Year_Vendor_Label_Cut_Client Name/Date/Purpose.txt

Ex: 2005_Radford_Benchmark_All Cos_07.24.05.txt

3. Copy the file back to the FTP site.

Adding a New Survey

1. We recommend using the template, which can be found on Insight, as the base for your import file.

a. In order to labeled as a “complete” survey, the following Standard Import Fields should be included, where available:

i. Base

ii. Actual & Target Bonus $

iii. Actual & Target Bonus %

iv. Actual & Target Total Cash

v. Actual & Target Total Direct Cash

b. All positions and scope cuts need to be included to be labeled as “complete.”

c. Obviously, International Surveys have different pay types. Make sure to import fields that are relatable to the items above (basic salary, total remuneration, etc.) and any appropriate allowances (car, housing, etc.).

2. Refer to the “Survey Data Formatting Checklist” in the Appendix to ensure header rows and survey data are correctly formatted for import.

3. When naming the text file it’s important to use a standard format so the file can be tied back to the survey name.

a. Year_Vendor_Label_[regional cut]_[client proprietary]_Complete.txt

▪ [regional cut] and [client proprietary] are optional fields to be used to indicate a regional survey or client-specific data. If a client name is included the survey data cannot be used for another client’s project without permission from the project manager from the original project.

▪ If an entire survey is imported, “complete” will be included in the file name. Absence of “complete” indicates only partial survey data available.

4. Copy the file to the FTP site ().

REWARDTM Survey Source (Vendor)

1. The source name in REWARDTM should include only the name of the survey source/vendor name. Do not include acronyms, year, survey title, client name, special comments, etc.

2. Use standard conventions as exampled below.

|Use: |Instead of: |Notes |

|WWDS |Watson Wyatt Data Services |For domestic surveys |

|WWDS International |Watson Wyatt Data Services |For international surveys |

|Mercer |William M. Mercer | |

|Towers Perrin |TP | |

|Abbott Langer & Associates |Langer or ALA | |

|Western Management Group |WMG | |

3. In cases where there are duplicate source/vendor names in the drop-down box (i.e. Mercer or WWDS) use the FIRST instance of the listing.

REWARDTM Survey Title

1. Do not include underscores in titles.

2. A format similar to .txt file naming should be followed.

a. Year Vendor Label – Regional Cut/Client (Complete)

i. If all data from the survey are being imported, include “Complete” in the title.

ii. If only partial data are imported, do not include “incomplete” in the survey title – this causes confusion and questions when presenting reports to clients.

iii. Inclusion of a client name as part of the survey title indicates the data are proprietary to the client and is not for general use.

3. Examples:

▪ 2005 Mercer Finance, Accounting and Legal Compensation Survey (Complete)

▪ 2005 Mercer Benchmark Survey – South Central Region

➢ Because “complete” is not included as part of the title, only selected jobs and/or data cuts were imported

▪ 2005/2006 WWDS Office Personnel – For-Profit (Complete)

▪ 2005 Radford Benchmark Survey – JDA

➢ Inclusion of “JDA” in the title indicates this is a proprietary survey to be used only for work done specifically for JDA

4. Please call April Tofanelli at (415) 733-4216 should any questions arise in relation to survey naming conventions.

Job Title Definitions

1. Survey job titles should be consistent with the title in the survey. Do not use abbreviations unless they are used in the survey.

Scope Definitions

1. Scopes should be represented as they are in the survey.

1. Go to the Comp Market Import database in REWARDTM

2. Go to Tools > Import/Export Data > Import Salary Survey

Step 1: Identify Survey Source (Vendor)

▪ Always check the dropdown list first

➢ Refer to the section on naming conventions if the vendor name is not in the dropdown box.

➢ Once entered, the vendor name is retained in the list, even if the import process is aborted because of errors.

➢ If reloading a survey, you must select the exact same survey source (vendor) you did the first time.

Step 2: Describe the Survey

▪ Always check the dropdown list first – if the survey is listed determine if the data already exists in the database. If data is in addition to items already there, the import process will append the data to the database.

➢ If the survey already exists in the database, download the .txt file, and follow the guidelines in “Preparing the Survey Import File - Adding Survey Data to a Survey Already in REWARDTM”.

➢ If the survey is not already in the database, see the section on “Naming Conventions – Survey Title”.

➢ If reloading a survey, note that you must select the exact same survey as in the original load.

Step 3: Identify the Survey Source

▪ Find the appropriate text file in the list.

Step 4: Map Which Columns to Import

▪ This screen shows all the available REWARDTM fields and asks you to identify where to put the information from the import file.

➢ Select the appropriate column header from the drop-down list.

➢ Leave the field blank if nothing is to be imported.

➢ If you’re reloading a survey you must exact-match on fields which constitute a unique survey record: survey job code, level, survey job title, and survey scope, otherwise you’re not updating records you’re adding them.

➢ For all other fields, if they are not populated/mapped, they will be blanked by the reload.

Step 5: Select which Cuts (Scopes) to Import

▪ REWARDTM displays the unique scopes contained in the import file and gives the option of importing all or only some of the data.

➢ If the survey is for general use, import all of the scopes.

Step 6: Select which Jobs to Import

▪ REWARDTM displays the unique job titles contained in the import file and gives the option of importing all or only some of the data.

➢ If the survey is for general use, import all of the jobs.

Step 7: Importing Data…

▪ REWARDTM will import the data by each pay type.

▪ Click “Finish” when the process has completed.

REWARD.Surveys

1. As part of the survey import process and until REWARD functionality has been expanded to include other survey importing details (like importer’s name, comments, etc.), we’ve set up a dedicated email box in Outlook.

2. Once you’ve imported a survey send an email to REWARD.Surveys@ with the following information:

▪ Date of import

▪ FTP File name

▪ REWARDTM Import Name

▪ Importer’s Name

3. At regular intervals, a spreadsheet containing import details will be forwarded to named survey importers as a way to keep everyone informed and abreast of recent uploads.

Named Resources and Importers

1. The following table contains primary and alternate points of contact should you have any questions about importing procedures.

Primary Points of Contact

|Office |Associate Name |Office |Associate Name |

|San Francisco |April Tofanelli |Chicago |Mary Christof |

|Chicago |Mollie Saunders |New York |Emile Chung |

Alternate Resources

|Office |Associate Name |Office |Associate Name |

|Atlanta |Don Mori |Denver |Robin Plumb |

|Atlanta |Jill Davidson |Los Angeles |Evonne Maokhamphiou |

|Boston |Kevin McDonald |New York |Cindy Pohlmann |

|Boston |Stephen Heron |Paris |Helen Petit |

|Boston |Terrence Newth |Philadelphia |Kathleen Bergmann |

|Chicago |Ed Bucher |Vancouver |Anna Tikanmaki |

|Chicago |Rachel Hopkins |Vancouver |Ryan Resch |

|Dallas |Deirdre Sullivan |Washington DC |Alison Humphrey |

|Denver |Dan Everett |Washington DC |Dana Finkelstein |

|Denver |Kimerle Campbell |Washington DC |Natasha McGeehan |

Survey Data Formatting Checklist

1. Review Survey Data Spreadsheet

▪ Delete columns of data you do not want to import

2. Format the Survey Header Row

▪ One row of header data

▪ No hidden columns in the file

▪ No spaces in the header file

▪ No symbols (%, $, “”)

▪ Create a “Filler” column (last column)

3. Format the Survey Data

▪ No hidden rows in the file

▪ No symbols (%, $, “”, etc.)

▪ No commas in numeric data fields

▪ Concatenate scope into one column if shown in more than one column

▪ Data should be represented as full numbers

(e.g. 51.4 to represent $51,400 should be entered as 51400)

▪ Percent data should be loaded as a numeric value

(e.g. 10% should be entered as 0.10)

▪ Add in “dummy data” in the last column (e.g. 0 or X)

4. Save and FTP your Survey Data File

▪ Save as .TXT

▪ FTP to

-----------------------

The preference is for ALL available comparators to be added, however in the absence of all data add at least the 25th, 50th, 75th and 90th data

................
................

In order to avoid copyright disputes, this page is only a partial summary.

Google Online Preview   Download