Limitation of Alteryx to connect data from Databases

[RE5260]

Hi Folks,

Some workflows that are being migrated from Cognos to Qlik are not functioning. Thus, customers desired to experience Alteryx. A demand has arisen, and I've been asked to -

Is there a limitation on how many records Alteryx can retrieve from the Oracle database? They have billions of records in their database.

Which tools should I use when designing workflows—input data tools or database tools? Will Alteryx Gallery run the workflow smoothly? What further conditions must be met in order to do this? How much system memory will be needed?

I have been asked to develop a process as an administrator that will connect to Oracle database and save it to Excel or CSV files. It's possible. But I wanted to know, Is it possible to develop an application that allows clients to access their private gallery and perform the same task? 

Please assist.

[fmvizcaino · Accepted solution]

Hi @RE5260 ,

Your best option is to use the in-database tools. With that, you would be using Oracle`s performance to run all your workflows but to use all the in-database features, it is required to have permission to write in Oracle since sometimes you have to create temp tables to eliminate the query complexity.

The trade-off of using the in-DB tools is that you will have access to a limited amount of tools and have SQL knowledge.

If you go with the regular tools, it is doable and the workflows will run with no issues, but the runtime will also increase substantially.

A bottleneck you will possibly have is the connection between Oracle and Alteryx, downloading billions of data is not easy. After that, the runtime will depend on the workflow complexity, but the AMP engine will speed up the process like no other tool. 

But by enabling the AMP engine, you will be using more resources and more cores and that can mess with the Gallery user experience and with everything else you already have running there. 

It is hard to give a clear answer without analyzing the content of your gallery, but I would say that a 4-core server is not enough to use Alteryx Server as an ETL tool (using the regular tools) and for everything else.

Best,

Fernando Vizcaino

[simonaubert_bd]

Hello @RE5260  I totally agree with @fmvizcaino  : the thing is you don't to retrieve billions of record on the machine that executes alteryx workflow while you can keep the data in db using alteryx in-db. This is typically what I have mainly used these 6 last years. And you can do it with a standard PC and standard server, not more.

"I have been asked to develop a process as an administrator that will connect to Oracle database and save it to Excel or CSV files. It's possible. "

What's the exact need ? Something I do is building an analytics app, mainly in db and finishing with a data stream out and an export to excel.. it works great. But not sure it's what you want to do.



Best regards,

Simon