In order to bring you the best possible user experience, this site uses Javascript. If you are seeing this message, it is likely that the Javascript option in your browser is disabled. For optimal viewing of this site, please ensure that Javascript is enabled for your browser.
Login  |   Cloud ERP  |   Home  |   qad.com




Release Notes for Releases 3.6.2 and 3.5.2
QAD Business Intelligence Version: 3.6.2 and 3.5.2
Date: July 2012
QAD BI 3.6.2 and 3.5.2 address the following issues:
fact_inv_mth_balance has been corrected so that it handles partial quantities.
fact_om_order_history has been corrected to ensure CLOSED orders are the most recent/current version of the record, so that the CLOSED status should also show properly in fact_om_order.
Fixes
QAD BI 3.6.2 and 3.5.2 include all fixes from 3.6.1 and 3.5.1, respectively, and in addition address:
 
Component
QAD Issue
Releases
Description
Metadata
QBIS-51
BI 3.5.2, BI 3.6.2
Inventory - fact_inv_mth_balance is now able to handle partial quantities; the numeric type for the columns has been corrected.
Metadata
QBI-2085, QBI-2091
BI 3.5.2, BI 3.6.2
Order Management - the order on the rank over clause is not set properly for the transaction_date, resulting in incorrect fact_om_order_history records.
Metadata
QBI-2087, QBI-2088
BI 3.5.2, BI 3.6.2
Order Management - update_stage_om_invoice_l_rng now uses the DATE_EARLIEST_POSSIBLE and DATE_LATEST_POSSIBLE parameters instead of hard-coding dates using English month names, to avoid issues in non-English environments.
Metadata
QBI-2068, QBI-2089, QBI-2090
BI 3.5.2, BI 3.6.2
Order Management - fact_om_order_history sometimes will have CLOSED orders that are the most recent/current version of the record, but will not be shown as most recent/current and are then showing up in fact_om_order as OPEN even though they are CLOSED.
Metadata
QBI-2086
BI 3.6.1
Financials - load_vo_mstr Where clause corrected usage of AP_PROCESS_DAYS and AP_CHECK_PROCESS_DAYS parameters.
Data Warehouse Tables Changed
The following section lists the Data Warehouse tables that have been changed, added, or deleted.
Note: The tables on the Modified list may have been changed structurally. For example:
Columns added or deleted
Indexes added or deleted
Changes made to business display names
Changes to visibility of particular columns in the BI Portal
Any combination of these modifications
List of Tables Modified for BI 3.6.2
Common Module
 
load_tr_hist
permsup_transaction_hist
perm_transaction_hist
stage_tr_hist_list
Financials Module
 
load_vo_mstr
Operations Module
 
extract_inv_trans_hist_cons
stage_inv_bal_mth_list
stage_op_transaction_merge
extract_inv_transaction_hist
stage_inv_bal_mth_trans
stage_op_transaction_merge
extract_po_transaction_hist
stage_inv_bal_mth_trans1
stage_op_transaction_merge
extract_op_transaction_hist
stage_inv_bal_mth_trans2
stage_op_transaction0
fact_inv_mth_balance
stage_inv_mth_balance
stage_op_transaction1
fact_inv_transaction
stage_inv_mth_balance_hist_calc
stage_op_transaction2
fact_op_transaction
stage_inv_mth_balance1
stage_po_order_hist_initial4
perm_op_hist
stage_inv_transaction1
stage_po_order_hist_initial5
stage_inv_bal_mth_current
stage_inv_transaction_con_cus3
stage_po_order_hist_initial6
stage_inv_bal_mth_current4
stage_inv_transaction_con_cus4
work_inv_change_hist_list
stage_inv_bal_mth_date
stage_op_tr_hist_rjct_wo
work_inv_change_list
stage_inv_bal_mth_date1
stage_op_trans_scrapi
work_inv_change_list1
stage_inv_bal_mth_date2
stage_op_transaction
 
Order Management Module
 
:
extract_om_transaction_hist
stage_om_booking_previous
stage_om_order
fact_om_booking
stage_om_booking_transaction
stage_om_order1
fact_om_shipment
stage_om_booking_transaction1
stage_om_order2
stage_om_booking
stage_om_invoice_transaction
stage_om_order3
stage_om_booking_max_tran
stage_om_invoice_transaction1
stage_om_invoice_line_ranged
stage_om_invoice_line_list
stage_om_order_snap
stage_om_order_snap1
stage_om_order_snap3
 
 
List of Tables Added for BI 3.6.2
 
stage_inv_mth_end_cst
stage_inv_mth_end_cst1
stage_inv_trx_chg_mths
stage_inv_trx_chg_mths1
List of Tables Deleted for BI 3.6.2
No tables deleted for BI 3.6.2 upgrade.
List of Tables Modified for BI 3.5.2
Note: The 3.5.2 patch includes all changes from the 3.5.1 patch. Even if you already installed the 3.5.1 patch, this reloading of application changes should not affect operation, but does mean that if any of the tables modified in 3.5.1 or 3.5.2 have customizations, you will need to reapply all those customatizations. Follow the upgrade instructions carefully.
 
extract_inv_trans_hist_cons
stage_inv_bal_mth_trans1
stage_om_order1
extract_inv_transaction_hist
stage_inv_bal_mth_trans2
stage_om_order2
extract_om_transaction_hist
stage_inv_bal_revalue1
stage_om_order3
extract_po_transaction_hist
stage_inv_mth_balance
stage_om_order_snap
fact_inv_mth_balance
stage_inv_mth_balance_hist_calc
stage_om_order_snap1
fact_inv_transaction
stage_inv_mth_balance1
stage_om_order_snap3
fact_om_booking
stage_inv_transaction_con_cus3
stage_po_order_hist_initial4
fact_om_shipment
stage_inv_transaction_con_cus4
stage_po_order_hist_initial5
load_tr_hist
stage_om_booking
stage_po_order_hist_initial6
perm_transaction_hist
stage_om_booking_max_tran
stage_po_order_snap1
permsup_transaction_hist
stage_om_booking_previous
stage_po_order_snap3
stage_inv_bal_item_list
stage_om_booking_transaction
stage_po_order_snapshot
stage_inv_bal_mth_current
stage_om_booking_transaction1
stage_tr_hist_list
stage_inv_bal_mth_current4
stage_om_invoice_line_list
stage_tr_hist_list
stage_inv_bal_mth_date
stage_om_invoice_line_ranged
stage_unit_of_measure3
stage_inv_bal_mth_date1
stage_om_invoice_transaction
work_inv_change_hist_list
stage_inv_bal_mth_date2
stage_om_invoice_transaction1
work_inv_change_list
stage_inv_bal_mth_list
stage_om_order
work_inv_change_list1
stage_inv_bal_mth_trans
 
 
List of Tables Added for BI 3.5.2
 
dim_snapshot_period_type
stage_inv_mth_end_cst1
load_snapshot_period_type
stage_inv_trx_chg_mths
stage_inv_mth_end_cst
stage_inv_trx_chg_mths1
List of Tables Deleted for BI 3.5.2
No tables deleted for BI 3.5.2 upgrade.
Upgrade to 3.6.2 from 3.6 or 3.6.1
Preparation
1
Plan for a couple of hours of down time.
2
Ensure that there are no loads currently in progress. All jobs must be completed.
3
Put all scheduled jobs On Hold until the upgrade is complete. Shut down the scheduler.
4
Take a backup of the current warehouse database.
5
Before you begin, make note of the following:
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 have neither Sales nor Purchase Order processing, note any custom jobs that might be after the final SNAPSHOT jobs: ____________________________________ _____________________________________________________________________________
Note any custom HIST_LOAD_JOBxxx jobs that appear after the standard ROLLUP or SNAPSHOT jobs: ______________________________________________________________
Important: This upgrade will replace some load and stage tables. If there are any customizations to these tables, ensure that they are ready to be made again.
Upgrade Steps for QAD BI Metadata
6
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 need to 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.
7
Log in to Setup Administrator.
8
Choose Tools|Start Logging. Select a name and location for your log file and save.
9
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. Make any changes if necessary:
On the left, select Dimension. On the right, click on the box next to Existing Dimension objects will be and select Altered.
On the left, select Dimension View. On the right, click on the box next to Existing Dimension View objects will be and select Recreated.
On the left, select Stage Table. On the right, click on the box next to Existing Stage table objects will be and select Recreated.
On the left, select Permanent Stage Table. On the right, click on the box next to Existing Permanent Stage table objects will be and select Altered.
On the left, select Fact Table. On the right, click on the box next to Existing Fact table objects will be and select Altered.
Click OK.
10
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 will take some time as the load process alters and re-creates various objects.
11
Start the Scheduler. Make sure all jobs are still suspended.
12
If the upgrade affected any customized tables, reapply your customizations. For a list of new and modified tables, see Data Warehouse Tables Changed.
13
If there were any customizations previously added to any of the modified jobs, update the job to reinclude 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:
PREPROCESS_COMMON_LOADS
PREPROCESS_COMMON_TRUNCATE
PREPROCESS_OP_LOAD
PREPROCESS_OP_TRUNCATE
DAILY_OP_PERM_EXTRACT
HIST_OP_PERM_EXTRACT
DAILY_OP_INV_PROCESS_CHAINED
HIST_OP_INV_PROCESS_CHAINED
HIST_OM_PROCESS_CHAINED
14
Check the values of the TR_HIST_DATE_MAX_Sxx parameters for each source. If necessary, increase TR_HIST_PROCESS_DAYS from the default value of 3 days. The number of Process Days must be such that the TODAY – earliest TR_HIST_DATE_MAX_Sxx is less than the TR_HIST_PROCESS_DAYS. There are also several new parameters to control how the BI system handles the rollover of the tr_trnbr:
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 may 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 actually 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 will fail and indicate 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 will roll 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 will force 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 will 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.
15
If you already installed the BI 3.6.1 patch, skip ahead to Step 16. 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.
16
Run the upgrade job for BI 3.6.2:
a
Run the UPGRADE_36_TO_362_QBI_2068 job.
b
Set the JOB_CHAINING_ENABLED parameter back to Y.
17
Set the INV_PROCESS_DAYS to 50000 to ensure that all data is reprocessed. Then run the DAILY loads.
Note: The daily load will take longer than normal, as all inventory transactions are being reprocessed to correct errors.
18
After the daily load completes, set the INV_PROCESS_DAYS to a small 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 will reference 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. Please be aware that you may experience at least one day of long processing time on the daily load.
Upgrade to 3.5.2 from 3.5 or 3.5.1
Preparation
1
Plan for a couple of hours of down time.
2
Ensure that there are no loads currently in progress. All jobs must be completed.
3
Put all scheduled jobs On Hold until the upgrade is complete. Shut down the scheduler.
4
Take a backup of the current warehouse database.
5
Before you begin, make note of the following:
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 have neither Sales nor Purchase Order processing, note any custom jobs that might be after the final SNAPSHOT jobs: ____________________________________ _____________________________________________________________________________
Note any custom HIST_LOAD_JOBxxx jobs that appear after the standard ROLLUP or SNAPSHOT jobs: ______________________________________________________________
Important: This upgrade will replace some load and stage tables. If there are any customizations to these tables, ensure that they are ready to be made again.
Upgrade Steps for QAD BI Metadata
6
Unzip the metadata for the BI 3.5.2 Upgrade.
7
Log in to Setup Administrator.
8
Choose Tools|Start Logging. Select a name and location for your log file, then save.
9
Load the BI 3.5.2 Upgrade Application as follows:
a
Set the application directory to the new metadata folder.
b
Load application upgradeBI 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.
e
You can always cancel 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.
f
The next dialog box is Application Load Properties. Verify that the default values are correct. Make any changes if necessary:
On the left, select Dimension. On the right, click on the box next to Existing Dimension objects will be and select Altered.
On the left, select Dimension View. On the right, click on the box next to Existing Dimension View objects will be and select Recreated.
On the left, select Stage Table. On the right, click on the box next to Existing Stage table objects will be and select Recreated.
On the left, select Permanent Stage Table. On the right, click on the box next to Existing Permanent Stage table objects will be and select Altered.
On the left, select Fact Table. On the right, click on the box next to Existing Fact table objects will be and select Altered.
Click OK.
Note: The upgrade application installations will take some time as the load process alters and re-creates various objects.
10
If the upgrade affected any customized tables, reapply your customizations. For a list of new and modified tables, see Data Warehouse Tables Changed.
11
Start the Scheduler. Make sure all jobs are still suspended.
12
If any customizations were previously added to HIST_INV_PROCESS_XXXXXXXX and DAILY_INV_PROCESS_XXXXXXX, update the job to reinclude those extra parts. You can double check what was in the job by looking at the newly renamed HIST_INV_PROCESS_XXXXXXXX_1 or DAILY_INV_PROCESS_XXXXXXXX_1. Then, rename any existing DAILY_INV_PROCESS_<source> job to DAILY_INV_PROCESS_<source>_1. Insert a copy of the new DAILY_INV_PROCESS_XXXXXXXX and rename it to DAILY_INV_PROCESS_<source>. Repeat this to copy the new HIST_INV_PROCESS_XXXXXXX for each source.
13
If any customizations were previously added to HIST_LOAD_PERM_XXXXXXXX and DAILY_PERM_XXXXXXX, update the job to reinclude those extra parts. You can double check what was in the job by looking at the newly renamed HIST_LOAD_PERM_XXXXXXXX_1 or DAILY_PERM_XXXXXXXX_1. Then, rename any existing DAILY_PERM_<source> job to DAILY_PERM_<source>_1. Insert a copy of the new DAILY_PERM_XXXXXXXX and rename it to DAILY_PERM_<source>. Repeat this to copy the new HIST_LOAD_PERM_XXXXXXX for each source.
14
If any customizations were previously added to HIST_SALES_PROCESS_XXXXXXX, update the job to reinclude those extra parts. You can double check what was in the job by looking at the newly renamed HIST_SALES_PROCESS_XXXXXXXX_1. Then, rename any existing HIST_SALES_PROCESS_<source> job to HIST_SALES_PROCESS_<source>_1. Insert a copy of the new HIST_SALES_PROCESS_XXXXXXXX and rename it to HIST_SALES_PROCESS_<source>.
15
Check the values of the TR_HIST_DATE_MAX_Sxx parameters for each source. If necessary, increase TR_HIST_PROCESS_DAYS from the default value of 3 days. The number of Process Days must be such that the TODAY – earliest TR_HIST_DATE_MAX_Sxx is less than the TR_HIST_PROCESS_DAYS. There are also several new parameters to control how the BI system handles the rollover of the tr_trnbr:
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 may 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 actually 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 will fail and indicate 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 will roll 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 will force 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.
16
If you are upgrading from 3.5.1 to 3.5.2, skip to step 17. If you are upgrading directly from 3.5 to 3.5.2, run the UPGRADE_35_to_351_QBIS_25 job to update the permsup_transaction_hist and fact_om_booking, tables to include the transaction date, and to seed the TR_HIST_DATE_MAX values for each source.
17
Run the Upgrade jobs for the inventory valuation and order history issues as follows:
a
Set the JOB_CHAINING_ENABLED parameter to N.
Note: Ensure that the INV_PROCESS_DAYS parameter is set to be large enough to load records from the ERP system since the last Daily run.
b
Run the UPGRADE_35_TO_352_OM_order_hist_index_drop_and_table_truncate job.
c
Run SET_CONNECTION_<connection_name> for your first source system. If you have only one source system, you do not need to perform this step.
d
If you have already installed BI 3.5.1, skip to step i. Otherwise, if you are upgrading directly from BI 3.5, run DAILY_COMMON_PROCESS_<connection_name>.
e
Run DAILY_PERM_<connection_name>.
f
Run DAILY_INV_PROCESS_<connection_name>.
g
Replace the prefix of the word DAILY with HIST for the INV_PROCESS_RUNNING_JOB_NAME parameter.
h
Run the UPGRADE_35_to_351_QBI_1773 job.
Note: This job may take a long time because it is recalculating the entire fact_inv_mth_balance table.
i
Run the HIST_PERM_EXTRACT job.
j
Run the UPGRADE_35_TO_352_QBI_2068_OM_ORD_HIST_RELOAD job.
k
Repeat steps c through j for each connection name.
l
Run the UPGRADE_35_TO_352_OM_order_hist_index_rebuild job.
m
Run the UPGRADE_35_TO_352_QBI_2068 _ROLLUPS job.
18
Set the INV_PROCESS_DAYS parameter back to an appropriate value such as 5-10 days, if necessary.
19
Set the JOB_CHAINING_ENABLED parameter back to Y.
20
Reset the DAILY_START job so that it will run again normally.
21
Resume normal processing.