King County Medicaid eligibility and claims data

This README describes the structure and content of Medicaid eligibility and claims data that King County government routinely receives from the WA State Health Care Authority (HCA), as well as an R package King County has developed to facilitate analysis and dissemination of these data.

Available data tables on SQL Server

Currently King County receives quarterly Medicaid eligibility and claims data files (i.e. ProviderOne data) from HCA and loads these into SQL. Moving forward under a new Master Data Sharing Agreement (DSA), King County will begin to receive monthly files which consist of a rolling 12-month refresh of eligibility and claims data. These monthly files will be loaded to SQL Server through an update process – old records will be replaced with new records where duplicates exist, and new records without old duplicates will simply be appended.

King County analysts transform raw eligibility and claims data to create an array of analytic-ready tables that can be used to flexibly compute people and event-based statistics over time, such as the count of Emergency Department visits by Medicaid member race/ethnicity.

For more information on data tables available on King County's SQL Servers, users can review the purpose and structure of each table and a data dictionary here.

An ever-growing group of King County analysts meet every three weeks to discuss their shared experiences using Medicaid claims data. Users can view ongoing agenda items and point people for specific topics.

Claims R package for rapid data analysis

King County analysts developed the claims R package to facilitate querying and analyzing the aforementioned analytic-ready eligibility and claims data tables.

Instructions for installing the claims package:

• Make sure devtools is installed (install.packages("devtools")).
• Type devtools::install_github("PHSKC-APDE/claims_data")

Instructions for updating the claims package:

• Simply reinstall the package by typing devtools::install_github("PHSKC-APDE/claims_data")

Current functionality of the claims package (v 0.1.11):

• Request an eligibility and demographics-based Medicaid member cohort
• Request a claims summary (e.g. ED visits, avoidable ED, behavioral health hospital stays) for a member cohort
• Request coverage group information (e.g. persons with disabilities) and automatically join to a specified data frame
• Request chronic health condition (e.g. asthma) information and automatically join to a specified data frame
• Tabulate counts by fixed and looped by variables (i.e. data aggregation), with automatic suppression and other features
• Calculate the top N causes of ED visits and hospitalizations

Training resources:

• R users can view a training video for how to use the claims package.
• Users can also view the R script used in the training video.

ETL Folder Access and Credentials Requirements for Claims and Housing Projects

Folder Access:

• \\kcitsqlutpdbh51\importdata\data - zip/csv files
  • KC_Claim
  • KC_Elig
• \\dchs-shares01\DCHSDATA\DCHSPHClaimsData - Geocoding/clean address data
• \\phdata01\DROF_DATA\DOH DATA\Housing - Public housing data
  • Organized_data
  • KCHA
  • SHA
  • Geocoding

SQL Database Access:

• KCITSQLPRPENT40
  • KCIT SQL Server (local)
  • Windows Authentication
  • ODBC - User DSN - SQL Server - /w Windows NT authentication - PHClaims
  • KCITSQLUTPDBH51
  • KCIT SQL Server (local)
  • Windows Authentication
  • ODBC - User DSN - SQL Server - /w Windows NT authentication - PH_APDEStore51
  • kcitazrhpasqldev20.database.windows.net
  • Azure SQL Server (cloud)
  • Azure Active Directory - Universal with MFA
  • kcitazrhpasqlprp16.azds.kingcounty.gov
  • Azure SQL Server (cloud)
  • Azure Active Directory - Universal with MFA

R Keyrings:

• hca_sftp - Access to HCA SFTP file portal
• here - Sign up for HERE freemium API (https://developer.here.com/) - App ID and API Key
• hhsaw - Access to Azure SQL Servers, will require to update as you update your KC and PH domain passwords
• inthealth_edw - Storage Account Key used with COPY INTO function for Azure SQL tables (acquire from KCIT)
• adl_tenant - Azure Data Lake Tenant ID for uploading new files to the datalake (username is either "dev" or "prod")
• adl_app - Azure Data Lake App ID for uploading new files to the datalake (username is either "dev" or "prod")

Azure Data Lake Folder Access:

• https://inthealthdtalakegen2.dfs.core.windows.net/inthealth/claims/mcaid
  • /claims
  • /incr
  • /init
  • /elig
  • /incr
  • /init