This solution helps View360 Technologies to easily author interactive data reports without writing any code using Microsoft Power BI Desktop. The team can choose modern data visualizations out of the box or customize without building them from scratch. It reduces their development efforts and provides a lot of flexibility. It also helps View360 customers to have consistent high-fidelity interactive data visualization experiences, rendered in HTML5, across any device.

Technologies used:

  • Azure Resource Manager
  • Azure SQL Database
  • Power BI Desktop
  • Power BI Embedded
  • Microsoft .NET Framework (for report deployment and viewing)

The project team included:

  • Sudhir Rawat – Senior Technical Evangelist, Microsoft DX India
  • Surbhi Jain – Audience Marketing, Microsoft DX India
  • Satish Kottapalli – Director, View360 Technologies
  • Budida Ranjith Kumar – Developer, View360 Technologies

Company profile

View360 Technologies | Hyderabad, India

View360 Technologies is an innovative IT outsourcing and consultancy company with various monitoring solutions: telecom tower monitoring, IT infrastructure monitoring, remote asset monitoring, energy optimization, and video surveillance. It also engages in telecom and infrastructure and consultancy services.

The telecom tower monitoring solution (telView360) is meant for monitoring the telecom tower infrastructure operations, telecom switching, and transmission nodes event management. It has been acknowledged as a cost-effective solution with an automation approach that factors in real-world issues such as network instability, power instability, human dependencies, and operational constraints in the network. It is aimed at maximizing the operational efficiency under severe power shortages and unstable power conditions in the field.

TelView360 is an integrated solution that alleviates maintenance issues associated with base transceiver station (BTS) sites due to geographical spread and infrastructure challenges. It consists of three levels of intelligence: continuous event monitoring, processing and controlling of event-generated data, and communicating with a remote server. These three levels of intelligence are used to enable the telecom service provider to automate the controlling functions, which ensure smooth running of BTS infrastructure, and to optimize the electric power utilization to lower the operating costs of a BTS site.

Problem statement

At present, View360 is using a web solution to share reports with telecom operators. A scheduled job runs at 2:00 AM every day and performs the following actions:

  • Data extraction from main table
  • Calculation of monitored values for defined periods (daily, weekly, monthly, and so on)
  • Storage in the reporting table

The users can view the reported data through an ASP.NET application for defined periods. However, if the user selects any period for which data is not present in the reporting table, then processing will happen at the client side. The solution consists of a .NET application and SQL running on AWS virtual machines. The web application is designed using ASP.NET to show reports over the web.

For live event reporting, View360 stores the latest events in another table and shows a separate report that displays the following values:

  • Current temperature
  • Battery voltage
  • Battery status
  • Diesel generator (DG) status
  • Current fuel value

Below are the pain points with the existing solution:

  • Visualization. Current reports are developed in ASP.NET and hence have limited options for visualization.
  • Longer development time. It takes more time for developers to design new reports.
  • Report rendering. Data processing happens at the client side if the data is not available for the specific period. Hence, it takes more time to render reports.
  • Cross-platform reporting. In the future, View360 is planning to provide reports over other devices (such as mobile and tablet), a challenge with the current design.

Solution

Step 1. Building the end-to-end flow

We did a proof of concept with the View360 team during a Power BI Embedded hackathon to design reports using Power BI Desktop, deploy to Power BI Embedded service on Azure, and access reports from their .NET application. Reporting data is available on Azure SQL Database.

Figure 1. Reporting architecture

reporting architecture

Step 2. Preparing query for reporting

We developed an understanding of the reporting requirements from the user and .NET code. The View360 team had a 300-line .NET code to process the data and build reports. For example, if data is not available for reporting, this code sends a query to the database and loop records for calculation such as “how long generator was running” and displays it in a table format. We converted the entire code into a SQL query.

Here is the query to display the duration for which EB (energy meter) is on/off for a given period and asset number.

    Select
    sum(t.TOTALValue) as durationMinutes, t.EBSTATUS 
    from (
                select  EBSTATUS, LAG(trackdatetime) OVER (ORDER BY 
                trackdatetime, EBSTATUS ) as prevtime
                ,Datediff(minute,LAG(trackdatetime) OVER (ORDER BY trackdatetime, EBSTATUS                           
                ), trackdatetime) as TOTALValue
                from TABLE_NAME
                where trackdatetime between '2016-11-05 00:00' and '2016-11-05 23:59'
                and assetno = 'XXXXXXXXXXXXXXXXX' 
    ) t   
    Group by t.EBSTATUS

Here is the query to display the duration for which DG (diesel generator) is on/off for a given period and asset number.

    Select
    sum(t.TOTALValue) as durationMinutes, t.digital2 
    from (
            select  digital2,LAG(trackdatetime) OVER  
                (ORDER BY trackdatetime, digital2 ) as prevtime
                ,Datediff(minute,LAG(trackdatetime) OVER (ORDER BY trackdatetime, digital2           
                ), trackdatetime) as TOTALValue
            from TABLE_NAME
            where trackdatetime between '2016-11-05 00:00' and '2016-11-05 23:59'
            and assetno = 'XXXXXXXXXXXXXXXXX' 
    ) t   
    Group by t.digital2

Here is the query to display the duration for which the battery is on/off for a given period and asset number.

    Select
    sum(t.TOTALValue) as durationMinutes, t.OnbatteryStatus 
    from (
              select  OnbatteryStatus, LAG(trackdatetime) OVER (ORDER BY      
              trackdatetime, OnbatteryStatus ) as prevtime
              ,Datediff(minute,LAG(trackdatetime) OVER (ORDER BY trackdatetime,                                              
              OnbatteryStatus ), trackdatetime) as TOTALValue
              from TABLE_NAME
              where trackdatetime between '2016-11-05 00:00' and '2016-11-05 23:59'
              and assetno = 'XXXXXXXXXXXXXXXXX' 
    ) t   
    Group by t.OnbatteryStatus

Step 3. Report design

Power BI Desktop is being used for designing the reports. Now the developers can easily design reports using direct query. Power BI Desktop offers a simple way for the developers to connect to the data source and design the reports using existing control or utilize the Power BI gallery for custom visualization control.

Step 4. Report deployment

For report deployment, we used a “provision sample” solution provided by Microsoft. The code is available here.

Step 5. Report rendering

For report rendering, we used the “embedded solution” sample provided by Microsoft from GitHub. The code is available here.

Challenge: The web application had been designed using Microsoft .NET Framework 4.0 and the customer is not willing to change it to model-view-controller (MVC) due to production timelines.

Solution: We wrote the following code to render a Power BI Embedded report in Microsoft .NET Framework 4.0 using Rest API. In the application, it creates and sends the app tokens that tell the Power BI Embedded service to render the requested report. This design doesn’t require the application to use Azure Active Directory for user authentication and authorization.

    Code Snippet 1– Power BI Rest API call.

    protected void Page_Load(object sender, EventArgs e)
    {
        string uri = "https://api.powerbi.com/v1.0/";
        WebRequest request = WebRequest.Create(uri + "collections/" + workspaceCollection + "/workspaces/" + workspaceId + "/reports");
        request.Headers.Add("Authorization", "AppKey " + accessKey);
        WebResponse response = request.GetResponse();
        Stream dataStream = response.GetResponseStream();
        StreamReader reader = new StreamReader(dataStream);
        string responseFromServer = reader.ReadToEnd();
        string[] Data = responseFromServer.Split('"');
        string EmbeddUrl = Data[21];
        int unixTimestamp = (int)(DateTime.UtcNow.Subtract(new DateTime(1970, 1, 1))).TotalSeconds + 1 * 60 * 60;
        String pbieKey1 = "{\"typ\":\"JWT\",\"alg\":\"HS256\"}";
        String pbieKey2 = "{\"wid\":\""+workspaceId+"\",\"rid\":\""+ reportId+"\",\"wcn\":\""+ workspaceCollection+"\",\"iss\":\"PowerBISDK\",\"ver\":\"0.2.0\"," +
                "\"aud\":\""+ resourceid+"\",\"exp\":"+ unixTimestamp+"}";
        String pbieKey1n2ToBase64 = Convert.ToBase64String(System.Text.ASCIIEncoding.ASCII.GetBytes(pbieKey1)).TrimEnd(padding).Replace('+', '-').Replace('/', '_') + "." + Convert.ToBase64String(System.Text.ASCIIEncoding.ASCII.GetBytes(pbieKey2)).TrimEnd(padding).Replace('+', '-').Replace('/', '_');
        String pbieKey3 = HMAC256EncryptBase64UrlEncode(pbieKey1n2ToBase64, accessKey);
        String access_token = pbieKey1n2ToBase64 + "." + pbieKey3;
        accessTokenText.Value = access_token;
        embedUrlText.Value = EmbeddUrl;
        reader.Close();
        response.Close();
    }
    private String HMAC256EncryptBase64UrlEncode(String str, String accessKey)
    {
        byte[]
        key = Encoding.UTF8.GetBytes(accessKey);
        byte[]
        strBytes = Encoding.UTF8.GetBytes(str);
        HMACSHA256 hmac = new HMACSHA256(key);
        hmac.Initialize();
        byte[] rawHmac = hmac.ComputeHash(strBytes);
        string b64Str = Convert.ToBase64String(rawHmac);
        return b64Str.TrimEnd(padding).Replace('/', '_').Replace('+', '-').Replace("[", "").Replace("=", "").Replace("]", "");
    } 

GitHub link

Challenge: The customer wants to provide users with the ability to select multiple parameters to analyze data better. We suggested the use of Power BI JavaScript SDK. However, there was a problem in that we could not set the parameter position like we can in SQL Server Reporting Services. In this case, the parameter can’t be set in inner query. The filters passed from JavaScript will always add an outer query. The customer wanted an easier way to put the parameters in the report.

Solution: First we simplified the query and then used slicer control in Power BI Desktop. Below is the query after change. We simplified other queries as well. Each query in the report will have “trackdatetime” as a common column.

Here is the query to display the duration for which EB (energy meter) is on/off.

    Select  EBstatus, trackdatetime,LAG(trackdatetime) OVER (ORDER BY trackdatetime, EBstatus ) as prevtime
    ,Datediff(minute,LAG(trackdatetime) OVER (ORDER BY trackdatetime, EBstatus ), trackdatetime) as TOTALValue
    From TABLE_NAME

Here is the query to display the duration for which DG (diesel generator) is on/off.

     Select digital2, trackdatetime as trackdatetime2 ,LAG(trackdatetime) OVER (ORDER BY trackdatetime, digital2 ) as prevtime
    ,Datediff(minute,LAG(trackdatetime) OVER (ORDER BY trackdatetime, digital2 ), trackdatetime) as TOTALValue
    From TABLE_NAME

Here is the query to display the duration for which the battery is on/off.

    Select  OnbatteryStatus, trackdatetime as trackdatetime3,LAG(trackdatetime) OVER (ORDER BY trackdatetime, OnbatteryStatus ) as prevtime
    ,Datediff(minute,LAG(trackdatetime) OVER (ORDER BY trackdatetime, OnbatteryStatus ), trackdatetime) as TOTALValue
    From TABLE_NAME

Challenge: When slicer was added to the report, it showed each line for every record in Azure SQL Database. It’s not a good experience for the end user to select multiple rows in slicer to see data for one day. Below is how slicer shows up in the report.

Figure 2. Slicer view

slicer view

Solution: After researching, we figured out that changes were made for slicer control in a November update of Power BI Desktop. We checked and updated Power BI Desktop to the latest version (November 2016 as of now). Below is how slicer shows up in the report after applying the update.

Figure 3. Slicer view after November update

slicer update

Power BI reports

Here are some sample reports the customer designed and integrated with their current application.

Figure 4. Displays status of equipment based on Assetno and Date

equipment status

Figure 5. Displays site status

site status

Pictures from the hackathon

Figure 6. Team at work

hackathon pictures

Opportunities going forward

We will continue to engage with View360 to help them in designing and managing the Power BI Embedded reports.

Once they gather enough data, they will also use Azure Machine Learning for predictive analytics and show the same on the Power BI Embedded reports.

Recommendations

  • Always keep Power BI Desktop updated. This can be done automatically if “Display update notifications for Power BI Desktop” is checked under Updates in Options (File-> Options and Settings).
  • Check Power BI visual library for custom visuals.
  • Keep Azure SQL and Power BI Embedded in the same region. This is to avoid any egress charges.
  • Bring only data that is required for reporting.
  • For report deployment/maintenance, use existing samples provided by Microsoft in GitHub or use Power BI CLI.
  • Use app tokens for embedding reports in the application if not using Azure Active Directory. Set an expiration time with every app token.

Conclusion

The Microsoft and View360 teams worked closely to conclude a successful implementation of the Power BI Embedded solution. The View360 team had their solution running on AWS. We used Azure SQL Database and Power BI Embedded, which are platform as a service (PaaS) services. Hence the integration was quick and straightforward.

The Power BI Embedded solution will benefit various stakeholders and developers as follows:

  • Design Power BI reports with ease and more visualization control.
  • Monitor the health of the telecom tower.
  • Get detailed analysis of various devices.
  • Integrate dashboard on cross-platforms (web, mobile, tablet, and more) in the future.

Quote from the customer

View360 Director Satish Kottapalli:

“We are delighted to partner with Microsoft for the Power BI. With our existing solution, the UX was poor, as there was a case of information overload. The dashboards were comprehensive but a bit complex for the field support engineers to interpret. The new solution based on Power BI and Azure enables us to create a superior customer experience by dynamically highlighting only the most relevant data based on the site condition.

“The visualizations are also much cleaner and neater without the clutter of the previous design. This helps the end user in identifying the most relevant KPIs without having to scroll and filter through multiple selections.”