Crestron Fusion meets Power BI

We have deployed and currently support quite a number of Crestron Fusion servers over the last few years. Most of these have been gathering – for quite a while now – at least some sort of data regarding our customers’ meeting rooms. With Fusion, Crestron does a great job providing us with monitoring and scheduling software that is quick to deploy and can efficiently scale over a large amount of rooms. While the Crestron processor gathers data from all available equipment, Fusion gives us an easy way for monitoring and storing all sorts of different events and statuses taking place in the room. For example, we can visualize when a certain display is turned on, what source is being displayed and which meetings are taking place at any given time. All that data is neatly stored in a SQL database, but… now what?

Fusion does come with an out-of-the-box reporting experience which unfortunately is currently rather limited. “Currently”, because I can imagine that some very smart people at Crestron are always working on expanding that functionality. In the meantime, however, we decided to leverage the possibilities of Microsoft Power BI to get more and better, interactive reports out of the same data.

All the events stored by Fusion are done so with the current state of the event and a timestamp, which gives us a lot of possibilities of reporting when and how many times certain events took place. It’s a bit more difficult to use that data, though, to get an understanding on how long a certain event took place. Fusion has some built in duration reports on standard aspects like display usage, online state and meetings. However, deriving similar insights from custom events and states proofs to be a bit more complicated.

Consider the following report created using Fusion’s default reporting functionality. It accurately provides an overview of how many times a given event occurred. As you can see the AV receiver in the first room has come online 18 times. For this particular state such a report doesn’t tell us all that much, because evidently the receiver has been offline an equal amount of times. After all, what goes up…

Now, if we process this data and add durations calculated for the online and offline states, this report actually provides us with more relevant insights. We wanted to come up with a report more like the next one. Here you can see, in the bottom circular graph, that the online state accounts for a substantial amount of the total duration. Granted, the offline state still has too big off a portion there, which is clearly related to the month November.

With that in mind, some of my colleagues (gotta love ’em) came up with a web application that connects to a Fusion database and performs a range of pre-processing and post-processing procedures on the available data. In doing so, we transform that data into a few new recordsets including duration for events of different time ranges. The work name of this application has been very creatively chosen to be “The Fusion Reports application” and in my opinion, one of the best features is that it can connect to any existing database to create new ways of reporting.

Here are some of the reports we have come up with so far. Keep in mind, these reports are in fact interactive. So on any given report, clicking one view will filter any other view on that same report. As such, the screenshots are not doing them any justice. Also, the data we used on these comes from actually running Fusion installs, which is why we decided to blur some aspects of the reports. We are working on a set of dummy data to test and showcase our progress by publishing public reports which you can manipulate at runtime. Stay tuned!

The following report shows how long a room has been offline or how long a device in that room has been offline. What we got from this report, in the bottom circular graph,  is that the online state clearly shows to be the biggest part of the total duration. However, the average duration for a room to be online without any errors is lower than 6 hours. So, although, the problems in the rooms are apparently self resolving, they occur regularly.

Note, the data used for these reports was captured during development and delivery of some projects. In fact, I would like to see much smaller red bars and bigger green bars.

Much like the “RX Online” report, the “Room online/offline” report can easily be repeated for any given device or other relevant state of equipment in the room. As shown in the following reports:

Using the interactive functionality of Power BI, reports can offer more then just an insight in relevant data. The following report provides an overview of all the processors in the installation, but also allows you to select any given processor and come up with some specific characteristics. The processors in this installation are all of the same model and are all running the same firmware, but in fact the two top right views are designed to provide an overview of uptime per firmware version and model.

The following report visualizes the display usage in the rooms, by showing both the amount of occurrences and the duration. Again, we can select and drill down on either a date or room and filter all other views for more detailed information. The bottom right view shows the amount of time the AV system is being used throughout the day. You can clearly tell that employees generally use the AV system longer in the morning and at the end of the day.

Apart from the display being used in the room, it seemed interesting to know which source types are most often used and whether some source types are generally being used longer than others. No big surprises there, HDMI is the big winner and there is a strong correlation between the amount of usages and the total duration.

And finally a must-have report. How many meetings take place (relative to date and room) and how long do these meetings last.

We are positive that the examples above proof that by slightly processing data, we can deliver quite some interesting insights on an already existing data set. During development of our application and by actually using it to come up with the example reports, we learned a thing or two on how you can get fooled when correlating incompatible datasets. But, maybe even more important, we learned that it all starts with efficiently capturing that data. Data analysis allows you to use statistics to level out possible large variances. However, when a certain event is incorrectly captured in the first place, it’ll be difficult to come up with accurate insights.

It’s been quite interesting to work on this and I’m sure we’ll be continuing our efforts to learn more about data in general and come up with even more practical use cases to offer to our customers.

Niko Brasseur