Dynamics 365 Analytics

Enterprise Data Warehouse for D365FO

Data Warehouse Solution

What is Dynamics 365 Analytics?

Dynamics 365 Analytics is an enterprise-grade data warehouse solution built on the Dynamics 365 Connector and powered by the dbt (data build tool) framework. It transforms raw Dynamics 365 Finance and Operations data into clean, modeled, and analysis-ready datasets across your entire organization.

How It Works

1
Dynamics 365 Connector

Seamless integration with Dynamics 365 Finance and Operations to extract transactional data

2
dbt Framework

Modular SQL transformations with built-in testing, documentation, and version control

3
Data Warehouse Layer

Production-ready dimensions and facts organized by business domain

Coverage Summary

57
Models Deployed
8
Business Domains
31
Dimensions
27
Facts

Database: 5400_dwh | Refresh Frequency: Daily | Status: Production Ready

Business Process Coverage

  • ORDER-TO-CASH: Customer → Sales Order → Invoice → Payment → Settlement
  • PROCURE-TO-PAY: Vendor → Purchase Order → Receipt → Invoice → Payment
  • INVENTORY: Product → Site → Warehouse → Daily Summary → Cost Tracking
  • TRANSFER ORDERS: Source Warehouse → Transfer Order → Transit → Destination
  • PRODUCTION: Production Order → Cost Calculation → Execution → Completion
  • SHIPPING: Voyage → Port → Delivery Tracking → Settlement

Overview

Complete sales and customer analytics spanning from customer master data through transaction settlement.

Models (11 total)

Type Model Name Description
DIM dim_customer Customer master data and attributes
DIM dim_customer_invoice Invoice header details
DIM dim_sales_order Sales order header information
DIM dim_sales_channel Sales channel definitions
DIM dim_sales_commission_group Commission group definitions
DIM dim_sales_pool Sales pool groupings
DIM dim_sales_return_reason Return reason codes
FCT fct_sales_order Sales order line details and metrics
FCT fct_customer_invoice Customer invoice details
FCT fct_customer_transaction Customer financial transactions
FCT fct_customer_transaction_with_settlement Customer transactions with settlement

Overview

Comprehensive inventory management covering products, warehouses, and daily inventory tracking with cost calculations.

Models (15 total)

Type Model Name Description
DIM dim_product Master product information with style, colour, size
DIM dim_site Manufacturing/distribution sites
DIM dim_warehouse Warehouse locations and details
DIM dim_inventory_journal Inventory journal dimensions
DIM dim_transfer_order Transfer order header information and status
DIM dim_uom Unit of measure master data and conversions
FCT fct_inventory_summary_daily Daily inventory positions and values
FCT fct_inventory_cost_daily Daily inventory cost calculations
FCT fct_inventory_journal Inventory adjustment transactions
FCT fct_inventory_order Inventory order line details
FCT fct_product_barcode Product barcode associations
FCT fct_product_released Released product variants
FCT fct_transfer_order Inter-warehouse transfer order transactions
FCT fct_uom_conversion Unit of measure conversion rates and rules

Overview

End-to-end procure-to-pay processes including vendor management, purchase orders, and invoice matching.

Models (9 total)

Type Model Name Description
DIM dim_vendor Vendor master data and classifications
DIM dim_purchase_order Purchase order header information
DIM dim_vendor_invoice Vendor invoice header details
FCT fct_purchase_order Purchase order line details
FCT fct_purchase_order_receipt Goods receipt transactions
FCT fct_purchase_order_delivery_remainder Outstanding delivery tracking
FCT fct_vendor_transaction Vendor financial transactions
FCT fct_vendor_transaction_with_settlement Vendor transactions with settlement
FCT fct_vendor_invoice Vendor invoice transaction details

Overview

Complete financial analytics including general ledger, pricing, charges, and multi-currency exchange rates.

Models (12 total)

Type Model Name Description
DIM dim_charge Charge code definitions
DIM dim_general_ledger_account Chart of accounts with types and categories
DIM dim_currency Currency master data
DIM dim_cost_group Cost grouping for BOM production
DIM dim_costing_version Costing version definitions and restrictions
FCT fct_item_price Item-specific pricing information
FCT fct_charge_transaction Charge transaction details
FCT fct_general_ledger General ledger transaction entries
FCT fct_exchange_rate Historical exchange rates
FCT fct_exchange_rate_today_global_default Current default exchange rates

Production & Manufacturing (3 models)

  • dim_production_order: Production order master data
  • fct_production_order: Production order execution data
  • fct_production_order_price_calculation: Production cost calculations

Shipping & Logistics (3 models)

  • dim_voyage: Voyage master data with vessel and booking information
  • dim_shipping_port: Port definitions and locations
  • fct_voyage: Voyage line item details

Organisation & Master Data (4 models)

  • dim_company: Company/legal entity information
  • dim_address: Address master data
  • dim_worker: Employee/worker information
  • dim_country_region: Country and region master data

Time Dimensions (2 models)

  • dim_date: Date dimension with calendar attributes
  • dim_time: Time dimension for intraday analysis
✓ Multi-Company Support
Handle multiple legal entities with proper segregation
✓ Multi-Currency
Exchange rates and currency conversion across all transactions
✓ Daily Refresh
Most operational data refreshed daily for timely insights
✓ Data Quality
Comprehensive testing and validation on all models
✓ Inventory Tracking
Daily inventory positions and cost calculations
✓ Global Trade
International shipping, voyages, and logistics tracking
✓ Full Traceability
Complete order-to-cash and procure-to-pay processes
✓ dbt-Powered
Modular, tested, and documented data models

Data Quality & Integrity

  • Referential Integrity: All dimensions include 'Unknown' and 'N/A' records
  • Comprehensive Testing: Data quality validations on all models
  • Unique Key Management: MD5 hash-based surrogate keys
  • Soft Delete Support: Logical deletion tracking with timestamps

Multi-Entity Support

  • Multi-Company: Support for multiple legal entities
  • Multi-Currency: Exchange rate management and currency conversion
  • Multi-Location: Site, warehouse, and address hierarchies
  • Multi-System: Integration with D365FO and D365CE

Refresh Strategy

  • Daily Refresh: Most operational data refreshed daily
  • Incremental Loading: Optimised incremental strategies for large fact tables
  • Near-Real-Time: More frequent refreshes can be configured

dbt Implementation

  • Modular Design: Well-organized model structure by business domain
  • Testing Framework: Comprehensive data quality tests on all models
  • Documentation: Full model lineage and descriptions
  • Version Control: Complete git history and change tracking

For Business Users

  • Use dimension tables for filtering and grouping data
  • Join fact tables to dimensions using the *_key fields
  • Leverage comprehensive time dimensions for temporal analysis
  • Utilise multi-currency facts for global reporting

For Developers

  • Follow established naming conventions (dim_* and fct_*)
  • Implement proper referential integrity with Unknown/N/A handling
  • Use standard metadata fields (jca_dbt_created_at, jca_dbt_updated_at, jca_dbt_is_deleted)
  • Leverage existing test frameworks for data quality validation

Performance Optimisation

  • Models are configured with appropriate sort keys
  • Incremental loading strategies implemented for large fact tables
  • Daily refresh schedule balances freshness with performance
  • Partitioning strategies optimise query performance

Summary: Complete breakdown of all 57 data models across 8 business domains, with dimension and fact counts.

Domain Coverage

Category Dimensions Facts Total
🛍️ Sales & Customer 7 4 11
📦 Product & Inventory 6 9 15
🔄 Purchase & Procurement 3 6 9
🏭 Production & Manufacturing 1 2 3
💰 Finance & Pricing 7 5 12
🚢 Shipping & Logistics 2 1 3
🏢 Organisation & Master Data 4 0 4
📅 Time Dimensions 2 0 2
TOTAL 31 27 57

Ready to implement a comprehensive data warehouse?

Contact JayConsulting to learn how our analytics solution can transform your data strategy.

Schedule a Consultation