Community Spring Cleaning week is here! Join your fellow Maveryx in digging through your old posts and marking comments on them as solved. Learn more here!

Alteryx Designer Desktop Discussions

Find answers, ask questions, and share expertise about Alteryx Designer Desktop and Intelligence Suite.

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

BAvitia88
8 - Asteroid

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"

8 REPLIES 8
harsh_alang
Alteryx
Alteryx

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

HarshA
oly
Alteryx Alumni (Retired)

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
8 - Asteroid

Alteryx Non-Admin Designer Version 2020.3.5.30242

BAvitia88
8 - Asteroid

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. 

oly
Alteryx Alumni (Retired)

@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

 

 

BAvitia88
8 - Asteroid

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
Alteryx Alumni (Retired)

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.

Ben_H
11 - Bolide

Hi @BAvitia88 

 

One issue that I've had in the past is with SQL queries pulling a field that for some reason is converted to another field type on the way out.

 

I would also consider swapping the standard input tool out used in Oly's batch macro for an inDB input.

 

Regards,

 

Ben

Labels