Inventory Data Model

Modified on Wed, 29 Jul at 2:32 AM

Model Summary

The QQube Inventory Data Model combines inventory activity, current inventory values, open orders, and assembly activity in one prepared model. It supports analysis of quantities, valuation, consumption, COGS, profit margin, and future inventory movement.

Model Type	Inventory activity and forecasting
Transaction Coverage	Inventory posting transactions, Sales Orders, Purchase Orders, pending builds, completed builds, transfers, and adjustments
Historical Coverage	Posting transaction history with current non-posting open orders and pending builds
Primary Use	Analyze on-hand inventory, movement, consumption, availability, and profitability

Best Used For

Forecasting stock availability from on-hand inventory, open Sales Orders, open Purchase Orders, and pending builds
Comparing sales history with component consumption
Reviewing reorder status, fill rate, and days out of stock
Measuring inventory turnover and average days to sell
Analyzing actual COGS, profit margin, and gross margin return on inventory
Tracking lots, serial numbers, transfers, and inventory adjustments

Key Capabilities

Includes linked posting and non-posting transactions.
Combines inventory quantities, valuations, transfers, adjustments, completed builds, consumption, sales, purchases, open Sales Orders, open Purchase Orders, and pending builds.
Supports base and printed unit-of-measure quantities.
Provides actual COGS and profit margin values for inventory and assembly items.
Supports bill-of-materials explosion for Sales Order transactions.
Includes lot and serial number tracking.

Inventory-Specific Logic

Consumption and Forecast

Consumption

Consumption quantity and amount represent inventory removed from stock. For assemblies, component inventory is consumed when the assembly is built. Sales and adjustments are also included.

Forecast

Forecast quantity and amount combine Purchase Order and Sales Order values in one field. Forecast Date combines Purchase Order Expected Date and Sales Order Ship Date.

Document Classifications

Document Category	History - Inventory: All posting transactions Sales Order Pending Builds Purchase Order
Document Forecast Type	On-Hand: All posting transactions that affect on-hand inventory Sales Order Pending Builds Purchase Order

Sales Order BOM Explosion

Sales Order transactions can be expanded to the component level for bill-of-materials analysis.

Quantity, Rate, and Amount Fields

Native fields come from QuickBooks. QQube Field Extensions add printed-unit, assembly, adjustment, transfer, consumption, forecast, and calculated margin values.

Native Fields (28)

Quantity	Rate and Percentage	Amount
Line Sales Order Original Quantity Line Sales Order Invoiced Quantity Line Sales Order Open Quantity Line Sales Quantity Line Purchase Order Original Quantity Line Purchase Order Received Quantity Line Purchase Order Open Quantity Line Purchase Quantity Line Inventory Quantity	Line Rate for Sales Line Rate Line Rate (Foreign) Line Rate Percent Line Exchange Rate	Line Sales Order Original Amount Line Sales Order Invoiced Amount Line Sales Order Invoiced Amount Actual Rate Line Sales Order Open Amount Line Sales Order Open Amount Actual Rate Line Sales Amount Line COGS Amount Line Paid Amount Line Open Amount Line Purchase Order Original Amount Line Purchase Order Received Amount Line Purchase Order Open Amount Line Purchase Amount Line Inventory Valuation Amount

QQube Field Extensions (26)

Quantity	Rate and Percentage	Amount
Line Sales Order Original Quantity [Printed] Line Sales Order Invoiced Quantity [Printed] Line Sales Order Initial Invoiced Quantity Line Sales Order Open Quantity [Printed] Line Sales Quantity [Printed] Line Purchase Order Original Quantity [Printed] Line Purchase Order Received Quantity [Printed] Line Purchase Order Open Quantity [Printed] Line Adjustment Quantity Line Pending Builds Quantity Line Pending Builds Quantity [Printed] Line Completed Builds Quantity Line Transfer Quantity Line Inventory Quantity [Printed] Line Consumption Quantity Line Consumption Quantity [Printed] Line Forecast Quantity Line Forecast Quantity [Printed]	Line Rate [Printed]	Line Sales Profit Margin Amount Line Adjustment Amount Line Pending Builds Amount Line Completed Builds Amount Line Transfer Amount Line Consumption Amount Line Forecast Amount

Power BI and Excel Power Pivot Measures

The prepared examples include 345 visible DAX measures. Open the complete Inventory DAX measure reference or use the section links below.

Measure Section	Visible Measures
Measures Without a Display Folder	1
.Base Measures	51
Amount Measures	4
Calendar Pattern Measures	105
Customer Measures	24
Inventory Measures	15
Item Measures	2
Quantity Measures	137
Vendor Measures	6

For the calculation methods and supporting patterns used in QQube, see DAX Methods Used in PowerPivot and Power BI.

Included Dimensions

Calendars

52/53 Tax Year	Billed Date	Due Date
Expected Date	Fiscal Year	Forecast Date
Fully Paid Date	Service Date	Ship Date
Suggested Discount Date	Transaction Date	Job End Date
Job Projected End Date	Job Start Date	More Info on Calendars

Document

Document Attributes

Line Attributes

Geography

Lists

Account (Inventory)	Account (Sales)	Bin Location
Class	Company	Currency
Customer	Customer Rep	Item
Item Sales Tax Code	Job	Job Rep
Other Name	Sales Rep	Ship Method
Site	Template	Terms
UofM	Vendor

Linked Information

Sales Linked Transactions

Account (Cash Source)

Technical Details

Model Structure	One prepared FACT table with related dimensions and relationships already included
Transaction Scope	Includes linked posting and non-posting transactions.
Historical Basis	Posting transaction history plus current open Sales Orders, Purchase Orders, and pending builds
Primary Values	Quantity, rate, percentage, amount, valuation, COGS, margin, consumption, and forecast fields
DAX Support	345 visible measures for Power BI and Excel Power Pivot

Expected Result

Customers can immediately analyze inventory activity, on-hand position, open demand and supply, assembly consumption, valuation, COGS, margin, and forecast values.

The FACT table, dimensions, relationships, fields, QQube extensions, and measures are already included.