Project.Cost

Purpose

The Project Cost (& Revenue) fact tables ares designed to display quantitative data related to the costs (and revenues) associated with projects. This table integrates with dimension tables, such as project_details and client_details, to provide detailed insights into the financial performance of projects. The primary purpose is to support project management and strategic decision-making across the organisation.

Code & Source related to Project.Revenue

Use Case

Owner: Martyn Cole
Typical Use Case: For the calculation of Project Costs/Revenue within the Project Reporting Portal
Style: Fact, meaning it consists of the measurements, metrics or facts of a business process

Source

FACT_PROJECT_CONN_DET_OL

WHERE [ELEMENT_TYPE_DB_KEY] = ‘COST’

Key Parameters

Company
Project
Customer

Sample Exec

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

Response

src.[COMPANY],
src.[PROJECT_ID],
src.[CUSTOMER_ID],

SEQ

[ACTIVITY_SEQ],
[CONTROL_CATEGORY] = [PROJECT_COST_ELEMENT],
[ELEMENT_TYPE_DB_KEY],

Dates

convert(date,COALESCE([DATE_MODIFIED],[DATE_CREATED]),23) as [DATE_MODIFIED],
convert(date,LAST_TRANSACTION_DATE,23) as [LAST_TRANSACTION_DATE],

Entity

project_item_id,
customer_item_id = ent_cust.customer_id,

Measures

try_convert(numeric(15,3),cast([ACTUAL_TRANSACTION]as float)) as  [ACTUAL_TRANSACTION],

cast([BASELINE_HOURS] as decimal (10,3)) as [BASELINE_HOURS],
cast([ESTIMATED_HOURS] as decimal (10,3)) as [ESTIMATED_HOURS],
cast([PLANNED_HOURS] as decimal (10,3)) as [PLANNED_HOURS],
cast([ACTUAL_HOURS] as decimal (10,3)) as [ACTUAL_HOURS],

try_convert(numeric(15,3),cast([BASELINE_COST] as float)) as  [BASELINE_COST],
try_convert(numeric(15,3),cast([ESTIMATED_COST] as float)) as  [ESTIMATED_COST],
try_convert(numeric(15,3),cast([PLANNED_COST] as float)) as  [PLANNED_COST],
try_convert(numeric(15,3),cast([USED_COST] as float)) as [USED_COST],
try_convert(numeric(15,3),cast([ACTUAL_COST] as float)) as  [ACTUAL_COST],

Codes

Project

ent2.item_label AS PROJECT_NAME,
src.PROGRAM_ID,
src.SUB_PROJECT_ID,
ent2.PRU,
ent2.[Project Category],

Other Codes

COALESCE([BASELINE_REVISION_NUMBER],0) as [BASELINE_REVISION_NUMBER],	
CURRENCY_CODE_ID as CURRENCY_CODE,

SQL

BMT-DWH-DEV/Project_Cost.sql