Power BI Inventory app semantic model

APPLIES TO: Generally available in Business Central 2024 release wave 2.

The semantic model in the Power BI Inventory app is organized in a Star Schema Model.

The fact tables contain information about individual transactions, such as item ledger entries, purchase and sales lines, production order lines, and warehouse activity from Business Central.

The dimension tables provide more context and attributes to the transactional data, such as item, customer, vendor, location, bin, lot, and serial number information.

Fact tables

Fact tables store transactional data and support summarizations such as SUM, AVG, COUNT, and more. The Power BI Inventory app includes several fact tables:

Assembly Headers

The app uses data from the following table:

  • Assembly Header

Table definition

Power BI field nameBusiness Central field nameDescription
Document No.No.Specifies the number of the assembly order.
StatusStatusSpecifies the status of the assembly order.
Quantity Per Unit of MeasureQty. per Unit of MeasureSpecifies the number of base units in the assembly order unit of measure.
Unit of Measure CodeUnit of Measure CodeSpecifies how each unit of the assembly item is measured.

Assembly Lines

The app uses data from the following table:

  • Assembly Line

Table definition

Power BI field nameBusiness Central field nameDescription
Document No.Document No.Specifies the number of the assembly order that the line belongs to.
Quantity Per Unit of MeasureQty. per Unit of MeasureSpecifies the number of base units in the assembly line unit of measure.
Unit of Measure CodeUnit of Measure CodeSpecifies how each unit of the assembly component is measured.

Item Ledger Entries

The app uses data from the following table:

  • Item Ledger Entry

Table definition

Power BI field nameBusiness Central field nameDescription
Entry No.Entry No.Specifies the number of the item ledger entry.
Entry TypeEntry TypeSpecifies the type of inventory transaction, such as purchase, sale, positive adjustment, or negative adjustment.
Expiration DateExpiration DateSpecifies the date when the item on the entry expires.
OpenOpenSpecifies whether the item ledger entry is still open for application.
Document No.Document No.Specifies the document number that created the item ledger entry.
Quantity Per Unit of MeasureQty. per Unit of MeasureSpecifies the number of base units in the entry unit of measure.
Unit of Measure CodeUnit of Measure CodeSpecifies how each unit of the item is measured.
Document TypeDocument TypeSpecifies the type of document that created the item ledger entry.

Planning Component Lines

The app uses data from the following table:

  • Planning Component

Table definition

Power BI field nameBusiness Central field nameDescription
Quantity Per Unit of MeasureQty. per Unit of MeasureSpecifies the number of base units in the planning component unit of measure.
Unit of Measure CodeUnit of Measure CodeSpecifies how each unit of the planning component is measured.

Prod Order Component Lines

The app uses data from the following table:

  • Prod. Order Component

Table definition

Power BI field nameBusiness Central field nameDescription
StatusStatusSpecifies the status of the production order that the component belongs to.
Document No.Prod. Order No.Specifies the number of the related production order.
Quantity Per Unit of MeasureQty. per Unit of MeasureSpecifies the number of base units in the component unit of measure.
Unit of Measure CodeUnit of Measure CodeSpecifies how each unit of the production order component is measured.

Production Order Lines

The app uses data from the following table:

  • Prod. Order Line

Table definition

Power BI field nameBusiness Central field nameDescription
StatusStatusSpecifies the status of the production order that the line belongs to.
Document No.Prod. Order No.Specifies the number of the related production order.
Starting DateStarting DateSpecifies the date when production is planned to start.
Quantity Per Unit of MeasureQty. per Unit of MeasureSpecifies the number of base units in the production order unit of measure.
Unit of Measure CodeUnit of Measure CodeSpecifies how each unit of the produced item is measured.

Project Planning Lines

The app uses data from the following table:

  • Project Planning Line

Table definition

Power BI field nameBusiness Central field nameDescription
Document No.Document No.Specifies the document number for the project planning line.
Quantity Per Unit of MeasureQty. per Unit of MeasureSpecifies the number of base units in the planning line unit of measure.
Unit of Measure CodeUnit of Measure CodeSpecifies how each unit of the item on the project planning line is measured.

Purchase Lines

The app uses data from the following table:

  • Purchase Line

Table definition

Power BI field nameBusiness Central field nameDescription
Document No.Document No.Specifies the document number of the purchase line.
Expected Receipt DateExpected Receipt DateSpecifies the date when the purchased items are expected to be received.
Document TypeDocument TypeSpecifies the type of purchase document.
Quantity Per Unit of MeasureQty. per Unit of MeasureSpecifies the number of base units in the purchase line unit of measure.
Unit of Measure CodeUnit of Measure CodeSpecifies how each unit of the purchased item is measured.

Requisition Line

The app uses data from the following table:

  • Requisition Line

Table definition

Power BI field nameBusiness Central field nameDescription
Quantity Per Unit of MeasureQty. per Unit of MeasureSpecifies the number of base units in the requisition line unit of measure.
Unit of Measure CodeUnit of Measure CodeSpecifies how each unit of the requisition line item is measured.
Auxiliary Index 1-Specifies an auxiliary index used by the semantic model for requisition line calculations.

Sales Lines

The app uses data from the following table:

  • Sales Line

Table definition

Power BI field nameBusiness Central field nameDescription
Document No.Document No.Specifies the document number of the sales line.
Shipment DateShipment DateSpecifies the date when the items on the sales line are expected to ship.
Document TypeDocument TypeSpecifies the type of sales document.
Quantity Per Unit of MeasureQty. per Unit of MeasureSpecifies the number of base units in the sales line unit of measure.
Unit of Measure CodeUnit of Measure CodeSpecifies how each unit of the sold item is measured.

Service Lines

The app uses data from the following table:

  • Service Line

Table definition

Power BI field nameBusiness Central field nameDescription
Document No.Document No.Specifies the document number of the service line.
Quantity Per Unit of MeasureQty. per Unit of MeasureSpecifies the number of base units in the service line unit of measure.
Unit of Measure CodeUnit of Measure CodeSpecifies how each unit of the service line item is measured.

Transfer Lines

The app uses data from the following table:

  • Transfer Line

Table definition

Power BI field nameBusiness Central field nameDescription
Document No.Document No.Specifies the document number of the transfer line.
Receipt DateReceipt DateSpecifies the date when the transfer-to location is expected to receive the items.
Transfer To Location CodeTransfer-to CodeSpecifies the code of the location that the items are transferred to.
Transfer From Location CodeTransfer-from CodeSpecifies the code of the location that the items are transferred from.
Shipment DateShipment DateSpecifies the date when the transfer-from location is expected to ship the items.
In Transit Location CodeIn-Transit CodeSpecifies the in-transit location code for the transfer.
Quantity Per Unit of MeasureQty. per Unit of MeasureSpecifies the number of base units in the transfer line unit of measure.
Unit of Measure CodeUnit of Measure CodeSpecifies how each unit of the transferred item is measured.

Warehouse Activity Lines

The app uses data from the following table:

  • Warehouse Activity Line

Table definition

Power BI field nameBusiness Central field nameDescription
Action TypeAction TypeSpecifies whether the warehouse activity line is a take or place action.
Assemble to OrderAssemble to OrderSpecifies whether the warehouse activity line is related to an assemble-to-order item.
ATO ComponentATO ComponentSpecifies whether the warehouse activity line handles an assemble-to-order component.
Quantity (Base)Qty. (Base)Specifies the quantity on the warehouse activity line in the base unit of measure.
Bin Key-Specifies a semantic model key that combines the bin code and location code.
Quantity Per Unit of MeasureQty. per Unit of MeasureSpecifies the number of base units in the warehouse activity unit of measure.
Unit of Measure CodeUnit of Measure CodeSpecifies how each unit on the warehouse activity line is measured.

Warehouse Entries

The app uses data from the following table:

  • Warehouse Entry

Table definition

Power BI field nameBusiness Central field nameDescription
Quantity Per Unit of MeasureQty. per Unit of MeasureSpecifies the number of base units in the warehouse entry unit of measure.
Unit of Measure CodeUnit of Measure CodeSpecifies how each unit on the warehouse entry is measured.

Warehouse Journal Lines (From Bin)

The app uses data from the following table:

  • Warehouse Journal Line

Table definition

Power BI field nameBusiness Central field nameDescription
Quantity Per Unit of MeasureQty. per Unit of MeasureSpecifies the number of base units in the warehouse journal line unit of measure.
Unit of Measure CodeUnit of Measure CodeSpecifies how each unit on the warehouse journal line is measured.

Warehouse Journal Lines (To Bin)

The app uses data from the following table:

  • Warehouse Journal Line

Table definition

Power BI field nameBusiness Central field nameDescription
Quantity Per Unit of MeasureQty. per Unit of MeasureSpecifies the number of base units in the warehouse journal line unit of measure.
Unit of Measure CodeUnit of Measure CodeSpecifies how each unit on the warehouse journal line is measured.

Dimension tables

The star schema model uses dimension tables and you can filter and group data.

ABC Classification

The app builds this table in the semantic model from the following tables:

  • ABC Analysis Setup

Table definition

Power BI field nameBusiness Central field nameDescription
ABC Class-Specifies the ABC class used to group items by sales contribution.
Class Boundaries-Specifies the lower and upper percentage boundaries for each ABC class in Power BI.

Bin

The app uses data from the following table:

  • Bin

Table definition

Power BI field nameBusiness Central field nameDescription
Bin DescriptionDescriptionSpecifies the description of the bin.
Bin Type CodeBin Type CodeSpecifies the bin type code that defines how the bin is used in warehouse activities.
Bin CodeCodeSpecifies the code of the bin.
Adjustment Bin-Specifies whether the bin is the adjustment bin for the location in Power BI.
Bin Zone CodeZone CodeSpecifies the zone code that the bin belongs to.

Customer

The app uses data from the following table:

  • Customer

Table definition

Power BI field nameBusiness Central field nameDescription
Customer No. & Name-Specifies a combination of customer number and customer name in Power BI.
Customer No.No.Specifies the number of the customer.
Customer NameNameSpecifies the name of the customer.
Customer AddressAddressSpecifies the address of the customer.
Customer Address 2Address 2Specifies additional address information.
Customer CityCitySpecifies the city of the customer.
Customer Post CodePost CodeSpecifies the postal code.
Customer StateCountySpecifies the state, province, or county as part of the address.
Customer Country/Region CodeCountry/Region CodeSpecifies the country/region of the address.
Customer Posting GroupCustomer Posting GroupSpecifies the customer's posting group.
Customer Price GroupCustomer Price GroupSpecifies the customer price group.
Customer Discount GroupCustomer Disc. GroupSpecifies the customer discount group.

Item

The app uses data from the following tables:

  • Item
  • Item Category

Table definition

Power BI field nameBusiness Central field nameDescription
Item No. & Description-Specifies a combination of item number and item description in Power BI.
Item No.No.Specifies the number of the item.
Item DescriptionDescriptionSpecifies the description of the item.
Item Base Unit of MeasureBase Unit of MeasureSpecifies the base unit used to measure the item.
Item Unit Cost (LCY)Unit CostSpecifies the unit cost of the item in the local currency.
Item Inventory Posting GroupInventory Posting GroupSpecifies the inventory posting group for the item.
Item Routing No.Routing No.Specifies the routing number used to produce the item.
Item Production BOM No.Production BOM No.Specifies the production BOM number used to produce the item.
Item Replenishment SystemReplenishment SystemSpecifies the replenishment system that supplies the item.
Item Single-Level Cap. Ovhd CostSingle-Level Cap. Ovhd CostSpecifies the single-level capacity overhead cost of the item.
Item Single-Level Capacity CostSingle-Level Capacity CostSpecifies the single-level capacity cost of the item.
Item Single-Level Material CostSingle-Level Material CostSpecifies the single-level material cost of the item.
Item Single-Level Mfg. Ovhd CostSingle-Level Mfg. Ovhd CostSpecifies the single-level manufacturing overhead cost of the item.
Item Single-Level Subcontrd. CostSingle-Level Subcontrd. CostSpecifies the single-level subcontracted cost of the item.
Item Single-Lvl Mat. Non-Invt. CostSingle-Lvl Mat. Non-Invt. CostSpecifies the single-level material non-inventory cost of the item.
Item Rolled-up Cap. Overhead CostRolled-up Cap. Overhead CostSpecifies the rolled-up capacity overhead cost of the item.
Item Rolled-up Capacity CostRolled-up Capacity CostSpecifies the rolled-up capacity cost of the item.
Item Rolled-up Mat. Non-Invt. CostRolled-up Mat. Non-Invt. CostSpecifies the rolled-up material non-inventory cost of the item.
Item Rolled-up Material CostRolled-up Material CostSpecifies the rolled-up material cost of the item.
Item Rolled-up Mfg. Ovhd CostRolled-up Mfg. Ovhd CostSpecifies the rolled-up manufacturing overhead cost of the item.
Item Rolled-up Subcontracted CostRolled-up Subcontracted CostSpecifies the rolled-up subcontracted cost of the item.
Item Scrap %Scrap %Specifies the percentage of the item that is expected to be scrapped during production.

Item Availability Filter

The app builds this table in the semantic model from the following tables:

  • Date

Table definition

Power BI field nameBusiness Central field nameDescription
View As-Specifies whether item availability values show net change or balance at date in Power BI.
Ordinal-Specifies the sort order for the item availability view options in Power BI.

Item Category

The app uses data from the following table:

  • Item Category

Table definition

Power BI field nameBusiness Central field nameDescription
Item Category CodeCodeSpecifies the code for the item category.
Item Category DescriptionDescriptionSpecifies the description of the item category.

Location

The app uses data from the following table:

  • Location

Table definition

Power BI field nameBusiness Central field nameDescription
Location CodeCodeSpecifies the code of the inventory location.
Location NameNameSpecifies the name of the inventory location.
Location Adjustment Bin Code-Specifies a semantic model key that combines the adjustment bin code and location code.

Lot No

The app builds this table in the semantic model from the following tables:

  • Item Ledger Entry

Table definition

Power BI field nameBusiness Central field nameDescription
Lot No.Lot No.Specifies the lot number assigned to item ledger and warehouse transactions.

Serial No

The app builds this table in the semantic model from the following tables:

  • Item Ledger Entry

Table definition

Power BI field nameBusiness Central field nameDescription
Serial No.Serial No.Specifies the serial number assigned to item ledger and warehouse transactions.

Vendor

The app uses data from the following table:

  • Vendor

Table definition

Power BI field nameBusiness Central field nameDescription
Vendor No. & Name-Specifies a combination of vendor number and vendor name in Power BI.
Vendor No.No.Specifies the number of the vendor.
Vendor NameNameSpecifies the name of the vendor.
Vendor AddressAddressSpecifies the address of the vendor.
Vendor Address 2Address 2Specifies additional address information.
Vendor CityCitySpecifies the city of the vendor.
Vendor Post CodePost CodeSpecifies the postal code.
Vendor StateCountySpecifies the state, province, or county as part of the address.
Vendor Country/Region CodeCountry/Region CodeSpecifies the country/region of the address.
Vendor Posting GroupVendor Posting GroupSpecifies the vendor's posting group.

Zone

The app uses data from the following table:

  • Zone

Table definition

Power BI field nameBusiness Central field nameDescription
Zone DescriptionDescriptionSpecifies the description of the zone.
Zone Bin Type CodeBin Type CodeSpecifies the bin type code used for bins in the zone.
Zone CodeCodeSpecifies the code of the zone.

Tip

You can easily track the KPIs that the Power BI reports display against your business objectives. To learn more, go to Track your business KPIs with Power BI metrics.

Inventory KPIs
Power BI inventory app
Use Power BI with Business Central

Find free e-learning modules for Business Central here