Visualizing Problem Desktops Using Power Bi

Shortly after I completed my article on analyzing Edge DX data using Power Query, my colleague, Joel Stocker, created a video showing how he used Power BI to accomplish a similar goal – gaining insights into problem desktops within an organization using data obtained by Edge DX. He did two things that I found interesting: he used an API to pull the data down from Edge DX and then visualized that data on a map. 

In this blog, I will give a written summary of his video. First, however, I will explain the differences between Power Query and Power BI; if you are only interested in using Power BI, you can skip the next section. I will then show you how to use Edge DX data with Power BI using an API and then create a dashboard using that data.

Power Query vs. Power BI

Power Query and Power BI are powerful data analysis tools from Microsoft, but they have different purposes and unique features.

Power Query is a free Excel add-on used for data transformation. It allows users to connect to various data sources, clean and shape the data, and transform it into a usable format for analysis. I like it because it uses an SQL-like methodology for data cleansing, merging multiple datasets, and aggregating data from various sources. This data can then be charted using Excel.

In contrast, Power BI is a comprehensive business intelligence and data visualization tool. It allows users to create interactive reports and dashboards by connecting to numerous data sources, modeling data, and displaying the data using a variety of methods. While Power Query is often used in the data preparation stage, Power BI takes the prepared data and turns it into meaningful insights through charts, graphs, and interactive elements. Joel used these interactive elements to dive deeper into a particular device from the aggregated data.

Whereas Power Query is a free add-on to Excel, Power-BI is a separately licensed product. There is a free license for Power BI, but it has limited features and prohibits sharing or collaborating features with others. That said, I was able to accomplish everything described in this blog using the free version of Power BI.

Creating an API Key in Edge DX

If you have an existing API key, you can use it; if not, you can create a new one (you can read the ControlUp documentation on how to do so here).

Installing Power BI

Power BI can be downloaded from the Microsoft Store. I downloaded the Power BI Desktop version, which has a free license.

Power BI is installed like any other application from the store. Once installed, I created a shortcut on my Start menu and launched it without difficulty.

Connecting Power BI

I clicked Get Data from Another Source from the Power BI home dashboard. I then selected Web from the drop-down menu and clicked Connect. 

From the From Web wizard, I clicked Advanced. Then, in the URL Parts text box, I entered the URL of my tenant, followed by /api/data/  (i.e., https://Controluptechmarketing.sip.controlup.com/api/data/) 

In the HTTP text box, I entered x-api-key. Then, in the text box to the right of it, I entered my API key and clicked OK.

This returned the device indexes from my Edge DX tenant and displayed them in the Power Query editor.

I could choose which index I wanted to work with from this list. For this example, I will use information from the latest computer_details data index. The indexes may be postfixed by the week of the year (i.e., 20233700).

I clicked on New Source in Power Query and filled out the wizard with the same information as before, but I also included the data index I wanted to pull in the second URL Part text box. I then clicked OK.

This pulled in the data and added a new query to the Power Query editor.

I selected the new query and clicked List.

I selected all the rows of data and then clicked To Table.

This transformed the contents into a table.

I clicked on the column header and clicked OK.

I clicked Apply from the Close & Apply drop-down menu.

As I was going to create a location map of the devices, I needed to extract the latitude and longitude of the devices; this is held in the remote_address_geo column. After I selected the column, I clicked the Transform tab, and selected JSON.

The column now showed a value of Record. 

Clicking the icon at the far right of the column and clicking OK expanded the fields.

The single cell had both the latitude and longitude. This needed to be split into two different fields.

I selected the column, selected Transform from the toolbar, and then selected Split Column. I split the column by using a comma as the delimiter. I made a note of the new column names.

I selected the Home tab and clicked Apply.

Creating a Power BI Dashboard

Now that I had the data, I could do the exciting part of this project – creating a visualization of each device’s location in my tenant.

I went back to the Power BI home dashboard. 

I clicked Model View from the left-hand navigation pane and verified that my information was now shown.

I clicked Report View.

I clicked the Map icon.

I dragged the Latitude and Longitude columns from the Data pane to the Visualizations pane.

The map now displayed bubbles representing the devices’ locations.

To make the map more interesting, I dragged the name column to the Bubble Size box.

The size of the bubble is representative of the number of devices in that location. When the bubble is pointed at, more information about them is exposed.

Joel’s video goes through how to add additional features to the dashboard, such as displaying the city on the map and drilling down to get more details about the devices in a location.

Although the above example is somewhat of a rehash of the information displayed on the home dashboard of Edge DX, it shows how easy it is to create a map showing the location of devices in your organization. Furthermore, with a few additional steps, you could display additional data on the map or use one of Power BI’s other visualization tools to display data. 

Visualization is a powerful way to understand and help others understand issues and situations affecting your company’s end users. Combining Edge DX’s powerful, real-time data collection agent and API with Power BI’s powerful data transformation and visualization abilities allows you to quickly extend the capabilities of Edge DX to help you and others understand your environment. Of course, the API can be used with other solutions and products.

For more information, be sure to visit our Edge DX page or schedule a demo with a ControlUp sales engineer.