Power BI

Power BI is a business intelligence tool by Microsoft. It aims to provide interactive and easy to use data visualization options. Users can create their own reports and dashboards.

Main features of Power BI are:

Rich Graphical visualization from complex data.
Ad-hoc Reporting.
Easy to use navigation pane.

Users can benefit from the easy connectivity in two different ways:

Flat file import.
Web API Connectivity.

Power BI Installation

In this section you will find instructions for installing Power BI Desktop version. This application is only available for the Windows operating system. In order to use Power BI on Linux and Mac operating systems you will need a Pro version.

To install Power BI Desktop version:

Go to powerbi.microsoft.com.
Click on DOWNLOAD FREE.
You will be redirected to Microsoft Store, click on Get and download will start.
After download is complete, Launch the application.
Create a new Power BI account or Sign in with the existing account and you are all set up.

Data Import

Below a few examples of importing data from inmation are provided. The examples will be based on the Demo Data set. To configure the Demo Data, follow this guide.

1. Flat file import

In DataStudio go to the I/O-Model panel. 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.
On the top left corner of the history grid, the 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 a Power BI data table. After choosing the export format, the data will automatically be displayed in the application which is registered in your system for this export format. There, save the file to your preferred location.
Open the 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, select the CSV file and click Open.
You are ready to work with your data.

2. Connectivity through Web API

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

Open the 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.
Choose the authentication option (see Authentication section).

When the data is successfully imported, a Power Query Editor screen will be displayed, where you can see the data as a List. To extract your data into a readable table format, follow these steps:

Click on the List
Click on the Record
Click on the List
To Table in the home ribbon
Select or enter delimiter → None; How to handle extra columns → Truncate Extra Columns.
Click on the expand button which is on the right corner of the column header.
To rename the table, type the new name in the Name field.
If you want to rename any columns, right click on the respective column header and select 'rename'.
Click Close & Apply to close Query Editor and save the table.
You are returned to Power BI Desktop view and now you can work with your data.

For more information about available Web API endpoints and how to use them, see here.

Advanced Endpoint

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

Advanced Endpoints have to be created in a specific place. This place depends on the settings of the WebAPIServer. In DataStudio go to the Server Model, select the WebAPIServer object and in the Properties Panel expand the Common section. The Context Path property points to the object which will hold the Advanced Endpoints for this WebAPIServer.

In the I/O Model navigate to this object and select it by clicking on it. In the Properties Panel expand the Script Library section and add a new Script Library by pressing the plus sign, and name the library "my-lib".
This simplified script example reads raw historical data of the process data item from the DemoData. Click … to add this script to Lua Script Body section:

local inAPI = require('inmation.api')

local lib = {}

function lib.readhist(_, arg, req, hlp)
    arg = arg or {}
    local now = syslib.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 = syslib.gettime(now-(5*60*1000))
    end
    local endTime = syslib.gettime(now)
    local qry = {
        start_time = startTime,
        end_time = endTime,
        items = {
            {
                p = "/System/Core/Examples/Demo Data/Process Data/DC4711"
            }
        }
    }

    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 = syslib.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

HTTP Method Get

/api/v2/execfunction/my-lib/readhist

Invoke the Lua script from the Postman tool to test if everything is working. If your test was successful, go to your Power BI Desktop, choose Get Data Web, paste your URL and click ok. After successful import, the preview tab should pop up, with your data transformed into a Power BI data table.

Response body

2019-07-23T07:39:00.041Z,16.824450683594
2019-07-23T07:39:30.041Z,16.898840332031
2019-07-23T07:40:00.041Z,17.029431152344
2019-07-23T07:40:30.041Z,17.212634277344
2019-07-23T07:41:00.041Z,17.340710449219
2019-07-23T07:41:30.041Z,17.212640380859
2019-07-23T07:42:00.041Z,17.141229248047
2019-07-23T07:42:30.041Z,17.09482421875
2019-07-23T07:43:00.041Z,17.226696777344
2019-07-23T07:43:30.041Z,17.072930908203

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

Authentication

Power BI supports different authentication options to import data from a web source. Some of the most popular options are listed below:

Basic Authentication

To use basic authentication, choose Get Data Other Web:

choose Basic, paste your URL and click Ok.
In the second screen again choose Basic, fill in your User name and Password. Press on Connect button.
If successful Query Editor screen will open.
You can now work with the data.

Windows Authentication

Windows Authentication can be used with all API v2 namespace endpoints.

To use Windows Authentication make sure your current Windows user or (domain) User Group is configured and you have access rights to Web API. For more information about Web API authentication and profile mapping, read here.

Go to Power BI Desktop application, choose Get Data Other Web.
Choose Basic Authentication → paste the URL and click Ok. Your URL should contain the following: http(s)://LOCALHOST:8002/api/v2/endpoint?secp=iwa
In the second screen choose Windows → Use My Current Credentials. In the drop down dialog box below choose to apply settings to the api/v2 namespace → Connect.
If successful, the Query Editor screen will open.
You can now work with the data.