The document is structured with valuable information presented in the following manner:

• FormID/MenuGroup/MenuOption Information 
• CSV Integrations 
  • IntegrationID = 198: HMIS SPM 2024 
  • IntegrationID = 201: HMIS SPM 2024 DQ Detail Export 
  • IntegrationID = 200: HMIS SPM 2024 Summary Export 
• Appendix A

FormID/MenuGroup/MenuOption Information

The 2024 SPM Export Process is run through Report Launch Form (FormID 5107).  This Form is a part of HMIS Reports (Menu Group). This Menu Group is located at the Report workspace for all the Baseline HMIS * Workgroups. One such instance of finding this Form to run the export is:

HMIS: Core* (Workgroup) -> Reports (Workspace) -> HMIS REPORTS (Menu Group) -> System Performance Measure (FY24) 

CSV Integrations

SPM Export Process comprises of three Integrations, each having its own set of Stored Procedures and Parameters. The three integrations are:

1. IntegrationID = 198: HMIS SPM 2024 Export

It’s the First integration that runs when the Export is initiated from the Report Launch Form. Following is the list of Stored Procedures it runs in the order they are mentioned. Referenced from table dbo.CSVStoredProcedures and the query used to extract the results below is:

 SELECT* FROM dbo.CSVStoredProcedures WHERE IntegrationID IN (198) ORDER BY IntegrationID,  ExecutionOrder;

ProcID: Shows the ID of the Stored Procedure; Primary Key for the table dbo.CSVStoredProcedures.

IntegrationID: Shows the linked Integration to the ProcID. The IntegrationID is the Primary Key for the table dbo.CSVIntegration and this table stores the Integration setup information.

ExecutionOrder: This tells you about the order in which the Integration runs the Stored Procedures and it runs in the ascending order. 

StoredProcName: Name of the Stored Procedure. Please note the schema for all these Stored Procedures is ‘report’.

The Stored Procedures named ‘HMIS_2024_SysPerfMeasure*’ are responsible for the exporting of the Measure details it’s named after. 

The Stored Procedure named SPM_Global_Setup insert the report parameters into the table report.SPMRun and the values inserted for the specific **TaskID  are used to extract the correct values from the result storing tables (Table names mentioned under the column ‘ResultStorageTable’) during the last step of the SPM Export Process. 

The Stored Procedure SPMCreateExportTask is responsible for using the parameters from the current running Task and Insert a record into the table dbo.AsynchronousTask which will run the next IntegrationID required in the Export Process. In this case, after running the IntgrationID = 198, this procedure is responsible for inserting a record in the table dbo.AsynchronousTask to run the IntegrationID = 201 (HMIS SPM 2024 DQ Detail Export). This procedure runs as the last step of the IntegrationID and hence it’s ExecutionOrder is 99. 

**TaskID is generated when an export task is started from the Report Launch Form, and the concerned tables are : dbo.AsynchronousTask (when a export is initiated, a record in first inserted into this table, first place that generates the TaskID) Inserting a record into this. 

Following Table shows the Stored Procedures, the CSV file being generated as the part of the export and the resulting tables. Referenced table dbo.CSVStoredProcedures and the query used to extract the results below is:

 SELECT * FROM dbo.CSVStoredProcedures WHERE IntegrationID IN (198);

StoredProcName: Name of the Stored Procedure. Please note the schema for all these Stored Procedures is ‘report’.

CSVFileName: The name of the CSV File storing the data from running the respective Stored Procedure and will be available to view the results as the part of the Export Zip folder from running this integration. Please Note that CSV Files ExportSetup and ExportTaskCreated will be blank files because it’s part of the process and has nothing to do with the SPM output. 

ResultStorageSchema: This represents the schema of the resulting tables used to store the output of the respective Stored Procedure. 

ResultStorageTable: This represents the table name of the resulting tables used to store the output of the respective Stored Procedure. Please note that the Stored Procedures SPM_Global_Setup and SPMCreateExportTask doesn’t have a resulting table to store the results because first one is responsible for storing the report parameters and later one is responsible for executing the next CSV Integration so no output is required to be exported in CSV. 

Once this Integration is successfully ran, the Export file will be generated and named as “hmis spm 2024 export_<datetime>_taskid_<tasked value>” and the Zip folder will be located for download at “Files on the Server” Tab located on the Top left of the Report Workspace in the CT 20 application. 

2. IntegrationID = 201: HMIS SPM 2024 DQ Detail Export

It’s the second integration that runs in the SPM export Process. This Integration has three Stored Procedures, following is the list those three. Referenced from table dbo.CSVStoredProcedures and the query used to extract the results below is: 

SELECT * FROM dbo.CSVStoredProcedures WHERE IntegrationID IN (201) ORDER BY IntegrationID, ExecutionOrder;

ProcID: Shows the ID of the Stored Procedure; Primary Key for the table dbo.CSVStoredProcedures

IntegrationID: Shows the linked Integration to the ProcID. The IntegrationID is the Primary Key for the table dbo.CSVIntegration and this table stores the Integration setup information.

ExecutionOrder: This tells you about the order in which the Integration runs the Stored Procedures and it runs in the ascending order. 

StoredProcName: Name of the Stored Procedure. Please note the schema for all these Stored Procedures is ‘report’. 

The Stored Procedure SPMCreateExportTask in this Integration is responsible for using the parameters from the current running Task (generated by the previous Task) and Insert a record into the table dbo.AsynchronousTask which will run the next IntegrationID required in the Export Process. In this case, after running the IntgrationID = 201, this procedure is responsible for inserting a record in the table dbo.AsynchronousTask to run the IntegrationID = 200 (HMIS SPM 2024 Summary Export). This procedure runs as the last step of the IntegrationID and hence it’s ExecutionOrder is 99. 

The Stored Procedure SPM_2024_DQ_1 provides the details for Data Quality Question1. The output generated from this procedure defines the value for Row 2 Column B – Q1 and Row 6 Column B – Q1. (Refer the screenshot below which is explained in detail in the link provided below (Page 27))

The Stored Procedure SPM_2024_DQ_4 provides the details for Data Quality Question4. The output generated from this procedure defines the value for Row 2 Column B and Column C – Q4. (Refer the screenshot below which is explained in detail in the *Link provided below (Page 32))

*Link - Please refer to the following link for details on how the Data Quality Stored Procedures for Q1 and Q4 have been created: 

FY 2024 HMIS Standard Reporting Terminology Glossary - Version 1.1 (hudexchange.info)

Following Table shows the Stored Procedures, the CSV file being generated as the part of the export and the resulting tables. Referenced table dbo.CSVStoredProcedures and the query used to extract the results below is: 

SELECT * FROM dbo.CSVStoredProcedures WHERE IntegrationID IN (201);

StoredProcName: Name of the Stored Procedure. Please note the schema for all these Stored Procedures is ‘report’.

CSVFileName: The name of the CSV File storing the data from running the respective Stored Procedure and will be available to view the results as the part of the Export Zip folder from running this integration. Please Note that CSV Files ExportTaskCreated will be a blank file because it’s part of the process and has nothing to do with the SPM Data Quality output. 

ResultStorageSchema: This represents the schema of the resulting tables used to store the output of the respective Stored Procedure. 

ResultStorageTable: This represents the table name of the resulting tables used to store the output of the respective Stored Procedure. Please note that the Stored Procedures SPMCreateExportTask doesn’t have a resulting table to store the results because it is responsible for executing the next CSV Integration so no output is required to be exported in CSV. 

Once this Integration is successfully ran, the Export file will be generated and named as “hmis spm 2024 dq detail export_<datetime>_taskid_<tasked value>” and the Zip folder will be located for download at “Files on the Server” Tab located on the Top left of the Report Workspace in the CT 20 application.

3. IntegrationID = 200: HMIS SPM 2024 Summary Export

This is the last Integration that runs and completes this process. This Integration has only one Stored Procedure, following is the table showing the same. Referenced from table dbo.CSVStoredProcedures and the query used to extract the results below is:

 SELECT * FROM dbo.CSVStoredProcedures WHERE IntegrationID IN (200) ORDER BY IntegrationID, ExecutionOrder;

ProcID: Shows the ID of the Stored Procedure; Primary Key for the table dbo.CSVStoredProcedures

IntegrationID: Shows the linked Integration to the ProcID. The IntegrationID is the Primary Key for the table dbo.CSVIntegration and this table stores the Integration setup information.

ExecutionOrder: This tells you about the order in which the Integration runs the Stored Procedures and it runs in the ascending order. 

StoredProcName: Name of the Stored Procedure. Please note the schema for all these Stored Procedures is ‘report’.

The Stored Procedure HMIS_SPM_2024_Summary generates an export file that is calculated from the outputs generated by running the previous Integrations. The code for this Stored Procedure is labelled with comments which allows you to understand the columns and calculations being used from all the SPM Measures result tables and Data Quality result tables each being separated out as its own section based on individual Measure and Data Quality Question. 

Following table will represent the CSV file name that will contain the final output of the SP HMIS_SPM_2024_Summary. Referenced table dbo.CSVStoredProcedures and the query used to extract the results below is: 

SELECT * FROM dbo.CSVStoredProcedures WHERE IntegrationID IN (200);

StoredProcName: Name of the Stored Procedure. Please note the schema for all this Stored Procedure is ‘report’.

CSVFileName: The name of the CSV File storing the data from running the respective Stored Procedure and will be available to view the results as the part of the Export Zip folder from running this integration. 

ResultStorageSchema and ResultStorageTable: These two columns are mentioned as NULL because there is no resulting table which is storing the output for this Integration as it is being directly exported as a Zip folder. 

Once this Integration is successfully ran, the Export file will be generated and named as “hmis spm 2024 summary export_<datetime>_taskid_<tasked value>” and the Zip folder will be located for download at “Files on the Server” Tab located on the Top left of the Report Workspace in the CT 20 application. 

The complete description for this IntegrationID 200 is mentioned in Appendix A (Referenced the spec document provided at the time of the development).

APPENDIX A:

System Performance Measures (SPM) Comma Separated Value (CSV) Export Specifications for FY23

Introduction:

This document creates the specifications for an export of SPM data to be uploaded to HDX 2.0 in lieu of manual data entry of the values already programmed by the SPM Programming Specifications or the HMIS Data Quality Report. Uploading will be optional for CoCs for FY 23 SPM Submissions. However, uploading will be required for CoCs for FY 24 submissions. 

Specifications:

The CSV Export should have 2 rows and 94 columns. The first row should be the Variable Name and the second row should be the value of that variable. A guide to the names, data types, and programming rules or references are provided below in the CSV Upload Field Guide Table. The table includes the specifications for the: 

1. Variable Name exactly as it should appear.
2. Data Type which will be one of 5 types below:
3. Programming Rules or Reference. Provides a reference the existing specification documentation or else a definition for the variable and its value. Any reference to existing specification will be to either: 

CSV Upload Field Guide Table:

94 Variables