Inspired Ingenuity or Overly Engineered? (Part 2)

So I think my solution was probably inspired somewhat by my gantt chart module.  I was trying to visualise the problem and realised I could actually turn the problem into a spatial one and then use Alteryx's spatial tools to solve it.

So here it is:

Imagine a rectangle for each task sitting on a time line (like a gantt chart but with all the tasks on one row).

If we make the length of the rectangles proportional to the length of the tasks,  then we can say the area of the rectangles represents the time taken for that task.

Now imagine another rectangle on the same time line representing the entire reporting period.  The area of this rectangle (in green below) represents the total time available.

Now if we cut the smaller task rectangles from the large reporting period rectangle, then the area remaining of the larger rectangle is the unused time.

With the spatial tools of Alteryx, cutting one set of polygons from another is easy.  So that's what I did:

You can download the module here:



I think you'll agree a rather unique way of using the spatial capabilities of Alteryx in a way they were never designed for.

So where does that leave my Occam's razor?  Well Ned has already sent me his solution and I have to say it doesn't involve any spatial tools and if I'd manage to think about the generate rows in the right way it would have been a quicker solution to implement than mine.  My module does run more quickly (0.9 seconds against 2.0 seconds on my machine) but the report only gets run once a month in the middle of the night so speed isn't really a necessity...

You can download Ned's solution here:



I think this one is a simpler solution than mine and for somebody opening the module to make changes in the future somewhat less daunting. So, without the necessity of a fast runtime, by applying Occam's razor I think Ned's is the better solution.

However having said that I hope that my solution has been interesting and might inspire some future spatial solutions to non-spatial problems.

Inspired Ingenuity or Overly Engineered?

Inspiring Ingenuity is one Alteryx's taglines and I think the module I will cover in this post is a great example of a way to solve a problem using the tools in a way they were never designed for.  However (and hence the title of the post) I wonder whether my solution is clever for the sake of being clever and in reality there is a simpler solution which I have just overlooked.

I believe strongly in an "Occam's Razor" approach to problem solving and Alteryx modules are no exception.  Occam's Razor is stated in a number of forms and really relates to scientific hypotheses.  Wikipedia says

 The principle is popularly summarized as "the simplest explanation is usually the correct one."

I've also heard it stated as "The simplest solution is the best solution".  Now this isn't quite the version I subscribe to.  Consider the example in Alteryx of needing to match against a large lookup file of several million rows on a regular basis.  Well the simplest solution would be two input tools, a join and browse.  But is that the best solution?  Well if the time it takes for the module to run is important to you, then no.  A better solution would be to load your master file into a Calgary dataset, index the columns you need to match on and then rebuild your module with an input tool, Calgary append and browse.  More work to set up, but in the end a better solution.


So where does that leave our Occam's razor.  Well continue down the Wikipedia article and we have this version

"pluralitas non est ponenda sine necessitate" ("plurality should not be posited without necessity")

in other words complexity in a solution should only be there if it is necessary. So in our above example we introduce the extra complexity of loading our data into Calgary to make our solution run faster.


Which brings me to a question about my module.  Is the complexity of my solution necessary or can somebody think of a simpler solution?


First the problem:


We have an Alteryx server which can run up to four Alteryx modules simultaneously.  Every time a module is run the start and end time of that module is logged in a SQL server database, which is then used for looking at  how the server is being utilized (using Alteryx to generate the reports of course).  


So one of the metrics we are interested in is server utilization:  how much of the processing time of the server did we use last month?  How much more capacity have we got until we need to get a second server? 


And the difficult question: 


How much time is the server completely idle? 


If the tasks were non overlapping this would be as simple as calculating the time for each of the jobs and subtracting the total of these from the total time for the reporting period. But the tasks do overlap...


I'm going to leave you to think about possible solutions and post my proposed solution in the next post.  If you want to have a go then I've provided my example data below to see if you can match my answer.

SAS Transpose

Last week I visited a client who had been trialling Alteryx.  The team who had been conducting the trial were SAS analysts and were particularly impressed by Alteryx's spatial capabilities.  At the end of the meeting I asked if there was anything which they thought they could do in SAS and not in Alteryx.  The reply was the only thing was that the transpose tool didn't really to seem to behave as they expected given their experience of SAS's Proc Transpose.

So this weekend I thought I would take a look at what the differences might be.  Now it has been a long time since I have written anything in SAS, so first step was a quick google to find out how the Proc Transpose tool worked.  I came across this excellent tutorial:

http://support.sas.com/resources/papers/proceedings09/060-2009.pdf

I decided I would recreate the lessons from this document using Alteryx.  Upon starting I realised why they had been having trouble, the Proc Transpose function of SAS is closer to an Alteryx cross tab than transpose,  but in actual fact to reproduce the results exactly Alteryx needs a transpose and cross tab tool. 

Just to be clear it is not that Alteryx tools aren't as efficient as SAS they are just different and I have added an extra example to each lesson which I have titled "The Alteryx Way" which shows what I think an Alteryx analyst would do to transform the example data.

I could have tried to turn my modules into a SAS Proc Transpose macro for Alteryx however I don't believe that would be the most efficient way of doing things in Alteryx.  I am not presenting these examples as the best way of doing things in Alteryx; more of a way for SAS analysts to get a handle on the differences between the two tools.  I think if you are going to program efficiently in any language you need to think about your solution in terms of how that language works.

My example modules can be downloaded below:



As always feel free to post any comments/questions here or e-mail me directly.

Alteryx Team Blog

The Alteryx team blog has a new home and name.  Check out the "Alteryx Engine Works" here:

http://blogs.alteryx.com/engineworks/default.aspx

Comments are also working at last, so hopefully we'll see some questions from the community on the new features of Alteryx 6.0 as the team blog about them.  First post on Document Constants is already there and looks like a powerful new feature.

For anyone who is subscribed to my google RSS Alteryx bundle then I have updated this to include the new "Alteryx All Blogs RSS Feed".  For anyone who isn't subscribed then the below link gives you access to all of the Alteryx RSS feeds in one place:

https://www.google.com/reader/bundle/user/08686630194161749278/bundle/Alteryx

On a related note the UK Alteryx Blog now has e-mail subscriptions available courtesy of feedburner.  Blog posts delivered directly to your inbox. Sign up at the below link:

Subscribe to The UK Alteryx Blog by Email

Palette Importer and Exporter

Seeing this post http://srcgroup.extendthereach.com/showthread.php?t=1414 by Margarita on the Alteryx forum reminded me of an Alteryx module I had started a while ago to try and solve a problem with theming on a map.  The solution didn't quite work out as I had hoped, but I believe the module may still be of use.

So here is my palette importer module for Alteryx:



It allows you to add palettes from Expression Design Swatch xml file format (or entered manually in a text input) to your Alteryx reportsettings.xml file.  The reason I chose this format is that I found this site http://www.colourlovers.com/palettes which has over a million palettes to download in this format (though frustratingly they all only seem to have a maximum of five colors, if anyone knows of any better sites please let me know).  You need to sign up to download them, but then just hit the design button under download options:

The module will pick up any number of palette xml files from the same folder that it is in.  I have included two as samples.  Once the module has run then you will need to restart Alteryx in order to be able to see the new palettes.

As Margarita suggests in her post please backup ReportSettings.xml before you start using this.  It is tested, but just in case there are any problems you will want to be able to restore your backup.  The other thing to mention is that if you import a palette with the same name as an existing one then the existing one will be overwritten.

It allows you to relatively quickly find a palette you like; import it into Alteryx and use it:

I have also put together this module:



which exports your palettes from reportsettings.xml to Expression Design Swatch xml file format files.  Ideal if you want to move a palette from one machine to another or share it on the web.

As always any comments/suggestions please let me know.

Gantt Charts

The below recently appeared on the Alteryx forum:

Hi,
I have a an Alteryx module that reports project data and milestones for various projects. What is missing is a visual representation of the milestones and I was wondering if anyone had used the Alteryx reporting tools to create a Gantt Chart type view of milestones/tasks.
Thanks,
Gavin

Hello Gavin,

We do not currently offer a Gantt Chart option in the Chart tool. Thank you for expressing an interest in this feature, we will look into it as a possibility for the future.

-Brett

While Brett's reply is quite correct; there is nothing to stop you creating your own Gantt Chart using the tools currently available in Alteryx 5.0.  If anyone remembers the techniques which were used to create a polar graph or a chess board which used the mapping tool as a custom renderer, then a similar technique could be used to create a Gantt chart.

So I've put together a sample module which shows a possible method for doing this. Which produces the below:

From the following data table:

The module uses a couple of interesting techinques including using the Generate Rows tool to create a data row for every day between two date ranges and generating the spatial objects needed to build up the Gantt charts by dynamically creating json objects (Thanks Ned).

The module looks like this:

and can be downloaded from here:

The things that need doing next with this are to take a look at the scaling aspect (it currently doesn't look so good for timeframes longer than a month) and turn the whole thing into a macro which outputs a report .

As always any questions/comments please post below or feel free to contact me directly.

Add Total Row and Column

Ever needed to add Row and Column totals to your data tables in Alteryx?  This macro makes it easy!

From this:

 

To this:

With this:

Download the macro here: