sap hana smart data integration

Data Replication and TransformationPDF download from SAP Help Portal:http://help.sap.com/saphelp_hanaplatform/helpdata/en/8c/da1ccc6a954c97bbbad069a6e3d8ba/content.htm

Created on July 22, 2015

The documentation may have changed since you downloaded the PDF. You can always find the latest information on SAP Help Portal.

NoteThis PDF document contains the selected topic and its subtopics (max. 150) in the selected structure. Subtopics from other structures are not included.

2015 SAP SE or an SAP affiliate company. All rights reserved. No part of this publication may be reproduced or transmitted in any form or for any purposewithout the express permission of SAP SE. The information contained herein may be changed without prior notice. Some software products marketed by SAP SEand its distributors contain proprietary software components of other software vendors. National product specifications may vary. These materials are provided bySAP SE and its affiliated companies ("SAP Group") for informational purposes only, without representation or warranty of any kind, and SAP Group shall not beliable for errors or omissions with respect to the materials. The only warranties for SAP Group products and services are those that are set forth in the expresswarranty statements accompanying such products and services, if any. Nothing herein should be construed as constituting an additional warranty. SAP and otherSAP products and services mentioned herein as well as their respective logos are trademarks or registered trademarks of SAP SE in Germany and othercountries. Please see www.sap.com/corporate-en/legal/copyright/index.epx#trademark for additional trademark information and notices.

Table of content

PUBLIC 2014 SAP SE or an SAP affiliate company. All rights reserved.

Page 1 of 31

Table of content1 Data Replication and Transformation1.1 Replicating Data1.1.1 Create a Replication Task1.1.2 Add a Target Column1.1.3 Edit a Target Column1.1.4 Delete a Target Column1.1.5 Load Behavior Options for Target Tables1.1.6 Activate and Execute a Replication Task1.2 Transforming Data Using SAP HANA Web-based Development Workbench1.2.1 Add a Variable to the Container Node1.2.2 Activate and Execute a Flowgraph1.3 Transforming Data Using SAP HANA Application Function Modeler1.3.1 Converting deprecated AFL Models (AFLPMML objects)1.3.2 Setting up the SAP HANA Application Function Modeler1.3.3 Flowgraphs1.3.4 Modeling a flowgraph1.3.4.1 Creating a Flowgraph1.3.4.2 Editing the flowgraph container1.3.4.3 Adding an object from the Project Explorer1.3.4.4 Adding a node from the Node Palette1.3.4.5 Editing a node1.3.4.6 Adding an anchor1.3.4.7 Editing an anchor1.3.4.8 Creating a connection1.3.4.9 Using the Table Editor1.3.4.10 Using the Mapping Editor1.3.4.11 Using the Expression Editor1.3.4.12 Using the Annotation Editor1.3.5 Tutorial1.3.6 Node palette flowgraphs1.3.6.1 Exporting the Node Palette1.3.6.2 Customizing the Node Palette1.3.6.3 Editing a node palette flowgraph1.4 Node Reference1.4.1 AFL Function1.4.2 Aggregation1.4.2.1 Aggregation Options1.4.3 Data Sink1.4.3.1 Data Sink Options1.4.4 Data Source1.4.4.1 Data Source Options1.4.5 Filter1.4.5.1 Filter Options1.4.6 Join1.4.6.1 Join Options1.4.7 Procedure1.4.7.1 Procedure options1.4.8 R-Script1.4.9 Sort1.4.9.1 Sort Options1.4.10 Union1.4.10.1 Union Options


Page 2 of 31

1 Data Replication and TransformationOverview of replicating and transforming data.Smart data integration is a set of functionality provided by several components you can use to retrieve data from an external system, transform it, and persist it inSAP HANA database tables. Smart data integration features and tools addressed in this section include:

The Replication Editor in the SAP HANA Web-based Development Workbench is used to create real time or batch replication scenarios for moving data intoSAP HANA studio.The Transformation Editor in the SAP HANA Web-based Development Workbench is used to create flowgraphs for transforming data, such as filtering,sorting and joining data into tables.Likewise, the application function modeler in SAP HANA Studio is also used to create flowgraphs for transforming data similar to SAP HANA Web-basedDevelopment Workbench.

Related InformationReplicating DataTransforming Data Using SAP HANA Web-based Development WorkbenchTransforming Data Using Application Function ModelerNode Reference

1.1 Replicating DataReplicate data from several objects in a remote source to tables in SAP HANA using the Replication Editor in SAP HANA Web-based Development Workbench.To replicate data from objects in a remote source into tables in SAP HANA, you must configure the replication process by creating an .hdbreptask file, whichopens a file specific to the Replication Editor.Before using the Replication Editor, you must have the proper rights to use the editor. See your system administrator to assign appropriate permissions. You mustalso have the run-time objects set up as described in the "SAP HANA Web-based Development Workbench: Catalog" chapter of the SAP HANA DeveloperGuide.

NoteThe Web-based Editor tool is available on the SAP HANA XS web server at the following URL:http:// :80 /sap/hana/ide/editor.

After the .hdbreptask has been configured, activate it to generate a stored procedure, a remote subscription, one or more virtual tables for objects that you want toreplicate, and target tables. The remote subscription is only created when the Initial load only option is cleared. When the stored procedure is called, an initialload is run. When real time is enabled, then subsequent changes are automatically distributed.DDL changes to source tables that are associated with a replication task will be propagated to SAP HANA so that the same changes will be applied to the SAPHANA target tables.

1.1.1 Create a Replication TaskA replication task retrieves data from one or more objects in a single remote source and populates one or more tables in SAP HANA.

PrerequisitesBefore using the Replication Editor, you must have the proper rights to use the editor. See your system administrator to assign appropriate permissions.

Procedure1. Highlight a package from the content pane and right-click. Choose File New Replication Task .2. Enter a file name and then click Create .3. In Remote Source , select the source data location from the drop-down list.4. In Target Schema , select the schema for the target table.5. In Virtual Table Schema , select the schema for the virtual table.6. Set Drop target table if exists with one of the following:

7. Set Initial load only . When selected, the data is replicated, but not continuously updated when changes are made to the source system.

NoteInitial load only is automatically disabled when the remote source doesn't support continuous loading.

8. (Optional) In the Virtual Table Prefix option, enter some identifying letters or numbers to help you label the virtual table. You might want a prefix to identifywhere the data came from or the type of information that it contains.

9. To include one or more tables in the replication task, click Add Objects .10. In the Select Remote Source window, you can browse to or search for the object(s) as follows. Note that in the Importable column, a zero means that it is

not importable and a one means that it can be imported. You can use Shift-click or Ctrl-click to select multiple objects.To browse for the object, expand the nodes as necessary and select the object(s).To search for an object:

Target table OutcomeExisting When this option is selected and there is an existing target table, the target table is deleted and recreated.New When this option is selected, a new target table is created.


Page 3 of 31

Click Create Dictionary to build a searchable dictionary of objects from the source.Enter filter criteria for Display Name, Unique Name, or Object Description that Contains, Equals, Starts with, or Ends with characters youenter.For example, to filter by name, enter the first few characters of the object name to display the objects that begin with those characters. TheCase sensitive restriction is optional. To add additional criteria to further filter the list, click the plus sign and enter the additional parameter(s).

(Optional) The bottom of this interface includes a time stamp for when the dictionary was last updated. You can also refresh or clear thedictionary here.Select the object(s) to add.

11. (Optional). Enter a prefix in the Target name prefix option. For example, you might want the prefix to be ADDR_ if the output table contains address data.The rest of the table name is the same as the remote object name. You can change the entire name on the main editing page, if necessary.

12. (Optional) Select Table Level Replication to replicate the entire table. When this option is selected, columns cannot be added, changed or removed.13. With the desired object(s) selected, click OK .14. (Optional) Click the Filter tab to enter SQL statements to further limit the rows being replicated using the SQL syntax of a WHERE clause. Only records

that meet the criteria of the filter are replicated.15. Click Save .

Related InformationAdd a Target ColumnEdit a Target ColumnDelete a Target ColumnLoad Behavior Options for Target TablesActivate and Execute a Replication Task

1.1.2 Add a Target ColumnAdd a column in a replication task.

Procedure1. From the Replication Editor, in the Target Columns tab, click Add .2. Choose whether to create a column or to include a column from a remote object.

From remote object : Browse to a source and table and choose the column you replicated in the virtual table. Select whether the column is part of theprimary key , and then click OK .

1. Select the column name.2. Select if this column is part of the primary key .3. Click OK .4. Rename the column.5. Enter the projection.

From scratch : Complete the following steps to create a column. Then, you can enter some SQL statements in the Filter tab to populate the columnduring replication.

1. Enter the Name of the column.2. Select the Data Type . For example, varchar, decimal and so on.3. Enter the number of characters allowed in the column.4. Enter the Projection (the mapped name) of the column.

NoteThe projection can be any one of the following:

column (enter the name of the source column in double quotes, for example, "APJ_SALES")string literal (enter the string as a value in single quotes, for example 'ERPCLNT800')SQL expression (for example, "firstname" + "lastname")

5. Select is nullable if the value can be empty.6. Select is part of the primary key if the data in the column will uniquely identify each record in a table.7. Click OK .

Related InformationCreate a replication task

1.1.3 Edit a Target ColumnModify the column to correct the data or to make it more accurate or useful.

ContextFor example, if you were using a Social Security number as a part of a primary key, and you need to stop using it for the primary key, you can edit the column tounselect the option. To edit a column:

Procedure1. Select the column.


Page 4 of 31

2. Click Edit .3. Change the data type, length, projection, nullable, and/or primary key options.4. Click OK .

1.1.4 Delete a Target ColumnRemove a column so that it is no longer used in the flowgraph.

Procedure1. Select the column.2. Click Delete .3. Confirm your deletion, and then click OK .

1.1.5 Load Behavior Options for Target TablesFor real-time replication tasks, you can select different target table types including one-to-one replication, actuals tables, or change log tables.

ContextSimple replication of a source table to a target table results in a copy of the source (same row count, same columns). However because the table replicationprocess also includes information on what row has changed and when, you can add these change types and change times to the target table.For example, in simple replication, deleted rows do not display in the target table. To display the rows that were deleted, you can select the Actuals Table optionthat functions as UPSERT when loading the target. This option adds two columns CHANGE_TYPE and CHANGE_TIME to the target table. The deleted rowsdisplay with a CHANGE_TYPE of D.You can also choose to display all changes to the target (INSERT functionality) which provides a change log table. Every changed row is inserted into the targettable including the change types, change time, and a sequence indicator for multiple operations that were comitted in the same transaction.

Procedure1. Select the replication task in the Workbench Editor.2. Select the Remote Object to edit.3. In the Details pane, select the Load Behavior tab.4. From the Load Behavior drop-down menu, select one of the following options:

Replicate : Replicates changes in the source one-to-one in the target.Replicate with logical delete : UPSERTS rows and includes CHANGE_TYPE and CHANGE_TIME columns in the target.Preserve all : INSERTS all rows and includes CHANGE_TYPE, CHANGE_TIME, and CHANGE_SEQUENCE columns in the target.

5. (Optional) You can rename the column names.6. Save the replication task.

ExampleConsider the following changes made to the LineItem table for sales order 100:

The target tables would display as follows.Replication Table :

Column DescriptionCHANGE_TYPE Displays the type of row change in the source:

I INSERTU UPDATED DELETEA UPSERTR REPLACET TRUNCATEX EXTERMINATE_ROW

CHANGE_TIME Displays the time stamp of when the row was committed. All changes committed within the same transaction will have the sameCHANGE_TIME.

CHANGE_SEQUENCE Displays a value that indicates the order of operations for changes that were committed in the same transaction.

Operation Time stamp DescriptionInsert 08:01 Add new line item 3 worth $60Insert 08:02 Add new line item 4 worth $40Delete 08:02 Delete line item 1Commit 08:03 Save the changes to the order


Page 5 of 31

Actuals Table :

Change Log Table :

1.1.6 Activate and Execute a Replication TaskActivation generates the run time objects necessary for data movement from one or many source tables to one or more target tables.

ContextThe replication task creates the following run time objects.

Virtual table(s): Generated in the specified virtual table schema. You can display the contents of the virtual table in SAP HANA studio.Remote subscription(s): Generated in the schema selected for the virtual table. This is only generated when the Initial load only option is not selected.Task(s): Generated in the same schema as the target table.View(s): Generated in the same schema as the virtual table.Target table(s): Populated with the content after execution.Procedure: Generated in the schema of the target table, the procedure performs three functions.

1. Sets the remote subscription to the Queue status.

NoteThe remote subscription is only created when Initial load only is unselected.

2. Calls Start Task to perform the initial load of the data.3. Sets the remote subscription to the Distribute status. Any changes, additions or deletions made to the source data during the initial load are updated in

the target system. Any changes to the source data thereafter are updated real time to the target.

NoteThe remote subscription is only created when Initial load only is unselected.

Procedure1. After the replication task is configured, click Save to activate.2. Go to the Catalog view and navigate to the stored procedure you just created.

NoteYou can access the Catalog view on the SAP HANA XS Web server at the following URLhttp:// :80 /sap/hana/xs/ide/catalog. Choose one of the following options to activate thereplication task.

Right-click the stored procedure, and then select Invoke Procedure .To call the stored procedure, use the following SQL script:CALL " "." :: ".START_REPLICATION

.The replication begins. You can right-click and select Open Contents to view the data in the target table in the Catalog view.

NoteIf the replication task takes longer than 300 seconds to process, you might receive an error about the XMLHttpRequest failing. You can correct thisissue by increasing the maximum run time option in the xsengin.ini file. Follow these steps:

Order Line Material Amount100 2 Bolt 200100 3 Nut 60100 4 Spacer 40

Order Line Material Amount CHANGE_TYPE CHANGE_TIME100 1 Screw 200 D 2015-04-23 08:04100 2 Bolt 200 I 2015-04-23 08:04100 3 Nut 60 I 2015-04-23 08:04100 4 Spacer 40 I 2015-04-23 08:04

Order Line Material Amount CHANGE_TYPE CHANGE_TIME CHANGE_SEQUENCE

100 1 Screw 200 I 2015-04-23 07:40 23100 2 Bolt 200 I 2015-04-23 07:40 24100 3 Nut 60 I 2015-04-23 08:04 50100 4 Spacer 40 I 2015-04-23 08:04 51100 1 Screw 200 D 2015-04-23 08:04 52


Page 6 of 31

issue by increasing the maximum run time option in the xsengin.ini file. Follow these steps:1. Login to SAP HANA studio as a SYSTEM user.2. In the Systems view, right-click the name of your SAP HANA server, and then choose Configuration and Monitoring Open Administration .3. Click the Configuration tab.4. Select xsengine.ini.5. Expand httpserver .6. Click Add parameter .7. In the Assign Values to option, select System , and then Next .8. In the Key option, enter max_request_runtime and then enter a value. For example, you might want to enter 1200. The value is in seconds.9. Click Finish and then close the Configuration tab and execute the replication task again.

ResultsYou can use SAP HANA Cockpit to monitor the results.

Related InformationSAP HANA SQL and System Views Reference (PDF)SAP HANA SQL and System Views Reference (HTML)

1.2 Transforming Data Using SAP HANA Web-basedDevelopment WorkbenchUse SAP Hana Web-based Development Workbench for replicating and transforming data.Use this tool to develop applications in a Web browser without having to install any development tools. This is a quick alternative to using SAP HANA Studio'sapplication function modeler. It simplifies development by providing many convenient functions such as replicating data into HANA, and transforming that data sothat you are using the records and tables necessary for your business. When creating a flowgraph with the nodes in the General palette, you will create aprocedure that you can call after activation.

1.2.1 Add a Variable to the Container NodeCreate variables to simplify the process of activating a flowgraph.

ContextWhen you create variable, you can use them in nodes that accept them such as the Filter and Join nodes. For example, in a Filter node, you might want toprocess only those records for a certain country, such as Spain. You can create a variable for each country in the Container Node. Then you can call the variablein the filter by surrounding the variable name with $$. For example,"COUNTRY" = $$Spain$$

Procedure1. Select the container node. This is the canvas area that holds the nodes that you drag onto it. For more information about the container node, see the SAP

HANA Developer Guide.2. In the Properties tab, click Variables .3. Click Add .4. Enter values for the variable.

ResultsThen when you activate the flowgraph, you can specify the output by calling the variable(s) in the function. For example,START TASK " "." :: " (country => '''US''', state => '''NY''');

Option DescriptionName The name of the variable. For example, "Florida". When using the variable in other nodes, surround the variable name with two

dollar signs. For example, in the Filter node when you output Florida data, you would use"STATE" = $$Florida$$

Kind Select one of the following options.expression : Use in nodes where the expression editor is located. This includes filters and attribute values.scalarParam : Use with scalar parameters such as R script procedures. There must be one scalarParam for each variable in this

Variables tab.Type The type of data contained in the column, for example, Nvarchar, Decimal, Date, and so on. Required when using scalarParam.Length The number of characters allowed in the column. Required when using scalarParam.Scale The number of digits to the right of the decimal point. This is used when the data type is a decimal. Required when using

scalarParam.Nullable Indicates whether the column can be null.Default Enter a value to use when the criteria is not met in the node. For example, when using the Filtering node to look for customers in

Germany, and you might set the default to Berlin if the country is not specified.


Page 7 of 31


1.2.2 Activate and Execute a FlowgraphAfter your flowgraph is created and configured, activate it to create the run-time objects.

ContextActivation creates the run-time objects based on the options set in the flowgraph.

Procedure1. From the Project Explorer, right-click on the .hdbflowgraph that you created.2. Choose Team Activate .

The run time objects are created.3. Choose one of the following:

If you configured the flowgraph for initial load only, use the following SQL to run the generated task:START TASK " "." :: "

NoteYou can also specify a variable when running Start Task. For example, if you have a Filter node set to output records for a specific country, youcan enter it in a similar way to the following.

START TASK " "." :: " (country => '''Spain'''); If you configured the flowgraph for real time, use the following SQL script to execute the generated initialization procedure:CALL " :: _SP"

If you configured the flowgraph for real time and want to pass a variable value, use the following script to execute the generated initialization procedure:CALL " :: _SP"(""'Spain'"")

For more information about Start Task and calling a table type, see the Start Task topic.


1.3 Transforming Data Using SAP HANA Application FunctionModelerOverview of SAP HANA application function modeler.The SAP HANA application function modeler is the default editor for flowgraphs. A flowgraph is a development object. It is stored in a project and has extension.hdbflowgraph. By default, the activation of a flowgraph generates a procedure in the catalog.

NoteIf the optional additional cost Enterprise Information Management component is available, a flowgraph can be configured to generate a task plan run-timeobject instead of a procedure.

A flowgraph models a data flow that can contain:tables, views, and procedures from the catalogrelational operators such as projection, filter, union, and joinfunctions from Application Function Libraries (AFL) installed on your systemattribute view and calculation view development objects

In addition the application function modeler provides support for some optional, additional cost components of the SAP HANA Platform such asthe Business Function Librarythe Predictive Analysis LibraryR ScriptsData Provisioning operatorsthe generation of task plans

The application function modeler is part of the SAP HANA Development perspective and utilizes the following components.


Page 8 of 31

Table 1: Components used by the SAP HANA application function modeler

TipYou can open the SAP HANA Development perspective by choosing Window Open Perspective SAP HANA Development , the Propertiesview by choosing Window Show View Properties , and the Project Explorer views by choosing Window Show View Project Explorer .

Related InformationThe SAP HANA Development PerspectiveThe Project Explorer ViewSAP HANA ProjectsAttribute ViewsCalculation ViewsAdd a Variable to the Container NodeSAP HANA Business Function Library (BFL)SAP HANA Predictive Analysis Library (PAL)SAP HANA R Integration Guide

1.3.1 Converting deprecated AFL Models (AFLPMML objects)Convert a deprecated AFL Model development object that was created by a previous version of the SAP HANA application function modeler into a flowgraph.

ContextAFL Models are development objects with the extension .aflpmml that were created with a previous version of the SAP HANA application function modeler.They are deprecated in SAP HANA SPS09.Compared to the complex data flows with various operators modeled by a flowgraph, an AFL Model object is restricted to model a single function from theApplication Function Library together with the data sources and data sinks that are connected to this function.An AFL Model can still be activated. However, since AFL Models are deprecated, it can no longer be directly edited with the application function modeler. Instead,the AFL Model first has to be converted to a flowgraph. Then this flowgraph can be edited with the application function modeler. For backward compatibility, theedited flowgraph can be re-converted to an AFL Model. This requires all changes to the flowgraph to be compatible with the restrictions of AFL Models.

Procedure1. In the Project Explorer view right-click on the AFL Model that you want to convert to a flowgraph, and then choose Convert to Flowgraph in the context-

sensitive menu.The application function modeler creates a new flowgraph with the same prefix and the .hdbflowgraph extension. A dialog appears that lets you deletethe AFL Model and its corresponding generated procedure. Afterward, you can edit the new flowgraph with the application function modeler.

NoteIf you choose not to delete the converted application function modeler Model and try to activate a flowgraph, you get an error stating that there alreadyexists an active catalog object with the same name (the new object tries to generate the same runtime object). You need to either delete or rename oneof the two objects and activate the modification as well.

Area Description1 Project Explorer view The Project Explorer is used as a source of objects that can be added to the Editing Area .2 Editing Area In the Editing Area , the flowgraph is modeled. Elements are added to the flowgraph by dragging objects from the

Project Explorer or node templates from the Node Palette to the Editing Area . There, they can be selected andedited via the context button pad and the context menu. The Editing Area supports standard editing operations likecopy, paste, and delete, as well as moving elements by drag and drop. The properties of selected flowgraph elementscan be edited in the Properties view.

3 Node Palette The Node Palette lists the node templates available to the application function modeler. These node templates canbe added to the flowgraph by dragging them to the Editing Area . In case an optional, additional cost component of theSAP HANA Platform is detected by the application function modeler, an additional compartment with node templatesfor its functions is automatically added to the Node Palette .

4 Properties view The Properties view shows the property details of the selected flowgraph element.


Page 9 of 31

NoteA flowgraph cannot be activated on a SAP HANA SPS08 system.

2. (Optional) Convert a flowgraph to a AFL Model. In the Project Explorer view right-click on the flowgraph that you want to convert to an AFL Model, and thenchoose Convert to AFLPMML in the context-sensitive menu.The application function modeler creates a new AFL Model with the same prefix and the .aflpmml extension.

NoteAFL Model objects are deprecated. This conversion is available for backward compatibility. Most features of a flowgraph are not supported by theAFLPMML format.

1.3.2 Setting up the SAP HANA Application Function ModelerConfigure your system to use the SAP HANA Application Function Modeler.Before modeling flowgraphs with the SAP HANA Application Function Modeler (AFM), make sure that the following system requirements are satisfied and that thefollowing database access rights are granted to the respective database users.

System RequirementsThe AFM has the following system requirements.

You have installed SAP HANA SPS09.You have installed the Application Function Libraries (AFLs) that you want to use. For more information, see the section Installing or Updating SAP HANAComponents in the SAP HANA Server Installation and Update Guide.You have enabled the Script Server in your SAP HANA instance. See SAP Note 1650957 for more information.

Privileges for the database user _SYS_REPOThe database user _SYS_REPO has to be granted the following object privileges:

SELECT object privileges for objects that are used as data sources,INSERT object privileges for objects that are used as data sinks,INSERT and DELETE object privileges for objects that are used as data sinks with truncation.

NoteGranting access rights to the user _SYS_REPO may constitute a security risk. Make sure that you understand the privileges you grant to database users.Also see the SAP HANA Security Guide.

Privileges for the database user of the AFMYou have to be granted the MODELING role.You have to be granted the EXECUTE privilege for the object SYS.REPOSITORY_REST.You have to be granted the following package privileges:

repo.read package privileges on your repository packagerepo.activate_native_objects package privileges on your repository packagerepo.edit_native_objects package privileges on your repository packagerepo.maintain_native_packages package privileges on your repository package

In addition, you have to be granted the following object privileges to the target schema of the flowgraph activation (default: _SYS_BIC):CREATE ANYALTERDROPEXECUTESELECTINSERTUPDATE

NoteGranting access rights to the user _SYS_REPO may constitute a security risk. Make sure that you understand the privileges you grant to database users.Also see the SAP HANA Security Guide.

Related InformationSAP HANA Server Installation and Update GuideSAP HANA Security Guide

1.3.3 FlowgraphsThis is an overview of all flowgraph elements.A flowgraph consists of several flowgraph elements that are depicted in the Editing Area . Every flowgraph element has a collection of properties that aredisplayed in the Properties view.


Page 10 of 31

Table 1: Flowgraph Elements

ValidationThere is a validation decorator in the right bottom corner of the flowgraph container and of each node. This decorator indicates if the complete flowgraph or therespective node is configured correctly. The details of a validation error are displayed by mouse-over on the validation decorator and in the Problems view.

TipYou can open the Problems view by choosing Window Show View Problems in the main menu of the HANA Studio.

CautionA flowgraph with validation errors will fail to activate.

AnnotationsAnnotations are nested key-value pairs that can be added to the flowgraph container and to nodes. The AFM uses annotations to store certain properties of theflowgraph such as custom palette information. An AFM user can store arbitrary meta data in the annotations. When the flowgraph is activated, all annotations areexposed in a table with the name extension .META in the flowgraph target schema. This way, they can be consumed at runtime.There are two main reasons for the user of the AFM to create annotations. The first reason is to add comments and documentation to the flowgraph. The secondreason is to pass meta data about the flowgraph and its nodes to an application consuming the runtime procedure generated by the activation. In this case theapplication has to be specifically designed to process the meta data. Although this is a rather specific and uncommon use-case, it is a very versatile approachthat utilizes flowgraphs to configure the analytic functionality of an application.

Related Information

Element Description1 Flowgraph container The flowgraph container represents the operator defined by the flowgraph. Every flowgraph has exactly one flowgraph

container. This flowgraph container has a name which has to differ from all other elements of the flowgraph. Theflowgraph container can have several anchors. They represent the inputs and outputs of the operator defined by theflowgraph. The central free area of the flowgraph container is its canvas. All nodes of the flowgraph are contained inthis canvas. The validation decorator in the right bottom corner of the flowgraph container indicates whether theflowgraph is configured correctly.

2 Node Nodes are the functional elements in a flowgraph. There are several different types of nodes which represent datasources, data sinks, and operators. A node has a name which has to be unique in the flowgraph. Like the flowgraphcontainer, a node can have several anchors. They represent the inputs and outputs of the node. The validationdecorator in the right bottom corner of a node indicates whether the node is configured correctly.

3 Anchor An anchor represents an input or an output of the flowgraph container or of a node. Every anchor has a kind and asignature which define the input or output it represents. For input anchors, the supported kinds are Table, Column,and Scalar. For output anchors, the only supported kind is Table. Anchors of the Column kind are considered to betables with a single column. Anchors of the Scalar kind are considered to be tables with a single column and a singlerow. This way, every anchor defines the table type of the input or output it represents.

4 Fixed content anchor A fixed content anchor is an input anchor for which the fixed content flag is set in the properties. It is displayed in whitecolor (in contrast to the light-blue colored standard anchors). A fixed content anchor cannot be the target of a connection.Instead, there is a table embedded in the flowgraph that is associated to the fixed content anchor. The table is displayedin the Fixed Content tab of the Properties view of the anchor.

5 Anchor region The flowgraph container and some nodes (for example, the Join node and the Union node) can have a variablenumber of input and output anchors. This is represented by an anchor region for the corresponding set of anchors.Anchors can be added to or removed from the anchor region. They can also be reordered in the anchor region.

6 Connection Connections represent the directed flow of data from a source to a target. The source and the target of a connection areanchors. The connection defines a table mapping between the table types defined by its source and target. The sourceof a connection is either an input anchor of the flowgraph container or an output anchor of a node. The target of aconnection is either an output anchor of the flowgraph container or an input anchor of a node. An anchor can be thesource of several connections. It can be the target of only one connection. A fixed content anchor cannot be the target ofa connection.


Page 11 of 31

Related InformationUsing the Mapping EditorUsing the Annotation EditorCustomizing the Node Palette

1.3.4 Modeling a flowgraphModel a flowgraph starting with its creation and concluding with the execution of the generated procedure.

ContextThe SAP HANA Application Modeler (AFM) supports standard graphical editing operations like move, copy, paste, and delete on the elements of a flowgraph.Detailed properties of these elements are edited in the Properties view. After editing and saving a flowgraph, it can be activated by the AFM and the generatedprocedure can then be executed via the AFM.If the flowgraph container has input anchors, the procedure has corresponding free inputs. It then cannot be executed directly. In this case, data sources have tobe bound to the free inputs in order to execute the runtime object. The AFM provides a wizard for this.

Procedure1. Create a new flowgraph or open an existing flowgraph in the Project Explorer view.

The flowgraph is opened in the Editing Area of the AFM.2. Edit the flowgraph container.3. Add and edit nodes.4. Add and edit anchors.5. Add and edit connections.

The validation decorators in the bottom right corners of the flowgraph container and the nodes indicate whether the flowgraph is valid.

NoteA flowgraph must be valid to be activated.

6. Save the flowgraph. Select File Save in the HANA Studio main menu.7. Activate the flowgraph. In the Project Explorer view, right-click the flowgraph object and choose Team Activate... from the context-sensitive menu.

A new procedure is generated in the target schema which is specified in the properties of the flowgraph container.

NoteThe generated procedure has inputs that correspond to the input anchors of the flowgraph container. To activate this procedure, these inputs have to bespecified.

8. Select the black downward triangle next to the Execute button in the top right corner of the AFM.A context menu appears. It shows the options Execute in SQL Editor and Open in SQL Editor as well as the option Execute and Explore for everyoutput of the flowgraph. In addition, the context menu shows the option Edit Input Bindings .

9. (Optional) If the flowgraph has input anchors, choose the option Edit Input Bindings .A wizard appears that allows you to bind all inputs of the flowgraph to data sources in the catalog.

10. Choose one of the options Execute in SQL Editor , Open in SQL Editor , or Execute and Explore for one of the outputs of the flowgraph.The behavior of the AFM depends on the execution mode.

11. Close the flowgraph. Select File Close in the HANA Studio main menu.

1.3.4.1 Creating a FlowgraphYou can use the SAP HANA AFM to create procedures using PAL functions. The first step is to create a Flowgraph (.hdbflowgraph) file.

Procedure1. In the SAP HANA Development perspective, open your existing SAP HANA project.2. In the Project Explorer view, right-click on the project name, and choose New Other .

The New wizard will appear.3. From the Wizard list, expand SAP HANA and then Database Development , select Flowgraph Model , and choose Next .4. Enter or select the parent folder, and enter the file name.

NoteYou only need to enter the base name. The system automatically adds the extension .hdbflowgraph to it.

5. Choose Finish .The new .hdbflowgraph file will appear in the Project Explorer view with the icon.

Execution mode BehaviorOpen in SQL Editor Opens a SQL console containing the SQL code to execute the runtime object.Execute in SQL Editor Opens a SQL console containing the SQL code to execute the runtime object and runs this SQL code.Execute and Explore Executes the runtime object and opens the Data Explorer view for the chosen output of the flowgraph.


Page 12 of 31

NoteIf your workspace was created in SAP HANA SPS05 or earlier, you will also see a .diagram file and a .aflmodel file. Opening these two files maycause errors. To avoid it, you need to manually activate the Diagram-aflmodel filter as follows:1. In the Project Explorer view, click the drop-down arrow (View Menu) in the upper-right corner and choose Customize View .2. In the Available Customizations dialog box, select Diagram-aflmodel Filter and choose OK .

6. If your project has not yet been shared, right-click on the project name, select Team Share Project . Make settings in the Share Project wizardand choose Finish .

1.3.4.2 Editing the flowgraph containerEdit the properties of the flowgraph in the Properties view of the flowgraph container.

ContextTable 1: Tabs in the Properties view of the flowgraph container.

Procedure1. Select the flowgraph container and open the Properties view.2. In the General tab, specify the name of the flowgraph container, as well as the target schema, and the generator of the flowgraph.

NoteThe name of the flowgraph container is initially auto-generated from the name of the flowgraph object in the Project Explorer view. This name has to bechanged if it does not adhere to the naming rules for the flowgraph elements. Names of flowgraph elements may contain only upper-case letters,underscores, and digits and must be unique in the flowgraph.

NoteYou need to be granted the CREATE ANY, ALTER, DROP, EXECUTE, SELECT, INSERT, and UPDATE privileges to the target schema of theflowgraph.

3. In the Mappings tab, use the Mapping Editor to remove or reorder input and output anchors and their attributes.4. In the Annotations tab, use the Annotations Editor to edit the annotations of the flowgraph container.

ResultsThe settings made on the flowgraph container determine the type of runtime object generated during activation and the number and signatures of its inputs andoutputs.

Related InformationFlowgraphsUsing the Mapping EditorUsing the Annotation EditorCustomizing the Node PaletteAdd a Variable to the Container Node

Tab name Description OptionalGeneral This tab contains the following entries:

Name : name of the flowgraph container,Display Name ; not used,Description : not used,Target Schema : schema in which the runtime object is generated during activation (default:

_SYS_BIC),Generator : the type of runtime object to be generated during activation. The option Task is

only relevant if the flowgraph uses the additional cost Enterprise Information Managementoptional component,Realtime Behavior : This option is only relevant when the flowgraph uses the additional cost

Enterprise Information Management optional component and the chosen Generator option isTask

No

Variables This tab is relevant only when the flowgraph uses the Enterprise Information Management optionalcomponent. For more information see the "Adding a Variable to the Container Node" topic in the SAPHANA Enterprise Information Management Configuration Guide.

Yes

Mappings The Mapping Editor in this tab is used to remove or re-order input and output anchors and theirattributes.

Yes

INPUT (I) / OUTPUT (O) These tabs correspond to the input and output anchors of the flowgraph container. They have the samenames as the respective anchors and the same contents as the All tabs in the Properties views ofthe anchors.

Yes

Annotations This tab contains the annotations of the flowgraph container. NoAll This tab is a summary of all tabs in this view except for the input and output anchor tabs. No


Page 13 of 31

1.3.4.3 Adding an object from the Project ExplorerDrag and drop an object from the Project Explorer view to the Editing Area .

PrerequisitesYou have opened a flowgraph in a project that has been shared with a HANA system.

ContextNodes are the functional elements in a flowgraph. There are several types of nodes which represent data sources, data sinks, and operators in the flowgraph.The following database objects are represented by nodes in a flowgraph.

Development objects in the project:Flowgraphs with no inputs and one output as Data Source nodesAttribute Views as Data Source nodesCalculation Views as Data Source nodes

NoteFlowgraphs that represent procedures with inputs or with more than one output cannot be directly inserted in other flowgraphs. However, it is possible toadd the procedure generated by activating one flowgraph to another flowgraph. This is done via drag and drop from the catalog (see below) or by addinga Procedure node from the Node Palette .

Runtime objects in the catalog:Tables as Data Source nodes, and as Data Sink nodesViews as Data Source nodesProcedures without scalar parameters and INOUT parameters as Procedure nodesTable Types and Tables as anchors

TipYou can also drag a Table Type or a Table to an anchor region to create a new anchor.

ProcedureIn the Project Explorer, select an object and drag it to the canvas of the flowgraph container.If the dragged object is a table, a pop-up dialog lets you choose if this table is used as a data source or a data sink in the flowgraph.A new node is added to the flowgraph. The type of the node matches the selected object in the Project Explorer. The flowgraph container is re-sized so thatthe new node is contained in the canvas of the flowgraph container.

NoteYou need to be granted SELECT access rights on the schema that contains the object.

NoteIn order to activate the flowgraph, database user _SYS_REPO needs to be granted SELECT object privileges for objects that are used as data sourcesand INSERT object privileges for objects that are used as data sinks.

CautionThe validation of the SAP HANA Application Function Modeler does not recognize when the signature of an input or output of a table or view haschanged. In this case the signature of the respective input or output of the added node is inconsistent with that of the object. Consequently, the flowgraphactivation fails.

In the Project Explorer, select a table type or a table and drag it to an anchor region.A new anchor with the same signature as the table type or the table is added to the anchor region at the position where the object was dropped.

NoteYou need to be granted SELECT access rights on the object.

NoteDragging a table to the anchor region only transfers the signature of the table to the anchor. No reference to the table or its content is stored in theflowgraph. Accordingly, no additional object privileges have to be granted to the database user _SYS_REPO.

Related InformationSetting up the SAP HANA Application Function ModelerAttribute ViewsCalculation Views

1.3.4.4 Adding a node from the Node PalettePUBLIC 2014 SAP SE or an SAP affiliate company. All rights reserved.

Page 14 of 31

Drag a node template from the Node Palette to the canvas of the flowgraph container in the Editing Area .

PrerequisitesYou have opened a flowgraph in a project that has been shared with a HANA system.

NoteThe Node Palette is generated according to the functionality provided by the system. If you work in a project that is not shared with a system or the system isoffline, the content of the Node Palette is restricted to a few basic relational operators. For example, the Data Source node and Data Sink node will bemissing the General tab.

ContextNodes are the functional elements in a flowgraph. There are several different types of nodes which represent data sources, data sinks, and operators in theflowgraph.

ProcedureIn the Node Palette, select the entry you want to add and drag it to the canvas area of the flowgraph container.

ResultsA new node is added to the flowgraph. The type of the node matches the selected node template in the Node Palette . The flowgraph container is resized such thatthe new node is contained in its canvas.

Related InformationSetting up the SAP HANA Application Function Modeler

1.3.4.5 Editing a nodeEdit the properties of a node.

ContextThe nodes in a flowgraph usually need to be configured. Relational nodes need configurations such as join conditions, filter predicates, and attribute sets forprojection. Edit the configuration of a node by selecting the node and navigating to its Properties view. The selection of tabs and the configuration options in theProperties view depend on the type of node.

Table 1: Tabs in the Properties view of a node

Procedure1. Select a node or add a new node.2. Select the name of the node.

The name field becomes active for editing.

NoteThe name of a node may contain only upper-case letters, underscores, and digits. It must be unique within the flowgraph.

Tab name Description OptionalGeneral This tab always contains the following elements:

Name : name of the node (editable),Display Name ; name of the node template entry (read-only),Description : description of the node template entry (read-only).

In addition, this tab contains most configuration options that are specific to theparticular node type.

No

Script This tab is only relevant if an optional additional cost component offers Script nodefunctionality (for example, R Integration).

Yes

Mappings If the node defines a mapping of its inputs to its outputs or contains an anchorregion, this mapping is displayed and can be edited in the Mapping Editor .

Yes

INPUT (I) / OUTPUT (O) These tabs correspond to the input and output anchors of the node. They have thesame names as the respective anchors and the same contents as the All tabs inthe Properties views of the anchors.

Yes

Annotations This tab contains the annotations of the node. NoAll This tab is a summary of all tabs in this view except for the input and output anchor

tabs.No


Page 15 of 31

3. In the Annotations tab of the Properties view, use the Annotations Editor to edit the annotations of the node.4. Edit the remaining properties of the node in the Properties view. In particular, specify the type-specific properties of the node in the General tab.

Related InformationUsing the Mapping EditorUsing the Annotation Editor

1.3.4.6 Adding an anchorAdd an anchor to an anchor region of the flowgraph container or a node.

ContextThe flowgraph container and some nodes (for example, the Join node and the Union node) can have a variable number of input or output anchors. In the flowgraph,this is represented by the existence of an anchor region for the corresponding set of anchors. New anchors can be added to the anchor region.

Procedure1. Right-click on the anchor region at the position you want to add the new anchor.2. In the context-sensitive menu, choose Add Input or Add Output (depending on whether you selected an anchor region for inputs or outputs).

ResultsA new anchor with an empty signature is added to the anchor region at the mouse pointer position where the context menu is opened.

NoteInstead of adding an anchor via the context-sensitive menu, you can also copy an existing anchor to an anchor region. This has the advantage that the newanchor has a fully defined signature.

NoteAlternatively, you can add a new anchor while creating a connection. In this case the new anchor inherits the signature from the source anchor of theconnection.

NoteA third option to add an anchor with a predefined signature is by dragging a table or a table type from the catalog to the anchor region.

NoteYou can also delete an anchor that you added to an anchor region. Some anchor regions have a minimum number of anchors (for example, the anchor regionsfor the inputs of the Join node and the Union node each have to contain at least two anchors). In this case, if the anchor region contains the minimum number ofanchors, then no anchor in the anchor can be deleted.

Related InformationAdding an object from the Project ExplorerEditing the flowgraph containerJoinUnion

1.3.4.7 Editing an anchorChange and define input and output table types.

ContextAnchors define inputs and outputs to the flowgraph container and to nodes.

Table 1: Tabs in the Properties view of an anchor.Tab name Description OptionalGeneral This tab contains the following entries:

Name : name of the anchorKind : kind of the anchor (Table, Column, Scalar).

No

Signature In this tab, you can use the Table Editor to change the signature of the anchor.Anchors of the kind Scalar or Column are considered to be tables with one column.

No

Fixed Content This tab exists only for input anchors. While the checkbox Fixed Content isselected, the anchor cannot be the target of a connection. Instead, a table providingthe input is stored in the flowgraph with the anchor. The table can be edited usingthe Table Editor in this tab.

Yes


Page 16 of 31

NoteMost anchors have a fixed kind that cannot be changed. Currently, the anchor kinds "Column" and "Scalar" are only supported for input anchors of AFLFunction nodes.

NoteMany anchors either have a fixed signature or obtain their signature via an automatic table mapping.

Procedure1. Select the anchor.2. Select the name of the anchor and edit it in the direct editing area.

The name field becomes active for editing.

NoteThe name of an anchor must consist of upper-case letters, underscores, and digits. It must be unique in the flowgraph.

3. Use the Table Editor to edit the signature of the anchor in the Signature tab of the Properties view.4. Select the Fixed Content tab in the Properties view.5. If you want to embed the content of the anchor with the flowgraph, select the checkbox Fixed Content .

If the checkbox Fixed Content is selected, the embedded table is shown in the Fixed Content tab. Use the Table Editor to edit the table.

NoteFor some areas of the Application Function Library the SAP HANA application function modeler provides template AFL Function nodes in separatecompartments of the Node Palette . These template nodes are preconfigured with fixed signature tables if the respective input is a design-timeparameter of the node.

Related InformationFlowgraphsUsing the Table EditorUsing the Mapping Editor

1.3.4.8 Creating a connectionCreate a new connection between two nodes or a node and the flowgraph container.

ContextA connection represents the directed flow of data from a source to a target. The source and the target of a connection are anchors. The connection defines a tablemapping between these table types defined by its source and target. The source of a connection is either an input anchor of the flowgraph container or an outputanchor of a node. The target of a connection is either an output anchor of the flowgraph container or an input anchor of a node. An anchor can be the source ofseveral connections. It can only be the target of one connection. A fixed content anchor cannot be the target of any connection.

Procedure1. Select without releasing the Connect button in the context button pad of the source anchor of the connection.2. Drag a connection to the target anchor.

NoteDepending on the node of the target anchor, the Create Input Table Mapping wizard may open. This wizard helps you to choose the right mapping forthe connection. You can still change this mapping in the Mapping Editor after completing the wizard. To open the wizard again, you have to remove theconnection and create it again.

ResultsA new connection between the source anchor and the target anchor is created. If possible, the signature of the source anchor is copied to the target anchor andpropagated forward through the flowgraph.

NoteYou can also add a new anchor to an anchor region and create a connection to this anchor in a single action. Instead of dragging the connection to an anchor,drag it to a free position in an anchor region. A new target anchor with the same signature as the source anchor is added before the connection is created.

Related Information

All This tab is a summary the other tabs. No


Page 17 of 31

Adding an anchor

1.3.4.9 Using the Table EditorEdit embedded tabled like anchor signatures and fixed content tables.

ContextEmbedded tables appear in various flowgraph elements. For example, anchors have signature tables and may have fixed content tables. Specialized nodes mayhave tables in the General tab of the Properties view. The SAP HANA Application Function Modeler provides a Table Editor to edit these tables.

ProcedureAdd, remove, and re-order rows of the embedded table by selecting the respective operations on the right side of the Table Editor .Edit an entry in the table by double-clicking the respective cell.

Related InformationFlowgraphsEditing an anchor

1.3.4.10 Using the Mapping EditorEdit the mappings between table types in the Mappings tab of the Properties view of a flowgraph element.

PrerequisitesYou have selected the Mappings tab of the Properties view of a flowgraph element.

ContextA mapping is a projection between table types. The Mapping Editor allows you to edit mappings between a number of source and target table types. The leftside of the editor shows the source table types, the right side shows the target table types. A binding of two attributes is indicated by a line between them.

NoteThe mapping editor is used to define the mappings of connections and possible projections within nodes (for example, the Filter node, the Join Node, and theUnion Node). It is also used to edit this inputs and outputs of the flowgraph container and of nodes which do not define a projection. In this case, no lines aredrawn between the attributes.

NoteNot all flowgraph elements allow free editing of all their mappings and table types. In this case the functionality of the Mapping Editor is restricted to thepermitted editing operations.

Procedure(Optional) To remove a table type, select it and press the minus sign on the right side of the Mapping Editor .To re-order the source or target table types, click on a table type and use the up/down arrows on the right side of the Mapping Editor .(Optional) To remove an attribute, select it and press the minus sign on the right side of the Mapping Editor .(Optional) To re-order the source or target attributes, click on an attribute and use the up/down arrows on the right side of the Mapping Editor .(Optional) To add an attribute from the source type to the target type, drag the source attribute and drop it on the root of the target tree.The attribute is appended at the end of the target attribute list. If the Mapping Editor defines a mapping, it is connected by a line with the source attributeindicating an attribute binding.(Optional, only available if the Mapping Editor defines a mapping) To re-assign a source attribute to a target attribute that is already assigned, drag thesource attribute to the target attribute.The old binding is replaced by the new one.

Related InformationFlowgraphsEditing the flowgraph containerEditing a nodeEditing a connection

1.3.4.11 Using the Expression EditorCompose expressions for filters, join conditions, and calculated attributes.


Page 18 of 31

ContextThe Expression Editor allows you to compose SQL expressions based on table type attributes and functions. It consists of an Function Palette on the top, anAttribute Palette on the left and a Text Field on the right.

NoteThe expression validation is disabled in the SAP HANA Application Function Modeler.

ProcedureType the expression in the Text Field.

NotePress CTRL + Space bar for auto-completion.

Select operators and functions in the Function Palette to add them to the Text Field .Drag attributes from the Attribute Palette to the Text Field .

Related InformationAggregationFilterJoin

1.3.4.12 Using the Annotation EditorAdd arbitrary annotations to the flowgraph container or a node.

ContextThe flowgraph container and all nodes have an Annotation tab in their Properties view. Annotations are nested key-value pairs. The SAP HANA ApplicationFunction Modeler (AFM) provides an Annotation Editor to edit existing annotations like the sap.afm.palette annotation or to add your own annotations.

NoteWhen the flowgraph is activated, all annotations are exposed in a table with the name extension .META in the flowgraph target schema. This way, they can beconsumed at runtime.

NoteFor some nodes, the annotations sap.afm.displayName and sap.afm.description are visible in the Annotation Editor . These annotations are forinternal use of the AFM and not supposed to be modified.

ProcedureAdd, remove, and re-order annotations by selecting the respective operations on the right side of the Annotation Editor .Edit the Key and the Value of an annotation by double-clicking the respective cell.Add nested annotations by first selecting an annotation row and then the Add Child operation on the right side of the Annotation Editor .A nested annotation appears below the selected annotation.Collapse and expand nested annotations by selecting the triangle to the left of an annotation key.

Related InformationFlowgraphsCustomizing the Node Palette

1.3.5 Tutorial

PrerequisitesYou have access to a running SAP HANA development system.You have a valid user account in the SAP HANA database on that system.Your user has been granted the MODELING role.Your user has been granted the EXECUTE privilege for the object SYS.REPOSITORY_REST.Your user has been granted the following repository package privileges:

repo.readrepo.activate_native_objectsrepo.edit_native_objectsrepo.maintain_native_packages

The system user _SYS_REPO has SELECT and ALTER privileges on the schema of your user.You have access to SAP HANA Studio and opened the SAP HANA Development perspective.


Page 19 of 31

You have created a system in the System view in the and logged on to this system with your user.You have created a repository workspace for the system.You have created a project in the Project Explorer view and shared it with the system via the workspace.

TipTo share a project, right-click on the project and choose Team Share Project in the context-sensitive menu. In the Share Project wizard,choose SAP HANA Repository on the first page and choose your repository workspace on the second page.

ContextThis tutorial leads you through the most common steps of using the SAP HANA Application Function Modeler (AFM). At the end of this tutorial, you will havecreated and tested a runtime procedure with the AFM.

Procedure1. Open the SQL console of the system and create the table type WEATHER and the two tables NORTH and SOUTH in your user's schema by executing the

following script.CREATE TYPE "WEATHER" AS TABLE ("REGION" VARCHAR(50), "SEASON" VARCHAR(50), "TEMPERATURE" INTEGER);

CREATE COLUMN TABLE "NORTH" LIKE "WEATHER";

INSERT INTO "NORTH" VALUES ('North', 'Spring', 10);INSERT INTO "NORTH" VALUES ('North', 'Summer', 23);INSERT INTO "NORTH" VALUES ('North', 'Autumn', 12);INSERT INTO "NORTH" VALUES ('North', 'Winter', 2);

CREATE COLUMN TABLE "SOUTH" LIKE "WEATHER";

INSERT INTO "SOUTH" VALUES ('South', 'Spring', 18);INSERT INTO "SOUTH" VALUES ('South', 'Summer', 34);INSERT INTO "SOUTH" VALUES ('South', 'Autumn', 23);INSERT INTO "SOUTH" VALUES ('South', 'Winter', 12);

After refreshing the catalog, the table type WEATHER with the three attributes REGION, SEASON, and TEMPERATURE appears in the directory Procedures Table Types of your user's schema. The two tables NORTH and SOUTH with the same signature appear in the directory Tables youruser's schema.Table 1: NORTH

Table 2: SOUTH

2. In the Project Explorer view, right-click on the existing project and choose New Other in the context-sensitive menu.The New wizard appears.

3. Choose SAP HANA Database Development Flowgraph Model , and then click Next .The New Flowgraph Model wizard appears.

4. In the text field File Name enter avg_temp as name of the new flowgraph and select Finish .The system automatically adds the file extension .hdbflowgraph. The AFM opens and in the Editing Area the empty flowgraph container is displayed.

5. Select the flowgraph container and enter the schema of your user to the Target Schema field in the Properties view.6. Add the table NORTH from the Node Palette to the flowgraph. For this, drag the Data Source entry from the General tab of the Node Palette (located on

the right side of the AFM) to the flowgraph (choose any free space inside the canvas of the flowgraph container). Choose the table NORTH from the schemaof your user in the dialog that appears.The node NORTH is added to the flowgraph.

7. Add the table SOUTH from the catalog to the flowgraph. For this, navigate in the catalog to the directory Tables in your schema (either in the ProjectExplorer view or in the Systems view). Drag the table SOUTH from the catalog to the flowgraph (place it below the NORTH node). Choose Data Sourcein the dialog that appears.The node SOUTH is added to the flowgraph.

8. Add a Union node to the flowgraph. For this, drag the Union entry from the General tab of the Node Palette to the flowgraph (place it right of the other twonodes).The node UNION is added to the flowgraph.

9. Create a connection between the DATA anchor of the NORTH node and the INPUT1 anchor of the UNION node. Click the Connect button in the contextbutton pad of the DATA anchor and drag a connection to the INPUT1 anchor.A connection between the NORTH node and the UNION node is created.

10. Create a second connection between the DATA_2 anchor of the SOUTH node and the INPUT2 anchor of the UNION node.

REGION SEASON TEMPERATURENorth Spring 10North Summer 23North Autumn 12North Winter 2

REGION SEASON TEMPERATURESouth Spring 18South Summer 34South Autumn 23South Winter 12


Page 20 of 31

A connection between the SOUTH node and the UNION node is created.11. Create a connection between the OUTPUT anchor of the UNION node and the output anchor region of the flowgraph container (the light-blue area at its right

boundary).The output anchor OUTPUT_2 is added to the output anchor region of the flowgraph container and a connection between the UNION node and the newanchor is created.

12. Save the flowgraph. Select File Save in the HANA Studio main menu.13. Activate the flowgraph. For this, right-click the flowgraph object in the Project Explorer view and choose Team Activate from the context-sensitive

menu.A new procedure is generated in the schema of your user.

CautionIf the system user _SYS_REPO does not have SELECT and ALTER privileges then the activation fails.

14. Execute the generated procedure. For this, select the Execute button in the top right corner of the AFM.The Data Preview view opens. It contains a tab with the SQL command that calls the generated procedure (with no input and one output) and a tab withthe result of the procedure. This result is the union of the tables NORTH and SOUTH.

15. Return to the AFM view for the avg_temp flowgraph.16. Add an Aggregation node from the General compartment of the Node Palette to the flowgraph (place it right of the UNION node).17. The node AGGREGATION is added to the flowgraph.18. Connect the OUTPUT anchor of the UNION node with the INPUT anchor of the AGGREGATION node.

The Mapping Editor for the connection is shown in the Properties view.19. In the Target area of the Mapping Editor for the new connection, select the attribute SEASON of the target INPUT. Remove this attribute by clicking the

Remove button on the right side of the Mapping Editor .The attribute SEASON and the corresponding mapping are deleted.

20. Select the AGGRAGATION node. In the General tab of its Properties view double-click the action of the attribute TEMPERATURE and change it to thevalue AVG.

21. Create a connection between the OUTPUT_3 anchor of the AGGREGATION node and the output anchor region of the flowgraph container.The output anchor OUTPUT_4 is added to the output anchor region of the flowgraph container and a connection between the AGGREGATION node and thenew anchor is created.

22. Save and activate the flowgraph. Execute the generated procedure.The Data Preview view opens again. It contains a tab with the SQL command that calls the generated procedure (with no input and two outputs) and twotabs with the results of the procedure. One result is still the union of the tables NORTH and SOUTH. The other result shows in two rows the averagetemperatures for the regions North and South.

23. Return to the AFM view for the avg_temp flowgraph.24. Delete the OUTPUT_2 anchor of the flowgraph container by choosing Delete in its context menu (or the respective button in the context button pad).25. Save and activate the flowgraph. Execute the generated procedure.

The Data Preview view opens again. It contains a tab with the SQL command that calls the generated procedure (with no input and one output) and asecond result tab that again shows in two rows the average temperatures for the regions North and South.

26. Return to the AFM view for the avg_temp flowgraph.27. Delete the SOUTH node from the flowgraph.

The SOUTH node and its connection to the UNION node is deleted.28. Create an additional input anchor for the flowgraph by adding the table type WEATHER from the catalog. For this, navigate to the directory Procedures

Table Types in the catalog and drag the entry WEATHER to the input anchor region of the flowgraph container.The input anchor DATA_2 is added to the flowgraph.

29. Create a connection between the new DATA_2 anchor and the INPUT_2 anchor of the UNION node.A new connection between the DATA_2 anchor and the UNION node is created.

30. Save and activate the flowgraph. Execute the generated procedure.A dialog appears where you can choose the free input DATA_2. Enter the table SOUTH in your user's schema to the Catalog Object field. The DataPreview view opens. Again, it contains a tab with the SQL command that calls the generated procedure (with one input and one output) and a second resulttab that shows in two rows the average temperatures for the regions North and South.

31. Close the flowgraph. Select File Close in the HANA Studio main menu.

ResultsYou have created a stored procedure that has one input table of the table type WEATHER and one output table that is produced by first forming the union of thetable NORTH with the input table and then calculating the average temperature of each season. This procedure can now be used in any application thatconsumes stored procedures.

Related InformationModeling a flowgraphTutorial: Add an SAP HANA System

1.3.6 Node palette flowgraphsA node palette flowgraph represents a node palette or a node palette compartment.The Node Palette of the SAP HANA application function modeler is customizable. A custom node palette is represented by a node palette flowgraph. Theseflowgraphs have the file extension .hdbflowgraphtemplate in the Project Explorer view.A node palette flowgraph contains Palette Container and template nodes. These represent the compartments or sub-compartments and the node templates of thecorresponding node palette. The Palette Container nodes and template nodes have a nested structure. This structure represents the hierarchy of the correspondingnode palette. Moreover, all nodes in a node palette flowgraph are aligned on a horizontal line. Their order (from left to right) represents the order of the node paletteentries (from top to bottom).The Node Palette hierarchy can have up to three levels.

1. The first level contains the compartments (for example, the General compartment of the application function modeler Node Palette ). Nodes are notpermitted on this level.


Page 21 of 31

2. The second level contains nodes (for example, the Filter node) and sub-compartments.3. The third level contains only nodes.

A node palette flowgraph represents either a complete node palette or a compartment of the node palette. In the first case, the nesting depth of the node paletteflowgraph is at least two and at most three, in the second case, the nesting depth is at most two.Each flowgraph can be assigned its own custom node palette. This is specified either on creation of the flowgraph or in the Annotations tab of the Propertiesview of the flowgraph container.

Related InformationFlowgraphsCreating a flowgraphEditing the flowgraph container

1.3.6.1 Exporting the Node PaletteExport the Node Palette as a node template flowgraph.

Procedure1. Right-click the Node Palette and choose Export entire palette from the context-sensitive menu.

The Save As wizard appears.2. Navigate to the directory of your project and save the node template flowgraph file with the extension .hdbflowgraphtemplate in this project.

Refresh the Project Explorer view, and then the node template flowgraph is available in your project.

NoteThe standard location of HANA projects on your local system is the directory hana_work in the home directory of your local user. There you find a sub-directory corresponding to the system shared with your project. The directory of the project is then located in the sub-directory __empty__.

1.3.6.2 Customizing the Node PaletteCustomize the node palette of a flowgraph by adding a reference to a node palette flowgraph to the annotations of its flowgraph container.

ContextA flowgraph can be assigned a custom node palette. This can be done in three ways.

Add additional compartments to the existing AFM node palette.Add additional compartments to an empty node palette.Add additional compartments to a custom node palette.

NoteThe recommended way to customize the node palette of a flowgraph is via the New Flowgraph Wizard during the creation of the flowgraph. The followingprocedure of directly editing the annotations of the flowgraph container is only advised if you actually need to change the node palette of an existing flowgraph.

Procedure1. Open the Annotations tab in the Properties view of the flowgraph container of a flowgraph.2. If the annotation does not exist, add the annotation with the key sap.afm.palette.3. (Optional) Insert the name of a node palette flowgraph (with the extension .hdbflowgraphtemplate) to the Value of this annotation. This replaces the

default AFM node palette with the custom node palette defined by the specified node palette flowgraph.4. If the nested annotation with the key isDefaultUsed does not exist, add it as a child to the annotation sap.afm.palette.

The Value of this annotation determined if the default AFM node palette is shown.5. If the nested annotation with the key additions does not exist, add it as a child to the annotation sap.afm.palette.6. (Optional) Insert a comma-separated list of names of node palette flowgraphs (with the extension .hdbflowgraphtemplate) to the Value of this

annotation. This adds the compartments defined by the specified node palette flowgraph to the node palette of the flowgraph.

Related InformationCreating a flowgraph

1.3.6.3 Editing a node palette flowgraphEdit a node palette flowgraph to model a custom node palette.

PrerequisitesYou have exported the Node Palette of the SAP HANA application function modeler to a node palette flowgraph Template.hdbflowgraphtemplate.In addition, you have created a new (standard) flowgraph Custom.hdbflowgraph with the advanced option of choosing the node palette flowgraph


Page 22 of 31

Template.hdbflowgraphtemplate as the Custom Node Palette.

ContextA node palette flowgraph represents a custom node palette for the application function modeler. Node palette flowgraphs can be edited with the application functionmodeler like standard flowgraphs. The behavior of the application function modeler when editing node palette flowgraphs differs in two aspects from the editing ofstandard flowgraphs.

1. All nodes in the node palette flowgraph are automatically aligned on a horizontal line. By this, the order of the nodes (left to right) represents the order of thecustom node palette entries (top to bottom).

2. The node palette flowgraph contains nested Palette Container nodes. These nodes represent the hierarchical structure of the custom node palette. Thesenodes look and behave similar to the flowgraph container.

In the following step by step tutorial, we use the application function modeler to customize the node palette of the Custom flowgraph by editing the Template nodepalette flowgraph. We cover only those aspects of modeling node palette flowgraphs that differ from modeling standard flowgraphs.

Procedure1. Open the Custom flowgraph with the application function modeler.

The application function modeler displays the empty Custom flowgraph with a custom node palette defined by the Template node palette flowgraph. At thispoint, this is still the default application function modeler node palette.

2. Open the Template node palette flowgraph with the application function modeler.The Template node palette flowgraph is displayed in a separate tab. The flowgraph container contains Palette Container nodes representing the topcompartments of the node palette for the Custom flowgraph.

NoteA node palette flowgraph contains no connections. Therefore the flowgraph container has no anchor regions. Creating connections is disabled whenediting node palette flowgraphs.

3. Right-click the GENERAL node and choose Collapse/Expand in the context-sensitive menu.The GENERAL node expands. It contains the template nodes of the General compartment of the application function modeler node palette.

NoteYou can collapse a Palette Container node by choosing again Collapse/Expand in the context-sensitive menu.

4. Drag the JOIN node to a position between the SORT node and the UNION node.The auto-layout function of the application function modeler rearranges the nodes such that the JOIN node and the SORT node have effectively swappedpositions.

5. Switch to the editing tab of the Custom flowgraph. Refresh the custom Node Palette by right-clicking the Node Palette and choosing Refresh in thecontext-sensitive menu.The Join node template and the Sort node template have swapped places in the General compartment of the Node Palette .

6. Switch to the editing tab of the Template node palette flowgraph. Add a Palette Container node to the GENERAL node by dragging the corresponding nodetemplate from the General compartment of the Node Palette to the canvas of the GENERAL node.A nested Palette Container node named COMPARTMENT is added to the GENERAL node.

7. Add an object from the Project Explorer view to the canvas of the COMPARTMENT node.8. Switch to the editing tab of the Custom flowgraph. Refresh the custom Node Palette .

The sub-compartment Palette Container is added to the General compartment of the custom Node Palette . It contains the node template for the objectfrom the Project Explorer view added to the COMPARTMENT node in the previous step.

9. Switch to the editing tab of the Template node palette flowgraph. Add a Filter node from the Node Palette to the COMPARTMENT node. Edit the DisplayName and the Description in the General tab of the Properties view of the Filter node. In addition, edit the signatures of the input and the output of theFilter node and define a filter expression.

10. Switch to the editing tab of the Custom flowgraph. Refresh the custom Node Palette .A new node template with the chosen display name and description (tool-tip) was added to the Palette Container sub-compartment.

11. Add node template of the new filter node from the custom Node Palette to the Custom flowgraph.The added Filter node has received the modified input and output signatures and the filter expression of the Filter node in the Template node paletteflowgraph.

12. Switch to the editing tab of the Template node palette flowgraph. Move the COMPARTMENT node from the canvas of the GENERAL node to the canvas ofthe flowgraph container.

13. Switch to the editing tab of the Custom flowgraph. Refresh the custom Node Palette .The previous Palette Container sub-compartment in the General compartment is now a new top level compartment of the Node Palette .

Related InformationModeling a flowgraph

1.4 Node ReferenceUse the nodes in the General palette to transform the data.Select the nodes that you want to use and place them on the canvas. Double-click the nodes to begin configuring them. Attach the nodes to other nodes bydragging the arrow from the previous node to the next node.

1.4.1 AFL FunctionAccess functions of the Application Function Library.


Page 23 of 31

PrerequisitesYou have added an AFL Function node to the flowgraph.

ContextUse this node to model functions of the Application Function Library (AFL) that are registered with the system. AFL functions are grouped by function areas.

NoteYou can retrieve the list of all AFL areas and functions registered in a HANA system by viewing the content of the views SYS.AFL_AREAS andSYS.AFL_FUNCTIONS.

Many AFL areas are optional components for HANA. For some of these optional components the SAP HANA Application Function Modeler (AFM) providespreconfigured node templates. In this case, the AFM automatically displays a separate compartment for this area in the Node Palette .

NoteYou can refresh the Node Palette by choosing Refresh in its context-sensitive menu.

Procedure1. Select the AFL Function node.2. In the General tab in the Properties view, select the drop-down menus for Area and the Function .

The AFM changes the inputs and outputs of the node according to the existing meta-data for the function on the server.

NoteFor some AFL areas there exists a preconfigured Node Palette compartment. You cannot change the Area or the Function of a node added from oneof these compartments.

3. If applicable, change the Category of the function.4. Specify the inputs and the outputs of the function by editing the signature and the fixed content of its anchors.

NoteFor some AFL areas there exists a preconfigured node template for this function. In this case, the fixed content of the inputs that define parameters ispreconfigured.

Related InformationUsing the Table EditorSAP HANA Business Function Library (BFL)SAP HANA Predictive Analysis Library (PAL)

1.4.2 AggregationAn Aggregation node represents a relational group-by and aggregation operation.

PrerequisitesYou have added an Aggregation node to the flowgraph.

Procedure1. Select the Aggregation node.2. In the Properties View, select the General tab to configure the group-by attributes and the aggregation functions.3. Choose the role of the attribute by double-clicking on the respective cell of the Action column.4. In the Mappings tab, use the Mapping Editor to define the output mapping of the node.

Related InformationUsing the Mapping Editor

1.4.2.1 Aggregation OptionsDescription of options for the Aggregation node.

Option DescriptionName The name of the node.Display Name


Page 24 of 31

1.4.3 Data SinkEdit nodes that represent data sinks.

PrerequisitesYou have added a Data Sink node to the flowgraph.

Procedure1. Select the Data Sink node.2. In the General tab of the Properties view use the drop-down menus Authoring Schema and Catalog Object to specify the data sink.

TipYou can configure the authoring schema by choosing Schema Mapping in the Quick view of the SAP HANA Modeler perspective.

3. In the same tab, specify the Truncate Behavior . If the option Truncate Table is selected, the table is cleared before inserting any data into the table.Otherwise, all inserted data is appended to the table.

4. Optionally, if the node is a Data Sink (Template Table) node, specify in the same tab in the drop-down menu Data Layout whether a table with row orcolumn layout is created.

ResultsThe signature of the input anchor is set automatically.

NoteTo activate the flowgraph, the database user _SYS_REPO needs INSERT and in case of truncation also DELETE object privileges for the chosen data sink.

Related InformationSetting up the SAP HANA Application Function Modeler

1.4.3.1 Data Sink OptionsDescription of options for the Data Sink node.

Display Name NoteAFM only.

The name shown in the Palette pane.

NoteThis option can only be changed when creating a template. It cannot be changed when using the nodeoutside of a template.

Description NoteAFM only.

(Optional.) Provides a comment about the operation. For example, "Calculate total sales in May."Column/Attribute The input column name that you want to use in an Aggregation operation.Aggregation/Action Choose one of the following:

Avg: calculates the average of a given set of columns.Count: returns the number of values in a table column.Group-by: use for specifying a list of columns for which you want to combine output. For example, youmight want to group sales orders by date to find the total sales ordered on a particular date.Max: returns the maxium value from a list.Min: returns the minimum value from a list.STDDEV: Returns the standard deviation of the given expression as the square root of the VARIANCEfunction.Sum: calculates the sum of a given set of values.Var: Returns the variance of expression as the square of the standard deviation

Option DescriptionEnter table or view name Note

AFM only.Enter the name of the table or view.

Matching items NoteAFM only.

Shows matching tables or views are as you begin typing in the previous option.


Page 25 of 31

1.4.4 Data SourceEdit nodes that represent data sources.

PrerequisitesYou added a Data Source node to the flowgraph.

Procedure1. Select the Data Source node.2. In the General tab of the Properties view use the drop-down menus Authoring Schema and Catalog Object to specify the data source.

NoteThe check-box Realtime Behavior is only relevant if the flowgraph uses the additional cost Enterprise Information Management optional component andif a task plan is generated.

TipYou can configure the authoring schema by choosing Schema Mapping in the Quick view of the SAP HANA Modeler perspective.

ResultsThe signature of the output anchor is set automatically.

NoteTo activate the flowgraph, the database user _SYS_REPO needs SELECT object privileges for the chosen data source.

Related InformationSetting up the SAP HANA Application Function ModelerAdd a Variable to the Container Node

1.4.4.1 Data Source OptionsDescription of the options in the Data Source node.

Option DescriptionName The name for the output target.Display Name Note

AFM only.The name shown in the Palette pane.

NoteThis option can only be changed when creating a template. It cannot be changed when using thenode outside of a template.

Description NoteAFM only.

(Optional.) Provides a comment about the target. For example, "West Region Sales Q1."Type Lists whether it is a view or table.Authoring Schema Lists the system or folder where the view or table is located.Catalog Object Lists the table or view.Truncate Behavior Limits the amount of data written to the Data Sink.Writer Type Choose from the following options:

insert: adds new records to a table.upsert: if a record doesn't currently exist, it is inserted into a table. If the record exists, then it isupdated.

sap hana smart data integration

Documents