Project.Dates

Purpose:

The purpose of this DataMart view is to provide a comprehensive timeline overview of a project’s key dates, from its creation to completion. It consolidates important project milestones such as planned and actual start and finish dates, delivery estimates, and project modifications. This view is designed to help stakeholders monitor project timelines, identify delays, and assess the accuracy of planning versus actual outcomes.

Synopsis:

This view retrieves project data from the ifs.DIM_PROJECT_OL$ source table, focusing on key date fields for tracking project life cycles. The key fields include:

• Project Identifiers: COMPANY, PROJECT_ID are used to uniquely identify the projects.

• Creation and Modification Dates:

  • Creation Date: Uses BASELINE_CREATED_DATE or falls back on PROJECT_MODIFIED_DATE.
  • Modification Date: Tracks when the project was last modified using PROJECT_MODIFIED_DATE.

• Project Planning and Actual Dates:

  • Start and End Dates: Includes planned (PLAN_START, PLAN_FINISH) and actual (ACTUAL_START, ACTUAL_FINISH) start and end dates, enabling comparison between project plans and actual execution.
  • Estimated Delivery Date: Uses PLAN_FINISH to track when the project is expected to complete.
  • Completion Date: Uses the most relevant date (either CANCELLED_DATE, CLOSE_DATE, or ACTUAL_FINISH) to signify the project’s end.

• Other Key Dates:

  • Approval Date: APPROVE_DATE marks when the project was formally approved.
  • Contractual Dates: Includes custom fields like CF$_CONTRACT_START_DATE and CF$_CUST_AGREED_FIN_DATE for contract-specific milestones.
  • Cancelled or Closed: Tracks if and when the project was cancelled or closed (CANCELLED_DATE, CLOSE_DATE).

This view is particularly useful for project managers and analysts to ensure alignment between planned and actual project timelines, track modifications, and identify whether projects were completed, cancelled, or delivered late. By providing these details, the view facilitates decision-making, project auditing, and forecasting future project timelines based on historical data.

Use Case

Owner: Martyn Cole
Typical Use Case: This view is designed to help stakeholders monitor project timelines, identify delays, and assess the accuracy of planning versus actual outcomes.
Style: Meta, designed with the intention of providing one fact per PROJECT_ID

Source

Key Parameters

Company
Details

Sample Exec

EXEC get.myView @dataMart = 'Project', @viewName = 'Dates', @token = '<myToken>', @version = '<202404>'

Response

SEQ

Dates

Measures

Codes

SQL

BMT-DWH-DEV/Project_Dates.sql