Dear Readers,
Summary
The Data extract feature allows you to export through custom outbound file types when running a pipeline job. The feature is available only on environments that use XDL (Express Data Loader) and are configured on HANA Database. You can extract custom files that you have previously or newly configured in CDL. The files are then stored in your previously configured outbound folder.
Prerequisites
- User should have access to run a pipeline
- User should have access to WebIDE/Hana Studio/Eclipse for connecting to Tenant DB
- Custom Table and Custom Stored Proc should be created for the data to transfer
- SFTP dropbox Access to Outbound folder.
- RestAPI v2 and OData API Access for debugging
Data Extract Documentation and Examples
Steps to Setup Data Extract Process in Express Data Loader (XDL)
Login to SAP Commission Portal with your credentials
After successful login, you can click on APPS highlighted in RED, a dropdown will appear and choose Express Data Loader
Now you can click on Configuration and select from the dropdown File Type Setup
As you see, there are 2 types Inbound & Outbound file type... To know more about File Types
Click on Outbound and Click on (+) to create
Configure outbound file type and specify the target table and target stored procedure from below screens
As this task needs to be handled manually, please configure as per the above example
After Saving, you can see from below custom file type is created
Let's create a Custom Table in HANA DB
Create a custom table to store the Extracted data in the EXT schema. (Extraction will always run with the EXT schema)
Connect WebIDE or HANA Database with below example provided.
Example
CREATE COLUMN TABLE Paymentextract (
positionseq BIGINT NOT NULL,
participantseq BIGINT NOT NULL,
periodseq BIGINT NOT NULL,
processingunitseq BIGINT NOT NULL,
payment DECIMAL(25,10) NULL,
unittypeforpayment BIGINT NULL
);
Create Custom Procedure in HANA DB
Create a custom stored procedure to extract the data:
CREATE OR REPLACE PROCEDURE ExtractPayment(OUT FILENAME varchar(120), IN pPlRunSeq BIGINT)
LANGUAGE SQLSCRIPT
SQL SECURITY INVOKER
AS
BEGIN
DECLARE DBMTK_TRUE TINYINT := 1; DECLARE DBMTK_FALSE TINYINT := 0; /* boolean constants */
DECLARE vSeq BIGINT;
declare vPeriodSeq bigint;
declare vPUSeq bigint;
select periodSeq into vPeriodSeq from CS_PlRun where pipelineRunSeq = :pPlRunSeq;
select processingUnitSeq into vPUSeq from CS_PlRun where pipelineRunSeq = :pPlRunSeq;
delete from Paymentextract;
insert into Paymentextract (positionSeq, participantSeq, periodSeq, processingUnitSeq, payment, unitTypeForPayment)
(select positionSeq, payeeSeq, periodSeq, processingUnitSeq, value, unitTypeForValue from CS_Payment
where periodSeq = :vPeriodSeq and processingUnitSeq = :vPUSeq);
commit;
FILENAME := 'PAYMENTEXTRACT.txt';
END
Run a Pipeline for the Data Extract Stage
The Data Extracts stage (option) can be selected from the PipelineRun wizard and below screen is provided with an example which is configured as per above
DataExtract file in Outbound Folder
After the pipeline job is completed with Success, you should see the file extracted in the tenant's outbound directory (specified during CDL setup): To know more about dropbox (sFTP)
To Automate through RestAPI for Pipeline Job with Data Extract follow below
Request Method : POST
Request URL : https://<tenantid>.callidusondemand.com/api/v2/pipelines
Content-Type: application/json
Authorization: {{authtoken}}
[
{
"command": "PipelineRun",
"stageTypeSeq": "21673573206720532",
"calendarSeq": "2251799813685250",
"periodSeq": "2533274790396303",
"processingUnitSeq": "38280596832649218",
"runMode": "full",
"removeStaleResults":false,
"onDemand":true,
"defer":false,
"runStats": true,
"traceLevel": "status",
"runDataExtracts":true,
"dataExtractsFileType":"PAYMENTEXTRACT",
"userId":"yogananda.muthaiah@sap.com"
}
]
Conclusion:
Customer/Admins/Finance Team will save man-hours to extract the data from the DB session in a Production environment and moreover this will be a one-time setup for payout extract and easily configurable through UI.
Another Known Issue: If you see below pipeline error after running DataExtract, to resolve this issue Raise a support ticket immed.
- SAP Managed Tags:
17 Comments
| User | Count |
|---|---|
| 5 | |
| 3 | |
| 2 | |
| 2 | |
| 1 | |
| 1 | |
| 1 | |
| 1 | |
| 1 | |
| 1 |
