Workflow takes 3 hrs to run A dynamic input for 200 records on a HANA model

Greetings,

I have a model that takes Material numbers <200 numbers, and uses the dynamic input tool to pass them through a HANA model that results in the bill of materials for each of the materials. The output is only around 14k rows of data.

The HANA model requires Material and plant as input parameters that is why I'm using the

Running all material numbers at once took 3:13 hours attached screenshot. Attempted to break up the materials and run in sets of 5 and have 15 simultaneous dynamic inputs pass them though the HANA model. Its been over an hour and only at 25% done.

Manually running one part number in Alteryx connected to the HANA model takes Designer x64 Finished running in 1:31 minutes is there an Alteryx tool that could help expedite?

Is there something else that I can try? The scheduler times out after 60 minutes and I need the extract to be used in other reports.

This is the SQL for the HANA model

SELECT
"CA_SORTKEY",
"CA_MATERIAL_TOPLVL",
"CA_MATERIAL",
"MAKTX",
"PR_TYPE",
"SPR_TYPE",
"ISSUING_PLANT",
"PHANTOM",
"SCHGT",
"MTART",
"DISLS",
"DATUV",
sum("EXT_QTY") AS "EXT_QTY",
"UOM",
sum("PLIFZ") AS "PLIFZ"
FROM "_SYS_BIC"."Cubic.Dashboards.MFG_STATUS.ReportingViews/CR_BOM_EXPLOSION_WO_BUY"('PLACEHOLDER' = ('$$IP_PLANT$$',
'2221'),
'PLACEHOLDER' = ('$$IP_MATNR$$',
'70006-3013-E-R007!'))
GROUP BY
"CA_SORTKEY",
"CA_MATERIAL_TOPLVL",
"CA_MATERIAL",
"MAKTX",
"PR_TYPE",
"SPR_TYPE",
"ISSUING_PLANT",
"PHANTOM",
"SCHGT",
"MTART",
"DISLS",
"DATUV",
"UOM"

duration.png

flow.PNG

Developer Tools

Custom Tools

Dynamic Processing

Optimization

Accepted answers

All comments

harsh_alang

Could you share what version of Designer and Server you are using ?

oly

Hi @BAvitia88 ,

In your current design all the Hana requests are running simulteneaously and traffic can have multiple bottlenecks through the driver, network, alteryx engine or SAP.

I believe you really need to put your HANA BOM into an iterative Macro, and instead of filter tool adjust the parameters to control your query. In that case requests will run sequentially and your math should more or less work as multiplying the original 1:30 minutes times 5 (?).

When you have performance issues and testing, I also recommend to make extra filters to narrow your data - either two batches or add some dummy test filters in your queries. Remove them once you solved the performance issue and ready to test production.

Hope it helps,

Oly

BAvitia88

Alteryx Non-Admin Designer Version 2020.3.5.30242

BAvitia88

I'm new to Macros, Tried to do it attached is the flow with some data. I'm not sure what I'm not doing correctly. Is this something you can help me with, or should I post a different question to the community for help with the macro.

Sales Order Data for Macro Help.yxzp

oly

@BAvitia88 ,

Your Macro was iterative and that means for example calculating interest or depreciation multiple times.

We need Batch Macro that works per individual records or groups of records. As I understand you need to execute the request for every MATNR value and then Union the results.

For the MATNR field from your first source inside the Macro we are creating a Control Parameter that will update our SQL in a regular Input Data Hana SQL object, not a Dynamic input. I created a sample SQL input that you can replace with your Hana query inside the Macro. Once you add control parameter - the macro is marked at Batch and will execute for every value/group of values.

For the Batch Macro results - they will be unioned automatically and you can add Record counters [Engine.Iteration Numbers] as you correctly did.

I'm not exactly sure why you still need the Hana query outside the macro and the second input for the Macro (actually now I have only one input). I believe it should be completely removed as you are passing the list of MATNR and should receive back all the records out from the Macro. But for example if that additional source should be triangulated - records filtered by specific flags it's ok. The only issue the macro might run all the Input records times the control parameters. Currently I copied your sample input data back into the workflow but see if you need to remove it.

Also, not exactly sure on all the caculations you currently do inside the macro to join the results - try first with minimum, just Hana query, and then see if you need to work with the batches separately or with a whole Unioned Macro' output together in the main workflow.

Let me know how it looks for you,

Best,

Oly

Sales Order Data for Macro Help.yxzp

BAvitia88

Thank you

Made the changes and connections, Designer x64 Finished running Sales Order Data for Macro Help.yxmd in 3:01 hours

The Macro did not help reduce the run time. The HANA model run time is the limitation.

oly

Thank you for confirmation and glad that you figured out the batch macro! Great you managed to get the data out and hope you will solve your business needs.

Quick Links

This months top contributors

AkimasaKajitani 389

mceleavey 388

mbarone 337

Hollingsworth 335

LanisC 335