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  |   On Demand  |   Home  |   qad.com




Release Notes for Current Release
QAD Business Intelligence Version: 3.9
Date: September 2013
New Feature Summary
The QAD BI 3.9 release introduces the following new features:
Auto Installer. BI 3.9 includes a new GUI-based installer that can be used to install the Data Warehouse Designer, create the Data Warehouse Database, and install the BI Portal. Additional GUI installers can then be used to install metadata modules into the Data Warehouse Database. This enhancement streamlines the installation process, thus reducing the need for manual configuration. For more information, see the QAD BI 3.9 Installation Guide.
Note: The Auto Installation can only be used for new installations and not for upgrades.
Dashboards. BI Portal objects, including charts and grids, can be included in the new QAD .NET UI Dashboards. BI Reports are not currently operational within the new Dashboard framework. The BI Portal webapp reference must be specified in the client-session.xml file. See QAD Dashboard documentation for more information.
Order Management Dashboard. In BI 3.9, the OM Module has been redesigned to include three new OM Dashboards dedicated to Bookings, Shipments and Sales, which are the major steps in the sales order process flow. Previously, the OM Dashboards only contained a limited number of visual items and displayed information at the company level, which includes all domains, for the current financial month or year. In BI 3.9, the BI dashboards contain more visual items that display information at both the company level and for a specific domain. Previously, the OM Dashboards did not have input parameters. The user could not view information for a different month and year or drill down into a specific domain. In BI 3.9, the new OM Dashboards provide a Year and Domain parameter bar so that users can view information for the domain and financial year of their choice. In addition to these improvements in BI 3.9, the BI Order Management module now correctly supports customer scheduled orders by displaying scheduled and non-scheduled orders in all OM dashboards.
Upgrade Instructions
See the QAD BI 3.9 Installation Guide for the instructions to upgrade to QAD BI 3.9.
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
The procedure that populates the table has changed
List of Tables Modified
Common Module Modified Tables
 
dim_credit_term
load_comd_det
load_tr_hist
dim_ee_saf
load_ee_DInvoice
load_tr_hist_history
dim_item
load_ee_Saf
load_vd_mstr
load_ad_mstr
load_ee_SafConcept
perm_transaction_hist
load_ar_mstr
load_exr_rate
stage_ee_saf
load_cm_mstr
load_icc_ctrl
stage_gl_calendar_year
load_com_mstr
load_pt_mstr
stage_transaction_hist
Financials Module Modified Tables
 
extract_ee_customer_invoice
stage_ee_ap_supplier_invoice_history2
extract_ee_customer_invoice_stage
stage_ee_ap_supplier_invoice_history3
extract_ee_posting_line
stage_ee_ap_supplier_invoice_history4
extract_ee_posting_saf
stage_ee_ap_supplier_invoice_movement_hist
extract_ee_supplier_invoice
stage_ee_ap_supplier_invoice1
extract_ee_supplier_invoice_stage
stage_ee_ap_supplier_invoice2
extract_gl_trans_hist
stage_ee_ar_customer_invoice_hist
fact_gl_transaction
stage_ee_ar_customer_invoice_hist1
load_ap_mstr
stage_ee_ar_customer_invoice_history
load_ard_det
stage_ee_ar_customer_invoice_history0
load_ck_mstr
stage_ee_ar_customer_invoice_history1
load_ckd_det
stage_ee_ar_customer_invoice_history2
load_ee_ad_mstr
stage_ee_ar_customer_invoice_history3
load_ee_CInvoice
stage_ee_ar_customer_invoice_movement_hist
load_ee_CInvoiceMovement
stage_ee_ar_customer_invoice_stage_hist
load_ee_Posting
stage_ee_ar_customer_invoice1
load_ee_Posting_all
stage_ee_customer_invoice_perm
load_ee_PostingLine_all
stage_ee_customer_invoice_stage_perm
load_ee_PostingSaf
stage_ee_gl_transaction1
load_gl_transaction_type
stage_ee_posting_line_perm
load_vo_mstr
stage_ee_posting_saf
perm_ee_customer_invoice
stage_ee_supplier_invoice_perm
perm_ee_customer_invoice_stage
stage_ee_supplier_invoice_stage_perm
perm_ee_posting_line
stage_gl_balance1
perm_ee_supplier_invoice
stage_gl_transaction1
perm_ee_supplier_invoice_stage
stage_gl_transaction2
stage_ap_invoice_snaps
stage_gl_transaction3
stage_ap_voucher_chk_match
stage_se_ap_inv_snap
stage_ar_invoice_snap
stage_se_ap_snap1
stage_ee_ap_supplier_invoice
stage_se_ap_snap2
stage_ee_ap_supplier_invoice_hist
stage_se_ar_inv_snap
stage_ee_ap_supplier_invoice_hist1
stage_se_ar_snap1
stage_ee_ap_supplier_invoice_history0
stage_se_ar_snap2
stage_ee_ap_supplier_invoice_history1
stage_se_ar_snap3
Operations Module Modified Tables
 
fact_po_order
stage_inv_transaction1
stage_po_order_hist_initial3
fact_inv_transaction
stage_inv_trx_chg_mth1
stage_po_order_hist_initial6
fact_po_order_history
stage_po_credit_term
stage_po_order_hist_initial7
fact_po_order_performance
stage_po_credit_term1
stage_po_order_hist1
load_pod_det
stage_po_ord_perf
stage_po_order_hist2
load_shft_det
stage_po_order_hist
stage_po_order_hist3
load_wod_det
stage_po_order_hist_initial
stage_po_order_hist4
load_wod_det_hist
stage_po_order_hist_initial1
stage_po_order_hist5
stage_inv_mth_balance
 
 
Order Management Module Modified Tables
 
extract_om_invoice_line
stage_om_booking1
stage_om_order_inv_line
extract_om_transaction_hist
stage_om_booking2
stage_om_order_inv_line1
fact_om_booking
stage_om_booking3
stage_om_order_inv_line2
fact_om_invoice
stage_om_booking4
stage_om_order_line
fact_om_order
stage_om_invoice
stage_om_order_line_all
fact_om_order_history
stage_om_invoice_line
stage_om_order_snap1
fact_om_order_performance
stage_om_invoice_order_line
stage_om_order_snap2
fact_om_shipment
stage_om_invoice_transaction
stage_om_order_snap3
load_cncu_mstr
stage_om_invoice1
stage_om_order_transaction
load_idh_hist
stage_om_invoice2
stage_om_order_transaction4
load_idh_hist
stage_om_invoice3
stage_om_order1
load_ih_hist
stage_om_ord_perf
stage_om_order2
load_sod_det
stage_om_ord_perf_full
stage_om_order3
perm_om_invoice_line
stage_om_ord_perf1
stage_om_shipment
stage_om_booking
stage_om_order
stage_om_shipment_transaction
stage_om_booking_previous
stage_om_order_header
stage_om_shipment1
stage_om_booking_transaction
stage_om_order_header_all
stage_om_shipment2
stage_om_booking_transaction1
stage_om_order_inv_header
stage_om_shipment3
stage_om_booking_transaction2
 
 
EAM Module Modified Tables
 
fact_eam_inv_transaction
load_eam_part_type
stage_eam_inv_trans_perm
load_eam_account
load_eam_squawks
stage_eam_inv_transaction
load_eam_domain
perm_eam_inv_transaction
 
Tables Added to Existing Modules
For the BI 3.9 release, there were no tables added to the Common and Financial Modules.
Order Management Module Added Tables
 
load_abs_mstr
load_tr_hist_ship_id
stage_om_schedule_detail2
load_absr_det
perm_customer_consigment
stage_om_scheduled_order_perf
load_idh_hist_cust_ref
stage_om_booking_transaction_all
stage_om_scheduled_order_perf1
load_idh_hist_pr_list
stage_om_invoice_scheduled_transaction
stage_om_scheduled_order_transaction
load_idh_hist_rlse_id
stage_om_ord_perf_all
stage_om_scheduled_order_transaction1
load_pc_mstr
stage_om_order_transaction7
stage_om_shipment_requirement
load_sch_mstr
stage_om_price_list
stage_om_shipment_requirement1
load_schd_det
stage_om_schedule_detail
stage_om_shipment_requirement2
load_tr_hist_ref
stage_om_schedule_detail1
 
Operations Module Added Tables
 
dim_liability_date
stage_po_order_hist_daily2
stage_po_order_hist_daily5
stage_po_order_hist_daily
stage_po_order_hist_daily3
stage_po_order_hist_daily6
stage_po_order_hist_daily1
stage_po_order_hist_daily4
stage_po_order_hist_daily7
List of Tables Deleted
For the BI 3.9 release, there were no tables deleted.
Enhancements
 
Table 1 BI 3.9 Enhancements  
Component
QAD Issue
Description
Metadata
(Multi-Module)
QBI-3437
The dimension formerly called the Item dimension was renamed to the Site-Item dimension in BI 3.8.1. This was done to avoid confusion over the dimension. It was not clear from the name that the business key of the dimension was actually on the combination of the Item and the Site.
However, the new name of Site-Item also caused some confusion in the Portal because users were looking for the Item dimension to be in a certain place alphabetically. Because Site-Item appeared much further down in the alphabetical list of dimensions, Portal display name for this dimension has been changed to Item-Site.
Portal Standard Objects (OM)
QBI-3593
The BI Portal Standard Objects for the Order Management module have been replaced.
The new objects are installed for any new BI Portal installation.
For current Order Management BI Portal customers, an upgrade script is provided that renames the current Order Management standard Portal objects as "deprecated". This script should be run prior to importing the new QAD Standard portal objects for Order Management.
Fixes
 
Table 2 BI 3.9 Fixes
Component
Qad Issue
Description
Metadata (Order Management)
QBI-2949
The source for 2 columns in fact_om_shipment file has been modified. The unit_cost_trans and unit_cost_base columns are now sourced from the sales order line.
Specifically, the new source is sod_det.sod_std_cost (or idh_hist.idh_std_cost) column. The base cost is based on the trans cost, converted to the base currency, as needed.
Previously, they were sourced from the individual cost components of the shipment transaction.
Additional logic was also added so that in the event that the transaction records exist and the invoices do not, which results in the base currency cost having a value and the trans value currency having a cost of 0, the transaction uses the base currency to calculate a trans currency cost.
 
QBI-3084
The fact_om_order snapshot table listed the current month's date as the prior day always. While it makes sense to display yesterday's date if the daily run ran between midnight and 8am, it does not make sense for a daily run that is run at any other time to display the prior date for the current day. It should display the current day. This has been corrected now so that any run between just after 8am and midnight presents the current month's end of day as the current day.The runs between midnight and 8am display data from the prior day.
 
QBI-3173
In order to ensure that stage_om_order_line_all is functioning properly for fact_om_order_history (picks last version of order per day) and fact_om_shipment and fact_om_booking, the logic leading up to this table was revamped to ensure that all invoice lines for a a given order/line/item combination get through to stage_om_order_line_all. That way, the history table can link up on the last record of the day, but a shipment or booking record can link to any record from that day. Changes were also made to better handle consigned orders, including ensuring the CN-SHIP records are getting through to fact_om_shipment, but not their correlating ISS-SO records. This was achieved in part by adding a perm_customer_consignment table that is fed from cncu_mstr.
 
QBI-3199
The creation of faked transaction records in stage_om_order_transaction4 was producing too many records because it was recreating records that had been filtered out in stage_om_order_transaction3. A ranking system was put in place on 4 and a new table, 7 was created to only pick the last invoice of each day for faked transaction records to ensure that only useful records are being created to be fed to stage_om_order1. Otherwise, multiple records are created for a day that immediately get filtered out. For serialized items where there are many invoices per day, this removes enormous overhead from the logic.
 
QBI-3308
In prior releases, when a Sales Order (sod_det) record was deleted and no invoices had been created against it, the record would be end_dated on the day that it was found. Because the logic that set that end date did not have filtering on it, it did not look for the already DELETED order_status records, causing the next daily run, the dss_history_end_date, to get set again to the new day. The result was that every day would be the new end date for the DELETED record.
Filters have now been put in place in the logic for fact_om_order_history so that once a record has a status of DELETED with an end date, the record does not keep resetting the end date every run. The only deleted records getting a current end_date are new ones.
 
QBI-3325
When you use the new perm_customer_consignment table, which is sourced from cncu_mstr, links are made as the data is pulled into extract_om_order_line and extract_om_transaction_hist so that a consignment_flag column can be populated for quick and easy reference of consigned orders. This logic is contingent on the ERP properly populating the consignment information in the cncu_mstr table. This sets the stage for more work down the road if anybody needs to determine if an extracted invoice (idh_hist) or transaction (tr_hist) record is of type consignment.
 
QBI-3331
dim_snapshot_period_type_key is now included in fact_om_order so that when an end of week lands on an end of month, both values appear in the table.
 
QBI-3368
ISS-SO transactions for consigned orders should not be getting through to the fact_om_shipment table. Instead the CN-SHIP records are the ones that should be sent through. Filters were added to ensure that records are properly flagged consigned and then the ISS-SO transactions are filtered out from getting to fact_om_shipment.
Metadata (Purchasing)
QBI-2959
The historical load of PO History Fact table has been modified to correctly transform the ship to code from the PO Header.
 
QBI-3021
In prior releases, if the end of week and end of month happened to fall on the same day, the snapshot table fact_po_order would only display either the end of week or end of month value for the business key. To fix this problem, there should be one row of data for each. Adding the rows was achieved by changing the business key to include the dim_period_snapshot_ty_key and changing the fact_po_order update logic to also look at the dim_period info when doing updates. The table now provides a row for each snapshot type when the date falls on the same day.
 
QBI-3035
The logic for Due Date was correct.
A new Liability Date column was added, as well as a correlating dimension.
Existing logic for PO Payment Due Date was corrected by creating a function that mimics the logic in the ERP with regard to setting the date based on various credit terms information and liability date. For detailed technical look at the logic, see the procedure GetPOPaymentDueDate.
 
QBI-3043
Daily Purchase Order Processing has been modified. In previous versions, multiple runs for the DAILY BI updated jobs could cause duplicate key errors during PO processing. Also, if the DAILY BI process was not run on a given day, all PO activity for that day was combined with activity for subsequent days.
This logic has been modified. Multiple DAILY runs no longer result in duplicate key errors. Also, if the DAILY run is skipped for any reason, the purchasing activity is correctly recorded on the day it took place.
 
QBI-3100
In fact_po_order_history, the order_amount columns should display the PRE-discount cost, while all other amount columns reflect the cost after discounts have been applied. This now works properly. For records with no discount, the ordered_amount and extended_amount columns are essentially the same. The difference between the two is only shown when discounts have been applied.
 
QBI-3104
PO Order History table (fact_po_order_history) now handles deleted purchase orders as follows:
The current row of the table (dss_current_flag = Y) is "expired". That is, the dss_history_end_date is set to the current processing date.
The quantity open is set to 0.
The PO Line Status is set to "Unknown".
 
QBI-3108
PO Order History table (fact_po_order_history) now handles deleted purchase orders as follows:
The current row of the table (dss_current_flag = Y) is "expired". That is, the dss_history_end_date is set to the current processing date.
The quantity open is set to 0.
The PO Line Status is set to "Unknown".
Metadata (Financials EE)
QBI-3052
QBI-2817 Release Notes
Financials EE History and Snapshot fact tables had some HIST load calculation errors prior to BI 3.9. Invoices with payments spread over multiple dates could have double counted payments or missing payments on some history and snapshot records. BI 3.9 fixes these calculations. Customers upgrading to BI 3.9 should run the FIN EE upgrade script, which truncates all AP and AR fact tables and does a fresh historical reload from source data. Due to QBI-3180 (Rewrite of perm tables resolving _ID/_code dimension fields) and QBI-3123 (Calculation problems fixed in AP/AR history and snapshot tables) there is no upgrade path to BI 3.8.2/3.9 for FIN EE other than erasing all BI data and reloading from Progress.
Tables impacted:
fact_ee_ap_supplier_invoice_history
fact_ee_ap_supplier_invoice_snapshot
fact_ee_ar_customer_invoice_history
fact_ee_ar_customer_invoice_snapshot
 
QBI-3162
Financials EE History and Snapshot fact tables had some HIST load calculation errors prior to BI 3.8.2/3.9. Invoices with payments spread over multiple dates could have double counted payments or missing payments on some history and snapshot records. BI 3.8.2/3.9 fixes these calculations. Customers upgrading to BI 3.9 should run the FIN EE upgrade script, which truncates all AP and AR fact tables and perm tables and does a fresh historical reload from source data. Due to QBI-3180 (Rewrite of perm tables resolving _ID/_code dimension fields) and QBI-3123 (Calculation problems fixed in AP/AR history and snapshot tables) there is no upgrade path to BI 3.8.2/3.9 for FIN EE other than erasing all BI data and reloading from Progress.
Tables impacted:
fact_ee_ap_supplier_invoice_history
fact_ee_ap_supplier_invoice_snapshot
fact_ee_ar_customer_invoice_history
fact_ee_ar_customer_invoice_snapshot
 
QBI-3467
The Currency Conversion procedure now allows for 10 significant digits. Previously it was using the SQL Server standard for division of non-floating decimals and giving 6 significant digits. This obviously changes the measure slightly if they go through currency conversion.
Metadata (Financials SE)
QBI-3394
In fact_gl_transaction (FIN SE GL), the General Ledger transactions associated with Accounts Receivable have a valid customer dimension. Most GL transactions do not have a customer. Prior to this version, these blank customers were assigned to a 'NOT APPLICABLE' dimension row created for each source/domain combination. These are now being assigned to the zero key dimension of unknown, which should improve performance.
 
QBI-3432
In very rare instances (only 1 occurrence in 4 years), the loading of stage_ap_voucher_chk_match gets stuck in an infinite loop as it toggles back and forth between records as it keeps correcting to 0 but by so it sets a matching record not to zero. This fix resolves that very rare issue.
Design Evaluation (Financials SE)
QBI-3459
Snapshot tables should contain the dim_snapshot_period_type_key so it can be determined if the records are for a Monthly or Weekly snapshot. In certain instances, both fall on the same day. The changes for this ticket ensure that the dim_snapshot_period_type_key is included in the AR and AP snapshot tables and that the calculations for the date values follow established snapshot logic. This ensures that when month end and week_end are on the same day, both values are properly displayed.
Metadata (Multi-Module)
QBI-3077
dim_credit_terms is a slowly changing dimension and for columns driving a slow change, the prior version of the record should be end dated and a new version of the record should be created with a current start date and flagged as current.
There are a handful of exceptions for columns that should not cause a slow change or change any column at all.
The following are the rules for column changes, which is now working in the 3.9 release:
Changes to credit_term_description, credit_term_modified_date, staged_credit_term_code should only warrant an update to the existing records.
Changes to dss_extract_timestamp, dss_process_batch_id, credit_term_percent_due (the first two change every run) should not ever change data in dim_credit_term.
Changes to any other columns should warrant slowly changing dimension type changes to the record, end dating the last one and start dating the new one and flagging it as current.
 
QBI-3436
Sometimes, the project_code field was not populated properly in the dim_project dimension due to the order of tasks in the DAILY_COMMON_PROCESS_CHAINED job causing the stage_ee_project task to possibly run before the preceding stages that it required. This is now fixed so that stage_ee_project does not run until after its preliminary stages are complete.
 
QBI-3438
The GL Transaction Types XC (GL Consolidation) and PC (Periodic Costing) have been added to the dim_gl_transaction_type dimension. Any GL records with these transaction types are now linked to the matching record in dim_gl_transaction_type instead of the Unknown value.
 
QBI-3522
In the case where one domain might have a different number of financial periods in a year than other domains, the dim_date_qad corporate date fields might be incorrect for some domains.
The stage_gl_calendar_year stage was correct to include the domain in the where clause, thus correctly capturing the end-of-period date properly for each domain.
Metadata (Inventory)
QBI-3534
As an example, a customer doesn't have their 201308 (August) fiscal period open. 201307 fiscal period ends on July 31st. On July 31st, customer enters transactions with an effective_date of August 1st. Those transactions then get picked up and an attempt is made to match them to a fiscal_period. When no match is found, the fiscal period is set to 0, which gets into the fact_inv_mth_balance table and then later causes subsequent staging tasks to fail.
Even if the next daily run has the requisite fiscal_period, it does not correct the previously created 0 fiscal_period, which would need to be deleted manually.
Any fiscal periods set to 0 should be filtered out prior to getting to fact_inv_mth_balance.
This is filtered out at stage_inv_mth_balances.
Extract from fact_inv_transaction in stage_inv_trx_chg_mths1 should not only look for dss_history_date > downloadtime-process_days, but also look for effective_date > downloadtime-process days as well. The result is that if they future-date an effective date, it is still picked up at the appropriate time.
Index placed on fact_inv_transaction of effective_date and item to ensure quicker retrieval for the stage_inv_trx_chg_mths1 data grabs.
Portal
QBI-3067
In previous versions, the BI Portal did not correctly handle new (custom) columns defined with key type 2, but referencing a non-dimensional table. This is an invalid combination. When a BI Portal designer attempted to build a query against the affected table (using any columns), an empty error message was displayed.
The application now correctly processes the query, and an error message is written to the BI Portal log file.
 
QBI-3256
Prior to the BI 3.9 release, there were issues related to parameter passing between Dashboard elements and reports. Specifically, when a parameter bar on a Dashboard was used to pass selection values to a visual item (Grid, Chart, etc) and then those parameters were passed to a report, the resulting report parameter bar did not reflect the selected values. In addition, if the user modified the parameter selection and the report parameter area and then re-ran the report, the updated parameter values were not used. Both of these issues have been fixed in the current release.
 
QBI-3278
The functionality for drill-in reports has been enhanced for this release.
When a user drills in to a report from another report, the drill-in report now has a fully functional parameter bar. This provides the user the opportunity to change parameter values and re-run the report. This enhancement also allows the user to export the drill-in report to either an Excel or .pdf.