Release of Alteryx Omni-Bus Tools v0.4 (previously known as JD Tools)

AlteryxOmnibus

This is in many ways related to the Beyond Alteryx Macros series, and how it all works will be described in detail in later posts within that series.

This release is primarily a tidy up of the framework and fixing some naming mistakes in the code. It also includes the release of the first proof of concept tool around using the Roslyn compiler within Alteryx (see the section below for details).

To install the tools download the release from here. Extract the zip file to a location where you will keep the files (Alteryx will run the tools from this location), then run the Install.bat. You may get Windows Smart Screen warning this is from an unknown source, and you should expect to get a UAC prompt as it need to install to Program Files.

If you wish to install the prototype Roslyn tools then run RoslynInstall.bat.

Following install all the tools should be visible within the Laboratory section of the ribbon:

image

The tools are tested on Alteryx 10.6 with an Admin Install. They have also been tested within the current Beta 1 of version 11. Installation for an Admin Install requires administrator permissions on the machine.

While currently there aren’t many sample workflows, each workflow has one or more test workflows. These show you how to use the tool and provide some sample output. You can download these here.

Finally, should you wish to uninstall the tools you can run the Uninstall.bat script. This will remove the files which tell Alteryx to load the add ins.

Should you have any issues or any feature you want added, then please raise an issue on the GitHub project here, or reach out to me on the Alteryx community or on twitter.

Current Tools:

Date Time Input

A slightly expanded version of the Date Time Now tool. It will create a date field equal to one of the following values:

  • Today
  • Now
  • Yesterday
  • StartOfWeek
  • StartOfMonth
  • StartOfYear
  • PreviousMonthEnd
  • PreviousYearEnd

Circuit Breaker

This is an expanded version of the the Test tool. The idea here is that the input data (I) is only passed onto the rest of the workflow if there is no data received in the breaker input (B).

Date Time Parser

This exposes the .Net DateTime parse functions to Alteryx. Parses a date from a text field and adds to the output.

As this tool supports all formats that the DateTimeParseExact function does, it should deal with parsing all the random formats that keep come up. This tool supports different cultures allowing parsing of any supported date formats.

This tool has had a lot of performance tuning and operates reasonably close to the native DateTime tool but allows any format (or allows .Net to attempt to match any it recognises).

Number Parser

This exposes the .Net double parse functions to Alteryx. Parses a number from a text field and adds to the output. Again, this tool supports different cultures allowing parsing of any supported number formats. Specifically, this means you can bring in numbers with thousand separators and in German format.

String Formatter

This tool can be used to convert from a numerical or date or time field to a string field. It supports different cultures and number formatting. Pretty much the inverse of the two Parser tools.

Hash Code Generator

This exposes the .Net System.Security.Cryptography HashAlgorithms to Alteryx. It takes and input string and computes the hash value. It supports MD5, RIPEMD160, SHA1, SHA256, SHA384 and SHA512.

Random Number

This generates a random number based of a sepcified distribution (currently Linear, Normal and LogNormal). Based of the Math.Net Numerics package. Can be seeded to allow the same sequence each time.

HexBin

This reproduces the hexagonal binning functionality built into Tableau. It is based of the d3 HexBin plugin. It defaults to a radius 1 hexagon but you can specify different sizes.

Sort with Culture

This tool exposes sorting with a culture from .Net. This was an experiment and there is actually sorting with culture provided in the main Sort tool.

Roslyn Input

Currently a proof of concept tool. It allows you to write C# code and have the results be pushed straight into Alteryx as a data flow. It has support for syntax highlighting and pushing one or more records. This is not a final version and will under go many changes in the next versions.

Known Issues

  • Browse Anywhere
    At the moment, there is no support for the writing to the Browse Anywhere system from within the .Net SDK. I believe the JavaScript SDK is going to get the functionality so hopefully the .Net SDK will as well.
  • Tool Group
    Since version 10.5 the designer doesn’t pick up the Tool Group specified in the Plug In ini file is not being picked up, at the moment this means all the tools end up within the Laboratory group. Later versions of the tools will come with an installer
  • Large Scale
    The Circuit Breaker and Sort with Culture tools both hold an in-memory set of data. At the moment there is no access to Alteryx’s intelligent caching system so these two will have memory issues on a large dataset
    .
  • Insufficient Documentation and Examples
    Currently just have the unit tests, need to create a wiki
  • No Installer
    Just a batch file to install and uninstall

Deep Down The Rabbit Hole: The Roslyn Experiment

When you find software that works for you and you understand it, stick with it.
Go deep, learn it, love it, and use it all the time. Never stop learning.
Ken Black

image

For a little while I have been wanting to attempt to integrate the Roslyn compiler into Alteryx and allow you to create a .Net script that it uses at run time. This release includes the first proof of concept tool on these lines. This allows you to create a lambda expression returning either a single object or an enumerable set of objects (must be an IEnumerable) which the tool will push into the Alteryx.

Currently you are limited to the System, System.Core, System.Data and System.Xml assemblies. Future versions will add support for specifying you own references. Likewise the using statements are fixed to:

using System;
using System.Collections.Generic;
using System.Linq;

The current editor (based on a Syncfusion Edit control) gives you syntax highlighting but no Intelli-Sense. Longer term I hope to improve the edit experience dramatically. Likewise the error messages from the compiler are still pretty raw, again I hope to make better in the next few releases.

image

At present there is no input data, I would like to have a few variations on this tool either taking individual records (allowing a C# based formula tool) or taking the collection of records (either one by one or en mass, allowing some aggregation work). There is a huge amount of potential and power in this space I think.

How It Works

When you enter code into the designer, it is passed to the Roslyn compiler and compiled in a background task (so the edit experience feels ok). This works by putting the lambda expression into a template block of code:

namespace Temporary 
{
    public class ExpressionClass
    {
        public static Delegate CreateDelegate()
            => MakeFunc({lambda});

        private static Func MakeFunc(Func code)
        {
            return code;
        }
    }
}

This code is then parsed into a C Sharp syntax tree and compiled via Roslyn. Any diagnostic messages are captured and returned to the GUI in a display below the editor. Once you are happy the C# code is stored in the XML configuration of the tool.

The designer will then ask the tool to provide the metadata for the output stream. The compiled assembly produced by the tool is loaded kept in-memory and the CreateDelegate() method is invoked. This returns a Delegate object which is of type Func. Wrapping it in a Func allows the tool to determine the return type of the function, and also permits use of anonymous types. Using reflection, the type’s properties are converted into a set of columns (please note this does not at present support recursion into complex types inside the top type). If the type T is an IEnumerable<>, then the generic parameter is accessed and it’s properties are used instead. If it is a simple value type (e.g. a string or DateTime), then just a Value column will be created.

When the engine runs this tool, the code is compiled during the initialisation step. The engine will then call to push all records to the workflow. If the returned object is a lazy evaluated IEnumerable<> then each record will be pushed as evaluated by a for each loop.

Now onto a few simple examples…

Hello World String:

The simplest example:

() => "Hello World"

This will push record to the records like:

image

Hello World Anonymous Type:

This is the default code provided in the tool.

() => new { A = "Hello World" }

While the output is the same as the string example (a single row), the anonymous type allows you to specify the name of the column so the result becomes:

image

Multiple Fields and Types:

Expanding on the simple Hello World class, this just adds a set of different fields:

() => new {   A = "Hello World",    B = 123,    Date = DateTime.Today  }

This creates a single record with 3 columns, but shows you conversion of strings, ints and DateTimes:

image

Enumerable Generation:

You can use the Enumerable functions and Linq Select to create rows of data. The enumeration will be lazily evaluated by the tool pushing each record as it is evaluated.

() => 
Enumerable.Range(0,1000)
.Select(a => new { 
    Value = a, 
    B = "Hello World" 
})

The result in this case is a 1000 rows long, each consisting of a row number and a text field:

SNAGHTML1f606a76

Not using a Lambda:

You don’t need to use a lambda. If you prefer an anonymous delegate then something like:

delegate {
    return new { User = Environment.UserName };
}

works perfectly well, returning the current user name.

Finally if you want to go to a full named method, this is possible. I am not guaranteeing this method will always work as it will be sensitive to any changes to the wrapper. This method gives you access to creating an iterator (using the yield command), but you lose the anonymous types possible with the lambda.

MyFunc);
 
private static IEnumerable MyFunc()
{     yield return "Hello";     yield return "World";
}
 
private static int Nothing() => (0

This is a trick at the moment but is something I want to enable properly in later versions. The first line causes the wrapper to call the function. The last line puts in a placeholder so the compiler is happy. With this method, you could put anything you like between these lines but limited to the references already included.

Watch this space…

These are just a few examples but give some ideas of how to use the tool as it stands. It is still very early in development but would welcome comments and suggestions as to what people would find useful from this idea.

2 thoughts on “Release of Alteryx Omni-Bus Tools v0.4 (previously known as JD Tools)

  1. James,

    I have a quick question on the random number generator. I am currently using Alteryx’s Simulation Sampling to get large numbers of random numbers from a lognormal distribution. Would it be possible for you in a future version to allow for multiple random numbers to be generated at once? I’ve created a quick batch macro to accomplish this, but It’s much slower than if the you update the base code to generate multiple random draws from the distribution. While my workflow with the simulation sampling tool works fine, I’m curious if using your .NET tool would be faster. I have my guess (your tool will be faster) but I have no way to know!

    Like

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s