Trella Health Marketscape Insights (PAC)/Strategy SFTP Data Delivery User Guide

Owned by Pierre Menard
Last updated: Mar 06, 2024 by Christine Cook
5 min read

Overview 

Customers are contracted with Trella Health to receive Trella Health Marketscape data. Data will be made available quarterly to customer via SFTP, as a single zip file containing one pipe delimited text file per table. This document outlines the process of receiving data via Secure File Transfer Protocol (SFTP). 

 

Deliverable File Structure

Each quarterly update will include the following files:

  1. The zipped data file containing pipe-delimited tables

  2. A Release Notes text file, detailing updates from the previous release

  3. A file named “SCHEMA_CHANGES_[release year quarter].txt”, with pipe-delimited list of all table/column updates

  4. A file named “SCHEMA_DDL_[release year quarter].sql”; the Data Definition Language (DDL) file includes the SQL scripts to generate the release database

  5. A file named “FTP_DATA_DICTIONARY.csv”; this pipe-delimited file includes column data types, ordinal positions, and definitions (as available). Note that some definitions reference metadata variables (e.g. “[[currentmayr]]”) - the values for these variables in a given release can be referenced in COM_REF_METADATA.

Data Available

PAC SFTP Sample Data https://excelhealthgroup.sharepoint.com/tech/Shared%20Documents/Forms/AllItems.aspx?id=%2Ftech%2FShared%20Documents%2FData%20Science%20%26%20Engineering%2FAd%20hoc%20requests%2FFTP%20Samples&p=true&ga=1

-Marketscape Insights Hospice/HHA/SNF users receive a set of tables specific to their purchased lines of business intended to mirror the Insights front-end product

-Marketscape Strategy tables are delivered to Strategy customers only on request, as the data structure is more complex than the Insights set of tables

NOTE: The Marketscape Strategy deliverable only includes the Explore metrics, and a small number of reference tables (e.g. NPI, ACO, etc.) intended to join to the explore tables.

A brief overview of the Marketscape Strategy data structure is included below; this information is included in the readme & release notes provided to customers.

Measure_dictionary: attribute_ID level definitions & groupers Contains attribute names, definitions, groupers, and formatting metadata Can be joined to eav_dictionary on identifier='MEASURE_ID',name=measure_dictionary.MEASURE_ID; high-level reference for measure definitions. Can be joined against specific attribute_id values using the above join to eav_dictionary Category: Categories of objects My_Object: List of objects, joined to category; Name=NPI (or e.g. ACO ID) Attribute: Attributes for individual measures, with possible groupers/dimensions; Uniq is static, while the ID value will change per release. Joined to dictionary & object_value tables via ID=attribute_id Dictionary: Dictionary reference for objects, with possible groupers/dimensions; IDENTIFIER is the type of entry selected for a given attribute_id. Joined to Attribute via ATTRIBUTE_ID. IDENTIFIER types: Format = data formatting, i.e. P2=Percentage with two decimal places; Uniq = Maps back to the Uniq field in Attribute Measure_Id = joins eav_attribute to dictionary on attribute_id = attribute.id TAG = individual tag for a given attribute_ID (including BETA tags, although BETA tags will only be included for the most recent Strategy release) TAGS = JSON list of tags PREVIOUS_NAME = Previously used attribute name, if a given measure has been renamed (excluding rolling quarterly names) SNF, PHYS, HOS, HHA, HOSP, PHYS-GRP, ACO = flag indicating if a given attribute_id applies to the named category of TrellaType Object_Value_Phys: Value for a given Object_ID, Dictionary_Entry_ID, and Attribute_ID (where NPI.TrellaType in ('PHYS-APP','MOS-OTHP')) Measure values for active physicians/providers & physicians with attributed lives and no Medicare FFS claims during the past year Object_Value: Value for a given Object_ID, Dictionary_Entry_ID, and Attribute_ID (where NPI.TrellaType not in ('PHYS-APP','MOS-OTHP')) Measure values for all other NPI types and ACOs

 

Establishing Connectivity

SFTP is a method for obtaining files between two computers. SFTP is secure and requires a username and private key (both provided by Trella Health) to access the SFTP server.

The Trella Health SFTP server URL is located at sftp.trellahealth.com. You provide the SFTP server URL, username, and private key to a SFTP client software which will then allow you to transfer files to and from the Trella Health SFTP server.

Customer action steps: 

  • Ensure enough disk space to store the data files. For 50 states, you’ll need approximately 5GB of storage.

  • Customer database administrator (DBA) to review requirements and ensure the customer environment can support the database.

There are many free products available for download to establish an SFTP connection. Some SFTP clients are listed at bottom along with detailed instructions.


Notification of New Data Files

A notification email will be sent to the customer when new data files are available for download. A brief description of specific changes/updates will be included with each quarterly release as a changelog text file. 

SFTP Clients

Specific instructions for connecting to the SFTP server with these products are provided next.

Using Cyberduck

Use these instructions to transfer files using Cyberduck.

  1. Open the Cyberduck client.

  2. Choose Open Connection.

  3. In the Open Connection dialog box, choose a protocol: SFTP (SSH File Transfer Protocol).

  4. For Server, enter the SFTP server endpoint (i.e. sftp.trellahealth.com).

  5. For Port number, enter the following:

    • 22 for SFTP

  6. For Username, enter the username that was provided to you by Trella Health.

  7. Do not enter a password. Instead, enter the SSH private key file that was provided to you by Trella Health in the SSH Private Key field.

  8. Choose Connect.

  9. Perform your file transfer.

Using FileZilla

Use these instructions to transfer files using FileZilla.

  1. Open the FileZilla client.

  2. Choose File, and then choose Site Manager.

  3. In the Site Manager dialog box, choose New site.

  4. On the General tab, for Protocol, choose SFTP.

  5. For Host name, enter the SFTP server endpoint (i.e. sftp.trellahealth.com).

  6. For Port number, enter the following:

    • 22 for SFTP

  7. For Logon Type, choose Key file.

    For Key file, enter the SSH private key that was provided to you by Trella Health.

  8. For User, enter the username that was provided to you by Trella Health.

  9. Choose Connect.

  10. Perform your file transfer.

Using WinSCP

Use these instructions to transfer files using WinSCP.

  1. Open the WinSCP client.

  2. In the Login dialog box, for File protocol, choose SFTP.

  3. For Host name, enter the SFTP server endpoint (i.e. sftp.trellahealth.com).

  4. For Port number, enter the following:

    • 22 for SFTP

  5. For User name, enter the username that was provided to you by Trella Health.

  6. Choose Advanced to open the Advanced Site Settings dialog box. In the SSH section, choose Authentication.

  7. For Private key file, browse for and choose the SSH private key file provided to you by Trella Health.

    Note

    If WinSCP offers to convert your SSH private key to the PPK format, choose OK.

  8. Choose OK to return to the Login dialog box, and then choose Save.

  9. In the Save session as site dialog box, choose OK to complete your connection setup.

  10. In the Login dialog box, choose Tools and then choose Preferences.

  11. In the Preferences dialog box, for Transfer, choose Endurance.

    Choose Disable for the Enable transfer resume/transfer to temporary filename for option.

    Note

    If you leave this option enabled, it substantially decreases upload performance. It also can lead to failures of large file uploads.

  12. For Transfer, choose Background, clear the Use multiple connections for single transfer check box.

    Note

    If you leave it selected, this approach can cause large file uploads to fail in unpredictable ways. An example includes creating orphaned multipart uploads and silent data corruption.

  13. Perform your file transfer.

    You can use drag-and-drop methods to copy files between the target and source windows. You can use toolbar icons to upload, download, delete, edit, or modify the properties of files in WinSCP.

Note

Be sure to disable WinSCP timestamp settings before you perform file transfers. To do so, in the WinSCP Transfer settings dialog box, disable the Set permissions upload option and the Preserve timestamp common option.

Using OpenSSH

Use these instructions to transfer files using OpenSSH.

  1. On Linux or Macintosh, open a command terminal.

  2. At the prompt, enter the following command: % sftp -i transfer-key sftp_user@service_endpoint

    In the preceding command, sftp_user is the user name and transfer-key is the SSH private key, both of which are provided by Trella Health. Here, service_endpoint is the server's endpoint (i.e. sftp.trellahealth.com).

    An sftp prompt should appear.

Technical Support

Throughout this process, the Trella Health technology team is available to work with you and ensure success by emailing support@trellahealth.com.