Conformed Dimension for Transaction Fact and Accumulating Snapshot Fact Table

[spaul]

I'm working on a new warehouse application for reporting against our IT Project database. I want to create both a transaction fact table and an accumulating snapshot fact table. The project database (source) consists of multiple normalized tables to record project number, description, developers, project requestor, etc. In addition, there is a status log that records the date, old status, and new status every time a project's status changes. For example, a project may change from Discovery to Spec/Design to Coding/Testing to Go Live, etc. Each of these changes are recorded in the status change table. It is possible for a status to be repeated for an individual project.

In addition, there are some dates that are manually entered as fields on the project header. These are not changes in status so do not appear in the status log. These dates include Request Date, Approved Date, Estimated Delivery Date.

My problem, if I create a transaction-based fact table, I'm unsure where to put my manually-entered dates. The grain of the fact table would be one row for every status change of a project. The manual dates are more like attributes of my project dimension and are not the correct grain to be surrogates of the fact table. The project dimension feels like the right place for these dates. (see Kimball Design Tip #61: Handling All The Dates)

Then I'd also like an accumulating snapshot fact table with the grain of one record per project. This would be used to record all of the manually entered dates (using a surrogate key pointing to different views of a date dimension) and also include a date surrogate for every status date for the project (like Coding/Unit Test date or Go Live date) with lag counts and status flags.

Am I on the right track? I'm unsure how I would then reuse my project dimension (trying to use conformed dimensions) that I created for the transaction-based fact table because it would have the manual dates as dimension attributes which are needed in the accumulating snapshot fact table. Any advice is appreciated.

[ngalemmo]

The accumulating snapshot you are trying to build sounds a lot like the project dimension itself... what is the point of the snapshot that isn't already covered in the transactional fact?

Also, from dealing with modeling project stuff in the past, are there not tasks, resources and project phases to deal with? What you describe seems to be missing a lot, but then again, I don't know what the requirements or intent of the model is...

[spaul]

The accum. snapshot would allow me to pull up the current status of any project and compare multiple dates more easily if the comparisons are across a single row. The accum snapshot would include both the manual dates as well as track the project status dates. I want the users to be able to run a report showing the current status of each project and to easily know how much time has passed between two dates (both some of the manual dates as well as the status change dates). If I only used the transaction fact table, then I would have to select the most recent transaction date to find the current status since each project would have many records. Then, reporting on the time that passed between the manual dates and/or status changes would be quite tricky in a reporting environment because I would have to perform multi-row comparisons. From everything I've read, the accum. snapshot seems like a better fit for comparing dates from beginning to end.

As far as the project database itself, this is perhaps a more simplified version of what you are used to seeing. The project phases are the "status" entries. We are not tracking resource time on each project through this database. I will have a resource dimension that will simply reference the lead developer and manager. There will also be a requestor dimension as well as a few others. This database is used to track the phases of all of the open projects and provides a quick way for management to see what is pending. This lists all projects from five-minute fixes to major projects involving teams of people and multiple systems. For advanced projects, there are seperate project plans, resource lists, and tasks lists maintained outside of this database. My focus is to report on the project status, status dates, and other major dates that are stored in the project header. Make sense?

[ngalemmo]

Why not just keep current status as an attribute of the project dimension?

As far as dates go, keeping start and end dates of each status in the transactional fact would allow you to easily calculate duration without the need of the snapshot. It would also make point-in-time reports simple.

[hang]

It is possible for a status to be repeated for an individual project.

There is another thread on the similar subject: http://forum.kimballgroup.com/dimensional-modeling-and-data-architecture-f6/help-in-declaring-grain-and-identifying-dimensions-t669.htm

From my understanding on Kimball's famous three types of facts, the accumulating snapshot should do the work in your case, and the lags between dates should be worked out by ETL at the time of the snapshot, instead of leaving the calculations to the reporting time. The only issue is how to model the repeated status in the snapshot. I think when a project is concluded, the snapshot should reflect the final state of the project life cycle from macro perspective, and the more granular mini cycles should be reflected in transaction fact table.

[spaul]

Thanks so much to both of you for taking the time to post. I appreciate the insights from both as I'm still considering two fact tables.

hang: Thanks for the reference to the similar post; I'm reviewing that. For my example, since the grain of transaction fact would be one row per transaction and the grain of the accum snapshot would be one row per project, it seems like having a single conformed dimension for project might not work out. For the accum snapshot, I was thinking of having individual dimensions for project, status, issue type, and requestor. Does this seem like the right approach?

ngalemmo: I've more fully described my proposed dimensions and am hoping this in not overkill. I'm unsure how to load a transaction fact table that includes the duration of each without having to compare a single transaction to the entire history. Can you overview how it is done to get me started? (Not sure if I'm asking for a mountain or a molehill with my question.)

Originally, I drew up a plan that had a seperate dimension for project, status, issue type, and requestor, but it seemed to imply that these dimensions applied to each transaction rather than to each project and didn't seem to fit the grain of the fact table. I've revised this.

Source
------
This is what my source for the Status Changes looks like:
Sequence project# Change_date Status_from Status_to Developer
123 3800 8/1/2010 Not Started Discovery jsmith
124 3800 8/2/2010 Discovery Spec/Design jsmith
125 4101 8/2/2010 User Testing Go Live jdoe
126 3800 8/3/2010 Spec/Design Coding jsmith

Basically, everything else is in the project header and related tables (3rd normal form).

Proposed Fact/Dimensions
-------------------------
TRANSACTION FACT
Grain: One record for every status change for a project

Fact_Project_Status_Change
---------------
project_id FK
change_date_id FK to dim date
status_from FK to View:dim status
status_to FK to View:dim status
developer_id FK

Dim_project
---------------
project_id
project_number
project_title
current_status
priority
complexity
issue_type_desc (New, Enhancement, Bug Fix)
requester_name
requester_department
steering_committee_member
lead_developer_name
developer_group
project_manager_name
project_request_date
project_approved_date
project_estimated_delivery_date

Dim_Status
---------------
project_status_id
status_desc (Not Started, Discovery, Spec/Design, Coding, User Testing, Go Live, Completed)


Dim Developer
---------------
developer_id
developer_name

Dim_Date
---------------
date_id
date
YYYYMM
Etc.

[hang]

I was thinking of having individual dimensions for project, status, issue type, and requestor

As far as accumulating snapshot is concerned, project_id is the only FK in the fact, others are just attributes of project dimension. I can see the reason for the Status dimension as it is a role playing dimension in Fact_Project_Status_Change. I am not sure about developer_id in the fact, unless it is the lead developer at the point of time. I imagine you would have an effective dated project_developer factless fact table to track the many to many relationship. However the most important one is the structure of your accumulating snapshot.

[Sponsored content]