How to connect Power BI to Infor VISUAL
Short answer
Connect Power BI to Infor VISUAL through its SQL Server database, ideally via a reporting copy or small warehouse, and use the on-premises data gateway for scheduled refresh in the Power BI service. Model a few core tables into a star schema, then add natural-language Q&A on top.
VISUAL ships with Crystal Reports and the usual window-based inquiries, but plants that want slicing, trend lines and shared dashboards usually turn to Power BI. The good news is that VISUAL stores its data in SQL Server, which Power BI reads natively.
The mistakes are also predictable: pointing DirectQuery at the live production database, importing wide transaction tables with no filters, and building visuals directly on raw tables. All three produce slow reports and unhappy users.
This page walks through a setup that has worked for similar systems: a reporting layer, a gateway, a compact model and a few dashboards people actually use.
What teams use it for
On-time delivery and backlog
Customer order lines by promised date, shipped date and status, with backlog by customer, part family and week.
Work order and shop loading
Open work orders by operation and resource, hours remaining against capacity, and jobs at risk of missing dates.
Purchasing and supplier performance
Open PO value, late lines by vendor, receipt variance and lead-time trends from PURC_ORDER_LINE history.
Inventory and turns
On-hand value by warehouse, aging, slow movers and turns by part class, based on inventory transactions.
Job profitability
Actual versus estimated material, labor and burden by job, where your costing setup allows meaningful comparison.
Infor VISUAL integration surface
- The VISUAL SQL Server database as the primary source. Relevant tables include CUSTOMER_ORDER, CUST_ORDER_LINE, WORK_ORDER, OPERATION, LABOR_TICKET, INVENTORY_TRANS, PURC_ORDER, PURC_ORDER_LINE, PART and VENDOR; confirm in your version.
- A reporting copy of the database, created by backup and restore, log shipping or replication, to keep analytic load off production.
- Power BI Desktop for authoring, and the Power BI service plus an on-premises data gateway for scheduled refresh when SQL Server is not internet-facing.
- Optional data warehouse or lakehouse layer, such as a separate SQL database or Microsoft Fabric, for cleaned dimensions and history.
- Existing Crystal Reports queries, useful as a reference for the business logic users already trust, such as how they define open or late.
How it works
- 1
Create a reporting source
Restore a nightly copy or set up replication to a second SQL Server. At minimum, use a read-only login on production with queries scheduled outside peak hours.
- 2
Build views for the model
Create views that join and rename tables into facts and dimensions: order lines, work order operations, PO lines, inventory transactions, with part, customer, vendor and date dimensions.
- 3
Choose Import mode first
Import with incremental refresh handles most plants. Use DirectQuery only on a reporting copy and only when near-real-time numbers are truly needed.
- 4
Install and register the gateway
Install the on-premises data gateway on a server that can reach SQL Server, register it, and add the data source with a read-only credential.
- 5
Model measures carefully
Define on-time, backlog and open quantity in DAX once, with the definitions agreed by operations. Document them inside the report.
- 6
Publish with row-level security
Limit by site, customer group or role where needed, and test with the view-as feature before sharing.
- 7
Add natural-language Q&A
Use the report's built-in Q&A or Copilot features if your licensing includes them, and add synonyms for shop vocabulary. For richer questions, add a separate chat layer over the same views.
Example flows
Nightly refresh of the delivery dashboard
Trigger: Scheduled refresh at 5:00 AM
- Gateway connects to the reporting database.
- Incremental refresh loads changed order lines only.
- Measures recompute on-time and backlog.
- Dashboard is ready for the morning production meeting.
Result: Everyone looks at the same numbers, and the production database is not touched during the day.
Planner asks a plain-language question
Trigger: Planner types 'late orders for customer Acme this month' into Q&A
- Q&A maps the words to the order line fact and customer dimension.
- It filters by promised date and status.
- A visual returns with the underlying lines available for drilldown.
Result: Faster answers for simple cuts of the data, with a clear fallback to a proper chat layer for harder questions.
Keeping data on-prem
Power BI's service is cloud-based, so refreshed data lives in Microsoft's cloud even though the source is on-prem. If that is not acceptable, Power BI Report Server is the on-premises option, with fewer features and no Fabric-based capabilities. Another route is keeping analytics local with a self-hosted tool over the same views, and putting a local chat assistant on top for natural-language questions with an open-weight model.
Security checklist
- Use a read-only SQL login for the gateway data source and store it in the gateway, not in reports.
- Apply row-level security for site or customer restrictions and test it.
- Hide cost and pay rate columns from the model unless a role needs them.
- Control workspace membership and sharing, since exports can copy data outside your controls.
- Check your tenant's region and compliance settings if data residency matters.
Ways to get it done
Direct connection to a reporting copy of SQL Server
Effort: Days to a few weeks for the first dashboards.
Smaller plants wanting dashboards quickly.
Data warehouse or Fabric layer
Effort: Weeks to months, with ongoing modeling work.
Multiple sites or systems, long history, or heavier transformation needs.
Partner support including Netray for the Q&A layer
Effort: Scoped add-on over the existing views.
You already have dashboards and want conversational access on-prem.
Pitfalls
- !Running DirectQuery against production during shifts.
- !Importing full transaction history with no incremental refresh.
- !Building measures on raw tables, so 'late' means different things on different pages.
- !Forgetting the gateway server is a single point of failure and needs monitoring.
- !Assuming Q&A understands shop vocabulary without synonyms and curated fields.
Frequently asked questions
Does Power BI have a native Infor VISUAL connector?
I would not assume one. The standard route is the SQL Server connector against the VISUAL database, usually through a reporting copy. Check with Infor or your reseller for any newer, supported analytics offerings.
Do I need the on-premises data gateway?
If your SQL Server is not reachable from the Power BI service and you want scheduled refresh online, yes. Desktop-only use does not need it, but sharing is harder.
Should I query production directly?
Avoid it where you can. A nightly restored copy or replica protects transaction performance and gives stable numbers for a day. Use production only for small, off-peak extracts.
Can I ask questions in plain English?
Power BI offers Q&A and, with the right licensing, Copilot. They work best on a clean, well-named model. For multi-step questions or document context, a separate chat layer over the same data is more flexible.
Which dashboards come first?
On-time delivery with backlog, then shop loading and purchasing lateness. These match daily meetings and are easy to validate against VISUAL screens, which builds trust in the new reports.
Want Power BI working on your Infor VISUAL?
A free 30 minute assessment: we map the integration path for your version and setup, and where it should run.
Get a free assessment