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 Knowledge Base

Definitive answers from Designer Desktop experts.

Database Connections: Creating an alias and the advantages of using an alias

TonyM
Alteryx Alumni (Retired)
Created

Database aliases can help users save time and allow easier sharing of data. Let's take a look at how.

The first step to creating a database alias in Designer is to establish the database connection. This can be done with the Input Data tool by selecting any of the database options in the dropdown.

Databases we can connect to with Alteryx, an overview.

http://www.alteryx.com/technical-specifications

Once you’ve established the connection through the connection admin, a database connection string will appear, like the one shown below.

n2LPsM

Next, let’s go to the Manage Database Connections window (or Manage In-DB Connections window for In-DB aliases). This will be under Options> Advanced Options > Manage Data Connections. Here we can add new aliases, and edit or delete, any existing ones.

Manage Database Connections overview:

There are two types ofaliases:

User:Any user can add a UserAliasthat only they will be able to access and change.

System:SystemAliases can only be added/edited by a local administrator.

Available options:

Sync All allows you to sync Gallery Data Connections

Add Connectionsallows you to create a new alias (see steps below)

Additional options can be found when you hover over an existing data connection:


pencil.pngAllows you to edit the password of the Alias.

n2Ls12

Allows you to delete an alias

n2LYW4

When adding a new alias, Alteryx will assist you in making the connection to both MS Sql Server, and Oracle. For all other database connections, select the 'Other' option.

There will be three things to include for 'Other' database connection types: the connection type (user vs system), an alias name and the connection string. The reason we made the connection first through the Input data tool is so that we can simply copy/paste that string into our alias manager.

When the alias has been created you can access this connection through the Input Data tool dropdown under Saved Data Connections. You’ll notice that the string now says aka:AliasName.

n2LcKG

Advantages:

Q: What happens when you have multiple workflows that all reference a connection string and your password changes?

A: If using workflows that reference an alias, the user will only have to update the password in the alias manager. If using a regular connection string, every Input Data tool will have to be updated in every workflow that uses that connection.

Q: Can I share, or export, a workflow that has database connection strings?

A: Yes! If you setup an alias on both user’s machines that are an exact match you will be able to share workflows that use those database connections.

Note:The screenshots and steps taken to create the database connection and alias were built in Designer version 11.8.

Tony Moses

Customer Support

Comments
Treyson
13 - Pulsar
13 - Pulsar

We heavily implement this tool internally. Since we are a small enough organization that we can direct all publications through a single user, the use of aliases help streamline that process as I don't have to rebuild the connection strings for every workflow that comes in for publication. Discovering this process was one of the biggest "Ah-Hah" moments for us. Great post Tony!

snadeau456
6 - Meteoroid

I have an alias manager and in the company I work for we have to change our passwords every 3 months.  The alias manager doesn't seem to work right for me; what is it that I could be doing wrong.  I thought once I update my company password, it would automatically take in alteryx.  Any assistance would be greatly appreciated. Thank you

Treyson
13 - Pulsar
13 - Pulsar

You will still need to make the changes in your alias manager. The only thing that this tool does is make it so that you don't have to go in and change every input tool.

 

I say "the only thing" like that's not a big deal, but it is.

Tom216
7 - Meteor

Is it possible to use the Alias in the 'Connect In-DB' node or does it only work for 'Input Data'? 

Treyson
13 - Pulsar
13 - Pulsar

Tom there is a separate In-DB alias tool.

Tom216
7 - Meteor

 Thanks for your quick response.

 

Which tool is this?  I can't see an In-DB alias tool in Alteryx or online.

Treyson
13 - Pulsar
13 - Pulsar

So there is a semantic mix up here and I apologize. They are no longer actually called Alias's by Alteryx. I believe that they are simply just called "connections". To create one, go to Options > Advanced Options > Manage In-DB Connections. 

 

Connections.png

 

You will need to set this up based on your environment. Once you have created and named it, you should always be able to select that connection to a DB.

 

Connectiosn2.png

 

Now if you are using a gallery and trying to utilize the Gallery Connections, it's a bit different. Your Gallery Admin would need to set those up and then give you access to them. There are a ton of benefits of using the gallery connectors, including but not limited to: A consistent naming convention across all users. Actually that's the best thing ever. 

venuchava
6 - Meteoroid
How do i change connection string and use the same database alias.
Treyson
13 - Pulsar
13 - Pulsar

@venuchava Are you working with a gallery connection or local (user) connection?

venuchava
6 - Meteoroid
Local User Connection
Treyson
13 - Pulsar
13 - Pulsar

You are most likely going to have to delete the existing connection and create a new one with the same name and new connection string. I believe that should do the trick. In the way that it works when you share the workflow to someone who has a connection with the same name on a separate computer, it's only looking for that.

venuchava
6 - Meteoroid
@Treyson Thank You! Will try that. It would have been great if we could add multiple connections to the same Alias and select the connection we need.
venuchava
6 - Meteoroid
I tried deleting the existing and recreating with same Alias name with different database. it did not work even the schema and table name is same but the database is different. I mean my input file is [Alias].[database].[schema].[table Name] this would have worked if my input file is [alias].[schema].[table name] Please let me know if any other suggestions. Thank you!
Dhanushka_A
7 - Meteor

I was hoping that I could alternate between HDC_TEST and HDC_PROD without having to delete and recreate the alias each time I want to change the environments. Is this not possible?  😞

 

Capture3.PNG

venuchava
6 - Meteoroid
"I tried deleting the existing and recreating with same Alias name with different database. it did not work even the schema and table name is same but the database is different. I mean my input file is [Alias].[database].[schema].[table Name] this would have worked if my input file is [alias].[schema].[table name] Please let me know if any other suggestions. Thank you!" Deleting and recreating with the same Alias Name with different database connection worked. Thanks Treyson!! for setting up the call and fixing it. Cheers 🙂
mayankbajaj123
5 - Atom

Hi,

 

I have been using this alias in my input tools without any issues. But I tried using it within a Python code, and it does not seem to work. Is there any way I can use this alias within Python code?

mayankbajaj123_0-1630071624384.jpeg