Press enter to see results or esc to cancel.

Connect Microsoft Office Excel using Power Query to Integration Manager

This is a tutorial describing how you can use all the data from Integration Manager in tools such as Microsoft Office Excel using Microsoft Power BI’s add-in for Excel – Power Query.

Before you get started

This tutorial also assumes that you have a basic understanding of what is Power Query and how to use it – if you do not please follow this link that helps you get started within just a couple of minutes. Please make sure you have the access to the Integration Manager instance(s) you want to connect to as well as a working copy of Power Query installed to your Excel. In case you are using Excel 2016 Preview, Power Query is already part of the suite and does not need be installed. In case you are a Excel 2010, 2013 user use the following link to download Power Query – https://www.microsoft.com/en-us/download/details.aspx?id=39379.

Note: The below tutorial also works while working with the Power BI designer – Microsoft Power BI’s stand-alone client for connecting to external data sources.

Introduction

While Integration Manager is used for logging and monitoring purposes, we thought – why not create an API layer to which our users can connect to by using 3rd party applications, such as Microsoft Excel, and take advantage of everything that is going on. And that is exactly what we did. Integration Manager‘s Web Client uses the API layer to work with Integration Manager’s data, that means that everything you can see in the GUI of the web client, can be received by using tools like Microsoft Excel.

The Web API

By navigating to the WebAPI URL of the installed instance of Integration Manager with your browser you get a list of all available API calls (see picture 1 below) –  the URL is usually something like http://servername/IM/WebAPI.

webapi-reference
Picture 1: Web API reference

 

The Web API layer is of course secured, that means for instance, that you are required to be part of the administrators group in order for you to access alarms, stylesheets and so on by using the Web API. You can only receive the data you are allowed to see, so if you are not an administrator you cannot use HTTP POST, PUT, DELETE commands – only GET and you can only receive events and monitor data from views that you have access to!

How to

Let’s assume you would like to get a list of all integrations that you have documented with the Repository Model of Integration Manager and import it to Microsoft Excel.

The API call for this action would be http://servername/IM/WebAPI/api/integrations/ (see Picture 2 below showing the result in the browser).

Picture 2: Result of integrations API call
Picture 2: Result of integrations API call

 

Copy the URL of the API call and open up a new Power Query – From Web (see Picture 3 – This is version Excel 2016 . In Excel earlier than 2016 you will have a tab that says “Power Query”).

Picture 3: New Power Query
Picture 3: New Power Query

 

Paste in the API call URL and press OK.

Power Query might ask you for more authentication details – use Windows Authentication and press OK. In the current state of Integration Manager we do not support other authentication methods than Windows Authentication. (see Picture 4 – Choose Windows Authentication).

Picture 3
Picture 4 – Choose Windows Authentication

 

As soon as you have pressed the OK button the Power Query Editor windows pops up and you are able to navigate through the API of Integration Manager with a nice and clean editor – the Power Query Editor.

Note: Integration Manager‘s API uses Collection+JSON as its standard for how the API is build – learn more at http://amundsen.com/media-types/collection/.

The result

If you have navigated through the object model that the API call provided you, you can easily achieve a system-dependency report showing you what systems are used in the different system integrations. (See picture 5 below – system dependencies)

Picture 5 - System Dependencies
Picture 5 – System Dependencies

 

Summary

Connecting Microsoft Office Excel / Microsoft Power BI to Integration Manager is easy by using the API reference of Integration Manager and using tools such as Power Query and/or the Power BI designer.

If you want to learn more about how to use Integration Manager together with Microsoft Power BI – feel free to contact us at info@integrationsoftware.se and we are happy to book an online demo with you for showing you the power of Integration Manager.