Project.Revenue

Purpose

The Project Revenue (& Cost) fact tables are designed to display quantitative data related to the revenues (and costs) 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.Cost

Use Case

Owner: Martyn Cole
Typical Use Case: For the calculation of Project Costs/Revenue within 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] = ‘REVE’

Key Parameters

Company
Project
Customer

Sample Exec

EXEC get.myView @dataMart = 'Project', @viewName = 'Revenue', @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 = ent1.customer_id,

Measures

try_convert(numeric(15,3),cast([ESTIMATED_REVENUE] as float)) as  [ESTIMATED_REVENUE],
try_convert(numeric(15,3),cast([PLANNED_REVENUE] as float)) as  [PLANNED_REVENUE],
try_convert(numeric(15,3),cast([POSTED_REVENUE] as float)) as [POSTED_REVENUE],
try_convert(numeric(15,3),cast([ACTUAL_REVENUE] as float)) as  [ACTUAL_REVENUE],

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_Revenue.sql