Power BI Subscription Billing app semantic model

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

The fact tables contain information about individual transactions from sources such as customer subscription contracts and vendor subscription contracts.

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

Fact tables

Fact tables store transactional data and support summarizations such as SUM, AVG, COUNT, and more. There are several fact tables in the Power BI Subscription Billing app:

Customer Contract Line

The app uses data from the following tables:

  • Subscription Contract Analysis Entries

Table definition

Power BI Field NameBusiness Central Field NameDescription
Subscription Line No.Entry No.Unique identifier of the subscription line entry.
TemplateTemplateSpecifies the code of the subscription package line template.
Customer Contract Line Desc.DescriptionSpecifies the description of the subscription line.
Subscription Start DateSubscription Line Start DateSpecifies the date from which the subscription line is valid and will be invoiced.
Subscription End DateSubscription Line End DateSpecifies the date up to which the subscription line is valid.
Next Billing DateNext Billing DateSpecifies the date of the next billing possible.
Analysis DateAnalysis DateDate used for analytical evaluations of subscription KPIs.
Billing Base PeriodBilling Base PeriodSpecifies for which period the amount is valid.
Invoicing Item No.Invoicing Item No.Item number used for invoicing this subscription line.
Customer Contract Line No.Subscription Contract Line No.Specifies the line number in the subscription contract.
Notice PeriodNotice PeriodSpecifies the notice period required before contract cancellation.
Initial TermInitial TermSpecifies the minimum length of the initial subscription contract period.
Extension TermSubsequent TermSpecifies the contract renewal term after the initial term expires.
Billing RhythmBilling RhythmSpecifies how often billing occurs (e.g., monthly, yearly).
Cancellation Possible UntilCancelation Possible UntilSpecifies the last date cancellation is allowed before renewal.
Term UntilTerm UntilSpecifies the commitment end date for the subscription line.
Renewal TermRenewal TermSpecifies the term by which the subscription line is renewed and the end of the subscription line is extended.
Dimension Set IDDimension Set IDSpecifies the value of the dimension set ID.
Remaining Term-Specifies the remaining number of months in the currenct subscription term.
Billing Rhythm Months-Specifies the billing rhythm in months.
Customer No.Partner No.Specifies the customer number.
Customer Contract No.Subscription Contract No.Specifies the number of the related customer subscription contract.
Customer Contract Line Key-Specifies a combination of customer subscription contract, customer subscription contract line and analysis date in order to create a unique identifier of each entry.
Is Latest Version-Specifies if the line represents the latest state of the customer subscription contract line.
Is Usage Based BillingUsage Based BillingSpecifies if the line is billed based on usage data.
Subscription Package Key-Specifies a combination of subscription and subscription package code in order to match subscription lines that originate from the same subscription package.
Source TypeSubscription Source TypeSpecifies the type of the subscription (item, G/L account).
Source No.Subscription Source No.Specifies the item number or G/L account number of the subscription.

Customer Contract Changes

The app uses data from the following tables:

  • Subscription Contract Analysis Entries

Table definition

Power BI Field NameBusiness Central Field NameDescription
Customer Contract Line Key-Specifies a combination of customer subscription contract, customer subscription contract line and analysis date in order to create a unique identifier of each entry.
DateSpecifies the period where the change in monthly recurring revenue occurred.
Change Type-Specifies the type of change in monthly recurring revenue (churn, downgrade, new, upgrade, UBB)

Customer Contract Deferrals

The app uses data from the following tables:

  • Customer Subscription Contract Deferrals

Table definition

Power BI Field NameBusiness Central Field NameDescription
Contract No.Subscription Contract No.Specifies the number of the related customer subscription contract.
Document TypeDocument TypeSpecifies the document type used to create the deferral.
Document No.Document No.Specifies the document number used to create the deferral.
Is Released KeyReleasedSpecifies whether the deferral has been released.
Posting DatePosting DateSpecifies the posting date of the deferral.
Document Line No.Document Line No.Specifies the number of the line in the related document.
Document Posting DateDocument Posting DateSpecifies the posting date of the document used to create the deferral.
Release Posting DateRelease Posting DateSpecifies the posting date on which the deferral was released.
G/L Entry No.General Ledger Entry No.Specifies the number of the G/L entry with which the deferral was released.
DiscountDiscountSpecifies whether the Subscription Line is used as a basis for periodic invoicing or discounts.
Dimension Set IDDimension Set IDSpecifies the value of the Dimension Set ID.
Customer Contract Line Key-Specifies a combination of customer subscription contract, customer subscription contract line and analysis date in order to create a unique identifier of each entry.
Contract Line No.Subscription Contract Line No.Specifies the line number in the customer subscription contract.

Customer Billing Forecast

The app uses data from the following tables:

  • Subscription Contract Analysis Entries

Table definition

Power BI Field NameBusiness Central Field NameDescription
Customer Contract Line Key-Specifies a combination of customer subscription contract, customer subscription contract line and analysis date in order to create a unique identifier of each entry.
Billing Date-Specifies the period of the forecast.

Vendor Contract Line

The app uses data from the following tables:

  • Subscription Contract Analysis Entries

Table definition

Power BI Field NameBusiness Central Field NameDescription
Subscription Line No.Entry No.Unique identifier of the subscription line entry.
TemplateTemplateSpecifies the code of the subscription package line template.
Vendor Contract Line Desc.DescriptionSpecifies the description of the subscription line.
Subscription Start DateSubscription Line Start DateSpecifies the date from which the subscription line is valid and will be invoiced.
Subscription End DateSubscription Line End DateSpecifies the date up to which the subscription line is valid.
Next Billing DateNext Billing DateSpecifies the date of the next billing possible.
Analysis DateAnalysis DateDate used for analytical evaluations of subscription KPIs.
Billing Base PeriodBilling Base PeriodSpecifies for which period the amount is valid.
Invoicing Item No.Invoicing Item No.Item number used for invoicing this subscription line.
Vendor Contract Line No.Subscription Contract Line No.Specifies the line number in the subscription contract.
Notice PeriodNotice PeriodSpecifies the notice period required before contract cancellation.
Initial TermInitial TermSpecifies the minimum length of the initial subscription contract period.
Extension TermSubsequent TermSpecifies the contract renewal term after the initial term expires.
Billing RhythmBilling RhythmSpecifies how often billing occurs (e.g., monthly, yearly).
Cancellation Possible UntilCancelation Possible UntilSpecifies the last date cancellation is allowed before renewal.
Term UntilTerm UntilSpecifies the commitment end date for the subscription line.
Renewal TermRenewal TermSpecifies the term by which the subscription line is renewed and the end of the subscription line is extended.
Dimension Set IDDimension Set IDSpecifies the value of the dimension set ID.
Remaining Term-Specifies the remaining number of months in the current subscription term.
Billing Rhythm Months-Specifies the billing rhythm in months.
Vendor No.Partner No.Specifies the vendor number.
Vendor Contract No.Subscription Contract No.Specifies the number of the related vendor subscription contract.
Vendor Contract Line Key-Specifies a combination of vendor subscription contract, vendor subscription contract line and analysis date in order to create a unique identifier of each entry.
Is Latest Version-Specifies if the line represents the latest state of the vendor subscription contract line.
Is Usage Based BillingUsage Based BillingSpecifies if the line is billed based on usage data.
Subscription Package Key-Specifies a combination of subscription and subscription package code in order to match subscription lines that originate from the same subscription package.
Source TypeSubscription Source TypeSpecifies the type of the subscription (item, G/L account).
Source No.Subscription Source No.Specifies the item number or G/L account number of the subscription.

Vendor Contract Deferrals

The app uses data from the following tables:

  • Vendor Subscription Contract Deferrals

Table definition

Power BI Field NameBusiness Central Field NameDescription
Contract No.Subscription Contract No.Specifies the number of the related vendor subscription contract.
Document TypeDocument TypeSpecifies the document type used to create the deferral.
Document No.Document No.Specifies the document number used to create the deferral.
Is Released KeyReleasedSpecifies whether the deferral has been released.
Posting DatePosting DateSpecifies the posting date of the deferral.
Document Line No.Document Line No.Specifies the number of the line in the related document.
Document Posting DateDocument Posting DateSpecifies the posting date of the document used to create the deferral.
Release Posting DateRelease Posting DateSpecifies the posting date on which the deferral was released.
G/L Entry No.General Ledger Entry No.Specifies the number of the G/L entry with which the deferral was released.
DiscountDiscountSpecifies whether the Subscription Line is used as a basis for periodic invoicing or discounts.
Dimension Set IDDimension Set IDSpecifies the value of the Dimension Set ID.
Vendor Contract Line Key-Specifies a combination of vendor subscription contract, vendor subscription contract line and analysis date in order to create a unique identifier of each entry.
Contract Line No.Subscription Contract Line No.Specifies the line number in the vendor subscription contract.

Vendor Billing Forecast

The app uses data from the following tables:

  • Subscription Contract Analysis Entries

Table definition

Power BI Field NameBusiness Central Field NameDescription
Vendor Contract Line Key-Specifies a combination of vendor subscription contract, vendor subscription contract line and analysis date in order to create a unique identifier of each entry.
Billing Date-Specifies the period of the forecast.

Dimension tables

The star schema model uses dimension tables and allows you to filter and group.

Customers

The app uses data from the following table:

  • Customer

Table definition

Power BI field nameBusiness Central field nameDescription
Customer No.No.Specifies the customer number.
Customer NameNameSpecifies the customer name.
Customer No. & Name-Specifies a combination of customer number and customer name in Power BI.
AddressAddressSpecifies the address.
Address 2Address 2Specifies the secondary address details.
CityCitySpecifies the city.
Post CodePost CodeSpecifies the postal code of the address.
StateCountySpecifies the state.
Country/Region CodeCountry/Region CodeSpecifies the country/region the address is in.
Customer Posting GroupCustomer Posting GroupSpecifies the posting group.
Customer Price GroupCustomer Price GroupSpecifies the price group.
Customer Disc. GroupCustomer Disc GroupSpecifies the discount group.

Customer Contract

The app uses data from the following table:

  • Customer Subscription Contract

Table definition

Power BI field nameBusiness Central field nameDescription
Customer Contract No.No.Specifies the customer subscription contract number from the related fact table.
Customer Contract TypeContract TypeSpecifies the contract type.
Customer Contract DescriptionDescriptionSpecifies the description.
Customer Contract No. Desc.-Specifies a combination of customer subscription contract number and description in Power BI.
Salesperson CodeSalesperson CodeSpecifies the salesperson responsible for the customer subscription contract.

Subscription

The app uses data from the following table:

  • Subscription
  • Subscription Line

Table definition

Power BI field nameBusiness Central field nameDescription
Subscription No.No.Specifies the subscription number from the related fact table.
Package CodeSubscription Package CodeSpecifies the subscription package code.
Subscription Desc.DescriptionSpecifies the description.
Analysis DateAnalysis DateSpecifies the date of the latest subscription contract analysis entry from the related fact table.
Subscription Package KeySubscription Package KeySpecifies a combination of subscription and subscription package code in order to match subscription lines that originate from the same subscription package.
Subscription No. Desc.-Specifies a combination of the subscription number and description in Power BI.

Vendors

The app uses data from the following table:

  • Vendor

Table definition

Power BI field nameBusiness Central field nameDescription
Vendor No.No.Specifies the vendor number.
Vendor NameNameSpecifies the vendor name.
Vendor No. & Name-Specifies a combination of the vendor number and vendor name in Power BI.
AddressAddressSpecifies the address.
Address 2Address 2Specifies the secondary address details.
CityCitySpecifies the city.
Post CodePost CodeSpecifies the postal code of the address.
StateCountySpecifies the state.
Country/Region CodeCountry/Region CodeSpecifies the country/region the address is in.
Vendor Posting GroupVendor Posting GroupSpecifies the posting group.

Vendor Contract

The app uses data from the following table:

  • Vendor Subscription Contract

Table definition

Power BI field nameBusiness Central field nameDescription
Vendor Contract No.No.Specifies the customer subscription contract number from the related fact table.
Vendor Contract TypeSubscription Contract TypeSpecifies the contract type.
Vendor Contract DescriptionDescriptionSpecifies the description.
Vendor Contract No. Desc.-Specifies a combination of vendor subscription contract number and description in Power BI.

Item

The app uses data from the following table:

  • Item
  • Item Category

Table definition

Power BI field nameBusiness Central field nameDescription
Item No.No.Specifies the item number.
Item NameDescriptionSpecifies the item description.
Item No. & Description-Specifies a combination of the item number and description in Power BI.
Base Unit of MeasureBase Unit of MeasureSpecifies the base unit of measure used for the item.
Item Category CodeItem Category CodeSpecifies item category used for the item.
Item Category DescriptionDescriptionSpecifies the item category description.
Inventory Posting GroupInventory Posting GroupSpecifies the inventory posting group.

Salesperson

The app uses data from the following table:

  • Salesperson/Purchaser

Table definition

Power BI field nameBusiness Central field nameDescription
Salesperson CodeCodeSpecifies the ID of the salesperson.
Salesperson NameNameSpecifies the name of the salesperson.

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.

Track your business KPIs with Power BI metrics
Setting up the Subscription Billing Power BI app
Subscription Billing controlling
Subscription Billing overview