People ask me what my favorite workstream is. After 15 years building manufacturing analytics, the answer is always the same. Not because it is the easiest to explain. Because when the data model is right, a buyer, a planner, and an ops leader can all get answers from the same two tables. That almost never happens in this industry.
Every quarter, someone asks me what my favorite workstream is. For context: Marquis builds analytics for pricing, procurement, inventory, and operations. Each workstream has its own complexity, its own data challenges, its own quirks from whichever ERP it came from.
I have a quick answer ready. It is always Inventory IQ.
But when I explain it to someone outside the field, I have to slow down. Because the reason I love it is not the dashboards. It is the data model underneath them.
Pricing analytics is valuable. But the business logic is always contested. What counts as list price? How do you handle freight and fuel surcharges? Is this SKU standard or custom? Before you can answer anything, you have to make a dozen definitional decisions, and every company makes them differently.
Procurement has the same problem from the other side. What is the "right" price for a component? Who decides when a purchase price variance is worth escalating versus expected? There is always something upstream of the data that requires interpretation.
Inventory is different. Every inventory movement is a transaction. Something comes in. Something goes out. Something moves from one bin to another. Your ERP records every one of those events with a quantity, a date, a location, and an item. The data is not perfect (nothing in manufacturing is), but the structure is clean. And when you build the right model on top of it, the analytics become genuinely elegant.
That elegance is what keeps bringing me back.
At the center of the model is a table called InventoryTransactionFact.
It is a single table that tracks every single in and out of inventory across all of time. Every receipt from a supplier. Every issue to a production work order. Every inter-plant transfer. Every warehouse adjustment. Every cycle count discrepancy. One row per event, one row per movement.
The attributes are deliberately minimal:
That is it. No derived fields. No calculated measures baked into the table. The math lives in the measures layer. The fact table captures transactional reality and nothing more.
When I explain this to manufacturing teams, the usual reaction is "that sounds simple." It is. That is the point. A perfect harmony of technology and business: the full complexity of every inventory event in the business reduced to its essential attributes, nothing more and nothing less.
The second table is where the operational intelligence lives: ItemPlanningDim.
Most ERP item masters capture what an item is. ItemPlanningDim captures what an item does at a specific warehouse. It is a slowly changing dimension at the item-warehouse grain, and it carries two categories of information that most ERP reports keep completely separate.
The first category is planning parameters: lead time in days, safety stock quantity, minimum order quantity, and current unit cost. These are the inputs every buyer and planner works from. They live in your ERP but they are scattered across different tables and rarely surfaced together.
The second category is a daily snapshot of quantity on hand at the item-warehouse grain. We pull this from the ERP every day and store it as of the current date.
That snapshot is the thing I am most pleased with. When you join it to InventoryTransactionFact, you get two independent paths to calculate days on hand: trailing consumption-based (what has actually been used over the last N days) and forward demand-based (what is expected based on open orders). Both calculations come from the same two tables. No separate demand planning system required. No reconciliation between systems.
The item-warehouse grain is also important. Most ERP item masters live at the item level. ItemPlanningDim lives one level lower. It knows that the same part has a 14-day lead time at Plant A and a 21-day lead time at Plant B because Plant B sources from a different supplier. That distinction matters enormously when you are trying to determine whether a location is at risk of a stockout.
Here is the star schema that sits underneath every Inventory IQ deployment. The fact table is at the center. Six dimension tables surround it.
The discipline of the model is in what it does not include. No calculated columns. No pre-aggregated measures. No derived days-on-hand value sitting in the fact table waiting to go stale. Every analytical output is computed at query time from the raw transactional record. That makes the answers trustworthy and the model maintainable.
I have been making a claim for years: InventoryTransactionFact and ItemPlanningDim together answer approximately 80% of the questions a buyer, planner, or operations leader asks on any given day. Here is what that looks like in practice.
The other 20% sits behind additional joins. Customer-specific demand planning. Supplier fill rate history. Lot traceability. Those answers require additional tables. But for the operational decisions that actually drive inventory outcomes day to day, two tables are enough.
That ratio matters. It means a planner can answer most of their questions from a model that is simple enough to understand completely. No black boxes. No mystery measures. Just transactions and planning parameters, computed freshly every day.
Here is the part I am most proud of after 15 years doing this work.
Whether a company runs Dynamics, Epicor, Sage, SAP, NetSuite, Infor, or any of the other systems we connect to, every ERP captures inventory transactions. The table names are different. The transaction type codes are different. The item numbering conventions are different. But the underlying reality is always the same: items move in and out of warehouses, and your ERP records it.
Our job is to normalize that reality into InventoryTransactionFact and ItemPlanningDim, regardless of which ERP it came from. For a PE-owned manufacturer running three different ERPs across five plants, this means every plant's inventory is visible in the same model, at the same grain, calculated the same way.
This is not a technical novelty. It is a business decision grounded in 15 years of observation: inventory movements are the fingerprint of a manufacturing business. The fingerprint is consistent even when the systems recording it are not. Build the model that captures the fingerprint cleanly, and you can normalize any ERP into it.
I have been working with this model for 15 years. We iterate on it. We add new dimension attributes as ERPs evolve. We add new transaction type mappings as we encounter new ERP configurations in the field. But the core structure, a fact table of every movement and a planning dimension at the item-warehouse grain, has been stable for a long time.
There is something genuinely satisfying about a data model that handles complexity through simplicity. Every ERP has its own complexity. Every plant has its own quirks. The model's answer to that complexity is not more complexity back, but a clean, disciplined structure that makes the hard questions easy to answer.
That is why Inventory IQ is my favorite workstream. Not because inventory is more important than pricing or procurement. But because the data model underneath it is the most elegant thing we build. And when the foundation is elegant, everything built on top of it is too.
Inventory IQ connects to your ERP, normalizes every inventory transaction into this model, and gives your buyers and planners the daily visibility they need to act before stockouts and excess become problems.
FAQ
The technical questions we get most often from operations teams and data teams building on top of ERP data.
InventoryTransactionFact records every inventory movement as a single row: one receipt, one issue, one transfer, one adjustment. Because every ERP captures inventory events with a quantity, a date, a location, and an item, this structure is consistent across different systems. The fact table contains no derived measures or pre-calculated fields. That discipline is what makes it reliable: the math lives in the reporting layer, not baked into the table.
A standard ERP item master captures what an item is: code, description, unit of measure. ItemPlanningDim captures what an item does at a specific warehouse. It holds planning parameters (lead time, safety stock, minimum order quantity, current cost) alongside a daily snapshot of quantity on hand at the item-warehouse grain. That combination lets you calculate days on hand two ways from the same model: trailing consumption-based and forward demand-based. Most ERP item masters do not carry both in one place at the warehouse level.
Yes. Every ERP records inventory transactions differently, but every ERP records them. The connector layer handles the ERP-specific extraction. The normalization layer maps transaction types, normalizes item codes, and translates unit of measure. Once loaded into InventoryTransactionFact and ItemPlanningDim, every plant looks identical to the analytics layer. Cross-plant comparisons become structurally possible for the first time, without touching any ERP or requiring the plants to standardize their systems.
Approximately 80% of daily buyer and planner questions: days on hand by SKU and location, items within one lead time of a stockout, items overstocked relative to their replenishment cycle, slow-moving and excess inventory across the network, cross-plant transfer opportunities, on-hand value by commodity and warehouse, inventory turn by product category, and items with no movement in 90, 180, or 365 days. The remaining 20%, such as customer-specific demand planning and supplier fill rate analysis, requires joining to additional tables.
With a certified connector in place, the full load into InventoryTransactionFact and ItemPlanningDim can be operational in weeks. The connector handles the ERP-specific extraction. The normalization layer handles transaction type mapping and item master translation. Each additional plant follows the same pattern: connect, map, normalize, load. For manufacturers who have grown through acquisition and are running multiple ERPs at different sites, there is no requirement to standardize ERP systems. The model normalizes at the data layer, not the ERP layer.
Inventory IQ connects to your existing ERP, normalizes every transaction into this model, and gives your buyers and planners the daily visibility they need to act before problems develop.