Power BI

Bitta Commission publishes its commission facts as read-only Business Central API queries, so Power BI, Excel and other analytics tools can read credits, attainment, earnings, balances, settlements and statements through the standard Business Central connector. There is no export job and no copy of the data, and each reader sees only the participants their commission scope allows. This page lists the fact endpoints, explains incremental refresh and security, and suggests a starting model.

Before you start

• The reading user signs in with their own Business Central account and needs a Bitta Commission permission set that reads the commission entries behind each endpoint. See Permission sets.
• In Power BI Desktop, use Get Data with the Dynamics 365 Business Central connector, or an OData feed of the endpoints below.

The fact endpoints

All fact endpoints use the API route api/bittaApps/commission/v1.0/companies({companyId})/{entitySet} on your Business Central API base URL, for example https://api.businesscentral.dynamics.com/v2.0/{tenant}/{environment}/.

Entity set	One row per	Main measures
commissionCreditFacts	Credit entry: a share of a sale credited to a participant	creditAmount, splitPercent
commissionAttainmentFacts	Attainment contribution toward a quota	currentContribution, accumulatedValue, targetValue
commissionEarningFacts	Earning entry, including corrections	finalEarningAmount, deltaAmount, currentBaseValue
commissionBalanceFacts	Balance movement between buckets, for example from Eligible to Payable	signedAmount
commissionSettlementFacts	Settlement entry	selectedAmount, exportedAmount, paidAmount, remainingAmount
commissionStatementFacts	Statement version	earningAmount, eligibleAmount, heldAmount, settledAmount

commissionCreditFacts and commissionAttainmentFacts are new in 1.2. Every fact carries participantNumber and participantName. Credit, attainment, earning and statement facts also carry periodCode, periodStartDate and periodEndDate, and credit, attainment and earning facts carry componentCode, so you need fewer joins. Attainment facts carry the targetValue and targetCurrencyCode of the quota; calculate the attainment percent in your report as accumulatedValue divided by targetValue. The full field lists are in API reference.

For master data, use the API pages in the same group: commissionParticipants, commissionPlans, commissionPlanVersions, commissionSettlements, commissionSettlementLines and commissionStatements.

Incremental refresh

Every fact returns lastModifiedAt and rowVersion. Credit, attainment, earning, balance and settlement facts are ledger entries: rows are only added, never changed, so entryNumber works as the incremental refresh watermark. A correction arrives as a new row; credit, attainment and earning rows link it to the original through correctionRootId. Statement facts are statement versions whose status can change, so refresh them on lastModifiedAt.

Security

Each request runs as the signed-in Business Central user and is filtered to the participants in that user's commission scope, the same as the commission pages. For example, a manager reads only the participants in their scope, and a user with a finance-wide scope reads every participant. Every read is recorded on Commission Export Evidence with the entity set, the scoped row count and the watermark. See Audit and security.

A report shared in the Power BI service shows the data of the account that refreshes it. If readers of a shared report should see different participants, set up row-level security in Power BI that matches their commission scope.

A suggested model

1. Dimensions: Participant (from commissionParticipants, keyed by participantId), Component (componentId, componentCode), Period (periodId, periodCode, start and end date) and a Date table on businessDate.
2. Facts: one table per entity set, with single-direction relationships from each dimension.
3. Starter measures:
   • Earned: SUM of finalEarningAmount.
   • Credited: SUM of creditAmount.
   • Paid: SUM of paidAmount.
   • Outstanding: SUM of remainingAmount.
   • Balance by bucket: a running SUM of signedAmount by the target bucket (toBucket).
   • Attainment %: accumulatedValue divided by targetValue.

Amounts are in the currency of each row (currencyCode, or targetCurrencyCode for targets). For reports across currencies, convert them with Business Central exchange rates or filter to one currency. See Multi-currency.

What's next

Find the Business Central reports that already summarize these figures: Commission reports.