Back to Home

Drop Sampling

Overview

What is Drop Sampling?

Drop sampling is the process we use to bulk-load intake forms into one of our software products from a .csv file. In the past this was done manually, but now it is mostly automated.

Back to top

Environment Setup

This section provides essential information for configuring the environment on your Cigna machine for the QUAD drop sample process.

  1. Ensure that the smart.p11_CodeList LookupValue for the ImportDropSample LookupCategory is set how you want. (It can be updated in the Setup_Codelist web page). If set to True every step of the job will be executed and drop samples that pass all validation will be imported into QUAD. If set to False only part of the SSIS job will run and drop samples will not be imported into QUAD. The latter option is useful for pre-checking the csv for errors, then taking care of them before anything is loaded into QUAD.
  2. If you want email notifications sent on fallout and success, ensure that the smart.p11_CodeList LookupValue for the SendDropSampleEmail LookupCategory is also set to True, and that Option1 contains the email address of the required recipient. (This can also be updated in the Setup_Codelist web page). Note that if more than one recipient is needed, multiple values will have to be inserted into the smart.p11_CodeList under this LookupCategory, one for each email address.
  3. Ensure that you are using "|" as your list separator. To add this as your list separator go to Control Panel. Click the link under "Clock and Region" called "Change date, time, or number formats". Click "Additional settings..." and update "List separator" to be "|".

Back to top

Main Steps

This section outlines steps you can take to mimic those taken by the business if you need to troubleshoot an issue or add a feature to the pipeline. For more information you can view the user guide that was written expressly for the business.

  1. In the file explorer of your Cigna machine, navigate to \\Wdcapp172\sobi_shared_folders\SAM3\CEM_UserData\DropSample.
  2. Locate the file entitled QUADDropSampleInputTemplate.xlsm. Copy it, paste it in a location on your local machine outside of the shared drive, and give it a unique name.
  3. Locate the file entitled QUADDropSampleFinalizedInput.csv. Copy it and paste it in a location on your local machine outside of the shared drive. It will probably be helpful to put it in the same folder in which you placed the copy of QUADDropSampleInputTemplate.xlsm.
  4. Open your local copy of QUADDropSampleInputTemplate.xlsm. If you get a message in the top banner saying "SECURITY WARING Macros have been disabled" click "Enable Content". If a popup appears asking whether to make this a trusted document, select "No".
  5. Navigate to the "InputData" tab on the bottom left of the spreadsheet. The "FinalData" tab will be open by default. Never make changes in the "FinalData" tab, as its purpose is to clean and trim data from the "InputData" tab. Hence making changes directly in the "FinalData" tab can result in dirty data that can potentially cause the whole job to fail.

  6. Fill out the spreadsheet in the "InputData" tab. Note that AccountNo, BONTID, Sample, SampleGroup, and SampleRouting are required fields. Without these fields, the sample on that line will end up in the fallout table. Also note that due to the data-cleaning process, copying and pasting large amounts of data into this file may take a few minutes.

    IMPORTANT NOTE: Never delete cells! If you need to remove the data in one or more cells, select them, right click, and select "Clear Contents".

  7. Navigate to the "FinalData" tab and copy all data besides the headers.

  8. Open your local copy of QUADDropSampleFinalizedInput.csv. Scroll to the first empty cell (it will be A2). Select it, right click, and hover over the "Paste Special" option in the context menu. Click "Values & Number Formatting". Save and close the file.
  9. For the dev and staging environments request the appropriate BMRS job for the environment. (For more information see BMRS documentation). This triggers the QUAD_DropSample SSIS package to run. For the production environment only, drag and drop your local version of QUADDropSampleFinalizedInput.csv to the following location: \\Wdcapp172\sobi_shared_folders\SAM3\CEM_UserData\DropSample. You will get a message asking whether you wish to replace the existing file in the destination. Click "Yes". The drop sample job will run automatically. Do not use the production environment unless you have been specifically told to do so!

Back to top

Backdating Drop Samples

"Backdating" describes the process for loading intakes as if they were created in the past. In order to backdate an intake, set the DateReceived field to the date you require. If the DateReceived field is the date the drop sample is loaded or is left blank the sample in question will not be backdated.

ProClaim Drop Samples

Rather than creating two entries in QUADDropSampleFinalizedInput.csv for ProClaim samples with both document types, create one entry with the document type "Both". This will ultimately result in the creation of two separate intakes, eliminating most of the manual work that was previously required.

Back to top

Troubleshooting Errors

Intakes that are not sampled or that have any data that is missing or improperly formatted will end up in the fallout table. After a QUAD drop sample job is finished, the presence of any data in this table triggers an email notification to be sent to the parties set up in the email list. It also triggers the creation of a csv file containing data from the fallout table at \\Wdcapp172\sobi_shared_folders\SAM3\CEM_UserData\DropSample\Fallout\QUADDropSampleFallout_<date-of-job>.csv. To diagnose the reason for fallout, do the following:

  1. Open this file.
  2. Open your local copy of QUADDropSampleFinalizedInput.csv and delete all existing data besides the header. Save and close the file.
  3. Repeat steps 1, 2, and 4 of the Main Steps.

  4. Copy the data from QUADDropSampleFallout_<date-of-job>.csv to the "InputData" tab of your new local copy of QUADDropSampleInputTemplate.xlsm.

    IMPORTANT NOTES:

    • Do not directly modify your original local copy of QUADDropSampleInputTemplate.xlsm. Running this file with original data back through the pipeline will result in duplicate samples being loaded into QUAD (i.e., those that didn't fail to begin with). You must copy all data from QUADDropSampleFallout_<date-of-job>.csv to a new local file and only run data from this new file back through the pipeline.
    • Similarly, do not directly modify the fallout data file, as it is good to have a record of what failed, when, and why.

  5. Scroll to the right-most (notes) column of your new local copy of QUADDropSampleInputTemplate.xlsm. This column will indicate the data field or fields that caused the fallout for that particular sample. All errors for a sample will be located in the notes column and will be semi-colon delimited. Fix the fields that caused the failure.

  6. Repeat steps 7 through 9 of the Main Steps.

This process can be repeated as many times as necessary until all samples are loaded into QUAD.

Back to top

Archive

A copy of all data in QUADDropSampleFinalizedInput.csv is made and saved to \\Wdcapp172\sobi_shared_folders\SAM3\CEM_UserData\DropSample\Archive every time a drop sample job is run. The file name will contain the date and time the job was run for future reference.

Back to top

Relevant Files

SQL

Tables

Stored Procedures

SSIS

Spreadsheets

Back to top

See Also

The user guide given to the business can be found here.

Back to top