Real time data collecting

This is the first part in my series on collecting data

I thought I would go back to the beginning and post about how I collect the data, as the reports are nothing without data. Most of the charts are based on data which is updated once a week, things like Goals or minutes played so the actual data capture is relatively simple, but for some of the data like transfers this gets updated throughout the week so I wanted to see what insights I could get from that. When I say transfers I’m referring to the way people move players in and out of their teams every week.

Collecting real-time data is something I have experience with as I designed a process for work, so I use the same building blocks. The overall design looks like this, but lets break it down.

The APIs I use are provided for anyone, so its just a case of having the knowledge on how to use them.

A Webjobs – runs continuously and gets the data, the data is too big to be sent to an Event Hub so I do some work on the data, I extract the nodes I want and send that to an even Hub but I also send the entire json file to a Storage account. The reason being I can use that data when I need it.

The data that is sent to the Event Hub is consumed by the Stream Analytics and ends up in an Azure SQL database. Within the Stream I can modify the data or add additional data if I so wish.

In my design I also want to merge this data with other data sources so I use Azure Data Factory to copy the data from the cloud to a SQL db on a local machine which is being used as a Data Warehouse and contains all the other football data. I have various processes running which collects different types of data.

At this point I have real football data and fantasy football together so I can start to blend it together to see what insights I can get.

This is an ongoing project for me which I have been working on as a side project for many years in which time I have changed and modified all the elements. Originally I used VB.net to write stuff but now I use C#, I removed ETLs which were based on SSIS and now I use Azure or even Python, cant wait till the support for Python in Function Apps is better 🙂

So that was a brief overview but it gives you the main parts and how I collect from that data source.

 

Next part will be based on the other data sources and how we blend data before visualizing it.

 

More new charts

I have been going through the data and seeing what I could come up with. I have created some new charts based on various pieces of data.

Here is a chart showing the last 6 season worth of data, highlighting average scored and conceded goals. Nothing really shows that we didn’t know.


 

 

 

 

 

 

 

This chart is based on the promoted sides and how they did in the first six games. This is more interesting as I always said don’t bet on the first 6 games as this is when the promoted teams do better, or perceived to do better as the other teams are getting used to them.

 

 

 

 

 

 

 

 

 

 

This chart goes into more details and shows how the promoted teams did in the following season. We can see things like;

  • Which teams from 3 to 6 actually got promoted.
  • Where the promoted teams finished in the following season to being promoted. I also grouped this by where they finished in the promotion season.
  • How many promoted teams stayed up.
  • Some stats on points.

 

 

 

 

 

 

 

 

These are just the first set of charts, I am starting to go through the data to see what information it tells me.

 

 

Its been an Age

Its been an age since my last post. Saying that I have been busy with work and things and the things have included re-development of the website and backend.

I have also been playing with Power BI a lot more. Here are some of my latest dashboards.

This chart is showing Home and Away goal difference by the current season.

 

This chart is showing Home and Away goal difference by various season.

This chart is showing home and away wins by teams by season, it also shows the percentage by season and it compares with the previous season to see if there has been more home wins or away wins.

 

 

It becomes more interesting when you look over more than 2 seasons.

 

I will be getting more active on here so keep an eye out for more posts.

 

Even more Power BI charts.

Another week and another Power BI chart, I’m looking forward to when we get all this good stuff in Azure.

This chart is looking at average points either at home or away for a given month and the selected teams, its all based on historical data since 2010.

average-points-by-team-and-month

There is so much more that this chart will do but I thought I would share it with you.

 

Power BI – Predictions

Still playing with Power BI, producing some charts for the Predictions.

This chart shows all predictions, for the test I have used 2 weeks worth of predictions.

week-22-preditions-pt1

You can select either all, not the latest or the latest predictions.

week-22-preditions-pt2

 

Then by selecting a game and hovering over the bubble you can see the stats, Arsenal home win against Watford, currently 0-2 down.

week-22-preditions-pt3

 

 

 

Power BI – Form of teams

Still playing with Power BI and looking at the data from the Predictor database.

This chart shows home and away form, the selectors on the left are for selecting League and teams

form1

If you click the charts the data will change. I have selected the Liverpool loss at home and the away charts shows you it was Swansea.

form2

 

If I select the Liverpool away losses on the away form chart, it highlights the teams that won on the home chart.

form3

 

If I use the slicers and once again select Liverpool, the home chart shows Liverpool only and the away chart shows the teams and the results.

form4

 

The use of Power BI makes the charts very interactive. As I’m looking at form charts I’m listening to Liverpool who are currently getting beat 1-2 at home to Wolves. January has been a bad month for Liverpool.

Demo charts & Dashboards using Power BI

Here are some charts/Dashboards that I have been playing with in Power BI, they are based on attack against defence, they are in the early stages of development so they will change and become more meaningful.

All teams from 20 leagues

bubble2

Specific only

bubble1

Premiership only

bubble3

 

Simple dashboard

bubble4

Simple dashboard with filters

bubble5

Power BI charts

Time has flown and I’m still not ready to roll out the new website, it is close, just need some time to get it uploaded and then some UAT.In the meantime I have been playing with Power BI, specifically the Globe 3D chart. The idea is to get all the stadium addresses in then plot the data on the globe.

globe-3d

Its good to zoom in and see close ups.

globe-3d-zoom

Then I have the normal charts, this one shows home goals and away goals and also most home goals in a league. I have only included certain leagues as I’m in the testing phase.

footy-data-dec-07

This chart shows the shots to goals ratio, just for the Premiership.

footy-data-shots-to-goals-dec-07

Anyway back to it.