Using Data lake and SQL to create custom reporting models

[Roney_Mathew]

Overview: Through a series of blogs, would like to share scripts that utilize data lakes built for SAP tables, to create reporting models that represent certain sections of SAP screens/transactions or areas of analysis. Hopefully, these scripts serve as an accelerator to cater multiple use cases.For this first script we'll look at building User Status using JCDS and JEST.

Background:  Most structured reporting tools (eg:BW) or ETL processes don’t bring in all fields available in source systems, these are deployed using a predefined datamodel (dimensions/measures) that collects fields from different tables and  limit what’s initially available for reporting, restricting the ability of Analysts to explore additional fields.

Eg: Financial reporting models built using ACDOCA or BSEG or FAGLFLEXA tables- Irrespective of the approach(CDS views or BW models), these don’t bring all fields from the source as they mostly focus on meeting initial requirements from primary stakeholders.

 Additional fields maybe available in SAP transaction systems and to make them available for reporting, multiple cycles of enhancements are implemented, reflecting a dependency on different support teams and time involved to meet these requirements.

Solution With a data lake that replicates tables from SAP, Analysts working with functional resources can build models that meet their specific needs. If replications are managed through SAP SLT, then it enables near realtime (possible delay of a few seconds) reporting. Review must be done with functional consultants to ensure that tables being replicated dont have confidential content.

As part of this blog series, we shall see some models that reflect SAP transactions or commonly used reporting metrics.

Factors that are not addressed in this blog but must be considered:

1. Organization of reporting models and data lake tables, if not using similar reference as SAP Application components. This becomes Important for managing confidentiality and ensuring personal information of customers, employees and vendors is only available to those that need it as part of their business roles.

1. Security models needed for
   
   
   1. Functional areas of reporting (multiple tables grouped in an area of reporting)
   
   
   1. Row based access
   
   
   1. Any additional configuration needed to secure fields in tables
   
   
   

Here's the first script:

1. Script for Plant maintenance object status

Need: Near real time availability of object status’ for Plant maintenance, eg: an emergency order created for addressing critical equipment failure, the status and progress of investigation needs to be communicated through the manufacturing channels for them to manage bottlnecks in production.

Solution: Below layout provides a simplified overview of how different tables are joined together with their respective fields.

 

Tables used:

JEST-Individual Object Status

JCDS-Change Documents for System/User Statuses (Table JEST)

JSTO- Status object information

TJ02-System status

TJ02T - System status texts

TJ04- Status control for object type

TJ30- User Status

TJ30T- Texts for User Status

Object status tables relationship overview

 

 

Script below provides active status’ for all Plant maintenance objects . To view all instances of status changes remove the JEST.INACT is NULL clause/restriction. Each table and the filter condition starts with a comment(begins with --) to show what it represents. May have to tweak formatting based on tool being used, especially the comments section.

 

SELECT



JEST.OBJNR AS OBJECT_NUMBER,



JSTO.OBTYP AS OBJECT_CATEGORY,



SUBSTR(JEST.OBJNR, 3) AS OBJECT,



JEST.STAT AS OBJECT_STATUS,



(CASE WHEN LEFT(JEST.STAT, 1) = ‘I’ THEN ‘SYSTEM’ ELSE ‘USER’ END) ASSTATUS_TYPE,



(CASE WHEN LEFT(JEST.STAT, 1) = ‘I’ THEN TJ02T.TXT04



ELSE TJ30T.TXT04 END) AS STATUS_SHORT_TEXT,



(CASE WHEN LEFT(JEST.STAT, 1) = ‘I’ THEN TJ02T.TXT30



ELSE TJ30T.TXT30 END) AS STATUS_LONG_TEXT,



JSTO.STSMA AS STATUS_PROFILE,



JCDS.USNAM AS STATUS_CHANGED_BY,



JCDS.UDATE AS STATUS_CHANGED_DATE,



JCDS.UTIME AS STATUS_CHANGED_TIME,



JCDS.CHIND AS STATUS_CHANGED_TYPE,



TJ04.INIST AS SYSTEM_STATUS_INITIAL_STATUS_FLAG,



TJ04.STATP AS SYSTEM_STATUS_DISPLAY_PRIORITY,



TJ04.LINEP AS SYSTEM_STATUS_LINE_POSITION,



TJ02.NODIS AS SYSTEM_STATUS_NO_DISPLAY_INDICATOR,



TJ02.SETONLY AS SYSTEM_STATUS_SET_ONLY_INDICATOR,



TJ30.STONR AS USER_STATUS_WITH_NUMBER,



TJ30.INIST AS USER_STATUS_INITIAL_STATUS_FLAG_INDICATOR,



TJ30.STATP AS USER_STATUS_DISPLAY_PRIORITY,



TJ30.LINEP AS USER_STATUS_LINE_POSITION,



CASE WHEN TJ30.LINEP = ’01’ THEN TJ30T.TXT04 END ASPOSITION1_USER_STATUS



FROM JEST --Individual object status



INNER JOIN JCDS -- Change Documents for System/User Statuses (Table JEST)



ON JEST.OBJNR = JCDS.OBJNR



AND JEST.STAT = JCDS.STAT



AND JEST.CHGNR = JCDS.CHGNR



LEFT JOIN JSTO -- Status profile information for objects



ON JEST.OBJNR = JSTO.OBJNR



LEFT JOIN TJ02T --System status texts



ON JEST.STAT = TJ02T.ISTAT



AND TJ02T.SPRAS = ‘E’



LEFT JOIN TJ04 -- System status control config table 2



ON JEST.STAT = TJ04.ISTAT



and TJ04.OBTYP = JSTO.OBTYP



LEFT JOIN TJ30T -- User status texts



ON JSTO.STSMA = TJ30T.STSMA



AND JEST.STAT = TJ30T.ESTAT



AND TJ30T.SPRAS = ‘E’



LEFT JOIN TJ02 ”System status config table 1



ON JEST.STAT = TJ02.ISTAT



LEFT JOIN TJ30 -- User status config table 1



ON JSTO.STSMA = TJ30.STSMA



AND JEST.STAT = TJ30.ESTAT



WHERE JEST.INACT is NULL -- remove this to see when a status was set inactive or to get timelines for all status

 

Conclusion : Using the above code we can active status' and their respective times for all operational objects that have been configured for status tracking. Similar approach can be used to get status' for CRM using table CRM_JEST and CRM_JCDS. Remove the inactive filter to get status' that are currently not active (depending on the values are mapped in data lake i.e default value of blanks as NULLs, NULL may need to be replaced with '')

 

Possible variations based on need:

1. To plot timeline of how the operational object moved between status' use JCDS

1. Restrict to certain Status profile(s) in table JSTO when requirement is to focus on certain types of objects or group

1. Restrict using change date and time if the need is to focus of recent changes within the hour or day(s)

 

Next blog will look at details of combining details of orders and related operational tasks