Project.Details

Purpose

The Common View for Project Details, excluding dates or measures, acts as a unified and reliable source for data on projects. It is accessible to different departments and systems across the organisation. This view supports integration with fact tables for reporting and analysis and provides a limited set of dimension attributes.

Use Case

Owner: Data Engineering
Typical Use Case: Supplies content for a Project slicer, ensuring a uniform style for Title descriptions.
Style: Dimension, meaning a single row per Project ID

Why This Version?

• Focus on Essentials: It keeps the essential fields for a quick view without overloading the report with too many fields.
• Description Concatenation: The shortened title is great for summary views and dashboards, which is often needed for display in compact spaces.

This version balances brevity with a bit of added context, and it provides a sufficient overview while remaining lightweight.

Source

DIM_PROJECT_OL

Parameters

Company
Project
Customer
Business Opportunity

Sample Exec

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

Response

    src.COMPANY,
    src.PROJECT_ID,
    src.CUSTOMER_ID, --2024-04-23 Change to Source
    busopp.OPPORTUNITY_NO,

    CONVERT(NVARCHAR(32),HashBytes('MD5', CONCAT_WS('|',src.COMPANY,src.PROJECT_ID)),2) as project_item_id,
    src.NAME as PROJECT_NAME,
    left(CONCAT_WS(' - ', src.PROJECT_ID, src.NAME), 40)
        + IIF(LEN(CONCAT_WS(' - ', src.PROJECT_ID, src.NAME)) > 40, '...', '') AS [Project_Title],
    src.CATEGORY1_ID as PRU,
    src.CATEGORY2_ID as Project_Category,

    src.DESCRIPTION as PROJECT_DESCRIPTION,

SQL

BMT-DWH-DEV/Project_Details.sql