staging.invoicestatuslookup and downstream DimInvoiceStatusLookUp.LedgerVoucher rows (e.g. 2020Ye/2020YE, 2023-OA-D-0552/RG/Rg). Two root causes identified:
subledgervoucher alone, so a UK voucher and a French voucher with the same number collapsed into one row (silently wrong).generaljournalentry.subledgervoucher contained case variants (finance user typing inconsistency). The view's DISTINCT and PARTITION BY use binary collation, so variants survived as separate dim rows.dataareaid throughout (derived by joining to landing.ledger — same pattern as FactGeneralLedgerTransaction), and normalise case with UPPER() at the earliest CTE. Belt-and-braces SourceDeduped in the load proc as a downstream safety net.
staging.invoicestatuslookup, plus load proc, dim table DDL, and verification queries. Agreed line-by-line, ready to deploy.| Item | Status | Change |
|---|---|---|
| CTE 1 — alv | Done | Added INNER JOIN landing.ledger on l.recid = gje.ledger, emit UPPER(l.name) AS dataareaid and UPPER(gje.subledgervoucher) AS subledgervoucher. Normalises case at the source; downstream partitions collapse variants automatically. |
| CTE 2 — ppj | Done | Added AND LOWER(alv.dataareaid) = LOWER(ppj.dataareaid) to the projproposaljour join. Carried alv.dataareaid through the SELECT. PARTITION BY now includes dataareaid. |
| CTE 3 — freetextinvoices | Done | Emit slvgje.voucherdataareaid AS dataareaid. PARTITION BY now on (generaljournalentry, voucherdataareaid). The join to custtrans was already correctly keyed on entity — no change there. |
| CTE 4 — ct | Done | Added dataareaid to both LEFT JOINs (project custtrans path and free-text path). Carried dataareaid through SELECT. ROW_NUMBER now partitions on (ledgervoucher, dataareaid), and ORDER BY prefers rows with non-null closed so the informative variant wins. |
| CTE 5 — ss | Done | No change. SELECT * carries dataareaid through automatically. The WHERE ct.myrow = 1 filter now correctly dedupes per (voucher, entity). |
| Final SELECT | Done | Emit CAST(dataareaid AS VARCHAR(16)) AS dataareaid as second column, right after ledgervoucher. Output is uppercase — matches analyst expectations and D365 convention. |
| LOAD.usp_diminvoicestatuslookup | Done | Full rewrite drafted. Version history header added. SourceDeduped CTE wraps the SELECT — partitions on UPPER(ledgervoucher), UPPER(dataareaid), orders by non-null settlementstatus then svsdocumentdate then ledgervoucher for a deterministic informative-row tie-break. DataAreaID added to INSERT + SELECT. |
| DIM.DimInvoiceStatusLookUp (table DDL) | Done | Add [DataAreaID] varchar(16) NULL as the second column. Deployment approach: use ALTER TABLE ADD in prod (non-destructive, column lands at end — accepted drift with git), drop-and-recreate in dev to keep DDL clean. |
| Verification queries | Done | Three checks drafted for post-deployment monitoring: (1) composite-key duplicates (should be zero); (2) case-insensitive duplicates (belt-and-braces); (3) legitimate cross-entity vouchers (sanity/audit, expected non-zero). Aliases use Dupes not RowCount to sidestep Fabric reserved-word parsing. |
| Concatenated Data Model lookup key | Done | Adds LedgerVoucherKey VARCHAR(420) as UPPER(ledgervoucher) + '|' + UPPER(dataareaid). Computed inline in the load proc INSERT (not in the staging view — keeps view clean, single source of truth for the concat expression). Persisted in dim table and exposed on DIM.InvoiceStatusLookUp. Lets Power BI use a single-column many-to-one relationship instead of a composite key. |
| DIM.InvoiceStatusLookUp (presentation view) | Done | Rewritten to expose DataAreaID (col 2) and LedgerVoucherKey (col 3) alongside existing columns. Uses CREATE OR ALTER for idempotent deploys. "Auto Generated" header comment dropped — must confirm whether the file is regenerated by an automated Fabric process before deploying, or the change gets overwritten. |
| Item | Status | What to do |
|---|---|---|
| Deploy staging view via Lakehouse script | Pending | Silver layer is a Fabric Lakehouse, so the view is deployed via a CREATE OR ALTER deployment script that runs against the SQL analytics endpoint (typically inside Create or Alter Staging Views.Notebook). Matthew is preparing this script. Full assembled view already drafted — ready to drop into the notebook cell. |
| Propagate LedgerVoucherKey to fact tables | Pending | Every fact table joining to DimInvoiceStatusLookUp needs its own LedgerVoucherKey column computed the same way (UPPER(ledgervoucher) + '|' + UPPER(dataareaid)). Candidates: FactGeneralLedgerTransaction, FactNetRevenue, plus any others surfaced by the semantic model audit. Without this, the single-column PBI relationship won't have a matching key on the fact side. |
| Check FACT.Derive_FactNetRevenue | Pending | Downstream consumer of the lookup. Check if it joins on ledgervoucher alone — if so, join key needs to become (ledgervoucher, dataareaid). |
| Audit other lookup views & dedup CTEs for the same bug | Pending | The invoicestatuslookup pattern is unlikely to be unique. Systematically review other staging.*lookup views (e.g. purchaseorderlookup, subledgerlookup, lkp_generalledgertransactions) and any dedup CTEs across the SilverGold notebook for: (a) partition/join keys missing dataareaid, (b) case-sensitive DISTINCT / PARTITION BY on voucher-like columns. Fix the same way where found. |
| Check Power BI semantic model | Pending | Dim will now have more rows (cross-entity vouchers become separate rows). Any fact→dim relationship keyed on LedgerVoucher alone will become many-to-many. Likely need a concatenated key column (LedgerVoucher + '|' + DataAreaID) on both sides. |
| Verify in prod | Pending | After deployment: run the three verification queries; confirm no case-variant duplicates in the dim; spot-check a cross-entity voucher (if any exists) resolves to the correct entity's settlement status; validate row counts against pre-refactor baseline. |