关于Tfs_Warehouse中FactCurrentWorkItem与DimWorkItem表关联的问询

Hey there! Let's unpack why you might encounter multiple FactCurrentWorkItem records linked to a single DimWorkItem—it’s not a bug, it’s intentional design tied to how TFS’s warehouse tracks work item history and changes.

Why the One-to-Many Exists #

• State transition snapshots: Every time a work item changes state (e.g., from New → Active → Resolved → Closed), the warehouse creates a new FactCurrentWorkItem record. The DimWorkItem table stores the core, relatively static attributes of the work item (like its ID, title, creator), while each fact record captures a time-stamped snapshot of its state, assignee, priority, and other mutable fields at that moment.
• Key field updates: Even if the state doesn’t change, updates to critical fields (like reassigning the work item, adjusting priority, or modifying its iteration path) can trigger a new fact entry. This lets you track how the work item’s properties evolved over time for reporting or analysis.
• Warehouse sync behavior: Occasionally, warehouse refreshes or metadata changes might generate duplicate-looking fact records, but these will always have distinct ChangedDate values or other identifiers to help you pinpoint the latest state.

Example Query to Explore the Relationship #

If you want to see all snapshots for a specific work item, you can run a query like this:

SELECT
    f.WorkItemSK,
    f.State,
    f.ChangedDate,
    f.AssignedTo,
    d.Title,
    d.WorkItemID
FROM
    FactCurrentWorkItem f
INNER JOIN
    DimWorkItem d ON f.WorkItemSK = d.WorkItemSK
WHERE
    d.WorkItemID = 1234 -- Replace with your target work item ID
ORDER BY
    f.ChangedDate DESC;

Quick Recap #

Think of DimWorkItem as the "master profile" for a work item, and FactCurrentWorkItem as a log of all its significant changes over time. This one-to-many structure is built to support historical reporting, cycle time analysis, and tracking work item progress—something a simple one-to-one link couldn’t do effectively.

内容的提问来源于stack exchange，提问作者Bill Greer