Skip to Content
Technical Articles
Author's profile photo Olaf Fischer

SAP Data Warehouse Cloud: Data Integration Monitoring – Sample Content for Reporting

Introduction

When using the data integration functionality a regular check with respect to executions status and errors is a common task.

Based on the delivered task monitoring views ( see for details: (https://help.sap.com/viewer/9f804b8efa8043539289f42f372c4862/cloud/en-US/4ab45090c5684ebf8765757a1dfc4e5d.html ) this blog describes an SQL view combining these into a good basis to build reports.

Starting with some sample scenarios providing ideas and inspiration of what can be achieved using the task log views, it also describes a concrete example.

Sample Scenarios

Number of Failing Runs

The view provides a list of schedules, that had at least one failing run in the past days. In the example below we see, that there are six schedules with at least one failure on the 28th or 29th of September (the first one shows up as it has an error not part of the screenshot).

For these schedules, the number of completed and failed runs is counted per day. In the example below you see the SOURCE LEDGER remote table replication failed 19 times and successfully completed 5 times on the 29th . This sums up to 24 – which is as expected given that the cron string 0 */1 * * stands for an hourly run.

Hint: to easily understand the cron string – use e.g. https://crontab.guru . For the example https://crontab.guru/#0_*/1_*_*_* .

The column with the cron strings indicate the expected frequeny of runs. The columns completed/failed indicate the count of execution status.

For a step-by-step instruction to create a story showing the information below, please scroll down to chapter “Create SQL View for Reporting”.

Failed%20Task%20Runs

These are some examples of reports you can build on the view:

Number of Failing Schedules

To have better overview of failing schedules, the following proposal just counts how many schedules failed at least once for a day.

For the 28th of Sept you see one schedule with a failure, hence the summary below shows only a count of 1, for the 29th you see six rows – and the summary shows a count of six.

Failed%20Schedules%20of%20the%20Past%20Week

Currently Running Task

Next to information on finished schedules, an overview about the currently running tasks – and a comparison to the past runs could be helpful.

In the given example the green line looks good: the duration is still below the average and median of the past executions.

If you check the blue line you see something suspicious – it runs unexpected long.

For the red ones again we see that they seem to be still running according to the task framework, still the absence of an active locks indicates that this is stopped/finished already.

 

Create the SQL View for Reporting

Now let’s start with the view creation that can be consumed in SAP Analytics Cloud story.

Navigate to your monitor space and create an SQL view with the following content. As a proposed naming convention, let the name end with V_R (it is a view on the reporting layer).

Settings:

Choose the following dimensions as measures:

The mappings with the SQL column names are:

  • DURATION -> Duration
  • ROW_COUNT -> Count
  • COUNT_RUNNING_CAL -> Count Running
  • COUNT_FAILED_CAL -> Count Failed
  • COUNT_COMPLETED -> Count Completed

And here is the SQL – you might want to check comments for more details … Don’t forget to check upfront that the space is selected as monitor space.

/* --------------------------------------------------------------------------------------------

  Monitoring of DWC Data Integration Tasks (dataFlow, persistence and replication activities)
  
  Link for documentation:
  - Main page for Data Integration Monitoring
    https://help.sap.com/viewer/9f804b8efa8043539289f42f372c4862/cloud/en-US/4cbf7c7fc64645bfa364332827557267.html
  - Technical description of TASK_LOG view
	https://help.sap.com/viewer/9f804b8efa8043539289f42f372c4862/cloud/en-US/4ab45090c5684ebf8765757a1dfc4e5d.html
  
  This view exposes:
   - task properties like duration and execution status (e.g. failed, completed, ...).
   - various measures for counting tasks by e.g. failed
   - the cron description of the schedule defined
   - locking status

   Todo before usage:
   - Check that the space is enabled for monitor space
   - Change the constant for the url_host to your DWC instance
   ------------------------------------------------------------------------------------------ */

SELECT 

	base."TASK_LOG_ID",

	base."SPACE_ID",
	base."APPLICATION_ID",
	base."OBJECT_ID",
	base."ACTIVITY",

	base."STATUS",

	base."START_TIME",
	to_date(base."START_DATE") "START_DATE",
	base."END_TIME",

	-- manual or scheduled execution on behalf of
	base."TRIGGER_TYPE",
	base."TRIGGERED_BY",

	-- enable jump from SAC report to DWC detailed view
	'<my-dwc-system>.hcs.cloud.sap' URL_HOST,
        concat(concat('/dwaas-ui/index.html#/dataintegration&/di/'
             , base."SPACE_ID"), base."MONITOR_TYPE") URL_PATH,

	-- at least one run on the same day has a failure
	failed."HAS_FAILURE" OBJECT_ID_WITH_FAILURE,

	-- the re-occurence of the task in cron notation
	"TASK_SCHEDULES_V_EXT".CRON,

	to_int(base."DURATION") "DURATION",
	to_int(base."ROW_COUNT") "ROW_COUNT",
	to_int(base."COUNT_RUNNING_CAL") "COUNT_RUNNING_CAL",
	to_int(base."COUNT_FAILED_CAL") "COUNT_FAILED_CAL",
	to_int(base."COUNT_COMPLETED_CAL") "COUNT_COMPLETED_CAL",
	
	CASE WHEN "TASK_LOCKS_V_EXT"."TASK_LOG_ID" IS NULL 
		then to_int(0) 
		ELSE to_int(1) 
	END "ACTIVE_LOCK"

FROM (
		SELECT "TASK_LOG_ID",
			"SPACE_ID",
			"APPLICATION_ID",
			CASE WHEN "APPLICATION_ID" = 'VIEWS' then '/viewMonitor/' 
				 WHEN "APPLICATION_ID" = 'REMOTE_TABLES' then '/remoteTableMonitor/' 
				 WHEN "APPLICATION_ID" = 'DATA_FLOWS' then '/dataFlowMonitor/' 
				 ELSE "APPLICATION_ID" END "MONITOR_TYPE",
			"OBJECT_ID",
			"ACTIVITY",
			"STATUS",
			"START_TIME",
			"START_DATE",
			"END_TIME",
			"TRIGGER_TYPE",
			"TRIGGERED_BY",
			"DURATION",
			to_int(1) "ROW_COUNT",
			CASE WHEN "STATUS" = 'RUNNING' then to_int(1) ELSE to_int(0) END "COUNT_RUNNING_CAL",
			CASE WHEN "STATUS" = 'FAILED' then to_int(1) ELSE to_int(0) END "COUNT_FAILED_CAL",
			CASE WHEN "STATUS" = 'COMPLETED' then to_int(1) ELSE to_int(0) END "COUNT_COMPLETED_CAL"
		FROM "TASK_LOGS_V_EXT"
	) base
	-- add locking information
	LEFT OUTER JOIN "TASK_LOCKS_V_EXT" 
		ON base."TASK_LOG_ID" = "TASK_LOCKS_V_EXT"."TASK_LOG_ID"
	
	-- add failed run during same day property
	LEFT OUTER JOIN (
		SELECT DISTINCT 'X' HAS_FAILURE,
			"START_DATE",
			"SPACE_ID",
			"APPLICATION_ID",
			"OBJECT_ID",
			"ACTIVITY"
		FROM "TASK_LOGS_V_EXT"
		WHERE "STATUS" = 'FAILED'
	) failed ON 
	        base."START_DATE" = failed."START_DATE" 
	    AND base."APPLICATION_ID" = failed."APPLICATION_ID" 
	    AND base."OBJECT_ID" = failed."OBJECT_ID" 
	    AND base."ACTIVITY" = failed."ACTIVITY"

	-- add cron information    
	INNER JOIN "TASK_SCHEDULES_V_EXT" ON 
	        base."SPACE_ID" = "TASK_SCHEDULES_V_EXT"."SPACE_ID" 
	    AND base."APPLICATION_ID" = "TASK_SCHEDULES_V_EXT"."APPLICATION_ID" 
	    AND base."OBJECT_ID" = "TASK_SCHEDULES_V_EXT"."OBJECT_ID" 
	    AND base."ACTIVITY" = "TASK_SCHEDULES_V_EXT"."ACTIVITY"

Create an SAP Analytics Cloud Story for the Failing Runs

  1. Create a blank story in your SAC system
  2. Insert your DWC analytical dataset as data model
  3. Insert a table and configure the point-of-view as outlined below

The following measures are needed:

  • Failed
  • Completed

Create Measure ‘Failed’:

Use measure COUNT_FAILED_CAL from the SQL Views and set the description to Failed. You can also directly use the Count Failed measure directly – still a shorter name gives a more compact view.

Create Measure ‘Completed’:

Important: Select “Enable Constant Selection” to have the correct values being displayed

Configure table: Set the drill state and filters according to the screenshot below:

Hyperlink to Open DWC Monitoring Page

If you like to enable a jump from your story to the integration monitor, you can add a hyperlink:

How to do: chose the Add -> Hyperlink option and specify the URL like shown below:

 

Conclusion

The new task related views provide a great addition to the existing monitoring around SAP Data Warehouse Cloud Data Integration.

Take this sample and tailor and enrich it to your daily needs.

I’d be happy to hear your thoughts, ideas and comments on this monitoring topic. Let me know in the comments!

Assigned tags

      Be the first to leave a comment
      You must be Logged on to comment or reply to a post.