Alteryx – First and Lasting impressions

If not for the new job that I have joined I would not have heard of this tool at all. Alteryx is another kid on the block that is mix of ETL / Analysis / Reporting all blended into one. The reporting part hasn’t impressed me much but some of the the ETL/ Analsyis features that it has just blew me away. In this post I would like to point out few key areas where I feel this just aces miles ahead of SSIS (or) Informatica.

Installation
You just need to go to the site, click on the Download Now. This would then prompt you to register and you MUST enable to their subscriptions. Once done, you would have 14 day trial of the product. It’s that simple.

Preview Data at every single stage post run
At each stage of transformation one can see the data before and after the transformation post the execution of the workflow. I just can’t fathom how are they even doing this. Let me show you with an example

Here is a simple workflow that is taking student data as input, doing some check on Gender (if Male/Female) and then cocatenating first name and last name for both the flows.

1_StudentImport_Overview

If you are coming with a ETL background, you would be able to quickly latch on to the transformations and what they do because they are so intuitive. Even an hours video from youtube would be sufficient to quickly scale up on transformations. If you notice each of the transformations above, there are green button like icons before and after. They are basically input and output (as if you didn’t decipher already). Now let’s say the workflow is run. Post run, I can click on any of these buttons to see the data at that particular stage.

Let me pick ‘Identifiy Gender’ transformation. Once you click on the transformation, all the inputs and outputs of that transformation are available to be previewed. Seen below is how the input data looks like. My condition was to seperate them by Gender –
2_IdentifyGender_Input
If I want to see what the ‘T’ output rows look like (i.e. Males), I just click on it –

2_IdentifyGender_True
Now let me have a look at the ‘F’ output rows –
3_IdentifyGender_False
Imagine this being the case at every stage of the transformation. It’s just incredible to be able to see how your business rules are working at every stage.
Let me just repeat one more time, if you haven’t read the sub heading, this preview is POST-RUN. I just can’t think of any alternative for this in either SSIS/Informatica.

Testing the DFT without Output-
Just scroll little back up and see the screenshot of the workflow I posted. It doesn’t contain an ‘Output’. The last transformation that you see is just a UNION ALL. Say if you are developing a POC or just testing somethings out, you avoid the necessity to create a destination and then dumping the data. Of course, Trash Destination comes quickly to mind from SSIS stack but that’s an add-on and not out-of-the-box feature. I can’t think of any in Informatica though.

Multicasting
Pretty much every single transformation’s output you can multicast and then branch off to do some entirely new logic altogether.

Dynamic column propogation-
I have been saving the best for the last. It has this incredibly advanced capability of bringing in dynamic columns just as if they have been there all along.  Let’s say in the data above, I perform two changes to the input file –
-Added new column say Location at the end
-Added new column Is Married after ‘Last Name’ column

Without doing any changes to the workflow, it just runs without throwing any error and here is my output from ‘Union’ transform –

4_ColumnAddition_WorkflowRun

HOLY COW! It’s just mind-blowing, ain’t it. In terms of data modifications, it was pretty drastic, as in new columns were added not just at the end but also in between, and Alteryx just doesn’t care about it. It just works!

I am sure, I am just scraping through the tip of the iceberg and there is HUGE amount of exploration left to do. What is also fantastic about this product is the community behind it.

Their community forums and learning channels are all free for anyone to ask and learn much like MSDN community or Informatica ones. They have weekly challenges running which are good fun to flex your muscle and give it a try. The whole interface though I feel they can improve on. I feel bit claustrophobic with all the overbearing green color theme and design but you get used to it.

All in all I am loving it. Watch out for future posts where I detail how it fares performance wise, error handling, configurability, looping, dynamic data parsing etc.

Indespensible Dev Tools

This is a post about some of the tools that I make sure are available on my system and which I have found to be highly indespnsible to my work

Greenshot –

It’s been almost 6 years now since I have been first introduced to Greenshot by our onsite manager who adviced on this fantastic piece of software for capturing screenshots. It’s absolute light-weight open source software. It takes over the Print+Screen functionality and can capture, some or whole page, adding annotations. Here are the main highlights which I feel are the best use of this software and that I routinely use besides the obvious annotations are  –

  • Capturing only certain region and ability to mail right away
  • Easy annotations
  • annotation-example
  • Obfuscation of selected area

obfuscation-example

  • Copy to Clipboard (THE most important one)
  • Add Counters – the best feature as shown below. It comes so very handy when doing documentations and detailing deployment steps. I just can’t thank them enough for incorporating this.The bonus is using these counters along with annotations, you just don’t need to write anything at all in the steps.add-counters-example
  • Ability to just delete the parts you don’t need and move around the objects. What I mean by that is in the above example, you can just remove the obfuscation or remove the number 3, or move that around to a different place. It’s just like multiple objects on your image but you have the freedome to chose and pick what you want.

No other alternative that I have used so far has even come close to this tool’s super lightness (occupies only 20 MB when not in use) and ease of use with some unbelievable features. One of the worst offending ones is the behemoth Snag It that just not only eats up your RAM but has tons of bloated features that feels over done and also the most obvious problem of not having a clean uninstall.

Download it from – Greenshot

Stickies

Stickies from Zhorn Software is again one such tool which, once you start using it, you would wonder how did you manage yourself without it. Again it’s absolutely free. They are the virtual version of real life stickies and once you install you will be presented with the ever so familiar stock stickie on screen. Some of the features that I most often use and the reason why it stands out from the plethora of wanna-be’s are  –

  • Ability to pin one up so that they are always even if there are multiple applicaitons running
  • Ability to copy and paste images, text
  • Setting alarms
  • Ability to hide them for certain period

Apart from these there are other plethora of features that I haven’t used yet. I mainly use them to categorize and store frequently used information such as server names pertaining to environment, file locations, making a to-do tasks, adding reminders etc.

You can get it from – Stickies

Note: Just the mere search of Stickies would throw up this as the first result should be a reason enough to know how fantastically popular this is.

SSMS Tools Pack

As the name suggests it’s a tool pack that packs power to the punch. It’s an add-in to SSMS and has variety of features and some of the ones that I use the most are –

  • SQL History – all the queries executed in a given script and session are stored in a dockable format in the SSMS using which it is very easy to scroll through the history
  • Insert Statement Generator – You can generate insert statements right from a specific table or all tables or even through your query results.
  • Search Database,Tables, View Data – Search through the whole database for a specific value.

Of course there are many more than the ones above. In the SQL Server world and especially talking about SSMS there are SO many out there and the one that immediately comes to everyone’s mind is the Red Gate stable. They are just different league altogether and servers seperate use case. The advantage of SSMS is it’s cheap and for SQL Server 2008 R2 and before it’s absolutely free and it’s best bang for a buck.

You can download it from – SSMS Tools Pack