Testing Snowflake vs Azure

As I mentioned in my last post, I’ve been testing several different cloud data warehouses.  Though I considered AWS Redshift and Google Big Query, I concluded they don’t have the feature set or compatibility with other tools that we need since our systems are fairly complex and relational and not at the enormous petabyte scale that Redshift and Big Query are designed for.  I settled on snowflake and Azure as my final two options.  My first choice was to try Azure’s offerings since I assumed that would be the most painless transition from our old SQL server databases.

My conclusion is that Microsoft is heading in the right direction but they still have a long way to go before making a truly cloud-optimized platform.  They currently offer Azure SQL Database as well as Azure SQL Data Warehouse.  SQL Database is basically just a cloud version of traditional SQL server.  I moved several databases up there to test it.  The two main things I liked over on-premise SQL Server were that you can scale the performance up and down as needed and they have introduced some Artificial Intelligence based database optimization tools which are a big help.  The scaling of the service was much easier than with an on-prem installation (obviously) but it was still quite complicated to automate requiring you to build a powershell script to scale it up and down on a regular basis over night, for example.  You’d think Microsoft would just create a simple auto-scaling or scheduled-scaling GUI to do this.

Continue reading “Testing Snowflake vs Azure”

Starting a New Chapter


Continue reading “Starting a New Chapter”

Why We’re Migrating to EXASOL!


Continue reading “Why We’re Migrating to EXASOL!”

Fuzzy Joins for Grouping Values Into Buckets

At my company we often work with panel data and need to be able to easily group individual panelists into buckets or profiles.  On a recent project I needed to identify which panelists were Light, Medium, Heavy or Extreme buyers within specific product categories based on how many times they purchased that category each year.

The criteria for what constitutes a certain buyer group was different for each category so I couldn’t just use a simple mathematical formula to figure out which group each buyer falls into.  Instead I needed to compare my panelist data with a criteria file to identify which group a user falls into.  Since this kind of task is commonly encountered and cannot be solved via a simple join, I thought I’d write a quick post explaining how I solved this issue with Alteryx.

Below is a screenshot of my panelist data showing the transaction count for each user ID/Category combination.

Continue reading “Fuzzy Joins for Grouping Values Into Buckets”

Calculating Correlations and R Squared Values with Alteryx



Continue reading “Calculating Correlations and R Squared Values with Alteryx”

Dynamic Field Renaming in Alteryx

In my work at TABS Analytics, I often find myself building re-usable alteryx workflows for processing sales data coming from syndicated sources such as Nielsen or IRI.  The goal is to build tools that can be applied to data coming from multiple different clients.  Though the basic content of the data is the same, the field names and format are often different depending on the client.  A basic measure such as sales dollars, could come with many different titles such as (Dollars, Sales Dollars, DOLLARS, Value Sales, etc.)  In this example I’m going to walk though how I use the dynamic rename tool to “iron out” these variations up front to avoid errors downstream.

Imagine that we have two different data sources with slightly different field names as shown below.

The goal is to build a workflow that can load both data sources, properly identify the fields, and process without errors.  In this case, the first thing we’ll need to do is rename the fields so that they are consistently identified throughout the rest of the workflow.  The standard Select tool won’t work since it will throw errors if you try to map two different raw field names to the same output name (i.e. mapping Dollars and Sales Dollars to both be renamed as just Dollars).

Continue reading “Dynamic Field Renaming in Alteryx”