Extends the Phase 1 fact with Information attributes: worker attribution, project category, transaction type, and invoice status. Fact grain unchanged — still one row per projpostedtranstable.RecID. Four new Silver tables to add (hcmworker is already in Silver). All four design decisions closed (see Decisions resolved).
Approach: Purely additive to Phase 1 — grain is not filtered. Extend
nb_fct_projecttransaction with a projcategory → projcategorygroup join and the resresourceidentifier → wrkctrtable → hcmworker chain for worker attribution. Build a new dedicated dim_projcategory with its own surrogate key ProjectCategoryKey (rather than leaving category attributes as degenerate columns). WorkerKey resolves against the existing dim_worker via hcmworker.RecID. Enum resolution for CategoryType uses globaloptionsetmetadata (Silver's established enum source), with srsanalysisenums only as a fallback if the specific enum is not present in Dataverse global choices. InvoiceStatus surfaced as a fact column so reports can filter to invoiced-only downstream; no invoice filter applied at fact level, preserving Phase 1 grain.
Done Implemented and pushed
To do Remaining work
Optional Deferred / decision needed
Done
| Task | Scope | State | Notes |
|---|---|---|---|
| Review PBI Project Transactions Information TMDL | Analysis | Done | Confirmed source is projpostedtranstable joined to projcategory, projcategorygroup, srsanalysisenums, resresourceidentifier, wrkctrtable, hcmworker, dirpartytable. Filter tntprojinvoicestatus = 3 applied in original. |
| hcmworker in Silver | D365-Fabric-Silver | Done | Already present in Silver config from earlier work — no addition needed for Phase 2. Reused directly by the worker chain. |
| Add projcategory to Silver | D365-Fabric-Silver | Done | Silver config entry landed with RecID, ProjectCategoryCode, ProjectCategoryType (Enumeration via globaloptionsetmetadata), ProjectCategoryGroupCode, CompanyID, audit columns. |
| Add projcategorygroup to Silver | D365-Fabric-Silver | Done | Silver config entry with RecID, ProjectCategoryGroupCode, ProjectCategoryGroupName, CompanyID, audit columns. |
| Add resresourceidentifier to Silver | D365-Fabric-Silver | Done | Kept unfiltered at Silver — RefTableID = 12312 filter applied at Gold fact level. Columns: RecID, ReferenceRecID, ReferenceTableID, audit. |
| Add wrkctrtable to Silver | D365-Fabric-Silver | Done | Columns: RecID, WorkerID (RecID pointer to hcmworker, matches hcmpositionworkerassignment naming), CompanyID, audit. |
| Silver PR merge and pipeline run | D365-Fabric-Silver | Done | Four Silver additions bundled and merged (commit 10ce113). Enum resolution for ProjectCategoryType via globaloptionsetmetadata confirmed working (99.998% resolved, 1 Unknown row out of 49,620 category rows). |
| Create nb_dim_projcategory | D365-Fabric-Gold | Done | New dim built joining Silver projcategory to projcategorygroup on GroupCode + CompanyID. Business key ProjectCategoryCode + CompanyCode, surrogate ProjectCategoryKey generated by addMetadata. MERGE upsert pattern. 49,620 rows landed; uniqueness on business key confirmed. |
Add projcategory entry to gold_config |
D365-Fabric-Gold | Done | Schema entry for the new dim with 6 columns: ProjectCategoryUID, ProjectCategoryCode, ProjectCategoryType, ProjectCategoryGroupCode, ProjectCategoryGroupName, CompanyCode. |
| Add nb_dim_projcategory to Load Dims pipeline | D365-Fabric-Gold | Done | Wired in alphabetically between nb_dim_postingtype and nb_dim_project. Bundled with the notebook + gold_config as commit eb70678. |
| Extend nb_fct_projecttransaction with Information joins | D365-Fabric-Gold (PBI 12) | Done | Added worker chain (resresourceidentifier → wrkctrtable → hcmworker, RefTableID = 12312), ProjectCategoryKey derived from ProjectCategoryID + CompanyID business-key hash, WorkerKey derived from hcmworker.RecID matching dim_worker. Three pass-through columns: TransactionCode, ProjectTransactionType, InvoiceStatus. MERGE update set extended. Commit 3a0f11e. |
Update projecttransaction entry in gold_config |
D365-Fabric-Gold | Done | Added 5 columns: ProjectCategoryKey, WorkerKey (long), TransactionCode, ProjectTransactionType (string), InvoiceStatus (integer). Datatype gotcha: initially used "int" which failed createTable with AssertionError — corrected to "integer" (commit aa6be98). Memory saved. |
| Diagnostic SQL for new columns | Testing | Done | Fact row count 11,849,880 with no fan-out. FK resolution rates: ProjectCategoryKey 99.95%, WorkerKey 96.19% (expected drop-off since non-Hour transaction types have no Resource). All Phase 1 keys unchanged. Pre-existing Phase 1 anomaly noted: chartofaccount_pct = 0.00% (MAINACCOUNT default dimension not populated on project transactions in D365 F&O) — not Phase 2 scope but worth follow-up. |
Add dim_projcategory shortcut to FeeFlash config |
D365-Fabric-Gold-FeeFlash | Done | dim_projcategory entry added to feeflash_config.Notebook alphabetically between dim_postinglayer and dim_project. Commit 6517f40. |
Raise PRs from working into main |
D365-Fabric-Silver + Gold + FeeFlash | Done | All three PRs raised and merged: Silver (10ce113, 4 new tables), Gold (dim_projcategory build + fact extension + gold_config + Load Dims wire-up, commits eb70678, 3a0f11e, aa6be98), FeeFlash (6517f40, dim shortcut). |
Phase 2 complete. PBI 12 Project Transactions fact fully delivered across Silver, Gold, and FeeFlash.
Decisions resolved
| Decision | Scope | State | Notes |
|---|---|---|---|
Create dim_projcategory or leave as degenerate |
Design decision | Done | Create dedicated dim_projcategory. The four category columns (Code, Type, GroupCode, GroupName) form a natural hierarchy that suits a dim, and gives reports proper slicing / drill-down support. Surrogate key ProjectCategoryKey on the fact. |
| Enum resolution source | Design decision | Done | Use globaloptionsetmetadata to match Silver's established pattern (all existing Silver enum-resolved columns source labels from Bronze.globaloptionsetmetadata). No need to add srsanalysisenums to Silver upfront — keep it as a fallback if a specific enum turns out to be missing from Dataverse global choices. |
Include ProjectTransactionTypeValue (enum int)? |
Design decision | Done | No raw enum int. Only the descriptive ProjectTransactionType label ('Hour', 'Expense', 'Fee', 'Item') surfaced — already resolved in Silver via the existing Enumeration mapping on projpostedtranstable.projtranstype. |
| Worker attribution: dim FK or degenerate? | Design decision | Done | WorkerKey FK to existing dim_worker via hcmworker.RecID. Matches codebase convention (all facts reference dims via surrogate keys). Confirm dim_worker coverage during diagnostic SQL step. |
Apply the tntprojinvoicestatus = 3 filter? |
Design decision | Done | No filter applied — Phase 1 grain preserved (Path A). Surface InvoiceStatus as a fact column so reports can filter to invoiced-only downstream. Non-breaking: same row count as Phase 1, Information columns populate for every posted transaction (they are known regardless of invoice status). |