We can use Data Services scripting language to create

1)Single use components call scripts

2)Reusable components called Custom functions

 

This demo involves creation of both a script and a Custom Function.

 

 

We can create reusable functions in Data Services scripting language using the smart editor. To create a custom function, right click on the “Custom Function” tab, and click on “New”.

screen1.jpg

Provide the function name and a brief description of its functionalities.

screen2.jpg

By default the return type of the function is int. In the function GET_DEPTNAME we want the function to return a string containing department name.  So I needed to change the return type to varchar. To do this, I right click on the “Return  “ and choose “Properties”.

 

screen3.jpg

I change the Return value properties to varchar 30.

screen4.jpg

Next I declare a local variable for my function by right-clicking “Local” and selecting “Insert…”

screen5.jpg

I declare a local variable  named $LOCAL_VARIABLE of type varchar 30.

screen6.jpg

I right click on Parameter , to insert a input parameter named $DEPARTMENT_ID of type varchar 4.

 

screen7.jpg

I write a function that reads the department name from the database, based on the input department Id,  and  stores it in the local variable. The content of the local variable is returned by the function.

 

$LOCAL_VARIABLE= SQL('MY_DATASTORE’,'SELECT DEPTNAME FROM DEPARTMENT WHERE DEPT_ID={$DEPARTMENT_ID}');

RETURN $LOCAL_VARIABLE;

 

The table that I am referring to has 3 columns as shown in below figure.

screen8.jpg

I call the function from a script, in a job JOB_DEMO_4.

screen9.jpg

The script has a single line call to the function that I have just created. When I run the job, the department name for department Id='M10' is printed.

screen10.jpg

    Monitor sample rate gives the number of rows processed before Data Services writes information to the monitor log file and updates job events. Data Services writes information about the status of each source, target, or transform. For example, if this value is set as 1000, Data Services updates the logs after processing 1,000 rows. The default is 1000.

 

With a higher Monitor Sample Rate, Data Services collects more statistics before updating the log file. Hence the instances of I/O operation are reduced. This in turn will avoid higher elapsed time of job execution. However, with a higher monitor rate, there is more lag between displaying the errors, if any.

 

Right click on the job. Select ‘Properties’. In the “Execution Options” tab, give the monitor sample rate.

 

 




 
 
 
 
 
 
 
 
 
 
 
 


Radhika Rao

Embedded Data Flow

Posted by Radhika Rao Jun 16, 2012

    An embedded dataflow is a dataflow that is invoked within another data flow. Data passes into or out of the embedded dataflow from the parent flow through a single source or target. The embedded dataflow can contain any number of sources or targets, but only one input or one output can pass data to or from the parent data flow.

 

Use embedded data flows to:

 

  • Reuse dataflow logic: Save logical sections of a dataflow so you can use the exact logic in other data flows, or provide an easy way to replicate the logic and modify it for other flows.
  • Simplify dataflow display: Group sections of a dataflow in embedded data flows to allow clearer layout and documentation.
  • Debug dataflow logic: Replicate sections of a dataflow as embedded data flows so you can execute them independently.

 

Following steps explains the creation of embedded dataflow.

 

 

Step 1: Create a flow to extract the count of source and job id. Then select the required objects and right click and choose option “Make Embedded Dataflow” and name the reusable object. The reusable dataflow will be available now in the dataflow tab in the object library.

 

Step 2: Drag and drop this reusable embedded dataflow wherever required.

Data Transfer transform can be used to move data from a source or from another transform into the target datastore.

 

Benefits:

 

  • Data Transfer transform will push down the operations to database server when the transfer type is a database table.
  • It enables a full push-down operation even if the source and target are in different data stores.
  • This can be used after query transforms with GROUP BY, DISTINCT or ORDER BY functions which do not allow push down.

 

       However, Data Transfer transform is best suited only when the data volume is huge. If the data volume is less, its usage will not provide much difference in performance.

 

    Also the way data transfers work depends on the scenario. When a look up is used and in the subsequent query transform if ‘Group By’ is used, then after the look up, the data transfer can be used. But when there are no resource- intensive operations being used, usage of Data Transforms will not provide much of an advantage.

Snapshot of few fields of source file

 

AccountGroupcodeBalance amount
123452743
223451255
323455150
523457870

   

Snapshot of lookup table

XXX1XXX2amount selectionGroupcode selection
8076807601Balance1;3;15-25;27;29-35
8075807601Balance52-60;65;70;77-79;85
9004900401Balance5

 

Now you have to lookup the XXX1 and XXX2 value from the lookup table based on the condition : value of sourcefile.groupcode in (lookup table.groupcode selection).

 

Method 1 : First way is splitting the semi-colons separated in the rows as described in this link http://wiki.sdn.sap.com/wiki/pages/viewpage.action?pageId=283705665 and then apply the logic as shown below :

 

1. replace - into ..

 

e.g. replace_substr( ff_final_selection."Groupcode selection",'-','..' )

 

2. Append it with square brackets in case of range values and then append overall by ms()

 

e.g.  'ms(' || ifthenelse( index(Query."Groupcode selection",'.',1) is not null,'\[' || Query."Groupcode selection" || '\]' ,Query."Groupcode selection" ) || ')'

 

Our final lookup table looks like now :

 

 

XXX1XXX2amount selectionGroupcode selection
8076807601Balancems(1)
8076807601Balancems(3)
8076807601Balancems([15..25])
8076807601Balancems(27)
8076807601Balancems([29..35])
8075807601Balancems([52..60])
8075807601Balancems(65)
8075807601Balancems(70)
8075807601Balancems([77..79])
8075807601Balancems(85)
9004900401Balancems(5)

 

Now you can easily lookup these values using the operator '~' in the lookup_ext function and get the desired output.

 

For lookup_ext with ms(). you can follow the link http://wiki.sdn.sap.com/wiki/display/EIM/lookup_ext%28%29+with+pattern

 

Output will be :

 

AccountGroupcodexxx1xxx2Balance amount
1234527807680760143
2234512nullnull55
3234551nullnull50
5234578807580760170

 

 

Method2 : Instead of splitting into the rows using a custom function with word_ext function

 

Function definition is given below :

 

# Initialization of Variables
$L_CNTR = 1;
$L_CNTR1 = 0;
$L_CNTR2 = 1;
$L_LEN_STR = length($input_field);
$L_OUTPUT_STR = '';

# Count the no. of semi-colons
while ($L_CNTR <= $L_LEN_STR)
begin
$L_TEMP = substr($input_field,$L_CNTR,1);
if (match_simple($L_TEMP,';') = 1)
      $L_CNTR1 = $L_CNTR1 + 1;
$L_CNTR = $L_CNTR + 1;
end
# replace the column value with suitable syntax
while ($L_CNTR2 <= $L_CNTR1+1)
begin
$L_TEMP = word_ext( $input_field,$L_CNTR2,';');
if (index($L_TEMP,'.',1) is not null)
$L_OUTPUT_STR = ($L_OUTPUT_STR || '\[' || $L_TEMP || '\]');
else $L_OUTPUT_STR = ($L_OUTPUT_STR || $L_TEMP);
if ($L_CNTR2 <> ($L_CNTR1+1))
$L_OUTPUT_STR = ($L_OUTPUT_STR || ';');
else $L_OUTPUT_STR = $L_OUTPUT_STR;
$L_CNTR2 = $L_CNTR2 + 1;
end

$L_OUTPUT_STR = ('ms(\{' || $L_OUTPUT_STR || '\})' );
# While loop ends

Return $L_OUTPUT_STR;

 

Apply the above fxn on the filed in lookup table and then follow the same steps described in Method1

Actions

Filter Blog

By author:
By date:
By tag: