Built on your folders, not a sample model
X3 does not keep your data in one place, and that is the real reason group reporting stalls. We settle the extraction route in the first conversation, then build the model underneath: real cost attached to each invoice line, actual issues and time against each work order, and folder, company and site carried as dimensions so the group number holds. Senior US-based engineers build it, then hand it over.
Representative dashboard — sample data
Where the native inquiries run out
Sage X3 stores data in folders — self-contained sets of companies, sites and configuration, each held as its own schema in the SQL Server or Oracle database beneath the ERP. That boundary is deliberate and it is part of why X3 scales across legislations. It is also why a request that sounds simple stalls: an inquiry, a query or a legacy report is written against one folder, so the moment somebody asks for margin by product line across the group, or inventory turns at every site, the answer gets assembled by hand in a spreadsheet and re-assembled the following month.
That is the gap we fill, and it is a modeling gap rather than a plumbing one. Most vendors selling into this space sell you a pipe: a connector or a replication tool that copies X3 tables somewhere. You still own the hard part afterwards — deciding which ledger a figure came from, which currency it is stated in, and whether two identically named tables in two folders describe the same item. We treat the extraction route as a sub-step to be settled and documented in the first conversation, then spend the engagement where the value sits: a star schema across sales, stock, production and the ledger, with measures your finance and operations leads will both stand behind.
What we build from Sage X3
Buy a block of prepaid Flex hours and, if you're unhappy within the first 4 hours, we refund you in full — no questions asked. We scope each task and quote the hours before work starts, hours deduct only for work you approve, and your banked hours never expire.
Why these builds stall
None of them is a visual problem, and none is solved by a better connector. Each one produces a number that looks plausible and is wrong — which is worse than a report that obviously fails.
Each folder holds its own copy of the same table names, so a model pointed at one schema answers for one slice of the business and looks complete doing it. Folder has to be loaded explicitly and carried as a dimension beside company and site, or the group total is short and nobody can say by how much.
X3's physical columns arrive with a numeric suffix — the item reference lands as ITMREF_0, not ITMREF — and fields declared as arrays, analytical dimensions among them, land as a row of numbered columns rather than as rows. Mapped flat, dimensions cannot be grouped at all until they are unpivoted.
X3 posts the legal ledger, the analytical ledger and any additional reporting ledger into the same accounting entry tables. Sum the amount column without filtering on ledger and every financial figure is inflated by a multiple that changes between companies — the hardest class of error to spot, because the shape of the report still looks right.
A folder can host companies under different legislations, each with its own accounts, its own local rules and often its own currency. A consolidated statement needs an agreed mapping from local accounts to one group structure before it means anything — that mapping is a decision your controller owns, and we build the model to hold it.
Reading every invoice line and stock movement on every refresh works for the first demo and degrades from there. X3 tables carry creation and update stamps, which is what an incremental load keys on — and the read belongs on a replica, so a nightly refresh never competes with an MRP run or your order entry team.
Through a read-only copy of the database beneath X3, or through the web services X3 publishes — never by pointing Power Query at the live production instance. Power BI ships no first-party Sage X3 connector, so a scheduled job reads the rows, lands them in a store you own, and Power BI models that copy on a refresh schedule.
Where X3 runs on your own infrastructure or with a partner who will provide a replica, the database route is usually the cheapest for long history: real SQL against SQL Server or Oracle, proper incremental keys, and no load on the application servers. It also needs the most careful hand, because the physical schema is not what the screens show you. Tables are grouped by folder schema, columns carry their index suffix, and a great many fields are codes that mean nothing until they are decoded against X3's own setup tables — so we build a named, documented extract per table rather than copying everything and hoping.
Where the deployment is hosted and no database connection is on offer, the route is the web services X3 publishes, or scheduled extracts, feeding the same landing store. That path is slower for a first backfill of several years and perfectly good for a nightly delta, which is why we confirm it with whoever administers your instance before quoting: the door that is open decides the refresh cadence the finished dashboard can support. If you already license Sage Data & Analytics for X3, we can model from the warehouse it maintains instead of duplicating the extraction — Sage sets the commercials for that product and does not publish a price, so ask your Sage partner what you already own before adding anything.
Downstream, the pattern is identical whichever route we take. We land a copy, refresh it incrementally so each run moves only rows changed since the last one — Microsoft's incremental refresh reference (opens in new tab) explains how the partitioning works — and let Power BI query the modeled copy. A daily scheduled refresh is the usual cadence and more frequent runs are available; the ceilings are set out in Microsoft's data refresh reference (opens in new tab). Where X3 sits inside your own network, an on-premises data gateway (opens in new tab) carries the connection; we configure it, document it, and fix breakages out of your banked hours.
This is the honest comparison behind the scope estimate you approve before any hours are spent. We confirm the route with whoever administers your X3 instance first, because the wrong assumption here is what turns a five-day build into a five-week one.
| Route out of X3 | Best for | Tradeoff |
|---|---|---|
| Read-only replica of the X3 database (SQL Server or Oracle) | On-premises and partner-hosted sites whose DBA can stand up a replica; long history at low cost | You read the physical schema — folder schemas, suffixed columns, coded fields to decode — and it can change on an X3 upgrade |
| Web services published by X3 | Hosted deployments where no database connection is offered; targeted nightly deltas | Paged and slower for a multi-year backfill; each object has to be requested and mapped, and throughput depends on what your host allows |
| An analytics warehouse you already license | Teams already running Sage Data & Analytics who want Power BI on the same numbers | You inherit its model and refresh; anything outside its scope still needs its own path, and its commercials are set by Sage, not published |
| Scheduled extracts from X3 into a store you own | Locked-down environments, or a first look at a handful of tables | Freshness is bounded by the export schedule, and somebody has to own the file handling |
| Landed copy modeled as a star schema | Every production build, on top of whichever route above applies | Adds a modeling layer — which is the point, not a cost |
Every view below maps to records your folders already hold. The table names are X3's standard ones; your version, the modules you run, and any custom fields or tables your integrator has added change the detail, so we confirm the mapping against your own folders before quoting the work.
| Dashboard area | X3 records it reads | Question it answers |
|---|---|---|
| Margin by product line | Sales invoices and their lines (SINVOICE, SINVOICED) joined to the item master and item-site records (ITMMASTER, ITMFACILIT), with cost taken from stock valuation rather than the current standard cost |
Which lines and items actually earn once real cost is attached, across every folder? |
| Work-order variance | Work orders with their material and operation lines (MFGHEAD, MFGMAT, MFGOPE) against the issues and time actually booked to them |
Which jobs consumed more material, labour or machine time than the routing assumed? |
| BOM & routing cost roll-up | Bills of material and routings with their operation times and cost centres, rolled against the item master | What should this product cost, and how far has actual drifted from it? |
| Inventory turns by site | Stock movement history (STOJOU) and stock lines against on-hand quantities per site (ITMFACILIT, FACILITY) |
How fast does each item turn at each site, what is sitting still, and where are we short? |
| OTIF delivery performance | Sales order lines and deliveries (SORDER, SORDERQ, SDELIVERY) comparing requested, promised and actual ship dates |
Are we shipping on time and in full — against the date the customer asked for, and the one we promised? |
| AR aging and group P&L | Journal entries and their detail (GACCENTRY, GACCENTRYD) filtered by ledger, with the customer master (BPCUSTOMER) and a mapped group account structure |
Who owes what, how old is it, and does the consolidated statement add up without a spreadsheet? |
By becoming explicit dimensions rather than filters somebody has to remember. Folder, company, site, ledger and legislation are carried as modeled columns on every fact table, local accounts are mapped to one agreed group structure, and each monetary measure declares the currency it reports — so a group figure can be drilled to a single site without changing meaning.
Currency is where this gets practical. An X3 document carries the currency it was transacted in, while the ledger states the same document in the company's currency, and a group reporting currency may be a third. All three are true, and a measure that does not say which one it uses will be quoted differently by two departments forever. We define the conversion rule with your controller — rate at posting for actuals, an agreed rate for comparatives — then label every measure accordingly, so the currency question gets settled once instead of during each board meeting.
The same discipline runs through access. Multi-site groups rarely want every site leader seeing every site's margin, so we apply row-level security on the folder, company or site dimension, which means one published report serves everyone and each person sees their own rows. Our guide to setting up row-level security in Power BI covers how that is configured and tested, and the wider modeling discipline behind all of it is described on our Power BI data modeling and DAX page.
The financial and delivery side of production: work-order variance split into material, labour and machine time; standard cost from the BOM (bill of materials) and routing roll-up against what a job actually consumed; inventory turns and stock cover by site; OTIF (on time in full) against both requested and promised dates; and margin by product line once real cost is attached.
Those five answer the questions a plant controller and an operations director argue about, and they all come from records X3 already holds — no shop-floor instrumentation required. Plant-floor OEE and downtime is a different data source entirely, read from machine states rather than from the ERP, and that build is covered on our Power BI for manufacturing and logistics page. Where X3 runs a distribution business rather than a plant, the fill-rate, backorder-aging and vendor-performance set is covered on our Power BI for wholesale distributors page; this page stays on the X3 data itself.
Manufacturers, distributors and process businesses running Sage X3 — typically several companies or sites, often across more than one legislation, where a controller or operations lead rebuilds the same workbook every month. You buy prepaid Flex hours that never expire: Starter at $2,500, Growth at $4,600, or Power at $8,000, with hourly rates from $100 on the largest block.
Because we scope each task and quote the hours before any work begins, you agree the cost of a build up front rather than watching a meter run. The usual pattern for X3 is a Growth or Power block: the extraction route, the folder and ledger modeling, and the first pages are front-loaded, and the remaining hours go to extra views, measure changes and refresh care over the following months. Most individual tasks ship in 3–5 business days after approval; a first multi-folder ERP build is larger and gets a clear timeline up front. There is no project minimum and no retainer — the mechanics are on how prepaid Flex hours work.
Sage sells several products that share a name and almost nothing technically, so it is worth being precise about which one you run. This page is about X3. If your books are in Sage Intacct's cloud financials, the dimension-based path is covered on our Sage Intacct Power BI dashboard page; smaller sites on the on-premises SMB product should start at our Sage 100 Power BI dashboard page; and contractors on Sage 300 CRE have a different data path again, covered on our Sage 300 CRE Power BI dashboard page. Groups running X3 alongside one of those get one model spanning both, rather than two reports nobody reconciles.
Everything. The Power BI file, the semantic model and DAX, the extraction logic and the documented table and field lists, the landing store, the refresh schedule, and the service account all live in your tenant and your environment. There is no black box, no licence to us, and no lock-in — if you never send another request, the dashboards keep running.
We publish to your workspace, document the access route and every table we read, and walk your team through the model so someone internal can maintain it. Most clients then keep a few banked hours for what follows: a page for a newly acquired company in a new folder, a measure that has to track a changed costing method, or a refresh that broke after an X3 upgrade moved a field. That is post-launch support without a retainer — the same hours, spent when you need them. Contact is form-only: send your request and we reply with a scope estimate.
business days, typical
hours, never expire
own everything
Extraction, modeling, DAX and refresh care all draw from the same banked hours. See Flex pricing →
How it works
Tell us your X3 version, whether it is hosted by you, a partner or Sage, how many folders, companies and sites you report on, and the views you need first. We confirm what your administrator can open up, then reply with a scope and an hours estimate before any work begins.
A senior US-based engineer builds the extraction, lands the rows incrementally, shapes the star schema on date, folder, company, site, item, customer and work order, attaches real cost from valuation, and writes the DAX — then ties every total back to the X3 reports your team already trusts.
We publish to your workspace, configure the gateway and a scheduled refresh, apply row-level security by folder, company or site where you need it, document everything, and hand it over. You own it outright; we keep the refresh healthy from your banked hours.
Representative perspective
Two folders, four legislations, and every consolidated number argued over. Getting folder and ledger into the model as real columns did more for us than any of the reporting tools we had already bought.Group controller, multi-site manufacturer
Representative perspective
We wanted work-order variance and turns by site without another spreadsheet cycle. The useful part was someone who knew the tables well enough to reconcile back to what X3 already told us.Operations director, industrial products company
Tell us your X3 version, your folder and company structure, and the three numbers you want first. We'll confirm the extraction route, scope the task and quote the hours before any work begins — drawn from prepaid Flex hours that never expire, with no project minimums.
FAQ
No. Power BI ships no first-party Sage X3 connector, so something has to read the SQL Server or Oracle database beneath X3, or call the web services X3 publishes, and land the rows where Power BI can model them. Choosing and documenting that route is a sub-step of the build we settle before quoting hours — not the build itself.
Yes, and it is usually the first thing we fix. Each folder is a separate schema holding its own copy of the same table names, so a model pointed at one folder returns a quietly partial answer. We load every folder you report on, tag each row with folder, company and site, and label the measures so a group total is never an accidental double count.
Usually yes, through a different door. Where no database connection is offered, we read the web services X3 publishes or take scheduled extracts, and land them in a store you own. We confirm what your hosting agreement and administrator actually allow before quoting, because that answer decides the refresh cadence the finished dashboard can support.
As explicit dimensions rather than filters someone has to remember. X3 posts legal, analytical and any additional ledgers into the same accounting tables, and companies under different legislations keep their own accounts, so the model carries ledger, legislation and company as columns, maps accounts to one agreed group structure, and labels every measure with the currency it reports.
No. We are an independent, US-based Power BI practice and claim no Sage or Microsoft partnership. Our engineers are Microsoft-certified, and we build only with documented, supported tooling: the access route your X3 administrator approves, plus Power BI's published gateway, incremental refresh and semantic model features.
Book a free demo
Tell us the task. We scope it, you approve, and a senior consultant delivers — typically in 3–5 business days. Prepaid hours that never expire.
The fastest way to reach us is the form — tell us the task and we'll reply within one business day.