Power BI Finance app semantic model

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

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

The fact tables contain information about individual transactions, such as general ledger entries, G/L budget entries, and customer and vendor ledger entries from Business Central.

The dimension tables provide more context and attributes to the transactional data, such as account, budget, customer, and vendor information.

Fact tables

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

Customer Ledger Entries

The app uses data from the following tables:

  • Cust. Ledger Entry
  • Detailed Cust. Ledg. Entry
  • Sales Invoice Header

Table definition

Power BI field nameBusiness Central field nameDescription
CLE Entry No.Entry No.Specifies the entry number of the customer ledger entry.
CLE Due DateDue DateSpecifies the date when the customer ledger entry is due.
CLE OpenOpenSpecifies whether the customer ledger entry is still open.
CLE Posting DatePosting DateSpecifies the posting date of the customer ledger entry.
CLE Document DateDocument DateSpecifies the document date of the customer ledger entry.
DCLE Entry No.Entry No.Specifies the entry number of the detailed customer ledger entry.
Ledger Entry AmountLedger Entry AmountSpecifies whether the detailed customer ledger entry amount updates the customer ledger entry balance.
Entry TypeEntry TypeSpecifies the type of detailed customer ledger entry, such as initial entry or application.
Document TypeDocument TypeSpecifies the document type of the detailed customer ledger entry.
Document No.Document No.Specifies the document number of the detailed customer ledger entry.
DCLE Initial Entry Due DateInitial Entry Due DateSpecifies the due date of the initial customer ledger entry.
Customer No.Customer No.Specifies the number of the customer account.
Application No.Application No.Specifies the application number of the detailed customer ledger entry.
Applied Customer Ledger Entry No.Applied Cust. Ledger Entry No.Specifies the customer ledger entry number that was applied.
Sales Invoice Header Document No.No.Specifies the posted sales invoice number.
Payment Terms CodePayment Terms CodeSpecifies the payment terms that apply to the posted sales invoice.
Payment Discount DatePmt. Discount DateSpecifies the date when the payment discount is available.
CLE Closed At DateClosed at DateSpecifies the date when the customer ledger entry was closed.
Late Payment-Specifies whether the payment was posted after the due date in Power BI.
Late Payment Invoice No.-Specifies the invoice number related to a late payment in Power BI.

G/L Budget Entries

The app uses data from the following table:

  • G/L Budget Entry

Table definition

Power BI field nameBusiness Central field nameDescription
Entry No.Entry No.Specifies the entry number of the G/L budget entry.

G/L Entries

The app uses data from the following tables:

  • G/L Account
  • G/L Entry
  • PBI C. Income St. Source Code

Table definition

Power BI field nameBusiness Central field nameDescription
Posting DatePosting DateSpecifies the posting date of the general ledger entry.
Closing Entry-Specifies whether the general ledger entry is treated as a closing entry in Power BI.
Source CodeSource CodeSpecifies the source code that identifies where the general ledger entry was created.
Source TypeSource TypeSpecifies the type of source that the general ledger entry is related to.
Source No.Source No.Specifies the number of the source that the general ledger entry is related to.
Entry No.Entry No.Specifies the entry number of the general ledger entry.
DescriptionDescriptionSpecifies a description of the general ledger entry.

Vendor Ledger Entries

The app uses data from the following tables:

  • Vendor Ledger Entry
  • Detailed Vendor Ledg. Entry
  • Purch. Inv. Header

Table definition

Power BI field nameBusiness Central field nameDescription
VLE Entry No.Entry No.Specifies the entry number of the vendor ledger entry.
VLE Due DateDue DateSpecifies the date when the vendor ledger entry is due.
VLE OpenOpenSpecifies whether the vendor ledger entry is still open.
VLE Posting DatePosting DateSpecifies the posting date of the vendor ledger entry.
VLE Document DateDocument DateSpecifies the document date of the vendor ledger entry.
DVLE Entry No.Entry No.Specifies the entry number of the detailed vendor ledger entry.
DVLE Ledger Entry AmountLedger Entry AmountSpecifies whether the detailed vendor ledger entry amount updates the vendor ledger entry balance.
Entry TypeEntry TypeSpecifies the type of detailed vendor ledger entry, such as initial entry or application.
Document TypeDocument TypeSpecifies the document type of the detailed vendor ledger entry.
Document No.Document No.Specifies the document number of the detailed vendor ledger entry.
DVLE Initial Entry Due DateInitial Entry Due DateSpecifies the due date of the initial vendor ledger entry.
Vendor No.Vendor No.Specifies the number of the vendor account.
Application No.Application No.Specifies the application number of the detailed vendor ledger entry.
Applied Vendor Ledger Entry No.Applied Vend. Ledger Entry No.Specifies the vendor ledger entry number that was applied.
PIH Document No.No.Specifies the posted purchase invoice number.
Payment Terms CodePayment Terms CodeSpecifies the payment terms that apply to the posted purchase invoice.
Payment Discount DatePmt. Discount DateSpecifies the date when the payment discount is available.
VLE Closed At DateClosed at DateSpecifies the date when the vendor ledger entry was closed.

Dimension tables

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

Customers

The app uses data from the following table:

  • Customer

Table definition

Power BI field nameBusiness Central field nameDescription
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 of the customer.
Customer StateCountySpecifies the state, province, or county as part of the customer address.
Country/Region CodeCountry/Region CodeSpecifies the country/region of the customer 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.
Customer No. & Name-Specifies a combination of customer number and customer name in Power BI.

G/L Account

The app uses data from the following table:

  • G/L Account

Table definition

Power BI field nameBusiness Central field nameDescription
G/L Account No./Name-Specifies a combination of general ledger account number and name in Power BI.
G/L Account No.No.Specifies the number of the general ledger account.
G/L Account NameNameSpecifies the name of the general ledger account.
Income/BalanceIncome/BalanceSpecifies whether the general ledger account is an income statement account or a balance sheet account.

G/L Account Category

The app uses data from the following table:

  • G/L Account Category

Table definition

Power BI field nameBusiness Central field nameDescription
G/L Acc. Category DescriptionDescriptionSpecifies the description of the general ledger account category.

G/L Budget

The app uses data from the following table:

  • G/L Budget Name

Table definition

Power BI field nameBusiness Central field nameDescription
G/L Budget NameNameSpecifies the name of the general ledger budget.
G/L Budget DescriptionDescriptionSpecifies the description of the general ledger budget.

Vendors

The app uses data from the following table:

  • Vendor

Table definition

Power BI field nameBusiness Central field nameDescription
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 of the vendor.
Vendor StateCountySpecifies the state, province, or county as part of the vendor address.
Vendor Country/Region CodeCountry/Region CodeSpecifies the country/region of the vendor address.
Vendor Posting GroupVendor Posting GroupSpecifies the vendor's posting group.
Vendor No. & Name-Specifies a combination of vendor number and vendor name in Power BI.

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.

Finance KPI overview
Power BI finance app
Use Power BI with Business Central

Find free e-learning modules for Business Central here