Data Cleansing Guide
Part 1 - Creating a role data download report
part 1 - creating a role data download report
Why create a data download?
Getting started
A couple of things to check first
Data Access
Role Notes
Creating a template
Designing your template
Creating a filter
Running your report
Introduction
The first stage in spring cleaning your database is to find out what information you have about your roles and how you, and perhaps others in the past, have organised it and used the database.
This part of the guide focuses on getting a report or reports out of Towers Watson into Excel and the purpose of each of the reports.
Why create a data download?
There are two reasons for creating reports to download the role data you hold on your Towers Watson database.
1To create a back up file of all your role data
2To assess what information you have, and to clear out role data that is no longer needed
These reports can be the same or they can be tailored to fit the two different purposes.
The second report will also help you to create a file to be imported back into Towers Watson with your changes and notes.
This guide will take you through the steps you need to take to generate a report as an Excel file.
Getting started
The first step is to sign into your Towers Watson database.
If you cannot remember your sign in details then please contact ECC by emailing and we will let you have a temporary password etc. If you have just forgotten your password then you can use the ‘Forgot Password’ link on the sign in screen to create a new password – but you will need to be able to answer your three security questions.
Once you have accessed to your database you will see your ‘Home’ page.
From the ‘Home’ page you need to go to ‘Templates’ in the ‘Reports’ section, you can do this by clicking on the link shown on the ‘Home’ page or through the drop down list under ‘Reports’ in the red ‘Menu’ bar.
A couple of things to check first
Data Access
Before you start to create a report it is wise to check that you have access to all the role data on your database, otherwise the report will only contain the information that you do have permission to see.
To check your access you will need to have ‘Systems Administrator’ rights on the system. If you are not sure then you can check by seeing what options you have in the ‘Setup’ menu.
If you can see ‘Users’ as an option then click on that this and you will see a list of all the people with access to your database split into a number of groups.
Click on your name and then the ‘Data Access’ tab
All the areas that are highlighted in grey are those that you have access to. To add additional areas highlight them and click on ‘Save’ on the left hand menu bar. (Make sure you retain the ones already highlighted)
If you cannot see the ‘Users’ option in the Setup menu then contact a member of your team that does have ‘Systems Administrator’ rights to change your access permissions. If no one in your team has ‘Systems Administrator’ rights then email and we can change your access group. For security purposes we will contact your organisation to check that this request is valid unless the email is from your organisation’s nominated ECC contact.
In rare instances the default security fields of ‘Role Location’ and/or ‘Employee Location’ may have been deleted from your database or made inactive, the list of fields shown on any role or employee record will confirm whether these are active fields. If they are absent you will have access to all role and employee records by default, unless a different security field has been set.
Role Notes
To be able to report on whether a role has documents attached to it Towers Watson have added a fields called ‘Has Attachment’, however this needs to be made available before it can be added to a report.
To do this select ‘Fields’ from the ‘Setup’ menu
Highlight ‘Has Attachments’ in the list of ‘Available Fields’ and move it into the ‘Selected Fields’ using the green arrow.
It will appear in the section labelled ‘Other’; you can change its position by highlighting it and using the up or down green arrows to get it in the right place.
Creating a Template
For the purposes of this guide we are going to create a template to gather role data. The foundation of any report is a ‘Template’, this determines which fields are included in a report and the order they appear in on the Excel spreadsheet.
Select ‘Templates’ from the ‘Reports’ menu.
The ‘Templates’ section has three parts:
- ‘My Templates’, which are those that you have created
- ‘Shared Templates’, which are those that other people have created and shared with you so that you can also use them
- ‘Standard Templates’, which are those ECC have created and are available to all users on all databases.
To start to create your template make sure you are in the section called ‘My Templates’ and click on ‘New’ on the left hand menu bar.
Once you have clicked on ‘New’ the inset dialogue box shown in the screen shot above will appear.
In the box click on the ‘Tabular’ option and then click OK.
The screen below will then appear and you can start to design your template.
Designing your template
For the purpose of checking what data you have in your database the template is straightforward to create.
Firstly give the report a name, this is the only mandatory information required.
You can also decide at this point if you want to share the template with colleagues. If you want to share then tick the box next to ‘Is Shared’ in the ‘Template Details’ section.
You can change this (or any other information in the template) later if you want to by going back to the ‘Templates’ screen, and in the ‘My Templates’ section click on the name of the template, which brings you to the screen above, make the changes you need to and then click ‘Save’.
The final stage in designing your template is to select which fields you want to include. In most cases the list of possible fields to include is divided into sections and in this guide we are concentrating on the role based fields.
The role based fields are divided into five sections
Role: Basic Data
Role: Role Evaluation Data
Role: Pay Details
Role: Other
Role: Points Evaluation Profiles
To select a field highlight it on the left hand list and use the green arrow to transfer it to the right hand column
Like most lists it is possible to select multiple fields to transfer in one go by using either the ‘Shift’ key for a adjacent fields or the ‘Control’ key for non-adjacent fields while highlighting them.
If you are creating a full backup of your Role data then include all the fields in the Role sections. (See note below)
If you are creating a template to assess and cleanse your data then you do not need to include any of the following:
‘Profile points by factor’
‘Profile points by subfactor’
‘Profile points by question’
‘Profile challenges by question’
‘Percentage of available points’
These are calculated fields and will not be likely to add any value when undertaking a data cleansing exercise, they will just make the spreadsheet even more unwieldy.
Note:There are two fields that should not be included in either report because they are composites and render the report out of alignment without adding any relevant information, these are:
‘Level by Organisational Sub Competency’
‘Comments by Organisational Sub-Competency’
Those on the left are the fields that you can leave behind and those on the right are those to include.
If you add a field that you then decide you don’t want you can use the green arrow to remove it, but the system asks you to ‘Save’ what you have done so far first.
In order to complete the design of your template click ‘Save’ on the left hand menu bar, a message will appear to let you know the template has been saved.
Creating a filter
If you have a large number of roles in your database you may wish to download them in batches and the creation of filters will help this process.
The usual two fields to use to divide your role data would be either ‘Role Location’ or ‘Role Division’, but you will need to look at what fields you have used in your database and which have the most useful ‘Look up’ options. If you have any doubt then email us via and we will have a look at your options with you.
In this example we are using ‘Role Location’ to divide our data into more manageable chunks.
From the ‘Reports’ menu select ‘Filters’
In the ‘My filters’ section select ‘New’ from the left hand menu.
Give the new filter a name and click on ‘New Filter Rule’ on the left hand menu bar.
You can also decide whether you want to share the filter with colleagues, if you do then check the box next to ‘Shared?’.
Once you have clicked on ‘New Filter Rule’ click in the ‘Select a Field’ box and all the possible filter fields will be shown (the Employee fields come after the Role fields). Click on the one you have decided on.
Once you have selected a field the second two boxes in the row will be populated
Firstly with an ‘Operator’ in this case ‘In’ or ‘Not In’, select ‘In’ and then highlight which of the Role Locations you want to use with this filter. You can select more than one to include.
Then click on ‘Save’ on the left hand menu bar.
Running your report
To run your report, select ‘Report Builder’ from the drop down ‘Reports’ menu.
The screen below will appear.
The process is governed by the top row of grey tabs, and the options within each stage are in the second row of grey tabs.
To run this report you first need to be in ‘Select Template’ and then ‘My Templates’ as shown above. Check the box next to the name of the template you want.
Then move to the ‘Select Filter’ tab
If you want all data in your database then click on the ‘Standard Filters’ tab on the second row and to check the box next to ‘All Roles’.
If you want to use your own filter then it should show in the ‘My Filters’ tab and you need to check the box next to its name.
For the purposes of this report you can miss out the third tab (‘Refine Filter’) because you either want all the roles in your database or those selected through the filter you set up.
Click on the ‘Review Selection’ Tab
You then need to select ‘All Profiles’ from the ‘Filter Profiles’ options. The options you are presented with here may differ between HERA databases and FEDRA databases – but both have the ‘All Profiles’ option that you need.
From the left hand menu select ‘Run’ this takes you to the Report Queue screen.
When the ‘Status’ shows as ‘Complete’ then check the box next to the name of the report and click on ‘Download’ from the left hand menu bar.
You can return to this point to re-run the report by selecting ‘Report Queue’ from the Reports menu, if you select ‘Download’ the report will run again without any updates you may have made to the system, if you click on ‘Run Again’ then the report will be updated to reflect any changes you have made to your database since the report was run last.
The report will remain in the ‘Report Queue’ for 14 days from the last time it was run. If you want to run the report again after it has disappeared from the ‘Report Queue’ then you need to start with the ‘Report Builder’ menu again.
When you have selected ‘Download’ or ‘Run Again’ the dialogue box below will appear
Click OK to open the report in Excel. This should happen automatically, but if it does not then you need to open the report from Excel and your ‘Downloads’ folder.
1