Project.Value

Purpose

The Project Value fact table is designed to display summarised financial data related to project hours, costs, and revenues. It integrates with dimension tables, such as project_details and client_details, to provide a comprehensive view of the financial performance of each project. The primary purpose is to support project management, strategic decision-making, and financial analysis across the organisation by offering clear insights into project cost and revenue dynamics.

Synopsis

The project.value view aggregates various financial and time-tracking metrics associated with each project. It includes data on baseline, estimated, planned, earned, committed, and actual costs, alongside revenue estimations and postings. Key joins include project_details for detailed project information and company_details for currency codes. This structure allows users to monitor project performance through key financial indicators, such as earned value, actual costs, and planned vs. posted revenues.

Use Case

Owner: Martyn Cole
Typical Use Case: Requested initially for the provision of total cost values to 
Style: Meta, designed with the intention of providing one fact per PROJECT_ID

Output Summary

The Project Value view calculates and sums the following key metrics for each project:

• Hours:
  • Total Baseline, Estimated, Planned, and Actual hours, aggregated across all project activities.
• Costs:
  • Total Baseline, Estimated, Planned, Earned, Committed, and Actual costs, providing a comprehensive summation of project expenses.
• Revenue:
  • Summed Estimated, Planned, Posted, and Actual revenue, offering a complete picture of the project’s financial inflows.

Source

QFACT_BMT_PROJ_CONN_OVERV_IAS$

DataMart Integration

src.COMPANY_ID AS COMPANY: This field represents the unique identifier for the company associated with the project, linking the project to its parent organisation.

src.PROJECT_ID: The PROJECT_ID serves as the primary key, uniquely identifying each project and allowing the fact table to consolidate all relevant data under this single reference.

dim_proj.CUSTOMER_ID: The CUSTOMER_ID is retrieved from the Project.Details_Plus dimension, providing a link between the project and the associated customer for tracking and reporting purposes.

dim_proj.CODE_E as PRU_Code: The PRU_Code represents the cost centre or business unit code linked to the project, sourced from the project dimension to support financial tracking and allocation.

Sample Exec

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

Response

SEQ

    src.COMPANY_ID AS COMPANY,
    src.PROJECT_ID,
    dim_proj.CUSTOMER_ID,  -- From Project.Details_Plus
    dim_proj.CODE_E as PRU_Code,

Dates

Measures

Hours

    sum(CAST(CAST(src.BASELINE_HOURS AS FLOAT) AS DECIMAL(10,3))) AS BASELINE_HOURS,
    sum(CAST(CAST(src.ESTIMATED_HOURS AS FLOAT) AS DECIMAL(10,3))) AS ESTIMATED_HOURS,
    sum(CAST(CAST(src.PLANNED_HOURS AS FLOAT) AS DECIMAL(10,3))) AS PLANNED_HOURS,
    sum(CAST(CAST(src.ACTUAL_HOURS AS FLOAT) AS DECIMAL(10,3))) AS ACTUAL_HOURS,

Costs

    sum(CAST(CAST(src.BASELINE_COST AS FLOAT) AS DECIMAL(15,3))) AS BASELINE_COST,
    sum(CAST(CAST(src.ESTIMATED AS FLOAT) AS DECIMAL(15,3))) AS ESTIMATED_COST,
    sum(CAST(CAST(src.PLANNED_COST AS FLOAT) AS DECIMAL(15,3))) AS PLANNED_COST,
    sum(CAST(CAST(src.EARNED_COST_VALUE AS FLOAT) AS DECIMAL(15,3))) AS EARNED_VALUE_COST,
    sum(CAST(CAST(src.COMMITTED AS FLOAT) AS DECIMAL(15,3))) AS COMMITTED_COST,
    sum(CAST(CAST(src.ACTUAL AS FLOAT) AS DECIMAL(15,3))) AS ACTUAL_COST,

Contingency

SUM(IIF(CONTROL_CATEGORY='CONTINGENCY',CAST(CAST(src.BASELINE_COST AS FLOAT) AS DECIMAL(15,3)),0)) as BASELINE_CONTINGENCY,
    SUM(IIF(CONTROL_CATEGORY='CONTINGENCY',CAST(CAST(src.PLANNED_COST AS FLOAT) AS DECIMAL(15,3)),0)) as PLANNED_CONTINGENCY,

Revenue

    sum(cast(cast(src.ESTIMATED_REVENUE as float) as DECIMAL(15,3))) as ESTIMATED_REVENUE,
    sum(cast(cast(src.PLANNED_REVENUE as float)  as DECIMAL(15,3))) as PLANNED_REVENUE,
    sum(cast(cast(src.POSTED_REVENUE as float)  as DECIMAL(15,3))) as POSTED_REVENUE,
    sum(cast(cast(src.ACTUAL_REVENUE as float) as DECIMAL(15,3))) as ACTUAL_REVENUE,

Codes

    COALESCE(dim_proj.BASELINE_REVISION_NUMBER, '0') AS BASELINE_REVISION_NUMBER,
    COALESCE(dim_proj.PROJECT_CURRENCY_CODE, dim_company.CURRENCY_CODE) AS CURRENCY_CODE

SQL

BMT-DWH-DEV/Project_Value.sql