SID Semantic Layer Control Tools

This project contains tools used to maintain the SID semantic layer.

• Deduplication: the sl-deduplicate-and-merge tool takes raw data from the SID reporting schema tables, de-duplicates it and merges it into an equivalent table within the Semantic Layer sl schema.
• Patient Conditions: the sl-process-conditions tool is used to generate the Semantic Layer patient conditions tables.
• Maintenance: the sl-manage tool allows general maintenance tasks to be carried out on the Semantic Layer such as creating or dropping table indexes.

Deduplication

Patient Conditions

Patient condition processing is carried out using the sl-process-conditions tool.

The tools are split into 5 different stages.

1. Stage 1 - processes SID reporting data from tables such as EDSUS and encounters into the sl.patient_all_codes table.
2. Stage 2 - reduces the sl.patient_all_codes table to the sl.patient_all_codes_distinct - a summary of the number of distinct patient records for a given code.
3. Stage 3 - for conditions in each codeset (CCS, Elixhauser, etc) generates codeset condition tables with an entry for each patient/condition combination.

The normal pattern of usage for new data would be, assuming 100 new raw data files have been received:

| Stage | Command | Description | | ------| ------- | ----------- | | 1 | > sidslctl stage1 createjobs --rawDataIds 32005-32104 | Creates 100 jobs in sl.control_job, one to process each raw data file. | | 1 | > sidslctl stage1 listjobs --jobIds 290-291 | Lists stage1 jobs for the given job ids. | | 1 | > sidslctl stage1 runjobs --jobIds 290-291 | Runs any outstanding stage1 jobs and update sl.control_processing_status accordingly. | | | | | 2 | > sidslctl stage2 createjobs --rawDataIds 32005-32104 | Creates a single job to reduce records in sl.patient_all_codes for raw data ids between 32005 and 32104 to distinct records for each patient/code combination. | | 2 | > sidslctl stage2 listjobs | Lists any unrun stage2 jobs. | | 2 | > sidslctl stage2 runjobs | Runs any outstanding stage2 jobs and updates sl.control_processing_status accordingly. | | | | | 3 | > sidslctl stage3 createjob -m '2023-01-01' | Creates a single job for each codeset to run stage 3 processing for the given milestone. Normally jobs will be created for annual milestones that map to those used by demographic milestone processing. | | 3 | > sidslctl stage3 runjobs | Runs any outstanding stage3 jobs and updates sl.control_processing_status accordingly. |

The Tools

Configuration

The SID sematic layer tools needs to connect to the SID database. Use this option to configure your SID database credentials that will be stored securely in your account. You must configure your credentials before the other tools will work.

Set your database configuration:

sidslctl configuration set

It will ask you to enter your database credentials and will store your password securely in the OS keychain or equivalent. Normally you should enter credentials for the live SID reporting database SID_REPORTING.

Show your database configuration:

sidslctl configuration show

Clear your database configuration:

sidslctl configuration clean

Stage 1

Stage 2

Stage 3

Development

git clone https://github.com/Sussex-OCC/sid-sl-control.git
cd sid-sl-control
npm i
npm run dev -- configuration show