This site uses different types of cookies, including analytics and functional cookies (its own and from other sites). To change your cookie settings or find out more, click here. If you continue browsing our website, you accept these cookies.
Calgary is a list count data retrieval engine designed to perform analyses on large scale databases containing millions of records. One use case for a Calgary database in an App or Macro is to offer users certain predetermined criteria (e.g. geography) to select and then return the corresponding data to them. A back-end process can combine multiple data sources, format and pre-filter as needed, and load them into a Calgary database that can be queried quickly by the app or macro . This way, the bulk of the processing happens outside of the app or macro allowing data to be returned more quickly.
Because most Calgary Databases (files with .cydb extension) contain millions of records with hundreds of fields, a method for allowing a user to quickly determine which records to read is available.
Recently an Alteryx client wanted to select specific records from a Calgary Database containing over 700 million records. The tool the client used to read records was the Input Data tool.
While the configuration of this tool does allow a user to limit the number of records input, it doesn’t provide the ability to select specific records (option 1 in the configuration).
A Filter tool could be attached to the Input Data but this would require all 700 million records be input first. To read 700 million records would require about 10 hours.
The Calgary Input tool enables users to query a Calgary database directly. (For help configuring the Calgary Input tool, see http://help.alteryx.com/current/index.htm#cshid=CalgaryInput.htm).
Since the .cydb file in our example is small (only 15 records), the amount to time it takes to read records using the Input tool isn’t much different than using the Calgary Input tool. However, if this file had contained millions of records, the time savings of using the Calgary Input can be huge.
If you would like see more examples of the Calgary Input tool, sample workflows are available in Alteryx Designer (go to Help --> Sample Workflows --> Data Artistry --> Calgary Tools --> 2. Calgary Input). In addition to Calgary Input, you will find other Calgary related tools.
Attached examples were created using Alteryx Designer 10.1.
Mosaic BG Dominant and Mosaic BG Household Distribution counts are balanced to Experian’s census estimates. ConsumerView is a marketing file and therefore doesn’t need to be balanced to the census estimates.
Calgary Regression Test Module Business Problem: If you have recurring updates on Calgary files then this module will likely be useful to you. When a Calgary fiIe is updated, it is imperative to be able to check the file for consistency. Depending on the number of columns and indices contained in the file, this can sometimes pose a challenge. Actionable Results:
Compare two versions of a Calgary file to determine if columns and indices remain constant
Complete regression testing through a simple application interface
Easily identify indices where counts have changed over 10%
Overview: Have you ever found yourself in a situation where you want to compare a newly built Calgary file with the previous iteration, in order to see if all the columns are still there, the indices are still there and that they give similar results as the last time? This Module does just that, and gives you a nicely edited document showing the results. For the querying, the app goes through all the indices that are in the Calgary directory, selects a random value from the file to use as the query value, and compares counts from the new and the old dataset, highlighting in red if the counts are more than 10% different. Vertical: Any Required Input: Calgary file with indices (previous and updated versions you wish to test) Application Process: For best results, run as a .yxwz instead of a .yxmd. In the application interface, just navigate to the two files and press Start. Be aware that if you have a lot of indices and a lot of data, it may take a while to run, so if you just want to try it out, start with something small.
Household Level Analytics Module
Business Problem: Businesses investing in new customer acquisition will be more successful in reaching prospects if they know which consumer profiles best describe their current customers. Compiling customer databases through marketing or loyalty card programs allows businesses to know who their customers are, as well as where they are located. When correctly leveraged, this type of information enables strategic and focused spending of marketing funds. Actionable Results :
Understand the demographic attributes of your customer base
Target new customers that fit the profile of your current customers
Ensure that your advertising and marketing funds are spent in the most effective way possible
Overview: Would you like to identify key demographic traits of your target customers? By appending household-level characteristics to a customer file, you can achieve the most accurate Consumer Profiling of both existing and prospective cstomers. This analysis allows business owners to target households that are not in their customer database, but are in their trade area and match the demographics of current customers. Customer acquisition using targeted households is a more efficient way to direct spending on advertising and marketing programs. Vertical: Retail Data Utilized: Customer file containing the following fields:
Customer Address containing street number, street name, city, state
Customer ZIP Code
Alteryx Data: Experian Household File Application Process:
The selected customer file is run through the Calgary Join tool using Experian household data to isolate the Experian records that match the customer records.
Fuzzy Matching is then performed to eliminate all duplicate records.
Finally, the wizard outputs the customer file with appended household-level data.
The option to be able to search a field with wildcard characters is often needed. This functionality within the Calgary suite of Tools can be accomplished by having a “Contains” function, where this is accomplished without utilizing a wildcard to search. This also allows you to be able to use the Indexing power that the Calgary products offer, without having to do these searches with downstream functions like Filter or other Formula based tools. Tools used: Here's how 1) Create a base cydb file with this option selected: 2) Once the base file has been created, you now want to modify that cydb file to enable the Fields you want to query with the “Contains” option. In this example dataset, we want to be able to do these type of queries for the “CONAME” and “ADDR” fields: Setup Calgary Input into Calgary Loader, when the following message will appear. Make sure you check “Yes” 3) Select the “Load a single advanced index” option Set the Index Name you would like (in this case, keeping the same name), and choose the field desired from the “Source Field”. Also, set Advanced Index Mode to Full Text 4) Repeat this process with all of the fields you’d like to be able to search. In this sample, placing “mail” in the CONAME and “ln” in the ADDR fields returned this record:
Data Products 101 Part 1: Installing your Data (Network & Command Line) This article will walk through the Network data installation process and is intended for Admins. If you are looking for a guide on the standard local installation process, then check out our article on Local Data Installation. Network Data Install (Advanced) Considering that the entire US Business Insights bundle is around 180GB, we often have cases where a user will run out of hard drive space during the data install. If you find yourself in this boat and are on a company network, you can resolve your space issues by performing a network install with the Alteryx Data Installer. This can also potentially save a huge amount of installation time when you have multiple users for the Data Bundle. Instead of performing this lengthy install numerous times – once for each user – you can install once and be done. The downside to a Network Install is performance. A Local Install will almost always have better performance, as a Network Install is limited to network speeds and read/write times on your network drive. A Network Install consists of two operations: 1. Preparing the Network Installation – contents of the Alteryx Data Install are copied to and prepared on your network location. 2. Registering the Network Installation – where each of your users navigate to the prepared network location in order to register the data sets to their machine. To Prepare a Network Installation: 1. Follow Steps 1 – 4 within the (Local Installation process article)[hyper-link here]. 2. You should now be at the Location screen - choose ‘Prepare a Network Install’ and browse to the shared network location where you wish to install. Be sure that all users have the same configuration if you're using a mapped network drive. At a minimum, each of your users will need read access to this location. If you plan to install CASS to this network location, then your users will also need write access to this location. Click ‘Finish’ after selecting the network location and let the Alteryx Data Installer run. 3. Once the installation is complete, each of your users will need to register the Prepared Network Install to their computers. To Register a Network Installation: 1. Each user will need to navigate to the location where you Prepared the Network Install. Once there, run ‘AlteryxDataInstall.exe’ and follow Steps 1 – 4 as outlined under the Local Data Install guide. On the Location screen, select ‘Register from a Network Location and click ‘Finish’. 2. The installation process should take no more than a couple minutes to copy over a handful of files to the user’s computer (sample data, etc.). Once done registering, the user can immediately start accessing the installed data in Alteryx. Command Line Install (Advanced) The Alteryx Data Installer also has command line operations for all of our IT users. Please refer to our Command Line documentation for more details.
Data Products 101
Part 1: Installing your Data (Standard)
So, you just bought one of the Data Bundles from Alteryx and you’re excited to dive in and start exploring Location and Business Insights for your company – but where do you start? How do you get access to this Data? Well, unlike a SQL database or CSV you might be used to working with, you’re going to start by installing these Data Products. This article will walk through the standard data installation process. If you are an Admin and are interested in a Network or Command Line installation, check out our article on Data Installation for Admins.
First and foremost, do you have your Data Package yet?
If you purchased either the US or Canada Business Insights bundles, you should have received a hard drive in the mail. If you have your hard drive plugged in and read, then go ahead and skip to Step 1 below.
If you purchased one of our Location Insights spatial bundles, then head to licensing and download portal and download the data. Assuming you have your Spatial license key activated, it’ll be under the Data Packages tab on the left – just click on the bundle you wish to download and then the .7z file you wish to download. Pay attention to the vintage tag as you’ll want the most recent quarter’s data.
Be sure to extract the .7z package after you’ve downloaded it. We recommend creating a new folder that you will extract into – instead of extracting everything onto your desktop.
If you haven’t received your US/CAN Business Insights hard drive yet, or don’t see the Data Packages tab on the Licensing and Downloads portal, you should reach out to our Fulfillment Team for support.
Local Data Installation Steps
The local data install is the standard installation type. First, you’ll want to make sure Alteryx is closed. Next, either plug in your Alteryx Data hard drive or navigate to wherever you extracted the data from the Licensing and Downloads portal and run the ‘AlteryxDataInstall.exe’ to launch the Alteryx Data Installer.
1. Click Next when the Welcome Screen appears.
2. Read and Accept the license agreement then click Next.
3. Select the data sets you would like to install. If you want all of them just click the All button on the right. Otherwise, you can select individual Data Products by selecting the check box next to them. After you’ve made your selection, click Next.
4. Choose any previously installed Data Products that you would like to uninstall by selecting them similarly to the previous screen. You don't have to choose anything here if you want to keep everything, however, keep in mind that these data bundles can be very large and you may not have enough space to keep multiple vintages installed locally. After you’ve made your selection, click Next.
5. Leave ‘Install to a Local Directory’ selected and browse to the file path you would like to install the data to. The default path will be auto-populated but if you'd like to install it somewhere else just update the path. Make sure the hard drive you install the data to has enough space. For instance, the US Business Insights bundle takes up over 180GB and we recommend 500GB of space for Alteryx Designer – so you will want to have at least 700GB available.
Once you’ve selected the install path, hit Finish and let the Alteryx Data Installer run.
Feel free to kick back and relax now – as this will take some time. An install of the entire US Business Insights bundle may take well over 3 hours depending on your hard drive write speed.
6. When you get to the ‘Complete’ screen you’re done. Now you can load up Alteryx and start diving into Business and Location Insights!
If you’re unsure where to get started with actually using these Data Products, then be sure to check out Part 2 of my Data Products 101 series. Stayed tuned for more to come!
The ConsumerView Matching macro enables users to match their customer file to the Experian ConsumerView data. Starting with customer information such as name and address you can leverage the ConsumerView macro in Alteryx to append a variety of information about your customers such as household segmentation, home purchase price, presence of children in a home, estimated education and income levels, length of residence, and many more!