Power BI Projects app semantic model

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

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

The fact tables contain information about individual transactions, such as project ledger entries, project planning lines, and project-related purchases from Business Central.

The dimension tables provide more context and attributes to the transactional data, such as project, customer, and resource information.

Fact tables

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

Project Ledger Entries

The app uses data from the following table:

  • Project Ledger Entry

Table definition

Power BI field nameBusiness Central field nameDescription
No. & Description-Specifies a combination of the number and description of the project ledger entry in Power BI.
Task No.Project Task No.Specifies the number of the related project task.
Posting DatePosting DateSpecifies the posting date of the project ledger entry.
Entry TypeEntry TypeSpecifies the entry type of the project ledger entry, either usage or sale.
TypeTypeSpecifies the type of account that the project ledger entry is posted to.
No.No.Specifies the number of the resource, item, or general ledger account posted on the entry.
DescriptionDescriptionSpecifies the description of the project ledger entry.
LocationLocation CodeSpecifies the location code if an item is posted.
Unit of Measure CodeUnit of Measure CodeSpecifies how each unit of the item or resource is measured, such as in pieces or hours.
Unit Cost (LCY)Unit Cost (LCY)Specifies the cost, in the local currency, of one unit on the entry.
Unit Price (LCY)Unit Price (LCY)Specifies the price, in the local currency, of one unit on the entry.

Project Planning Lines

The app uses data from the following table:

  • Project Planning Line

Table definition

Power BI field nameBusiness Central field nameDescription
Task No.Project Task No.Specifies the number of the related project task.
Line No.Line No.Specifies the planning line's entry number.
TypeTypeSpecifies the type of account that the planning line relates to.
Line TypeLine TypeSpecifies the type of planning line.
No.No.Specifies the number of the resource, item, or general ledger account on the planning line.
DescriptionDescriptionSpecifies the description of the resource, item, or general ledger account on the planning line.
Unit Cost (LCY)Unit Cost (LCY)Specifies the cost, in the local currency, of one unit on the planning line.
Unit Price (LCY)Unit Price (LCY)Specifies the price, in the local currency, of one unit on the planning line.
Planning DatePlanning DateSpecifies the date of the planning line.

Purchases

The app uses data from the following table:

  • Purchase Line

Purchase lines are broken down into Purchase Orders (Outstanding) and Purchase Orders (Received Not Invoiced). Use the Source Type field to filter on these sources.

Table definition

Power BI field nameBusiness Central field nameDescription
Source Type-Specifies the source of the record, either outstanding purchase orders or purchase orders received but not invoiced.
Document TypeDocument TypeSpecifies the type of the purchase document.
Line No.Line No.Specifies the line number of the purchase line.
Document No.Document No.Specifies the document number of the purchase line.
No.No.Specifies the number of the item, resource, or general ledger account on the purchase line.
TypeTypeSpecifies the type of the purchase line.
DescriptionDescriptionSpecifies a description of the purchase line.
Project No.Project No.Specifies the number of the related project.
Task No.Project Task No.Specifies the number of the related project task.
Project No. & Task No.-Specifies a combination of the project number and project task number in Power BI.

Dimension tables

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

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.

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.

Items

The app uses data from the following tables:

  • Item
  • Item Category

Table definition

Power BI field nameBusiness Central field nameDescription
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.
Item Inventory Posting GroupInventory Posting GroupSpecifies the inventory posting group of the item.
Item Category CodeCodeSpecifies the code for the item category.
Item Category DescriptionDescriptionSpecifies the description of the item category.
Item No. & Description-Specifies a combination of item number and description 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 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 that the project is blocked from being posted in transactions.
Project Project ManagerProject ManagerSpecifies the person who's assigned to manage the project.
Project CompleteCompleteSpecifies that the current status of the project is complete.
Project No. & Description-Specifies a combination of project number and description in Power BI.

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.

Tasks

The app uses data from the following table:

  • Project Task

Table definition

Power BI field nameBusiness Central field nameDescription
Task Project No.Project No.Specifies the number of the related project.
Task No.Project Task No.Specifies the number of the project task.
Task DescriptionDescriptionSpecifies a description of the project task.
Task Project No. & Task No.-Specifies a combination of project number and project task number in Power BI.
Task Project Task & Description-Specifies a combination of project task number and description in Power BI.
Project Task Starting DateStart DateSpecifies the start date of the project task.
Project Task Ending DateEnd DateSpecifies the end date of the project task.

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
No.No. or Account No.Specifies the code of the item, resource, or general ledger account.
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.

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.

Projects KPI overview
Power BI projects app
Use Power BI with Business Central

Find free e-learning modules for Business Central here