QAD Business Intelligence 3.11

BI 3.11 Enhancements and Fixes (1 of 10)
Component	QAD Issue	Description
Cubes, Metadata – Order Management, Portal Standard Objects – OM	QBI-4111	The OLAP Sales cube has been updated to include a measure for Order Performance. Also, the Order Line Order Type dimension has been added, and Domain has been added to the Entity dimension. The measures for existing tables also have been updated. New measures have been added and some of the more detailed cost measures have been removed. This update replaces the existing cube. If you have customized the Sales cube directly rather than making a copy and customizing that copy, you should make a copy of your Sales cube before loading the Order Management update application. The upgrade requires you to delete the Cube in Analytical Services and re-create and repopulate it in the BI DWD.
Cubes	QBI-4603	The single Sales OLAP cube has been split into five different cubes, one for each of the following fact tables: • fact_om_order • fact_om_booking • fact_om_shipment • fact_om_invoice • fact_om_performance The “old-style” cubes in the DWD have been deprecated and are no longer shipped or supported. The measures included in each cube have been streamlined to improve processing time and reduce size, but you can customize the cubes to add additional measures if required. Copy the cube to a new, custom name before modifying it to avoid having your changes overwritten when future updates are made to the standard objects.
Design Evaluation – OM, Metadata – Order Management	QBI-4364	This issue has been resolved. The invoice_number has been added to fact_om_shipment.
Design Evaluation – Operations	QBI-3214	To handle the occasional work orders that have a blank or null work order number, BI now assigns a value of NONE to those records. That value is found in both the perm_wo_ and perm_wr_ tables and in the subsequent fact tables. As work order number is part of the business key, it is critical that this not be a null value. Also, analytic tools do not have anything on which to drill down if the value is null. This also prevents issues in the perm tables where “null != null” would cause duplicates in the tables.
Design Evaluation – Operations	QBI-4428	This addresses issues with duplicate key failures during daily loads of fact_po_order_history that typically arise when there are two versions of a PO in the staging tables. The code has been changed in fact_po_order_history to properly handle the issue that was causing BI po_order_history_load to fail with duplicate key error. Typically the issue surfaces if multiple versions of a PO order/line are in the fact table and then another multiple version of the PO order/line comes in from the prior stage table.
Documentation	QBI-4426	For customers attempting to manually install BI on a split system, the BI Manual Installation instructions have been updated with a more detailed explanation about how to do this. See the appendix to QAD Business Intelligence Installation Guide for further details, but this is primarily meant for the people doing implementations.
Documentation	QBI-4533	Text was corrected in the Source Strategy (Column Description) for fact_om_invoice and fact_om_shipment tables' gross margin columns.
EAM	QBI-4829	The BI EAM Module now accommodates the ERP table name changes necessary for integrating EAM and EE. (For example: employee becomes employee_eam.) The scheduled jobs in BI detect which table name is in the EAM source without the need of a manually updated version number.
ETL Performance	QBI-3971	Extra jobs have been added to the historic load job chain that clean up all of the stage tables for each module before proceeding to the next module. This ensures that the database does not grow unnecessarily large with populated stage tables that are no longer needed for the next module being loaded. These jobs are labeled cleanup_<module>_tables. They are set up as part of the historic job chain parameter list by the initial_job_setup job.
ETL Performance	QBI-4756	An index was added to stage_om_order to improve performance and ensure that fact_om_order_history runs faster.
ETL Performance	QBI-4758	The code for stage_om_scheduled_order_perf1 was rewritten to perform more efficiently.
ETL Performance	QBI-4764	To reduce the amount of space required for a history load, some large Inventory stage tables in the hist_op_inv_process_chained job are now truncated immediately after the data is used in subsequent stages.
Metadata – Capacity Plan	QBI-4342	The dim_release_date dimension view has been corrected to have more useful, distinguishable Business Display Names, which appear in the Portal Query Designer. Previously, the column names in the table were identical to the underlying dim_date_qad dimensions from which they were derived, which made it difficult to tell different date dimensions apart when their columns were used in Portal queries.
Metadata – Capacity Plan	QBI-4344	The Routing Operation and PO Line Number attributes of the OP Work Order Routing Transaction Fact table now appear properly in the Portal Query Designer as attributes under the OP Work Order Routing dimension rather than as measures. Previously these were appearing as measures, along with the process_batch_id and extract_row_id columns. To accomplish this, the metadata flags for “Additive” were removed and “Attribute” was added for the following columns in the fact_wo_routing_transaction table: • routing_operation • po_line_number • extract_row_id • process_batch_id The extract_row_id and process_batch_id also had their End User Layer flag cleared to prevent them from being visible in the Portal because they are not generally used in Portal queries.
Metadata – Capacity Plan	QBI-4605 QBI-4665	The order of the columns in the perm_work_center_master table is now source_system_code, domain_code. Previously it was domain_code, source_system_code, which caused the index to work inefficiently and caused stages that use the perm_work_center_master table to be slower than necessary.
Metadata – Capacity Plan	QBI-5066	dim_production_line now replaces a NULL value for the production line code (the value of the ln_mstr.ln_line field) with the word NONE so the record can be handled properly. Null values in a field that is part of the business key cause duplicate errors. Previously, a null value in ln_mstr.ln_line caused a duplicate error when the record was processed in dim_production_line.
Metadata – Financials EE	QBI-3406	Set + Cursor updates were removed from Financials EE staging. This has no impact on the customer because FIN EE did not use these cursors to create new dimension keys. Any unknown fields like project or sub_account have always been set to the zero dimension key. This change improves performance and has no impact on the data.
Metadata – Financials EE	QBI-3848	Financials EE is now using the parameter defaults for cost center, project, account, and so on, that have been used in SE accounting. Unknown or empty codes are changed to the default values you selected during the installation process.
Metadata – Financials EE	QBI-4258	Set+Cursor updates removed from Financials EE staging. This has no impact on the customer as FIN EE did not use these cursors to create new dimension keys. Any unknown fields, such as project and sub_account, have always been set to the zero dimension key. This improves performance and has no impact on the data.
Metadata – Financials EE	QBI-4352	The Common Module now supports multi-source staging.
Metadata – Financials EE	QBI-4440	The Financials EE Module now supports multi-source staging.
Metadata – Financials EE	QBI-4534	The SSM Module now supports multi-source staging.
Metadata – Financials EE	QBI-4542	The Operations (OP, Purchasing and Inventory) Module now supports multi-source staging.
Metadata – Financials EE	QBI-4550	The Order Management Module now supports multi-source staging.
Metadata – Financials EE	QBI-4783	Overlapping Financial Periods in fact_ee_gl_balance (PostingHist in ERP) are now caught at the load table level instead of staging all the way to the fact table and receiving duplicate primary key errors. Installers have a difficult time cleaning up the data warehouse once the data gets through. This typically happens after an SE-to-EE upgrade, but every case has involved the Progress data being directly altered by an administrator and not as a result of an ERP code issue. The new error checking is part of the load itself so the only data that has to be deleted from BI is the load table data for that source. The only time you do not clear the whole table is on a daily run when using multi-source staging and this is not the first source loaded. Once the load table is clean and the ERP is cleaned up, the failed job can be restarted in the Scheduler without having to change any staged data in the BI data warehouse. Post Load Procedure post_load_ee_PostingHist was added to help catch errors in ERP FIN EE GL data.
Metadata – Financials EE	QBI-4832	Three items were fixed. The initial problems were: • The ar_aging_history_date column, which was set as the getdate() in the prior staging table, was introducing time stamps into the dss_history_start and dss_history_end_dates of the fact table. It should have stripped out the time stamp. • The time stamp issue was causing some updates to the table to not properly end_date records and left the future end date, plus on occasion it was setting the new start date to 3000-01-01, which was the day after the future end_date of the prior record. • In some cases, the dss_current_flag was not properly reset after a run, leaving no current record active, although the most recent version of the record still had the future end date. This update fixes all three issues.
Metadata – Financials EE	QBI-4850	Column widths were widened to handle columns where data frequently exceeded the default widths in the ERP. This should eliminate unnecessary reworking of columns sizes in BI.
Metadata Financials SE	QBI-3405	Set+Cursor updates removed from Financials SE staging. All fact tables now use “Unknown” zero-key dimension record when the dimension cannot be identified in the ERP master data. For example, if an invoice contains an item that does not appear in item master, a dim_item record is not created to simulate it. The order instead reflects the generic unknown item. This is a minor loss in functionality that creates a major performance benefit during each DAILY scheduled job run.
Metadata Financials SE	QBI-4196	Set+Cursor updates removed from Financials SE staging. All fact tables now use “Unknown” zero-key dimension record when the dimension cannot be identified in the ERP master data. For example, if a general ledger posting contains a cost center that does not appear in the master table, a dim_cost_center record is not created to simulate it. The posting instead reflects the generic unknown cost center. This is a minor loss in functionality that creates a major performance benefit during each DAILY scheduled job run.
Metadata Financials SE	QBI-4360	This issue only affects BI 3.10 with a FIN SE source fact_ap_voucher_history and fact_ap_voucher_snapshot that were incorrectly deleting the final version of some vouchers, which left a voucher in an unpaid, aged status instead of closing out as paid. This issue has been repaired and any user upgrading from BI 3.10 to BI 3.11 needs to rebuild these two tables using the upgrade job and a DAILY run afterward. New installs of BI 3.11 are already fixed.
Metadata Financials SE	QBI-4432	The Financials SE Module now supports multi-source staging.
Metadata Financials SE	QBI-4531	A new parameter of gltr_hist_lookback_days was added. This parameter limits the hist job load of load_gltr_hist_history by the number of days prior to the date of the hist run, which impacts perm_gl_transaction_hist. Default: 500000 (huge) Example: 1095 (3 years of data from date of the history load) Set this parameter before the initial history job load to limit the amount of information in: • load_gltr_hist_history • perm_gl_transaction_hist • fact_gl_transaction
Metadata Financials SE	QBI-4558	This procedure catches a small bug that occasionally generates duplicates in the fact_ar_invoice_history table. A time stamp was being added to a date instead of just the date being recorded in a dss_history_start_date record. The time stamp sometimes caused run failures because the date and datetime values did not match. This fix cleans up any records with the time stamp and prevents the time stamp from being introduced into the dss_history_start_date records on future daily runs. Important for upgrades: This change to the modified update procedure for stage_are_invc_hist_age_recalc requires additional changes after implementation if you have any custom columns on this table. Those custom columns need to be reintroduced to the procedure after the upgrade. This fix makes no changes to the table, just to the update procedure.
Metadata – Inventory	QBI-4107	The default value for the INV_BALANCE_HIST_START_DATE is now set to <customer defined. Format YYYY-MM-DD>. This needs to be set before an initial data load and is included in QAD Business Intelligence Installation Guide. (See the “Reference” chapter, Parameter Values that Need to be Set Before History Load table.) Previously this parameter defaulted to 2008-01-01 and it was not obvious that it should be changed to meet the customer’s needs for the amount of inventory history required in BI balanced against the size of the fact table.
Metadata – Inventory	QBI-4604	INV_LAST_IN_MSTR_EXTRACT has been eliminated. The parameter was used to keep in_mstr in sync with transaction history to get a precise snapshot of inventory. This in sync datetime is by source and would not work in the multi-source staging environment so it has been replaced with a direct query on in_mstr’s extract timestamp as needed.
Metadata – Inventory	QBI-4675	This issue improved Inventory performance. • stage_inv_mth_balance_hist_calc now runs in roughly half the time as before due to new supporting stage tables • stage_inv_mth_balance_hist_fiscal_period and stage_inv_mth_balance_hist_periods run a few dozen calculations to replace one calculation per inventory balance
Metadata – Inventory	QBI-4694	This issue improved Inventory performance. • custom_fact_inv_mth_balance runs in about one-third the time id did prior to applying the new supporting stage tables • stage_inv_mth_balance_hist_fiscal_period and stage_inv_mth_balance_hist_periods run a few dozen calculations to replace one calculation per inventory balance These changes also were applied to stage_mrp_calculations and stage_mrp_calculations1, but the test data is too small to show performance improvement.
Metadata – Inventory	QBI-4744	Inventory processing is now more efficient. There were no changes to data.
Metadata – Inventory	QBI-4904	In earlier versions of the code, many instances of stage tables joined to the dimension dim_item to get important information. All of that information is now grabbed in one join upfront so that less processing is required in the stage tables
Metadata – Inventory	QBI-4990	The logic for finding the end of month last cost needs to be different for AVG cost records vs. regular records. BI now filters out the use of CST-ADJ records for AVG cost method records. Although CST-ADJ records reflect the most recent cost for a correlating transaction, the effective period for the cost adjustment can be different than the effective period for the CST-ADJ record. The last non-CST-ADJ record for the fiscal period is used to calculate the last cost of the month. For regular orders, CST-ADJ records can be used.
Metadata – Multi-Module	QBI-3865	Data loads are now successful when SQL Server Native Client 11.0 is chosen as the ODBC driver for the Data Warehouse. This requires upgrading to QAD Data Warehouse Designer 6.7.4.
Metadata – Multi-Module	QBI-4106	A longstanding, minor problem with the calendar’s moving financial quarter has been fixed. dim_date_qad.moving_fin_quarter was not properly handling the year rollover. This attribute should equal “Current” for the last three financial periods. For example, on Aug. 28, the attribute is set to current for every date in June, July, and August up to and including Aug. 28. The setting of this attribute in the DAILY scheduled jobs (daily_date_roll_qad()) would only set Current back to January 1. This has been corrected.
Metadata – Multi-Module	QBI-4174	The default value for the corp_currency_code parameter is now set to <Enter 3-letter corporate currency code>. This needs to be set before an initial data load and is included in QAD Business Intelligence Installation Guide. (See the “Reference” chapter, Parameter Values that Need to be Set Before History Load table.). Previously this parameter defaulted to USD and it was not obvious that it could or should be changed to meet the customer’s needs. Incorrectly leaving it as USD if the customer mostly uses other currencies can result in slower processing due to excessive currency conversions.
Metadata – Multi-Module	QBI-4274	Two indexes have been added to dim_date_qad on current_fin_month to improve the performance of the queries used in the Portal’s CFO Dashboard. The new indexes are fin_month and dim_date_qad_key.
Metadata – Multi-Module	QBI-4361	Multi-Source Staging Structural Portion (Step 1) The INITIAL_JOB_SETUP that creates the Scheduled job list in parameters (HIST_JOB_LIST% and DAILY_JOB_LIST%) has been modified in anticipation of the full multi-source staging project in BI 3.11. This first step includes: • Moving all EAM source types to the end of the job list. • Adding CONFIRM_CONNECTION_XXXXXXXX job to the end of each PREPROCESS_[sourcename]_LOAD list. • Adding load_source_list and the functionality to manage which sources are being loaded during the DAILY load. • Making sure the TRUNCATE jobs cover all installed modules regardless of which source is loading and which modules are loading for that single source. • Making sure customized jobs added by the customer allow the maximum number of jobs to be in the daily load job list during the rebuild process in INITIAL JOB SETUP.
Metadata – Multi-Module	QBI-4485	load_ld_det now only loads if the Operations Module OP is installed. load_ld_det is a table used in Operations only. As a result, tr_hist loading has been removed from hist_op_perm_chained and daily_op_perm_chained because it is no longer needed in the OP Module. No upgrade script is associated with this change.
Metadata – Multi-Module	QBI-4493	setup_job_chain_master() has been modified to make the cleanup jobs in the hist_load_job parameter list optional. The job parameter:com_add_cleanup_jobs was added to control this option with Y/N.
Metadata – Multi-Module	QBI-4559	This fixes an update process that was updating a perm_transaction_hist table record unnecessarily in an effort to capture any changes to invoices, fsm_type, and svc_type. This change ensures that the update of the perm_transaction_table by the procedure is only for the instances where there was a change to that record. No update scripts need to be run for this fix.
Metadata – Multi-Module	QBI-4671	permsup_transaction_hist deprecated. All references to the table were replaced with new fields in perm_transaction_hist. An upgrade script was added for the Common Module. This reduces database size and reduces usage of TempDB during DAILY run.
Metadata – Multi-Module	QBI-4830	Corrected parameter default values to not include spaces after commas and updated comments in parameters to indicate that lists should not have spaces.
Metadata – Multi-Module	QBI-4831	FindModuleExists has been enhanced to handle spaces within the list of modules. Previously it would not recognize modules if there were spaces between the module designations, which could result in tables such as op_hist or tr_hist not being loaded as expected.
Metadata – Multi-Module	QBI-4905	Some duplicate tasks were cleaned up out of Scheduled Jobs. These changes have no impact to the data or run time.
Metadata – Multi-Module	QBI-4976	tr_date is now the only criteria used to search for lookback days instead of using both tr_hist and ih_hist. This change improves performance times considerably.
Metadata – Multi-Module	QBI-4991	New pre-build sql catches errors in dependent simultaneous tasks. Severed scheduled jobs were repaired and all modified jobs were put in the host script for the BI 3.11 upgrade script. Only the job task order changed in each case.
Metadata – Multi-Module	QBI-5001	Procedure admin_auto_restart_failed_jobs is now in MASTER_311.
Metadata – Multi-Module	QBI-5002	cost_method has been added from the cs_mstr.cs_mthd to dim_item table to distinguish between Standard and Average costing methods for item/site combinations. The average costed item/sites are labeled with AVG. Depending on how you have your data set up, standard may be blank or it may say “standard.” This change was especially important for the Inventory Module.
Metadata – Order Management	QBI-2214	This change ensures that commonly problematic bad dates in certain table columns get handled properly to prevent load table load failures. For example, if 07-07-0011 is tr_expire, this normally would cause the load table to get no data or cause a load failure. Now the bad date is fed into the data warehouse but converted into the DATE_EARLIEST_POSSIBLE parameter value.
Metadata – Order Management	QBI-4109	Logic in prior releases considered an order_line closed if the shipped amount was equal to or greater than the ordered amount. This did not account for the fact that an order line should not be considered closed until fully invoiced. The new logic considers a non-scheduled order to be closed in only three situations: • If the shipped amount and the invoiced amount exceed the ordered amount. • If, regardless of the invoiced or shipped amount, the record is generated only from an invoice and there is no correlating sod_det record. • If, regardless of the invoiced or shipped amount, the record is generated from an invoice and the sod_det record that exists has the column complete_state-=01. In all other cases the records are considered open.
Metadata – Order Management	QBI-4169	The BI system adds to the dim_site dimension any site codes that are referenced by item-site data, even if the site is no longer in the si_mstr table. In earlier BI versions, prior to BI 3.10, any various code values found in transactional data that were not already in the appropriate dimension (account codes, site codes, project codes), were automatically added to the dimension. This type of processing was resource-intensive and was removed from BI 3.10. However, this meant old item data that may have referenced site codes that were not longer in si_mstr would have a valid site_code but link to the “Unknown” dim_site record. This fix ensures the dim_site is updated with site codes that are referenced by any item data, even if the site_code no longer exists in the si_mstr table (the primary source for dim_site).
Metadata – Order Management	QBI-4351	The Customer Sold-To Code in fact_om_shipment table records value usually comes from the Sales Order record (so_mstr.so_cust field). However, BI sometimes gets shipment information from the Transaction Table (tr_hist) for which the corresponding sales order has been deleted. In this case, the fact_om_shipment table now displays the Customer Sold-To Code that is in the Transaction Table’s Address field (tr_hist.tr_addr). In previous releases, if the sales order was missing, the shipment record showed “Unknown” for the Customer Sold-To Code.
Metadata – Order Management	QBI-4364	The fact_om_shipment table now includes the Invoice Number associated with the Sales Order Line, if one exists in the tr_rmks field of the tr_hist record for this order line.
Metadata – Order Management	QBI-4491	Sales Account information from the invoice or sales order line tables has replaced accounting codes from invoice or sales order headers in the OM fact tables. There is an accompanying upgrade script for OM but if the account codes in OM are unimportant, the upgrade script does not need to be run.
Metadata – Order Management	QBI-4492	Scheduled orders in prior releases would extract all versions of an order on every daily run, meaning an order number that has been scheduled for 10 years would have all of its lines extracted for all of the prior 10 years of records on each DAILY extract from perm. The changes made here ensure that only the scheduled order records that have had any changes are extracted.
Metadata – Order Management	QBI-4560	Perm (as well as correlating work and extract) tables were added for the OM Module to capture the absr_det and schd_det information. The inclusion of perm_tables ensures that on the daily extract BI limits the amount of data being extracted to just changed records, not all the records in the table. This should ensure much faster daily runs for customers with scheduled orders because only relevant information is being gathered.
Metadata – Order Management	QBI-4620	This change only impacts scheduled orders in OM and should result in improved performance. The original code was doing multiple passes through the perm_transaction_history table, which contains all transactions that have ever occurred, instead of the extract_om_transaction_history table, which only contains the most recent changes. Fact tables should be populated with the same results.
Metadata – Order Management	QBI-4621	This change affects scheduled orders in OM and should result in improved performance. The original code did two select-within-a-select statements, which run inefficiently. One of these was recoded to follow normal coding standards. OM fact tables should still populate with the same results.
Metadata – Order Management	QBI-4664	permsup_om_invoice_header and permsup_om_invoice_line deprecated. All references to permsup fields have been replaced with new fields in perm tables. An upgrade script was added for the OM Module. This reduces database size and reduces usage of TempDB during DAILY run. NOTE: When upgrading from BI 3.10 to BI 3.11, it is critical that INITAL_JOB_SETUP is run again so that new CONFIRM_CONNECTION_% jobs are created and added to the DAILY job chain. This is part of the new Multi-Source Staging management system and the new jobs are required whether you are single or multi-source staging. This should be done prior to running the upgrade jobs as they also use CONFIRM_CONNECTION_%.
Metadata – Order Management	QBI-4695	The stage_om_price_list no longer fails if a non-integer value exists in the pc_mstr. pc_min_qty field. The error was due to a comparison in the ZipNumbers function that was implicitly converting a value to integer to make a comparison to zero. By quoting the 0, BI now does a string comparison that does not attempt to convert the pc_min_qty value.
Metadata – Order Management	QBI-4699	In order to constrain the amount of data in the fact tables to a certain date, for example a 2-year look back for fact tables, set HIST_FIRST_DATE_PERM parameter to a date 3 years before and HIST_FIRST_DATE_FACT to 2 years before. The perm tables should be populated with 3 years of data and the fact tables should have data starting at the 2-year date from the _FACT parameter.
Metadata – Order Management	QBI-4707	In the past, Financials EE has suffered from NULL or blank numeric fields. All known cases can be traced to conversions of SE to EE or other administrative manipulations of ERP data. BI now has several safeguards in place to turn the null/blank values to 0 or 1 depending on the situation so that calculations can be made on all existing data.
Metadata – Order Management	QBI-4741	To improve performance on stage_om_order_transaction4, where the customer has many scheduled orders, indexes were added to: • extract_om_invoice_header • extract_om_invoice_line • stage_om_order_transaction1
Metadata – Order Management	QBI-4760	POSTPROCESS_OM_SUMMARY during the DAILY job runs now takes 10-15 minutes less time than before. stage_om_order_snap2 now correctly uses the SQL Server index to make it faster.
Metadata – Order Management	QBI-4788	fact_om_order_history now runs faster and uses less temporary disk space with performance improvements that do not change the data results.
Metadata – Order Management	QBI-4811	The changes made to accommodate scheduled orders in OM ended up creating problems with regular orders in the fact_om_shipment table because there were multiple extra columns included as the business key. A change of due_date in 3.9 and 3.10 is causing the original record to remain in the table and a new record to be created, essentially creating duplicates. For non-scheduled orders, a change of due_date should change the due_date on the record, not create a new record and leave the old one. The solution was to create a composite key of the values relevant to scheduled orders and to set the composite key value for regular orders to NONE. The upgrade simply checks for the scheduled order flag = Y and builds the composite key from existing records. Otherwise if not a scheduled order it populates the new column with NONE.
Metadata – Order Management	QBI-4925	The business key for scheduled orders is different than for regular orders. This was causing problems for non- scheduled orders if things like due_date changed. (due_date is part of the scheduled_order business key but should not be for regular orders.) A new column, scheduled_order_composite_key, was added to these tables to ensure that parts needed for a scheduled order that were extras to a regular order business key, could all be in one place. For regular orders, the value in the composite key column is NONE.
Metadata – Order Management	QBI-4974	Invoice number was removed from the business key on perm_customer_consignment because that column can start out null and be populated later, creating duplicate records. That also means that two order/lines can be in the table with two different invoices and can cause duplicates when records are matched to the perm_table to find consigned orders, so the references to the perm table also do a distinct on the records to ensure that only one record is returned.
Metadata – Order Management	QBI-4978	In instances where tr_hist records have shipments for one item and the idh_hist records have a different item, the records were not going through to fact_om_shipment. This has been resolved, by not matching on item number. An extra column, discrepancy_notes, was added all the way up through to the fact table that displays the tr item and idh item if they do not match.
Metadata – Purchasing	QBI-3414	NULL values from Progress fields wo_mstr.wo_nbr and wo_mstr.wo_lot are now being filtered out of load_wo_mstr/load_wo_mstr_hist. These records are considered invalid and can cause duplicate primary keys in fact_wo_order. Users experiencing duplicate key errors in scheduled jobs DAILY_OP_WO_PROCESS_CHAINED or HIST_OP_WO_PROCESS_CHAINED may need this fix to change the “where” clause in two load tables.
Metadata – Purchasing	QBI-4348	Added additional column quantity_ordered_final to fact_po_order_history and populated it with data from the cum_quantity_received+cum_quantity_required columns from the extract_po_receipts table. Receipts take priority over POs and tr_hist data. This ensures that BI has the actual final quantity ordered in an instance when an order is partially fulfilled and the customer accepts that as the full amount. Fact PO order history now reflects both the original amount ordered and the final amount ordered (or accepted).
Metadata – Purchasing	QBI-4430	An extract join was added to query for extract_po_receipt_hist. It has been shown to boost performance on this one extract in one instance.
Metadata – Purchasing	QBI-4669	permsup_op_order_header, permsup_op_order_line, and permsup_om_receipt_hist deprecated. All references to permsup fields have been replaced with new fields in perm tables. An upgrade script was added for the OP Module. This reduces database size and reduces usage of TempDB during DAILY run. NOTE: When upgrading from BI 3.10 to BI 3.11, it is critical that INITAL_JOB_SETUP is run again so that new CONFIRM_CONNECTION_% jobs are created and added to the DAILY job chain. This is part of the new Multi-Source Staging management system, and the new jobs are required whether you are single or multi-source staging. This should be done prior to running the upgrade jobs as they also use CONFIRM_CONNECTION_%.
Metadata – Purchasing	QBI-4745	Two Purchasing stage tables used in the DAILY load were incorrectly referencing some fields from the History load tables. stage_po_order_hist_daily3 and stage_po_order_hist_daily6 have been corrected to source the tables that contain daily load data.
Metadata – Purchasing	QBI-4810	This issue was caused by multiple versions of records in a staging table being joined to even more versions in the fact table. The system was not looking for just the dss_current_flag in (‘P’,‘Y’) records and as a result, ended up with a Cartesian join on the insert. Adding the line: AND vers.dss_current_flag in (‘P’, ‘Y’) at the end of the existing vers join resolves the issue.
Metadata – SSM	QBI-4787	The load_ca_mstr table loads the ca_opn_time and ca_cls_time values into nvarchar fields. In stage_ssm_call_master the values are implicitly converted to numeric values in order to calculate the Open or Close time. These calculations are now embedded in a cas. statement to ensure that the values are actually numeric prior to attempting the calculation, because it is possible that one of these fields could contain a non- numeric character. Previously the calculation failed if a non- numeric character was encountered; now any non-numeric character is treated as 0.
Portal	QBI-3112	Some of the standard dashboards have visual items that are minimized by default. This is done to minimize system overhead when the dashboard is initially displayed. Previously, when the user restored one of these minimized visual items, the restore action did not show the visual item. However, if the user clicked the “Restore” button on a linked parameter bar, the visual item displayed correctly. This problem is now resolved. Visual items display correctly when they are restored.
Portal	QBI-3937	In QAD BI Portal reports that require a date input, there is a noticeable and significant delay between when digits for a date are entered and when they appear on screen. The fix for this is addressed in three ways: • Best practice for Report date parameters is to use lookup queries. This is now articulated in Knowledge Base AA-8643 and will be included in an update to the BI Portal User Guide. • New Validation reports use lookup queries for dates. This addresses the specific issue with GL Validation reports. • Additional indexes have been added to dim_date_qad DW, specifically in support of the CFO Dashboard. Customers can also add their own indexes, if they have specialized date lookup requirements.
Portal	QBI-3944	In the BI Portal, the indicator in the left window frame now shows as “Busy” until the report has been generated in the right window frame.
Portal	QBI-4021	In BI Portal reports, subtotals and totals could display different rounding formats than the column numbers. This happened when there were many columns on a report. This has been corrected.
Portal	QBI-4104	BI charts can be added to a dashboard within the ERP. From those BI charts, you can drill out to the BI dashboard that contains the chart. In the past, this functionality did not display the correct BI dashboard.
Portal	QBI-4270	If BI Portal version 3.10 is installed on a server that does not have the DejaVu Sans true-type font installed, when you try to run a report you get the error: Font ‘DejaVu Sans’ is not available to the JVM. See the Javadoc for more details This was resolved for the maintenance release BI 3.10A. The DejaVuSans.tff file was added to the BI Portal package and the fonts definition file was modified to reference the file.
Portal	QBI-4275	BI 3.10.0 includes new functionality in support of Single Sign On (SSO). With SSO, the BI Portal uses SAML to authenticate users of the BI Portal Client. For users who access the BI Portal from the ERP, using the ERP Dashboards, LDAP authentication is used.
Portal	QBI-4489	ResetPassword function now works as expected; a random password is generated and the correct encrypted value is stored in the database. Previously the incorrect encrypted value was being stored in the database, so the random password sent to the user did not actually allow the user to log in.
Portal	QBI-4805	The BI Portal has been modified to allow users to log in using an e-mail address. This affects both the BI Portal client and the API used by the .NetUI application. In conjunction with this modification, the BI Portal user maintenance screen now enforces unique e-mail addresses. If a customer wants to remove this constraint, this is done by setting <userEmail userEmailUnique=“false”/> in the server-config.xml file.
Portal	QBI-4982	The BI Portal can now perform user authentication against an Active Directory or other LDAP server. When LDAP authentication is enabled and the user has been configured for external authentication, the user is authenticated against the LDAP Server. No password is stored or used in the BI Portal database. This functionality requires additional setup components. See QAD Business Intelligence Portal User Guide for more information related to system setup. The User Maintenance function has also been updated to allow system administrators to maintain the data that is required for Active Directory authentication. See QAD Business Intelligence Portal User Guide for details on these columns.
Portal	QBI-5053	New functionality has been added to the BI Portal to add/update/disable users based on information in an Active Directory server. This functionality can be executed via a script or from a new Administration screen. The users to be modified are specified by Active Directory search filters. Refer to QAD Business Intelligence Installation Guide and QAD Business Intelligence Portal User Guide for detailed installation and usage information.
Portal Standard Objects – Financials SE	QBI-4254	Removed SE CFO Dashboard from Financial module’s portal standard objects. The SE CFO Dashboard was redundant; there is a CFO Dashboard that is applicable to either SE or EE Financials. In addition, the SE CFO Dashboard lacked a parameter bar but included a visual item, the Income Statement chart, that required a parameter for the Entity, resulting in a confusing display of all entities in a single chart.