Build plan for the core Proposal fact table (gold.fct_proposal). Grain: one row per projproposalcost.RECID (Cost proposal type only). Phase 1 delivers the Cost branch of what will eventually be a five-way UNION mirroring the original GL SL Proposal query. Empl / Item / OnAcc / Revenue proposal types deferred to a future Phase 2 (requires additional Bronze ingestion work first, since those source tables are not currently in the 67-table Bronze list).
Approach: Follow the pattern proven by Project Transactions (PBI 12 Phase 1). Source is Silver's
projproposalcost, joined to projcosttrans (transaction detail), projproposaljour (proposal header), projproposalcostdetail (line detail), resresourceidentifier (worker chain start), plus the default-dimension resolution DataFrame (fd_resolved). Absorb Proposal Information attributes into the fact per PBI 11 (assign to existing dims where possible, otherwise degenerate on the fact, otherwise discard). Currency conversion follows the Currency Conversion Standard — 18 amount columns (3 measures × 6 currency variants), all 5 non-Transaction variants derived via rate joins since the Proposal source tables store Transaction currency only.
Done Implemented and pushed
To do Remaining work
Optional Deferred / decision needed
Done
| Task | Scope | State | Notes |
|---|---|---|---|
| Review PBI 11 spec and Fee Flash V3 Proposal + Proposal Information TMDL | Analysis | Done | Confirmed target column list from Proposal.tmdl (32 columns: RECID, DATAAREAID, CurrencyID, 7 FDx default-dim columns, TransDate, InvoiceDate, 7 RecID pointers, ProjectTransactionType enum, 18 amount columns spread across 6 currency variants x 3 amount types). Proposal Information TMDL has ~30 attributes for absorption. |
| Review GL SL source query | Analysis | Done | Original PBI Proposal is a UNION of FIVE proposal-type subqueries: Cost (PROJPROPOSALCOST), Empl (PROJPROPOSALEMPL), Item (PROJPROPOSALITEM), OnAcc (PROJPROPOSALONACC), Revenue (PROJPROPOSALREVENUE). Each joins its own transaction detail table, plus common joins to PROJPROPOSALJOUR (header) and RESRESOURCEIDENTIFIER (worker chain). Only Cost source tables are in the current Bronze — other four types deferred. |
To do
| Task | Scope | State | Notes |
|---|---|---|---|
| Add projproposaljour to Silver | D365-Fabric-Silver | To do | Proposal header table. Columns needed: RECID, PROPOSALID, DATAAREAID, plus any header attributes needed for the fact (e.g. status, dates). Already in Bronze (in the 67-table ingest list). |
| Add projproposalcost to Silver | D365-Fabric-Silver | To do | Cost proposal line table — the primary source (fact grain). Columns needed: RECID, DATAAREAID, PROPOSALID, TRANSID, CURRENCYID, TRANSDATE, DEFAULTDIMENSION, 6 amount variants (Tx / Acc / Reg / Rep / GBP / USD invoice-nett), plus RecID pointers. Already in Bronze. |
| Add projproposalcostdetail to Silver | D365-Fabric-Silver | To do | Cost proposal line detail. Columns needed: RECID, PROPOSALREFRECID (join back to projproposalcost.RECID), plus any additional amount / attribute columns referenced by the GL SL Cost branch. Already in Bronze. |
| Add projcosttrans to Silver | D365-Fabric-Silver | To do | Cost transaction lookup (joined from projproposalcost via DATAAREAID + TRANSID). Provides Resource and other transaction attributes. Already in Bronze. |
| Silver PR merge and pipeline run | D365-Fabric-Silver | To do | Merge Silver PR and run ingest so all four new tables land in the Silver lakehouse. Verify enum resolution for ProjectTransactionType via globaloptionsetmetadata. |
Add proposal fact entry to gold_config |
D365-Fabric-Gold | To do | Full schema entry with ProposalUID, FKs (CompanyKey, AccountingDateKey, CurrencyKey, CostCenterKey, CounterpartKey, DepartmentKey, OfficeKey, LeadProjectKey, ChartofAccountKey, ProjectKey, ProjectCategoryKey, CustomerKey, WorkerKey), 18 amount columns (raw), degenerate date InvoiceDate, plus absorbed Information attributes and Group B degenerate RecIDs (see decisions). |
| Create nb_fct_proposal | D365-Fabric-Gold (PBI 11) | To do | Full Cost-branch build following the projecttransaction pattern. Source: projproposalcost. Joins: projcosttrans, projproposaljour, resresourceidentifier → wrkctrtable → hcmworker for worker chain, fd_resolved x 7 for default dimensions. Null-guarded xxhash64 for all FK derivations, matching the corresponding dim's key derivation. |
| Add fact to Gold Orchestration pipeline | D365-Fabric-Gold | To do | Insert nb_fct_proposal into the Load Facts child pipeline alphabetically (goes between nb_fct_headcount and nb_fct_projecttransaction). |
Add fct_proposal shortcut to FeeFlash config |
D365-Fabric-Gold-FeeFlash | To do | Add entry to feeflash_config.Notebook (source_schema gold, destination_schema fee_flash). Direct pass-through, no _feeflash variant unless the FeeFlash workspace needs a filtered / slimmer version. |
| Diagnostic SQL to verify FK joins and row count | Testing | To do | Same pattern as projecttransaction validation: row count vs Silver baseline, ProposalUID uniqueness, per-key resolution rate (Key <> 0), FK-to-dim JOIN validation for new-territory dims (dim_customer, dim_projcategory, dim_worker), amount magnitudes sanity check. |
Raise PR from working into main |
D365-Fabric-Silver + Gold + FeeFlash | To do | Three PRs, one per repo: Silver (4 new tables), Gold (fct_proposal build + gold_config + Load Facts), FeeFlash (shortcut). |
Decisions resolved
| Decision | Scope | State | Notes |
|---|---|---|---|
| Phase 1 scope: Cost only vs all five proposal types | Design decision | Done | Cost only for Phase 1. The four Cost-branch source tables (projproposalcost, projproposalcostdetail, projproposaljour, projcosttrans) are already in Bronze. Adding Empl / Item / OnAcc / Revenue types requires 8+ extra Bronze ingest additions and is deferred to a future Phase 2. Cost is the largest proposal type in most consulting businesses so covers the majority of report needs on its own. |
Notebook / table naming: fact_ vs fct_ |
Design decision | Done | Use fct_proposal and nb_fct_proposal. PBI 11 spec uses fact_proposal, but codebase convention (driven by getPrefix() in nb_0001_functions_gold) is fct_. Matches every other fact in the repo. |
| Date handling: TransDate and InvoiceDate | Design decision | Done | TransDate hashed to AccountingDateKey (FK to dim_accountingdate). InvoiceDate kept as a raw degenerate date column on the fact (no FK). Matches the fct_generalledger pattern (line 705 raw AccountingDate + line 712 raw InvoiceDate). The Fee Flash V3 semantic model already relates Proposal.InvoiceDate to 'Transaction Date'.Date directly (date-to-date), not through a surrogate — so no need to build a dim_invoicedate roleplay. |
| Amount handling: fact columns and currency conversion approach | Design decision | Done |
Materialise on the fact, following the Currency Conversion Standard. 18 amount columns total: 3 measures × 6 currency variants each. All rate-derived except the native Transaction column.
3 core measures (derived from GL SL Cost-branch query):
Why all 5 non-Transaction variants are derived: Both source tables store transaction currency only — confirmed by Bronze schema checks. projproposalcostdetail has amountcur but no amountmst. projproposaljour has salesorderbalance, invoiceamount, costvalue, and other amounts but zero MST-suffixed columns. All conversions to Accounting / Reporting / Regional / Global / GBP go through rate joins for both source tables. Considered but rejected: using the header's exchrate / exchratesecondary columns for direct multiplication instead of a rate-table lookup — would be more historically accurate but breaks pattern-consistency with the GL SL source and other facts.
Rate lookup: BUDGET rate type, FROM = transaction ccy, rate at transaction date, coalesce to 1 on unmatched, using the base + inverse rate union pattern. Full spec on the Currency Conversion Standard page. |
| Sign convention on amounts | Design decision | Done | No sign flip applied. Matches projecttransaction Phase 1 and the GL SL Proposal source query. Proposal amounts are stored positive natively in D365 (forward-looking commitments rather than double-entry postings). |
| Rolling window on TransDate | Design decision | Done | 3-year rolling window applied (TransDate >= trunc(add_months(current_date(), -24), 'year')) matching projecttransaction and other facts. Bounded volume, faster refresh, consistent cross-fact date filtering. Proposals older than 3 years are almost certainly stale for live reporting. |
| Ledger exclusion (GB01 / GB02 / GB03) | Design decision | Done | GB01, GB02, GB03 excluded — standing convention across every fact. Applied via a left join to ledger then filter to ledger.CompanyID NOT IN (GB01, GB02, GB03) OR ledger.CompanyID IS NULL. These legal entities are test / defunct / setup and never appear in business reporting. |
Decisions needed
| Decision | Scope | State | Notes |
|---|---|---|---|
ProjectKey source: projrecid or FDProject |
Design decision | Decide | Same choice made in PBI 12 Phase 1 (chose projrecid via a real projtable join). Recommendation: same call here for consistency. GL SL Proposal query surfaces both projrecid and FDProject. |
| Group B RecIDs: activityrecid, fundingsourcerecid, itemidrecid | Design decision | Decide | Three RecID pointers in the Proposal table with no existing dim. Options per RecID: (a) new dim, (b) degenerate long column on fact (ActivityRecID, FundingSourceRecID, ItemRecID), (c) discard. Recommendation: all three degenerate to avoid scope creep — consulting-firm reports rarely slice by funding source or item, and activity is niche. Promote to full dims later only if a specific report needs it. |
| CurrencyKey derivation: RecID pointer or currency code | Design decision | Decide | Proposal.CurrencyID is an int64 RecID pointer to the currency table — different from projecttransaction where the currency link was via the string code CURRENCYID. Need to align CurrencyKey hash derivation with what dim_currency / dim_currencytype actually keys on. Requires reading the dim notebook before finalising. |
| Proposal Information disposition: 30 attributes to absorb, degenerate, or discard | Design decision | Decide | Per PBI 11, Proposal Information is not a standalone Gold table — its ~30 attributes are absorbed into the fact. Each column needs a call: assign to an existing dim (e.g. Cost Centre Match may belong on dim_costcenter), add as degenerate column, or discard. Requires per-column review; not blocking Phase 1 fact structure but must be completed before the fact schema is finalised. |