Here at Bime we have some football fanatics who use the tool to track statistics throughout the season. Season-long datasets are great, with 20 teams playing 38 matches giving you lots of datapoints to work with, but tournament football can be a bit trickier as there are fewer teams, working in a group structure, and then knock-out rounds reducing the data points still further. So, for the Africa Cup of Nations, we came up with an idea that was a little different - a 'Fantasy League' for clubs. With Afcon happening mid-season, many fans are bemoaning losing key players to the tournament, so this might help to cheer them up! Each club would win points for their players' performance for their national team. But how could that actually be done?
Answer: the Query Blender!
This may seem a frivolous topic, but the structure of the data involved is actually very similar to what a lot of our clients are looking at - a static squad-list of all the players available (in real-BI-life, a product list, staff roster, or property portfolio) and a record of selections, goals, and cards per match (like an event log, a sales record, a stock movement report). With the Query Blender you can analyse the trends and events of the latter against the permanent details of the former, at the aggregated level.
So - in our squad list (taken from Wikipedia) we have the player names and what country they represent, along with their club and league affiliation; in our match reports, we show which players started or were substitutes, the goals scored, goals conceded, bookings and sendings off. And because we have a common element in both - here, player name, but it could be a product code, a staff ID number, a stock reference - we use that as the join key so the Query Blender brings them both together!
The Query Blender allows to you to define multiple joins, and also the nature of the join. We chose a right join to ensure we will see all the clubs and club countries in our list, even if they have not won any points yet - sometimes the absence of information is as useful as its presence! Then, we can use an attribute from one file (club) with a measure from another (points), to build up our league - and apply filters to focus in on particularly club leagues. We can even create calculations across the join - using measures from both datasets to create a success rate (here, points per player, but maybe an ROI, or generating an income report from static pricing and dynamic order quantity information).
The Query Blender is therefore a quick and easy way to bring your different datasets together so you can see results in greater detail; you can blend your sales records with your web analytics with your CRM, to get a really deep look at your data. Also, it lets us see which club will 'win' the Cup of Nations.