JD Edwards to Power BI, done for you
Finance and operations reporting modeled straight from the JDE schema — the account ledger, sales order detail, item master and address book — whether EnterpriseOne runs on Oracle, SQL Server or IBM i, whether you are still on World, and whatever BI tool sat on top before. Built by a senior US-based engineer, not a data warehouse programme.
Representative dashboard — sample data
Why these projects stall
JD Edwards has run the ledger, the order book and the warehouse at mid-market manufacturers and distributors for decades, and the tables behind it are perfectly reachable from Power BI — EnterpriseOne sits on an ordinary Oracle, SQL Server or IBM i database. What stops most in-house attempts is not access. It is that F0911 is not called “General Ledger”, every column starts with a two-letter prefix, dates are six-digit Julian integers, amounts carry implied decimals, and half the useful columns hold a code whose meaning lives in a separate user defined code table.
Decode those conventions once, in Power Query, and JDE becomes one of the cleaner ERP sources to model: a proper account ledger, a sales order detail table with cost and price on every line, an item master with category codes already designed for slicing, and a single address book shared by customers, suppliers and employees. That is the job this page describes — the source-system work, at the schema level, regardless of whether an Oracle BI layer, spreadsheets or nothing at all came before.
What we build from JD Edwards
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. Hours are deducted only for work you've reviewed and approved, there's no project minimum, and your banked hours never expire.
What the build covers
Every one sits upstream of the report page, and every one is where an in-house attempt tends to stop.
A database login your DBA creates with select rights on the business data schema — typically the production data source in EnterpriseOne — plus the control tables that hold UDCs. Nothing writes back, and nothing depends on a JDE application user.
Every CYYDDD date column becomes a real date in Power Query, with zero mapped to blank rather than to the year 1899. A single date table keyed to your fiscal date pattern then drives year-to-date, prior-period and period-close comparisons.
Order types, status codes, search types and category codes are joined to their descriptions from F0005, with the space-padded keys trimmed so the join actually matches. Slicers show “Sales Order” rather than “SO”.
F0911 and F4211 become fact tables; the account master, business units, items, and address book become conformed dimensions shared across finance and operations pages, so revenue by customer on one page reconciles to the ledger on another.
Implied decimals restored per the data dictionary, actual amounts kept separate from budget and foreign-currency ledgers, and margin computed from the cost and price already carried on each sales order line rather than re-derived later.
Scheduled or incremental refresh through the on-premises data gateway, row-level security by company or business unit, and failures fixed by us out of your banked hours rather than landing on whoever built it.
Through the database underneath the application, because Power BI ships no JD Edwards connector and does not need one. EnterpriseOne stores business data in ordinary relational tables on Oracle Database, Microsoft SQL Server or IBM Db2 for i, and Power BI reaches each of those with a standard connector through an on-premises data gateway. World is IBM i only, so it always takes the Db2 route. The JDE application layer — security, business functions, the data dictionary — is bypassed entirely, which is why the modeling work described further down exists: the database knows nothing about what a UDC means or how many decimals an amount carries.
Two decisions shape the rest of the project. The first is direct read versus extract. A read-only login against the production schema is the fastest start and fine for most mid-market volumes in import mode, but some DBAs will not permit any external reader on the transactional database, and a nightly extract into a SQL Server or warehouse copy you own is the right answer there. The second is the driver on the gateway machine: Microsoft's Oracle Database connector (opens in new tab) requires Oracle Client for Microsoft Tools to be installed wherever the connection runs, and Db2 for i normally needs IBM's ODBC driver. Neither is difficult, but both are the sort of thing that costs a week when nobody owns it.
| Route | What it is | When we use it |
|---|---|---|
| Oracle Database connector | Power BI's native Oracle connector reading the EnterpriseOne business data schema under a read-only account | The default for E1 on Oracle — import mode, refreshed through the gateway off-peak |
| SQL Server connector | The same pattern against E1 installed on Microsoft SQL Server, optionally through DBA-owned views | E1 on SQL Server, or any JDE extract landed into a SQL Server reporting database |
| IBM Db2 for i | The Db2 or ODBC connector reading JDE libraries on IBM i, using IBM's driver on the gateway machine | JD Edwards World, and EnterpriseOne installations that kept the IBM i database |
| Scheduled extract | A nightly job copying the tables the model needs into a SQL Server, Fabric or warehouse database you own | Where the DBA will not allow external reads on production, or history should be retained beyond JDE purges |
| Orchestrator and AIS REST | EnterpriseOne's REST services exposing orchestrations and application queries over HTTPS | Small, near-real-time reads such as today's order status; not a bulk extraction route for the ledger |
| An existing warehouse copy | JDE already landed into SQL Server, Snowflake, Databricks or Microsoft Fabric by an internal pipeline | Always our first question — it is faster to refresh, cheaper to model and easier to join to non-JDE data |
A note on DirectQuery, because it is often the first thing a JDE administrator asks for. It keeps every visual as a live query against your ERP, which is exactly the load most sites want to avoid, and the Julian and UDC decoding has to be pushed into views for it to perform. We choose import mode unless there is a real current-minute requirement; the trade-offs are set out in Microsoft's DirectQuery documentation (opens in new tab) and in our own guide to DirectQuery vs Import mode.
Because the schema was designed for the application, not for a person reading it, and three conventions in particular defeat a straight load into Power BI. Each is mechanical once you know it, and each is invisible until you do — the classic symptom is a dashboard whose revenue is a hundred times too large and whose dates all fall in the 1970s.
| Convention | What you see in the table | What we do with it |
|---|---|---|
| Julian dates (CYYDDD) | A six-digit integer: century digit, two-digit year, day of year. 124001 is 1 January 2024; 0 means no date | Converted to a true date column, zero mapped to blank, then related to one date table built on your fiscal date pattern (F0008) |
| Implied decimals | Amounts and quantities stored as whole numbers; 1250000 may mean 12,500.00 | Divided by the display decimals defined in the data dictionary for each field, checked against a JDE report before anything else is built |
| User defined codes (UDCs) | Short codes such as SO, 520, 999 in status, type and category columns; descriptions live in F0005 keyed by system, record type and code | Trimmed and joined to F0005 descriptions, so a slicer shows “Sales Order” and “Invoiced” instead of codes |
| Column prefixes | Every column carries a two-character table prefix: GLAA, SDAN8, ABALPH, IMLITM | Renamed to business names with the prefix dropped, and documented in a mapping you keep |
| Ledger types | F0911 and F0902 rows for AA (actual), BA (budget), CA (foreign currency) and others sit in the same table | Filtered and separated so actuals, budget and currency views are explicit measures, never accidentally summed |
| Account structure | Business unit, object and subsidiary held as separate columns and as a short account ID | Joined to the account master into a hierarchy — company, business unit, object, subsidiary — with level-of-detail used for subtotals |
The Julian conversion itself is a one-line Power Query expression — #date(1900 + Number.IntegerDivide(j, 1000), 1, 1) + #duration(Number.Mod(j, 1000) - 1, 0, 0, 0) with a guard for zero — but it has to be applied to every date column on every table, and the fiscal calendar has to be built from your date pattern rather than the calendar year, or period-to-date figures will never match the JDE financial reports your controller already trusts. The padded-key problem on UDC joins is the other one that quietly ruins a first attempt: the code column in F0005 is space-padded, so a join on the raw value returns nothing and every description comes back blank.
A short list, in practice. JDE has thousands of tables, but a finance and operations pack draws on a couple of dozen, and the ones below cover the large majority of what leadership asks for. We confirm exact names against your installation during scoping, because customized systems sometimes carry the same data in a custom table with a different number.
| Table | What it holds | Role in the model |
|---|---|---|
| F0911 Account Ledger | Every posted and unposted journal line, with GL date, ledger type, account, amount and document | The finance fact table — P&L, cost centre and drill-to-transaction reporting |
| F0902 Account Balances | Period buckets of net postings per account, ledger type and fiscal year, plus the prior-year cumulative | Fast balance-sheet and trend reporting where transaction detail is not needed |
| F0901 Account Master and F0006 Business Unit Master | Chart of accounts with object, subsidiary and level of detail; business units with company and category codes | Account and business unit dimensions — the hierarchy every finance page rolls up through |
| F4211 Sales Order Detail | One row per order line: customer, item, branch, quantity, unit price, unit cost, statuses and request, promise and ship dates | The sales fact — revenue, gross margin, open orders, backlog and on-time delivery |
| F4201 Sales Order Header and F42119 Sales Order History | Order-level attributes, and lines moved out of F4211 by sales update | Header attributes joined to the detail; history appended so closed periods stay reportable |
| F4101 Item Master and F4102 Item Branch | Item numbers, descriptions and sales and purchasing category codes; per-branch planning and GL class | Item dimension — the category codes are usually the best product hierarchy you already have |
| F41021 Item Location and F4111 Item Ledger | Quantity on hand, committed and on order by location; the Cardex of every inventory movement | Inventory snapshot and movement facts — turns, aging and dead stock |
| F0101 Address Book | One record per customer, supplier, employee and business unit address, with search type and category codes | Customer and supplier dimensions, both cut from the same address book by search type |
| F4311 Purchase Order Detail and F0411 AP Ledger | Purchase lines with supplier, item, quantities and dates; supplier invoices and payments | Purchasing and payables facts — vendor lead time, price variance and AP aging |
| F03B11 Customer Ledger | Customer invoices and receipts in EnterpriseOne (F0311 in World) | Receivables fact — AR aging, days sales outstanding and collection trend |
Modeled this way, the JDE schema turns into a conventional star: F0911 and F4211 as facts, the account, business unit, item, address book and date tables as dimensions. That is the pattern in Microsoft's star schema guidance (opens in new tab), and the general craft is walked through in our star schema guide. The detail worth calling out is the address book: because customers, suppliers and employees share one table, it has to be filtered by search type into separate dimensions, or a supplier slicer will offer you your own staff.
Less than you might expect at the table level, which is the good news for World sites that have put off reporting. EnterpriseOne inherited World's schema, so F0911, F4211, F4101 and F0101 carry the same names and largely the same columns, and Julian dates, implied decimals and UDCs are identical conventions on both. The differences that matter for a Power BI build are about where the data sits and how it is reached.
| Aspect | JD Edwards World | JD Edwards EnterpriseOne |
|---|---|---|
| Database | IBM Db2 on IBM i only | Oracle Database, SQL Server or Db2 for i, chosen at installation |
| Connector and driver | Db2 or ODBC connector with IBM's driver on the gateway machine | Native Oracle or SQL Server connector, or the Db2 route above |
| Where the tables live | Libraries on IBM i, one per environment | Schemas per data source, typically a production business data schema and a control schema for UDCs |
| Customer ledger | F0311 | F03B11, with a different structure for invoices and receipts |
| REST access | None natively | Orchestrator and AIS REST services, useful for small near-real-time reads |
| Modeling approach | The same: Julian conversion, decimal restoration, UDC decoding, then a star schema over the ledger, order and item tables | |
Sites in the middle of a World-to-EnterpriseOne upgrade, or running both during a transition, are a case we scope explicitly. Because the model is built on business names rather than physical table locations, repointing it from one environment to the other after the upgrade is a Power Query change, not a rebuild.
The set below is what finance and operations leadership at JDE sites consistently ask for, and each embeds a definition someone has to agree before the number means anything. We write them as documented DAX measures over the star schema and the fiscal date table, then validate every total against a JDE report or an existing spreadsheet your team already trusts — the ledger against the trial balance, sales against the sales update, inventory against the stock status report. That discipline is the Power BI data modeling and DAX work every build sits on.
| Measure | Source tables | How it is defined |
|---|---|---|
| Net income and P&L by business unit | F0911, F0902, F0901, F0006 | Actual ledger only, rolled up through object and subsidiary, by fiscal period from your date pattern |
| Budget vs actual | F0902 (AA and BA ledger types) | Budget ledger against actual per account and period, with variance and variance percent as explicit measures |
| Revenue and gross margin | F4211, F42119 | Extended price less extended cost per invoiced line, by customer, item category, branch and salesperson |
| Open orders and backlog | F4211 | Lines whose next status precedes invoicing, valued at extended price, aged from request date |
| On-time shipment | F4211 | Actual ship date against promised date and against requested date, reported separately because they are different questions |
| Inventory value and turns | F41021, F4111, F4102 | On-hand quantity at unit cost by branch and location; cost of goods over average inventory for turns |
| AR and AP aging | F03B11, F0411 | Open amounts bucketed from due date as of the selected date, so month-end aging is reproducible |
| Vendor lead time and price variance | F4311, F4101 | Receipt date less order date per supplier; PO unit cost against the item's standard cost |
The model is published to your Power BI workspace and refreshed through an on-premises data gateway installed on a server that can see the JDE database — configuration is covered in Microsoft's on-premises data gateway documentation (opens in new tab) and our own gateway setup guide. Refresh runs on a schedule that matches how the data moves, usually nightly after the JDE batch jobs finish, and on the ledger and sales history tables we configure incremental refresh (opens in new tab) so only recent periods are re-read from the database each cycle. That is what keeps a multi-year F0911 from turning every refresh into a full table scan of production.
Security follows the structure JDE already uses. Row-level security is driven by company and business unit, so a plant controller sees their business units, a regional sales manager sees their branches and the CFO sees everything, all from one published report. Where JDE row security or a multi-company setup already encodes those rules, we mirror them rather than inventing a second set. Everything is under credentials your administrator provisions, and the model, the report and the documentation are yours from day one.
Prepaid Flex hours that never expire: Starter 20 hours at $125/hr ($2,500), Growth 40 hours at $115/hr ($4,600), Power 80 hours at $100/hr ($8,000), with hourly work from $100/hour on the largest block. A first build covering the connection, the Julian and UDC decoding, the ledger and sales order facts and their shared dimensions, plus the finance and sales pages, usually fits a Growth block, because the decoding and validation are front-loaded. A narrower build — one fact, one subject area, from a database your DBA has already exposed through views — can fit Starter. Adding inventory, purchasing and receivables, or reconstructing history from a warehouse copy, typically takes the work into a Power block.
Most tasks ship within 3–5 business days of scope approval, and you get a scope and hours estimate before anything starts. Our guide to Power BI dashboard development cost covers what moves the number, how prepaid Flex hours work explains the model in full, and custom Power BI dashboard development describes the wider service this build sits inside. There is no warehouse programme to fund first, and no packaged JDE content to license.
Manufacturers, distributors, and construction and real estate businesses running EnterpriseOne or World with a controller, operations director or IT manager who is tired of re-keying JDE reports into spreadsheets each month. The industry framing for those sectors is on Power BI for manufacturing and logistics and Power BI for wholesale distributors, and the finance pack that usually comes first — budget vs actual, cash, aging — is described on Power BI for finance teams.
Two adjacent pages are worth distinguishing. If your reporting data already sits in a SQL Server database that is not an ERP, or you want the generic database route without any of the JDE-specific decoding, our SQL Server Power BI dashboard development page covers that: views, storage mode, gateway and refresh for any schema. This page is the ERP-schema-specific version. And if what you are really replacing is Oracle Business Intelligence sitting on top of JDE — an RPD, Answers and dashboards — the OBIEE to Power BI migration page covers rebuilding that BI layer; the source-system work here is the same either way, and the two are frequently one engagement. Contact is form-only — send your request and we'll reply with a scope estimate.
business days, typical
hours, never expire
own everything
A first JDE build usually fits inside one Flex block, and hours deduct only for work you approve. See Flex pricing →
How it works
Tell us whether you run World or EnterpriseOne, which database it sits on, and the pages you need first. Your DBA creates a read-only login; we confirm the date pattern, the ledger types in use and which UDCs matter, then reply with a scope and hours estimate before any work begins.
A senior US-based engineer converts the Julian dates, restores decimals, decodes UDCs, builds the star schema and fiscal date table, and writes the DAX for the ledger, sales, inventory and aging measures — validating every total against a JDE report your team already runs.
Published to your workspace with the gateway configured, scheduled or incremental refresh, row-level security by company and business unit, and a column mapping and documentation you keep. We maintain it from your banked hours.
Representative perspective
Our first attempt showed revenue a hundred times too high and every order dated 1970. Once the dates and decimals were handled properly the numbers tied to the trial balance on the first pass.Controller, multi-plant manufacturer on EnterpriseOne
Representative perspective
We had been told JDE reporting meant a warehouse project. A read-only login, a gateway and a proper model got the sales and inventory pages in front of the branches inside a few weeks.IT manager, regional distributor on JD Edwards World
Tell us which release you run, what database it sits on, and which reports your team re-keys every month. We'll confirm the access route, scope the task, and tell you the hours before anything starts — no project minimums, no obligation.
FAQ
Through the database underneath it. EnterpriseOne business data lives in a schema on Oracle Database, SQL Server or IBM i, and Power BI reads it with the matching connector through an on-premises data gateway under a read-only account. Where direct reads are not permitted, a scheduled extract into a SQL Server or warehouse copy is the alternative we scope.
JDE stores dates as six-digit Julian integers in the form CYYDDD: a century digit, a two-digit year and the day of the year. So 124001 is 1 January 2024 and zero means no date. We convert every date column in Power Query, treat zero as blank, and build a real date table so time intelligence works normally.
User defined codes are JDE's lookup lists: order types, status codes, category codes and hundreds more, all stored as short codes with descriptions held in F0005 keyed by system, record type and code. The code column is space-padded, so a naive join returns nothing. We trim and join each one to its description so slicers read in English.
Yes. World runs on IBM i and shares most of the table and column naming with EnterpriseOne, including Julian dates, implied decimals and UDCs, so the same modeling approach applies. The differences are the database driver, library naming on IBM i and a handful of tables such as the customer ledger, and we confirm those during scoping.
It should not, and we design so it cannot. Import mode is the default, with refresh scheduled off-peak and incremental refresh on the ledger tables so only recent periods are re-read. Where your DBA prefers no direct reads at all, we build on a nightly extract or a reporting copy instead, which is often the better answer anyway.
No. We are an independent US-based Power BI consultancy with no Oracle partnership to claim and no packaged JDE content to sell. We build using documented database connectors and extraction routes under credentials your administrator provisions and owns, and everything we produce is published into your own Power BI workspace.
If the database you want reported on is not an ERP, or you want the generic database route without JDE-specific decoding, start at SQL Server Power BI dashboard development. Sites replacing the Oracle BI layer that sat on top of JDE should read OBIEE to Power BI migration, which covers rebuilding the RPD, Answers and dashboards; this page covers the source-system half that both share. For the wider service the build sits inside, read custom Power BI dashboard development. Plants and distribution networks will find the sector view on Power BI for manufacturing and logistics and Power BI for wholesale distributors, finance teams on Power BI for finance, and the modeling craft underneath all of it is described in data modeling and DAX. Every task is scoped before work begins from your prepaid block of senior-led hours — no project minimums, no change-order surprises, and hours are deducted only for work you approve.
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.