Unfortunately, this pertains to a large financial project used to manage recoveries from litigation/settlements, so I’m limited in what I can share. However, here’s the overall data model:
The analytical powerhouse of the structure is the Master Transactions table which integrates data from a number of different authoritative financial sources. In addition to the key fields linking each transaction back to the relevant dimension table, and a series of fields tracking the status of workflow approvals for transactions, we’ve employed a “long and narrow” approach, the core of which is the following fields for each record:
Transaction ID
Transaction Date
Transaction Type
Amount
where Transaction Type encompasses Receipts, Obligations, Allocations, Expenditures, Deobligations, Manual Adjustments, Transfers In and Transfers Out.
This dovetails beautifully with the CALCULATE/FILTER paradigm in DAX, where we perform operations on Amount, filtered by Transaction Type. Then linking the Transaction Date to @Melissa’s Extended Date Table gives us the ability to do a full range of time intelligence analysis.
Hope this is helpful. Would be glad to take a look at your current data model/fact table structure.
- Brian