DATA608: Final Project
Donald Butler
12 May 2023
library(tidyverse)PowerBI Visualization Project
I work for Carrols Corporation which is the largest Burger King franchisee in the US. In my role, I am responsible for managing the Windows computer systems and the other networked devices in each of our 1000 restaurants.
I wanted to work with a dataset from my work and developed a data visualization using PowerBI with a dataset that I created for tracking the network status of each device in our restaurants. PowerBI is an enterprise tool, but I wanted to start learning it since it is the tool used in my organization.
Device Status Data
In each store there are a number of different types of computer systems. The Point-of-Sale terminals, Verifone credit card terminals, and digital menu boards are some systems that are visible to customers. Additionally, there are kitchen controllers that display orders to be made.
I developed a system that would poll all of the devices in each store and generate a result file that shows the status of each device and the last time it was online. This is an example file that shows the status of the 25 devices in the store.
datafolder <- "https://raw.githubusercontent.com/dab31415/DATA608/main/Final%20Project/Data"
store_df <- read.csv(paste0(datafolder,'/00101_DeviceStatus.csv'))
glimpse(store_df)## Rows: 25
## Columns: 9
## $ Site <int> 101, 101, 101, 101, 101, 101, 101, 101, 101, 101, 101, 10…
## $ PollDate <chr> "05/14/2023 15:35:54", "05/14/2023 15:35:54", "05/14/2023…
## $ DeviceId <int> 101, 201, 202, 203, 204, 301, 401, 402, 403, 404, 901, 90…
## $ DeviceName <chr> "Chef", "PHU Main-1", "PHU Main-2", "PHU Spec-1", "PHU Sp…
## $ DeviceType <chr> "Chef", "PHU", "PHU", "PHU", "PHU", "DTD", "IDMB", "IDMB"…
## $ Manufacturer <chr> "", "", "", "", "", "", "Sicom", "Sicom", "Sicom", "Sicom…
## $ IPAddress <chr> "10.116.64.20", "10.116.64.10", "10.116.64.11", "10.116.6…
## $ State <chr> "Online", "Online", "Online", "Online", "Online", "Online…
## $ LastOnline <chr> "05/14/2023 15:35:54", "05/14/2023 15:35:54", "05/14/2023…We are primarily interested in which devices are offline, and how long they have been down. At this store we have an inside menu board and the three drive-thru boards offline.
store_df %>% filter(State == 'Offline')## Site PollDate DeviceId DeviceName DeviceType Manufacturer
## 1 101 05/14/2023 15:35:54 403 Inside DMB-3 IDMB Sicom
## 2 101 05/14/2023 15:35:54 901 Lane-1 Disp-1 ODMB Stratacache
## 3 101 05/14/2023 15:35:54 902 OCU-1 OCU Stratacache
## 4 101 05/14/2023 15:35:54 903 Lane-1 Disp-3 ODMB Stratacache
## IPAddress State LastOnline
## 1 10.116.64.4 Offline 05/02/2023 16:47:10
## 2 10.122.64.8 Offline 04/07/2023 15:36:50
## 3 10.122.64.9 Offline 04/07/2023 15:36:50
## 4 10.122.64.10 Offline 04/07/2023 15:36:50Each morning, each stores results file is combined into a single report and sent via email to our internal helpdesk which is responsible for getting these devices back online.
offline_df <- read.csv(paste0(datafolder,'/DeviceStatus_Down.csv'))
glimpse(offline_df)## Rows: 888
## Columns: 10
## $ CLS <int> 101, 101, 101, 101, 105, 113, 119, 120, 132, 133, 133, 136…
## $ BKC <int> 10816, 10816, 10816, 10816, 7370, 16026, 2243, 1264, 8547,…
## $ Region <int> 8, 8, 8, 8, 8, 8, 8, 8, 8, 8, 8, 8, 14, 14, 14, 14, 14, 14…
## $ DM <chr> "Mark Hopkins", "Mark Hopkins", "Mark Hopkins", "Mark Hopk…
## $ Critical <chr> "Warning", "Warning", "Warning", "Warning", "Warning", "Wa…
## $ DeviceName <chr> "Inside DMB-3", "Lane-1 Disp-1", "OCU-1", "Lane-1 Disp-3",…
## $ IPAddress <chr> "10.116.64.4", "10.122.64.8", "10.122.64.9", "10.122.64.10…
## $ Status <chr> "Offline", "Offline", "Offline", "Offline", "Offline", "Of…
## $ LastOnline <chr> "05/02/2023 16:47:10", "04/07/2023 15:36:50", "04/07/2023 …
## $ DaysOffline <chr> "10", "35", "35", "35", "107", "258", "108", "0", "4", "54…head(offline_df)## CLS BKC Region DM Critical DeviceName IPAddress Status
## 1 101 10816 8 Mark Hopkins Warning Inside DMB-3 10.116.64.4 Offline
## 2 101 10816 8 Mark Hopkins Warning Lane-1 Disp-1 10.122.64.8 Offline
## 3 101 10816 8 Mark Hopkins Warning OCU-1 10.122.64.9 Offline
## 4 101 10816 8 Mark Hopkins Warning Lane-1 Disp-3 10.122.64.10 Offline
## 5 105 7370 8 Mark Hopkins Warning VF-1 10.115.64.153 Offline
## 6 113 16026 8 Michelle Weiss Warning Lane-1 Disp-3 10.122.65.138 Offline
## LastOnline DaysOffline
## 1 05/02/2023 16:47:10 10
## 2 04/07/2023 15:36:50 35
## 3 04/07/2023 15:36:50 35
## 4 04/07/2023 15:36:50 35
## 5 01/25/2023 15:37:58 107
## 6 08/27/2022 15:35:58 258PowerBI Visualization
I wanted to provide a means for operational managers to see the device status in the stores that they manage.
I created the visualization by loading the data into PowerBI with the Power Query interface. The raw data is shown in the table on the bottom. The sections on the top-left allow you to filter the data in various ways. As selections are made, each section responds to those selections.
Filter by Region
In the report, I created separate pages for each region. This changes the top-center visualization to represent district managers and stores.
Filter by Device Type
The slicer objects on the visualization filter the other sections of the report. Here I selected IDMB and ODMB which are the inside and outside digital menu boards.
Filter by District Manager
Filtering on a district manager will drill down to their stores. Each section of the report now shows the details for that specific manager.
Filter by Store
You can select specific stores in the top-center visualization. This filters the remaining sections based on those selections.
Report Access
This report has been shared to managers in a Microsoft Teams channel which they are members. This makes it easy to get updated data in their hands relatively quickly.
####Mobile Device
PowerBI is also accessible through mobile device apps. Here is an image captured on an iphone.
