I want to use open source NHL data to create a data visualization that would offer value to end users.
I decided to use sports (specifically hockey) data for a couple of reasons. First, I really like hockey. Second, sports data is great for displaying complex data models which are conceptually easy to understand. This data offers information free of industry bias making it easy to establish cause and effect. Finally, sports data is publicly available and ready to be tapped into.
A little searching led me to the somewhat undocumented NHL Stats open API. I say somewhat undocumented, since some very smart individuals have created an unofficial documentation for the API which helps the likes of me use it.
Like most web APIs, the gist of this one is that I make an HTTP GET request and the API sends me back some data. The NHL Stats API returns data in a JSON format. Now, each call to the API returns a single "object", depending on which endpoint I am calling (e.g. calling the Team endpoint would return a "Team" object). The main endpoint I needed to call for this project was the Feed endpoint, which returns an object with all the events in a single game, along with some metadata about the game. Now, I could have visited the API with a web browser and saved the result to a JSON file, but the catch is that a single call returns data for a single game in a single season. To get all the data for games played in a single season would require me making almost 1500 calls! I needed to automate this. Plus, I ended up requiring data from more than one endpoint.
I was already familiar with it.
It has built in multi-threading and parameter configuration capabilities.
It would be easy to extend if I ever wanted to store the results in a database or integrate more data sources later.
All the SSIS package had to do was loop through some input parameters (which seasons to download and which games), call each endpoint for each game, and save the data to local JSON files. It took a while to download, but once it was done, I had local data that my BI applications could ingest.
The next step in my process was to load the data from the local repository to my BI application; for this project I was using Microsoft's Power BI to do both my data modelling and data visualizations. Luckily for me, Power BI has an option to load and combine files from a folder. Once I had my JSON files in a folder (grouped by source endpoint), I could apply my transformation steps in Power BI in one go and have them roll out to all the files. To start though, I just wanted to open and read all the files into memory. Unfortunately, I quickly ran into an issue.
Now, this isn't exactly the most helpful error message, and I admit, at first I started down the wrong rabbit hole. I won't get into all the troubleshooting details, but long story short I had one file (out of 16,000) that was not in a valid JSON structure, leading to this error. Once I had that figured out, it was easy enough to remove the offending file, and load data into Power BI.
So, what does that mean for my project? Well, it means I needed to add some of the cleaning steps of my ELT process before the data is ingested in Power BI (which I guess makes this an ETLT process, patent pending). For me, this was a simple exercise of adding a component to my SSIS package that confirms each API call resulted in a valid JSON string before saving it to a file. However, depending on the situation it could have been more complicated. The moral is, never trust your source for clean data!
JSON is a semi-structured data source. Most data visualization tools work best with relational data, meaning the next step in this process was to come up with a relational model for my data and transform my data into the new model.
There's no one nice way to model data, and no consistent way to transform data to get there. Even with the same dataset, it can be modelled in several ways in order to better answer different questions. For this project, since I was moving from semi-structured nested data that was also quite denormalized, my strategy was to flatten the source out and then renormalize the data. I did this by sectioning the subsets into tables and building what I needed to create my relationships and hierarchies.
Before I started transforming any data, I needed to know what I wanted it to end up as (at least at a conceptual level). Rather than trying to create a full relational model, I focused on defining my facts and dimensions. For this data model, I kept it quite simple, with three facts (goals, penalties, and faceoffs) and 3 dimensions (players, teams, and seasons).
For the few lucky data visualization projects where there are defined requirements about the target audience, what they want to see, and what questions they want answered, life's good. For the rest of us, often the main challenge to overcome is simply deciding what visualisations to display.
Referring back to the idea I used at the start this venture, all I said was that I wanted to create something that offered value to end users. To translate that idea into an example of a visualisation I could create, I went through a simplified thought process.
What end users do I want to target? I decided to target hockey analysts.
What is the job of the analysts? They want to predict when, or help, a team win games.
How do teams win hockey games? They score more goals than their opponents.
So now I that had an idea about what might be important to end users, I wanted to create a data visualisation with the focus being goal scoring. The next step was to figure how to do display the data.
Conveniently, some of the data that is included in the API is the location of the goal, in Cartesian coordinates. By plotting these points on a scatterplot and adding a background image of a hockey rink, we can have a nice visual of all the goals scored for a team. Add some coordinate normalization, a mirrored graph, and some filters, and the result is an interactive dashboard that lets a user select two teams and compare goal volume and locations. Now that's something that offers interesting, data-based insights into a game.
Sometimes, we don't know what Business Intelligence can offer us, or what we can do with it, until we try to actually do something. Creating quick prototypes and examples can be one way to see what is possible. Additionally, it can be an incredibly helpful tool in gathering requirements. Often, business users have trouble articulating what they want, but once they see what we have created, they can offer suggestions or wish lists for improvement. These steps offer one path for taking an idea for a prototype and turning it into some demoable.
Need help with gaining deeper business insights out of your data? Get in touch with our BI team.
Moving from ideation to activation as Carfinco drives towards a more innovative future.
Carfinco provides automotive loans for new and used vehicles to Canadians utilizing a network of franchises and select independent partners. In order to improve their overall customer experience, Carfinco identified the need for a new and more expansive online customer journey.
Self-Service Portal: Online self-service capabilities for customers coming through the traditional dealer-buyer relationship.
Direct to Consumer App: An innovative new way of interacting with current and potential customers.
Corporate Site: Redefining Carfinco's corporate online presence.
Armed with fully defined roadmaps and project plans, Carfinco needed a trusted IT partner to assist in implementing their new strategy.
A successful project requires more than great code; it's about partnership and working together to deliver software humans love to use. Following an insightful Ideation Workshop, Carfinco re-engaged DevFacto for the project implementation. With a deep understanding of Carfinco's requirements, the transfer of knowledge to the development teams was detailed and efficient.
With well established concepts from the ideation phase, DevFacto was able to begin creating a customer-centric self-service solution quickly.
The Carfinco Self-Service Portal was created as a responsive web application to ensure customers would enjoy the experience through both desktop and mobile access, while having functionality to manage their existing Carfinco loans and personal details.
The direct to consumer app was a completely new and innovative idea; the first of its kind within the industry. Eventually branded as "AutoArriba", the app was given a different look and feel to distinguish itself from Carfinco in the marketplace.
AutoArriba reached the goal of making private vehicle transactions easier by facilitating the loan application, approval, transfer of funds, sales agreement, and even onsite vehicle inspection. Once a loan is facilitated, consumers are then seamlessly directed to manage their loans in the Carfinco Self-Service Portal, connecting the two solutions and making life easier for consumers.
Carfinco's online web presence had not been updated significantly for close to a decade. Following the Ideation Workshop, a new website platform was selected, the site content was refreshed and the overall user experience was enhanced with help from DevFacto UX teams.
In addition to improving the site for visitors, Carfinco site administrators now have the ability to easily manage their public facing content, add temporary marketing pages, and even change design elements as their strategy evolves.
introduced a new brand (AutoArriba) for interacting with clients at the beginning of their vehicle loan journey.
Each of these new programs work together to establish a digital foundation that transforms the way Carfinco interacts with consumers, while also providing a platform for future innovation.
Galen Gower, VP Business Development, Carfinco Financial Group Inc.
Troy Graf, Chief Operating Officer, Carfinco Financial Group Inc.
Making an app is fairly easy. You have an idea, scribble some mockups on a napkin, spend a month or two hunched over your computer and then release it to the world. The overwhelming majority of apps are average or maybe even good. Taking it to the next level is much harder. What makes a good app great?