IFS and Power BI: Connecting Without Direct Database Access

Introduction

Many IFS Cloud implementations face a critical challenge: building governed, regularly refreshed dashboards without direct access to the underlying Oracle database. OData connectors provide a supported API-based route for pulling projection data into Power BI. Import-mode refresh is not inherently real time; freshness depends on the dataset mode, refresh schedule, source workload, and any staging layer.

This guide walks you through the entire process of connecting Power BI to IFS Cloud via OData, from initial setup through incremental refresh and star-schema design. By the end, you'll have a performant, scalable BI solution that doesn't require database passwords or direct SQL access.

---

Why OData Over Direct Database Access?

Before diving into the technical setup, let's understand why OData is the recommended approach for IFS-to-Power BI integration:

• Security: No need to distribute database credentials. OAuth 2.0 handles authentication.
• Auditability: IFS, the identity provider, gateway, and Power BI supply operational/security evidence appropriate to their configured logging; design the end-to-end audit trail rather than assuming every row read is retained forever.
• Flexibility: Access permission-controlled projections rather than raw tables, with a published metadata contract.
• Maintainability: A governed projection can absorb some database change, although projection and enumeration contracts can still change across releases.
• Compliance: Projection grants and server-side business-data access apply to the calling identity; verify the exact company/site/row scope with negative API tests.

Direct application-database access is not normally available to customers in IFS-managed Cloud and should not be designed as the reporting contract. In operating models where authorised read access exists, it still needs explicit supportability, security, workload, and lifecycle governance.

Decide Whether OData Is the Whole Pipeline

OData is an excellent contract for bounded operational reporting and incremental extraction, but a Power BI semantic model should not repeatedly scan a very large transactional entity during business hours. Make the architecture decision from measured volume and freshness requirements:

Whichever pattern you choose, IFS remains the system of record for the operational business object. The reporting store owns its extracted history and reconciliation evidence, not the authority to write changes back.

---

Understanding IFS Cloud Projections and OData

IFS Cloud exposes its data through projections—REST API endpoints that represent logical groupings of business data. These projections use the OData (Open Data Protocol) v4 standard, making them naturally compatible with Power Query in Power BI.

Key Concepts

Projections: Think of them as permission-controlled service contracts over IFS entities/actions. Names and entity sets change by installed component/release. For example, the inspected Cloud environment exposes:

• PartHandling.svc/PartCatalogSet (inventory data)

Do not extrapolate names such as OrderHandling or ReceivingHandling from a module description: those service names were not present in the inspected environment. Discover the actual order, receipt, and invoice projections in the target API Explorer.

OData Query Options: Leverage system query options to filter, sort, and paginate:

• $filter: Row-level filtering ($filter=startswith(tolower(Description),'MF'))
• $select: Column selection to reduce payload
• $orderby: Sorting for deterministic pagination
• $top: Record limit per request (critical for pagination)
• $skip: Offset for paginated requests

Finding Available Projections

1. Log into IFS Cloud.
2. Navigate to Setup > Integration > API Explorer.
3. Browse available projections with detailed documentation, request samples, and authentication requirements.
4. Copy the OData endpoint root URL (for example, https://your-ifs-host/main/ifsapplications/projection/v1/PartHandling.svc/ when that projection is installed and granted).

---

Step 1: Set Up OAuth Authentication in IFS Cloud

Power BI requires an OAuth/OIDC-capable identity path to IFS. Agree the flow first: the built-in connector commonly uses an interactive Organizational Account, while unattended refresh may require a governed gateway, dataflow, or custom connector whose confidential client is registered in IFS. A client ID/secret cannot simply be pasted into the standard OData.Feed M expression.

Create an IAM Client

1. In the IAM client administration page available in the target release, register a dedicated client for the selected flow.
2. Configure only the required grant type, redirect URI(s), audience/scope, and token lifetime. Do not enable password grant as a shortcut.
3. Keep the integration identity least-privileged in IFS: grant only the required projections/operations and business-data scope.
4. If a confidential client is used, place its secret in the connector/gateway/platform credential store and define rotation ownership. Never store it in PBIX, M source, Git, or documentation.
5. Test the same identity that Power BI Service will use; success with an administrator's Desktop session is not evidence that scheduled refresh is correctly authorised.

Find OAuth Endpoints

1. Open the issuer's /.well-known/openid-configuration document exposed for the target IFS identity setup.
2. Record its authorization_endpoint, token_endpoint, supported grants and scopes. Do not construct a Keycloak /auth/realms/... path: IFS identity routing has changed between releases and customer identity configurations.

---

Step 2: Configure the OData Connection in Power BI Desktop

Initial Connection Setup

1. Open Power BI Desktop and go to Home > Get Data > OData Feed.
2. Enter the OData service root URL:

   https://your-ifs-host/main/ifsapplications/projection/v1/PartHandling.svc/
   

3. Click OK. A navigator window shows entity sets published by that projection and visible to the authenticated identity.

Handle Authentication

Use one of these complete, governed paths; do not paste a bearer token into Web API as a substitute for an OAuth design.

Option A: Interactive Organizational Account (where the connector supports the issuer)

1. Select Organizational Account and choose Sign in.
2. Complete the interactive authorization flow for the IFS identity configuration.
3. Set the credential scope at the stable IFS OData service root rather than an individual entity URL.
4. Confirm the navigator exposes only the projections/entity sets granted to that identity.
5. Before publishing, prove that Power BI Service can bind and renew the same supported credential path; a Desktop sign-in alone does not establish scheduled-refresh support.

If the sign-in dialog cannot complete the IFS issuer's flow, stop rather than entering a token or client secret into another credential type.

Option B: Governed Connector, Dataflow, or Gateway for Unattended Refresh

If the standard OData connector's Organizational Account flow cannot use your IFS issuer, use an approved Power Query custom connector, dataflow/integration service, or gateway pattern that implements the issuer's OAuth flow and stores credentials in the platform credential store. Do not embed a client secret in M: it is stored in the PBIX/query definition, can be exposed to editors, and commonly causes scheduled-refresh/dynamic-source problems. Confirm whether the IFS client is an interactive authorization-code client or confidential service client; they are not interchangeable.

---

Step 3: Managing Large Datasets with Pagination

IFS projections can contain millions of records. Power BI has default request limits, so pagination is essential to avoid timeouts and performance degradation.

Understanding OData Pagination

OData supports $top/$skip when the service exposes them, but IFS/server-driven paging may return an @odata.nextLink. The continuation link is authoritative:

• $top=1000: Return maximum 1000 records
• $skip=2000: Skip the first 2000 records
• Combined: $skip=2000&$top=1000 returns records 2001–3000

Implementing Pagination in Power Query

OData.Feed normally follows service-provided continuations for a selected entity set. Start with the connector rather than pre-counting and synthesising offsets:

Best Practices for Pagination

• Follow @odata.nextLink unchanged if you use Web.Contents instead of OData.Feed; do not recalculate or assume a fixed page size
• Use a stable, unique $orderby only when the projection supports it; offset paging over changing data can still duplicate/skip records
• Disable parallel queries in Power BI refresh settings to avoid throttling
• Test incrementally: Verify the first 1–2 pages work before rolling out full pagination

---

Step 4: Incremental Refresh Patterns

For large entity sets, incremental refresh can reduce refresh time by reprocessing selected time partitions.

Prerequisites

Power BI incremental refresh partitions rows by the range column. It works most naturally for append-oriented facts with a stable business/event date. Your source table must have:

1. A stable Date/DateTime partition column exposed by the projection and filterable server-side
2. A refresh horizon long enough to cover measured late arrivals, corrections, failed schedules, and recovery time
3. A separate reconciliation/deletion strategy; neither a creation date nor a change timestamp necessarily captures deletes

Do not partition directly on a timestamp that changes whenever the row is edited: the same business key can move into a newer partition while its old copy remains archived. For mutable master data or updates to old facts, prefer a customer-owned staged upsert/history store, refresh the bounded dimension in full, or use a stable partition date plus Power BI's change-detection option only within a proven correction horizon.

Configure Incremental Refresh

1. In Power BI Desktop, go to Transform Data and open Power Query Editor.

2. Create two parameters: RangeStart and RangeEnd (both Date/Time type).

   • Set default values to a small recent time window (e.g., last 7 days)

3. Filter your table using these parameters. PartitionDate below represents the stable, projection-specific date chosen during design:

4. Let Power Query fold the RangeStart/RangeEnd predicate through OData.Feed where possible. OData v4 timestamp literals use ISO-8601 values; the old datetime'...' wrapper is OData v3 syntax. Prefer a folded table filter over a hand-built URL:

5. In table properties, enable Incremental refresh:

   • Right-click the table → Incremental refresh and real-time data
   • Archive data starting: for example, 2 years (retains loaded rows in that partition range; it does not manufacture change history)
   • Incrementally refresh data starting: for example, 7 days only when evidence shows that window covers late/corrected data and recovery from missed refreshes

6. Publish to Power BI Service, configure the refresh schedule separately, and perform the initial refresh (which takes longer because it loads the retained partition range). Refresh frequency and the reprocessing window solve different problems: a daily schedule does not prove that seven days is enough correction history.

Query Folding Verification

Query folding is crucial—it means Power Query pushes filters to OData, not pulling all data locally. To verify:

1. In Power Query Editor, right-click the step before your final table.
2. If View Native Query is available, inspect it; OData sources do not expose that command in every connector/version.
3. Use Power Query query diagnostics and an authorised IFS/API-gateway trace in test to confirm the $filter reached the server. Do not infer folding solely from preview speed.

---

Step 5: Designing a Star Schema for Dimensional Analysis

Raw IFS data is operational; for BI, restructure it into a star schema with fact and dimension tables.

Fact Tables

Fact tables store measurable business events (sales, shipments, inventory movements):

In Power BI:

1. Pull the order-line entity set discovered in the target tenant's API Explorer as the base; do not assume an OrderHandling.svc/CustomerOrderLineSet contract exists.
2. Add columns for Quantity, NetAmount, and DeliveryDate.
3. Create relationships to dimension tables via key columns.

Dimension Tables

Dimensions provide context (who, what, when, where):

Build dimension tables in Power BI:

1. Go to Modeling > New Table and create:

   DimDate = CALENDAR(DATE(2020,1,1), TODAY())
   

2. Add calculated columns:

   Month = FORMAT([Date], "MMM"),
   Year = YEAR([Date])
   

Relationship Best Practices

• Use surrogate keys (integers) instead of text codes for relationships
• Set "Many-to-One" for fact-to-dimension relationships
• Enable bi-directional cross-filter only when necessary (can slow queries)
• Hide OData key columns in the report layer

---

Step 6: Common Pitfalls and Solutions

Throttling and Timeouts

Problem: Power BI requests are rejected or time out.

Solutions:

• If the contract allows a client page-size preference, reduce it from the measured failing value and still follow every server-provided continuation link
• Disable parallel loading in refresh settings
• Schedule refreshes outside business hours
• Capture response status, correlation IDs, duration, concurrency, and representative query shape; then engage the relevant IFS/service owner if supported capacity or throttling needs investigation

Query Folding Breaking

Problem: Refresh times jump from seconds to minutes; a transformation step breaks folding.

Common culprits:

• Table.AddColumn with custom logic
• Table.Group or Table.Pivot
• Complex text transformations

Solution: Move non-foldable steps after initial OData load, or use native OData filters instead.

Authentication Token Expiration

Problem: Scheduled refreshes fail after initially working.

Cause: access/refresh tokens or a client secret may expire, consent can change, or the gateway/connector may not implement the selected flow correctly.

Solution:

• Use a dedicated least-privilege integration identity and the credential lifetime/rotation policy approved for the environment
• Let the supported connector/gateway refresh tokens; do not persist refresh tokens or client secrets in M
• Contact Microsoft Support if token refresh consistently fails

Large Model Size

Problem: The Power BI model grows unexpectedly, refresh becomes memory-intensive, or the selected Service capacity is exceeded.

Solutions:

• Apply $select in OData queries to fetch only needed columns
• Archive old data outside the incremental refresh window
• Split large fact tables by business function (Sales, Purchasing, Inventory)
• Use Aggregation Tables for summary visuals

Data Consistency Issues

Problem: Reports show different totals depending on refresh time.

Cause: Timezone mismatches or incomplete day data.

Solution:

• Use UTC time consistently for RangeStart/RangeEnd
• Enable "Only refresh complete days" in incremental refresh settings
• Document the refresh schedule in your data dictionary

---

Step 7: Performance Optimization Strategies

Use Column Selection ($select)

Instead of pulling all columns:

Implement Bounded Incremental Processing

For a large append-oriented fact, filter the stable partition date through the current RangeStart/RangeEnd window:

Verify folding and define deletion/reconciliation handling. Incremental refresh does not remove a source record that disappears outside the refreshed partitions. If the source exposes only a mutable modification watermark, consume it into a key-based staged upsert rather than presenting a moving timestamp as a safe Power BI partition key.

Cache Dimension Tables

Dimensions often change less frequently than facts. Reuse them through a governed dataflow, warehouse/lake layer, or an intentionally shared/composite semantic-model design supported by the organisation's Power BI architecture:

1. Extract a dimension once using the same stable key and business-scope rules as the fact pipeline.
2. Publish it through the chosen reusable data layer rather than copying slightly different M logic into every PBIX.
3. Refresh each dimension at a cadence that matches its change rate and reconciliation SLA.
4. Coordinate dimension and fact publication so facts never expose keys absent from the visible dimension version.

Aggregation Tables

For large fact tables, pre-aggregate at the data source or in Power BI:

AggOrdersByMonth:
  - Year, Month
  - OrderCount, TotalRevenue, AvgOrderValue (aggregates)

Create the fact/aggregation relationships and configure Manage aggregations (or the equivalent semantic-model feature) so Power BI knows which detail columns and aggregation functions the table represents. A relationship by itself does not guarantee automatic aggregation routing.

---

Step 8: Testing and Validation

Validation Checklist

• Sample data verification: Compare row counts and totals to IFS reports
• Completeness: Verify all required columns are present
• Relationships: Spot-check foreign keys link correctly
• Date filtering: Confirm date ranges in visuals match expectations
• Performance: Initial refresh meets the capacity-specific load window with representative history
• Incremental refresh: Routine and catch-up refreshes meet the agreed freshness SLA
• Deletes/cancellations: Source removals and lifecycle reversals reconcile correctly
• Security: An out-of-scope identity cannot extract restricted companies/sites/attributes

Query Performance Analysis

In Power BI Desktop:

1. Go to View > Performance Analyzer.
2. Click Start Recording and interact with visuals.
3. Review Query duration and storage time.
4. Optimize slow visuals by simplifying DAX or improving model design.

---

Deployment and Ongoing Operations

Publishing to Power BI Service

1. Save your PBIX file.
2. Go to File > Publish and select a workspace.
3. In semantic-model/dataflow settings, bind the supported OAuth credential and gateway/connector path designed earlier. Do not type a confidential IFS secret into an unrelated authentication field merely because it is available.
4. Set a refresh schedule (e.g., daily at 2 AM).

Monitoring

• Check Datasets > Refresh History for failures.
• Set up email alerts for refresh failures via Power BI Admin Portal.
• Document business logic and data lineage in the model description.

Versioning and Governance

• Version your PBIX files (e.g., PartMaster_v1.2_2026-04-03.pbix)
• Maintain a data dictionary listing all projections, filters, and transformations
• Select and monitor the Power BI capacity/licensing tier from measured model size, refresh concurrency, audience, governance, and service limits; Premium/Fabric capacity is not a universal prerequisite

---

Key Takeaways

1. OData is the supported API starting point for many IFS-to-Power BI models; introduce a governed staging layer when history, reuse, volume, or transformation warrants it.
2. OAuth authentication removes database passwords, but only a governed connector/gateway flow keeps client credentials out of PBIX and M.
3. Server-driven pagination is non-negotiable for large datasets; let OData.Feed follow continuations or preserve @odata.nextLink exactly.
4. Incremental refresh can cut routine load substantially when the projection supplies filterable change semantics and the query folds; it still needs deletion and reconciliation handling.
5. Star schema design separates operational data from analytical structure; build facts and dimensions intentionally.
6. Query folding is your best friend—verify it works to ensure fast, reliable refreshes.
7. Test thoroughly before rolling out to stakeholders; data quality feeds decision quality.

---

Additional Resources

• IFS Cloud API Explorer — Browse available projections
• Microsoft Power Query Documentation — M language reference
• OData Specification — Standard query options and best practices
• Power BI Incremental Refresh Guide — Official Microsoft guide
• IFS Cloud Authentication — OAuth and IAM Client setup

---

Questions or Issues?

When connecting IFS to Power BI goes sideways:

1. Check query folding first—most performance issues stem from broken folding.
2. Review supported IFS/IAM, gateway, and Power BI refresh evidence for the failed correlation/time window; page names and log access vary by release and operating model.
3. Test OData endpoints with API Explorer or an approved OAuth-capable client before configuring Power BI.
4. Leverage the IFS Community at https://community.ifs.com/ for projection-specific advice.

Building a robust BI layer on top of IFS requires methodical testing, but the payoff—governed, timely insight without distributing application-database passwords—is worth the effort.