BusOpp.Value

Purpose

The Business Opportunity (Line) Value fact table is designed to display quantitative data related to individual line items of business opportunities. This table integrates with dimension tables, such as BusOpp.Details and Client/Cutomer_Details, to provide insight into potential revenue, sales pipeline, and opportunity management. The primary purpose is to support sales and business development analysis and reporting across the organisation.

Use Case

Owner: Martyn Cole
Typical Use Case: Project potential revenue based on the value and expected close dates of opportunities.
Style: Fact, meaning it consists of the measurements, metrics or facts of a business process

Source

FACT_BUSINESS_OPP_LINE_OL

Parameters

Company
Business Opportunity
Customer

Sample Exec

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

Response

src.COMPANY,
src.[CUSTOMER_ID],
src.OPPORTUNITY_NO,
clv.PROJECT_ID,

SEQ

REVISION_NO_KEY,
LINE_NO_KEY,

Entity

busopp_item_id,

Dates

cast(src.[DATE_ENTERED] as date) as [DATE_ENTERED],
cast(src.[START_DATE] as date) as [START_DATE],
cast(src.[ESTIMATED_SIGN_DATE] as date) as [ESTIMATED_SIGN_DATE],
cast(src.[CLOSURE_DATE] as date) as [CLOSURE_DATE],
cast(line.[WANTED_DELIVERY_DATE] as date) as [WANTED_DELIVERY_DATE],

— — Measures

try_convert(numeric(15,3),cast(line.[BASE_SALE_UNIT_PRICE] as float)) as [BASE_SALE_UNIT_PRICE],
try_convert(numeric(15,3),cast(line.[SALE_UNIT_PRICE] as float)) as [SALE_UNIT_PRICE],

try_convert(numeric(15,3),cast(src.[BASE_EST_OPPORTUNITY_VALUE] as float)) as [BASE_EST_OPPORTUNITY_VALUE],
try_convert(numeric(15,3),cast(src.[EST_OPPORTUNITY_VALUE] as float)) as  [EST_OPPORTUNITY_VALUE],
try_convert(numeric(15,3),cast(src.[TOTAL_BASE_OPP_VALUE] as float)) as [TOTAL_BASE_OPP_VALUE],
try_convert(numeric(15,3),cast(src.[TOTAL_CURR_OPP_VALUE] as float)) as [TOTAL_CURR_OPP_VALUE],
try_convert(numeric(15,3),cast(src.[NOT_QUOTED_OPP_BASE_VALUE] as float)) as [NOT_QUOTED_OPP_BASE_VALUE],
try_convert(numeric(15,3),cast(src.[NOT_QUOTED_OPP_CURR_VALUE] as float)) as [NOT_QUOTED_OPP_CURR_VALUE],
try_convert(numeric(15,3),cast(line.[TOTAL_BASE_VALUE] as float)) as  [TOTAL_BASE_VALUE],
try_convert(numeric(15,3),cast(line.[TOTAL_CURR_VALUE] as float)) as  [TOTAL_CURR_VALUE],
try_convert(numeric(15,3),cast(src.[WON_OPP_BASE_VALUE] as float)) as [WON_OPP_BASE_VALUE],
try_convert(numeric(15,3),cast(src.[WON_OPP_CURR_VALUE] as float)) as [WON_OPP_CURR_VALUE

[PROBABILITY],
[RISK_LEVEL],

Codes

BusOpp

item_label,

Market

src.[DISTRICT_CODE],
src.[REGION_CODE],
src.[MARKET_CODE],
src.[MAIN_REPRESENTATIVE_ID],
src.[SOURCE_ID],

Other Codes

		src.[BUSINESS_TYPE],
		src.[OPPORTUNITY_TYPE],
		src.[WON_LOST_REASON_ID],
		src.[LOST_TO],
		src.[CANCELLATION_REASON],
		src.[STAGE_ID],
		src.[STATE],
		src.[CLOSED_STATUS],
		line.[PRICE_SOURCE],
		src.[CURRENCY_CODE]

SQL

BMT-DWH-DEV/BusOpp_Value.sql