Project.Details_Plus

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: Combines COMPANY and PROJECT_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