Purpose
The Project.Details_Plus
dimension table provides an extended view of project details, offering a comprehensive set of attributes essential for data segmentation and analysis. This table consolidates information relevant to various aspects of project management, including project identification, attributes, codes, dates, and manager details. It integrates seamlessly with fact tables and other dimensions to support detailed reporting and analysis across multiple departments and systems within the organisation.
Designed for adaptability, this table ensures scalability for evolving reporting needs and is optimised for integration into the wider data ecosystem.
Use Case
Owner: Martyn Cole
Typical Use Case: Used in the Project Reporting Portal to segment and analyse project data by various attributes such as project status, milestones, and financial details.
Style: Dimension table containing a single row per PROJECT_ID
DataMarts Integration
This view can be utilised across the following DataMarts:
- Company: To align project data with organisational details.
- Customer: To link projects with customer-specific data.
- Business Opportunity: To track and analyse linked opportunities.
Source
DIM_PROJECT_OL$Integrations:
DIM_EMPLOYEE_OL$
: To retrieve project manager details.QFACT_OPP_PROJECT_CLV_OL$
: For opportunity numbers.DIM_COMPANY_OL$
: For company currency codes.QFACT_PROJECT_HISTORY_LOG_OL$
: For historical baseline comments.
Update Frequency: Daily, aligned with the Data Warehouse refresh schedule.
Sample Exec
EXEC get.myView @dataMart = 'Project', @viewName = 'Details_Plus', @token = '<myToken>', @version = '<202404>'Response
Identifiers:
- Includes unique identifiers such as
COMPANY
,PROJECT_ID
,CUSTOMER_ID
,OPPORTUNITY_NO
,- and
PRU_Code
.
- Hash-based identifiers for simplified integration:
PROJECT_SEQ
: CombinesCOMPANY
andPROJECT_ID
.
Attributes:
PROJECT_NAME
,PROJECT_DESCRIPTION
CURRENCY_CODE
is coalesced with the company’s currency
JSON Groupings
Category:
Groups project categories, types, and methods.
{
"Project_Category": "Category1",
"PROJECT_TYPE": "TypeA",
"EARNED_VALUE_METHOD": "MethodX"
}
Status:
Tracks project status and risk indicators.
{
"STATUS": "Active",
"WORK_AT_RISK": "Yes",
"Project_Status": "WAR"
}
Codes:
Encapsulates program IDs, codes, and market information.
{
"PROGRAM": {
"ID": "PROG001",
"DESC": "Development Program"
},
"CODE_C": {
"CODE": "C001",
"DESC": "Code Description C"
},
"MARKET": {
"CODE": "MK001"
}
}
Dates:
Provides various date attributes related to project milestones and status:
{
"Creation_Date": "2023-11-01",
"Start_Date": "2023-11-15",
"End_Date": "2024-03-30"
}
Further Dates: are available in Project.Dates
Manager:
Manager details, including the name and email of the project manager
{
"Title": "Manager - John Doe",
"Details": {
"ID": "M001",
"NAME": "John Doe",
"EMAIL": "john.doe@example.com"
}
}
Baseline:
Contains revision numbers, planning updates, and comments.
{
"REVISION_NUMBER": "001",
"PLND_VALUES_UPD": {
"BY": "John Doe",
"LAST": "2024-02-15",
"Comment": "Baseline updated for Q1"
}
}
SQL
To access the full SQL structure, refer to…
BMT-DWH-DEV/Project_Details_Plus.sql
Checked 19/06/2024
12 projects are in DWH that are not in the G-MIS view
These are 12 projects with Project Access OFF, thus not in the G-MIS view
No Action required, these are capability projects that have now been replaced by others.
Source of Opportunity No.
Business Rule…
MAX(Opportunity No) WHERE Project is Delivery and Opportunity has positive status (incl. CLOSED)
LEFT JOIN
(
SELECT
MAX(CF$_BO_NO) AS OPPORTUNITY_NO,
CF$_PROJECT_ID AS PROJECT_ID
FROM
ifs.QFACT_OPP_PROJECT_CLV_OL$
JOIN
ifs.FACT_BUSINESS_OPP_OL AS status
ON CF$_BO_NO = OPPORTUNITY_NO
AND status.STATE NOT IN
(
'CANCELLED',
'UNCONFIRMED'
)
WHERE
LEFT(CF$_PROJECT_ID, '1') = 'D'
GROUP BY
CF$_PROJECT_ID
)
AS busopp
ON busopp.PROJECT_ID = src.PROJECT_ID