Upgrade to QAD BI 3.6 from QAD BI 3.5
BI Metadata Upgrade Procedure
Preparation
1 Plan for a couple of hours of down time.
2 Ensure that none of the loads are currently in progress. All jobs must be completed.
3 Put all scheduled jobs On Hold until the upgrade is complete. Shut down scheduler.
4 Take a backup of the current warehouse database.
5 Before you begin, make note of the following:
a Note any custom DAILY_LOAD_JOBxxx jobs that appear after the final ROLLUP jobs; for example, DAILY_PO_ROLLUP, or DAILY_SALES_ROLLUP if you do not have the Purchase Orders module installed. If you do not have Sales or Purchase Order processing, note any custom jobs that might be after the final SNAPSHOT jobs): _____________________________________________________________
b Note any custom HIST_LOAD_JOBxxx jobs that appear after the standard ROLLUP or SNAPSHOT jobs: _____________________________________________________________
Warning This upgrade replaces some load and stage tables, so if there are any customizations to these tables, ensure that they are ready to be made again.
Upgrade Steps for QAD BI Metadata
1 Unzip the metadata for the Common module, plus any other modules you have installed, into a directory. The Metadata files are now segregated by module, so you only install the modules you need.
• All users must run the Common metadata upgrade application.
• If you process any of the Order Management (Sales) fact_om_* tables, also install the Order Management (OM) upgrade application.
• If you process any of the SE and/or EE financial tables, also install the Financials upgrade application.
• Finally, if you process Inventory and/or Purchase Order tables, install the Operations module upgrade application.
Perform the Common upgrade first; the other upgrades can be done in any order.
2 Log in to Setup Administrator.
3 Choose Tools -> Start Logging. Select a name and location for your log file and Save.
4 Load the Common Module Upgrade Application as follows:
a Set your application directory to the new metadata/common folder.
b Load Application upgradeCom v36 by right-clicking on it and choosing Install Application.
c Select the ODBC DSN for your DWD instance from the drop-down list.
d A dialog box prompts you "Do you want to proceed now?"; click OK.
Note: You can always Cancel here to review the list of new/modified objects; to continue with the load, right-click in the window and choose Proceed with Load Application.)
e The next dialog box is for "Application Load Properties":
• Select Dimension on the left - on the right, click the box next to "Existing Dimension objects will be" and select Altered.
• Select Dimension View on the left - on the right, click the box next to "Existing Dimension View objects will be" and select Recreated.
• Select Stage Table on the left - on the right, click the box next to "Existing Stage table objects will be" and select Recreated.
• Select Permanent Stage Table on the left - on the right, click the box next to "Existing Permanent Stage table objects will be" and select Altered.
• Select Fact Table on the left - on the right, click the box next to "Existing Fact table objects will be" and select Altered.
• Click OK.
5 The upgrade application output may have errors that some indexes could not be dropped. This is OK as they are new indexes that do not yet exist in the database. The upgrade script tries to drop each index before recreating it; if the index is new, the drop fails. Note any other errors in the upgrade output other than the index errors. Consult Support before continuing.
6 Repeat for any other modules that need to be upgraded.
a For Order Management, change the Application Directory to the metadata/order_management subdirectory, and install application upgradeOM v36.
b For Operations, change the Application Directory to the metdata/operations subdirectory, and install application upgradeOps v36. You may see an error here about the inability to create index dim_po_line_status_idx_A. This is corrected when the index is rebuilt after running the Upgrade jobs as described below.
c For Financials (both EE and SE are in the same metadata package), change the Application Directory to the metadata/financials subdirectory, and install application upgradeFin v36.
7 The upgrade application installations take some time as the load process alters/recreates various objects. If any of the tables that were modified had included customizations, reapply those customizations to your upgraded tables when you have installed all pertinent upgrade applications. Verify that all other customizations are not affected.
8 Start the Scheduler, but make sure that all jobs are still suspended.
9 Populate some new parameters and possibly modify some existing values, as follows:
a Modify AR_PAYMENT_PROCESS_DAYS and COM_PROCESS_DAYS from the default values if desired:
• AR_PAYMENT_PROCESS_DAYS: Used to find backdated payments in ar_mstr and ard_det tables. To save daily load time, not all records are loaded daily from ar_mstr and ard_det - only open records, plus closed records whose ar_date or ar_paid_date is between Today and Today minus AR_PAYMENT_PROCESS_DAYS. Set this to a value that safely allows you to extract backdated payments; for example, if payments are sometimes entered with a date that is 3 months in the past, set this to approx. 90 days. However, if backdating is never more than 45 days, enter 45. Default value is 60.
• COM_PROCESS_DAYS: Used to reduce load time by limiting the amount of data extracted from the pt_mstr, sct_det, and exr_rate tables to only those rows that have been modified between Today and Today minus COM_PROCESS_DAYS. Default value is 750.
b Financial module only: Modify AP_CHECK_PROCESS_DAYS from the default value if desired:
• AP_CHECK_PROCESS_DAYS: Used to find backdated checks or vouchers in ap_mstr table. To save daily load time, not all records are loaded daily from ap_mstr - only open records, plus closed records such as checks with a back-dated void date or clear date, or vouchers with a back-dated paid date, between Today and Today minus AP_CHECK_PROCESS_DAYS. Set this to a value that safely allows you to extract backdated rows; for example, if transactions are sometimes entered with a date that is 3 months in the past, set this to approx. 90 days. However, if backdating is never more than 45 days, enter 45. Default value is 60.
c Operations module only: Modify OP_LOAD_LOOKBACK_DAYS, OP_MOVING_AVG_WEEKS, OP_OEE_SNAPSHOT_FREQUENCY, and OP_PROCESS_DAYS from the default values if desired:
• OP_LOAD_LOOKBACK_DAYS: Used to limit the amount of data extracted from wo_mstr, wod_det, and wr_route to just those open or closed between Today and Today minus OP_LOAD_LOOKBACK_DAYS. Default value is 20.
• OP_MOVING_AVG_WEEKS: The number of weeks on which to base the Moving Average calculations. Default value is 13.
• OP_OEE_SNAPSHOT_FREQUENCY: The frequency of snapshots in the OP OEE snapshot tables. Possible values are: W for Weekly, M for Monthly, B for both weekly and monthly. Default value is B for both.
• OP_PROCESS_DAYS: The number of days of OP to extract and process from permanent staging tables. Default value is 5.
10 ***Very important *** Check the PO_ORDER_HISTORY_DATE and SALES_ORDER_HISTORY_DATE parameters. If the current date minus the PO_PROCESS_DAYS is newer than the PO_ORDER_HISTORY_DATE, or the current date minus the SALES_PROCESS_DAYS is newer than the SALES_ORDER_HISTORY_DATE, increase the process days value so that the current date minus that number of days produces a date that is before the related ORDER_HISTORY_DATE (PO or SALES).
11 Delete all of your existing SET_CONNECTION_<source> jobs. (Note: do NOT delete the SET_CONNECTION_XXXXXXXX job; that is the template that is used to create new source-specific SET_CONNECTION jobs.)
12 Set INITIAL_JOB_SETUP_DATE to the current date.
13 Change INITIAL_JOB_SETUP_ENABLED to Y.
14 In the Scheduler, run the INITIAL_JOB_SETUP job.
15 The list of History and Daily jobs is rewritten by the INITIAL_JOB_SETUP job. Add to the end of the DAILY_LOAD_JOBxxxx and HIST_LOAD_JOBxxxx load lists any jobs that occurred after the snapshot jobs (which you noted in the Preparation section).
16 Run the Upgrade jobs for each module you have installed:
a Run the UPGRADE_35_TO_36_COMMON job.
b If you installed the Financials module upgrade package, run the UPGRADE_35_TO_36_FINANCIALS job.
c If you installed the Operations module upgrade package, first set the Parameter QBI_924_PATCH_RUN to N, then run the UPGRADE_35_TO_36_OPERATIONS job. Note: the QBI_924_PATCH_RUN parameter is initially set to Y to guard against the accidental running of the patch upgrade task where it is not warranted. Set it to N before running the Operations Upgrade job; after the job completes, the parameter is removed, ensuring the QBI-924 upgrade task is not allowed to run a second time.
d If you installed the Order Management (Sales) module upgrade package, run the UPGRADE_35_TO_36_ORDER_MANAGEMENT job.
17 If the upgrade affected any customized tables, reapply your customizations (a list of new and changed tables can be found at the end of the Upgrade instructions.)
18 Resume normal processing.
To Add a New Source
If you want to add a data source, do the following:
1 Add the new source name to DAILY_LOAD_SOURCE_Sxx (where xx represents the next number in the sequence).
2 Add the new source name to HIST_LOAD_SOURCE_Sxx (where xx represents the next number in the sequence).
3 Add the new source name to INITIAL_JOB_SETUP_CONNECTION_xx (where xx represents the next number in the sequence).
4 Add an INITIAL_JOB_SETUP_CONNECTION_xx_RUN (if it does not exist) and set its value to N.
5 Add an INITIAL_JOB_SETUP_CONNECTION_xx_TYPE (if it does not exist) and set its value to either EE or SE depending on which type your financials system for that source is.
6 Make sure INITIAL_JOB_SETUP_DATE is set to the current date.
7 Set INITIAL_JOB_SETUP_ENABLED to Y.
8 Run INITIAL_JOB_SETUP.
9 Double-check the INITIAL_JOB_SETUP_CONNECTION_xx_RUN parameters to make sure that any sources that have been historically loaded have a Y next to their relative _xx_ number and that any new sources have an N.
10 Go to the Scheduler. Start the HIST_START job. Once it starts, double check to make sure that it started running HIST_ jobs only for your new data source.