Instructions/Procedure for Scheduled Reports to execute 'after ETL'
The 'After ETL' schedule for Reports,Alerts & Scheduled Mail is now taken care in such a manner that it is initiated from ARC itself. One Quartz thread will be created at the start of the Tomcat. This thread will start running from 12 AM midnight until it finds the ETL status as 'YES' for every 15 minutes. It will check the table ?AFTER_ETL_STATUS? for the column ?ETL_COMPLETED?. Once it finds the status as ?Y?, the thread will change the ETL status to ?N? and starts executing the 'after ETL' jobs. After executing all the jobs, it will create another thread that will start running from the next day midnight. Once the new thread begins, the earlier thread will stop. The changes that have to be performed as part of the ETL/Application is as follows: From the ETL Perspective, ------------------------------------ 1) Create a table named ?AFTER_ETL_STATUS? with column ?ETL_COMPLETED? of nvarchar(1). The table script is as follows: CREATE TABLE AFTER_ETL_STATUS ( ETL_COMPLETED nvarchar(1) ); 2) By default, insert entry ?N? to this table (INSERT AFTER_ETL_STATUS VALUES (?N?)) and once ETL is completed update the column to ?Y?. (only one record should be present for this table) From the Application perspective, --------------------------------------------- In 'arc.properties' file: a) arc.afteretl.enable = yes b) arc.afteretl.minute.interval = 15 c) arc.afteretl.alert = yes d) arc.afteretl.reportsubs = yes e) arc.afteretl.qanda = no f) arc.afteretl.schdreport = yes g) arc.afteretl.schdemail = yes h) arc.afteretl.dashboardcache = yes For Dashboard caching at ETL, the following properties must be set in 'dashboard.properties' file: a) caching.startup = no b) caching.users = All c) caching.noof.user = 3 Definition of the Properties defined ----------------------------------------------- a) arc.afteretl.enable --> Need to set this as ?yes? in order to start this thread. After making this to 'yes', tomcat should be restarted for the creation of the thread b) arc.afteretl.minute.interval --> This is the time interval for the thread to check for the ETL completion. By default it is set as15 minutes. For every given minutes, the thread will check the above table if the record for the column is ?Y? or not c) arc.afteretl.alert --> If this is set to ?no? then, it will not check the Alerts for After ETL jobs d) arc.afteretl.reportsubs --> If this is set to ?no? then, it will not check the Report Subscription for the 'after ETL' jobs e) arc.afteretl.qanda --> If this is set to?no? then, it will not check the Question and Answer jobs f) arc.afteretl.schdreport --> If this is set to ?no? then, it will not check the Scheduled Reports/KPI for the 'after ETL' jobs g) arc.afteretl.schdemail --> If this is set to ?no? then, it will not check the Scheduled Emails for the 'after ETL' jobs h) arc.afteretl.dashboardcache --> If this is set to ?no? then, it will not re-cache the Dashboard portlets i) caching.startup --> This is meant for running the dashboard cache on the tomcat start-up. If the dashboard is to be cached through the 'after ETL' thread then, there's no need to run the dashboard cache on tomcat start-up. Hence, set this to ?no? j) caching.users --> Either one or multiple users can be specified as comma separated entries like admin,gowri,muthu etc. If the dashboard is to be re-cached for all the users then, set this to ?All? k) caching.noof.user --> It is necessary to enable this option eventhough all the users are enabled for dashboard caching. Caching each and every portlet of the dashboards for all the users will consume more time in forming the query, executing this query in the DB and ultimately forming the report data. ARC users are more often considered and it considers the top 'n' users for caching. For e.g., if this property is set to '3' then, while caching the dashboard, it will check for the most frequently used ARC users (using 'no.of logins' by users) and fetch the top 3 users and for only for those users, it will cache the dashboard.