Extract and Load via SSMT

Description

The SSMT tool is an Allscripts provided tool that allows clinics to extract and load the data in their TouchWorks system. 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 be used by a person who understands how to use it and you should be sure to follow the Allscripts documented process when utilizing the 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 progam bugs could create a mess.  Completing a backup first takes only a few minutes and can save 
    hours of time if something does happen.

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: (usually 'sa')
• Clinical DB Password:

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 right 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 you use to load and manage user and provider accounts.
• SSMT: User Security Classifications - This is the spreadsheet that allows you to assign security classifications to a user.
• SSMT: RID - Resultable Item Dictionary - This is the spreadsheet that allows you to load the result definitions from your various lab vendors.
• SSMT: OID - Orderable Item Dictionary - This spreadsheet allows you to load you Order Level items for your various vendors.
• SSMT: Order Performing Facility Identifiers - This spreadsheet is where you synchronize multiple vendors.
• SSMT: OID - Order Defaults - Req Perf Location / Site - This is the spreadsheet that allows you 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 allows you 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 where you would load or edit Charge Codes within TouchWorks
• SSMT: Document Type - This is the spreadsheet that allows you to upload or edit documents within TouchWorks