Raw Surgery Center Data

Revolutionize Your ASC Data Workflows

The most flexible and comprehensive way to access all your surgery center data.

With HST’s raw surgery center data solution you can:

Automatically load and sync your complete HST data set to your data warehouse in a fast and reliable manner.

Combine data from all of your HST centers into a single data warehouse.

Create an extract, transform, and load (ETL) pipeline to prepare your HST data, eliminating the need for pulling manual reports.

Integrate your HST data with your data from other systems for a more complete and integrated view of your business.

Access Data in Your Data Warehouse

Sync your HST data with the Data Warehouse of your choice. We support most major cloud-based data warehouses such as Snowflake, Microsoft Azure, Amazon Redshift, and more. 

HST’s raw surgery center data solution is a cloud hosted platform that sends all of your HST data to the data warehouse of your choice. This allows you to centralize your HST data with your other business system’s data, so you can get more detailed and actionable business insights.

Database Support

HST currently supports Snowflake, Microsoft Azure, Google Cloud Storage, Amazon Redshift as well as most existing database technologies. HST’s raw surgery center data solution is built on AWS Redshift. If your database does not have a native integration with AWS, scripts can likely be written to load the data into your database solution.

Get Started

When you subscribe to HST’s raw surgery center data module, HST turns on data replication between your hosted HST products and our proprietary HST Data Platform. The initial load of your data will take some time and is dependent on the size of your databases, but after the initial load, your HST data refreshes regularly. Once we have all of your data loaded and synced to the HST Data Platform, we create an S3 bucket that only your data is stored in. To ensure security, we whitelist your IP address and create secure login credentials for you.

Verifying Your Connections

Prior to connecting your data warehouse to the HST S3 bucket, it may be helpful to verify that your connection and credentials are working and set up correctly. We recommend using the AWS CLI tool to verify your connection.

To access the raw surgery center data module, HST needs the Public IP address that you will be using to connect. HST will whitelist that IP address to ensure secure access to the S3 bucket. If you do not know your IP address, you can search ‘what is my IP address’ in your browser.

Once IP whitelisting is complete, HST will then send you the S3 bucket name and security key pair in separate, encrypted emails.

To access the S3 bucket, you will either need a Linux terminal or will need to download the AWS CLI

AWS CLI available here: https://aws.amazon.com/cli/
For a list of useful commands and documentation please visit the AWS CLI reference docs here: https://awscli.amazonaws.com/v2/documentation/api/latest/reference/s3/index.html

Once you have downloaded and installed the AWS CLI, open a terminal, and run the below command line prompts in the terminal using the HST provided security key pair from step 2 above. We have included screenshots in line with the commands, your inputs and outputs should be similar to what is shown below. Please note that you do not need to include the ‘<’ and ‘>’ as part of the following commands:

aws configure set aws_access_key_id
aws configure set aws_secret_access_key
aws configure set region us-east-1

To see the contents of the S3 bucket, use the below command, if you see a list of files, you have successfully accessed the S3 bucket.

aws s3 ls s3://<bucketname>

If you’re connecting to the sales demo bucket, use the below command:

aws s3 ls s3://hst-datalake-demo/unload/fullload/2023/04/18/

5. Once successful access is determined, the folder structure is as follows:

s3://<bucketname> /unload/fullload/<year>/<month>/<day>/ 

For example, you can find the files by going through the folder structure:

aws s3 ls s3://hst-datalake-demo/unload/fullload/2023/04/18/

HST retains the past three days of data. Replace the bucket name, month, and day in the above command with a date within the past three days to access your data. Please note, we do not update the demo S3 bucket regularly, so the only data available is from the 04/18 date.

There are multiple methods for pulling information out of an S3 bucket. One method is to copy files to a local directory by using the below command in the terminal. If this is your preferred method, we would recommend creating an automated script to copy and load the data from the S3 bucket into your preferred database/data warehouse. In the below example, we copy data from the S3 bucket into a local folder ‘s3test’ inside of the Documents folder.

aws s3 cp s3://hst-datalake-demo/unload/fullload/2023/04/18/ ./ –recursive

Once the download is complete, you can verify that the documents have successfully been copied by looking in the destination directory:

The files are copied as a generic filetype and will appear as pipe delimited text file.

Connecting Without Using the AWS CLI

For customers that want a native integration between the S3 bucket and their data warehouse, many of the major data warehouse providers offer integrations that connect and load the data with minimal setup. Below are a few cultivated links that our customers have found helpful in setting up these integrations.  

In the future, we plan to allow direct connection access from a BI tool to the HST S3 bucket. For customers that wish to directly connect with their BI tool, we will be using Simba Athena

Connecting to HST from Snowflake: https://docs.snowflake.com/en/user-guide/data-load-s3-config-storage-integration
Connecting to HST from Microsoft Azure: https://learn.microsoft.com/en-us/azure/data-factory/connector-amazon-simple-storage-service?tabs=data-factory
Connecting to HST from Google Cloud Storage: https://cloud.google.com/architecture/transferring-data-from-amazon-s3-to-cloud-storage-using-vpc-service-controls-and-storage-transfer-service
Connecting to HST from AWS: Since the HST’s raw surgery center data module is built on AWS, we can share direct access or create a new read only user to access your S3 bucket.

Querying HST Data in your Data Warehouse

In your data warehouse, your HST data is available as views. There are 150+ views available to you for HST’s surgery center management solution and HST’s electronic charting solution. There may be even more tables available if your organization has additional products such as HST’s scheduling and care communication, HST’s patient texting and pre-assessments, and HST’s patient estimates. Entity Relationship Diagrams (ERD) for HST’s surgery center management solution are available to customers upon request. 

To help get you started, below are a handful of queries you can use as guidelines and starting points to tailor for your own use.

Querying HST Transaction Data

Create custom reports for all types of HST Transactions. Monitor what transaction codes are being used and where they’re being used. See what has been Posted, is Unposted, and is a Work in Progress. 

Use the below query as a starting point for pulling your transaction information.

Select
hth.TranDate
, hat.TranHeaderKey
, hth.PostDate
, hth.Status
, hth.Description
, hat.TranAmount
, hat.ApplyPayerID
, hat.AccountNumber
, hvt.VisitNumber
, hpn.LastName + ‘,’ + hpn.FirstName + ‘ ‘ + hpn.MI as “Patient Name”
, hvp.PhysicianID
, hpy.LastName + ‘,’ + hpy.FirstName + ‘ ‘ + hpy.MI as “Physician Name”
, hvt.AdmitDate
, hth.AccountingYear
, hth.AccountingPeriod
, hth.ChangeBy
from t_AccountTran as hat
join
t_TranHeader as hth
on hat.TranHeaderKey = hth.TranHeaderKey
and hat.databasename = hth.databasename
left join
t_Visit as hvt
on hat.visitkey = hvt.visitkey
and hat.databasename = hvt.databasename
left join
t_Person as hpn
on hvt.PersonKey = hpn.PersonKey
and hvt.databasename = hpn.databasename
left join
(SELECT vs.*
FROM t_VisitService as vs
WHERE vs.PrimaryProcedure = 1) as hvs
on hat.VisitKey = hvs.VisitKey
and hat.databasename = hvs.databasename
left join
(SELECT vp.*
FROM t_VisitPhysician as vp
WHERE vp.PhysicianRole = 1) as hvp
on hvs.VisitServiceKey = hvp.VisitServiceKey
and hvs.databasename = hvp.databasename
left join
t_Physician as hpy
on hvp.CenterID = hpy.CenterID
and hvp.PhysicianID = hpy.PhysicianID
and hvp.databasename = hpy.databasename
where
hvs.PrimaryProcedure = 1
order by
hth.TranDate asc

Querying HST Transaction Data

Create custom reports your Contractual and Revenue data. Understand granular financial data by Payer, Visit, Patient and multiple other dimensions. Calculate liquidation rates, aggregate Contractual Fees, and understand your writeoffs. 

Use the below query as a starting point for pulling your contractual and revenue information.

select
hpn.LastName + ‘, ‘ + hpn.FirstName + ‘ ‘ + hpn.MI
as Patient_Name
, hvt.AccountNumber
, hvt.VisitNumber
, hvi.PayerID
, hvb.BillAmount as Bill_Amount
, hvb.ContractFee as Contract_Amount
, hvb.BillAmount – hvb.ContractFee as Estimated_Contractual_Write_Off
, hvb.ContractWriteOff as Actual_Contractual_Write_Off
, hvb.BillAmount – hvb.ContractFee – hvb.ContractWriteOff
as Contractual_Variance
, hvl.BillAmount – hvl.PaymentFromPrimary – hvl.PaymentFromCopay
– hvl.PaymentFromSelfPay – hvl.Balance – hvl.ContractWriteOff
as Estimate_Write_Off_vs_Actual_Write_Off_Adj
, hvb.ContractFee as Estimated_Revenue
, (hvl.PaymentFromPrimary + hvl.PaymentFromCopay + hvl.PaymentFromSelfPay)
– hvb.ContractFee as Revenue_Variance
, hvl.Balance as Balance
from t_Visit as hvt
left join
t_Person as hpn
on hvt.PersonKey = hpn.PersonKey
and hvt.databasename = hpn.databasename
left join
t_VisitInsurance as hvi
on hvt.VisitKey = hvi.VisitKey
and hvt.databasename = hvi.databasename
left join
t_Payer as hpr
on hvi.PayerID = hpr.PayerID
and hvi.databasename = hpr.databasename
left join
t_FinancialClass as hfc
on hpr.FinancialClass = hfc.FinancialClass
and hpr.databasename = hfc.databasename
left join
t_VisitBilling as hvb
on hvi.InsuranceKey = hvb.InsuranceKey
and hvi.databasename = hvb.databasename
left join
t_VisitLedger hvl
on hvt.visitkey = hvl.visitkey
and hvt.databasename = hvl.databasename
where
hvt.AdmitDate >= ‘2023-01-01’
order by
hvt.AdmitDate asc

Start experiencing the most flexible and comprehensive way to access all your surgery center data.

LEARN MORE