Jet Enterprise NAV V1.13.1 Change Log
Overview:
This version provides significant performance improvements with regard to the execution of the staging database and data warehouse.
Change List:
· Dimensions
o Customer
§ Change Key from “Name” to “No”
§ Change Layout to “KeyAndName”
§ Change Name Column to “Name”
§ Add “ – “ (without the quotes) to the Key box below Name Column
o Vendors
§ Change Key from “Name” to “No”
§ Change Layout to “KeyAndName”
§ Change Name Column to “Name”
§ Add “ – “ (without the quotes) to the Key box below Name Column
o Salesperson Purchaser
§ Change key to only be “Code” (currently “Code” and “Name”)
o Posting Group
§ Rename to Inventory Posting Group
o Date
§ Changed key of Date dimension to work properly with Date filters in Excel
· Add new dimension level named “DateKey”
o Key Table = Date
o Key Field = DateValue
o Uncheck box for “Visible”
o Lay-out = Key
o Click “Advanced”
§ Set Type = Date
o Click “Ok” to close window
o Right-click “DateKey” level and select “Set Key Level”
o Deploy/execute OLAP database
· Cubes
o Accounts Receivable Cube
§ Dimensions
· Customers
o Set Dimension Relation to “Customer No” field
§ Measures
· Add “Document Count YTD” measure
o Accounts Payable Cube
§ Dimensions
· Vendors
o Set Dimension Relation to “Vendor No” field
§ Measures
· Rename all measures with (LCY) to LCY
· Added measure for “Document Count YTD”
o Purchase Cube
§ Dimensions
· Vendor – Buy From
o Set Dimension Relation to “Buy-From Vendor No” field
· Add “Line Type”
o Set Dimension Relation to “Type” field
· Add “Product Posting Group”
o Set Dimension Relation to “Gen. Prod. Posting Group” field
· Add “Business Posting Group”
o Set Dimension Relation to “Gen. Bus. Posting Group” field
· Transactional Currency
o Rename to Transaction Currency
§ Measures
· Added the following YTD measures
o Amount YTD
o Amount LCY YTD
o Quantity YTD
o Average Cost LCY YTD
o Sales Cube
§ Dimensions
· Cust - Sell-to
o Set Dimension Relation to “Sell-to Customer No” field
· Cust - Bill-to
o Set Dimension Relation to “Bill-To Customer No” field
§ Measures
· Renamed the following measures to promote consistency in naming conventions
o Sales COGS à Cost of Goods Sold
o Sales Discount à Discount Amount
o Sales Discount % à Discount %
o Sales Gross Profit à Gross Profit
o Sales Gross Profit % à Gross Profit %
o Sales Quantity à Quantity
o Sales Discount YTD à Discount Amount YTD
o Sales Discount YTD % à Discount % YTD
o Sales Quantity YTD à Quantity YTD
o COGS YTD à Cost of Goods Sold YTD
o Inventory Cube
§ Dimensions
· Add Product Posting Group dimension
o Relate through Gen. Prod. Posting Group field (added below)
· Add Business Posting Group dimension
o Relate through Gen. Bus. Posting Group field (added below)
o Sales Cube
§ Dimensions
· Customers
o Set Dimension Relation to “Customer No” field (NOTE: Must be done after adding Customer No to Finance Transactions fact table; see Data Warehouse section below)
· Add “Product Posting Group”
o Set Dimension Relation to “Gen. Prod. Posting Group” field
· Vendors
o Set Dimension Relation to “Vendor No” field (NOTE: Must be done after adding “Vendor No” to Finance Transactions fact table; see Data Warehouse section below)
· Add Salesperson dimension
o Relate through Salesperson Code field
· Data Warehouse
o “Accounts Payable Transactions” Table
§ Delete “Source No.” field (unused)
§ Delete “Vendor Name” field (unused)
o “Accounts Receivable Transactions” Table
§ Delete “Source No.” field (unused)
§ Delete “Customer Name” field (unused)
o “Finance Transactions” Table
§ Delete “GL Account Name” field (unused)
§ Delete “Customer Vendor Name” field (unused)
§ Add “Customer No” field (NOTE: Must be down after adding “Customer No” field to G/L Entry table in Staging database; see below)
§ Add “Vendor No” field (NOTE: Must be down after adding “Vendor No” field to G/L Entry table in Staging database; see below)
o Inventory Transactions
§ Delete “Item Description” field (unused)
§ Add Gen. Bus. Posting Group field from Value Entry
§ Add Gen. Prod. Posting Group field from Value Entry
o Sales Transactions
§ Delete “Sell-to Customer Name” field (unused)
§ Delete “Bill-to Customer Name” field (unused)
§ Delete “Salesperson Name” field (unused)
§ Add “Gen. Prod. Posting Group” field from Sales Line, Sales Invoice Line, and Sales Cr. Memo Line tables in stage
o Purchase Transactions
§ Delete “Buy-from Vendor Name” field (unused)
§ Delete “Purchaser Name” field (unused)
§ Remove “Purchase Line.Posting Date” link from “Posting Date” field
§ Add “Document Date” from Purchase Line in staging database to “Posting Date” field in data warehouse
§ Add “Gen. Bus. Posting Group” field from Purchase Line, Purch. Inv. Line, and Purch. Cr. Memo Line tables in stage
§ Add “Gen. Prod. Posting Group” field from Purchase Line, Purch. Inv. Line, and Purch. Cr. Memo Line tables in stage
· Staging Database
o G/L Entry Table
§ Delete “G/L Account Name” field (unused)
§ Delete “Source” field (unused)
§ Delete “Budget Name” field (unused)
§ Delete “Budget Version” field (unused)
§ Remove “Job No.” field from data source (unused)
§ Edit “Closing Entry” field to be:
· CASE WHEN DATEPART(hh,[Document Date]) = 23 THEN 1 ELSE 0 END
§ Delete “Customer/Vendor Name” field (unused)
§ Add custom field called “Customer No”
· Custom transformation: [Source No.]
o Add Condition: Source Type = 1
§ Add custom field called “Vendor No”
· Custom transformation: [Source No.]
o Add Condition: Source Type = 2
o Value Entry
§ Delete “Item Description” field (unused)
§ Edit “Posting Date from Item Ledger Entry”
· Check box for “Manual Index Creation” on red field
· Change operator from “MAX” to “Top” on green field
§ Add Gen. Prod. Posting Group field
§ Add Gen. Bus. Posting Group field
o Sales Invoice Line
§ Delete “Bill-to Customer Name” field (unused)
§ Delete “Sell-to Customer Name” field (unused)
§ Delete “Salesperson Name” field (unused)
§ Delete “Order No” field (unused)
§ Add “Gen. Prod. Posting Group” field
o Sales Cr.Memo Line
§ Delete “Bill-to Customer Name” field (unused)
§ Delete “Sell-to Customer Name” field (unused)
§ Delete “Salesperson Name” field (unused)
§ Add “Gen. Prod. Posting Group” field
o Sales Line
§ Delete “Bill-to Customer Name” field (unused)
§ Delete “Sell-to Customer Name” field (unused)
§ Delete “Salesperson Name” field (unused)
§ Add “Gen. Prod. Posting Group” field
o Purch. Inv. Line
§ Delete “Buy from Vendor Name” field (unused)
§ Delete “Purchaser Name” field (unused)
§ Add “Gen. Prod. Posting Group” field
§ Add “Gen. Bus. Posting Group” field
o Purch. Cr.Memo Line
§ Delete “Buy from Vendor Name” field (unused)
§ Delete “Purchaser Name” field (unused)
§ Add “Gen. Prod. Posting Group” field
§ Add “Gen. Bus. Posting Group” field
o Purchase Header
§ Add “Document Date” field
§ Delete “Posting Date” field (unused)
o Purchase Line
§ Delete “Buy from Vendor Name” field (unused)
§ Delete “Purchaser Name” field (unused)
§ Add “Document Date” field from Purchase Header
· Create join for “No.” = “Document No.” and “DW_Account” = “DW_Account”
§ Delete “Posting Date” field (unused)
§ Add “Gen. Prod. Posting Group” field
§ Add “Gen. Bus. Posting Group” field
o Detailed Vendor Ledg. Entry
§ Delete “ Vendor Name” field (unused)
§ Delete “Source” field (unused)
o Detailed Cust. Ledg. Entry
§ Delete “ Customer Name” field (unused)
§ Delete “Source” field (unused)
o