Information Technology | Economical IT » Catalogue Content Management, Data Migration User Guide

Scottish Procurement Catalogue Content Management Data Migration User Guide Version 1.0 Data Migration User Guide March 2013 Data Migration – how to guide 1. Gathering data for data migration process The first stage of the data migration process is to capture all relevant data that needs to be uploaded into Catalogue Content Management (CCM). Depending on what eProcurement/eOrdering system you use you should be able to run reports which will provide you with the required information. You will only use the one Data Migration Template (DMT) (which consists of 7 separate tabs) to migrate all relevant information. Key information that needs to be captured in the data migration templates are:- Buying Organisation (BO) informatio - BO identifier (I.D) (unique identifier) name, category(A, B, C),email, phone number. Users information - BO user I.D & BO organisation id (both unique identifiers), name, user name, password (optional), department, e:mail, address, role within CCM

(Admin, Buyer, Requisioner) Supplier Organisations (SO) Information - SO I.D (unique identifier), supplier name, company type (PLC, Private Company), DUNS number, address, e:mail address, phone number Supplier Users (SU) – SU I.D & SO ID (both unique identifiers), name, username password (optional), e:mail, address (optional) System Admin Users – name, username, password, e:mail Contracts – title, contract reference number, start/end date, description (optional) United Nations Standard Products and Service Code (UNSPSC) –one code that depicts entire contract e.g 42000000 - Medical Equipment and Accessories and Supplies Catalogues – catalogue key, contract ref no, supplier user I.D, valid from/to date, procurement contact details (optional), catalogue file, (punchout secret key, punchout URL, punchout I.D) - if any are applicable Depending on what eProcurement and reporting solutions are in use there are various options available to obtain the necessary information to

populate the DMT:    1.1 If your Centre of Expertise (CoE) or BO has a Datamart supplier reports can be produced. If these are run at a CoE level they can be filtered to a local level, further filters may be available on content type (catalogue, non-catalogue and punchout), please check with your CoE if this service is available. To identify users at a local level (for Pecos customers only) Actuate can be used to run a user report. Local contract databases (for supplier and contact information only). Reporting tools specific to the organisation. Cleansing of data Like any data migration process, it is recommended that every effort is taken to cleanse all data prior to it being loaded into CCM. This can be a lengthy process (depending on the quality of data in the first instance), therefore it is important that a suitable amount of time is factored into the data migration process. For CCM to work in an efficient manner and for the benefits to be realised, there must only

be 1 unique supplier profile in CCM wherever Version 1.0 Data Migration User Guide March 2013 possible. However in some instances there may be a requirement for a duplicate supplier to be added. This is only authorised in the following scenarios:• Supplier has multiple legal entities (eg numerous Data Universal Numbering System (DUNS) numbers) which relates to the supplier having more than the one depot. • Supplier has numerous different ways in which they transact with customers e.g cXML, email, fax etc. • Logistical reasons on the way that suppliers are set up i.e maintenance is covered from one location, orders are based in another therefore Purchase Orders (PO’s) need to be separated. Note: All duplicate suppliers will be identified in CCM. The reasons for their addition (if not clear) will be discussed at the monthly CCM User Group. If it is agreed that a standard supplier profile can be used, the duplicate supplier profile will need to be removed/deactivated from

CCM. As a result of this, all data must be thoroughly cleansed before it is loaded into CCM. Any duplicate information that is included in the data migration process will be highlighted in the error reports produced by CCM. This information will need to be removed from the DMT Full details on supplier management during CCM implementation can be found on the ePS customer only area or see document referenced below). If you do not have access to this, please email the CCM mailbox at Scottish ProcurementCCM@scotland.gsigovuk To help identify all potential duplicate information that is currently on CCM, ED can provide reports which will capture this information. Point 21 below provides more detail 2. Templates required for the Catalogue Content Management (CCM) process In order to migrate all of the necessary data onto CCM, a DMT has been created by European Dynamics (ED). This template must be used and populated when migrating any data onto CCM. You will find the latest version of the DMT

in the DMT section of this site A copy of the latest version of the template can be obtained from the ePS customer only area, if you do not have access to this please email the CCM mailbox at Scottish Procurement-CCM@scotland.gsigovuk The data migration process should be managed in two stages:(i) Capture and migrate all relevant data for your buying organisation that is already live in CCM (e.g relevant Cat A & Cat B contacts) (ii) Capture and migrate all remaining data that needs to be uploaded into CCM. Both of the above steps can be captured on the one DMT (per BO or CoE) which will be the only template used throughout the data migration process. Version 1.0 Data Migration User Guide March 2013 2.1 Capture all data currently on CCM Step 1 European Dynamics (ED) will provide a pre-filled DMT which will have the first 4 worksheets (Buying Organisations, BO Users, Supplier Organisations, Supplier Users) fully completed with the relevant data from CCM Production at the time of

your data migration process commencing. All data that is currently held in CCM Production must be entered in the exact same format on the DMT as available in CCM Production. Any differences between your data migration information and the information currently live in CCM Production will generate an error report. To obtain a copy of the pre-populated DMT please e:mail the CCM Mailbox Scottish Procurement-CCM@scotland.gsigovuk The Scottish Government will then approve the request and send on to ED (this is to ensure the scheduling and management of all data migration activity is captured). On receipt of the approved e:mail, ED will send the pre-filled DMT to the relevant end user, this is the template that you will use throughout the data migration process. Not all of the data that is pre-filled on the DMT will be of use to your CoE/BO, therefore all data that is not relevant can be deleted from the DMT. This is done by deleting the necessary lines in each relevant tab. If you do not

delete this information this may result in errors occurring which will be captured in the error report. Three types of data will need to be removed from the DMT:1. Duplicate data – any data that is currently on the DMT that you already use ie Buying Organisations, Suppliers, addresses etc. 2. Data that you currently do not use can be removed from the pre-filled DMT however this is optional. If you wish to keep this data on the DMT there is no need to remove it A sample of a pre-populated DMT is attached in the Data Migration section of this site. 2.2 Capture and migrate all local data (not currently held in CCM) Step 2 In order to continue to populate the DMT with all local information you must gather all relevant CCM data that needs to be migrated into the CCM system. The DMT includes both mandatory and non-mandatory fields. All mandatory fields are highlighted in red These fields must be completed within the data migration process, if not completed errors will incur. Appendix 1

details all mandatory fields As the DMT is already pre-filled with the common data (with all non-relevant data having been deleted), the DMT now needs to be completed with all new data that is relevant to your specific CoE or BO. The best process is to work through each of the tabs and complete with the required data. Tabs one to five are filled out in a similar manner (ie completion of names, addresses, e:mail addresses, phone numbers etc). Tab 6 (Contract) and tab 7 (Catalogues) are completed in a similar format, however a large amount of the data that is required are the unique identifiers that have been created in tabs one to five. These unique identifiers link all the data together once loaded into CCM. (This is covered in more detail in the next section). Version 1.0 Data Migration User Guide March 2013 2.3 Unique Identifiers Within the DMT there are specific pieces of information that need to be replaced with unique identifiers. These unique codes need to be cross

referenced throughout the DMT It is critical that this needs to be done correctly as this is how the correct users, suppliers and content are all created and linked together for your CoE/BO within the CCM system. Detailed below are a list of all fields that require a unique identifier:Buying Organisations • ID (column A, e.g BO0001) BO Users • ID (column A, e.g BOU0001) Supplying Organisations • ID (column A, e.g SO0001) Supplier Users • ID (column A, e.g SU0001) 2.31 Mapping of Unique Identifiers All of the above fields need to be mapped to specific fields within the template. If the correct information is not entered into the correct fields then upon data migration load process errors will occur (you will receive a specific error message report confirming the issues ). Point 26, Phase 2 and Appendix 2 for more information Please see Appendix 1 details all of the above unique Identifiers, colour coded and mapped to the relevant fields throughout the DMT. 2.32 Unique

Naming Conventions Along with the unique identifiers there is also the need for unique naming conventions. Again these are required within specific fields in the DMT template. These fields have to be unique for all CoE/ BO’s, (however if any of this unique information relates to any contracts/content that you may use, this key information must be copied in the exact same format on the DMT (see 2.1 and sample of pre-filled DMT for more information) All the fields that need a unique naming convention are detailed below :Buying Organisation • Organisation name (e.g National Procurement, NHS Lothian etc) Version 1.0 Data Migration User Guide March 2013 • Email address ( this can be the same e:mail address for BO’s only). Only email addresses of users must be unique, as this is the only way that CCM can identify a user punching out from PECOS. Buying Organisation Users • • Username (e.g NPJSmith, AiBJHaig) Email (must be unique and valid, all user e:mail accounts in CCM

must have a valid unique email address. This email address must be unique across all users in CCM and in DMT) can only be used once within CCM) Supplying Organisation • Duns number (e.g 212141451 - Arco, this must be 9 alphanumeric digits) Supplier Users • • Username (e.g Officesgrant) Email must be unique and valid, all user email accounts in CCM must have a valid unique email address. This email address must be unique across all users in CCM and in DMTas it can only be used once within CCM) Contracts • • Contract reference number (e.g SP-ST001A, must be unique) UNSPSC code (does not need to be unique but does need to be valid, if not valid will be rejected by CCM). CCM currently uses version 8121 (as this is the version that is used in PECOS), any eProcurement/finance system that does not use this version, if there is any differences in UNSPSC codes this will result in errors which will be captured in the error reports. Note: Please ensure when populating the DMT or

manually within CCM that all user based roles (BO User and SO Users) email addresses are valid. If a user email address is unknown, his/her email address should be replaced with an email address that its domain at least belongs to the “owner” of the account, e.g if Scottish Government has created a supplier account within CCM and does not know the e:mail address of the BO/SO User, at least the email address of the supplier should be set to “xyz@scotland.gsigovuk ie any recipient under domain belonging to Scottish Government. For all other email addresses (BO & SO the domain must belong to the “owner” of the account). 2.4 Attaching catalogue files to the DMT As part of the data migration process all current catalogue files (which will be included in the data migration process) must be referenced within the catalogue tab of the DMT and with the exact same name e.g OfficeDepotcat2012xls) as the files that are saved in the zip file which are uploaded with the DMT. If the

name is not the same these files will be rejected during the data migration upload process as CCM will not recognise them. Error reports will be produced highlighting any differences in naming conventions. Version 1.0 Data Migration User Guide March 2013 All catalogue files are uploaded at the same time as the DMT. All catalogue files are sent in a zipped file and uploaded to the secure sFTP site (or whatever secure method you have agreed with ED to upload the DMT and all relevant zipped files. See point 25 for more information and options. Appendix 1, (catalogue tab) provides further examples on catalogue naming conventions. 2.5 Loading data On completing the DMT there are various options available to upload the DMT to allow ED to access the relevant information:• • 2.6 sFTP site – secure site that has been set up between ED and the Scottish Government team. This can only be accessed from a PC within the Scottish Government (therefore if this is your preferred option

you will always need to come to Scottish Government to load your data). sFTP site which can only be accessed by CoE/BO that are in the process of data migrating. One tool that can be used is Filezilla (Filezilla will allow end user to transmit files from his/her PC to a FTP location. Please see attached link for more information on how to access Filezilla - http://wiki.filezilla-projectorg/Using You will need to check with your IT department on what restrictions/permissions are required or are in place. Once you have agreed the secure file transfer process, you must inform ED of your preferred option. Scheduling of DMT Once a BO has completed populating their DMT (both with data and catalogue zipped files) the uploading of the template needs to be scheduled with the SG team and ED (to ensure workload and resources are allocated accordingly). In line with this it has been agreed that the Scottish Government team will manage and timetable one CoE/BO data migration process from end to

end. This migration process must be finished prior to the next data migration process starting. To agree and reserve a specific time period for your CoE/BO’s data migration process, please e:mail the CCM mailbox - Scottish Procurement-CCM@scotland.gsigovuk 2.7 Data migration load process Once ED receives the populated DMT and relevant zip file, the following phases will be carried out by ED. Phase 1 This phase only relates to the NHS (or any CoE/BO that is set up in the same way as NHS). All catalogue files are pre-processed in order to produce individual files per catalogue, the conclusion of this process will be:• • An error report will be produced, which details that there are missing catalogues files or more catalogue files are produced than expected. Or, confirm that there are no issues and that all files have been correctly preprocessed, enabling progression to the next phase. Version 1.0 Data Migration User Guide March 2013 Phase 2 The first step is that a copy of

the CCM PROD DB is restored to CCM TEST environment (to ensure that CCM TEST is updated with the latest information from PROD). Once this has been completed, ED will start loading the DMT. The conclusion of this process will be:• • Either an error report will be produced which advises that there are logical mistakes in the DMT xls (all errors are listed to show where and what the mistakes are). See Appendix 2 provides a list of all error messages. Or, confirm that there are no issues and that the DMT has passed validation, enabling progression to the next phase. The messages that may be included in the phase 2 error report are separated into groups (as per the tabs in the DMT, ie buying organisations, buying organisation users etc). The name in the group usually identifies the worksheet that should be used as the starting point. The error report is e:mailed by ED in notepad format. On receipt of the file (and in order to get full sight of all the error messages), the best process

is to select the data in the text file and then copy and paste into a Word document. This will present the error messages in a format that is more user friendly and much easier to read and understand (as per the format detailed in Appendix 2). Version 1.0 Data Migration User Guide March 2013 A few of the common error messages are detailed below:Message Already exists in Excel(XLS) Already exists, the details provided in the XLS do not match those already existing in the CCM system User already exists in the application and is exactly the same, but the organisation that the user belongs in the XLS does not match the organisation of the user in the application. The start date is equal or after the end date. The end date is equal or before now Could not be cross referenced in the appropriate sheet Description The XLS record exists more than once within the XLS The XLS record already exists in the DB having different values. The migration tool finds an existing user in the

application and all the fields match. However the Organisation that this user belongs to does not match the Organisation that the same user belongs to in the DB. The start date of a Contract or Catalogue is after the end date of the Contract or Catalogue The Contract or Catalogue within the XLS is already expired A value from a cell that should be cross referenced in the appropriate sheet was not found in that sheet e.g Worksheet Contracts, Column Creater#, the user id should exist in the worksheet BO Users. If the id could not be found in the BO Users sheet, this error will be thrown up. Could not be found in This error occurs when a file system filename provided (e.g Catalogue file) could not be found in the attachments provided. Is not being This happens if a file contained referenced in the in the attachments is not migration XLS referenced in the migration XLS Comma separated list This happens if a commacontains duplicate separated list contains the same values. element more than

once e.g “1, 2, 3, 1, 5” where element 1 exists twice Solution Remove duplicate records in XLS Ensure that existing record(s) have the exact same information as detailed in the data migration report (Point 2.1 provides further detail) Change the user in the DMT to the same organisation as detailed in the DB Change date so that it is within the term of the Contract or Catalogue Change date so that it is within the contract term. Add necessary I.D that is missing to correct worksheet. Identify what file is missing and add to zip file that holds all attachments. Identify file and add relevant information to the DMT. Remove the duplication. Version 1.0 Data Migration User Guide March 2013 ED have indicated that from receipt of the DMT to the producing an error report the lead time is approximately 3 business days. Phase 3 On correction of all error messages within the DMT, the migration process will upload the catalogues files and automatically kick-off their processing until

reaching the moment of catalogue approval. If following processing one or more catalogues contain business errors (e.g if two products within a catalogue contain the same Stock Keeping Unit (SKU), a new Phase 3error report is generated, presenting all errors in tabular format, detailing:• • • • • XLS row from the catalogue worksheet Catalogue file (with link to the actual catalogue) Catalogue zip file Catalogue error report, as produced by CCM (with link to actual report) Catalogue warning report, as produced by CCM (with link to actual report) This error report is produced by CCM and is identical to the reports an end-user would get if they were to upload a catalogue manually onto CCM. This report is not produced through the data migration process. If any errors arise at this point, they need to be rectified Once rectified, the catalogues will need to be uploaded in CCM. A few examples of these are detailed below;SKU/Catalogue Line: Description: SKU: 017874 Item 017874:

UoP2 is INVALID. Numeric field up to 20 digits greater then 0. Item 098323: UoP2 is INVALID. Numeric field up to 20 digits greater then 0. SKU: 098323 If no errors are produced in Phase 3, then the data migration process is successful. The final stage of the process is that ED requests the data migration owner’s approval before completing the date migration to CCM PROD environment. This step completes the migration process and all data will then be live on CCM PROD environment. Version 1.0 Data Migration User Guide March 2013 GLOSSARY ACRONYM / WORD Description Actuate Reporting solution available to PECOS customers only. BO Buying Organisation CCM CoE Catalogue Content Management system developed for the Scottish public sector Centre of Expertise cXML Commerce eXtensible Markup Language Data mart A subset of data warehouse which has been designed to satisfy a particular function or reporting area within an organisation. A data mart can be designed and developed

independently of other data marts. A data warehouse can contain many data marts. Data Migration Template – the template used for migrating all data to CCM Data Universal Numbering System – Independent system used to map vendors European Dynamics – Service Providers of CCM DMT DUNS Number ED eProcurement NHS PECOS Electronic Procurement – The process where requisitions are placed and once approved these requisitions are converted into Orders which are automatically and electronically communicated to suppliers. With the Scottish Government the main eProcurement system is PECOS National Health Service PO Elcom’s Purchase to Pay System. The system streamlines the process of requisitioning, ordering and paying for goods and services Purchase Order sFTP Secure File Transfer Protocol SKU Stock Keeping Unit SO Supplier Organisation SU Supplier user within supplier organisation UNSPSC United Nations Standard Products and Service Code – Global codes used to classify

products Microsoft Excel XLS Version 1.0 Data Migration User Guide March 2013 Reference documentation The documents mentioned in the appendices below, can be found the same site as this document. Appendix 1 – DMT showing mandatory fields and all required unique identifiers Appendix 2 – Sample of error report issued from CCM during data migration process CCM Guidelines – High Level Version 1.0 Data Migration User Guide March 2013