BCP – Create CSV files from SQL Server

I have been playing with my football data and getting it from files and inserting it into a database, but I decided that I wanted to extract the data as CSV files and add them to a Azure data lake then link it to Power BI.
Ignore the fact that there is a column called ‘JSONExtract’ 🙂
declare @season varchar(5), @leagueid int, @dateid int, @Command varchar(1000) , @BCPCommand varchar(1000), @File varchar(150),  @SQL varchar(500), @FullFile varchar(150)
set @File = 'C:\FootballData\Results'
declare db_results cursor for

select distinct se.seasonID, leagueid, dateid
  from [database].[schema].[table] hl
  join [data].[Season] se
  on se.Season = hl.Season
  and JSONExtract = 'N'
  order by 1, 2


open db_results
fetch next from db_results into @Season, @Leagueid, @dateid 
while @@FETCH_STATUS = 0
begin
truncate table [ThePredictor].[data].[json]

insert into [database].[schema].[table](LeagueID,Dateid, season, hometeamid, awayteamid,fthg,ftag,ftr,hthg,htag,htr)
   select LeagueID,Dateid, hl.season, hometeamid, awayteamid,fthg,ftag,ftr,hthg,htag,htr
   from [database].[schema].[table] hl join [thepredictor].[data].[Season] se
   on hl.season = se.season
   where se.Seasonid = @Season
   and Leagueid = @LeagueID
   and DateID = @DateIDset @sql = 'select * from [ThePredictor].[data].[json] for json auto'
   set @FullFile = CONCAT(@File, @Season, @Leagueid, @dateid,'.csv')
   set @BCPCommand = 'BCP ' + '"' + @SQL + '" queryout ' +  @FullFile + ' -c -t, -T -S server\instance'

--Update the table so only the latest records are extracted.
  update [database].[schema].[table]
  set JSONExtract = 'Y'
  where Leagueid = @LeagueID
  and DateID = @DateID

execute xp_cmdshell @BCPCommand
fetch next from db_results into @Season, @Leagueid, @dateid
end
close db_results
deallocate db_results

After I run the SP I get a series of files which I then upload to a data lake.


 

 

 

 

 

Using Power BI I connect to the data lake and I import the files. At this stage I still need to merge the files.


 

 

 

 

After merging I get a single data set.


 

 

 

 

At this point I can create a simple chart.


 

 

 

 

 

 

Now, this was just a simple example of creating files from a database/data warehouse and using the files in Power BI.

The possibilities are endless really.

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