Engine Works
Under the hood of Alteryx: tips, tricks and how-tos.
No need to hide I’m a big fan of In Database and tools. It’s fast and uses my bandwidth efficiently especially working from home. I don’t need to pull all data to my computer, all are being processed on the server side, and I only get what I need.
Sometimes I’m in trouble a little bit.
Let’s assume, I use the Connect-InDB tool, connect to the database, run my query and workflow, then save the workflow and close it. The next time I open it, I see my Designer freeze, and I believe Connect-InDB tool tries to connect to the database to pull metadata info. It looks awesome, right? And imagine you have tens of Connect-InDB tools, you must wait for each one to finish pulling metadata information.
In another scenario, within these tens of Connect InDB tools, you decided either to disable the container where the tool is located or decided to make a change or delete. It is not a surprise that even doing this, for some reason Designer freezes, most likely is trying to cache some data in the background.
So far, you just waited until this process was completed, and then you are surprised you just remembered that you changed your password and didn’t update it in Connect-InDB tool. Unfortunately, you don’t use DCM. So, what happens next? You wait until your Designer returns from freezing, and you see a number of errors saying the username or password is invalid. After that, it happens to me, it locks my database access, and even my Windows account. Either I should contact IT Support to activate my Windows, and then the database.
It is an awesome tool but … DCM is a partial solution because it only helps when you update your password, but background data processing and freezing will stay there. Good news, I found a way to avoid this.
I use the Text Input tool and create 2 fields, named query and dsn.
In the query field write your query, and dsn just is your data source name in DCM. Connect Text Input with the Select Records tool, and point to the first line so you’ll be sure that you will refer always to the first line for your query and dsn. This part is optional, but my experience shows that many times I created an extra row in Text Input tool which caused an error. Then the last step is to connect the Dynamic Input InDB tool and point your field for your query and dsn.
The magic is here. After saving the workflow and then re-opening it, Designer will not freeze or make any data progress in the background because the Dynamic Input InDB tool will not request anything from the Text Input tool unless you run it. So, if you changed your password but you didn’t update in DCM for example, it’s not going to be a problem, because until you run, no InDB tool will try to connect and pull data from the database.
This small trick may help you to override many issues and even you may find a way to automate, which will be another article.
Please share your feedback and opinions if you find this article useful.
