Instructions for requesting a batch of data from DSM
This article describes how to generate the requestor file for the DSM Lab Extract. The file is stored on a file share that DSM can access. See Instructions for importing a batch of DSM Data for the related import process.
How frequently to request
We will request quarterly, or as needed, keeping in mind that we don't want to generate too much workload.
Initially our DSM contact had concerns about workload and wanted a lower frequency, but then he said "Send on the ‘requestor’ file as often as you want to see the output and I will make the time." (as per email from Alun Carter, Monday, February 06, 2017 10:50)
Instruction/steps for requesting data
- copy files to local, they are large and won't work well on a share.
- make a new local directory
- Put the following into the directory
- make sure you have data where the NDC's have been completed. You should copy PREVIOUS days backup of both the centralized and PHIA data files.
- Centralized_data.mdb -the last one has been fully checked for links
- PHI.mdb
- DSM_Labs_Consistency_check.accdb
- DSM Labs data.accdb
- make sure you have data where the NDC's have been completed. You should copy PREVIOUS days backup of both the centralized and PHIA data files.
- open DSM Labs Consistency check.accdb
- click the "Export requestor file" button
- If you get the error message 58- there is already file in the folder that you need to delete.
- follow the wizard, which will export a file to the shared folder and start an email to DSM
- close Access and delete files you had copied
- enter at the top of the log below when you requested data, and for what range
- check the number requestors.xls if the same as the number you requested by using the send date time.
missing dispo dates
if at import time a dispo dttm is still missing it will be replaced with Now().
Dependencies
The request process asks for all, and the import process de-duplicates, so there should be no gaps or overlaps.
Requesting Lab DSM
- Need to send monthly or quarterly
- A complete (combined) file in addition to split files given - start date Oct 14, 2020 batch request.
- After importing the split files to DSM_Lab_accdb, the split files are deleted saving only the combined file .
Log
Put newest entry at the top
| Request Date | No_Records | Unmatched D_ID with DSM dump | SendDtTm range start | SendDtTm range end | Date Imported to Database | Comments | ||
|---|---|---|---|---|---|---|---|---|
| 2024 Aug 23 | 1603 | 2024 Jul 12 | 2024 Aug 23 | |||||
| 2024 Jul 12 | 1918 | 64 (3.3%) | 2024 May 30 | 2024 Jul 12 | 2024 Jul 19 | All vetted. Upload excel file from SAS. | ||
| 2024 May 30 | 1668 | 38 (2.3%) | 2024 Apr 19 | 2024 May 30 | 2024 Jun 20 | All vetted. Upload excel file from SAS. | ||
| 2024 Apr 19 | 3056 | 105 (3.4%) | 2024 Feb 9 | 2024 Apr 19 | 2024 02 27 | Vetted = 3022, Incomplete = 34. Export in ACCESS still not working. Upload excel file from SAS. | ||
| 2024 Mar 11 | 1300 | 2024 Feb 09 | 2024 Mar 11 | Export failed resent with Apr 19/24 batch. | ||||
| 2024 Feb 09 | 1581 | 49 (3.09%) | 2024 Jan 09 | 2024 Feb 09 | 2024 Feb 27 | All Vetted | ||
| 2024 Jan 09 | 1906 | 54 (2.83% ) | 2023 Nov 28 | 2024 Jan 09 | 2024 Jan 22 | All Vetted | ||
| 2023 Nov 28 | 1422 | 40 (2.81%) | 2023 Oct 25 | 2023 Nov 28 | 2023 Dec 6 | All Vetted | ||
| 2023 Oct 25 | 1598 | 47 (2.94%) | 2023 Sep 22 | 2023 Oct 25 | 2023 10 30 | Vetted = 1573, Inc = 25. The Incompletes have dispo date before or on Sept 30,2023 to be included in the July to Sept quarter report. | ||
| 2023 Sep 22 | 1341 | 40 (2.98%) | 2023 Aug 22 | 2023 Sep 22 | 2023 Oct 10 | All Vetted | ||
| 2023 Aug 22 | 1729 | 50 (2.89%) | 2023 Jul 12 | 2023 Aug 22 | 2023 Aug 28 | Contain Vetted =1713, Inc = 16. The Incompletes have dispo date before or on June 30,2023 to be included in the April to June quarter report. The new DSM_Labs_data starts on this batch. | ||
| 2023-Jul 12 | 1532 | 34 (2%) | 2023 Jun 07 | 2023 Jul 12 | 2023 Jul 26 | All Vetted. Tina split the DSM_Labs_data and relabeled it as DSM_LABS-data-pre_2023-08-27. This batch is the last entry to pre-2023-08-27. | ||
| 2023 Jun 07 | 1961 | 88 (4.49%) | 2023 Apr 25 | 2023 Jun 07 | 2023 Jun 12 | All Vetted | ||
| 2023 May 12 | 795 | 94 (11%) | 2023 May 15 | from batch 4/25/2023 - Resent 795 found unmatched and no Lab or Blanks. . | ||||
| 2023 Apr 25 | 2495 | 799 (32%) | 2023 Mar 1 | 2023 Apr 25 | 2023 May 10 | All Vetted | ||
| 2023 Mar 01 | 2634 | 126 (4.78%) | 2023- Jan 10 | 2023 Mar 1 | 2023 Apr 25 | All Vetted | ||
| 2023 Jan 10 | 2122 | 103 (4.85%) | 2022 Nov 23 | 2023 Jan 10 | 2023 Feb 2 | All Vetted | ||
| 2022 Nov 23 | 2591 | 107 (4.12%) | 2022 Oct 5 | 2022 Nov 23 | 2022 Dec 5 | All Vetted | ||
| 2022 Oct 5 | 1537 | 65 (4.22%) | 2022 Aug 31 | 2022 Oct 05 | 2022 Nov 11 | All Vetted | ||
| 2022 Aug 31 | 4176 | 181 (4.33%) | 2022 Jun 01 | 2022 Aug 31 | 2022 Sept 15 | All Vetted
Import 247783 | ||
| 2022 Jun 01 | 7337 | 354 (4.82%) | 2021 Dec 31 | 2022 Jun 01 | 2022 Jun 10 | Sent Vetted only. PT Import - 2975 Blanks (41%).
REDO: New Import 384033 - New import date 2022 Sep 14 Old Import 191606 entries. | ||
| 2022 Jan 03 | 3970 | 70 (1.8%) | 2021 Oct 13 | 2021 Dec 31 | 2022 Jun 10 | PT Sent only Vetted profiles. (JM using SAS - 95 have matched D_ID but no corresponding Lab dates.) PT import - Blanks 2522 (63%).
REDO: New Import 187808 - New import date 2022 Sep 13 Old Import 20041 entries. | ||
| 2021 Oct 13 | 3984 | 64 (1.6%) | 2021 July 20 | 2021 Oct 13 | 2022 Jun 09 | PT Sent only Vetted profiles. (JM using SAS - 6 D_IDs at requestor were re-labeled to match the L_LOG; 104 have matched D_ID but no corresponding Lab Dates.) PT import - Blanks 1275 (32%).
REDO: New Import 95765 - New import date 2022 Sep 13 Old Import 108800 entries. | ||
| 2021 July 20 | 8015 | 223 (2.78%) | 2021 Apr 15 | 2021 July 20 | 2021 Aug 12 | Included Incomplete = 3164 and Vetted = 4851
REDO: New Import 264054 - New import date 2022 Sep 09 Old Import 135903 entries. | ||
| 2021 Apr 15 | 6289 | 369 (5.86%) | 2021 Jan 19 | 2021 Apr 15 | 2021-June 09 | Included Incomplete=3163 and Vetted=3126.
REDO: New Import 212257 - New import date 2022 Sep 08 Old Import 70310 entries. | ||
| 2021 Jan 19 | 4167 | 118 ( 2.83% ) | 2020-Nov-27 | 2021-Jan-19 | 2021-Jan-27 | Included Incomplete=2076 and Vetted=2091. The following 5 Incompletes (H3_HSC_H4-19723, H3_HSC_H4-19777,H3_HSC_H4-19790, G8_GRA_N3-19794,G8_GRA_N3-19968) are not included on this sent because of D_ID changes.
REDO: New Import 196891 - New Import Date 2022 Sep 08 Old Import 47357 entries. | ||
| 2020 Dec 03 | 2580 | 170 (6.58%) | 2020 Sep 30 | 2020 Nov 27 | 2021 Jan 27 |
Started the New DSM access program to this batch on wards. REDO: New Import 99550 - New Import Date: 2022 Sep 08 Old Import resulted to 55544 entries. | ||
| 2020 Oct 14 | 1836 | 34 (1.8%) | 2020-Aug-31 | 2020-Sep-30 | 2020-Oct-29 | JM did this Oct2020: unmatched 34 (1.8%) | ||
| 2020 Aug 31 | 3589 | 237 (6.6%) | 2020-Jul-15 | 2020-Aug-31 | 2020-Sep-30 | JM re-do Oct2020: unmatched 237 (6.6%) | ||
| 2020 Jul 15 | 6848 | 1345 (19%) | 2020-Apr-21 | 2020-Jul-15 | 2020-Aug-14 | Added 793(12%) unmatched from batch 21April2020 making the total=6848; 1189 out 6848 are incomplete (398 with no dispo date and 791 with dispo date); JM re-do Oct2020: unmatched 681 (9.9%) | ||
| 2020 Apr 21 | 6269 | 766 (12%) | 2020-Jan-27 | 2020-Apr-21 | 2020-May15 | JM re-do Oct2020: unmatched 393 (6.3%) | ||
| 2020 Jan 27 | 5567 | 413 (7%) | 2019-Nov-20 | 2020-Jan-27 | 2020-Feb-10 | JM re-do Oct2020: unmatched 516 (9.3%) | ||
| 2019 Nov 21 | 7350 | 417 (5%) | 2019-Jul-31 | 2019-Nov-20 | 2019-Dec-18 | JM re-do Oct2020: unmatched 557 (7.6%) | ||
| 2019 Jul 31 | 5738 | 146 (2.5%) | 2019-May-13 | 2019-Jul-31 am | 2019-Oct-24 | JM re-do Oct2020: unmatched 410 (7.8%) | ||
| 2019 May 13 | 3583 | 491 (13%) | 2019-Mar-27 | 2019-May-13 am | 2019-May-29 | JM re-do Oct2020: unmatched 239 (6.7%) | ||
| 2019 Mar 28 | 4198 | 249 (6%) | 2019-Jan-23 | 2019-Mar-27 am | 2019-Apr-10 | JM re-do Oct2020: unmatched 320 (7.6%) | ||
| 2019 Jan 23 | 4521 | 1st- 1220 (27%), 2nd- 334 (7%) | 2018-Oct-31 | 2019-Jan-23 am | 1st- 2019-Mar-9; 2nd- 2019-Jun-14 | 1612 records of HSC have no lab data and re-send June 6, 2019; JM re-do Oct2020: unmatched 330 (7.8%) | ||
| 2018 Oct 31 | 5873 | 508 (9%) | 2018-Jul-11 | 2018-Oct-31 am | 2019-Mar-9 | |||
| 2018 Jul 11 | 1716 | 106 (6%) | 2018-Jun-19 | 2018-Jul-11 am | 2019-Mar-9 | |||
| 2018 Jun 19 | 4212 | 378 (9%) | 2018-Apr-05 | 2018-Jun-19 am | 2019-Mar-10 | |||
| 2018 Apr 5 | 4532 | 359 (8%) | 2018-Jan-25 | 2018-Apr-05 am | 2018-May-18 | |||
| 2018 Jan 25 | 5710 | 448 (8%) | 2017-Oct-26 | 2018-Jan-25 am | 2018-Apr-9 | |||
| 2017 Oct 26 | 16590 | 1246 (8%) | 2016-Dec-31 | 2017-Oct-26 am | 2018-Apr-9 | |||
| 2017-Mar-14 | 6078 | 559 (9%) | 2016-Aug-30 | 2016-Dec-31 am | 2017-Apr-12 ? |
Early requests that followed a different pattern:
- 16:05, 2016 October 17 (CDT) || 2016-08-01 (but use 1980-01-01 for import because first one) until 2016-08-30
- 2015-12-?? - Discharge date 2015-04-01 - 2015-04-30