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:

To move data from one database to another
To load data from a spreadsheet to a database
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

Navigate to SSMT through Internet Explorer. Use the folloing 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

When using a virtual server, search from within the virtual server and use: http://galentest/Touchworks/imps/ssmt/ssmt.asp

Bookmark this URL on the server browser (Internet Explorer)

Gather the following information from the Tech or TOps:

Clinical DB Server:

Clinical DB: (usually 'Works')

Clnical 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)

Verify the Headers and make sure that you are in the correct Database and Framework.

Select a content category from the drop down menu. The name here should match the name in the Build Workbook (BW)

Extract Data

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

ALWAYS check show headers when extracting data

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

keep this window open and open Excel.

WARNING BRFORE pasting any information into Excel:

All Excel worksheets need to be reformatted 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.

click in the upper right hand corner of the screen to select all cells

right click and select format cells

In the Format cells Dialog box select “Text” and click on OK

Keeping Excel open, Navigate back to SSMT.
Click inside the large data field and use CTRL-A to SELECT ALL (do not click and drag to select all because you could miss something.
use CTRL-C to copy

Navigate back to Excel.
Put your cursor in the very first cell of the Excel spreadsheet
Use CTRL-V to paste the data into Excel.

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.

1. 1. 1. means that 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)

Load Data

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.

All Excel worksheets need to be reformatted 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. 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.

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)

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.

Navigate back to SSMT

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.

Uncheck 'Show Headers in Extracted Data?'

Navigate back to your excel document. In Excel, you want to select all of your Data, but not the headers and no empty column. Go to Column A and grab 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)

Use Ctrl-C to copy the Data
Navigate back to SSMT
put cursor into the SSMT Data Field
Use CTRL-A to select all
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.

click on Load Data

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