Project.Cost_History

Purpose

For the calculation of Project Baseline Costs/Revenue

Use Case

Owner: Martyn Cole
Typical Use Case: Project Reporting Portal
Style: Fact

Data Marts

Company
Project
Customer

Code & Source related to Project.Revenue(History)

Source

FACT_PROJECT_HISTORY_LOG_OL

Where

[ELEMENT_TYPE_DB_KEY] = ‘COST’

Sample Exec

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

Response

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

SEQ

[ACTIVITY_SEQ],
[PROJECT_COST_ELEMENT],
[ELEMENT_TYPE_DB_KEY],
[ELEMENT_TYPE_DB_KEY] = [KEY_ELEMENT_TYPE_DB],

Dates

convert(date,[DIM_HISTORY_DATE_ID],23) as [HISTORY_DATE],

convert(date,[DIM_BASELINE_START_DATE_ID],23) as [BASELINE_START_DATE],
convert(date,[DIM_BASELINE_FINISH_DATE_ID],23) as [BASELINE_FINISH_DATE],

Measures

Transaction

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

Hours

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],

Cost

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

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

SQL

BMT-DWH-DEV/Project_Cost_History.sql