Procurement Vulnerability Analysis for SAP SpotApp

This SpotApp was created by a partner, Axis Group. It has been verified by ThoughtSpot, but is supported by Axis Group, and not ThoughtSpot Support. For questions, support, and more information, contact Derek Garrison from Axis Group at derek.garrison@axisgroup.com.

SpotApps are ThoughtSpot’s out-of-the-box solution templates built for specific use cases and data sources. They are built on ThoughtSpot Modeling Language (TML) Blocks, which are pre-built pieces of code that are easy to download and implement directly from the product.

The Procurement Vulnerability Analysis for SAP SpotApp mimics the SAP data model. When you deploy it, ThoughtSpot creates several worksheets, answers, and Liveboards, based on your SAP data in your cloud data warehouse.

This is a sample Liveboard, created after you deploy the Procurement Vulnerability Analysis for SAP SpotApp:

Procurement Vulnerability SpotApp Liveboard

Use the Procurement Vulnerability Analysis for SAP SpotApp to quickly identify where disruptions could occur in your global supply chain, reducing risk and building resilience. View alternate suppliers who provide similar products to diversify your sourcing and establish resilience in your supply chain.

Prerequisites

Before you can deploy the Procurement Vulnerability Analysis for SAP SpotApp, you must complete the following prerequisites:

Run SQL commands

The following SQL commands help standardize data types and column names. Replace the database name and schema name with your specific information.

You may need to modify the code for the SQL requirements of your specific cloud data warehouse.

SQL commands:

Click on the dropdown to view the SQL commands.
create or replace view V_DIM_VENDOR_BY_MATERIAL_GROUP(
	MATKL,
	NAME1,
	MANDT,
	TELF1,
	LANDX
) as
SELECT
  "ta_1"."MATKL" ,
  "ta_2"."NAME1" ,
  "ta_1"."MANDT" ,
  "ta_2"."TELF1" ,
  "ta_3"."LANDX"
FROM “<DATABASE NAME>“.”<SCHEMA NAME>“."EKPO" "MTA_0"
  JOIN “<DATABASE NAME>“.”<SCHEMA NAME>“."MARA" "ta_1"
    ON (
      "MTA_0"."MATNR" = "ta_1"."MATNR"
      AND "MTA_0"."MANDT" = "ta_1"."MANDT"
    )
  JOIN “<DATABASE NAME>“.”<SCHEMA NAME>“."EKKO" "MTA_1"
    ON (
      "MTA_0"."MANDT" = "MTA_1"."MANDT"
      AND "MTA_0"."EBELN" = "MTA_1"."EBELN"
    )
  JOIN “<DATABASE NAME>“.”<SCHEMA NAME>“."EKKO" "MTA_2"
    ON (
      "MTA_0"."MANDT" = "MTA_2"."MANDT"
      AND "MTA_0"."EBELN" = "MTA_2"."EBELN"
    )
  JOIN “<DATABASE NAME>“.”<SCHEMA NAME>“."LFA1" "ta_2"
    ON (
      "MTA_1"."MANDT" = "ta_2"."MANDT"
      AND "MTA_1"."LIFNR" = "ta_2"."LIFNR"
    )
  JOIN “<DATABASE NAME>“.”<SCHEMA NAME>“."LFA1" "MTA_3"
    ON (
      "MTA_2"."MANDT" = "MTA_3"."MANDT"
      AND "MTA_2"."LIFNR" = "MTA_3"."LIFNR"
    )
  JOIN “<DATABASE NAME>“.”<SCHEMA NAME>“."T005T" "ta_3"
    ON (
      "MTA_3"."LAND1" = "ta_3"."LAND1"
      AND "ta_3"."SPRAS" = 'E'
    )
GROUP BY
  "ta_1"."MATKL",
  "ta_2"."NAME1",
  "ta_1"."MANDT",
  "ta_2"."TELF1",
  "ta_3"."LANDX";

create or replace view V_DIM_MAKT(
	MANDT,
	MATNR,
	SPRAS,
	MAKTX
) as
Select
 	cast (MANDT as VARCHAR(3)),
	cast (MATNR as VARCHAR(40)),
	cast (SPRAS as VARCHAR(1)),
	cast (MAKTX as VARCHAR(40))
 From “<DATABASE NAME>“.”<SCHEMA NAME>“."MAKT" Where SPRAS = 'E';

create or replace view V_DIM_MATERIAL_SOURCING_TYPE(
	MATNR,
	MANDT,
	VENDOR_SOURCE_TYPE,
	COUNTRY_SOURCE_TYPE,
	REGION_SOURCE_TYPE
) as
SELECT
  "ta_1"."MATNR" ,
  "ta_1"."MANDT" ,
  CASE
    WHEN count(DISTINCT "ta_2"."NAME1") > 1 THEN 'multi-source'
    ELSE 'single-source'
  END "VENDOR_SOURCE_TYPE",
  CASE
    WHEN count(DISTINCT "ta_2"."LAND1") > 1 THEN 'multi-country'
    ELSE 'single-country'
  END "COUNTRY_SOURCE_TYPE",
  CASE
    WHEN count(DISTINCT "ta_2"."REGIO") > 1 THEN 'multi-region'
    ELSE 'single-region'
  END "REGION_SOURCE_TYPE"
FROM “<DATABASE NAME>“.”<SCHEMA NAME>“."EKPO" "MTA_0"
  JOIN “<DATABASE NAME>“.”<SCHEMA NAME>“."MARA" "ta_1"
    ON (
      "MTA_0"."MATNR" = "ta_1"."MATNR"
      AND "MTA_0"."MANDT" = "ta_1"."MANDT"
    )
  JOIN “<DATABASE NAME>“.”<SCHEMA NAME>“."EKET" "ta_3"
    ON (
      "MTA_0"."MANDT" = "ta_3"."MANDT"
      AND "MTA_0"."EBELN" = "ta_3"."EBELN"
      AND "MTA_0"."EBELP" = "ta_3"."EBELP"
    )
  JOIN “<DATABASE NAME>“.”<SCHEMA NAME>“."EKKO" "MTA_1"
    ON (
      "MTA_0"."MANDT" = "MTA_1"."MANDT"
      AND "MTA_0"."EBELN" = "MTA_1"."EBELN"
    )
  JOIN “<DATABASE NAME>“.”<SCHEMA NAME>“."LFA1" "ta_2"
    ON (
      "MTA_1"."MANDT" = "ta_2"."MANDT"
      AND "MTA_1"."LIFNR" = "ta_2"."LIFNR"
    )
WHERE (
  (
    TO_DATE("ta_3"."SLFDT",'yyyymmdd') >= dateadd(month, -18, current_date)
    AND TO_DATE("ta_3"."SLFDT",'yyyymmdd') < current_date
  )

)
GROUP BY
  "ta_1"."MATNR",
  "ta_1"."MANDT";

create or replace view V_DIM_T005T(
	MANDT,
	SPRAS,
	LAND1,
	LANDX
) as
Select
 	cast (MANDT as VARCHAR(3)),
	cast (SPRAS as VARCHAR(1)),
	cast (LAND1 as VARCHAR(3)),
	cast (LANDX as VARCHAR(15))
 From “<DATABASE NAME>“.”<SCHEMA NAME>“."T005T" Where SPRAS = 'E';

create or replace view V_DIM_TCURR(
	BEDAT,
	MANDT,
	TCURR,
	KURST,
	FCURR,
	UKURS
) as
SELECT
CAST((99999999 - CAST(MaxDate AS integer)) AS varchar) "BEDAT",
"MANDT",
Lower("TCURR") as "TCURR",
"KURST",
Lower("FCURR") as "FCURR" ,
"UKURS"
FROM(
    SELECT
        "ta_1"."GDATU",
        "ta_1"."MANDT" "MANDT",
        "ta_1"."TCURR" "TCURR",
        "ta_1"."KURST" "KURST",
        "ta_1"."FCURR" "FCURR",
        "ta_1"."UKURS",
        min("ta_1"."GDATU") over ( partition by "ta_1"."MANDT", "ta_1"."TCURR" , "ta_1"."KURST" , "ta_1"."FCURR" ,"ta_1"."UKURS") as MaxDate
    FROM “<DATABASE NAME>“.”<SCHEMA NAME>“."TCURR" "ta_1") WHERE "GDATU"=MaxDate and "KURST" ='M' and "TCURR" ='USD';

Deploy the Procurement Vulnerability Analysis for SAP SpotApp

After you complete the prerequisites, you are ready to deploy the Procurement Vulnerability Analysis for SAP SpotApp and begin leveraging its pre-built content.

To deploy the Procurement Vulnerability Analysis for SAP SpotApp, refer to Deploying SpotApps.

Procurement Vulnerability Analysis for SAP SpotApp schema

The following table describes the schema for the Procurement Vulnerability Analysis for SAP SpotApp.

Table Column name Column type Required column

TCURR

KURST

VARCHAR

Y

TCURR

TCURR

VARCHAR

Y

TCURR

UKURS

DOUBLE

Y

TCURR

MANDT

VARCHAR

Y

TCURR

GDATU

VARCHAR

Y

TCURR

FCURR

VARCHAR

Y

T005T

MANDT

VARCHAR

Y

T005T

SPRAS

VARCHAR

Y

T005T

LAND1

VARCHAR

Y

T005T

LANDX

VARCHAR

Y

LFA1

NAME3

VARCHAR

Y

LFA1

MANDT

VARCHAR

Y

LFA1

ORT02

VARCHAR

Y

LFA1

TELF1

VARCHAR

Y

LFA1

LAND1

VARCHAR

Y

LFA1

NAME4

VARCHAR

Y

LFA1

PSTLZ

VARCHAR

Y

LFA1

STCEG

VARCHAR

Y

LFA1

ADRNR

VARCHAR

Y

LFA1

TELFX

VARCHAR

Y

LFA1

ORT01

VARCHAR

Y

LFA1

LIFNR

VARCHAR

Y

LFA1

REGIO

VARCHAR

Y

LFA1

STRAS

VARCHAR

Y

LFA1

NAME1

VARCHAR

Y

LFA1

NAME2

VARCHAR

Y

EKPO

MAHN1

DOUBLE

Y

EKPO

PEINH

DOUBLE

Y

EKPO

EBELP

VARCHAR

Y

EKPO

VOLUM

INT64

Y

EKPO

BPRME

VARCHAR

Y

EKPO

LOEKZ

VARCHAR

Y

EKPO

NTGEW

INT64

Y

EKPO

BNFPO

VARCHAR

Y

EKPO

MAHNZ

DOUBLE

Y

EKPO

BEDNR

VARCHAR

Y

EKPO

LMEIN

VARCHAR

Y

EKPO

EREKZ

VARCHAR

Y

EKPO

STATU

VARCHAR

Y

EKPO

ABSKZ

VARCHAR

Y

EKPO

PSTYP

VARCHAR

Y

EKPO

KNTTP

VARCHAR

Y

EKPO

MAHN3

DOUBLE

Y

EKPO

EBELN

VARCHAR

Y

EKPO

NETPR

DOUBLE

Y

EKPO

KTMNG

INT64

Y

EKPO

MATNR

VARCHAR

Y

EKPO

STATUS

VARCHAR

Y

EKPO

INSMK

VARCHAR

Y

EKPO

WERKS

VARCHAR

Y

EKPO

LGORT

VARCHAR

Y

EKPO

EMLIF

VARCHAR

Y

EKPO

BRTWR

DOUBLE

Y

EKPO

REPOS

VARCHAR

Y

EKPO

MANDT

VARCHAR

Y

EKPO

WEPOS

VARCHAR

Y

EKPO

GEWEI

VARCHAR

Y

EKPO

BANFN

VARCHAR

Y

EKPO

INFNR

VARCHAR

Y

EKPO

NETWR

DOUBLE

Y

EKPO

BPUMZ

DOUBLE

Y

EKPO

VOLEH

VARCHAR

Y

EKPO

BWTAR

VARCHAR

Y

EKPO

MEINS

VARCHAR

Y

EKPO

AEDAT

VARCHAR

Y

EKPO

SPINF

VARCHAR

Y

EKPO

TWRKZ

VARCHAR

Y

EKPO

ABMNG

INT64

Y

EKPO

BPUMN

DOUBLE

Y

EKPO

MAHN2

DOUBLE

Y

EKPO

ABELP

VARCHAR

Y

EKPO

ABDAT

VARCHAR

Y

EKPO

GNETWR

DOUBLE

Y

EKPO

MTART

VARCHAR

Y

EKPO

BRGEW

INT64

Y

EKPO

BSTYP

VARCHAR

Y

EKPO

TXZ01

VARCHAR

Y

EKPO

BWTTY

VARCHAR

Y

EKPO

MENGE

INT64

Y

EKPO

IDNLF

VARCHAR

Y

EKPO

ELIKZ

VARCHAR

Y

EKET

WAUHR

TIME

Y

EKET

TDDAT

VARCHAR

Y

EKET

SLFDT

VARCHAR

Y

EKET

TDUHR

DATE

Y

EKET

EBELN

VARCHAR

Y

EKET

ELDAT

VARCHAR

Y

EKET

MANDT

VARCHAR

Y

EKET

MENGE

INT64

Y

EKET

ELUHR

TIME

Y

EKET

EBELP

VARCHAR

Y

EKET

WADAT

VARCHAR

Y

EKET

DAT01

VARCHAR

Y

EKET

LPEIN

VARCHAR

Y

EKET

LDUHR

TIME

Y

EKET

GLMNG

INT64

Y

EKET

DABMG

INT64

Y

EKET

AMENG

INT64

Y

EKET

WAMNG

INT64

Y

EKET

WEMNG

INT64

Y

EKET

BEDAT

VARCHAR

Y

EKET

MNG02

DOUBLE

Y

EKET

ALTDT

VARCHAR

Y

EKET

EINDT

VARCHAR

Y

EKET

LDDAT

VARCHAR

Y

EKET

ETENR

VARCHAR

Y

EKKO

FRGKE

VARCHAR

Y

EKKO

WKURS

DOUBLE

Y

EKKO

BEDAT

VARCHAR

Y

EKKO

WAERS

VARCHAR

Y

EKKO

BSART

VARCHAR

Y

EKKO

FRGGR

VARCHAR

Y

EKKO

ERNAM

VARCHAR

Y

EKKO

LOEKZ

VARCHAR

Y

EKKO

FRGZU

VARCHAR

Y

EKKO

FRGSX

VARCHAR

Y

EKKO

KNUMV

VARCHAR

Y

EKKO

VERKF

VARCHAR

Y

EKKO

ABSGR

VARCHAR

Y

EKKO

KUNNR

VARCHAR

Y

EKKO

EKGRP

VARCHAR

Y

EKKO

LIFNR

VARCHAR

Y

EKKO

BUKRS

VARCHAR

Y

EKKO

ZTERM

VARCHAR

Y

EKKO

EBELN

VARCHAR

Y

EKKO

AEDAT

VARCHAR

Y

EKKO

BSAKZ

VARCHAR

Y

EKKO

MANDT

VARCHAR

Y

EKKO

FRGRL

VARCHAR

Y

EKKO

STATU

VARCHAR

Y

EKKO

EKORG

VARCHAR

Y

EBAN

MATNR

VARCHAR

Y

EBAN

WERKS

VARCHAR

Y

EBAN

BADAT

VARCHAR

Y

EBAN

DISPO

VARCHAR

Y

EBAN

BNFPO

VARCHAR

Y

EBAN

EKORG

VARCHAR

Y

EBAN

EKGRP

VARCHAR

Y

EBAN

MANDT

VARCHAR

Y

EBAN

AFNAM

VARCHAR

Y

EBAN

WAERS

VARCHAR

Y

EBAN

PREIS

DOUBLE

Y

EBAN

BANFN

VARCHAR

Y

EBAN

BERID

VARCHAR

Y

EBAN

RESWK

VARCHAR

Y

EBAN

MENGE

DOUBLE

Y

EBAN

BSMNG

DOUBLE

Y

MAKT

MATNR

VARCHAR

Y

MAKT

MAKTX

VARCHAR

Y

MAKT

MANDT

VARCHAR

Y

MAKT

SPRAS

VARCHAR

Y

MARA

NTGEW

DOUBLE

Y

MARA

MANDT

VARCHAR

Y

MARA

BSTME

VARCHAR

Y

MARA

MEINS

VARCHAR

Y

MARA

BRGEW

DOUBLE

Y

MARA

MTART

VARCHAR

Y

MARA

MATKL

VARCHAR

Y

MARA

MATNR

VARCHAR

Y

MARA

MBRSH

VARCHAR

Y

MARA

ERSDA

VARCHAR

Y

MARA

GEWEI

VARCHAR

Y

T024

EKNAM

VARCHAR

Y

T024

EKGRP

VARCHAR

Y

T024

MANDT

VARCHAR

Y