BusOpp.Details

Purpose

The Common View for Business Opportunity Details, excluding dates or measures, acts as a unified and reliable source for data on business opportunities. 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 Business Opportunity slicer, ensuring a uniform style for Title descriptions.
Style: Dimension, meaning a single row per Business Opportunity No.

Why This Version?

• Focus on Essentials: It keeps the essential fields for a quick view without overloading the report with too many fields.
• Status & Timeline (Optional): Adding STATE and ESTIMATED_SIGN_DATE allows quick reference to both the status and timeline without needing to dig into a full view.
• 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

FACT_BUSINESS_OPP_OL$

Integration with Dimension Tables:

Although not explicitly joined in the provided query, this table typically integrates with dimension tables for:

• Opportunity No:
• Project Details: To provide context on which project the opportunity pertains to.
• Company Details: To ensure company-specific data is included for analysis.
• Customer Details: To enrich the data with additional customer information.
• PRU Value: PRU is used to segment financial responsibilities across different parts of a company.

Sample Exec

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

Response

    src.COMPANY,
    src.OPPORTUNITY_NO,
    src.CUSTOMER_ID,
    clv.PROJECT_ID,
    src.[CF$_PRU_VALUE] AS PRU,  -- PRU Value (custom segmentation) -- 2024-09-06

Description

    src.DESCRIPTION AS BusOpp_Desc,
    LEFT(CONCAT_WS(' - ', src.OPPORTUNITY_NO, src.DESCRIPTION), 40) 
        + IIF(LEN(CONCAT_WS(' - ', src.OPPORTUNITY_NO, src.DESCRIPTION)) > 40, '...', '') AS BusOpp_Title,  -- Shortened title

Key Attributes

    src.OPPORTUNITY_TYPE,
    src.PROBABILITY,
    clv.RISK_LEVEL,

Optional fields

    src.STATE,  -- Opportunity state for quick status
    TRY_PARSE(src.ESTIMATED_SIGN_DATE AS DATE) AS ESTIMATED_SIGN_DATE  -- Optional: key date for timeline (if needed)

SQL

BMT-DWH-DEV/BusOpp_Details.sql