Section 06
Key Concepts — Reference Notes
Core D365 and migration concepts worth understanding when planning a move from legacy Microsoft BI to Azure.
What is Dynamics 365 and how does it differ from Salesforce or SAP?
D365 is a modular cloud suite combining CRM and ERP under one roof — Salesforce is CRM-only, SAP is ERP-heavy. D365's key differentiator is deep integration with the full Microsoft ecosystem: Teams, Outlook, Azure, Power Platform, and Fabric. It's one licence relationship for the whole stack.
What is the difference between CRM and ERP in D365?
CRM modules (Sales, Customer Service, Marketing) are customer-facing — managing relationships, pipelines, and service. ERP modules (Finance, Supply Chain, Business Central) are back-office — accounting, inventory, manufacturing. Both share the same Dataverse data layer, which is a key technical advantage.
What is Dataverse?
The unified data layer beneath all D365 apps. Stores records in a consistent, secure, cloud-hosted schema. It makes sharing data between D365 apps seamless — a Sales opportunity can reference a Finance invoice with no custom integration. Synapse Link replicates Dataverse tables to Azure for analytics.
How does D365 use AI?
Microsoft Copilot is embedded across all modules — it summarises emails, suggests next steps for sales reps, predicts stock shortfalls, automates case routing, and generates marketing content. In D365 Sales, AI agents can discover leads, prioritise tasks, and automate follow-ups autonomously.
How would you migrate from SSIS / SSAS / SSRS to Azure?
SSIS → Azure Data Factory (pipelines). SSAS → Azure Analysis Services or Fabric Semantic Models (DAX carries over directly). SSRS → Power BI for dashboards plus Fabric Paginated Reports for pixel-perfect reports (same RDL format). For D365 data specifically, Dataverse Synapse Link handles ongoing replication — no SSIS extraction packages needed.
What is Qlik Replicate and why use it over ADF?
Qlik Replicate is a log-based CDC engine — it reads transaction logs from on-prem SQL Server, Oracle, or SAP and streams changes to Azure in near real-time, without impacting the source system. ADF is an orchestration tool, not a CDC tool. They complement each other: Qlik moves data out of legacy systems, ADF orchestrates what happens to it inside Azure. Post-migration, ADF's native CDC for SQL Server can replace Qlik for most scenarios.
What is the medallion architecture?
A lakehouse design pattern using three layers: Bronze (raw ingested data, no transforms), Silver (cleaned, validated, conformed data), Gold (business-ready aggregations and models for reporting). It replaces the old data warehouse ETL pattern with a more flexible, Spark-friendly approach on ADLS or Fabric OneLake.
Platform perspective: D365 works best when treated as a platform, not a single product. Understanding Dataverse, Synapse Link, and Power Platform clarifies the data engineering layer beneath the business applications.
Section 07
Enterprise BI & Supply Chain — Practical Notes
Approaches and lessons from ERP, supply chain, database migration, and BI modernisation programmes — drawn from delivery at Cathay Pacific Cargo Terminal, HSBC, CHANEL, M&S, and British Airways.
D365 F&SCM · ERP
Supply Chain & Logistics
SSIS · SSAS · SSRS
SQL Server 2016+
Azure · Power BI · Fabric
Qlik Replicate CDC
HK & India delivery
Delivery background
Overview
Enterprise delivery across aviation logistics, global banking, and luxury retail — working with operations, finance, and warehouse teams alongside technology. At Cathay Pacific Cargo Terminal: 13 years on cargo and supply chain systems at HKIA — integrations, warehouse operations, and enterprise applications. At HSBC through Capgemini: cloud data platform delivery — D365 on-prem to Azure migration using Qlik Replicate CDC, SQL Server modernisation, and Power BI for enterprise analytics. Focus areas include ERP data, supply chain KPI design, and moving legacy Microsoft BI stacks to Azure without disrupting business operations.
ERP, supply chain applications & BI modernisation
ERP and supply-chain application experience
Cathay Pacific Cargo Terminal (13 years): Cargo terminal operations at HKIA — warehouse management, cargo handling workflows, SITA/ACCS integrations, and line-of-business applications supporting inbound/outbound freight, ULD tracking, and operational reporting. This is real supply chain at scale: inventory movement, SLA-driven operations, and multi-party logistics.
HSBC (Capgemini): Global finance programme architecture. D365 on-prem ERP data extracted and replicated to Azure for analytics — finance and operational reporting across a regulated banking environment.
CHANEL HK: Enterprise architecture and data analytics framework across retail and operations systems.
M&S / British Airways: Retail supply chain and aviation logistics enterprise delivery — RFQ/RFP responses, integration platforms, and cross-functional stakeholder management.
Connect to D365: D365 Finance & Supply Chain Management is the modern cloud successor to Dynamics AX — covering procurement, inventory, warehouse management, manufacturing, and demand planning. My cargo terminal background maps directly to WMS, inventory, and fulfilment modules.
Database migration experience
At HSBC: led the technical architecture for D365 on-prem → Azure SQL migration using Qlik Replicate for log-based CDC — near real-time replication without heavy load on source systems. This is the pattern I'd reuse for any SQL Server 2016 warehouse or staging DB: assess source, replicate to Azure landing zone, validate row counts and checksums, cut over with rollback plan.
At Cathay (recent CCT programme): Oracle → Aurora PostgreSQL and on-prem → AWS migration for cargo terminal applications — same principles: discovery, parallel run, data reconciliation, phased cutover.
Key principle: Migration should not be treated as lift-and-shift. Map dependencies — SSIS packages, linked servers, cube data sources, report connections — before changing any database.
BI environment modernisation strategy
My approach is a four-layer modernisation, not a big-bang replacement:
1. Stabilise — document what exists (SSIS, SSAS, SSRS, SQL Agent jobs, file shares). Freeze scope creep. Establish a single source of truth for metadata.
2. Decouple ingestion — move ETL from SSIS to ADF/Fabric pipelines. Keep SSAS cubes running in parallel during transition.
3. Modernise the semantic layer — migrate SSAS tabular models to Azure Analysis Services or Fabric Semantic Models. DAX carries over. Retire multidimensional cubes only after tabular equivalent is validated.
4. Democratise consumption — Power BI for self-service and executive dashboards. Fabric Paginated Reports for legacy SSRS RDL reports. Synapse Link or CDC for D365/ERP ongoing replication.
At HSBC this pattern was executed: Qlik for CDC → Azure SQL → Power BI, with the legacy stack kept running until business sign-off on each report.
SSAS takeover, cube design & business logic
How did you take over SSAS with no internal backup?
A common enterprise scenario — the original developer left years ago and documentation is thin. A practical recovery approach:
Week 1 — Discovery without touching production: Export everything available via SSMS/XMLA scripts, SQL Agent job definitions, and SSIS package source. Talk to the top report consumers: "What decisions do you make from this cube?" not "What columns do you need?"
Week 2 — Reverse-engineer the model: Open the SSAS project in Visual Studio or Tabular Editor. Map measures, calculated columns, hierarchies, and KPIs. Trace each measure back to its source table/view in SQL Server. Build a measure dictionary — business name, DAX/MDX definition, source table, owner, refresh frequency.
Week 3 — Shadow and validate: Run the same queries in Excel/Power BI against the cube and against source SQL. Reconcile numbers with finance or operations. Discrepancies reveal hidden business rules (filters, unknown members, custom rollups).
Week 4 — Establish ownership: Assign a business owner per subject area (e.g. inventory, procurement, finance). Publish documentation. Set up automated XMLA backups to source control going forward.
Operating principle: Assume zero documentation on day one. Treat the running system as the spec — reverse-engineer it, validate with business users, and rebuild knowledge before changing anything.
Cube / tabular model design and business logic — real situations
Situation 1 — Conflicting KPIs: Operations reported "on-time shipment %" differently from finance's "revenue recognition date." The cube had a hidden filter on a status column that only the original developer knew about. Solution: Created a conformed date dimension with role-playing relationships (order date, ship date, invoice date) and published a KPI glossary signed off by both teams.
Situation 2 — Slow multidimensional cube: Users complained about 30-second query times on a large inventory cube. Solution: Identified high-cardinality attributes being queried at wrong grain. Introduced aggregations, partitioned the measure group by month, and migrated the heaviest subject area to a tabular model in DirectQuery mode against a pre-aggregated SQL view.
Situation 3 — Supply chain grain mismatch: Warehouse team wanted SKU-level stock; procurement wanted vendor-level spend — same cube, different grains. Solution: Designed separate measure groups at correct grains with a shared conformed product dimension. Used DAX CALCULATE with REMOVEFILTERS for cross-grain metrics rather than forcing one flat table.
Design principles I follow: Star schema first. Conformed dimensions across finance and supply chain. Measures defined once in the semantic model — never duplicated in reports. Business logic lives in the model, not in Excel.
SQL Server 2016 — options and end-of-life plan
SQL Server 2016 — what migration options would you suggest?
SQL Server 2016 extended support ends 14 July 2026 — this is urgent for any organisation still on it.
Option A — Azure SQL Managed Instance (recommended for most): Near 100% SQL Server feature compatibility. SSIS can run via Azure-SSIS Integration Runtime. Linked servers and Agent jobs supported. Lowest code change for legacy ETL and stored procedures.
Option B — SQL Server 2022 on-prem / Azure VM: If data sovereignty or latency rules out cloud. Use DMA (Data Migration Assistant) for compatibility assessment. Plan a side-by-side migration with log shipping or Always On for cutover.
Option C — Azure SQL Database: For smaller, cloud-native workloads without SQL Agent or cross-database dependencies. Cheapest but least compatible with legacy SSIS/linked server patterns.
Option D — Synapse dedicated SQL pool / Fabric Warehouse: For analytical workloads only — not OLTP. Migrate the data warehouse tier here; keep OLTP on MI or SQL 2022.
Recommendation framework: Run DMA assessment first. Classify each database as OLTP, staging/ETL, or warehouse. OLTP → MI or SQL 2022. Warehouse → Synapse or Fabric. Staging → ADF + ADLS medallion architecture. Avoid migrating everything to a single target.
SQL Server 2016 — 90-day action plan:
Days 1–30: Inventory all SQL 2016 instances (Microsoft Assessment and Planning Toolkit). Classify databases. Identify SSIS/SSAS/SSRS dependencies.
Days 31–60: Run DMA compatibility reports. Pilot one non-critical database to Azure MI. Test SSIS packages on Azure-SSIS IR.
Days 61–90: Present migration wave plan to leadership with cost, risk, and timeline per wave. Secure budget. Begin Wave 1 (dev/test environments first).
Documentation — SSAS, SSIS, and data flows
How do you create clear documentation for SSAS, SSIS, and data flows?
I use a three-tier documentation model — one page per tier, no 200-page documents nobody reads:
Tier 1 — Executive one-pager: What systems exist, what business questions they answer, who owns them, when they refresh, and what happens if they go down.
Tier 2 — Data flow diagrams (SSIS / ADF): One diagram per pipeline showing source → transform → destination. Annotate with refresh schedule, row counts, SLA, and error handling. Tool: draw.io or Visio, stored in Git alongside the code.
Tier 3 — Technical deep-dive:
• SSIS: Export packages to source control. Document each package: purpose, source connection, destination, key transforms, variables, and error flow. Auto-generate lineage using tools like Azure Purview or SSIS Documenter.
• SSAS: Measure dictionary (business name, formula, source, owner). Dimension attribute list with hierarchies. Processing schedule and dependencies. Export model via Tabular Editor to JSON in Git.
• Data flows: Medallion layer map — Bronze (raw), Silver (cleansed), Gold (business-ready). Each table: grain, primary key, refresh method (full/incremental/CDC), and downstream consumers.
Golden rule: Documentation lives in the same repo as the code. If it's in a Word doc on SharePoint, it's already out of date.
90-day approach for BI modernisation programmes
Days 1–30 · Listen & map
Stabilise & discover
Meet business owners in supply chain, finance, and warehouse ops. Inventory all SQL Server 2016 instances, SSIS packages, SSAS models, and SSRS reports. Map data flows end-to-end. Identify the top 10 reports by usage. Do not change anything yet.
Days 31–60 · Assess & pilot
Prove the path
Run DMA assessment on SQL 2016. Pilot one SSIS package in ADF. Rebuild one critical SSAS measure in a tabular model. Reconcile numbers with business. Document findings. Present a phased modernisation roadmap with cost and risk.
Days 61–90 · Plan & execute Wave 1
Deliver quick wins
Migrate dev/test SQL 2016 to Azure MI. Move 2–3 high-value reports to Power BI. Establish XMLA backups and Git-based documentation. Set up CDC for one ERP data source. Get business sign-off before touching production cubes.
Risks and mitigations
| Risk |
Impact |
Mitigation |
| No SSAS documentation or backup |
Wrong numbers after migration |
Reverse-engineer before changing. Parallel-run old vs new for 4+ weeks. Business sign-off per report. |
| SQL Server 2016 end of support (Jul 2026) |
Security/compliance breach |
Prioritise internet-facing and PCI/SOX-scoped instances first. Azure MI for fastest compliant path. |
| Hidden business logic in SSIS/cube |
Silent data corruption |
Row-count and checksum reconciliation at every layer. Automated data quality tests in ADF. |
| Users resist leaving Excel/SSRS |
Low adoption of Power BI |
Replicate familiar layouts first. Train power users. Keep SSRS running in parallel until confidence builds. |
| ERP cutover breaks CDC pipeline |
Stale analytics |
Qlik Replicate or ADF CDC with monitoring alerts. Fallback to scheduled full loads. Runbook for DBA. |
| Scope creep across supply chain modules |
Delayed delivery |
Wave-based plan: finance first, then inventory, then WMS. Fixed timelines per wave with steering committee. |
| India/HK timezone support gaps |
Long incident resolution |
Follow-the-sun runbook. Train 1–2 local engineers. Escalation matrix with SI partner (TCS/Wipro/HCL). |
Users struggling with the existing SSAS cube — your solution
If current users are struggling with the existing SSAS cube, what is your solution?
First, diagnose why they're struggling — the fix depends on the root cause:
If it's performance: Check query patterns in SSAS DMVs. Add aggregations, partition large measure groups, or migrate to tabular with columnstore compression. For real-time needs, use DirectQuery against indexed SQL views rather than processing a 50GB cube nightly.
If it's complexity (too many dimensions, confusing hierarchies): Don't fix the cube — build a thin semantic model on top with only the 15–20 measures users actually need. Deploy as a Power BI dataset with curated perspectives per role (warehouse vs finance vs procurement).
If it's tooling (Excel pivot tables are clunky): Migrate consumption to Power BI with pre-built dashboards. Keep the SSAS cube as the engine underneath initially — users get a modern UI without a risky backend change on day one.
If it's data freshness (yesterday's stock levels): Introduce CDC from ERP to staging SQL, process the cube incrementally every hour instead of nightly full refresh. Or bypass the cube for operational dashboards using DirectQuery.
If it's training (users don't understand the data): Publish a measure dictionary. Run 1-hour workshops per team. Assign a BI champion in each department.
Recommended approach: Do not rip out the cube on day one. Put a modern consumption layer in front of it — Power BI with a simplified semantic model — fix freshness with incremental processing, and migrate the engine to Azure Analysis Services or Fabric in Wave 2 once users trust the numbers.
Summary: Legacy D365 and SQL Server stacks can be stabilised, documented, and modernised in waves while keeping business operations running. At HSBC, a programme replicated on-prem ERP data to Azure with Qlik CDC and delivered Power BI for enterprise analytics. At Cathay, cargo and supply chain systems demonstrated how reporting accuracy directly affects operations — the same discipline applies when migrating off SQL Server 2016 before end of support in July 2026.