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:
Here's the first script:
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:
Next blog will look at details of combining details of orders and related operational tasks
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
User | Count |
---|---|
13 | |
10 | |
7 | |
7 | |
6 | |
5 | |
5 | |
5 | |
4 | |
4 |