IFS Business Intelligence with Azure Synapse: A Real-World Architecture

Building business intelligence on top of an ERP system is a balance between capturing operational richness and delivering analytical clarity. IFS Cloud, with its REST-based OData API, presents a natural fit for Azure Synapse—Microsoft's unified analytics platform. Yet the path from IFS operational data to Synapse isn't straightforward. This guide walks data engineers and architects through a proven architecture for extracting IFS data, incrementally loading it into Synapse, applying dimensional modelling discipline, and surfacing insights via Power BI.

Why Azure Synapse for IFS Analytics?

IFS Cloud is designed as a modern, cloud-first ERP. Its architecture emphasizes API-first access via OData, making it an excellent candidate for cloud analytics. Azure Synapse offers three key advantages for IFS-based data warehouses:

1. Massively Parallel Processing (MPP) Architecture — Synapse dedicated SQL pools distribute storage and query work across compute nodes. That can support large analytical models, but only when distribution, table design, statistics, workload management, and capacity fit the data.

2. Unified Integration Stack — Synapse Pipelines (built on Azure Data Factory) provide orchestration, while Data Lake Storage Gen2 serves as a cost-effective staging layer. This eliminates the need for separate ETL tools and reduces operational complexity.

3. Power BI Connectivity — Power BI can use Import, DirectQuery, or composite semantic-model patterns over the Synapse SQL endpoints. Synapse workspace linking is also available in supported tenant/workspace combinations; it is a convenience, not a substitute for modelling, security, or capacity design.

For organisations running IFS Cloud, Synapse can separate analytical workloads from the operational API. MPP does not guarantee sub-second dashboards: distribution, data volume, workload class, semantic model and Power BI mode determine latency.

Architecture Overview

The architecture follows a medallion pattern (bronze → silver → gold), separating data ingestion, transformation, and presentation concerns:

IFS Cloud (OData endpoints)
        ↓
    [OData connector via ADF]
        ↓
Azure Data Lake Storage Gen2 (Bronze: Raw extract)
        ↓
[Synapse Pipelines + Spark for transformation]
        ↓
Azure Synapse Dedicated SQL Pool (Silver: Cleansed, normalized tables)
        ↓
[Star schema creation + dimensional modelling]
        ↓
Synapse Dedicated SQL Pool (Gold: Facts + Dimensions)
        ↓
Power BI (Import, DirectQuery, or a composite model over the published SQL endpoint)

Each layer serves a specific purpose: bronze ingests raw data from IFS OData feeds; silver applies validation, type consistency, and SCD (slowly changing dimension) logic; gold surfaces a business-ready star schema optimized for analytics.

Phase 1: OData Extraction & Incremental Loading

Configuring the OData Connector

IFS Cloud exposes projection services at URLs such as /main/ifsapplications/projection/v1/<Projection>.svc/<EntitySet> (the exact context/version comes from API Explorer). /fnd/api/v1/company and /person are not generic IFS Cloud endpoints. Azure Data Factory's OData/REST capabilities can ingest them, but IFS OAuth issuer compatibility, token acquisition and continuation paging must be proven for the target environment.

Connector Configuration:

• Base URL: copy the release-specific service root/entity set from IFS API Explorer
• Authentication: use a dedicated IFS confidential client and keep its secret in Key Vault; ADF managed identity can access Azure resources/Key Vault but does not automatically authenticate as an IFS user
• Timeout & Retry: set workload-tested timeouts and bounded exponential backoff for 429/eligible 5xx responses, honouring Retry-After; do not impose a universal 300-second timeout

Incremental Load Strategy

Querying an entire entity set on every run wastes capacity. Use an incremental attribute only when that exact projection exposes a filterable, trustworthy change timestamp/version. LastModifiedDate and CREATEDDATETIME are not present on “most IFS entities” by contract.

Checkpoint Pattern:

1. Store the watermark (last successfully extracted datetime) in Azure Table Storage or a Synapse table
2. Query with an overlap window and a deterministic (changed_at, business_key) ordering; OData v4 timestamps use ISO-8601 literals rather than the v3 datetime'...' wrapper
3. Follow every server-provided @odata.nextLink, merge/deduplicate by the real entity key, then advance the checkpoint only after the bronze write and validation succeed
4. For full refreshes (e.g., quarterly reconciliation), reset the watermark and re-ingest

Example OData Query:

GET /main/ifsapplications/projection/v1/<Projection>.svc/<EntitySet>?$filter=<ChangedAt>%20ge%202026-01-01T00:00:00Z&$orderby=<ChangedAt>,<Key>

Handling Soft Deletes

Do not assume IFS never physically deletes rows or that every projection supplies IsActive/Status. State transitions, cancellation and deletion semantics are entity-specific. Use a documented deletion/tombstone feed if available; otherwise run periodic key reconciliation or bounded full snapshots and define how hard deletes propagate. SCD Type 2 records attribute history—it is not itself a deletion detector.

Scheduling & Orchestration

Use Azure Data Factory's scheduling triggers (hourly, nightly) based on business cadence. For real-time demand (inventory snapshots), implement event-driven triggers via Azure Service Bus, checking for IFS-generated webhook notifications.

Illustrative starting cadence (replace after volume/SLA testing):

• Transactional facts (orders, shipments): Every 4 hours
• Master data (customers, products): Daily, with full refresh weekly
• Dimensions (calendars, hierarchies): Daily

Phase 2: Transformation & Normalization

Bronze-to-Silver Transformation

Raw OData extracts often contain redundancies, nested structures, and inconsistent naming. Spark SQL or Synapse Pipelines (using data flows) can normalize this data:

1. Type Validation — Preserve the EDM types exposed in $metadata, then validate how the selected connector serialises/materialises them before casting to warehouse types
2. Deduplication — OData pagination can overlap; remove duplicates keyed by IFS primary keys
3. Null Handling — Standardize null representations (empty strings, zeros, actual NULLs)
4. Field Mapping — Map IFS field names to business-friendly aliases

Example Spark SQL transformation:

Company, CreatedAt, ModifiedAt, Status and Name are illustrative mapped fields—inspect the selected projection's metadata. Spark SQL does not universally support a Snowflake-style QUALIFY, so the subquery keeps the example portable.

Handling Slowly Changing Dimensions (SCD)

Master data (customers, products) changes over time. Implement SCD Type 2 (effective-date versioning) for dimensions where history matters:

Run the close-and-insert steps in a controlled load transaction where the chosen Synapse engine supports it, handle multiple changes for one key in timestamp order, and retain full timestamp precision. One MERGE branch cannot both update the matched row and insert its successor.

Phase 3: Dimensional Modelling & Star Schema Design

Star Schema Principles

A star schema organizes data around fact tables (measures) surrounded by dimension tables (descriptive attributes). This design accelerates queries, simplifies business logic, and enables efficient Power BI DirectQuery.

Fact Table Design:

• Grain: Define the lowest level of detail (e.g., one row per sales order line)
• Foreign Keys: Reference dimension tables only; no direct references to operational tables
• Measures: Additive (qty sold, revenue) or semi-additive (inventory balance)
• Degenerate Dimensions: Short codes stored in facts (e.g., order_status) to avoid excessive joins

Example: Sales Fact Table

Dedicated SQL pool primary/unique constraints are informational NOT ENFORCED constraints when declared; they do not reject duplicates. Enforce the fact grain in the load/reconciliation process. Choose the hash distribution column from measured cardinality, skew, data movement, and common joins—order_id is illustrative, not a universal answer.

Dimension Tables

Dimensions provide context for facts. For IFS, typical dimensions include:

• dim_customer — Company, customer class, credit limit, industry, geographic territory
• dim_product — Item, category, subcategory, uom, cost, supplier
• dim_date — Calendar attributes (year, quarter, month, day of week, fiscal periods), holiday flags
• dim_location — Warehouse, plant, distribution center, geographic coordinates
• dim_employee — Sales rep, manager, department, cost center

Example: Customer Dimension (SCD Type 2)

Replication can be effective for a dimension small enough to copy to every distribution. A larger dimension may need hash distribution and a columnstore or clustered index instead. Size it from the loaded data and join pattern.

Conformed Dimensions

In enterprise analytics, multiple fact tables often share dimensions. A conformed dimension ensures consistent definitions across all facts. For example, a single dim_customer should be used by sales, accounts receivable, and support facts.

Define dimension governance:

• Owner: A specific team (e.g., customer data team) is responsible for dimension quality
• Approval Gates: Changes to dimension schemas require peer review
• Version Control: Track dimension schema changes in Git

Phase 4: Power BI Integration & Connectivity

DirectQuery vs. Import

Power BI offers two connectivity modes:

DirectQuery:

• Queries the Synapse warehouse directly; no data is imported into Power BI
• Best considered when the model cannot or should not be imported at the required freshness, or when consumers must query the current warehouse publication
• Trade-off: Visual latency and concurrency depend on the warehouse endpoint, semantic-model design, network, capacity, and upstream data freshness; it is not automatically real time

Import:

• Copies data into Power BI's in-memory engine, usually giving faster and more predictable visual interaction
• Best considered for models that fit the tenant/capacity limits and can meet freshness through scheduled or incremental refresh
• Trade-off: Refresh latency, semantic-model storage, processing time, and the need to reconcile late changes/deletes

Recommendation for IFS analytics: Benchmark Import first for user-facing analytical performance, then select DirectQuery or a composite model when measured size, publication latency, governance, or refresh windows require it. A large fact table does not by itself make DirectQuery faster. Aggregations and hybrid/composite patterns can keep common queries responsive while detailed rows remain at the warehouse.

Authentication & Network Security

When connecting Power BI to Synapse:

1. Governed non-personal authentication where supported

   • Use a Microsoft Entra service principal, managed identity, or delegated identity pattern supported by the selected Power BI connector/gateway and tenant configuration
   • Grant only read access to the published warehouse schemas/views
   • Store any secret/certificate in the platform credential store and define rotation; never share a developer's personal credential

2. Private connectivity

   • A Synapse managed private endpoint is created from the Synapse managed virtual network to a supported target; it is not a Power BI-to-Synapse endpoint
   • For Power BI access to a private Synapse endpoint, evaluate a VNet data gateway or Microsoft's currently supported private-link/gateway pattern for your tenant/capacity

3. Row-Level Security (RLS)

   • Define RLS rules in Power BI using DAX or the DirectQuery filter context
   • Example: relate an authorised-user/territory bridge to the model and filter its normalised user principal with USERPRINCIPALNAME(); test effective identity through the Service/gateway path

Workspace Linking

Where the feature is enabled and supported for the tenant, Synapse Studio can link to a Power BI workspace:

1. In Synapse Studio, navigate to Manage → Linked Services → Power BI
2. Link to your Power BI workspace (requires Power BI admin consent)
3. Create or open the supported Power BI semantic-model/report artefacts from Synapse Studio

The labels and prerequisites have evolved with Power BI/Fabric administration. Confirm tenant settings, workspace licensing, permissions, and the current Microsoft documentation before making this link part of the operating model.

Query Performance Optimization

DirectQuery performance depends on underlying Synapse query speed. Optimize:

1. Columnstore and Distribution Design — Clustered columnstore is a strong default for large dedicated-pool facts, but small staging tables and some point-lookup patterns may justify a heap/clustered index. Avoid issuing the following statement if the table was already created with a clustered columnstore index:

2. Statistics — Keep column statistics current (especially for filtering columns)

3. Aggregation Tables — Create pre-aggregated views for common queries

   Do not aggregate monetary measures across currency_code values. If the business needs one reporting currency, join a governed effective-dated exchange-rate contract and retain both source and converted amounts plus the rate/date used.

4. Power BI Aggregations — Tell Power BI about aggregation tables via DirectQuery Aggregations, allowing Power BI to fold low-grain queries automatically

Data Quality & Governance

Validation Rules

Implement data quality checks in the silver layer:

Run these daily via Synapse Pipelines; log results to a metadata table for monitoring.

Lineage & Documentation

Maintain a data dictionary in Synapse's Purview integration or a wiki:

Operational Considerations

Scaling & Cost Management

• Compute: benchmark a representative concurrency/data set, then choose and scale the SQL-pool/Spark/serverless capacity; DW500c is not a universal starting size
• Storage: Apply lifecycle tiers only after the business retention, query, restore-time, legal-hold, and ADLS/blob feature constraints are agreed; “archive after two years” is not a universal rule
• Scheduled Pausing: Pause a dedicated SQL pool only in a proven idle window and account for ingestion, semantic-model refresh, maintenance, recovery, and resume time; storage charges remain while compute is paused
• Azure Advisor: Monitor recommendations for cost optimization and performance tuning

Monitoring & Alerting

Set up Synapse Analytics monitoring:

1. Query Execution Metrics — Average run time, queue wait time, data scanned
2. Pipeline Failures — Alert on OData extraction failures or transformation errors
3. Data Freshness — Alert if watermark hasn't advanced in 24 hours

Use Azure Monitor + Logic Apps to send Slack/Teams notifications for anomalies.

Change Capture and Reconciliation

Cosmos DB Change Feed observes data written to Cosmos DB, not changes inside IFS, and customers cannot enable SQL Change Tracking on an IFS-managed Cloud database. For higher-frequency needs, use an IFS-published event/integration contract where the target object supports it, or a contracted replication/data service. Keep periodic API reconciliation because events are normally at-least-once and may not represent every correction/delete.

Real-World Example: Sales Order Analytics

Let's walk through a concrete implementation:

Requirement: Build a dashboard showing sales order metrics (order count, revenue, fulfillment rate) by customer, product, and month.

Steps:

1. Extract via OData

   • Discover the real order-header and line entity sets in IFS API Explorer
   • Apply an OData v4 filter such as $filter=<OrderDate> ge 2026-01-01T00:00:00Z only if that attribute/type supports it
   • Follow @odata.nextLink and join with the projection's declared keys
   • Capture the complete order-line key, customer, product, date, status/fulfilment inputs, amount, and source currency

2. Bronze Layer (Raw)

3. Silver Layer (Cleaned)

   Populate fulfillment_flag from a governed mapping of the exact IFS status values and business completion rule for the target projection. Do not infer fulfilment from display text in the report.

4. Gold Layer (Star Schema)

   Replace line_no/release_no/line_item_no with the complete key published by the selected projection; omitting part of the release-specific key can collapse distinct lines. Enforce one silver row per key before materialising #resolved_sales, tune the overlap to the observed commit delay, and run one load per checkpoint at a time. The validation rejects both missing and overlapping Type 2 dimension versions before facts are touched. The transaction then makes fact replacement and checkpoint advancement one rollback-protected publication unit. A ROW_NUMBER() recalculated on each incremental batch is not a durable surrogate key.

5. Power BI Report

   • Create measures: [Total Orders] = DISTINCTCOUNT(fact_sales[order_id])
   • [Total Revenue] = SUM(fact_sales[order_amount]), evaluated within one currency_code context; otherwise use a separately governed reporting-currency amount
   • [Average Order Value] = DIVIDE([Total Revenue], [Total Orders])
   • [Total Lines] = COUNTROWS(fact_sales)
   • [Fulfilled Lines] = CALCULATE([Total Lines], fact_sales[fulfillment_flag] = 1)
   • [Fulfillment Rate] = DIVIDE([Fulfilled Lines], [Total Lines])
   • If the KPI is order completion rather than line fulfilment, publish an order-grain completion rule; an order with one fulfilled line is not necessarily fulfilled
   • Visualize by customer, product, and month using dimension tables

Key Takeaways

1. Incremental Loading is Essential — Query large OData feeds with time-based filters; store watermarks to avoid re-processing
2. Medallion Pattern Simplifies Debugging — Separate raw ingestion (bronze), cleaning (silver), and analytics (gold) to isolate issues
3. Star Schema Accelerates Reporting — Dimensional modelling with conformed dimensions makes Power BI queries fast and consistent
4. DirectQuery Scales Differently — Unlike traditional import-based analytics, DirectQuery queries the warehouse directly; optimize columnstore indexes and statistics
5. Governance Must Be Intentional — Document lineage, implement validation rules, and assign ownership to ensure long-term data trust
6. Cost Optimization Requires Discipline — Pause unused pools, schedule maintenance windows, and monitor query performance to control Azure spend

Conclusion

Building a modern data warehouse on IFS Cloud using Azure Synapse is achievable with disciplined architecture and thoughtful data modelling. By following the medallion pattern, implementing incremental OData loads, and applying dimensional modelling principles, you create a foundation that scales from departmental analytics to enterprise-wide business intelligence. The combination of Synapse's MPP compute, Data Lake's cost-effective storage, and Power BI's interactive reporting provides a fully integrated analytics platform without the complexity of legacy data warehouse technologies.

Start small: extract a single IFS business entity (e.g., sales orders), build the fact and dimension tables, and connect Power BI. Once you've proven the pattern, expand incrementally to additional entities and use cases. This iterative approach minimizes risk and builds organizational confidence in the analytics platform.