How to query Infor Data Lake with Compass SQL
how to query infor data lake with sql
Also searched as
- infor compass sql workbench how to
- query bod data in infor data lake
- infor data lake table names sql
- compass sql infor os
Short answer
Compass is the SQL query layer in Infor OS for querying Infor Data Lake, where flattened copies of BODs published over ION are stored by Noun and Verb. Open a Compass SQL Workbench tab, browse the schema for the correct table, and run a filtered SELECT rather than pulling the full history, since Data Lake tables can hold years of append-only data.
Applies to: Infor OS Portal, Infor Data Lake, Compass, CloudSuite tenants publishing BODs to the lake
Run a SQL query against Infor Data Lake with Compass
- 1In Infor OS Portal, open Compass from your Homepages or apps list - depending on the CloudSuite product it may appear as a standalone tile or under a Data Lake grouping.
- 2Open a new SQL Workbench tab and select the catalog/schema for the data you need. Data Lake objects are generally named after the BOD Noun/Verb combination that populated them, so the schema and table names follow ION naming, not the source application's UI labels.
- 3Use the schema tree on the left to confirm the exact table and column names before writing a query - naming can differ slightly by product line and version.
- 4Write a standard SELECT statement with a WHERE clause scoped by a date range or a specific Document ID, since Data Lake tables can hold years of history and an unfiltered scan is slow and can time out.
- 5Run the query and review the result grid; large result sets are paginated, so add explicit filtering rather than expecting to pull everything at once.
- 6Save the query if it will be reused, remembering that Compass SQL is a read-only analytical layer over the lake, not a path to write data back into any source application.
- 7Export results to CSV or Excel for ad hoc analysis, or reuse the same schema as a Live Access source in Birst for a recurring dashboard instead of rebuilding the query there.
What Data Lake actually stores
Infor Data Lake holds flattened, historical copies of the BODs published across ION as applications create and update business objects. It is built for analysis and cross-application reporting, not as a live transactional store, so it is append-heavy and generally reflects what happened rather than the current live state of a record in the source app.
Finding the right table
Table and schema names follow the BOD's Noun and often the publishing application area, not the field labels a user sees in the source app's UI. Always browse the schema tree in Compass before guessing a table name, and check a sample of rows before building a query that other reports will depend on.
SELECT documentid, postingdate, amount
FROM financials.generalledgerjournal
WHERE postingdate >= '2026-01-01'
LIMIT 500
Performance basics
Filter early and narrowly. Avoid SELECT * on high-volume tables like transaction or order history, and always include a date or key filter rather than relying on the UI's pagination to make an unfiltered query feel fast. Because publishing to the lake happens after the fact, also expect some latency between an event happening in the source app and it showing up in a Compass query - do not use Compass for same-second lookups.
Compass vs Birst vs direct app reporting
Compass is the right tool for a one-off or exploratory SQL question across BOD data, especially when you already know roughly what table you need. Birst is the better choice for a recurring dashboard meant for business users, since it adds a modeled, secured, and refreshed layer on top of the same lake data. Reporting directly inside the source application is still usually faster and more current for anything that needs to reflect this second's state.
Common pitfalls
- !Treating Data Lake as real-time - there is publish latency from the source app to the lake, so it is not suited to same-second lookups.
- !Running an unfiltered SELECT * on a high-volume table like transaction history and timing out or overloading the query engine.
- !Assuming table and column names match the source app's UI field labels - they follow BOD/Noun naming instead.
- !Forgetting the append-only design means old or superseded records are not removed from the lake just because the source record changed.
- !Building a report directly against Compass instead of Birst for something that needs security, scheduling, or wide business-user access.
How an ERP-grounded AI assistant handles this
Ask ERPray a plain-English question about ledger activity or order history and, grounded on the actual Data Lake schema for that tenant, it can generate and run the underlying Compass SQL, add a sensible date filter, and explain which table it queried - useful for people who know the business question but not the BOD-based table names. It still operates within the same read-only, tenant-scoped access a human Compass user would have, and it flags when results look like they could be affected by publish latency.
Frequently asked questions
What is Infor Compass?
Compass is the SQL query workbench in Infor OS for running ad hoc SQL against Infor Data Lake, the historical store of BODs published across ION-connected applications. It is browser-based and reachable from Infor OS Portal.
Is data in Infor Data Lake real-time?
No. There is a publish delay between an event occurring in the source application and its BOD landing in Data Lake. It is well suited to historical and trend analysis but not to same-second operational lookups.
Can I write data back to my ERP through Compass SQL?
No. Compass is read-only against the lake. Any change to a source record has to go through the source application itself or an integration built for that purpose - Compass is for querying, not updating.
What SQL dialect does Compass use?
Compass supports standard ANSI-style SELECT syntax including WHERE, JOIN, GROUP BY, and LIMIT-style clauses suitable for analytical queries. Exact function support can vary by Infor OS release, so check the schema browser and test small before relying on advanced functions.
Related
Diagnose and fix a failed BOD document flow in ION Desk
A Document Flow showing Failed in ION Desk means a BOD (Business Object Document) stopped either at schema validation, at the mapping step, or at the target application. Open the failed instance's detail to see the exact failing element, fix the source data or mapping, then use Resubmit rather than re-triggering the original transaction to avoid duplicates.
How-toConnect a Birst Space to Infor Data Lake and build a report
Birst is Infor's embedded BI layer, and the recommended way to report on live Data Lake data is a Live Access source inside a Birst Space rather than a static upload. Build the logical model over a filtered set of tables, apply row-level security, and publish the dashboard to a Ming.le homepage so users reach it without leaving Infor OS.
Error fixFix a 401 Unauthorized error from Infor ION API
A 401 from ION API almost always means the credentials in the .ionapi file no longer match what ION API Gateway expects - not that the password is simply wrong. Check whether the Authorized App or service account behind the file was revoked, regenerated, expired, or issued for a different tenant, then re-test with a direct OAuth2 client_credentials call before touching any calling code.
Error fixFix a Ming.le homepage widget that will not load
A blank or endlessly spinning Ming.le widget is usually a permissions, session, or backend availability problem, not a broken widget itself. Confirm the issue is widget-specific, check the user's Security Group access to the underlying app, and look at the widget's network calls in browser dev tools to see whether it is failing with 401/403 or simply timing out against a down service.
AI for ERPNatural Language Query for ERP Data: Ask SAP, Infor, or Oracle a Question in Plain English
See how natural language query over SAP, Infor, Oracle, and NetSuite data works: grounded text-to-SQL, role-based permissions, and a visible audit trail.
AI for ERPA Private LLM Grounded on Your ERP Data
How a private LLM answers questions on your ERP data: RAG plus text-to-SQL, role-based permissions inherited from the ERP, and where each fits.
Stuck on Infor OS / Infor Data Lake?
Talk to engineers who work inside Infor OS / Infor Data Lake every week, and who build private AI that answers these questions from your own ERP data.