Project.Forecast

Purpose

The Project Forecast fact table is designed to display projected data related to future project performance, including anticipated costs, revenues, timelines, and resource requirements. This table integrates with dimension tables, such as Project_Details, to provide detailed insights into future planning and strategic decision-making. The primary purpose is to support forecasting, budgeting, and proactive project management across the organisation

Use Case

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

Source

FACT_PROJ_FORECAST_ITEM_OL

Key Parameters

Company
Project
Customer

Sample Exec

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

Response

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

SEQ

[ACTIVITY_SEQ],
[FORECAST_ID_KEY],
[ITEM_NO_KEY],

Dates

convert(date,[START_DATE],23) as [START_DATE],
convert(date,[END_DATE],23) as [END_DATE],
convert(date,[CUT_OFF_DATE],23) as [CUT_OFF_DATE],

Entity

project_item_id,
customer_item_id = ent1.customer_id,

Measures

Costs

cast([ESTIMATED] as decimal(10,3)) as [ESTIMATED],
cast([PLANNED] as decimal(10,3)) as [PLANNED],
cast([COMMITTED] as decimal(10,3)) as [COMMITTED],
cast([USED] as decimal(10,3)) as [USED],
cast([ACTUAL] as decimal(10,3)) as [ACTUAL],

Estimate

[ETC], -- Estimate to Complete
[EAC], -- Equivalent Annual Cost
[PREVIOUS_ETC],
[PREVIOUS_EAC],
[EAC_NET_CHANGE],

Codes

Project

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

Other Codes

[FORECAST_ITEM_TYPE],
[LINE_STATUS],
[CUT_OFF_PERIOD]

SQL

BMT-DWH-DEV/Project_Forecast.sql