Excelling at data manipulation with LDC Via

There’s no point having an all-singing all-dancing database application, if the data is locked up and can’t easily be extracted, reported on, analysed, and easily re-purposed for use elsewhere. Everybody’s favourite tool for working with business data in these ways is Microsoft Excel. So, naturally, it’s important that LDC Via has great (excel-lent, in fact) support for Excel. And it does, using Microsoft’s ‘Power Query’ feature.

When we were talking to people at the Engage conference in Ghent, this question came up, so we felt it warranted a fairly detailed ‘how-to’ blog post. In fact, the process is pretty simple, and to make it even simpler we’ve not only written it down step-by-step, but also provided a downloadable Excel file to get you up and running.

Using Power Query, which is a free add on to Excel, we can create a parameter-driven spreadsheet that pulls in data from any internet source, including LDC Via.

As ever, the internet is a wonderful source for getting started with these things, so thanks to the likes of Ken Puls and Matt Masson this is what we built in a few hours: LDC Via - Excel Power Query Sample. There are two sheets in the workbook: ‘LDC Via Data’ displays a table of data that has been pulled from LDC Via; and ‘Settings’ contains the configuration settings that define where to get the data from, what API key to use, and which fields to display in the output results.

To create all this we need two Power Query functions and a main “routine” to actually get the data.

The first function is called fnGetParameter and simply reads the settings from the Settings worksheet: We have four configuration settings: endpoint defines the LDC Via API endpoint from which data will be extracted. The simplest one will use our collections API that gets lists of documents from a collection count defines how many documents at a time will be extracted from the API apikey is your API Key that authenticates you to LDC Via fields is a comma separated list of field names from the LDC Via collection that will be displayed in the results

Next we need to actually go and get each page of data from LDC Via, and the fnGetCollectionData function performs this task: The relevant page number is passed in as a parameter called “start”, and then we use the Web.Contents and Json.Document functions to retrieve the data and parse it into an object that we can work with.

The documentation for Power Query, is, shall we say, opaque at best, but there is a list of functions that are available to you on the Microsoft site—the Power Query editor itself feels very much like going back to the Notes @formula editor, but once you get your head around it, you can perform some pretty powerful operations on the data.

In our case we’re loading the JSON document from LDC Via, extracting an array of document objects stored in the “data” property, converting that to a table, and returning all this to the calling function.

Now we need to pull these two functions together and actually run the query to load the data: Power Query doesn’t really have the concept of a For style loop, so we have to spoof that by creating our own range of page numbers. In this case we’ll loop from 1 to 100, and with 1,000 documents per request that will allow us to get up to 100,000 documents from LDC Via. For each page we call the fnGetCollectionData function, extract the fields that are specified in the settings worksheet, and finally insert them into the main worksheet.

Clearly the speed of all this depends on the number of documents in the database in the first place, but even the example above, with 40,000 documents, takes less than a minute to load and parse all of the data.

To go and get data from a different collection in LDC Via, simply modify the settings worksheet as appropriate, right-click on the GetTableData function, and choose the “Refresh” option: the data will be automatically downloaded and parsed.

Of course, once all your data is loaded you can do all of the things that you’re used to with Excel: pivot tables, filtering, sorting, charts and so on. The world is your mollusc.

I’ve got my free account. Now what?

We decided that everyone should be able to try out LDC Via for free. With our trial account you get full functionality so that you can see and do everything that paid accounts can. We know there’s a lot to LDC Via, and it can be a little overwhelming, so we thought we’d talk about what you may want to try out having signed up.

First things first, you’ll want to migrate some data. You’ve got two choices here. If your Domino server is internet-facing and you can enable Domino Data Service then a cloud-based migration is the simplest route. Our Migration Cheatsheet document should help you get set up there.

Once your Domino server is configured, you can go to the Database Migrator page and enter the details for your server. You’ll need to enter the URL pointing to your database, your Domino username and password and then select the view that you want to migrate. That’s it! We do the rest of the work for you.

If your Domino server is not internet-facing or DDS is out of the question, then you can use our Desktop Migration Utility. You just need to complete the config file and run the migrator from your desktop machine.

Once done, now that we have some data we’ll want to do something with it. If the database you migrated matches one of our standard templates (Mail, Discussion, Teamroom, Document Library) then you can go to the Database Viewer page and view your application.

The main benefit of LDC Via comes with the use of our REST API of course: you can now create custom applications using whatever technology you prefer. We have samples using XPages, .NET and AngularJS to get you started, but anything that can talk to a REST API can use our services.

Hopefully thoughts will be bubbling to the top of your mind…

• What if I want to run a “live archive” of data from Domino so that I can reduce the size of my NSF?
• What if I want to securely expose a subset of data behind the firewall to customers via the internet?
• What if I want to shut down a Domino server but retain access to all of the data?

LDC Via launched at Engage 2015

Last week, most of the the LDC Via team attended the Engage User Group event in Ghent, Belgium along with 320 other people.

As ever, Theo ran an amazing couple of days with a wonderful venue, content and catering and we were very happy to be sponsoring for the first time.

Among all of the sessions and conversations, LDC Via was officially launched!

From today you can go to our website and register a free account to try out our Domino migration utilities.

See what you can do with our standard application templates, and investigate what you can build with our full featured programming layer.

Ben and Matt presented a session introducing LDC Via: you can download the slide deck from our website.

It’s traditional at these user groups to run a giveaway competition. Thanks to everyone who entered our word search, you all did very well. The lucky winner of our Sonos Play:1 was Simon Peek from We4IT.

We want to talk about documentation. No don't go!

… it’s interesting! Well, useful. We promise.

Here at LDC Via Towers we’re all developers, and a large portion of our lives is spent working with badly documented APIs. We’ve just created our own API for you to work with your LDC Via data and we wanted to make sure that it’s as painless for you as possible. Enter the <trumpets sound> LDC Via API Documentation site </trumpets fade>.

The high-water mark of API documentation, in our opinion, is from Stripe and you’ll see that our site is very similar in structure. Our API site is very much a living document. A lot of our time currently is spent using our own API. When we spot things that are confusing or incomplete, we fix them. But as always, if you have any suggestions please let us know using the “Ask Us” link to the side of this post (and indeed on most other LDC Via pages).

Of course, we didn’t develop the site ourselves, we”re too busy writing our own code. Instead we used the wonderful Slate project from Tripit.

MongoDB security

MongoDB security concerns have recently hit the press: you may have seen some of the posts. The latest focusses on some German students who identified tens of thousands of MongoDB databases that are wide open: no security on them at all!

Information Age: Major security alert as 40,000 MongoDB databases left unsecured on the internet

Setting aside the insanity of not performing the most basic of security procedures, it’s probably good to outline how we at LDC Via approach this sort of thing, courtesy of our very own Matt White…

It’s a pain in the bum for us to get access to the databases, let alone anyone else.

Yep. We lock our MongoDB servers down. We lock them away from the internet. We lock our databases down. And then we employ application-level security too. That’s how it should always be done.