Upgrade Steps for QAD BI Metadata
1 Unzip the metadata for the Common module and any other modules you have installed.
Note: Because the metadata files are now segregated by module, it is only necessary to install the modules that are needed.
• Perform the Common upgrade first. The other upgrades can be done in any order.
• All users must run the Common metadata upgrade application.
• If you process any of the Order Management (Sales) fact_om_* tables, install the Order Management (OM) upgrade application.
• If you process Inventory and/or Purchase Order tables, install the Operations module upgrade application.
• If you process any Financial tables, install the Financials module upgrade application.
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 by right-clicking on the file and choosing Install Application.
c From the drop-down list, select the ODBC DSN for your DWD instance.
d The system prompts you to proceed. Click OK.
Note: You can cancel the installation here to review the list of new and modified objects. To continue with the load, right-click in the window and choose Proceed with Load Application.
e The next dialog box is Application Load Properties. Verify that the default values are correct. Change the value if necessary:
• On the left, select Dimension. On the right, click the box next to Existing Dimension objects will be and select Altered.
• On the left, select Dimension View. On the right, click the box next to Existing Dimension View objects will be and select Recreated.
• On the left, select Stage Table. On the right, click the box next to Existing Stage table objects will be and select Recreated.
• On the left, select Permanent Stage Table. On the right, click the box next to Existing Permanent Stage table objects will be and select Altered.
• On the left, select Fact Table. On the right, click the box next to Existing Fact table objects will be and select Altered.
• Click OK.
5 Repeat for any other modules that must be upgraded.
• Order Management. Change the Application Directory to the metadata/order_management subdirectory and install application upgradeOM.
• Operations. Change the Application Directory to the metadata/operations subdirectory and install application upgradeOps.
• Financials. Change the Application Directory to the metadata/financials subdirectory and install application upgradeFin.
Note: The upgrade application installations take some time as the load process alters and re-creates various objects.
6 In SQL Server Management Studio, run the misc.sql script in your Data Warehouse database:
<Location of unpacked Common module>\sqlserver\misc.sql
This enables an extra “Tech Notes” tab in your Data Warehouse Designer.
7 Start the Scheduler. Make sure that all jobs are still suspended.
8 If the upgrade affected any customized tables, reapply your customizations. For a list of new and modified tables, see
Data Warehouse Tables Changed.
9 If there were any customizations previously added to any of the modified jobs, update the job to include those extra parts. You can double check what was in the job by looking at the newly renamed <jobname>_1. The jobs that changed in this release are:
|
• CUSTOMER_PROCESS
|
• POSTPROCESS_OP_CUBES
|
|
• DAILY_JOB_SETUP
|
• CLEANUP_OM_TABLES
|
|
• HIST_JOB_SETUP
|
• DAILY_OM_PROCESS_CHAINED
|
|
• DAILY_COMMON_PROCESS_CHAINED
|
• HIST_OM_PROCESS_CHAINED
|
|
• PREPROCESS_COMMON_LOADS
|
• POSTPROCESS_OM_CUBES
|
|
• PREPROCESS_COMMON_TRUNCATE
|
• CLEANUP_FIN_TABLES
|
|
• CLEANUP_COMMON_TABLES
|
• DAILY_FIN_EE_AP_PROCESS_CHAINED
|
|
• HIST_COMMON_PROCESS_CHAINED
|
• DAILY_FIN_EE_AR_PROCESS_CHAINED
|
|
• HIST_MODULES_LOAD_FINISH
|
• DAILY_FIN_EE_GL_PROCESS_CHAINED
|
|
• INITIAL_JOB_SETUP
|
• DAILY_FIN_EE_PERM_CHAINED
|
|
• PREPROCESS_COMMON_EEONLY_LOADS
|
• DAILY_FIN_SE_PERM_CHAINED
|
|
• CLEANUP_OPERATIONS_TABLES
|
• HIST_FIN_EE_AP_PROCESS_CHAINED
|
|
• PREPROCESS_OP_LOAD
|
• HIST_FIN_EE_FINISH_CHECK
(was HIST_EE_AP_AR_FINISH_CHECK)
|
|
• PREPROCESS_OP_TRUNCATE
|
• HIST_FIN_EE_GL_PROCESS_CHAINED
|
|
• DAILY_OP_PERM_EXTRACT
|
• HIST_FIN_EE_PERM_CHAINED
|
|
• HIST_OP_PERM_EXTRACT
|
• HIST_FIN_SE_PERM_CHAINED
|
|
• DAILY_OP_INV_PROCESS_CHAINED
|
• POSTPROCESS_FIN_SE_AP_SUMMARY
|
|
• HIST_OP_INV_PROCESS_CHAINED
|
• POSTPROCESS_FIN_SE_CUBES
|
|
• DAILY_OP_PO_PROCESS_CHAINED
|
• PREPROCESS_FIN_EE_LOADS
|
|
• HIST_OP_PO_PROCESS_CHAINED
|
• PREPROCESS_FIN_TRUNCATE
|
10 There are several new parameters (as of BI 3.6.1) to control how the BI system handles the rollover of the tr_trnbr – ensure that these are set appropriately for your system:
• TR_HIST_PROCESS_DAYS. This is the number of look-back days when extracting data from tr_hist. The records being extracted are records where tr_trnbr is greater than the highest transaction_number records already extracted for that source, except after tr_trnbr rollover, when newer transactions have smaller trnbrs than older transactions. The BI DW must also take into account the transaction date in order to find the lower-numbered transactions that are new. This parameter says to extract from the ERP transactions entered within the last X days. If the number of days since the TR_HIST_DATE_MAX_Sxx is greater than the TR_HIST_PROCESS_DAYS parameter, a task that checks this during the run fails and indicates that this number needs to be increased. The default value is 3.
• TR_HIST_MAX_VALUE. This is the maximum number allowed for the tr_hist.tr_trnbr field, or in other words, the number after which the tr_trnbr rolls over and start at the first available low number. The default value is 99999999. If the tr_sq01 sequence is set to a different maximum value, enter that here.
• TR_HIST_PRE_ROLLOVER_COUNT. This is the number of records that would typically be in three days’ worth of data to ensure readiness for tr_hist.tr_trnbr rollover. Once the tr_trnbr for a source is detected to be within TR_HIST_PRE_ROLLOVER_COUNT of TR_HIST_MAX_VALUE, the BI system sets the TR_HIST_TRAN_MAX_Sxx value for the source to 0. This forces the tr_hist extraction to scan the whole table for records that have been processed within the last TR_HIST_PROCESS_DAYS. The default value is 5000.
• TR_HIST_PCT_OF_MAX_REF. This is the percent of the TR_HIST_MAX_VALUE to use as a reference point to look for new low-numbered transactions when dealing with the rollover. The default value is 50. When this value is 50, you search the transactions with numbers less than 50% of the TR_HIST_MAX_VALUE for the highest number with a transaction date within the processing window.
11 If you already installed the BI 3.6.1 or BI 3.6.2 patch, skip ahead to Step
12.
If you are upgrading directly from BI 3.6, run the Upgrade jobs for the tr_hist.tr_trnbr roll-over issue for each module you have installed:
• Common Module. Run the UPGRADE_36_to_361_QBIS_25_COMMON job to update the permsup_transaction_hist table to include the transaction date and to seed the TR_HIST_DATE_MAX values for each source.
• Order Management. Run the UPGRADE_36_to_361_QBIS_25_OM job to update the fact_om_booking table to include the transaction date.
• Operations. Run the UPGRADE_36_to_361_QBIS_25_OP job to update the fact_op_transaction table to include the transaction date.
12 Run the upgrade jobs for BI 3.7:
a Set the JOB_CHAINING_ENABLED parameter to N.
b Run the UPGRADE_36_TO_37_ QBI_2121 job.
c If you use the Order Management module, run the UPGRADE_36_to_37_ OM job.
d If you use the Operations module, run the UPGRADE_36_to_37_PO job.
13 There are two new parameters for each connection:
• INITIAL_JOB_SETUP_CONNECTION_xx_DAILY_MODULES
• INITIAL_JOB_SETUP_CONNECTION_xx_HIST_MODULES.
The upgrade installs copies of this parameter for connections 01 and 02. If you have more than two connections, add additional copies of this parameter for each additional connection. The INITIAL_JOB_SETUP job uses these parameters to determine the Daily and History job chains, respectively. By default the parameters for the 01 and 02 connections contain all ERP modules (COM, OM, OP, FIN). Remove any modules that you do not use from this list.
14 Set INITIAL_JOB_SETUP_ENABLED to Y and run INITIAL_JOB_SETUP to rebuild the daily and historical job chains.
15 If you use the Operations module, truncate the fact_inv_mth_balance table. It is re-created in the next step. Enter the following command in SQL Server: truncate table fact_inv_mth_balance.
16 Set the INV_PROCESS_DAYS to 50000 to ensure that all data is reprocessed. Set the new INV_HISTORY_DATE parameter to today’s date.
Note: The daily load takes longer than normal, as all inventory transactions are being reprocessed to correct errors, and the fact_inv_mth_balance table is being repopulated.
17 Run the Daily load. If you had set the INV_PROCESS_DAYS value to a large number, after the daily load completes, set the INV_PROCESS_DAYS to your normal value, such as 2, and resume normal processing.
Note: These steps reload the fact_inv_transaction table with data as of the current day. Depending on the amount of process days, when the DAILY update resumes, it references all records from that fact table while trying to update the fact_inv_mth_balances table. Longer processing times are expected until the current date, minus the number of process days, exceeds the dss_update_time timestamp value from the reload. Once that time period has passed, the run times should improve substantially. You may experience at least one day of long processing time on the daily load.