inmation Power BI Connector

Power BI is a business intelligence tool, offering data visualization, real-time dashboards and analytics. Power BI is recognized as market leader and is widely used in many industries (Gartner: Magic Quadrant for Business Intelligence and Analytics Platforms, February 2017).

inmation clients can benefit from the easy connectivity which was enabled by the inmation Power BI connector. Custom connectors, just like inmation connector, allows simple data querying from the source of the data to the Power BI Desktop application.

Installation

In this section, you will find instructions for installing Power BI and installing and working with inmation Power BI connectors.

Power BI Installation

Power BI Desktop application is only available for Windows operating system, however Power BI read more about System Requirements here. To install Power BI Desktop:

  1. Go to powerbi.microsoft.com.

  2. Click on DOWNLOAD FREE.

  3. You will be redirected to Microsoft Store, click on Get and download will start.

  4. After download is complete, Launch the application.

  5. Create a new Power BI account or Sign in with the existing account.

inmation Connector Installation

To download and install the connector:

  1. In This PC / Documents directory create a folder Power BI Desktop inside create another new folder and name it Custom Connectors.

  2. Download the connector file(s).

  3. Place the file into the Documents/Power BI/Custom Connectors folder.

To avoid a warning about connection to the third-party services, you will need to enable loading uncertified connectors:

  1. Open Power BI Desktop.

  2. Go to File | Options and settings | Options.

  3. Go the Security tab.

  4. Under Data Extensions, select Allow any extension to load without warning or validation.

  5. Restart Power BI Desktop.

Open a new Power BI file, go to the options and settings, in the Preview features tab enable Custom data connectors by selecting it. This feature allows the data structure preview when loading the data.

Examples

Below a few examples of working with Power BI in combination with inmation will be provided. The examples will be based on inmation demo data, to configure the demo data, follow this guide.

inmation connector

Open Get Data and search for inmation connectors. Pick one of the following connectors:

  1. inmation - Read Historical Data connector. Returns specified object’s processed historical data. Click here to download the inmationReadHistoricalData.mez file.

  2. inmation - Execute Function connector. Fetches data by using the inmation Web API ExecuteFunction endpoint. Click here to download the inmationExecFunction.mez file.

Arguments

Depending on which connector you are going to use, you will see one of the following argument tables in the first display screen. Below tables contain explanation of each argument.

inmation - Read Historical Data

Name Description

Web API base URL

The base address of the inmation Web API.

Start Time

The start time (UTC) of the interval to retrieve data for.

End Time

The end time (UTC) of the interval to retrieve data for.

Paths

Comma separated paths of the object to be queried for historical data.

Aggregate

The aggregate type as string code. E.g. Average.

inmation - Execute Function

Name Description

Web API base URL

The base address of the inmation Web API.

Library

Name of the script library which contains or returns the function to execute.

Function (optional)

Name of the function to execute. Required in case the library itself does not return a function.

Context (optional)

A path, which specifies the context in which the provided function has to be executed. By default, the context path of the inmation Web API server object.

Second display screen is a login screen containing your inmation username and password input fields.

Flat files

  1. DataStudio allows flat file export, right click on the selected object and choose add item(s) to History Grid. Select the desired period and History grid will be displayed on your screen.

  2. On the top left corner of the history grid, export button can be found (fourth button from the left), DataStudio supports CSV, Excel and JSON format export. Power BI supports all of these formats, however CSV is easiest to work with, since CSV is directly recognized and translated into Power BI data table. After choosing export format the file will automatically open, go to file save as to save the file on your chosen location on the system.

  3. Open Power BI Desktop application, click on the New Source tile, which is located in the menu bar on the left side of the screen. Click on more and choose File.

  4. Open Power BI Desktop application, click on the New Source tile, which is located in the menu bar on the left side of the screen. Click on more → File → CSV → Connect → choose the CSV file → click Open.

  5. You are ready to work with your data.

Web

Postman or Swagger and inmation Web API can be used to generate URL, which in turn can be used to import data from inmation DataStudio to Power BI Desktop. Note that only GET method can be used to fetch the data using Power BI Web method.

  1. Open Power BI Desktop application, click on the New Source tile, which is located in the menu bar on the left side of the screen. Click on more → Other → Web → Connect.

  2. Paste URL and click OK.

  3. To expend your data into a Power BI data table, choose Into Table from the menu bar.

  4. Click on the expand button and choose Expand to New Rows.

  5. Repeat action, and choose which values should be included in the table.

  6. After data has been fully expanded into a table, save the data table by clicking on Close & Apply.

  7. Now you can work with your data which is in the Data tab.

For more information about available inmation Web API endpoints and how to use them read here.

Web - Advanced Endpoint

Advanced Endpoints is a very powerful way to embed your corporate logic directly in the inmation Web API. Advanced Endpoint can be used for data export and Content-Type setting, for example to CSV instead of the usual JSON.

To create advanced endpoint for reading historical data go to DataStudio, select APIContext and click on Script Library which located in the Object Properties tab on the right side of the screen. Add a new Script library by presing the plus sign and named the library "read-historical-data". This simplified script example reads historical data for one of the performance counters from the DemoData, add this script to Lua Script Body section:

local inAPI = require('inmation.api')

local lib = {}

function lib:readperfcounters(arg, req, hlp)
    arg = arg or {}
    local now = inmation.currenttime()
    local startTime
    if type(arg.starttime) == 'string' then
        -- Use starttime supplied by the caller.
        startTime = arg.starttime
    else
        -- Fallback to a default relative starttime
        startTime = inmation.gettime(now-(5*60*1000))
    end
    local endTime = inmation.gettime(now)
    local qry = {
        start_time = startTime,
        end_time = endTime,
        filter = {
            v = {
              ["$gte"] = arg.limit or 50
            }
        },
        items = {
            {
                p = "/System/Core/Performance Counter/Core/Core/CPU",
            }
        }
    }
    local respData = {}
    local res = inAPI:readrawhistoricaldata(qry, req, hlp)
    local rawData = res.data or {}
    local histData = rawData.historical_data or {}
    local queryData = histData.query_data or {}
    if #queryData > 0 then
        queryData = queryData[1]
        local items = queryData.items or {}
        if #items > 0 then
            items = items[1]
            for i,t in ipairs(items.t) do
                local value = items.v[i]
                local timestampISO = inmation.gettime(t)
                local row = ("%s,%s"):format(timestampISO, value)
                table.insert(respData, row)
            end
        end
    end
    local result = table.concat(respData, '\n')
    return hlp:createResponse(result, nil, 200, { ["Content-Type"] = "text/csv" })
end

return lib

To invoke the function and generate URL use Postman. Invoke this endpoint by:

Method: POST URL: /api/v2/execfunction/read-historical-data/readperfcounters

{
    "start_time": "2019-04-02T10:00:00.000Z",
    "limit": 35
}

For more information about Advanced Endpoint visit Web API Advanced Endpoint.