How to Create a KPI table

Tabular Data can be displayed on the Visual KPI web interface using the KPI Table Object (requires an enterprise:inmation license and installation of Visual KPI). The KPI Table object executes an embedded Lua script when it receives a request from Visual KPI via the Web API. The script then returns a JSON table that is interpreted by Visual KPI and displayed as a table in the web interface (more details available in the system documentation).

This example will use the DemoData simulation objects from the Using Lua Scripting Jump Start. Use the Examples_DemoData MassConfig file found here to create these objects.

To create a KPI Table, go to the KPI Model in DataStudio and select a group object (if you have created the DemoData objects, you can select the Enterprise Object "inmation Köln"). Right-click and select Admin New KPI Data KPI Table.

Figure 1. Create KPI table in KPI Model

In the Create Object wizard, give the KPI Table a name and click Next >.

Figure 2. Create KPI Table Wizard

In the Table Type Selection options, select Lua from the drop down menu and click Create to create the object in the KPI Model.

Figure 3. Table Type Selection Options

Once the KPI Table is created in the KPI Model, click on the "…" next to Lua Script Body in the Object Properties Panel to add a Lua script to the object

Figure 4. Add Lua Script to Created KPI Table

Insert the following Lua script into the Script Editor and click OK:

return function(arg)
    local timestamp = arg.TIMESTAMP
    local starttime = arg.STARTTIME
    local endtime   = arg.ENDTIME

    local result = {}
    local paths = {"/System/<Core-Server-Name>/Examples/DemoData/ProcessData/DC666",
                    "/System/<Core-Server-Name>/Examples/DemoData/ProcessData/DC4711"}

    local names = {}
    for i=1,#paths do
        local parent, child = syslib.splitpath(paths[i])
        table.insert(names,child)
    end

    local function replacenil(val,rep)
        if val == nil then
            return rep
        else
            return val
        end
    end

    local aggregates = {"AGG_TYPE_AVERAGE"}
    local intervals  = 10
    local start_time = syslib.gettime(starttime, "%Y-%m-%dT%H:%M:%SZ")
    local end_time   = syslib.gettime(endtime, "%Y-%m-%dT%H:%M:%SZ")

    local res = syslib.gethistory(paths, start_time, end_time, intervals, aggregates, 100, 100, false, false, "UASTANDARD")

    local schema = {}
    table.insert(schema, {name = "Timestamp", description = "Source Timestamp", type = "string"})
    table.insert(schema, {name = names[1], description = paths[1], type = "number"})
    table.insert(schema, {name = names[2], description = paths[2], type = "number"})
    result["schema"] = schema

    local data = {}
    for i = 1, intervals do
        local row = {}
        table.insert(row, syslib.gettime(res[1][i].T))
        table.insert(row, replacenil(res[1][i].V,"NULL"))
        table.insert(row, replacenil(res[2][i].V,"NULL"))
        table.insert(data, row)
    end
    result["data"] = data

    return result
end

The inserted script is a function that is returned and takes the arg variable as it’s argument. This variable consists of the STARTTIME, ENDTIME and TIMESTAMP properties that are supplied by Visual KPI from the Time Ranges selected in the Visual KPI web interface (these are converted into a readable format using the syslib.gettime function). The script uses the start and end time to perform a History call (using the syslib.gethistory function) on the DemoData objects listed in the paths table. The replacenil function is used to handle any null values that might be returned from the history call. The script returns a Lua table, result, that is converted to a JSON document automatically before being returned to Visual KPI. The structure of the returned JSON is explained in more detail in the system documentation but is essentially made up of two keys: "schema" and "data". The "schema" key contains an array that contains the column headers for the table (each column is a sub-document containing "name", "description" and "type", "name will be the title of each column). The "data" key contains a separate array for each row of data in the column. Open the Visual KPI web interface and open the Test Table.

Figure 5. KPI Table in Visual KPI

The column headers in the table can be matched to the values assigned in the "schema" key and the rows of the table can be matched to the values assigned in the "data" key.

Figure 6. Returned JSON matches the displayed KPI Table in the Web Interface

Using Lua, any column headers and data can be assigned to the "schema" and "data" keys. Using the STARTTIME, ENDTIME etc. arguments from Visual KPI allows you to sync the data time periods with the Visual KPI web interface and essentially make history calls from there.