Extract and Load via SSMT

Description

The Starter Set Migration Tool (SSMT) is an Allscripts provided tool that allows clinics to extract and load the data in their Allscripts Touchworks EHR. Three common uses are:

1. To move data from one database to another
2. To load data from a spreadsheet to a database
3. To extract data, edit it, and then load it back

This tool should only be used by persons who understands how and when to use SSMT and always adhere to the Allscripts documented process when utilizing this tool.

Warning: Ensure that you complete a database SQL Backup prior to loading any data through SSMT. The tool is pretty robust, but human error or program bugs could create a mess. Completing a backup first takes only a few minutes and could save hours of time if something does happen.

Excel Formatting Tips

Excel

SSMT v2.2. HF 1

SSMT Version v2.2. HF 1 contains several changes (these can also be found on Supportforce> Production Documentation> SSMT and CMT category:

• Removed "Security Code Name" field from content category "Task Name" in SSMT.* Added Specimen Type category
• SSMT Installer allows clients to install a clinical and/or framework database that is not named "Works" and "IDXwf."
• SSMT - "Favorites: Vaccines" creates and load a new entry successfully.
• SSMT "Favorites: Medication" - The sublist of Users should be in alphabetical order by displayed user name.
• Using SSMT to copy Task Views can corrupt the Task Views if the view (1) contains a Task Type with a name that has parentheses [such as 'Mng Chg Edits (Manage Charge Edits)'] or (2) contains many entries for a single filter in the view.
• SSMT - Should be able to extract, edit, and load Note Definitions successfully.You are now able to extract and edit the "SectionDisplayName" column; change the "Default Action" column to "1" for all of the Assessment and Active Problems; and then load Note Definitions successfully.
• SSMT content category "Clinical Desktop Views - Users" extracts the correct information for users and set users defaults appropriately.
• SSMT is removing the entry from "NeedsInfoDEList" using the "OID - Orderable Item" content category.
• SSMT content category "Favorites: Problem Based - Meds" - Should be able to remove items.You are now able to successfully remove favorite items using SSMT content category "Favorites: Problem Based – Meds".
• The SSMT content category "OID_OCD Mapping" will now correctly overwrite existing OCD mappings in the OID and delete existing OCD mappings in the OID (this is equivalent to "unmapping" an item).
• SSMT - The Suffix and Prefix fields are displaying in the wrong fields for Referring Provider.
• Inactivate existing pre-11.2 favorites in an 11.2 environment using SSMT.

Access SSMT and Enter Database Login

1. Navigate to SSMT through Internet Explorer. Use the following URL and Replace Server Name with the web server name or IP provided by Tech or TOps: http://Server Name/TouchWorks/imps/ssmt/ssmt.asp

2. Gather the following information from the Tech or TOps:

• Clinical DB Server:
• Clinical DB: (usually 'Works')
• Clinical DB User: (contact Allscripts or your Galen resource for login credentials if needed)
• Clinical DB Password: (contact Allscripts or your Galen resource for login credentials if needed)

IDX Web FrameWork

• FW DB Server: (usually same as Clinical DB Server)
• FW DB: (usually 'IDXwf')
• FW DB User: (usually 'sa') FW DB Password: (usually same as Clinical DB Password)

3. After logging in, check the header to be sure that you are in the correct Database and Framework.

4. Select a content category from the drop down menu. The name here should match the name in the Build Workbook EXACTLY

Extract Data

5. The Show Database calls box should remain unchecked unless you are using it for troubleshooting

6. ALWAYS check show headers when extracting data

7. Extract Data by clicking on the Extract data button in the lower left hand corner of the screen

• This is what your screen will look like

8. Open Excel

9. Delete any headers existing in Excel

10. Reformat all Excel worksheets as Text BEFORE loading extracted data to Excel The Excel default is General which strips leading 0’s so if extracted data is loaded into Excel before the cells are reformatted, it strips the zeros and does not “remember” they were there so formatting the cells to text after importing the extracted data will not work. Forgetting to change the cell format to text BEFORE pasting the data into Excel will cause problems in the application a) Click in the upper left hand corner of the screen to select all cells b) Right click and select format cells c) In the Format cells Dialog box select “Text” and click on OK

11. Navigate back to SSMT

12. Click inside the large data field and use CTRL-A to SELECT ALL (including headers)

13. CTRL-C to copy

14. Navigate back to Excel

15. Put your cursor in the very first cell of the Excel spreadsheet

16. CTRL-V to paste the data into Excel (including headers)

Check all cells for #####

17. Do a find for # and reformat any column containing ##### to General

a) Select the header of the column containing ####

b) Right click and select format cells as General

The #### means the data is too large for the cell. If the data is loaded into SSMT without changing the format of these columns to General the #### will be loaded in place of the data and cause problems in the application

18. Done

Load Data

1. Select a content category from the Go Live Weekend Configuration Guide. Find the same category in the BW extractions worksheet tabs. The category names should match exactly. If they do not, extract the same category with SSMT and make sure the headers match.
2. Verify the blank Excel worksheet was reformatted to text BEFORE the extracted data was pasted in

   The Excel default is General which strips leading 0’s so if extracted data is loaded into Excel before the cells are reformatted, it strips the zeros and does not “remember” they were there so formatting the cells to text after importing the extracted data will not work. Forgetting to change the cell format to text BEFORE pasting the data into Excel will cause problems in the application.

   Check each worksheet by clicking in the upper right-hand corner to select all cells and then right-clicking to format cells. You should see either no selection highlighted (because the sheet was formatted as text and then some columns were changed to general to fix ####) or Text highlighted. There is no way to tell if cells were formatted to text before or after importing the extracted data except to check with whoever extracted it.

3. Check all cells for #####

   Do a find for # and reformat any column containing ##### to General.

   Select the header of the column containing ####.

   Right click and select format cells as general.

   (#### means that the data is too large for the cell)

4. Modify Categories with Create (Y N) Column

   If the category has a column labeled "Create (Y N)" or something similar, change all these values to Y. load the data via SSMT (see details below), ignore the errors that say “No Record Found to Update”. Change all the Create (Y N) values back to N and load again. This should return no errors.

5. During GoLive, Try to avoid moving unwanted inactive items created in test to production

   Have a discussion with the client to identify these items because some inactive items may still be wanted in test. For the unwanted inactivated items created in test, make a back-up copy of the BW then delete the unwanted items from the BW before loading to prod via SSMT.

6. Navigate back to SSMT
7. Select the content category you will be loading from the drop down menu. The name here should match the name of the Excel worksheet exactly.
8. In the SSMT Window, use CTRL-A to select all, then hit delete. This ensures that there are no empty spaces in the SSMT data window that could throw off the Data you are loading.
9. Navigate back to your excel document. In Excel, you want to select all of your Data, but not the headers and no empty column. Grap starting from cell A2, even if there is no data in that cell and drag to grab all columns with data and NO MORE THAN 700 ROWS of DATA (SSMT can only move 65K of data at a time)
10. Use Ctrl-C to copy the Data
11. Navigate back to SSMT
12. put cursor into the SSMT Data Field
13. Use CTRL-A to select all
14. Use CTRL-V to paste data from excel.
    • Note: using Ctrl-A then Ctrl- V helps ensure that you do not have any blank spaces in the data field that will distort your data.
15. Click on Load Data in lower left.

• When loading menus the Server IIS Services MUST be restarted after the load for menus to appear and full privileges must be given to 'twappadmin'.

Error Messages

The SSMT tool returns various error messages. Here is a page dedicated to SSMT Error Messages

Content Categories

Below is list of content categories used to to migrate or update data via SSMT. Select the Spreadsheet name for a more in-depth description.

• SSMT: Users / Providers - This is the spreadsheet used to load and manage user and provider accounts.
• SSMT: User Security Classifications - This is the spreadsheet used to assign security classifications to user.
• SSMT: RID - Resultable Item Dictionary - This is the spreadsheet that is used to load the result definitions from various lab vendors.
• SSMT: OID - Orderable Item Dictionary - This spreadsheet used to load Order Level items for various vendors.
• SSMT: Order Performing Facility Identifiers - This spreadsheet is used to synchronize multiple vendors.
• SSMT: OID - Order Defaults - Req Perf Location / Site - This is the spreadsheet that is used to set defaults on a Site or Requested Performing Location level. This can set various default behaviors such as charge behavior, order detail, and much, much more.
• SSMT: OID - Order Defaults - Insurance/PatientLocation/Site - This is the spreadsheet used to specify orderable behavior on the insurance, patient location, or site level. It is used to set defaults such as the Default Requested Performing Location, Requested Performing Location Picklist, Internal/External Required behavior, Referred to Vendor Org required behavior, Referred to Location Site Required behavior, and Referred to Provider Required behavior.

• SSMT: Charge Codes - This spreadsheet is used to load or edit Charge Codes within TouchWorks
• SSMT: Document Type - This is the spreadsheet used to upload or edit documents within TouchWorks

• SSMT: Favorites Orderable Items <Order Type> - Used to load/edit/copy orderable items favorites. The various Order Types (Lab, Imaging, Supplies, etc)