I recently created a post detailing how to trigger a stored procedure using a Dataflow.
In this post I will go through the second iteration of the solution that only requires the use of single control table by granting the main space privalges to write to the Open SQL Schema Space.
The complete scripts to generate all the objects in the Open SQL Schema Space and the JSON/CSN to create the dataflow can be found at the end.
Steps
1 - Create Stored Procedure Control Table - Open Schema Space
Create an Open SQL Schema Space and launch the Database Explorer to create the table Control Table in the Space.
2 - Grant Main Space Additional Privileges - Open Schema Space
Allow the Main space Full access to the control table.
4 - Create Table for Output - Open Schema Space
Create a target table in the Open Schema Space that will be populated by the execution of the stored procedure.
5 - Create Stored Procedure - Open Schema Space
Create a stored procedure that checks if the control table has a new run entry and populates the target table.
6 - Schedule Stored Procedure - Open Schema Space
Schedule the stored procedure to run every minute.
7 - Create Dummy Record Table - Open Schema Space
Table that contains one record, used for record generation in dataflow.
8 - Create Data Flow - Main Space
Create a Data Flow that will populate CONTROL_TABLE with the next RUN_NUMBER causing the stored procedure to be executed.
You can use CSN/JSON file below but will need to ensure Open SQL Schema space objects have already been created, find and replace the values below and save as .json before importing.
<SPACE_NAME> - Space Name
<OSS_NAME> - Open SQL Schema Space Name
<CONTROL_TABLE_NAME> - Name of Stored Procedure Control Table
<STORED_PROC_NAME> - Name of Stored Procedure
<DUMMY_RECORD_TABLE_NAME> - Table that holds dummy record for dataflow record generation
<DATAFLOW_NAME> - Name of Dataflow
9 - Run the Data Flow - Main Space
You're now ready to run the data flow but be aware that the job is not complete when the the data flow execution has finished, you should check the Control Table to confirm when the stored procedure execution has completed.
I hope you find this guide useful and happy to hear any feedback or alternative workarounds that have been used.
Full Open SQL Schema Object Creation Script
Find and Replace with your values and save as .json
<SPACE_NAME> - Space Name
<OSS_NAME> - Open SQL Schema Space Name
<CONTROL_TABLE_NAME> - Name of Stored Procedure Control Table
<STORED_PROC_NAME> - Name of Stored Procedure
<DUMMY_RECORD_TABLE_NAME> - Table that holds dummy record for dataflow record generation
<DATAFLOW_NAME> - Name of Dataflow
In this post I will go through the second iteration of the solution that only requires the use of single control table by granting the main space privalges to write to the Open SQL Schema Space.
The complete scripts to generate all the objects in the Open SQL Schema Space and the JSON/CSN to create the dataflow can be found at the end.
Steps
1 - Create Stored Procedure Control Table - Open Schema Space
Create an Open SQL Schema Space and launch the Database Explorer to create the table Control Table in the Space.
/*
Create Control Table for Stored Procedure Execution
Find and replace the following with your values
<SPACE_NAME> - Space Name
<OSS_NAME> - Open SQL Schema Space Name
<CONTROL_TABLE_NAME> - Name of Stored Procedure Control Table
*/
CREATE COLUMN TABLE "<SPACE_NAME>#<OSS_NAME>"."<CONTROL_TABLE_NAME>"(
"SP_NAME" NVARCHAR(100) NOT NULL,
"DATE" DATE NOT NULL,
"RUN_NUMBER" INTEGER NOT NULL,
"SP_START_DATE_TIME" LONGDATE,
"SP_END_DATE_TIME" LONGDATE,
"RUN_STATUS" INTEGER NOT NULL,
PRIMARY KEY ("SP_NAME","DATE","RUN_NUMBER")
);2 - Grant Main Space Additional Privileges - Open Schema Space
Allow the Main space Full access to the control table.
/*
Provide main space all privileges to Stored Procedure Control Table
Find and replace the following with your values
<SPACE_NAME> - Space Name
<OSS_NAME> - Open SQL Schema Space Name
<CONTROL_TABLE_NAME> - Name of Stored Procedure Control Table
*/
CALL "DWC_GLOBAL"."GRANT_PRIVILEGE_TO_SPACE" (
OPERATION => 'GRANT',
PRIVILEGE => 'SELECT',
SCHEMA_NAME => '<SPACE_NAME>#<OSS_NAME>',
OBJECT_NAME => '<CONTROL_TABLE_NAME>',
SPACE_ID => '<SPACE_NAME>');
CALL "DWC_GLOBAL"."GRANT_PRIVILEGE_TO_SPACE" (
OPERATION => 'GRANT',
PRIVILEGE => 'UPDATE',
SCHEMA_NAME => '<SPACE_NAME>#<OSS_NAME>',
OBJECT_NAME => '<CONTROL_TABLE_NAME>',
SPACE_ID => '<SPACE_NAME>');
CALL "DWC_GLOBAL"."GRANT_PRIVILEGE_TO_SPACE" (
OPERATION => 'GRANT',
PRIVILEGE => 'DELETE',
SCHEMA_NAME => '<SPACE_NAME>#<OSS_NAME>',
OBJECT_NAME => '<CONTROL_TABLE_NAME>',
SPACE_ID => '<SPACE_NAME>');
CALL "DWC_GLOBAL"."GRANT_PRIVILEGE_TO_SPACE" (
OPERATION => 'GRANT',
PRIVILEGE => 'INSERT',
SCHEMA_NAME => '<SPACE_NAME>#<OSS_NAME>',
OBJECT_NAME => '<CONTROL_TABLE_NAME>',
SPACE_ID => '<SPACE_NAME>');4 - Create Table for Output - Open Schema Space
Create a target table in the Open Schema Space that will be populated by the execution of the stored procedure.
/*
Create a table for the output
Find and replace the following with your values
<SPACE_NAME> - Space Name
<OSS_NAME> - Open SQL Schema Space Name
<TARGET_TABLE_NAME> - Target Table Name
*/
CREATE COLUMN TABLE "<SPACE_NAME>#<OSS_NAME>"."<TARGET_TABLE_NAME>"
(TEST VARCHAR(100));
5 - Create Stored Procedure - Open Schema Space
Create a stored procedure that checks if the control table has a new run entry and populates the target table.
/*
Create Stored procedure that checks if control table has been populated with a new run entry
Find and replace the following with your values
<SPACE_NAME> - Space Name
<OSS_NAME> - Open SQL Schema Space Name
<STORED_PROC_NAME> - Name of Stored Procedure
<CONTROL_TABLE_NAME> - Name of Stored Procedure Control Table
<TARGET_TABLE_NAME> - Target Table Name
*/
CREATE PROCEDURE "<SPACE_NAME>#<OSS_NAME>"."<STORED_PROC_NAME>"
AS
BEGIN
IF
(SELECT 1 as RUN FROM "<SPACE_NAME>#<OSS_NAME>"."<CONTROL_TABLE_NAME>"
WHERE "DATE" = CURRENT_DATE AND "SP_NAME" = '<STORED_PROC_NAME>' AND "RUN" = 0 AND "SP_START_DATE_TIME" IS NULL
) = 1
THEN
--Updater SP Start Time
UPDATE "<SPACE_NAME>#<OSS_NAME>"."<CONTROL_TABLE_NAME>"
SET "SP_START_DATE_TIME" = CURRENT_TIMESTAMP
WHERE "DATE" = CURRENT_DATE AND "SP_NAME" = '<STORED_PROC_NAME>' AND "RUN" = 0 ;
COMMIT;
-- Call other Stored procedures or steps
INSERT INTO "<SPACE_NAME>#<OSS_NAME>"."<TARGET_TABLE_NAME>" ("TEST")
VALUES (CONCAT('Test Entry - ',(current_utctimestamp)));
-- --Updater SP End Time
UPDATE "<SPACE_NAME>#<OSS_NAME>"."<CONTROL_TABLE_NAME>"
SET "RUN" = 1, "SP_END_DATE_TIME" = CURRENT_TIMESTAMP
WHERE "DATE" = CURRENT_DATE AND "SP_NAME" = '<STORED_PROC_NAME>' AND "RUN" = 0 ;
END IF;
END;
6 - Schedule Stored Procedure - Open Schema Space
Schedule the stored procedure to run every minute.
/*
Find and replace the following with your values
<SPACE_NAME> - Space Name
<OSS_NAME> - Open SQL Schema Space Name
<STORED_PROC_NAME> - Name of Stored Procedure
*/
-- Schedule Stored Procedure to run every minute
CREATE SCHEDULER JOB <STORED_PROC_NAME>_JOB CRON '* * * * * * 00'
ENABLE PROCEDURE "<SPACE_NAME>#<OSS_NAME>"."<STORED_PROC_NAME>" ;7 - Create Dummy Record Table - Open Schema Space
Table that contains one record, used for record generation in dataflow.
/*
Create Dummy Record table and populate with 1 entry
Find and replace the following with your values
<SPACE_NAME> - Space Name
<OSS_NAME> - Open SQL Schema Space Name
<DUMMY_RECORD_TABLE_NAME> - Name for table to hold dummy record for dataflow use
*/
CREATE COLUMN TABLE "<SPACE_NAME>#<OSS_NAME>"."<DUMMY_RECORD_TABLE_NAME>"(
ID" INTEGER
PRIMARY KEY ("ID"));
INSERT INTO "<SPACE_NAME>#<OSS_NAME>"."<DUMMY_RECORD_TABLE_NAME>" ("ID") VALUES(1);8 - Create Data Flow - Main Space
Create a Data Flow that will populate CONTROL_TABLE with the next RUN_NUMBER causing the stored procedure to be executed.
You can use CSN/JSON file below but will need to ensure Open SQL Schema space objects have already been created, find and replace the values below and save as .json before importing.
<SPACE_NAME> - Space Name
<OSS_NAME> - Open SQL Schema Space Name
<CONTROL_TABLE_NAME> - Name of Stored Procedure Control Table
<STORED_PROC_NAME> - Name of Stored Procedure
<DUMMY_RECORD_TABLE_NAME> - Table that holds dummy record for dataflow record generation
<DATAFLOW_NAME> - Name of Dataflow
9 - Run the Data Flow - Main Space
You're now ready to run the data flow but be aware that the job is not complete when the the data flow execution has finished, you should check the Control Table to confirm when the stored procedure execution has completed.
I hope you find this guide useful and happy to hear any feedback or alternative workarounds that have been used.
Full Open SQL Schema Object Creation Script
/*
All Variables
Find and replace the following with your values
<SPACE_NAME> - Space Name
<OSS_NAME> - Open SQL Schema Space Name
<CONTROL_TABLE_NAME> - Name of Stored Procedure Control Table
<STORED_PROC_NAME> - Name of Stored Procedure
<DUMMY_RECORD_TABLE_NAME> - Table that holds dummy record for dataflow record generation
<TARGET_TABLE_NAME> - Target Table Name
<DATAFLOW_NAME> - Name of Dataflow
*/
/*
Create Control Table for Stored Procedure Execution
Find and replace the following with your values
<SPACE_NAME> - Space Name
<OSS_NAME> - Open SQL Schema Space Name
<CONTROL_TABLE_NAME> - Name of Stored Procedure Control Table
*/
CREATE COLUMN TABLE "<SPACE_NAME>#<OSS_NAME>"."<CONTROL_TABLE_NAME>"(
"SP_NAME" NVARCHAR(100) NOT NULL,
"DATE" DATE NOT NULL,
"RUN_NUMBER" INTEGER NOT NULL,
"SP_START_DATE_TIME" LONGDATE,
"SP_END_DATE_TIME" LONGDATE,
"RUN_STATUS" INTEGER NOT NULL,
PRIMARY KEY ("SP_NAME","DATE","RUN_NUMBER")
);
/*
Provide main space all privileges to Stored Procedure Control Table
Find and replace the following with your values
<SPACE_NAME> - Space Name
<OSS_NAME> - Open SQL Schema Space Name
<CONTROL_TABLE_NAME> - Name of Stored Procedure Control Table
*/
CALL "DWC_GLOBAL"."GRANT_PRIVILEGE_TO_SPACE" (
OPERATION => 'GRANT',
PRIVILEGE => 'SELECT',
SCHEMA_NAME => '<SPACE_NAME>#<OSS_NAME>',
OBJECT_NAME => '<CONTROL_TABLE_NAME>',
SPACE_ID => '<SPACE_NAME>');
CALL "DWC_GLOBAL"."GRANT_PRIVILEGE_TO_SPACE" (
OPERATION => 'GRANT',
PRIVILEGE => 'UPDATE',
SCHEMA_NAME => '<SPACE_NAME>#<OSS_NAME>',
OBJECT_NAME => '<CONTROL_TABLE_NAME>',
SPACE_ID => '<SPACE_NAME>');
CALL "DWC_GLOBAL"."GRANT_PRIVILEGE_TO_SPACE" (
OPERATION => 'GRANT',
PRIVILEGE => 'DELETE',
SCHEMA_NAME => '<SPACE_NAME>#<OSS_NAME>',
OBJECT_NAME => '<CONTROL_TABLE_NAME>',
SPACE_ID => '<SPACE_NAME>');
CALL "DWC_GLOBAL"."GRANT_PRIVILEGE_TO_SPACE" (
OPERATION => 'GRANT',
PRIVILEGE => 'INSERT',
SCHEMA_NAME => '<SPACE_NAME>#<OSS_NAME>',
OBJECT_NAME => '<CONTROL_TABLE_NAME>',
SPACE_ID => '<SPACE_NAME>');
/*
Create a table for the output
Find and replace the following with your values
<SPACE_NAME> - Space Name
<OSS_NAME> - Open SQL Schema Space Name
<TARGET_TABLE_NAME> - Target Table Name
*/
CREATE COLUMN TABLE "<SPACE_NAME>#<OSS_NAME>"."<TARGET_TABLE_NAME>"
(TEST VARCHAR(100));
/*
Create Stored procedure that checks if control table has been populated with a new run entry
Find and replace the following with your values
<SPACE_NAME> - Space Name
<OSS_NAME> - Open SQL Schema Space Name
<STORED_PROC_NAME> - Name of Stored Procedure
<CONTROL_TABLE_NAME> - Name of Stored Procedure Control Table
<TARGET_TABLE_NAME> - Target Table Name
*/
CREATE PROCEDURE "<SPACE_NAME>#<OSS_NAME>"."<STORED_PROC_NAME>"
AS
BEGIN
IF
(SELECT DISTINCT 1 as RUN_STATUS FROM "<SPACE_NAME>#<OSS_NAME>"."<CONTROL_TABLE_NAME>"
WHERE "DATE" = CURRENT_DATE AND "SP_NAME" = '<STORED_PROC_NAME>' AND "RUN_STATUS" = 0 AND "SP_START_DATE_TIME" IS NULL
) = 1
THEN
--Updater SP Start Time
UPDATE "<SPACE_NAME>#<OSS_NAME>"."<CONTROL_TABLE_NAME>"
SET "SP_START_DATE_TIME" = CURRENT_TIMESTAMP
WHERE "DATE" = CURRENT_DATE AND "SP_NAME" = '<STORED_PROC_NAME>' AND "RUN_STATUS" = 0
and "RUN_NUMBER" = (SELECT MIN("RUN_NUMBER") FROM "<SPACE_NAME>#<OSS_NAME>"."<CONTROL_TABLE_NAME>" WHERE "DATE" = CURRENT_DATE AND "SP_NAME" = '<STORED_PROC_NAME>' AND "RUN_STATUS" = 0) ;
COMMIT;
-- Call other Stored procedures or steps
INSERT INTO "<SPACE_NAME>#<OSS_NAME>"."<TARGET_TABLE_NAME>" ("TEST")
VALUES (CONCAT('Test Entry - ',(current_utctimestamp)));
-- --Updater SP End Time
UPDATE "<SPACE_NAME>#<OSS_NAME>"."<CONTROL_TABLE_NAME>"
SET "RUN_STATUS" = 1, "SP_END_DATE_TIME" = CURRENT_TIMESTAMP
WHERE "DATE" = CURRENT_DATE AND "SP_NAME" = '<STORED_PROC_NAME>' AND "RUN_STATUS" = 0
and "RUN_NUMBER" = (SELECT MIN("RUN_NUMBER") FROM "<SPACE_NAME>#<OSS_NAME>"."<CONTROL_TABLE_NAME>" WHERE "DATE" = CURRENT_DATE AND "SP_NAME" = '<STORED_PROC_NAME>' AND "RUN_STATUS" = 0) ;
END IF;
END;
/*
Schedule the Stored Procedure to run every minute
Find and replace the following with your values
<SPACE_NAME> - Space Name
<OSS_NAME> - Open SQL Schema Space Name
<STORED_PROC_NAME> - Name of Stored Procedure
*/
-- Schedule Stored Procedure to run every minute
CREATE SCHEDULER JOB <STORED_PROC_NAME>_JOB CRON '* * * * * * 00'
ENABLE PROCEDURE "<SPACE_NAME>#<OSS_NAME>"."<STORED_PROC_NAME>" ;
/*
Create Dummy Record table and populate with 1 entry
Find and replace the following with your values
<SPACE_NAME> - Space Name
<OSS_NAME> - Open SQL Schema Space Name
<DUMMY_RECORD_TABLE_NAME> - Name for table to hold dummy record for dataflow use
*/
CREATE COLUMN TABLE "<SPACE_NAME>#<OSS_NAME>"."<DUMMY_RECORD_TABLE_NAME>"(
"ID" INTEGER
,PRIMARY KEY ("ID"));
INSERT INTO "<SPACE_NAME>#<OSS_NAME>"."<DUMMY_RECORD_TABLE_NAME>" ("ID") VALUES(1);
Find and Replace with your values and save as .json
<SPACE_NAME> - Space Name
<OSS_NAME> - Open SQL Schema Space Name
<CONTROL_TABLE_NAME> - Name of Stored Procedure Control Table
<STORED_PROC_NAME> - Name of Stored Procedure
<DUMMY_RECORD_TABLE_NAME> - Table that holds dummy record for dataflow record generation
<DATAFLOW_NAME> - Name of Dataflow
{
"definitions": {
"<DUMMY_RECORD_TABLE_NAME>": {
"kind": "entity",
"elements": {
"ID": {
"@EndUserText.label": "ID",
"type": "cds.Integer",
"key": true,
"notNull": true
}
},
"@EndUserText.label": "<DUMMY_RECORD_TABLE_NAME>",
"@ObjectModel.modelingPattern": {
"#": "DATA_STRUCTURE"
},
"@ObjectModel.supportedCapabilities": [
{
"#": "DATA_STRUCTURE"
}
],
"@DataWarehouse.external.schema": "<SPACE_NAME>#<OSS_NAME>",
"@DataWarehouse.external.entity": "<DUMMY_RECORD_TABLE_NAME>"
},
"<CONTROL_TABLE_NAME>": {
"kind": "entity",
"elements": {
"SP_NAME": {
"@EndUserText.label": "SP_NAME",
"type": "cds.String",
"length": 100,
"key": true,
"notNull": true
},
"DATE": {
"@EndUserText.label": "DATE",
"type": "cds.Date",
"key": true,
"notNull": true
},
"RUN_NUMBER": {
"@EndUserText.label": "RUN_NUMBER",
"type": "cds.Integer",
"key": true,
"notNull": true
},
"SP_START_DATE_TIME": {
"@EndUserText.label": "SP_START_DATE_TIME",
"type": "cds.Timestamp"
},
"SP_END_DATE_TIME": {
"@EndUserText.label": "SP_END_DATE_TIME",
"type": "cds.Timestamp"
},
"RUN_STATUS": {
"@EndUserText.label": "RUN_STATUS",
"type": "cds.Integer",
"notNull": true
}
},
"@EndUserText.label": "<CONTROL_TABLE_NAME>",
"@ObjectModel.modelingPattern": {
"#": "DATA_STRUCTURE"
},
"@ObjectModel.supportedCapabilities": [
{
"#": "DATA_STRUCTURE"
}
],
"@DataWarehouse.external.schema": "<SPACE_NAME>#<OSS_NAME>",
"@DataWarehouse.external.entity": "<CONTROL_TABLE_NAME>"
}
},
"version": {
"csn": "1.0"
},
"meta": {
"creator": "CDS Compiler v1.19.2"
},
"$version": "1.0",
"dataflows": {
"<DATAFLOW_NAME>": {
"kind": "sap.dis.dataflow",
"@EndUserText.label": "<DATAFLOW_NAME>",
"contents": {
"properties": {},
"metadata": {
"dwc-isPrimaryKeysProcessed": true,
"dwc-restartOnFail": false
},
"parameters": {},
"parameterMapping": {},
"description": "<DATAFLOW_NAME>",
"processes": {
"projection1": {
"component": "com.sap.dataflow.projection",
"metadata": {
"label": "RUNS TODAY",
"x": 197.99999904632568,
"y": 112,
"height": 60,
"width": 120,
"config": {
"expression": "\"DATE\" = CURRENT_DATE()\nand \"SP_NAME\" = '<STORED_PROC_NAME>'",
"attributeMappings": [
{
"target": "SP_NAME",
"expression": "\"SP_NAME\""
},
{
"target": "DATE",
"expression": "\"DATE\""
},
{
"target": "RUN_NUMBER",
"expression": "\"RUN_NUMBER\""
}
]
},
"inports": [
{
"name": "inTable",
"type": "table",
"vtype-ID": "$INLINE.source3_outTable"
}
],
"outports": [
{
"name": "outTable",
"type": "table",
"vtype-ID": "$INLINE.projection1_outTable"
}
]
}
},
"aggregation1": {
"component": "com.sap.dataflow.aggregation",
"metadata": {
"label": "MAX_RUN_NUMBER",
"x": 367.9999990463257,
"y": 112,
"height": 60,
"width": 120,
"config": {
"aggregationDefinitions": [
{
"source": "SP_NAME",
"target": "SP_NAME"
},
{
"source": "DATE",
"target": "DATE"
},
{
"source": "RUN_NUMBER",
"target": "RUN_NUMBER",
"aggregation": "MAX"
}
]
},
"inports": [
{
"name": "inTable",
"type": "table",
"vtype-ID": "$INLINE.projection1_outTable"
}
],
"outports": [
{
"name": "outTable",
"type": "table",
"vtype-ID": "$INLINE.aggregation1_outTable"
}
]
}
},
"projection2": {
"component": "com.sap.dataflow.projection",
"metadata": {
"label": "CREATE FIELDS",
"x": 367.9999990463257,
"y": 12,
"height": 60,
"width": 120,
"config": {
"attributeMappings": [
{
"target": "DATE",
"expression": "CURRENT_DATE()"
},
{
"target": "RUN_STATUS",
"expression": "0"
},
{
"target": "SP_NAME",
"expression": "'<STORED_PROC_NAME>'"
},
{
"target": "ID",
"expression": "\"ID\""
}
]
},
"inports": [
{
"name": "inTable",
"type": "table",
"vtype-ID": "$INLINE.source1_outTable"
}
],
"outports": [
{
"name": "outTable",
"type": "table",
"vtype-ID": "$INLINE.projection2_outTable"
}
]
}
},
"projection3": {
"component": "com.sap.dataflow.projection",
"metadata": {
"label": "NEW_RUN_NUMBER",
"x": 723.9999980926514,
"y": 62,
"height": 60,
"width": 120,
"config": {
"attributeMappings": [
{
"target": "NEW_RUN_NUMBER",
"expression": "IFNULL(\"RUN_NUMBER\",0)+1"
},
{
"target": "DATE",
"expression": "\"DATE\""
},
{
"target": "RUN_STATUS",
"expression": "\"RUN_STATUS\""
},
{
"target": "SP_NAME",
"expression": "\"SP_NAME\""
},
{
"target": "ID",
"expression": "\"ID\""
}
]
},
"inports": [
{
"name": "inTable",
"type": "table",
"vtype-ID": "$INLINE.join1_outTable"
}
],
"outports": [
{
"name": "outTable",
"type": "table",
"vtype-ID": "$INLINE.projection3_outTable"
}
]
}
},
"join1": {
"component": "com.sap.dataflow.join",
"metadata": {
"label": "Join 1",
"x": 553.9999980926514,
"y": 62,
"height": 60,
"width": 120,
"config": {
"tableMappings": [
{
"source": "inTable1",
"attributeMappings": [
{
"source": "DATE",
"target": "DATE"
},
{
"source": "RUN_STATUS",
"target": "RUN_STATUS"
},
{
"source": "SP_NAME",
"target": "SP_NAME"
},
{
"source": "ID",
"target": "ID"
}
]
},
{
"source": "inTable2",
"attributeMappings": [
{
"source": "RUN_NUMBER",
"target": "RUN_NUMBER"
}
]
}
],
"joinDefinitions": [
{
"left": "inTable1",
"right": "inTable2",
"type": "LEFT_OUTER",
"joinOptions": {
"leftCache": "automatic",
"rightCache": "automatic",
"leftRank": 0,
"rightRank": 0
},
"condition": "\"inTable1\".\"DATE\" = \"inTable2\".\"DATE\"",
"joinOptimizationType": "Automatic"
}
]
},
"inports": [
{
"name": "inTable1",
"type": "table",
"vtype-ID": "$INLINE.projection2_outTable"
},
{
"name": "inTable2",
"type": "table",
"vtype-ID": "$INLINE.aggregation1_outTable"
}
],
"outports": [
{
"name": "outTable",
"type": "table",
"vtype-ID": "$INLINE.join1_outTable"
}
]
}
},
"source3": {
"component": "com.sap.database.table.consumer",
"metadata": {
"label": "<CONTROL_TABLE_NAME>",
"x": 12,
"y": 62,
"height": 60,
"width": 120,
"config": {
"service": "HANA",
"hanaConnection": {
"configurationType": "Configuration Manager",
"connectionID": "$DWC"
},
"qualifiedName": "<CONTROL_TABLE_NAME>",
"dwcEntity": "<CONTROL_TABLE_NAME>",
"remoteObjectType": "TABLE",
"fetchSize": 1000,
"forceFetchSize": false,
"failOnStringTruncation": true,
"dwcExternalSchema": "<SPACE_NAME>#<OSS_NAME>"
},
"outports": [
{
"name": "outTable",
"type": "table",
"vtype-ID": "$INLINE.source3_outTable"
}
]
}
},
"target1": {
"component": "com.sap.database.table.producer",
"metadata": {
"label": "<CONTROL_TABLE_NAME>",
"x": 893.9999980926514,
"y": 62,
"height": 60,
"width": 120,
"config": {
"service": "HANA",
"hanaConnection": {
"configurationType": "Configuration Manager",
"connectionID": "$DWC"
},
"qualifiedName": "<CONTROL_TABLE_NAME>",
"dwcEntity": "<CONTROL_TABLE_NAME>",
"remoteObjectType": "TABLE",
"fetchSize": 1000,
"forceFetchSize": false,
"failOnStringTruncation": true,
"dwcExternalSchema": "<SPACE_NAME>#<OSS_NAME>",
"mode": "append",
"upsert": false,
"batchSize": 1000,
"forceBatchSize": false,
"attributeMappings": [
{
"expression": "\"DATE\"",
"target": "DATE"
},
{
"expression": "\"RUN_STATUS\"",
"target": "RUN_STATUS"
},
{
"expression": "\"SP_NAME\"",
"target": "SP_NAME"
},
{
"expression": "\"NEW_RUN_NUMBER\"",
"target": "RUN_NUMBER"
}
],
"hanaAdaptedDataset": {
"schema": {
"genericType": "TABLE",
"tableBasedRepresentation": {
"attributes": [
{
"name": "SP_NAME",
"templateType": "string",
"length": 100
},
{
"name": "DATE",
"templateType": "date"
},
{
"name": "RUN_NUMBER",
"templateType": "int32"
},
{
"name": "SP_START_DATE_TIME",
"templateType": "timestamp"
},
{
"name": "SP_END_DATE_TIME",
"templateType": "timestamp"
},
{
"name": "RUN_STATUS",
"templateType": "int32"
}
],
"uniqueKeys": [
{
"attributeReferences": [
"SP_NAME",
"DATE",
"RUN_NUMBER"
]
}
]
}
}
}
},
"inports": [
{
"name": "inTable",
"type": "table",
"vtype-ID": "$INLINE.projection3_outTable"
}
]
}
},
"source1": {
"component": "com.sap.database.table.consumer",
"metadata": {
"label": "<DUMMY_RECORD_TABLE_NAME>",
"x": 197.99999904632568,
"y": 12,
"height": 60,
"width": 120,
"config": {
"service": "HANA",
"hanaConnection": {
"configurationType": "Configuration Manager",
"connectionID": "$DWC"
},
"qualifiedName": "<DUMMY_RECORD_TABLE_NAME>",
"dwcEntity": "<DUMMY_RECORD_TABLE_NAME>",
"remoteObjectType": "TABLE",
"fetchSize": 1000,
"forceFetchSize": false,
"failOnStringTruncation": true,
"dwcExternalSchema": "<SPACE_NAME>#<OSS_NAME>"
},
"outports": [
{
"name": "outTable",
"type": "table",
"vtype-ID": "$INLINE.source1_outTable"
}
]
}
}
},
"groups": [],
"connections": [
{
"metadata": {
"points": "322.9999990463257,142 362.9999990463257,142"
},
"src": {
"port": "outTable",
"process": "projection1"
},
"tgt": {
"port": "inTable",
"process": "aggregation1"
}
},
{
"metadata": {
"points": "492.9999990463257,42 520.9999985694885,42 520.9999985694885,83 548.9999980926514,83"
},
"src": {
"port": "outTable",
"process": "projection2"
},
"tgt": {
"port": "inTable1",
"process": "join1"
}
},
{
"metadata": {
"points": "492.9999990463257,142 520.9999985694885,142 520.9999985694885,101 548.9999980926514,101"
},
"src": {
"port": "outTable",
"process": "aggregation1"
},
"tgt": {
"port": "inTable2",
"process": "join1"
}
},
{
"metadata": {
"points": "678.9999980926514,92 718.9999980926514,92"
},
"src": {
"port": "outTable",
"process": "join1"
},
"tgt": {
"port": "inTable",
"process": "projection3"
}
},
{
"metadata": {
"points": "137,92 164.99999952316284,92 164.99999952316284,142 192.99999904632568,142"
},
"src": {
"port": "outTable",
"process": "source3"
},
"tgt": {
"port": "inTable",
"process": "projection1"
}
},
{
"metadata": {
"points": "848.9999980926514,92 888.9999980926514,92"
},
"src": {
"port": "outTable",
"process": "projection3"
},
"tgt": {
"port": "inTable",
"process": "target1"
}
},
{
"metadata": {
"points": "322.9999990463257,42 362.9999990463257,42"
},
"src": {
"port": "outTable",
"process": "source1"
},
"tgt": {
"port": "inTable",
"process": "projection2"
}
}
],
"inports": {},
"outports": {},
"vTypes": {
"scalar": {
"string_100": {
"name": "string_100",
"description": "String(100)",
"vflow.type": "scalar",
"template": "string",
"value.length": 100
},
"string_23": {
"name": "string_23",
"description": "String(23)",
"vflow.type": "scalar",
"template": "string",
"value.length": 100
}
},
"structure": {},
"table": {
"projection1_outTable": {
"name": "projection1_outTable",
"vflow.type": "table",
"rows": {
"components": [
{
"SP_NAME": {
"vflow.type": "scalar",
"vtype-ID": "$INLINE.string_100"
}
},
{
"DATE": {
"vflow.type": "scalar",
"vtype-ID": "com.sap.core.date"
}
},
{
"RUN_NUMBER": {
"vflow.type": "scalar",
"vtype-ID": "com.sap.core.int32"
}
}
]
},
"keys": [
"SP_NAME",
"DATE",
"RUN_NUMBER"
]
},
"aggregation1_outTable": {
"name": "aggregation1_outTable",
"vflow.type": "table",
"rows": {
"components": [
{
"SP_NAME": {
"vflow.type": "scalar",
"vtype-ID": "$INLINE.string_100"
}
},
{
"DATE": {
"vflow.type": "scalar",
"vtype-ID": "com.sap.core.date"
}
},
{
"RUN_NUMBER": {
"vflow.type": "scalar",
"vtype-ID": "com.sap.core.int32"
}
}
]
},
"keys": [
"SP_NAME",
"DATE",
"RUN_NUMBER"
]
},
"projection2_outTable": {
"name": "projection2_outTable",
"vflow.type": "table",
"rows": {
"components": [
{
"DATE": {
"vflow.type": "scalar",
"vtype-ID": "com.sap.core.date"
}
},
{
"RUN_STATUS": {
"vflow.type": "scalar",
"vtype-ID": "com.sap.core.int32"
}
},
{
"SP_NAME": {
"vflow.type": "scalar",
"vtype-ID": "$INLINE.string_23"
}
},
{
"ID": {
"vflow.type": "scalar",
"vtype-ID": "com.sap.core.int32"
}
}
]
},
"keys": [
"ID"
]
},
"projection3_outTable": {
"name": "projection3_outTable",
"vflow.type": "table",
"rows": {
"components": [
{
"NEW_RUN_NUMBER": {
"vflow.type": "scalar",
"vtype-ID": "com.sap.core.int32"
}
},
{
"DATE": {
"vflow.type": "scalar",
"vtype-ID": "com.sap.core.date"
}
},
{
"RUN_STATUS": {
"vflow.type": "scalar",
"vtype-ID": "com.sap.core.int32"
}
},
{
"SP_NAME": {
"vflow.type": "scalar",
"vtype-ID": "$INLINE.string_23"
}
},
{
"ID": {
"vflow.type": "scalar",
"vtype-ID": "com.sap.core.int32"
}
}
]
},
"keys": [
"ID"
]
},
"join1_outTable": {
"name": "join1_outTable",
"vflow.type": "table",
"rows": {
"components": [
{
"DATE": {
"vflow.type": "scalar",
"vtype-ID": "com.sap.core.date"
}
},
{
"RUN_STATUS": {
"vflow.type": "scalar",
"vtype-ID": "com.sap.core.int32"
}
},
{
"SP_NAME": {
"vflow.type": "scalar",
"vtype-ID": "$INLINE.string_23"
}
},
{
"RUN_NUMBER": {
"vflow.type": "scalar",
"vtype-ID": "com.sap.core.int32"
}
},
{
"ID": {
"vflow.type": "scalar",
"vtype-ID": "com.sap.core.int32"
}
}
]
},
"keys": [
"RUN_NUMBER",
"ID"
]
},
"source3_outTable": {
"name": "source3_outTable",
"vflow.type": "table",
"rows": {
"components": [
{
"SP_NAME": {
"vflow.type": "scalar",
"vtype-ID": "$INLINE.string_100"
}
},
{
"DATE": {
"vflow.type": "scalar",
"vtype-ID": "com.sap.core.date"
}
},
{
"RUN_NUMBER": {
"vflow.type": "scalar",
"vtype-ID": "com.sap.core.int32"
}
},
{
"SP_START_DATE_TIME": {
"vflow.type": "scalar",
"vtype-ID": "com.sap.core.timestamp"
}
},
{
"SP_END_DATE_TIME": {
"vflow.type": "scalar",
"vtype-ID": "com.sap.core.timestamp"
}
},
{
"RUN_STATUS": {
"vflow.type": "scalar",
"vtype-ID": "com.sap.core.int32"
}
}
]
},
"keys": [
"SP_NAME",
"DATE",
"RUN_NUMBER"
]
},
"source1_outTable": {
"name": "source1_outTable",
"vflow.type": "table",
"rows": {
"components": [
{
"ID": {
"vflow.type": "scalar",
"vtype-ID": "com.sap.core.int32"
}
}
]
},
"keys": [
"ID"
]
}
}
}
},
"sources": {
"<CONTROL_TABLE_NAME>": {
"elements": {
"SP_NAME": {},
"DATE": {},
"RUN_NUMBER": {},
"SP_START_DATE_TIME": {},
"SP_END_DATE_TIME": {},
"RUN_STATUS": {}
}
},
"<DUMMY_RECORD_TABLE_NAME>": {
"elements": {
"ID": {}
}
}
},
"targets": {
"<CONTROL_TABLE_NAME>": {
"elements": {
"DATE": {},
"RUN_STATUS": {},
"SP_NAME": {},
"RUN_NUMBER": {}
}
}
},
"connections": {}
}
}
}
- SAP Managed Tags:
