How to Import SFDC Recursive Hierarchies to Tableau

If you have dealt with hierarchies from Salesforce (SFDC), or from most normalized transactional systems, you have faced some challenges to use those hierarchies in Tableau. Those systems treat hierarchies as a recursive model, storing Parent to Child relationships in only 2 columns. The recursive model approach to hierarchies is very efficient in terms of storage, and flexible as it allows for a variety of levels and branches of different lengths. Yet, usage relies on User Defined Functions which are not available in Tableau, as it would break its columnar aggregation logic. There is a quick and dirty approach in Tableau: rebuild the hierarchy using Groups, but it is limited to 2 levels and requires manual maintenance in Tableau, not ideal… A real hierarchy in Tableau requires each level to be defined as its own column in the data source. Breaking down the hierarchy in levels is very desirable for Tableau or any other BI tools really, as it enables filtering and aggregating by any node.

Continue reading
Posted in Alteryx, Automation, Marketing, Sales, SFDC, SQL, Tableau, Uncategorized | Tagged , | 4 Comments

Snowflake Summit 2019 Debrief

I was very excited at the thought of attending the first edition of the Snowflake customer event In San Francisco last week, and the event did deliver…

Continue reading
Posted in Alteryx, Automation, Performance, Snowflake, SQL, Tableau, Uncategorized | Leave a comment

Classify records based on keywords in a text field

Have you ever been tasked to assign customers to a category based on a free form text field? This happens a lot in B2B marketing, using the Title that a prospect has input into a form. It can certainly be done manually, but with larger data sets, automation becomes a must have.

There is no magic wand to perform this classification, but if you already have narrowed down a list of keywords to test with, this can be done pretty easily with Alteryx and a bit of Regex know how. As an example, I will use the file of reviews used in a previous post, which contains a lot of text and many records, to demonstrate the performance of the solution. The scenario is that I need to be able to distinguish reviews of bars vs. reviews of restaurants, and I want some flags so that I can filter them easily.

Continue reading
Posted in Alteryx, Marketing, Quick & Dirty | Tagged | Leave a comment

How to edit Salesforce credentials within several Alteryx workflows at once

In Alteryx, the Salesforce connector is very useful to easily import raw data from Salesforce using its wonderful API. All you need to input is the email and password for a API enabled user login, as well as the Security Token.

The connectors are so convenient to combine SFDC tables, that you will quickly have several within a single workflow, and you will most likely have several workflows, that you spent countless hours to configure, as Salesforce tables can be VERY long. For instance, you must select the proper combination of fields to extract, as Salesforce tables, especially the custom ones, are laden with open text fields, useless for analytics in most cases. Those open text fields are input with very few restrictions of characters and will often trip S3 or your database.

There is just a slight issue with this design: on one hand, the SFDC credentials you did input are stored locally in each connector object you deploy, on the other hand, most SFDC logins require episodic password changes as they are set to expire. And should that happen, you will have failing workflows, unless you tirelessly inventory the location of each and painstakingly process to edit them. In my experience, that is not the end of the suffering: when you click on the connector to go edit the credentials, Alteryx Designer sends a request to SFDC featuring credentials, and after a few of those requests, sometimes just a handful, SFDC will lock your login out! You will then need to either get an admin to unlock the account, always a fun conversation to have with an admin, or wait one hour for SFDC to automatically remove the lock.

Continue reading
Posted in Alteryx, SFDC | 4 Comments

Alteryx INDB Traps and Limitations Series: Filtering Nulls

I get to spend a lot of quality time with INDB tools, which represent 90% of my workflows. I love them, I can generate code almost at the speed of thoughts. They are fast to put together but also very powerful, especially when coupled with modern data bases such as Redshift or Snowflake, as they don’t hog any local resource.

Yet, I have been running over time into a series of issues and limitations of those tools, far from being obvious, and which all caused perplexity and time spent to address. I will share them through a blog series, and will start with a trap easy to describe, yet lethal in terms of its impact on data integrity: INDB Filter does not handle NULLs by default, unlike its cousin the In Memory Filter.

Continue reading
Posted in Alteryx, Redshift, Snowflake, SQL | Leave a comment