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"
Could you share what version of Designer and Server you are using ?
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
Alteryx Non-Admin Designer Version 2020.3.5.30242
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
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.
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.
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
User | Count |
---|---|
35 | |
27 | |
7 | |
7 | |
7 |