Flowgraphs in XSA (express edition H2SPS04)

Recently I was looking into flowgraphs in HANA 2 XSA SPS04. Since I have background in XS, HANA Modeling, and SAP BODS, I thought this may be an easy exercise to achieve.

Before getting into the solution, let’s see what is a flowgraph? A flow graph is the diagram representation of some logic executed with 0 or more filters (input params) in order to get some output.

I started by re-using my wave project that contains a database module called db.

I created a folder called flowgraphs and a flow graph object called FG_REP_T1 that I will use to replicate an existing table

In the web ide, right click on the new folder and create a new file of type flowgraph

When the flowgraph editor opened, I wanted to see my options.

When clicking on the plus sign, the context menu provided me some options for what I wanted to achieve on this flowgraph. Not having a lot of background knowledge on the options or possibilities on a flowgraph, I was quickly able to reuse some prior knowledge from BODS and play with a few different options.

My first operation was to include a data source (where data originates) and a target (where data will land). My first exercise was simply to get the idea of what to do from point A to point B point of view. I was able to quickly map the source to the target. Then, I saved my flowgraph and after building my db module, I was able to see that the flowgraph had no errors. In order to see the output I ran a data preview. Very simple wasn’t it?

I wish all real scenarios were as easy as my first attempt… wrong!!!   Most likely we will need to perform some data modeling (joins, unions, projections, filtering, or create calculated fields such as including a timestamp) in order to populate some target table. Due to my data modeling background, I was also able to understand the flow without a lot of digging into documentation, rules, syntax, etc.

My second attempt was to modify the existing flowgraph. A little more involved that a straight mapping. It had 2 data sources, a join, a projection (including a timestamp calculated field) and a target node using a template table. Btw – template tables are used when there is no pre-existing target table and we can create one on the fly. Alternatively, we could have selected an existing table as our target source.

Every time we make changes to the flow, we must Save and Build our module in order to see changes reflected in the HANA system. After building and receiving the successful message, I toggled my browser to the SAP Web IDE and into the database explorer where I was able to select my container schema > tables and run a select statement on it. As my test scenario, I ran the flowgraph several times in order to see the REC_DT being updated (as the timestamp when I ran the process)

As I did this step repetitively, I also realized that more often than not, a job will need to be involved in running the flowgraph. I did a very small google search where I found the syntax on the documentation to run the Task from the SQLScript console. Then, I realized that using this approach will allow us to include a script into a stored proc and possibly scheduling it from an XS job.

After running the SqlScript to Start the Task we can immediately see the updated timestamp (from my calculated field)

Now, everything is cool until we need to look into other configurations other than the default ones.

At the flowgraph level we can see one that has the behavior type:

◉ Batch task * I used this one since I was doing it manual and then scripted

◉ Realtime * will represent a more real time business scenario where we want to reflect changes almost immediately

◉ Transactional – these operations are executed on demand

◉ Procedure  — when stored procedures are involved

The second tab shows a section for variables – this is similar to input parameters in a calculation view.

If we click on the plus sign (right side above the grid) we can create a variable to be used as a filter in our join/projection, etc.

I didn’t do much in partitions since I was working in a simple scenario, however, for the purpose of the blog, the partitions section refers to partitioning the data if we have large volumes.

Inside my join node, notice I had to select what is on the left and right joins. Again, very similar to a calculation view where I need to select the join type and the join condition.

Joins:

On Filters: We can select the mapped columns and compare that against a filter variable using SQLScript like syntax using comparison operators such as >, <, >-=, <=, =

What do the Icons on each node mean :

Inspect: general info about the node

Configure: mappings, filters, join information, calc columns?

Preview: data preview

In the target node:

Target:

Columns structure

Settings:

Truncate: append vs re-load new

data operations: upsert, insert, update

Logical keys: applies to the structure In context

Questions that you may have (if your answer is different than mine, please let me know your thoughts)

◉ Why use a flowgraph and not SAP BODS (ETL tool)?

Both approaches can be used, however, the ETL tool can also be used for other data sources/targets external to SAP HANA. My example is simply to see how flowgraphs in XSA are used and raise awareness of this feature within XSA. The same feature is available on HANA 1 XSC using the Web Workbench. The tool itself looks different but the functionality may be similar. As you should know, the newer versions of the product may have more features available. Further, the flowgraph feature comes by default on XSA as a db object, while using SAP BODS we will need to have a separate user in the SAP BODS environment, create a data store, use workflows and data flows, and then run that BODS job externally to HANA.

◉ Did I encounter any issues while developing this simple flowgraph? Or should we expect more complex scenarios to have some known issues?

In my simple scenario, I didn’t encounter issues with access since my user has a container and the table I used also existed within my container schema* I will say that if we need to use tables from other schemas, most likely we will need to use synonyms, grants, and all the other required db objects in order to be able to select from the sources and upsert into the target.

From the functionality point of view, one thing that happened to me a couple of times was that if I clicked on preview then on configuration a few times back and forth, then my web ide screen went black as if it was not responding quickly. I had to close the flowgraph and reopen it in order to continue to work?  known issue maybe – I hope?

◉ Can the flows be executed on a schedule and how?

Yes, they can. We would need to use an xs job in order to run a proc or scripted sql.