SAP SDI flow graph Batch Task-- Improve Performence

[former_member428197]

Hi,

We are trying de-normalize the data using SDI flow graph. We created a flow graph(Batch Task) which joins around 7 tables and inserts into a one flat table.

Flow graph is running perfect with out any issues, Further we want to know the bottle necks and improve the performance of the flow graph.

If there is any SQL/Stored procedure generated for the flow graph then where can i see the SQL/Stored Procedure (location path)?

We are on SAP HANA 1.0 SP12

Thanks

Srini

[former_member240828]

Hi Srini,

Yes, this seems to be a bug. For the JIT it should take the default values of the variables and you once you activate a flowgraph with the JIT flag set for one of the nodes you can access the calc scenario.

In my example below the TargetSchema of the flowgraph is SYSTEM and the flowgraph "varJITTest" is located in the package public.sap. The JIT Flag is activated on the node "Filter1".

SELECT * FROM "SYSTEM"."public.sap::varJITTest_Filter1_CV";

Similar to this you should be able to access your calc scenario as well. This is then translated into operations on the engines in hana. So there is not necessarily one equivalent sql function for this. Analyzing the plan of this calc scenario should still allow you to figure out the complexity of the individual operations, filter conditions, aggregations etc.

Kind Regards,

Timo

[former_member240828 · Accepted solution]

Hello Srini,

the Flowgraph in Batch Task mode is generating a Taskplan which will then be executed.

One way to look into the performance of those operators is to enable the "Just in time" preview on the last node of the flowgraph and activate the flowgraph again. You can now click on a small preview icon next to that node and you can then execute a select query on a generated calc scenario for the flowgraph including this operation. This Query can now be analyzed by right clicking on the query and analyze query.

Kind regards,

Timo Wagner

[BenedictV · Accepted solution]

Hi TImo,

I have a 'data sink' with a DB table as my last node and when I do preview, the select is just a SELECT top 100 from <DB Table>. Am I missing something here? If I want to analyze all the filters and joins performance, shouldn't I get a more detailed query with all the joins and filters?

[former_member240828 · Accepted solution]

Hi Benedict,

with the last node I meant the last transformation.

If we have a flowgraph like this:

Source1 . ---- Join --- Aggregation -- Data Sink
Source 2 ----/

You can apply the "Just in Time" Preview on the Aggregation Node and this will then create a calc scenario that should be equivalent to the flowgraph output of the aggregation.

You can use the regular sql analyzer capabilities to look into the different operations and e.g. see how a certain Join condition impacts your overall performance.

Kind Regards,

Timo Wagner

[former_member428197 · Accepted solution]

HI Timo,

Thanks for ur response, we have variables defined in flow graph and the "Just in Time" preview doesn't work when we defined any variables.I'm not sure if this a bug in 1.0 SP 12?

Is there a way to know what SQL the flow graph is executing?

Thanks

Srini

[BenedictV · Accepted solution]

Ok, that works. I am able to use the 'just in time' to look at inter mediate results just like a data preview on nodes in a calculation view.

[BenedictV · Accepted solution]

Hi Srinivas,

Create a copy of the flowgraph and set it to 'procedure' instead of task. Now you will see a stored proc created under the same schema the flowgraph was defines in. If you look inside the proc you can see the entire SQL generated for the flowgraph. You can take this and execute it in a sql console and look at the plan, filters etc.

Goodluck.

[former_member240828 · Accepted solution]

Hi Srini,

if you've set default variables you should still be able to access the JIT.

If you can't can you try to manually access the generated Calc Scenario?

It should be visible in the webide catalog in the Column Views Section of your Target Schema following this pattern:

"SYSTEM"."public.sap::varJITTest_Filter1_CV"

With SYSTEM being the Target Schema, public.sap the package, varJITTest is the flowgraph name and Filter1 is the name of the node with the JIT flag set.

Converting it into a procedure is not a fit all solution, as the task framework allows additional operations to be performed that are not available in the procedure target mode.

Thanks,

Timo

[former_member428197 · Accepted solution]

Thanks Timo and Benedict for you valuable suggenstions.

Thanks

Srini

[former_member428197]

Thanks Timo and Benedict for you valuable suggenstions.

Thanks

Srini