Project.Expenses

Purpose

The Project Expenses (& Time) fact tables are designed to display quantitative data related to the expenses (and time) associated with projects. This table integrates with dimension tables, such as project_details and employee_details to provide detailed insights into the utilisation of resources and the costs incurred. The primary purpose is to support project management and operational efficiency across the organisation.

Code & Source related to Project.Time

Use Case

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

Source

FACT_PROJECT_TRANS_OL

WHERE Report_Group <> TIME

Key Parameters

Company
Project
Customer
Resource

Sample Exec

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

Response

[COMPANY_ID],
src.[PROJECT_ID],
src.[CUSTOMER_ID],
src.[RESOURCE_ID],

SEQ

[ACTIVITY_SEQ],
[PROJECT_TRANSACTION_SEQ_KEY],
[RESOURCE_SEQ],
dim_rpt.REPORT_CODE_GROUP,

Entity

project_item_id,
resource_item_id = ent_res.resource_id,
customer_item_id = ent_cust.customer_id,

Dates

convert(date,[ACCOUNT_DATE],23) as [ACCOUNT_DATE],

Measures

SALES

[SALES_QUANTITY],
[SALES_PRICE],
[SALES_AMOUNT],
[SALES_PRICE_BASE],
[SALES_AMOUNT_BASE],

INTERNAL

[INTERNAL_PRICE],
[OVERHEAD_PRICE],
[TOTAL_INTERNAL_PRICE],
[INTERNAL_AMOUNT],

INVOICE

[INVOICE_ID],
[INVOICABILITY],
[INVOICE_STATUS],
[CURRENCY_CODE],

Codes

Project

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

Employee

[EMP_NO],
[EMPLOYEE_CATEGORY],

Resource

RESOURCE_PARENT_ID = parent_res.item_code,
RESOURCE_DESCRIPTION = parent_res.item_desc,

Other Codes

[ORG_CODE],
[ORG_STRUCTURE_ID],
[POS_CODE],
[REPORT_CODE],
[WORK_LOCATION]

SQL

BMT-DWH-DEV/Project_Expenses.sql