Asset & Usage Analysis – a Microsoft Power BI Application using Plantronics Open APIs

UPDATE: Plantronics has created an app for Power Bi that can be installed today in your Power BI environment. 
See the overview of Plantronics app for Microsoft Power BI for more details.

---
Asset & Usage Analysis – a Microsoft Power BI Application using Plantronics Open APIs

Download link:Sample Code for PDC - Power BI.zip

Prerequisites
  • The developer is a certified Power BI engineer and understands how to connect and publish organizational content packs to the Power BI service
  • The developer is a trained administrator for Plantronics Manager Pro and the API suite is active in the Plantronics Manager Pro service
  • The developer has a Power BI Pro subscription and has installed the Power BI Desktop
  • The developer has downloaded the PBIT file from the Plantronics Developer Connection web site
  • The developer has an understanding of both R and M script coding with a 64 bit Windows PC
To begin, open Power BI Desktop by double click on the Power BI template file provided (Asset & Usage Analysis.pbit) which will open in Power BI Desktop. This .pbit file is provided as sample code and does not support refresh in Power BI Service.
  • Enter your Region URL and Tenant API Code and click Load.  The Query Days default is 90 days but may be changed if required.
  • Once the report loads click the Publish button.
  • When prompted, save the file PMP Insights.pbix |
  • A Publish to Power BI pop up will appear as shown below.  Select “My workspace” as the destination and click the Select Button.
  • Click on the Open “PMP Insights.pbix’ in Power BI” hyperlink and enter your Office 365 credentials if requested.
  • The report will open in Power BI Service. As you will see this is a tabbed multi-page report.
  • You can now populate a Dashboard by pinning the report pages or individual visualizations as shown below.
  • The Dashboard is created and named as part of the first pinning as shown below
  • And the Dashboard is created……further tiles of interest can be added from the report in the same way.
Plantronics Developer Connection – Power BI
Implementation of App Authorization Request
In order to connect the Power BI Content Pack to your Plantronics Manager Pro tenant, you require 4 pieces of information, as follows:
  1. REST URL
  2. API Key
  3. Tenant id
  4. App Id
Introduction

One of the Partner App Management API methods (requestAppAuthorizationUsingPUT ) sends an App authorization request to Plantronics Manager Pro for the requested tenant - see http://developer.plantronics.com/oda/apis/put/partner/app/%7BappId%7D/authrequests . 
This method is used in Power BI to perform the initial app authorization request where you enter your Tenant information when prompted by the Power BI Template file (.pbit).
A description of a Power BI implementation follows 

Power BI Implementation
  1. Going Beyond GET requests

    As a query and reporting tool Power BI lends itself to performing GET requests against REST API. For instance, the following snippet of M Code retrieves Inventory details from the API ODA Sandbox.
let
    Source = Json.Document(Web.Contents("https://oda-sandbox.plantronicsmanager.com/reports/assets/tenant/af0813e6-81c0-4a77-89d2-
3338d0277c42/devices?
apikey=1hKMMPFMmaldnC0rcohvXN0UX7UeWPjb&lastSeenStartTime=2017-06-01T18%
3A41%3A05.735Z&lastSeenEndTime=2017-06-03T18%3A41%3A05.735Z&page=0&count=10", 
[Headers=[appId="a7bbeaaf-4225-4d3e-ac63-19789a13b845"]])),
    _embedded = Source[_embedded],
    deviceInventoryList = _embedded[deviceInventoryList],
    #"Converted to Table" = Table.FromList(deviceInventoryList, Splitter.SplitByNothing(), null, 
null, ExtraValues.Error),
    #"Expanded Column1" = Table.ExpandRecordColumn(#"Converted to Table", "Column1", 
{"tenantId", "tenantName", "_links", "product", "lastSeen", "firstSeen", "softphone", 
"device"}, {"Column1.tenantId", "Column1.tenantName", "Column1._links", 
"Column1.product", "Column1.lastSeen", "Column1.firstSeen", "Column1.softphone", 
"Column1.device"}),
    #"Expanded Column1.product" = Table.ExpandRecordColumn(#"Expanded Column1", 
"Column1.product", {"productId", "_links", "manufacturer", "family", "name", "modelId", 
"type", "vendorId"}, {"Column1.product.productId", "Column1.product._links", 
"Column1.product.manufacturer", "Column1.product.family", "Column1.product.name", 
"Column1.product.modelId", "Column1.product.type", "Column1.product.vendorId"})
in
    #"Expanded Column1.product"
  1. Setup R 
This is available as a free download from https://mran.microsoft.com/download/
Please note that this requires a 64 bit Microsoft machine.

Configure the following settings in Power BI Desktop:
  • Privacy Settings - Select Options & Settings / Options and select the Always Ignore Privacy Level settings as shown below.
  • Data Load Settings - Select Options & Settings / Options and select the settings shown below..
  1. Running the Power BI Implementation
  • Double click on the PowerBI template file provided (Authorisation Request.pbit)
  • This will launch in Power BI Desktop
  • Select your Regional Data Access URL from the dropdown list.
  • Enter your PMP Tenant API Code and click the Load button
  • Click Enable script visuals when prompted
  • A "Request Confirmed" message - (200)  will be displayed
  • If the Authorization Request is re-run a "Request Already Submitted" (400) message will be displayed. Note: that these values are displayed per Subscription (e.g. Asset Analysis) and may differ between Subscriptions.
  1. Close Power BI Desktop - Exit & Don’t Save
Sample Code 
# Input load. Please do not change #
`dataset` = read.csv(
  'C:/Users/john.dawson/REditorWrapper_145e00a2-638b-4489-ab64-9e9ee99a62fe/input_df_9028f2d7-4eb8-49de-ad58-d4f6d65d110a.csv', 
  check.names = FALSE, encoding = "UTF-8", blank.lines.skip = FALSE);
# Original Script. Please update your script content here and once completed copy below section back #
# to the original editing window #
# check & install required packages using pacman
if (!require("pacman")) {
  install.packages("pacman")
  library(pacman)
}
pacman::p_load("data.table", "httr", "gridExtra")

# get parameters from dataset
sURLDomain <- as.character(subset(dataset$Value, dataset$Key == "URL"))
sTenantAPICode <- as.character(subset(dataset$Value, dataset$Key == "TenantAPICode"))
sAppID <- as.character(subset(dataset$Value, dataset$Key == "AppID"))
sAPIKey <- as.character(subset(dataset$Value, dataset$Key == "APIKey"))

# build list of Subscription IDs
# Ideally, this list should come from another Plantronics API
lSubscriptionIDs <- list("USAGEHISTORICAL", "USAGEREALTIME", "ASSETSHISTORICAL", "ASSETSREALTIME", 
  "CONVERSATIONDYNAMICHISTORICAL", "CONVERSATIONDYNAMICREALTIME", "ACOUSTICHISTORICAL", "ACOUSTICREALTIME")

# initialise list that will contain details of results of authorisation requests
lSubscriptionAuthorisationsRequested <- list()

for (sSubscription in lSubscriptionIDs) {
  # build http PUT request
  sURL <- paste(sURLDomain, "/partner/app/", sAppID, "/authrequests?tenantApiCode=", sTenantAPICode, 
    "&subscription=", sSubscription, sep = "")
  sHeader <- toString(sAPIKey)
  names(sHeader) <- "apikey"
  lResult <- PUT(sURL, add_headers(.headers = sHeader))
  
  # get result info.
  iStatus <- status_code(lResult)
  lContent <- content(lResult)
  sStatusMessage <- switch (iStatus / 100, "Code 100", "Request successful", "Code 300", lContent$errorMessage, 
    lContent$status)
  lThisSubscriptionAuthorisation <- list(toString(iStatus), sStatusMessage, sSubscription)
  names(lThisSubscriptionAuthorisation) <- c("Status", "Status message", "Subscription")
  
  # add to list of results of authorisation requests
  iLengthlSubscriptionAuthorisationsRequested <- length(lSubscriptionAuthorisationsRequested)
  lSubscriptionAuthorisationsRequested[[iLengthlSubscriptionAuthorisationsRequested + 1]] <- lThisSubscriptionAuthorisation
}

# if any Tenant Subscription authorisations were requested build dataframe for display
if (iLengthlSubscriptionAuthorisationsRequested > 0) {
  lSubscriptionAuthorisationsRequested <- Filter(Negate(is.null), lSubscriptionAuthorisationsRequested) # remove empty rows
  dfSubscriptionAuthorisationsRequested <- data.table::rbindlist(lSubscriptionAuthorisationsRequested)
  gSubscriptions <- tableGrob(dfSubscriptionAuthorisationsRequested, rows = NULL)
  sHeader <- paste("Subscription authorisation request results", "\n\n", "URL: ", sURLDomain, "\n", "AppID:", sAppID, "\n")
  grid.arrange(gSubscriptions, top = sHeader)
}

Questions? Please use the comment form below.