Archive

Archive for the ‘Alteryx’ Category

Using Alteryx for Data Quality checks

November 15th, 2016 No comments

Data Quality is now a rapidly growing area in many Financial Services organisations. There are multiple vendors, such as Informatica and Ab Initio, with software specifically marketed as a data quality tool. Undoubtedly they are both great products, however they are expensive, in all likelihood in the majority organisations would take significant time before they are approved for purchase.

This is where Alteryx can step in, it’s a great tool to very quickly implement a data quality solution. The price point will not deter the majority of FS organisations and the ongoing administration is not an expensive burden.

Most data quality checks are highly specific, hence are bespoke and unable to be standardised. A business rule against a specific data point in a specific data set is a unique check. For example in the financial services world there are a number of rules defining an ISIN. Depending on the country code (first 2 letters of the ISIN) the remainder of the ISIN could have a specific format and a relationship to other data points, such as a CUSIP.

For something as simple as an ISIN there are actually many specific business rules to identify whether it is correct. In the data quality world each business rule is another quality check. These quality checks all require writing to mirror the business rules.

From the technical perspective Data Quality is actually an ETL process.

  • Extraction: the source data needs to be sourced and brought into the quality check
  • Transformation: the business rule check, transforming the source data into a check result
  • Load: the capture of the results

In Alteryx terms a workflow can hold a number of checks against the same data set. A data set is Input to the workflow, the business rules are written using Formula tools and the results are Output.

Significantly accelerate the writing of quality checks by creating an appropriate Data Quality Check template workflow in Alteryx. The inputs and checks (formula tools) are easily modified and the outputs should be standardised for streamlined reporting of the results.

Using Alteryx the technical side and automation of Data Quality checking can be achieved very quickly. Get in touch if you would like to learn more about our tactical Data Quality solution.

Categories: Alteryx Tags: ,

Move or Copy files in Alteryx

July 26th, 2016 1 comment

In previous posts I’ve written about how to use the Run Command tool in Alteryx to publish a tableau extract to tableau server and how to unzip a file in Alteryx. Another great use for the Alteryx Run Command tool is to move and copy files.

To move files from a directory into another directory the following information is required:

  1. File path of file to move
  2. Destination directory

Set up the workflow in the following way:

1. Enter the above fields in a Text Input tool, creating the fields FullPath and DestinationDirectory

Alteryx Move Copy Text Input

Note: The backslash at the end of the DestinationDirectory is very important. Without that a new file would be created called Engine in D:\Alteryx. Read more…

Categories: Alteryx Tags:

Unzip a file in Alteryx

April 21st, 2016 3 comments

I question often seen on the Alteryx forums is how to unzip a file in Alteryx.

In an earlier post I covered how to use the Alteryx Run Command tool to publish a Tableau Data Extract to the Tableau Server in an Alteryx workflow using TabCmd.

This post is similar but is showing how to unzip using the Run Command. The Run Command tool is able to call exe programs, meaning anything possible by exe is possible within Alteryx.

Credit to this solution needs to be given to jdunkerley79 who supplied the solution in the Alteryx forum: http://community.alteryx.com/t5/Data-Preparation-Blending/Can-Alteryx-unzip-a-file-as-part-of-the-workflow/td-p/10604

The first thing to do is install software to unzip. Currently I’m using 7-zip (http://www.7-zip.org/). This can be called via the command prompt.

Next is how to use this in Alteryx: Read more…

Categories: Alteryx Tags:

Publish to Tableau Server from Alteryx using Run Command

March 30th, 2016 No comments

One of the things I often read about on the Alteryx forums is people asking how to publish to Tableau Server from Alteryx. Previously I wrote a post about publishing to Tableau Server from Alteryx using the Events functionality. A downside of this is the timing of the publish to Tableau – it either happens before or after the workflow has run. To publish to Tableau server from within an Alteryx module the Run Command tool can be used, meaning publishing from Alteryx to Tableau Server can happen from anywhere within the worklow. Read more…

Install Alteryx non admin version

February 10th, 2016 No comments

Installing the Alteryx non admin version was a slight challenge as I couldn’t find the right link simply from the Alteryx home page.

Therefore here is a link taking you to the page containing the latest admin and non admin versions of Alteryx: http://downloads.alteryx.com/downloads.html

Categories: Alteryx Tags:

Publish Tableau Data Extract directly from Alteryx

December 29th, 2015 No comments

For those Alteryx and Tableau Server users the ability to publish Tableau Data Extract (tde) files directly from Alteryx to the Tableau Server is a huge benefit for report automation. There are a number of good posts already published advising how this should be done, such as this excellent guide from Interworks. In this post I’ll replicate some of what is said in the post, should that link break at some point, and also add to it where I ran into difficulties.

To publish to Tableau Server from Alteryx requires using Tableau’s tabcmd. I expect it’s also possible using the REST API from Tableau but I was unable to work it out, so this post will focus on the tabcmd solution, which, in my opinion, is far simpler. If you’re not a Tableau Server administrator in your role you may need to install tabcmd.

Do the following to set up a workflow to publish directly to Tableau Server from Alteryx, using the workflow configuration Events:

Step 1

Open the Alteryx workflow that creates the tde file.

AlteryxOutputToTde

Step 2

In the Events section of the Workflow – Configuration add a Run Command. Also ensure ‘Enable Events’ is checked.

AlteryxWorkflowEvents

Step 3

Choose “After Run Without Errors”

AlteryxEventRunCommand

Step 4

Enter the location of the tabcmd.exe in the Command box

AlteryxEventTabcmd

Step 5

Enter the command line to execute in the Command Arguments section. I would recommend entering this into Notepad first and copying into the Command Arguments section.

For some unknown reason when I typed directly into Alteryx or into MS Word, copying to Alteryx, when the command was passed into tabcmd by Alteryx, the quotation marks in the command string weren’t being recognised causing it to fail. Entering the command into Notepad and copying it into Alteryx helped me get around that problem.

AlteryxEventCommands

Example tabcmd command line:

publish “\\NetworkLocation\TableauDataToPublish.tde” -s TableauServer -u UserName -p Password -t TableauSite -o -r “Data Sources”

Important things to note about the string:

  • publish = the tabcmd being called
  • -s = tells tabcmd which Tableau Server to use. This doesn’t need to be the “https://TableauSite” URL, it is better as the server name as entered to the Tableau Postgres database
  • -u = the username used to login to the Tableau Server (also needs to be set up on the Tableau Server)
  • -p = the password used to login to the Tableau Server
  • -t = the name of the Tableau Site on the Tableau Server to use
  • -r = the project name to publish to. If this is not included the Default project is used.
  • -o = Overwrite the existing tde file with the same name

There are a number of things that can be specified in the tabcmd command string. The Tableau help documentation has a comprehensive list of those items.

Step 6

Ensure the Timeout (in seconds) is set appropriately to give enough time for the tde file to be published to the Tableau Server

 

Now it’s complete, run the workflow and you should no longer need to manually publish tde files.

Important notes:

  • If using the Alteryx server the Alteryx service account will need setting up on Tableau Server with a Publisher role.
  • If tabcmd fails use the tabcmd.log file to see the error message(s). Alteryx will only tell you it failed, the tabcmd.log will tell you why it has failed. This is copied from the tabcmd overview on the Tableau website advising where to find the tabcmd.log:Status messages and logsWhen a command is successful, tabcmd returns a status code of zero. A full error message for non-zero status codes is printed to stderr. In addition, informative or progress messages may be printed to stdout.A full log named tabcmd.log that includes debugging, progress, and error messages is written to C:\Users\<username>\AppData\Local\Tableau.

 

Alteryx output to Tableau Data Extract tde file

October 6th, 2015 No comments

To output data from Alteryx to Tableau is incredibly simple, Alteryx has the output File Format of a TDE file.

Alteryx Output Data Tool

Just select your data, drag in an Output tool and set the file type to be Tableau Data Extract (tde). Alternatively enter a .tde file name into the ‘Write to File or Database’ input box and it’ll default to a Tableau Data Extract File Format.

If you don’t have a tde file already created, Alteryx will create it for you – although this isn’t 100% clear. Type the name and location you want for your Tableau Data Extract in the ‘Write to File or Database’ location and Alteryx will create and put your tde file in that location.

Alteryx Tde Output Configuration

In the Output Options you can now append to an existing Tableau Data Extract file, meaning you don’t need to recreate the entire tde every time, particularly useful if you have incremental data. If not then go for the Overwrite Existing Extract File (Create if does not Exist) option.

Starting out with R in Alteryx

February 17th, 2014 Comments off

One of the great things that makes Alteryx so useful is the integration with R, making advanced statistical analysis possible for those without the ability to code in R. As part of my Alteryx training series the R integration is one of the areas I believe is key to learn.

If you are new to Alteryx and would like to try out using the R integration the first thing to do is install the R predictive tools.

Go to Help – About to work out which version of Alteryx you have installed and subsequently which of the R installers to install.

When starting out using Alteryx and the R functionality I suggest using the built in Alteryx R demo projects.

In File – Open Sample – Predictive Analytics there are a lot of projects already built using many of the R predictive analytics components. Work through those, get a basic understanding and then you can call yourself a data scientist 🙂

Alteryx Predictive Analytics Samples

Categories: Alteryx Tags: , ,

2012 Primary Schools Performance in Tableau

February 8th, 2014 Comments off

I wrote an earlier post as part of my Alteryx training using 2012 UK primary school data. As per this post on joining data in Alteryx, using the trade area tool in Alteryx and exporting the data into a TDE file, a Tablea Data Extract, I have now put the data into Tableau. In a couple of years when my son is a little older I’ll have to recreate this with up to date primary school data.

Alteryx and Tableau to display UK schools data

February 6th, 2014 Comments off

As part of my Alteryx training, following on from my starting out with Alteryx, I decided to try and use it for a real world example to test it out. I recently had a baby so have to start thinking about schools. Luckily the UK government make school performance data public and the excellent Guardian Datablog have tidied it up for me (the data is for 2012, hence now out of date, but good enough for my training in Alteryx). The data contains school address details, local authority details, school size, school religion and multiple measures of performance.

I would like to know which schools are performing well – i.e. their pupils have high attainment in their exams – and then see which streets are within X miles to help guide me which streets I would need to live to get my son into a chosen school. Alteryx has an in built function called Trade Area where it can map show you a chose radius from a point, which is ideal for the schools approximated catchment area analysis.

Radius of 0.3 miles around each school

Radius of 0.3 miles around each school

Each purple dot is a school with the cirle around the dot being the approximate catchment area, although looking at this it appears the catchment area in London will, in reality, be less than 0.3 miles. Read more…