Working with Databases

Accessing databases from Alteryx is very simple and fast. And the methods we use for files can apply to databases as well.

But databases have more peculiarities, many of which we, as analysts, cannot change (such as response speed, availability, and design).

Also, we’ll be addressing the basics of Data Connection Manager (DCM), a very useful and powerful feature introduced by Alteryx in version 2021.4, but highly improved in version 2022.1.

DCM is a secure, centralized, single-source administration, storage, and connection-sharing capability for database and cloud interoperability, offering enhanced security improvements (credentials linked to data sources and resolved at runtime).

If you are an administrator within your company, you have probably already identified the huge benefits DCM brings to your job. If you’re not, you’ll realize how easy it is to administer your credentials and connections using DCM once you start using it.

Another powerful feature of Alteryx Designer is the In-Database (In-DB) tools. These tools allow us to perform blending and analysis against large sets of data without moving the data out of the database, providing performance improvements over the traditional methods, since everything is executed within our Database Management System (DBMS) and no traffic along the network is required (very low to no latency).

In this chapter, we’ll be looking at some recipes to improve how we work with databases:

• Scanning databases dynamically (cursor behavior, but more efficient)
• Using Alteryx Calgary Databases
• Creating credentials in DCM
• Creating connections in DCM
• Getting information from your In-DB connections/queries