Supplier Spend Analysis

This solution sample provides an example on how to accelerate insights into supplier spend position using SAP ERP or SAP BDC sourced data.

With it you can:

Optimize total spend by material type and track country concentration.
Understand hidden purchasing patterns to maximize procurement value.
Use AI to uncover new insights.

Reference architecture

A high level reference architecture provides an overview of the source specific data products required to feed this use case.

Figure 1 . Supplier Spend Analysis solution sample reference architecture for Cortex Framework.

Required data products

If building on top of SAP ERP sourced data the following Cortex Framework data products are required:

Vendors
Purchasing Documents
Purchasing Organizations
Materials
Material Groups
Material Types

If building on top of SAP BDC sourced data the following BDC data products are required:

Product
Purchase Order
Company Code
Vendor

Code snippets

The following code snippet provides an example on how to build a BigQuery data model for supplier spend analysis on top of Cortex Framework sourced SAP ERP data products or after integration of SAP BDC data products.

  WITH 
  
 purchasing_items 
  
 AS 
  
 ( 
  
 SELECT 
  
 client_mandt 
 , 
  
 purchasing_document_number_ebeln 
 , 
  
 item_number_of_purchasing_document_ebelp 
 , 
  
 material_number_matnr 
 , 
  
 plant_werks 
 , 
  
 po_quantity_menge 
 , 
  
 net_order_value_in_po_currency_netwr 
 , 
  
 gross_order_value_in_po_currency_brtwr 
 , 
  
 delivery_completed_flag_elikz 
  
 FROM 
  
< YOUR_PROJECT_ID 
> . 
< YOUR_CORTEX_DATA_PRODUCTS_DATASET 
> . 
 purchasing_document_items 
 ), 
 purchasing_headers 
  
 AS 
  
 ( 
  
 SELECT 
  
 client_mandt 
 , 
  
 purchasing_document_number_ebeln 
 , 
  
 vendor_account_number_lifnr 
 , 
  
 purchasing_organization_ekorg 
 , 
  
 purchasing_document_date_bedat 
  
 as 
  
 order_date_bedat 
 , 
  
 currency_key_waers 
  
 FROM 
  
< YOUR_PROJECT_ID 
> . 
< YOUR_CORTEX_DATA_PRODUCTS_DATASET 
> . 
 purchasing_document_headers 
 ), 
 vendor_data 
  
 AS 
  
 ( 
  
 SELECT 
  
 client_mandt 
 , 
  
 account_number_of_vendor_or_creditor_lifnr 
  
 as 
  
 vendor_account_number_lifnr 
 , 
  
 name1_name1 
  
 as 
  
 vendor_name_name1 
 , 
  
 country_key_land1 
 , 
  
 central_deletion_flag_for_master_record_loevm 
  
 FROM 
  
< YOUR_PROJECT_ID 
> . 
< YOUR_CORTEX_DATA_PRODUCTS_DATASET 
> . 
 vendors_md 
 ), 
 material_data 
  
 AS 
  
 ( 
  
 SELECT 
  
 client_mandt 
 , 
  
 material_number_matnr 
 , 
  
 material_text_maktx 
 , 
  
 material_type_mtart 
 , 
  
 material_group_matkl 
  
 FROM 
  
< YOUR_PROJECT_ID 
> . 
< YOUR_CORTEX_DATA_PRODUCTS_DATASET 
> . 
 materials_md 
 ), 
 purchasing_orgs 
  
 AS 
  
 ( 
  
 SELECT 
  
 client_mandt 
 , 
  
 purchasing_organization_ekorg 
 , 
  
 purchasing_organization_text_ekotx 
  
 FROM 
  
< YOUR_PROJECT_ID 
> . 
< YOUR_CORTEX_DATA_PRODUCTS_DATASET 
> . 
 purchasing_organizations_md 
 ) 
 SELECT 
  
 i 
 . 
 client_mandt 
 , 
  
 i 
 . 
 purchasing_document_number_ebeln 
 , 
  
 i 
 . 
 item_number_of_purchasing_document_ebelp 
 , 
  
 h 
 . 
 vendor_account_number_lifnr 
 , 
  
 v 
 . 
 vendor_name_name1 
 , 
  
 v 
 . 
 country_key_land1 
 , 
  
 i 
 . 
 material_number_matnr 
 , 
  
 m 
 . 
 material_text_maktx 
 , 
  
 m 
 . 
 material_type_mtart 
 , 
  
 m 
 . 
 material_group_matkl 
 , 
  
 h 
 . 
 purchasing_organization_ekorg 
 , 
  
 o 
 . 
 purchasing_organization_text_ekotx 
 , 
  
  
 -- Baseline financial & temporal fields 
  
 h 
 . 
 order_date_bedat 
 , 
  
 i 
 . 
 po_quantity_menge 
 , 
  
 i 
 . 
 net_order_value_in_po_currency_netwr 
 , 
  
 i 
 . 
 gross_order_value_in_po_currency_brtwr 
 , 
  
 i 
 . 
 net_order_value_in_po_currency_netwr 
  
 as 
  
 spend_usd 
 , 
  
  
 -- Active vendor indicator (True if not deleted) 
  
 CASE 
  
  
 WHEN 
  
 v 
 . 
 central_deletion_flag_for_master_record_loevm 
  
 = 
  
 'X' 
  
 THEN 
  
 false 
  
 ELSE 
  
 true 
  
 END 
  
 as 
  
 active_vendor_indicator 
 , 
  
  
 -- Purchase Price Variance (PPV) - Net versus Gross baseline/target difference 
  
 ( 
 i 
 . 
 gross_order_value_in_po_currency_brtwr 
  
 - 
  
 i 
 . 
 net_order_value_in_po_currency_netwr 
 ) 
  
 as 
  
 purchase_price_variance_ppv 
 , 
  
  
 -- Delivery and overdues tracking 
  
 i 
 . 
 delivery_completed_flag_elikz 
 , 
  
 CASE 
  
  
 WHEN 
  
 i 
 . 
 delivery_completed_flag_elikz 
  
 != 
  
 'X' 
  
 THEN 
  
 i 
 . 
 net_order_value_in_po_currency_netwr 
  
 ELSE 
  
 0 
  
 END 
  
 as 
  
 open_po_net_amount 
 , 
  
  
 CASE 
  
  
 WHEN 
  
 i 
 . 
 delivery_completed_flag_elikz 
  
 != 
  
 'X' 
  
 AND 
  
 h 
 . 
 order_date_bedat 
 < 
 DATE_SUB 
 ( 
 CURRENT_DATE 
 (), 
  
 INTERVAL 
  
 30 
  
 DAY 
 ) 
  
 THEN 
  
 true 
  
 ELSE 
  
 false 
  
 END 
  
 as 
  
 is_overdue 
 , 
  
  
 -- Efficiency & Performance metrics 
  
 SAFE_DIVIDE 
 ( 
 i 
 . 
 net_order_value_in_po_currency_netwr 
 , 
  
 i 
 . 
 po_quantity_menge 
 ) 
  
 as 
  
 average_spend_per_unit 
 , 
  
 ( 
 i 
 . 
 gross_order_value_in_po_currency_brtwr 
  
 - 
  
 i 
 . 
 net_order_value_in_po_currency_netwr 
 ) 
  
 as 
  
 gross_net_variance 
 , 
  
 1 
  
 as 
  
 line_item_count 
  
 FROM 
  
 purchasing_items 
  
 i 
 LEFT 
  
 JOIN 
  
 purchasing_headers 
  
 h 
  
 ON 
  
 i 
 . 
 client_mandt 
  
 = 
  
 h 
 . 
 client_mandt 
  
 AND 
  
 i 
 . 
 purchasing_document_number_ebeln 
  
 = 
  
 h 
 . 
 purchasing_document_number_ebeln 
 LEFT 
  
 JOIN 
  
 vendor_data 
  
 v 
  
 ON 
  
 h 
 . 
 client_mandt 
  
 = 
  
 v 
 . 
 client_mandt 
  
 AND 
  
 h 
 . 
 vendor_account_number_lifnr 
  
 = 
  
 v 
 . 
 vendor_account_number_lifnr 
 LEFT 
  
 JOIN 
  
 material_data 
  
 m 
  
 ON 
  
 i 
 . 
 client_mandt 
  
 = 
  
 m 
 . 
 client_mandt 
  
 AND 
  
 i 
 . 
 material_number_matnr 
  
 = 
  
 m 
 . 
 material_number_matnr 
 LEFT 
  
 JOIN 
  
 purchasing_orgs 
  
 o 
  
 ON 
  
 h 
 . 
 client_mandt 
  
 = 
  
 o 
 . 
 client_mandt 
  
 AND 
  
 h 
 . 
 purchasing_organization_ekorg 
  
 = 
  
 o 
 . 
 purchasing_organization_ekorg

Enable AI agents

After creating your Supplier Spend Analysis data model in BigQuery, you can build a custom data agent . This lets you query supplier spend metrics directly using natural language, bypassing the need for complex SQL.