How we connect Alteryx to AllVue BlackMountain Everest

Black Mountain's “Everest” is an increasingly popular financial technology system which integrates an investment firm's front and back office systems to provide a holistic view across all investment portfolios. The system is cloud based, with our client hosting in Microsoft Azure.

The Everest system has an API, which allows other systems to connect to Everest and retrieve data.
The Everest administrators create a named datasource, which might directly map to a table, or view, and grant read privilege to particular users, so that data can be extracted from Everest.
Our client was using an Excel add-in to extract the data, which was then joined into an Alteryx workflow, but asked if we could provide a connector for Everest for a seamless experience.

We love a challenge so of course said “Yes” - this blog post tells the story of how we made the connector.
At no time have we had access to the system, so have built this based on google and Black Mountain’s documentation.

The connector is in Read Only test currently, but after successful testing we will make the Connector available.

Making the Connection

Systems hosted in Azure can have various authentication and authorisation methods. In this case, the system uses OpenID, and you provide a username and password combination. You send your credentials to a URL specifically for LogOn, and that gives you back a token and a session ID, which you can then use to make your data query. The token is evidence that you have privilege to access certain data, and the sessionID is used to make the token valid for a limited time.

The problem here is that Azure is not expecting something like Alteryx to be making the connection, so you get multiple HTTP messages back, and you have to fish out your token and session ID from these messages. Azure is expecting an ASPX web page to make the connection, so the Alteryx person has to parse those messages and extract the right token, and the right session ID, from multiple options.

For this part, it helped that I have a history working with ASPX, and designing web pages, so I could quickly separate the wheat from the chaff, but you could overcome this with trial and error too.

So, you grab a Download Tool, create a string containing your username and password, set your HTTP headers, and make a POST request. Azure's credential checker chews that request and fires back a few messages, and you text process them to extract your token and session ID.

Once you have extracted the correct token and session ID, you can use them to request data from Everest, via the DataSource.

Making the API Call

The Everest administrators should have set up a named DataSource and given your user identity (username) read access to it.
If this is in place, retrieving data involves making an HTTP call to a URL with that DataSource name.

At this point, you also have to give a PageSize, to say how many rows you want returned, like saying "Please give me the first 50 rows for this data source". But you do not know what you are going to get back, so how do you provide a sensible number?

Web systems like small messages, and cutting the data into pages is a very common way to return data. It also nicely models the human approach to reading data, looking at one page at a time where each page is the same size.

When data is returned, you get your page of rows, but you also get some contextual information about the page, including the total number of rows. Once we know the total number of rows, we can work out how many page requests we have to make to get all the rows.

So, we make a preliminary call and request page 1, with a row size of 1. This call should always work if there is any data to retrieve. From this we get a value in totalDataRowCount, telling us the size of the data.

I use a default page size of 100,000 records, as this is big enough for many queries, but not so big that it would freak out a database system or overwhelm packet sizes for HTTP messages. You might have really large rows of course, and you might need to play with this value to find a default page size that makes everyone happy, but something in the range 1000 to 1,000,000 is probably going to work.

With a nominated page size, you can divide the known totalDataRowCount by the default page size, and you know how many pages to pull.

Once we know how many pages to pull, we can make one Alteryx record per request, using a GenerateRows tool. Each record builds a request for a page, so if you want 5 pages to cover all your rows, you end up with 5 records, requesting pages 1 to 5, with a page size of 100,000.

You then set up the Download tool, build in the token and session ID from the credential stage, add on the page request details, and fire off your 5 (or however many) page requests to Azure.

Note: Azure might throttle very large requests, and you might have to get funky and add a time pause between each request, but this is all doable.

Handling Returned Data

Data is returned in pages, wrapped in JSON packets. JSON is text data in a format that originated in JavaScript, so JSON is "JavaScript Object Notation".

Alteryx has a lovely JSON parse tool that will bash the returned JSON data into rows. There is still some work to do though.

The JSON data contains headline summary information about the page, and the page records themselves. Some Alteryx work is required to split off the summary from the actual data, and then some work is required to process the JSON array into Alteryx records. The JSON Parse tool leaves you with some key=value pairs that have to be bounced into rows, but this kind of thing is totally in the Alteryx Crosstab tool's wheelhouse.

Finally, with row based data, you can sort the data on a row number that is also provided, and you end up with an accurate representation of the data source, as rows, in the expected order, and your API call is complete.

Opening the Door

With Alteryx as the conduit, you now have a way to programmatically pull data from BlackMountain's Everest.
That means you can start to build reports and schedule them on Alteryx Server.

Additionally, this gives you the ability to use BlackMountain as a data source for other systems.
If those other systems have API's, the possibility opens up for Alteryx to be used to connect two disparate systems together.
For example, a SAP accountancy system could be fed from BlackMountain data sources, or a CRM system such as Salesforce or DealCloud could be driven with Everest data to send reporting emails to customers on a regular basis.