Build plan for the core Project Transaction fact table (gold.fct_projecttransaction). Grain: one row per projpostedtranstable.RecID. Phase 1 delivers the fact with keys, default-dimension resolution, exchange-rate conversions, and 13 measure columns. The Information attributes (worker, category, transaction type) are deferred to Phase 2.
Approach: Followed the pattern proven by Type A Block 2. Source is Silver's
projpostedtranstable, joined to projtable, ledger, and the default-dimension resolution DataFrame (fd_resolved, same as used in Type A). Exchange-rate joins for five currency variants (Accounting, Regional, Global (USD), GBP, Reporting), all at transdate. Thirteen amount columns delivered: Quantity, TotalCost and TotalSales in transaction currency, plus five converted Cost and five converted Sales variants. Write is a Delta MERGE upsert keyed on ProjectTransactionUID with RowHash for change detection. All five design decisions closed (see Decisions resolved). Fact build complete; still to wire into Gold Orchestration and add the FeeFlash shortcut.
Done Implemented and pushed
To do Remaining work
Optional Deferred / decision needed
Done
| Task | Scope | State | Notes |
|---|---|---|---|
| Review PBI Project Transactions TMDL | Analysis | Done | Confirmed source is projpostedtranstable joined to projtable, ledger, seven default-dimension views, and four exchange-rate views. 13 measures identified (Quantity + 6 Cost + 6 Sales currency variants). |
| Add projpostedtranstable to Silver | D365-Fabric-Silver | Done | Silver config entry landed with 17 columns including RecID, TransactionCode, ProjectCode, ProjectCategoryID, ActivityNumber, ProjectTransactionType (enum), Resource, InvoiceStatus, TransactionDate, CurrencyCode, Quantity, TotalCostAmountTransactionCurrency, TotalSalesAmountTransactionCurrency, DefaultDimensionRecID, CompanyID, ModifiedDateTime, CreatedDateTime, CreatedPartition. Partitioned on CreatedPartition. |
| Silver PR merge and pipeline run | D365-Fabric-Silver | Done | Confirmed on main. projpostedtranstable available in the Silver lakehouse for Gold consumption. |
Add projecttransaction fact to gold_config |
D365-Fabric-Gold | Done | Full schema entry landed with ProjectTransactionUID, 10 FK keys (Company, AccountingDate, Currency, ChartOfAccount, CostCenter, Counterpart, Department, Office, LeadProject, Project), Quantity, 6 Cost amount columns, 6 Sales amount columns, TransactionDate, CurrencyCode, Partition. |
| Create nb_fct_projecttransaction | D365-Fabric-Gold (PBI 12) | Done | Full fact build implemented following the Type A Block 2 pattern. fd_resolved DataFrame drives seven default-dim joins (COSTCENTER, COUNTERPART, DEPARTMENT, LEADPROJECT, MAINACCOUNT, OFFICE, PROJECT). Five exchange-rate joins at transdate (Accounting uses DAY rate, others use BUDGET rate). All key columns use null-guarded xxhash64 with fallback to 0 for missing dim rows. Write is a Delta MERGE upsert keyed on ProjectTransactionUID with RowHash gating updates. |
| Add fact to Gold Orchestration pipeline | D365-Fabric-Gold | Done | nb_fct_projecttransaction added to the Load Facts child pipeline. Same PR also wired up nb_fct_typeavalue, nb_dim_budgetmodel, nb_dim_postinglayer, and nb_dim_typeatransactiontype that were also missing from orchestration. |
| Add shortcut to FeeFlash | D365-Fabric-Gold-FeeFlash | Done | fct_projecttransaction shortcut added to feeflash_config.Notebook (source_schema gold, destination_schema fee_flash). Direct pass-through, no _feeflash variant. |
Raise PRs from working into main |
D365-Fabric-Gold + FeeFlash | Done | Two PRs raised and merged — one on D365-Fabric-Gold-FeeFlash for the FeeFlash config, one on D365-Fabric-Gold for the pipeline orchestration additions. |
| Diagnostic SQL to verify FK joins | Testing | Done | Row count, ProjectTransactionUID uniqueness, per-block dim resolution rates, amount magnitudes, date window and ledger exclusion sanity checks all verified. |
Phase 1 complete. Continues in Phase 2 (Information).
Decisions resolved
| Decision | Scope | State | Notes |
|---|---|---|---|
| Sign convention on amounts | Design decision | Done | No sign flip applied. Amounts stored in D365 native sign. Cost and Sales in projpostedtranstable are typically stored positive, so both native and converted amounts pass through unchanged. |
Rolling window on transdate |
Design decision | Done | 3-year rolling window applied to match Type A. Filter is TransactionDate >= trunc(add_months(current_date(), -24), 'year'). |
| Grain filter: invoiced-only vs all rows | Design decision | Done | No invoice-status filter in Phase 1. Matches the original PBI Project Transactions main-source query which includes all posted transactions. Invoice-only restriction (tntprojinvoicestatus = 3) is deferred to the Phase 2 Information block. |
ProjectKey source: projrecid or FDProject |
Design decision | Done | Sourced from projtable.RecID via the natural PROJID + DATAAREAID join, not from the default-dimension bag. Guaranteed populated per transaction and matches the recommendation in the original plan. |
| Ledger exclusion (GB01/02/03) | Design decision | Done | GB01, GB02, GB03 excluded to match Type A. Applied via a left join on ledger then a filter that keeps rows where the ledger CompanyID is either null (no ledger match) or not in the excluded list. |