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:

  1. Flat file import.

  2. Web API Connectivity.

Power BI Installation

In this section, you will find instructions for installing Power BI Desktop version.

Power BI Desktop application is only available for Windows operating system. In order to use Power BI Linux and Mac operating systems you will need a Pro version. To install Power BI Desktop version:

  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 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

  1. DataStudio allows 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 → File → CSV → Connect → choose the CSV file → click Open.

  4. 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 URL, which in turn can be used to import data from 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. Choose authentication option (see Authentication section).

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

  1. Click on the List

  2. Click on the Record

  3. Click on the List

  4. To Table in the home ribbon

  5. Select or enter delimiterNone; How to handle extra columnsTruncate Extra Columns.

  6. Click on the expand button, which is on the right corner of the column header.

  7. To rename the table, type new name in the Name field.

  8. If you want to rename columns, right mouse click on the column header and select rename.

  9. Close & Apply to close Query Editor and save the table.

  10. You are returned to Power BI Desktop view and now you can work with your data.

For more information about available the Web API endpoints and how to use can be found here.

Advanced Endpoint

Advanced Endpoints 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.

To create advanced endpoint for reading historical data go to DataStudio, select APIContext and click on Script Library which is located in the Object Properties tab on the right side of the screen. 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. 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 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 → click ok. After successful import 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 Web source. Few most popular options are listed below:

Basic Authentication

To use basic authentication, choose Get DataOtherWeb:

  1. choose Basic, paste your URL and click Ok.

  2. In the second screen again choose Basic, fill in your User name and Password. Press on Connect button.

  3. If successful Query Editor screen will open.

  4. 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.

  1. Go to Power BI Desktop application, choose → Get DataOtherWeb.

  2. Choose Basic Authnetication → paste URL and click Ok. Your URL should contain the following: http(s)://LOCALHOST:8002/api/v2/endpoint/secp=iwa

  3. In the second screen choose WindowsUse My Current Credentials. In the drop down dialog box below choose to apply settings to the api/v2 namespace → Connect.

  4. If successful Query Editor screen will open.

  5. You can now work with the data.