ETL_004: Preparation of Monthly Membership Renewal files for Mosaic and Go Inspire

1.0 Introduction

This document outlines the steps for CIS Support Team members in using the automated solution (ETL_004) to process the monthly membership renewal files for Mosaic and Go Inspire.

The solution automates various tasks associated with these files, ensuring they're ready for the fulfilment stage, including key steps to process Go Inspire renewal files as follows:

1. Go Inspire SM, SMF, SMC membership renewal files

DIABETES_CONNECTION

Based on the connection to diabetes and whether we hold a Permission to store sensitive data activity record on CIS that is Current and equal to Yes - (Active).

SENSIT

If we hold a Permission to store sensitive data activity record on CIS that is Current and equal to Yes - (Active).

2. Go Inspire SM membership renewal files split

Split the DD and Non-DD Go Inspire SM membership renewal files into emailable and non-emailable segmented files.

This is based on:

Whether we hold a Permission to Email activity record on CIS that is Current and equal to Yes - (Active).
Members for whom we do not hold a positive Permission to Email activity record (or who have no email address in the renewal file).

2.0 Quick Start Guide

This section provides a brief overview of the steps required to work with the automated solution.

Steps:

Final Data Stage: All necessary pre-processing has been completed, with any issues addressed. Renewal files should be in the Final Data location for the current renewal month.
Run the SQL job: Execute the SQL Agent Job job_ETL_004_MembershipRenewalFiles to activate the PS script.
Review the outputs: Review the counts in the summary report and check log entries for any errors, ensuring the new segments are correctly processed.
Collect files: Gather the files and move them forward for the next stage of the renewal process.

3.0 Overview

A control script manages the workflow and oversees file movement. The solution ensures that:

All files found in the Final Data folder are logged and renamed by omitting the OP031- prefix.
Specific files for Mosaic (SM, SMC, and SMF renewals) are copied and renamed by omitting the GoInspire prefix.
The GoInspire Non-DD Renewal files are all appended with Diabetes Connection and Sensitive Data permissions details.
The GoInspire Non-DD Renewal SM file is split into email and non-email renewal segments.
The GoInspire Renewal SM files are split into email and non-email segments.

4.0 Requirements

The solution expects to receive CSV files generated by the operational job OP031:SM Create Monthly RRL Output - Step 3.

The solution expects eight renewal files as follows:

OP031-GoInspire DD Renewals PM - yyyy-mm-dd
OP031-GoInspire DD Renewals SM - yyyy-mm-dd
OP031-GoInspire DD Renewals SMC - yyyy-mm-dd
OP031-GoInspire DD Renewals SMF - yyyy-mm-dd
OP031-GoInspire Non-DD Renewals PM - yyyy-mm-dd
OP031-GoInspire Non-DD Renewals SM - yyyy-mm-dd
OP031-GoInspire Non-DD Renewals SMC - yyyy-mm-dd
OP031-GoInspire Non-DD Renewals SMF - yyyy-mm-dd

The solution assumes that all necessary pre-processing steps have been carried out, all issues have been resolved, and the renewal files are in the Final Data folder for the current renewal month.

5.0 Processing Steps

A high-level overview of the workflow is shown below.

5.1 Calling the PowerShell Script

To initiate the automated solution, a SQL Agent job is used. This job is situated on the gs1dukmsqlv03 server and is called job_ETL_004_MembershipRenewalFiles.

It calls the control script named ETL_004_Control.ps1.

Note: The PS scripts are stored here: \\gs1dukmsqlv03\Powershell\ETL\Tasks\ETL_004_MembershipRenewalsPreProcessing-Segment.

Currently, the job is not scheduled and must be run on demand. In the future, the job will be scheduled to run automatically to search for files ready for processing.

If needed, see solution article on how to execute a SQL Agent manually.

5.2 Review the Report and Log

Upon the script's completion, two primary outputs provide insights into the processing actions: an emailed summary report and a PS log.

Summary Email:

Upon completion, you will receive a summary report via email that details the actions taken on the files, including the new segmentation. This report should be used to compare against renewal pre-counts to ensure all is as expected.

PS Log:

In addition to the email report, a log is written capturing each processing step, especially highlighting any error messages or issues encountered. Review the log entries for any error messages. The log file is attached to the summary email and can also be found in the Processed folder within the Final Data folder.

5.3 Processed Files

Once the pre-processing is complete, all files in the Final Data folder can advance to the next stage of the renewal process. The transformed raw input files are moved to a Processed folder, with their names prepended with processed. These files are retained for reference only.

The screenshot below shows objects within the Final Data folder when pre-processing is complete.

Highlighted items include:

The files replicated for Mosaic.
The output from the split of the GoInspire Non-DD Renewals SM file:
- Members in the GoInspire Non-DD Renewals (email) SM file will be sent an invitation to renew online.
- Members who remain in the GoInspire Non-DD Renewals SM file will go through the standard renewal process.
The output from the split of the GoInspire DD Renewals SM file:
- Members in the GoInspire DD Renewals (email) SM file have an active email opt-in.
- Members who remain in the GoInspire DD Renewals SM file are those without an active email opt-in, or email address present in the renewal file.

6.0 Troubleshooting

If you encounter any issues, refer to the troubleshooting steps below:

Check the log file for error messages and try to resolve any problems found.
Check that the input files, including the new segmented files, are in the Final Data folder for the current renewal month and are in the correct format.
Ensure that the SQL Agent Job has been executed.
Verify that the processed files are being written to the correct folder and are in a valid format.

Contact Michael Geelan for further assistance if issues persist. Be sure to include any error messages received and details on the steps taken to diagnose and resolve the issue.

7.0 Document Properties

Property Value Version
0.13 Created
2023-08-18 Last updated 2024-06-09 Author Michael Geelan