Power BI Purchases app semantic model

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

The semantic model in the Power BI Purchases app uses a Star Schema Model.

The fact tables hold details about individual transactions, like purchase budgets, purchase lines, and posted purchase entries from Business Central.

The dimension tables add context and attributes to the transactional data, including vendor, item, project, and purchaser information.

Fact tables

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

Purchase Budget

The app uses data from the following table:

  • Item Budget Entry

Table definition

Power BI field nameBusiness Central field nameDescription
Entry No.Entry No.Specifies the entry number of the purchase budget entry.

Purchase Credit Lines

The app uses data from the following tables:

  • Purch. Cr. Memo Line
  • Purch. Cr. Memo Hdr.

Table definition

Power BI field nameBusiness Central field nameDescription
DescriptionDescriptionSpecifies a description of the posted purchase credit memo line.
Purchase Credit Line No.Line No.Specifies the line number of the posted purchase credit memo line.
Exprected Receipt DateExpected Receipt DateSpecifies the expected receipt date on the posted purchase credit memo line.

Purchase Invoice Lines

The app uses data from the following tables:

  • Purch. Inv. Line
  • Purch. Inv. Header

Table definition

Power BI field nameBusiness Central field nameDescription
DescriptionDescriptionSpecifies a description of the posted purchase invoice line.
Purchase Invoice Line No.Line No.Specifies the line number of the posted purchase invoice line.
Expected Receipt DateExpected Receipt DateSpecifies the expected receipt date on the posted purchase invoice line.

Purchase Lines

The app uses data from the following tables:

  • Purchase Header
  • Purchase Line

Table definition

Power BI field nameBusiness Central field nameDescription
Order DateOrder DateSpecifies the date when the purchase order was created.
Document DateDocument DateSpecifies the date when the purchase document was created.
Due DateDue DateSpecifies when the purchase invoice is due for payment.
DescriptionDescriptionSpecifies a description of the purchase line.
Purchase Line No.Line No.Specifies the line number of the purchase line.
Planned Receipt DatePlanned Receipt DateSpecifies the date when the item is planned to arrive in inventory.
Expected Receipt DateExpected Receipt DateSpecifies the date when you expect to receive the items.
Promised Receipt DatePromised Receipt DateSpecifies the date that the vendor promised to deliver the order.
Requested Receipt DateRequested Receipt DateSpecifies the date that you requested the vendor to deliver the order.

Purchase value entries

The app uses data from the following tables:

  • Value Entry
  • Item Ledger Entry

Table definition

Power BI field nameBusiness Central field nameDescription
Item Ledger Entry No.Entry No.Specifies the entry number of the related item ledger entry.
Value Entry No.Entry No.Specifies the entry number of the value entry.
Value Entry TypeEntry TypeSpecifies the type of value entry.
Document No.Document No.Specifies the document number of the value entry.
Document TypeDocument TypeSpecifies the type of document that created the value entry.
Item Ledger Entry TypeEntry TypeSpecifies the entry type of the related item ledger entry.
AdjustmentAdjustmentSpecifies whether the value entry is an adjustment.
Capacity Ledger Entry No.Capacity Ledger Entry No.Specifies the related capacity ledger entry number.

Dimension tables

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

Documents

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

  • Purchase Credit Lines
  • Purchase Invoice Lines
  • Purchase Lines
  • Purchase Value Entries

Table definition

Power BI field nameBusiness Central field nameDescription
Document No.Document No.Specifies the document number from the source purchase table.
Document TypeDocument TypeSpecifies the document type from the source purchase table.
Source Table-Specifies the semantic model table that contributed the document.

G/L Accounts

The app uses data from the following table:

  • G/L Account

Table definition

Power BI field nameBusiness Central field nameDescription
G/L Account No.No.Specifies the number of the general ledger account.
G/L Account NameNameSpecifies the name of the general ledger account.
G/L Account TypeAccount TypeSpecifies the purpose of the account, such as Posting, Heading, Total, Begin-Total, or End-Total.
G/L Account Income/BalanceIncome/BalanceSpecifies whether the account is an income statement account or a balance sheet account.
G/L Account No. and Name-Specifies a combination of the general ledger account number and name in Power BI.

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 description in Power BI.
Item No.No.Specifies the number of the item.
Item DescriptionDescriptionSpecifies the description of the item.
Item Unit Cost (LCY)Unit CostSpecifies the unit cost of the item in the local currency.
Item Category DescriptionDescriptionSpecifies the description of the item category.

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 for the location.
Location NameNameSpecifies the name of the location.
Location Adjustment Bin Code-Specifies a location-specific adjustment bin code in Power BI.

Project

The app uses data from the following table:

  • Project

Table definition

Power BI field nameBusiness Central field nameDescription
Project No.No.Specifies the number of the project.
Project DescriptionDescriptionSpecifies a short description of the project.
Project Bill to Customer No.Bill-to Customer No.Specifies the customer that the project is billed to.
Project Creation DateCreation DateSpecifies the date when the project was created.
Project Starting DateStarting DateSpecifies the date when the project starts.
Project Ending DateEnding DateSpecifies the date when the project is expected to be completed.
Project StatusStatusSpecifies the current status of the project.
Project Posting GroupProject Posting GroupSpecifies the posting group that links project transactions to the appropriate general ledger accounts.
Project BlockedBlockedSpecifies whether the project is blocked from posting.
Project Project ManagerProject ManagerSpecifies the person who's assigned to manage the project.
Project CompleteCompleteSpecifies whether the project is complete.
Project No. & Description-Specifies a combination of project number and description in Power BI.

Purchase Budget Name

The app uses data from the following table:

  • Item Budget Name

Table definition

Power BI field nameBusiness Central field nameDescription
Purchase Budget NameNameSpecifies the name of the purchase budget.
Purchase Budget DescriptionDescriptionSpecifies the description of the purchase budget.

Purchaser

The app uses data from the following table:

  • Salesperson/Purchaser

Table definition

Power BI field nameBusiness Central field nameDescription
Purchaser CodeCodeSpecifies the code for the purchaser.
Purchaser NameNameSpecifies the name of the purchaser.

Reason codes

The app uses data from the following table:

  • Return Reason

Table definition

Power BI field nameBusiness Central field nameDescription
Return Reason CodeCodeSpecifies the return reason code.
Return Reason DescriptionDescriptionSpecifies the description of the return reason.

Resources

The app uses data from the following table:

  • Resource

Table definition

Power BI field nameBusiness Central field nameDescription
Resource No.No.Specifies the number of the resource.
Resource NameNameSpecifies the name of the resource.
Resource Base Unit of MeasureBase Unit of MeasureSpecifies the base unit used to measure the resource.
Resource Unit CostUnit CostSpecifies the cost of one unit of the resource.
Resource Unit PriceUnit PriceSpecifies the price of one unit of the resource.
Resource No. and Name-Specifies a combination of resource number and name in Power BI.

Type

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

  • Item
  • Resource
  • G/L Account

Table definition

Power BI field nameBusiness Central field nameDescription
Type-Specifies the type of the object, either item, resource, or general ledger account.
Type Key-Specifies a concatenated value generated by combining the No. and Type columns.
No.No. or Account No.Specifies the code of the item, resource, or general ledger account.

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 street 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 posting group.

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.

Purchases KPI overview
Power BI purchasing app
Use Power BI with Business Central

Find free e-learning modules for Business Central here