Cognos data import

This page explains how to get data from the Cognos EPR Report into CCMDB.accdb and rolled out to collectors.

Process

Tina receives the data in a scheduled, automated email from Decision Support Services every morning as early as possible, usually before 8. The data is then imported into CCMDB.accdb master on Regional server and provided to collectors via regular roll-out and news and backup.

Outlook Rule in Tina's profile to get data to Pagasa and Lisa

Decision Support Services sends a report to Tina. The report needs to go to Pagasa and Lisa so it can be imported and rolled out; this is accomplished by outlook rule "Cognos to Pagasa" in Tina's Outlook. This was an automated process at some point but isn't at this time since no PC is reliably on the network when it should happen.

Process if the email does not arrive

Generally the email from Decision Support Services arrives around 7:40 +/-1hr. This can be delayed if the ETL that feeds the process is delayed. If the email has not arrived by 9:30, see Decision Support Services for who to contact.

Steps to import and roll out

Each morning the Data Processor or Database Manager runs LoadCognos.bat (if the .bat isn't visible, it is the file of type "Windows Batch File"). If the data collector is unavailable, the Process Analyst does this. Anyone who has the requisite email in their mailbox would be able to run this, but for now only Lisa receives it as a final fall-back.

What happens in the process

This information is only relevant for troubleshooting and should not usually be done manually.

LoadCognos.bat calls LoadCognos.vbs which calls CCMDB.accdb.Function CognosImport for the actual steps performed during import, and how the queries and tables work together and the email to collectors is generated. process then returns to LoadCognos.bat which copies CCMDB.accdb to the Regional Server and then calls Updt all.bat to roll it out to all collectors Fallback process The import can also be manually run on any computer that has CCMDB.accdb using the Cognos2_Service_Starter#Cognos_Import_Button and the data can be manually sent out to collectors following the same process as Rolling out changes. When run on Tina's computer the process will roll out any current changes in the local file; if anything was changed, update log saying it has been rolled out!

Newly encountered units or services

If a unit or service is part of the import that is not already in our reference tables, an email will be sent to Tina and Lisa. Lisa will inform Tina what the new unit/service is about and Tina will add it to the master version of CCMDB.accdb accordingly.

Newly encountered units

Newly encountered units need to be added to the s_Cognos_Units table, see more info there about what the fields mean.

If the unit is one we collect then Level of care hierarchy needs to be determined (discuss with the Statistician if not clear) and entries need to be added to Boarding Loc and s_level_of_care table.

Newly encountered services

Newly encountered services need to be added to the s_Cognos_Services table, see more info there about what the fields mean.

If the service is one we collect then it needs to be added to Service tmp entry options.

Problem Log

This is the problem log from when the process was automated. Now that it's done manually there haven't really been problems for some time.

The VBA logs its progress to a log file \\ad.wrha.mb.ca\wrha\REGION\Shared\ICU_DATA_COLLECTION\Maintenance\ccmdb_log.txt 2021-03-04 - Pagasa provided screen shot of error "Error in sub notify_about_new_Congos: The operation failed. The messaging interfaces have rerutned an unknown error. If the problem persists, restart Outlook. - -2147467259" This happened when PC was rebooted at the end of the day before, locked in and then left like that over night. 2020-Nov-10 - email was delayed to 9:17, then rolled out ok 2020-Nov-09 - Pagasa is away and the updater task has failed on her PC; Tina will do it manually until Pagasa comes back 2020_Oct_23 - Chastity report came late @ 08:46, Run manually cognos. 2020_Oct_21 - Chastity email -ETL failed last night so there will be no report today. No new cognos. 2020_Oct_9 - task was stuck because CFE was open 2020_Oct_8 - Task schedule did not run because computer were rebooted by EHealth overnight. Pagasa changed the Task schedule so it does not run on weekends. Concern will be this long weekend and the task may hang because data from email not deleted before it runs on Tues AM. 2020-Oct-6 & 7 - Tuesday - ran at 0740. No issues.