This tutorial will show you how to create a transform stream and add custom header and footer in result of transformation.
Sample Data
For this example, the following extract data will be used:
120::SECURITY_ALIAS:1111111:UPDATE_DATE:2014-11-11:EFFECTIVE_DATE:2014-11-11:UPDATE_SOURCE:CREASMF:COUPON:1.1111:COUPON_DAY_OF_MONTH:1:SOURCE_NAME:EAGLE PACE :SMU_PRIMARY_ASSET_ID:1111:SMU_PRIMARY_ASSET_ID_TYPE:PROPERTY:HISTORY_ONLY_FLAG:N:ROWNUM:1:UPDATE_TIMESTAMP:2014-11-11T15\3A41\3A13-05\3A00:XREFIDENTIFIERS:0000111,LOAN;:SMU_XREFIDENTIFIERS:1111,PROPERTY;:UNDERLYING_CUSIP:2222:UNDERLYING_SECURITY:Adress #8:UNDERLYING_SEC_ALIAS:333333:FIRST_INCOME_DATE:2010-01-01:ISSUE_DATE:2010-01-01:LIFETIME_FLOOR:0:PRPL_FLAG:N:STAR_EARNINGS_PROCESS_IND:1:FID_CALLED_DT:2011-11-11:FIE_FIRST_PRIN_DT:2002-02-02:MBS_ORIG_FACE_AMT:22200000:
And the following outgoing message will be used:
'MMPriceRequest|' + Convert(Today(),'dd/mm/yyyy')
Identifier|Price Date|Price|ISO Currency Code|Yield|Yield type|Price Method|Babson Identifier [this is data lines]
[...]
ZZZZZZZ|1 [total records count]
To get the extract data, you can use Extract Demand List using following control message:
<EagleML xmlns="http://www.eagleinvsys.com/2011/EagleML-2-0" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:type="RunTaskRequest" eaglemlVersion="2-0" xsi:schemaLocation="http://www.eagleinvsys.com/2011/EagleML-2-0 eagleml-main-2-0.xsd" eaglemlType="RunTaskRequest"> <header> <messageId>TEST_DEMANDLIST_EXTRACT</messageId> <sentBy>http://www.client.com</sentBy> <sendTo>http://www.eagleinvsys.com/eagle_ml-2-0_default_cm_control_message</sendTo> <replyTo>http://www.client.com/Eagle Control Messages</replyTo> <creationTimestamp>2012-08-02T22:13:32.272-05:00</creationTimestamp> </header> <taskIdentifier> <correlationId correlationIdScheme="correlationIdScheme">TEST_DEMANDLIST_EXTRACT_10</correlationId> <businessTaskId correlationIdScheme="businessTaskIdScheme">TEST_DEMANDLIST_EXTRACT</businessTaskId> </taskIdentifier> <taskTypeEnum>NEW</taskTypeEnum> <taskParameters> <taskParameter> <name>ActionType</name> <dataType>S</dataType> <value>EXTRACT</value> </taskParameter> <taskParameter> <name>StreamName</name> <dataType>S</dataType> <value>eagle_ml-2-0_default_out_q</value> </taskParameter> <taskParameter> <name>FeedType</name> <dataType>S</dataType> <value>DEMANDLIST</value> </taskParameter> <taskParameter> <name>EffectiveDate</name> <dataType>S</dataType> <value>20141124</value> </taskParameter> <taskParameter> <name>UsePricingCenterDemand</name> <dataType>S</dataType> <value>Yes</value> </taskParameter> <taskParameter> <name>OutputFormat</name> <dataType>S</dataType> <value>TagValue</value> </taskParameter> <taskParameter> <name>T_StreamName</name> <dataType>S</dataType> <value>zbpr_t</value> </taskParameter> </taskParameters></EagleML>
This Control Message should drop into eagle_ml-2-0_default_cm_control_message stream. Don't forget change correlationId node value.
Description of additional task parameters:
- EffectiveDate - optional, set custom effective date for demand extract in yyyymmdd format. If this parameter is missed or empty then today() value will be used;
- UsePricingCenterDemand - optional, custom parameter for demand extract, possible values Yes/No;
- OutputFormat - optional, output extract format;
- T_StreamName - optional, custom transform stream (see [How to use custom T streams in Data Extracts ).
Create the Solution
Create a Solution From a Template
- In IWS, click on New Solution from the Solutions toolbar. The New Solution window will appear.
- Select 'Templates'. On the right side you will now see choices for 'Simple Transform and Generic Load' and 'Simple Load'. Select 'Simple Transform and Generic Load'.
- Enter a meaningful, detailed name for your solution in the pink textbox toward the bottom of the window.
- Click OK.
Always start with a template when creating a new transformation. By leveraging a template, many of the necessary properties and information will already be populated for you. This simplifies your work and reduces the risk of errors.
Setting Translator Transformation Parameters
The Select parameters window will appear.
On the left side of the window, under Source Format, select STAR (TagValue).
Then click the Load from File button on the bottom part of the Source section and select the location and name of the source data.
IWS will interpret the file based on the above selected options and render it in the Sample data tab.
You can click into the Raw data tab to help with the file interpretation if the results in Sample data are not as expected.
Note: If you do not have a sample file, you can enter the example data shown below directly in the Raw Data tab
On the right side of the window, under Destination Format, select CSV File and select Load from File to set example of outgoing message.
Click the Apply button on the bottom right corner.
Step 3. Mapping
After that, IWS (Integration & Workflow Studio) automatically creates all the necessary files and open first Translator worksheet.
Now, you can see two filled grid shapes: Source grid, which filled by sample Star data, and Destination grid, which filled by CSV data.
First, create simple mapping, drop Result Output Translation shape to worksheet and variable with "totalCount" name.
Note! Set the following expression to "totalCount" variable and properties:
expression: convert(atol(:totalCount
+ 1, 0)
Scope: GLOBAL
Conditionon to include: 0
Make double click to the "Result Output Translation" shape and enter to sub-worksheet. Drop variable from the toolbox, give the "varHeader" name and set following expression (this will be custom header of message):
Drop another variable from the toolbox, give the "varFooter" name and set following expression (this will be custom footer of message):
Note: totalCount var stores info about processed records.
Drop "Code" shape and set following expression:
:RES_MESSAGE: is reserved variable, which is used as result outgoing message. So we just add custom header before result of transformation and custom footer after and save it.
Also, need add some advanced shapes for correct processing:
Call include (set source as path "eagle_ml-2-0_cm/w_config.inc"):
Note: drop it before Code shape.
Drop variable and give the name "CorrelationId" and set following expression:
Here is expression:
SubString(:FILE_NAME:,Find( :FILE_NAME:, :corrIdSep:, 0, 1 ) + StringLength(:corrIdSep,Find( :FILE_NAME:, :corrIdSep:, 0, 2 ) - Find( :FILE_NAME:, :corrIdSep:, 0, 1 ) - StringLength(:corrIdSep)
Drop also "Status" shape to check coorelationId var.
After that drop "Loop while" shape and enter to its sub-worksheet:
Drop inside "Call include" shape (set source as path "eagle_ml-2-0_cm/extract_eofw.inc") and "Break" shape:
Apply changes (click green check on the top of second sub-worksheet).
Set condition to this loop:
Drop variable and give the name "ParOutputFormat" between Status shape and Loop While, set 'CSV' expression:
Apply changes for first sub-worksheet.
Step 4. Remove Task Loader
Return to main workflow and remove loader step. As result you can see it:
Step 5. Publish
Last step is save solution and publish it.
Click to "Save All Files" button:
After that, click to "Publish" button:
Note: The "main" stream for receive data has same name as IWS solution (or main workflow).
Tutorial: How to Use Custom T Streams in Data Extracts
Run Task Request
In order to use custom T streams in Data Extracts, it necessary to specify the T_StreamName parameter in the Run Task Request (RTR) control message with a value of the name of custom T stream.
If the output format needs differ from the EagleML, that shoud be specified in the RTR too, using the OutputFormat parameter.
Let's consider the example of using custom T stream star_to_fixed_demo_t which has the output format FIXED. In the RTR, the output format should be defined as TagValue.
Example with star_to_fixed_demo_t T stream
RTR Control Message for run:
<EagleML xmlns="http://www.eagleinvsys.com/2011/EagleML-2-0" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:type="RunTaskRequest" eaglemlVersion="2-0" xsi:schemaLocation="http://www.eagleinvsys.com/2011/EagleML-2-0 eagleml-main-2-0.xsd" eaglemlType="RunTaskRequest"> <header> <messageId>1SAJZDC19GHFMIG9</messageId> <sentBy>http://www.eagleinvsys.com</sentBy> <sendTo>http://www.eagleinvsys.com</sendTo> <creationTimestamp></creationTimestamp> </header> <taskIdentifier> <correlationId correlationIdScheme="correlationIdScheme">dkISSUEPRICEEXTRACT7</correlationId> <businessTaskId correlationIdScheme="businessTaskIdScheme">ISSUEPRICEEXTRACT7</businessTaskId> </taskIdentifier> <taskTypeEnum>NEW</taskTypeEnum> <taskParameters> <taskParameter> <name>StreamName</name> <dataType>S</dataType> <value>eagle_ml-2-0_default_out_q</value> </taskParameter> <taskParameter> <name>ActionType</name> <dataType>S</dataType> <value>EXTRACT</value> </taskParameter> <taskParameter> <name>FeedType</name> <dataType>S</dataType> <value>ISSUEPRICEEXTRACT</value> </taskParameter> <taskParameter> <name>maxrows</name> <dataType>S</dataType> <value>500</value> </taskParameter> <taskParameter> <name>todate</name> <dataType>S</dataType> <value>2014-12-31 15-00-00</value> </taskParameter> <taskParameter> <name>fromdate</name> <dataType>S</dataType> <value>2005-01-20 00-00-00</value> </taskParameter> <taskParameter> <name>OutputFormat</name> <dataType>S</dataType> <value>TagValue</value> </taskParameter> <taskParameter> <name>T_StreamName</name> <dataType>S</dataType> <value>star_to_fixed_demo_t</value> </taskParameter> </taskParameters> </EagleML>
Run
In Eagle, open th Message Center Console Application.
Find the stream eagle_ml-2-0_default_cm_control_message in Message Streams list and send RTR Control Message by clicking on the stream and choosing Send data:
Note: Correlation Id parameter in RTR has to be unique for each run!
Next, go to the EJM Tasks Tab and enter as Search filter Correlation Id which was used in RTR.
The Extract Steps has been display in the below grid.
The star_to_fixed_demo_t has been called as the transformation stream for extracted data.
Double click on necessary row in Steps grid to see stream details.
The Incoming and Outgoing messages of star_to_fixed_demo_t are presented below:
The extracted and transformed data will be copied to the /cmw/extracts/ app directory.
0 Comments