FSM Analytics

FSM-Source Space Updates

We've made significant improvements in the model space and the SMD to reduce the time it takes to refresh data loads in FSM Analytics using Birst.

Certain subject areas pulled in a full extract every time though the command was refresh.

Program/Business class/Process affected:

  • CSF-Source Space
  1. Variable

    Removed RequsitionLineDistribution_ExtractStartDate

    Added ReceivableGLDistribution_ExtractStartDate

    Type - Query

    Query - SELECT MAX([FSM Extract Variables.Extract Start Date]) FROM [ALL] WHERE [FSM ExtractVariables.Extract Object] = 'ReceivableGLDistribution'

    Return Value - Single Value

    Default Value - 2013-01-01T00:00:00.000Z

  2. Scripted Source (AR Extract Variable)

    Select Statement - Add

    union

    select 'ReceivableGLDistribution', max([Level(Incr Receivable GL Distribution.Unique ID).AR VariationID]), max([Level(Incr Receivable GL Distribution.Unique ID).Data Lake Time Stamp]), max([Level(IncrReceivable GL Distribution.Unique ID).Export Stamp]) from [Level(Incr Receivable GL Distribution.UniqueID)]

  3. Scripted Source (Clear Source Tables)

    Add these lines before END COMPLETE

    DELETE FROM [Level(Incr AP Income Withholding Code.Unique ID)] WHERE 1=1

    DELETE FROM [Level(Incr AP Vendor Withholding Code.Unique ID)] WHERE 1=1

    DELETE FROM [Level(Incr AR Customer.Unique ID)] WHERE 1=1

    DELETE FROM [Level(Incr AR Receivable Invoice.Unique ID)] WHERE 1=1

    DELETE FROM [Level(Incr AR Sales Representative.Unique ID)] WHERE 1=1

    DELETE FROM [Level(Incr IC Item GTIN.Unique ID)] WHERE 1=1

    DELETE FROM [Level(Incr PO Buyer.Unique ID)] WHERE 1=1

    DELETE FROM [Level(Incr PO Purchase Order Buyer Message.Unique ID)] WHERE 1=1

    DELETE FROM [Level(Incr PO Purchase Order Line Distrib Detail.Unique ID)] WHERE 1=1

    DELETE FROM [Level(Incr PO Purchase Order Receipt Line.Unique ID)] WHERE 1=1

    DELETE FROM [Level(Incr PO Purchase Order Receipt.Unique ID)] WHERE 1=1

    DELETE FROM [Level(Incr PO Supplier.Unique ID)] WHERE 1=1

    DELETE FROM [Level(Incr PO Vendor Item.Vendor Item Unique ID)] WHERE 1=1

    DELETE FROM [Level(Incr Receivable GL Distribution.Unique ID)] WHERE 1=1

    DELETE FROM [Level(Incr RQ Requesting Location.Requesting Location Unique ID)] WHERE 1=1

    DELETE FROM [Level(Incr TX Tax Code.Unique ID)] WHERE 1=1

    DELETE FROM [Level(Incr TX Tax Entity.Unique ID)] WHERE 1=1

    DELETE FROM [Level(Incr TX Tax Transaction.Unique ID)] WHERE 1=1

    DELETE FROM [Level(Incr TX VAT Reporting Line.Unique ID)] WHERE 1=1

    DELETE FROM [Level(Source Buyer.Unique ID)] WHERE 1=1

    DELETE FROM [Level(Source Customer.Unique ID)] WHERE 1=1

    DELETE FROM [Level(Source Item GTIN.Unique ID)] WHERE 1=1

    DELETE FROM [Level(Source PO Line Distribution Detail.Unique ID)] WHERE 1=1

    DELETE FROM [Level(Source Purchase Order Buyer Message.Unique ID)] WHERE 1=1

    DELETE FROM [Level(Source Purchase Order Receipt Line.Unique ID)] WHERE 1=1

    DELETE FROM [Level(Source Purchase Order Receipt.Unique ID)] WHERE 1=1

    DELETE FROM [Level(Source Purchasing Company.Purchasing Company Unique ID)] WHERE 1=1

    DELETE FROM [Level(Source Receivable GL Distribution.Unique ID)] WHERE 1=1

    DELETE FROM [Level(Source Receivable Invoice.Unique ID)] WHERE 1=1

    DELETE FROM [Level(Source Requesting Location.Requesting Location Unique ID)] WHERE 1=1

    DELETE FROM [Level(Source Sales Representative.Unique ID)] WHERE 1=1

    DELETE FROM [Level(Source Supplier.Unique ID)] WHERE 1=1

    DELETE FROM [Level(Source Tax Code.Unique ID)] WHERE 1=1

    DELETE FROM [Level(Source Tax Entity.Unique ID)] WHERE 1=1

    DELETE FROM [Level(Source Tax Transaction.Unique ID)] WHERE 1=1

    DELETE FROM [Level(Source VAT Reporting Line.Unique ID)] WHERE 1=1

    DELETE FROM [Level(Source Vendor Item.Vendor Item Unique ID)] WHERE 1=1

    DELETE FROM [Level(Source Vendor Withholding Code.Unique ID)] WHERE 1=1

    DELETE FROM [Level(Source Income Withholding Code.Unique ID)] WHERE 1=1

  4. Scripted Source (OE Customer Order)

    Select Statement - Add to end of select items

    [Level(Source Customer Order.Unique ID).ShippingMethod],

    [Level(Source Customer Order.Unique ID).TransactionId]

    Add to Script (before // Check that the value is a timestamp

    [Shipping Method]=[Level(Source Customer Order.Unique ID).ShippingMethod]

    [Transaction ID]=[Level(Source Customer Order.Unique ID).TransactionId]

  5. Scripted Source (OE Customer Order Return Line)

    Add to Script (before // Check that the value is a timestamp

    [Insurance Total]=[Level(Source Customer Order Return Line.Unique ID).InsuranceTotal]

    [Invoice Catch Weight Quantity]=[Level(Source Customer Order Return Line.UniqueID).InvoiceCatchWeightQuantity]

  6. Scripted Source (PO Extract Variables)

    Select Statement

    UNION

    SELECT 'VendorItem', MAX([Level(Incr PO Vendor Item.Vendor Item Unique ID).PO Variation ID]),MAX([Level(Incr PO Vendor Item.Vendor Item Unique ID).Data Lake Time Stamp]), max([Level(IncrPO Vendor Item.Vendor Item Unique ID).Export Stamp]) FROM [Level(Incr PO Vendor Item.VendorItem Unique ID)]

  7. Scripted Source (Reset Variables)

    Add before END COMPLETE

    [Extract Start Date] = FORMAT(DATETIME(GETVARIABLE('AllExtractDate')),'yyyy-MMdd\'T\'HH:mm:ss.SSS\'Z\'')

    [Last Update] = NOW

    [Dummy Date] = NOW

    [Extract Rep Set] = 'AR'

    [Extract Object] = 'ReceivableGLDistribution'

    WRITERECORD

    [Extract Start Date] =FORMAT(DATETIME(GETVARIABLE('AllExtractDate')),'yyyy-MM-dd\'T\'HH:mm:ss.SSS\'Z\'')

    [Last Update] = NOW

    [Dummy Date] = NOW

    [Extract Rep Set] = 'PO'

    [Extract Object] = 'VendorItem'

    WRITERECORD

  8. Raw Source (FSM_BankStatementLine)

    BankReference - increased to varchar(80)

  9. Scripted Source (CB Bank Statement Line)

    BankReference - increased to varchar(80)

  10. Raw Source (FSM_ContractTierQualifier)

    ContractTier - increased to varchar(20)

  11. Scripted Source (PO Contract Tier Qualifier)

    Contract Tier - increased to varchar(20)

  12. Raw Source (FSM_ContractTierMember)

    ContractTierMemberRequestingLocation - increased to varchar(20)

    ContractTierMemberLocation - increased to varchar(20)

  13. Scripted Source (PO Contract Tier Member)

    Requesting Location - increased to varchar(20)

    Location - increased to varchar(20)

  14. Raw Source (FSM_Supplier)

    SupplierName - increased to varchar(120)

  15. Scripted Source (PO Supplier Name)

    Supplier Name - increased to varchar(120

  16. Raw Source (FSM_PayablesInvoice)

    InvoiceSearchDocument - increased to varchar(2048)

  17. Raw Source (FSM_Item)

    TextSearch - increased to varchar(2048)

  18. Scripted Source (IC_Item)

    Text Search - increased to varchar(2048)

JT-1624261

FSM-Source-GL Space Updates

We've made significant improvements in the model space and the SMD to reduce the time it takes to refresh data loads in FSM Analytics using Birst. Certain subject areas pulled in a full extract every time though the command was refresh.

Program/Business class/Process affected:

CSF-Source-GL

Scripted Source ( Clear Source Tables)

Added these lines before END COMPLETE

DELETE FROM [Level(Incr GL Accounts.Unique ID)] WHERE 1=1

DELETE FROM [Level(Incr GL Chart of Accounts Hierarchy.Unique ID)] WHERE 1=1

DELETE FROM [Level(Incr GL General Ledger Chart Account.Unique ID)] WHERE 1=1

JT-1649711

FSM-Model-FIN Space Updates

We've made significant improvements in the model space and the SMD to reduce the time it takes to refresh data loads in FSM Analytics using Birst. Certain subject areas pulled in a full extract every time though the command was refresh.

Program/Business class/Process affected:

  • CSF-Model-FIN
  • Scripted Source (Bank Statement Line)
  • BankReference - increased to varchar(80)

Use these steps to test the enhancement:

JT-1653205

FSM-Model-ORD Space Updates

We've made significant improvements in the source and models spaces as well as the SMD to reduce the time it takes to refresh data loads in FSM Analytics using Birst. Certain subject areas pulled in a full extract every time though the command was refresh.

Program/Business class/Process affected:

This is the code that has been updated in 2021.10.00.

If you want to apply the changes manually to a customized space you now have the code.

CSF-Model-ORD Space

In Scripted Source (Franchise Contract Charge), Set Snapshot Policy to Incremental Refresh and delete key to FranchiseContractChargeKey.

In Scripted Source (Franchise Sales), Set Snapshot Policy to Incremental Refresh and delete key to FranchiseSalesKey.

Dependencies: This change is available in the Financials & Supply Management Analytics using Birst release 2021.10.00.

JT-1649714

FSM-Model Space Updates

We've made significant improvements in the model space and the SMD to reduce the time it takes to refresh data loads in FSM Analytics using Birst. Certain subject areas pulled in a full extract every time though the command was refresh.

Program/Business class/Process affected:

CSF-Model Space

A. Variables

Added ItemQtyByDay_History_Months - Constant – 6

Added ItemQtyByDay_StartDate - Query - SELECT DATEADD(DAY, -31,DATETIME(FORMAT(DATEADD(Month, ((-1) *INTEGER(GETVARIABLE('ItemQtyByDay_History_Months'))), NOWDATE),'YYYY-MM-01'))) FROM[ALL]

B. Scripted Source (Next Tran Date For Item Loc)

Added if statement to filter to range based on window size variable

/* Limit Range of Item By Day to History Window and a further 31 days */

IF MAX([Level(ITL Attributes.ITL Attributes).Transaction Date]) >= DATEADD(DAY, -31,DATETIME(FORMAT(DATEADD(Month, ((-1) *INTEGER(GETVARIABLE('ItemQtyByDay_History_Months'))), NOWDATE),'YYYY-MM-01'))) THEN

END IF

C. Scripted Source (Item Quantity By Day)

Select Statement - added to where clause

AND DATEDIFF(Day, [Level(ITL Attributes.ITL Attributes).Transaction Date],DATETIME(GetVariable('ItemQtyByDay_StartDate'))) <= 0

Line 521 - added if logic to only start with first day of the

IF [v_Last_TranDate] >= DATETIME(FORMAT(DATEADD(Month, ((-1) *INTEGER(GETVARIABLE('ItemQtyByDay_History_Months'))), NOWDATE),'YYYY-MM-01')) THENTHEN

WRITERECORD

END IF

Line 879

Logic to filter start of range

IF [v_tmp_TranDate] >= DATETIME(FORMAT(DATEADD(Month, ((-1) *INTEGER(GETVARIABLE('ItemQtyByDay_History_Months'))), NOWDATE),'YYYY-MM-01')) THEN

WRITERECORD

END IF

D. Scripted Source (Contract Tier Qualifier)

Contract Tier - increased to varchar(20)

E. Scripted Source (Contract Tier Member)

Requesting Location - increased to varchar(20)

Location - increased to varchar(20)

F. Scripted Source (Supplier)

Supplier Name - increased to varchar(120)

G. Scripted Source (Item)

Text Search - increased to varchar(2048)

JT-1649712

Updating Model relationship diagrams

We've added Model Relationship Diagrams in our User Guides. These diagrams will be found at the opening of each subject area chapter in the Financials & Supply Management using Birst User Guide Version 2021.10.00

Dependencies:

This change is available in the Financials & Supply Management User Guide release 2021.10.00. You can also find this information in KB Articles 2212118 (this release) and 2207913 (all releases).

JT-1631706

Incorrect Quantity measure used in Inv Transactions Adhoc measure dropdown list filter.

An Error is encountered when selecting Quantity as a measure: "A query or expression contains an unknown column. Please contact your administrator. Transaction Date: Sum: Quantity"

Upon checking, Measure filter is using 'Quantity', it should be using 'ICT Quantity' measure from Inventory Transactions.

Program/Business class/Process affected:

  • Inventory Transactions Crosstab Adhoc

    Embedded Filter/s with issue: Measure

  • Inventory Transactions Comparison Adhoc

    Embedded Filter/s with issue: Measure 1, Measure 2

Use these steps to test the enhancement:

JT-1612394

Requisitions by Vendor needs (missing) filtered out

The report 'Requisitions by Vendor' had included the vendor ' Missing" which included the volume of requisitions that were not sourced from a Vendor. We updated the report and filtered out the lines where the Vendor is 'Missing".

JT-1605400

New Widget "Off Contract Req Lines" for Req Activity Analysis

A new widget has been added - Off Contract Req Lines. It's on the Requisition Activity Analysis dashboard. It ranks Off Contract requisition lines by Vendor.

Dependencies: This change is available in the Financials & Supply Management Analytics using Birst release 2021.10.00

JT-1633525

PO Receipt Analysis - unused/blank embedded filter exists

Removed the unused embedded filter for PO Receipts Analysis dashboard in MSCM-RAD.

JT-1638868