How to connect Power BI to Macola
Short answer
Connect Power BI to Macola by pointing it at read-only SQL views (Macola 100 on SQL Server), the Progress ODBC driver for older lines, or a nightly warehouse copy. Use an on-premises data gateway for scheduled refresh, then build dashboards for inventory, open orders, late POs and margin.
Macola sites have usually lived on Crystal Reports and Excel for years. Power BI is the natural next step because it reuses the same data, adds interactivity, and slots into the Microsoft licenses many companies already own. The challenge is data shape: Macola tables are transactional and not designed for analysis.
Connecting is the easy part. The work that determines whether anyone trusts the dashboards is modeling: defining what counts as an open order, how to treat unposted batches, and which date drives each report. Do that once, in the warehouse or semantic model, not in each report.
Once the model exists, adding natural-language questions on top becomes practical, because the AI is querying defined measures instead of guessing at raw tables.
What teams use it for
Inventory and turns dashboard
On-hand value, days of supply, slow movers and obsolescence candidates by location, using inventory and usage history.
Open order backlog and on-time delivery
Backlog by customer and ship week, plus promised versus actual ship dates to measure delivery performance.
Purchasing: late POs and supplier scorecard
Late lines, receipt variance and lead time actual versus planned by vendor.
Job cost and margin
Estimated versus actual cost by job, with material, labor and overhead split, where your Macola modules track it.
AR aging and cash outlook
Aging buckets, top overdue customers and expected receipts by week for the finance team.
Macola integration surface
- SQL Server direct (Macola 100): Power BI connects natively. Use read-only views and avoid querying base tables during business hours at scale.
- ODBC to a Progress database (older Macola lines): a vendor or third-party ODBC driver is typically required. Check driver bitness and licensing on the gateway machine.
- Warehouse copy: a nightly or hourly extract into a separate SQL database lets you reshape tables and keeps reports off the production server.
- On-premises data gateway: required for scheduled refresh from the Power BI service to a server on your network. Install it on a stable server, not a laptop.
- Crystal Reports definitions: they encode existing business logic and are a good source for measure definitions to replicate.
How it works
- 1
Choose direct, DirectQuery or warehouse
Import mode with a warehouse is the best default. DirectQuery against production can slow transactions and is harder to model.
- 2
Build the staging views or warehouse tables
Create clean fact tables for orders, shipments, POs, receipts, inventory and ledger, plus dimension tables for items, customers and vendors.
- 3
Install and register the data gateway
Install on a domain server, register with your Microsoft tenant, add data sources and set credentials for a read-only account.
- 4
Define measures once
Write DAX measures for open backlog, on-time delivery and days of supply with finance and operations sign-off.
- 5
Build the first three dashboards
Start with inventory, backlog and purchasing, validate against Macola screens for a sample period, then release.
- 6
Schedule refresh and monitor
Set refresh windows outside posting peaks, and alert on failures.
Example flows
Nightly refresh into the backlog dashboard
Trigger: 2 AM scheduled job.
- ETL copies changed order and shipment rows to the warehouse.
- Gateway refreshes the Power BI dataset.
- Dashboard recalculates backlog and on-time delivery.
- Failures email the BI owner.
Result: Sales sees a current backlog at 7 AM without touching the production server.
Natural-language question on the model
Trigger: Planner asks 'which vendors were late most often last quarter?'
- Question is sent to a Q&A layer that knows the semantic model.
- The layer maps to the late-line measure and vendor dimension.
- Result returns as a table and a chart, with the measure definition shown.
Result: An answer that matches the dashboard number because both use the same measure.
Keeping data on-prem
Power BI itself is a cloud service, but the data does not have to be exposed. The on-premises data gateway makes an outbound connection, so you do not open inbound ports to the Macola server, and only the imported dataset lives in the service. If policy forbids that, Power BI Report Server is the on-prem option with its own licensing and feature limits, so check what you need. For natural-language questions on restricted data, run an open-weight model locally that queries the same warehouse and semantic definitions, rather than sending rows to a cloud service. Keep the warehouse database on the same network as Macola for fast, safe refresh.
Security checklist
- Use a dedicated read-only database account for the gateway, with access to views only.
- Apply row-level security in the model for users who should see only certain plants or customers.
- Remove cost and margin from datasets shared with sales or external viewers.
- Limit who can publish and share workspaces, and review sharing monthly.
- Keep the gateway server patched and monitor its logs, since it holds data source credentials.
Ways to get it done
Direct connection to read-only views
Effort: Days to a couple of weeks.
Small data volumes and a single analyst building a few reports.
Warehouse plus semantic model
Effort: About 4 to 10 weeks depending on modules and customization.
Most manufacturers; needed for trusted shared metrics and history.
Partner built with AI Q&A, such as Netray
Effort: A scoped phase for the model, then incremental dashboards.
You want dashboards plus natural-language questions with on-prem models.
Pitfalls
- !Running heavy DirectQuery against production and slowing order entry.
- !Defining 'open order' differently in each report so numbers never agree.
- !Ignoring unposted batches and showing numbers that disagree with the ledger.
- !Installing the gateway on a personal machine that sleeps.
- !Forgetting custom fields and user-defined tables that hold the real dates.
Frequently asked questions
Can Power BI connect directly to Macola?
For SQL Server based Macola, yes through the SQL Server connector. For Progress based installs you typically need an ODBC driver. Confirm your database platform first, and prefer read-only views or a warehouse copy over raw tables.
Do I need the on-premises data gateway?
If you use the Power BI service and your Macola database is on your network, yes, for scheduled refresh. It makes an outbound connection, so you avoid opening the database to the internet.
Should I build a data warehouse?
For anything beyond a few reports, yes. It isolates production, lets you reshape tables and keeps history. Direct import from views is acceptable for a pilot.
Can I ask questions in plain English?
Power BI has Q&A features that depend on your licensing and a well-named model. A custom assistant over the same semantic model can also work, including with a local model when data must stay on-prem.
Which dashboards should I build first?
Inventory health, open order backlog with on-time delivery, and late purchase orders. They use common tables, answer daily questions and are easy to validate against Macola screens.
Want Power BI working on your Macola?
A free 30 minute assessment: we map the integration path for your version and setup, and where it should run.
Get a free assessmentTry it here, free
Macola AI Assistant Sizing and Value Calculator
Estimate GPU count, token volume, index size, hours saved and payback for a private on-prem AI assistant on Macola.
Keep exploring
Free tools
- Macola AI Assistant Sizing and Value CalculatorEstimate GPU count, token volume, index size, hours saved and payback for a private on-prem AI assistant on Macola.
- Macola Private AI Layer Blueprint GeneratorGenerate a shareable architecture plan for adding a private, on-prem AI assistant to Macola, with components, security controls and a three-phase rollout.
- NetSuite SuiteAnalytics Connect ODBC Setup and Troubleshooting ChecklistCheck licensing, role permissions, driver bitness, DSN settings and the usual failure points when connecting Excel, Power BI or a warehouse to NetSuite over ODBC or JDBC.
ERP fixes and how-tos
- How to Add AI to Macola ERP On-Prem Without Sending Data to a CloudHow to put a private AI assistant on Macola 10 and SQL Server without moving data to a cloud: replica, views, retrieval index, local model, audit and rollout.
- IFS Cloud Power BI Integration Using OData ProjectionsHow to connect IFS Cloud to Power BI using OData projections, which authentication to use, and how to avoid slow reports and overloading the ERP database.
- Fix Sage 100 SOTAMAS90 ODBC Connection ErrorsFix Sage 100 ODBC failures on the SOTAMAS90 DSN: 32-bit versus 64-bit driver mismatch, wrong company code, path rights, and external access security.
Runs with your ERP
- Versino Power BI Add-on and SAP Business One: ready datasets and dashboardsHow the Versino Power BI add-on exposes SAP Business One data as ready datasets, what it covers, what breaks, and where a private on-prem AI layer helps.
- Eureka BI (Power BI) with Infor SyteLineWhat is known about the Eureka BI Infor Marketplace listing for SyteLine and VISUAL, built on Microsoft Power BI, plus setup, pitfalls and a private AI layer.
- Birst Analytics and Infor SyteLineHow Birst, Infor's cloud BI platform, fits with SyteLine: what data it reads, setup steps, common pitfalls, alternatives, and where private AI helps.
Guides
- Power BI Integration with SyteLineConnect Power BI to Infor SyteLine for manufacturing analytics. Configure DirectQuery, build data models from SL tables, and publish interactive dashboards.
- SyteLine Reporting: Alternatives to Built-In ReportsExplore SyteLine reporting alternatives. Compare built-in reports, Crystal Reports, SSRS, Power BI, and Birst for CloudSuite Industrial analytics.
- BAAN Reporting Modernization and BI Platform MigrationModernize BAAN reporting with BI platform migration, Crystal Reports replacement, real-time dashboards, and self-service analytics for legacy BAAN environments.
AI on your ERP
- AI for Power BI on ERP Data: Answers Beyond the Dashboard You Already BuiltAdd grounded natural-language answers on top of Power BI semantic models and live ERP tables, with row-level security respected and models served on-prem.
- AI for ECI Macola, from Progression Series to Macola 10Add private AI to ECI Macola, including legacy Progress OpenEdge Progression Series: query orders and inventory in plain English, triage Visual Integrator jobs.
Integrations
- How to connect Power BI to Infor SyteLineHow to connect Power BI to Infor SyteLine: direct SQL Server, IDO or ION API, data warehouse options, on-prem gateway, key dashboards, and natural-language Q&A.
- How to connect Power BI to Sage 100How to connect Power BI to Sage 100 through SQL Server, ODBC or a warehouse, set up the on-prem gateway, build key dashboards, and add natural-language Q&A.
Software
- Microsoft Power BIVendor-neutral guide to Microsoft Power BI for manufacturers: modules, ERP and MES integrations, strengths, limitations, alternatives and AI use cases.
- TableauVendor-neutral guide to Tableau for manufacturers: modules, ERP and data warehouse integrations, strengths, limitations, alternatives and AI use cases.