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



  •     QAD Glossary

  • 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.