Question Engine Migration - Data Partner Processes

By the end of the Question Engine Migration. Microsoft Access will no longer be supported by PopMedNet for Summary Table queries. The instructions below explain how to create a SQL server summary table database from .txt files. These instructions assume you have verified that you have SQL server and are ready to create a Summary Table Database in SQL Server format. The queries included in the below steps are provided to assist you in creating and maintaining a Summary Table Database in SQL Server. If you already have an ETL process in place you may continue to use your current process. However, please still inform the PopMedNet team that you are using SQL Server for Summary tables.

Please note that Question-Engine-based summary table queries (which require SQL Server) will not be sent until all Data Partners have completed Step 4 in these instructions. The PopMedNet team will communicate when Question-Engine-based querying will begin.

Data Partners have also been invited for a training on either January 31st, 2019 or February 6th 2019. A copy of the slides used in the presentation can be found here: PMN - QE Data Partner Training - Sentinel and HDC.pptx. The presentation will provide a clear overview of what differences to expect with the current switch to Question Engine. 

Step 1: Create the Summary Table Database - DUE DATE: 3/12/19

1. Obtain the most recent ETL .txt files that represent your Summary Table data
   1. Users of the Sentinel network are sent a summary table creation package via PopMedNet (a SAS program) that generates these .txt files. One text file is generated for each Summary Table Database table
2. Place the summary table files .txt files in a folder on the workstation/computer on which SQL Server is installed. Please note, SQL Server Management Studio, (or the SQL management program you use) might not be installed on the same machine as SQL server.
3. Open SQL Server Management Studio (or the SQL management program you use).
4. Execute this query: Create Summary Table Database
   
   1. This query will create a properly formatted and empty summary table database within your SQL Server environment. 
   2. Within the query there are placeholders where you must enter a desired database name. They are clearly indicated within the code.
   3. After executing the query, you may need to refresh the database list before the database will appear in your list of databases.
5. After the empty database has been successfully created, execute this query: Import Summary Table Data
   1. There is a placeholder within this query where you must enter the filepath where your summary table .txt files are stored. If you do not enter the proper file path, you will get an error when executing the query. The placeholder is clearly indicated within the code.
   2. This query may take a few minutes to run depending on the amount of data being imported.
6. If the query in step 5 is successful, you have successfully created a Summary Table Database in SQL Server. You may use the query in step 5 to import future ETLs if desired. The query will overwrite existing data and import new data from the file path specified.
7. Inform the PopMedNet Team that you completed this step by commenting on your open Question Engine Task Tracking Ticket. View your Question Engine Task tracking ticket in your list of open service desk tickets.

Step 2: Test the Summary Table Database - DUE DATE: 3/27/19

1. After creating the summary table database and importing data, run this query: Test Summary Table Database
   1. This query will test that all tables exist and contain data. It will also produce a result table that lists all tables, the number of members in each, and the min/max year of data in each.
2. If the test passes you will see the following in the "messages" of the query result window: "Passed Column Check Test"
3. Inform the PopMedNet Team that you completed this step by commenting on your open Question Engine Task Tracking Ticket. View your Question Engine Task tracking ticket in your list of open service desk tickets.

Step 3: Connect Your SQL Server Summary Table Database to a new Question Engine Compatible DataMart - DUE DATE: 4/25/19

1. On 4/1/2019 (for Sentinel) and 4/4/19 (for HDC) a new DataMart called "Organization DataMart" will appear in your list of DataMarts available to you in your DataMart client. Your current DataMart will have the suffix "Legacy" appended. If you participate in Sentinel and HDC, a new DataMart will appear in the list for BOTH networks.
2. Follow these steps to configure your new DataMart and connect it to your SQL Server Summary Database: Configuring your DataMarts.
   
   1. IMPORTANT. As a member of Sentinel and/or HDC, you only need to perform the steps under the "Summary Table Query Model Processor Settings" heading. Question engine allows multiple request types to be understood by a single model processor.  The Summary Table model processor can receive file distribution, modular program and all types of Summary Table (Prevalence, Incidence, and MFU) requests. 
   2. If you do not use summary tables, you still need to configure a new question engine compatible DataMart to receive any queries 
3. Inform the PopMedNet Team that you completed this step by commenting on your open Question Engine Task Tracking Ticket. View your Question Engine Task tracking ticket in your list of open service desk tickets.

DELAYED- Step 4: Execute a test query to ensure the connection to your SQL Server Database was successfully configured - DUE DATE:  TBD

1. After you confirm completion of step 3, the PopMedNet team will send a Summary Table test query to your question engine compatible DataMart. When you receive the query, please respond query and upload results. 
2. The PopMedNet Team will verify that your results were successfully uploaded to the query tool. You do not need to comment on your Question Engine Task Tracking ticket indicating your completion of this step.
3. Once the PopMedNet team verifies that your query executed and results were uploaded, we will close out your Question Engine Task Tracking issue and comment that you have successfully migrated to question engine! 
4. Once all Data Partners complete step 4, a communication will be sent indicating when production queries will begin to be sent to your question engine compatible DataMart.