Build status for the Type A Values fact table (gold.fct_typeavalue) and its supporting dimension (gold.dim_typeatransactiontype). Source is a UNION of GL rows filtered to customer-invoice vouchers on a specific main account (Type 0 — Summary, 24,938 rows) and invoice-line rows from custinvoice tables (Type 1 — Detail, 345,955 rows). Both blocks built, merged to main, and orchestrated in the Load Dims and Load Facts pipelines.
Approach: Fact built from Silver directly, replicating the D365 SQL. Rejected coupling to
fct_generalledger because Quantity, raw voucher, and raw glrecid are not stored there. All five currency conversions (Accounting/Local, Regional, Global (USD), GBP, Reporting) are pre-computed on the fact via exchange-rate joins. Block 1 amounts multiplied by −1 to align sign convention with Block 2 (both now positive, matches original PBI SQL). Block 2 uses default-dimension resolution replicating bi.v_defaultdimensionview. Interproj deduplication prevents multi-project fan-out on Detail rows. Both blocks unioned via unionByName with a 10-trillion UID offset on Block 2 to guarantee no collision.
Done Implemented and pushed
To do Remaining work
Optional Deferred / decision needed
Done
| Task | Scope | State | Notes |
|---|---|---|---|
| custinvoicetable Silver config | D365-Fabric-Silver | Done | Header table for customer invoices. Columns: RecID, InvoiceDate, CurrencyCode, DefaultDimensionRecID, CompanyID. Pushed on working branch. |
| custinvoiceline Silver config | D365-Fabric-Silver | Done | Line-level table. Columns: RecID, ParentRecID, Quantity, AmountTransactionCurrency, CompanyID. Pushed on working branch. |
| custinvoicelineinterproj Silver config | D365-Fabric-Silver | Done | Links invoice line to project. Columns: RecID, CustInvoiceLineRecID, ProjectID, ProjectDataAreaID. Pushed on working branch. |
| Review PBI Type A Values TMDL | Analysis | Done | Confirmed union structure, join paths, and column list. Confirmed dimensional overlap with fct_generalledger. |
| Silver PR merged and pipeline run | D365-Fabric-Silver | Done | PR merged to main and Silver ingest run. custinvoicetable, custinvoiceline, and custinvoicelineinterproj all landed in the lakehouse. |
| Create nb_dim_typeatransactiontype | D365-Fabric-Gold (PBI 8) | Done | Static 2-row lookup: (0, "Summary") and (1, "Detail"). Built inline using spark.createDataFrame. Natural key TypeATransactionTypeID, surrogate TypeATransactionTypeKey hashed from that. Pushed on working branch. |
Add typeatransactiontype dim to gold_config |
D365-Fabric-Gold | Done | Schema entry added: TypeATransactionTypeUID, TypeATransactionTypeID, TypeATransactionType. |
| nb_fct_typeavalue — Block 1 (GL / Summary) | D365-Fabric-Gold (PBI 13) | Done | Notebook created with Block 1 fully implemented, Block 2 stubbed. Copies the nb_fct_generalledger transformation pattern with vendor/PO/fintag joins removed. Filters: ledger GB01/02/03 exclusion, 3-year rolling window (24 months from start of year), mainaccount 600500, PostingType 'Intercompany revenue', subledgervoucher CINV/CCRN. All five currency amounts (Local, Regional, Global (USD), GBP, Reporting) computed via rate joins. Amounts multiplied by -1 in Block 1 to align sign convention with Block 2 (matches original PBI SQL). Adds TypeATransactionTypeID=0 and TypeATransactionTypeKey hashed to match dim. |
Add typeavalue fact to gold_config |
D365-Fabric-Gold | Done | Full 28-column schema added: TypeAValueUID, TypeATransactionTypeID, TypeATransactionTypeKey, all FKs, seven amount columns, Quantity, AccountingDate, CurrencyCode, Partition. |
| Null-guard bug fix on fact key hashes | D365-Fabric-Gold | Done | Added when(...isNull, 0).otherwise(hash) guards on LeadProjectKey, CounterpartKey and UltimateCustomerKey in both nb_fct_typeavalue and nb_fct_generalledger. Prior code produced hash-of-null values that didn't match any dim row. Detected via FK integrity check on Block 1. Commit 436b124. |
Add InvoiceAccount to Silver custinvoicetable |
D365-Fabric-Silver | Done | Added the InvoiceAccount column to the Silver ingest so Block 2 can populate CustomerKey via c1.InvoiceAccount → custtable.CustomerAccountCode. Pushed and Silver ingest re-run. Commit 62670b8. |
| nb_fct_typeavalue — Block 2 (Detail) + union | D365-Fabric-Gold (PBI 13) | Done | Full Block 2 implementation with default-dimension resolution (fd_resolved replicating bi.v_defaultdimensionview), 3-year rolling window on InvoiceDate, ledger GB01/02/03 exclusion, interproj dedup (row_number over CustInvoiceLineRecID ordered by RecID) to prevent multi-project fan-out. CustomerKey populated via InvoiceAccount → custtable. UltimateCustomerKey hardcoded to 0. Exchange rates all DAY at InvoiceDate. TypeAValueUID = c2.RecID + 10 trillion for guaranteed union uniqueness. Union: src = block1_src.unionByName(block2_src). Commit ed9f897. |
| Sign flip on Block 1 amount columns | D365-Fabric-Gold (PBI 13) | Done | All 7 Block 1 amount columns multiplied by −1 to align sign convention with Block 2 (matches original PBI SQL). Quantity untouched. Commit 3670059. |
| Diagnostic SQL to verify FK joins | Testing | Done | Ran row count, UID uniqueness, UID range separation, amount magnitudes, ledger exclusion, date window and FK integrity checks across both blocks. Block 1 near-perfect resolution. Block 2 has expected zero resolution for ChartOfAccount, CostCenter and other financial dimensions because these aren't stored on custinvoicetable's DefaultDimension bag in T&T's D365 setup (confirmed 1:1 with original PBI dataflow behaviour). |
| Add shortcuts to FeeFlash | D365-Fabric-Gold-FeeFlash | Done | feeflash_config.Notebook updated with two active shortcut entries for dim_typeatransactiontype and fct_typeavalue. dim_ultimatecustomer intentionally excluded. Commit b41819d. |
| Add dim + fact to Gold Orchestration pipelines | D365-Fabric-Gold | Done | nb_dim_typeatransactiontype added to Load Dims and nb_fct_typeavalue added to Load Facts. Same commit also added nb_dim_budgetmodel, nb_dim_postinglayer and nb_fct_projecttransaction. Commit c374c43, merged to main. |
Raise PR from working into main |
D365-Fabric-Gold | Done | All four commits merged to main: rename (d9c3e2e), null-guard fix (436b124), Block 2 build (ed9f897), sign flip (3670059). FeeFlash config PR (b41819d) also merged. |
Optional / deferred
| Task | Scope | State | Notes |
|---|---|---|---|
Add fiscalcalendarperiod to Silver |
D365-Fabric-Silver | Optional | Required only if PeriodType is to be surfaced on the fact. Bronze already ingests it. Defer until PBI 7 direction is confirmed. |
| PBI 7 — PeriodType treatment | D365-Fabric-Gold (PBI 7) | Optional | Decide whether PeriodType lives as an attribute on dim_accountingdate (preferred if it aligns to date) or as a standalone dim. Impacts the Type A fact's PeriodType handling — currently held as a degenerate int if included at all. |
| Missing dim resolution on Detail rows | Semantic model / reporting | Optional | Block 2 (Detail) rows resolve to Unknown for ChartOfAccount, CostCenter, Department, Office and LeadProject because these aren't stored on the DefaultDimension bag for customer invoice headers in T&T's D365 setup. Confirmed 1:1 with the original PBI dataflow. If any report ever needs mainaccount detail on Detail rows, we could add a custinvoicejour (posted journal) lookup to Block 2 as a follow-up. |
| UltimateCustomer resolution on Summary rows | D365-Fabric-Gold (PBI 13) | Optional | Block 1 has 1 real UltimateCustomer match out of 24,938 rows. Project itself resolves fine (Block 1 pulls from the GL default-dimension bag, which is populated). The failure is downstream, on the projcontract.CompanyID = lgr.CompanyID predicate.
Case sensitivity ruled out. Initially suspected but confirmed non-issue: Silver upper-cases CompanyID on ingest across all four tables in the chain (ledger, projtable, custtable, projinvoicetable), all via UPPER(...) AS CompanyID in their select statements, with CheckType: "upper" quality checks on ledger and projinvoicetable. So although Block 1's Gold joins don't wrap CompanyID in f.upper() (unlike Block 2's joins which do), no case drift can reach them.
Primary hypothesis — intercompany cross-company mismatch. Block 1 filters to intercompany revenue (mainaccount 600500, PostingType 'Intercompany revenue', CINV/CCRN vouchers). By definition intercompany means the posting sits in one ledger company while the customer contract lives in the counterparty company, so projcontract.CompanyID = lgr.CompanyID is structurally wrong for this workload — the projcontract row exists, just not in the ledger's company, so the join returns null and the chain to custtable collapses.
Likely fix: look up the counterparty on projinvoicetable using the counterparty company rather than the ledger company. Check whether gjae or gje carries a counterparty CompanyID that can be joined into projcontract instead.
To validate: (1) drop the projcontract.CompanyID = lgr.CompanyID predicate and re-run — if match rate jumps, hypothesis confirmed; (2) compare with nb_fct_generalledger where the same code runs on non-intercompany GL; (3) once fixed, mirror Block 2's f.upper() style on Block 1 joins for consistency (defensive, not needed for correctness given Silver's normalisation). |