Cognos data import: Difference between revisions

From CCMDB Wiki
Jump to navigation Jump to search
 
(40 intermediate revisions by 4 users not shown)
Line 2: Line 2:


== Process ==
== Process ==
Tina receives the data in a scheduled, automated email from Chastity 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.
[[Decision Support Services]] sends an automated email report to Tina every morning. 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 ===
=== Cognos extract via email ===
Decision Support Services sends a report to Tina. The report needs to go to Pagasa so we can schedule the automated import on a machine that sits on WRHA network at all times. So, there is an outlook rule "Cognos to Pagasa" in Tina's Outlook profile to forward the email to Pagasa.
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.  
{{Discuss|*where does the scheduled task pull the data from?  Chasity email with excel? }}
=== Scheduled Task on Pagasa's PC ===
There is a scheduled task on Pagasa's PC that calls [[LoadCognos.bat]] early each morning.


=== Data import instructions ===
=== Outlook Rule in Tina's profile to get data to Pagasa and Lisa ===
'''When run on Tin'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! '''
Decision Support Services emails 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.
There should be no instructions required if the scheduled task running [[LoadCognos.bat]] works. Follow the trail through that file if troubleshooting is required.


== Background ==
==== Process if the email does not arrive ====
If the email has not arrived by 9:30, we contact [[Decision Support Services]] (see that page for contact info, it keeps changing).
 
=== Steps to import and roll-out ===
Once the email arrives each morning we run [[LoadCognos.bat]] (if the .bat isn't visible, it is the file of type "Windows Batch File").
The import and roll-out is generally done by the first of the following who is available:
# [[Database Manager]]
# [[Data Processor]]
 
==== What happens in the process ====
{{Collapsable | always=This information is only relevant for troubleshooting and should not usually be done manually. | full=  
* [[LoadCognos.bat]] calls
* [[LoadCognos.bat]] calls
* [[LoadCognos.vbs]] which calls  
* [[LoadCognos.vbs]] which calls  
* [[CCMDB.accdb]].[[Sub CognosImport]] for the actual steps performed during import, and how the queries and tables work together and the email to collectors is generated.
* [[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 [[Rolling out changes | roll it out]] to all collectors
* process then returns to [[LoadCognos.bat]] which copies [[CCMDB.accdb]] to the [[Regional Server]] and then calls [[Updt all.bat]] to [[Rolling out changes | roll it out]] to all collectors


== Cognos Error/ Problem Log ==
'''Fallback process'''
{{Discuss| Tina, not sure where you want keep and error log and trouble shooting steps.}}
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 ==
{{Collapsable
| always= 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.
| full=
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.|
*'''2020-Oct-5 - Monday''' - Task schedule screen stuck open Monday morning.  This seems to occur because data is sent by email from Chastity on both Saturday and Sunday.  Because Pagasa is not in during the weekend to delete the email from Chastity with data in email, the task scheduler then does not run on Monday.  Pagasa has to run it manually when she comes in before 0900.
*''''2020-Sept 28- Oct 2.20''' - Task schedule ran without issue.
*'''2020-Sept-27''' -Cognos task schedule, script screen hanging open. Did not run properly.  Chasity email came at 0701. Pagasa ran task manually and deleted to emails from Chastity on Sat, Sun and Mon.  (Task is scheduled to run at 0740 hrs).
*'''2020-Sep-27''' - Cognos task schedule did not appear to run o Sunday.  Email from Chastity 0716. No email sent out to staff.
*'''2020-Sep-26''' - Cognos task schedule ran on Saturday.  Email came from Chastity 0730.  Task schedule seemed to run ok. Email sent out.
*'''2020-Sep-25''' - Cognos ran no problem today after we changed the time.
*'''2020-Sep-24''' - We changed the Task scheduler to run @ 07:40 instead of 07:15, Chastity emai came 07:01, The task scheduler error window message gave the following message: Network problems preventing connection  to microsoft exchange 61315439.
*'''2020-Sep-18''' - Did not work.  Chasity email at 0702 and task scheduler ran 0716 but was hanging open when Pagasa came in. She had to run it manually.  Note: Task scheduler had also ran over the weekend and sent email out to staff Sat Sept 19.   
*'''2020-Sep-18''' - Did not work.  Chasity email at 0702 and task scheduler ran 0716 but was hanging open when Pagasa came in. She had to run it manually.  Note: Task scheduler had also ran over the weekend and sent email out to staff Sat Sept 19.   
*'''2020_Sep_18''' - Email from Chasity came in at 0701 hrs.  Task schedule ran at 0716 hrs.  Successfully ran on it's own today.
*'''2020_Sep_18''' - Email from Chasity came in at 0701 hrs.  Task schedule ran at 0716 hrs.  Successfully ran on it's own today.
*'''2020_Sep_15''' - Email from Chasity came in at 0731 hrs.  The Task scheduler ran at 0732 hrs. Black script window hanging open on Pagasa's computer. She pressed ENTER and the window closed, unlike the previous week when she had to press Ctr C to close.  The email was sent to DC CC and DC Med is that NEW Cognos data is available.  Staff reported that they did not get NEW, but got previous days data.  Pagasa then manually ran the scheduled Task and staff reported getting today cognos data.
*'''2020_Sep_15''' - Email from Chasity came in at 0731 hrs.  The Task scheduler ran at 0732 hrs. Black script window hanging open on Pagasa's computer. She pressed ENTER and the window closed, unlike the previous week when she had to press Ctr C to close.  The email was sent to DC CC and DC Med is that NEW Cognos data is available.  Staff reported that they did not get NEW, but got previous days data.  Pagasa then manually ran the scheduled Task and staff reported getting today cognos data.
*'''2020_Sep_10''' - Cognos ran this am 07:36 from Chastity.  Run the Task scheduler (to run at 0715 daily) the black script screen ran but a '''message pop''' up asking for '''pw to close the ccmdb.mdb''', just closed the message box and the script run but no email received for "New Cognos etc" . Rebooted my computer and re run the Task scheduler still no email. Phoned the collectors if they received new cognos and they did, but no email notice. 35 minutes later the email showed up in peoples inbox.
*'''2020_Sep_10''' - Cognos ran this am 07:36 from Chastity.  Run the Task scheduler (to run at 0715 daily) the black script screen ran but a '''message pop''' up asking for '''pw to close the ccmdb.mdb''', just closed the message box and the script run but no email received for "New Cognos etc" . Rebooted my computer and re run the Task scheduler still no email. Phoned the collectors if they received new cognos and they did, but no email notice. 35 minutes later the email showed up in peoples inbox.
**Contacted Herman about the issue. He forwarded a notice about delivery delays on Exchange this am.  He later sent the updated that it was resolved before noon.
**Contacted Herman about the issue. He forwarded a notice about delivery delays on Exchange this am.  He later sent the updated that it was resolved before noon.
 
*'''2020_Sep-08''' - Cognos scheduled task ran this am at 0715 (as it is scheduled).  Pagasa saw black script screen ''hanging'' open when she came in at 0840 hrs. She closed out of it.  She noted that Chastity's email came in in 0701 hrs.  Pagasa proceeded to manually run Cognos Task Scheduler to roll out to staff. She did not see Type mismatch 13...error message box until 0910 hrs, as she had started to do other work (scanning TISS). Only difference from Friday after running cognos task scheduler manually was that the email was generated and sent out to staff that ''new cognos data is available in 5 min, and to run news and backup''.  
*'''2020_Sep-08''' - Cognos scheduled task ran this am at 0715 (as it is scheduled).  Pagasa saw black script screen ''hanging'' open when she came in at 0840 hrs. She closed out of it.  She noted that Chastity's email came in in 0701 hrs.  Pagasa proceeded to manually run Cognos Task Scheduler to roll out to staff. She did not see Type mismatch 13...error message box until 0910 hrs, as she had started to do other work (scanning TISS). Only difference from Friday after running cognos task scheduler manually was that the email was generated and sent out to staff that ''new cognos data is available in 5 min, and to run news and backup''.  
**PW message box still comes up when exiting CCMDB.accdb Programs.  
**PW message box still comes up when exiting CCMDB.accdb Programs.  
Line 37: Line 82:
*email was not generated and sent out to staff that new cognos data is available.......
*email was not generated and sent out to staff that new cognos data is available.......
*other bug that appeared after Cognos ran today was the '''CCMDB PROGRAM''' is popping up a '''PW message box when you EXIT'''. Had to press cancel to get rid of it. [[User:TOstryzniuk|Trish Ostryzniuk]] 15:15, 2020 September 4 (CDT)
*other bug that appeared after Cognos ran today was the '''CCMDB PROGRAM''' is popping up a '''PW message box when you EXIT'''. Had to press cancel to get rid of it. [[User:TOstryzniuk|Trish Ostryzniuk]] 15:15, 2020 September 4 (CDT)
}}


== Related articles ==  
== Related articles ==  

Latest revision as of 09:13, 2023 September 27

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

Process

Decision Support Services sends an automated email report to Tina every morning. The data is then imported into CCMDB.accdb master on Regional server and provided to collectors via regular roll-out and news and backup.

Cognos extract via email

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.

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

Decision Support Services emails 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.

Process if the email does not arrive

If the email has not arrived by 9:30, we contact Decision Support Services (see that page for contact info, it keeps changing).

Steps to import and roll-out

Once the email arrives each morning we run LoadCognos.bat (if the .bat isn't visible, it is the file of type "Windows Batch File"). The import and roll-out is generally done by the first of the following who is available:

  1. Database Manager
  2. Data Processor

What happens in the process

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

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.

Related articles

Related articles: