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 assessment