Part 16 – S1 C1 P15 – How I Learned to Stop Worrying and Love the Data

So if you (at least) have gone through the last post to the end, downloaded the sql file, have SSMS and have run the script you will have a database with reference data and my first months data with Civil Service Strollers in the stg_all_data_t table. If you’ve never had any interaction with SQL you may now understand a few of its core tenets. Or not. Doesn’t matter. Though it will make this post a bit easier for you……

I’m not going to run through every bit of code we use here – just where new bits come up, but I will give you the lowdown on the why behind the code – hopefully that will help you make your own informed decisions.

I mentioned last time about using a Stored Procedure to transform the code and load it to the target tables (dbo.squad_x_t). A Stored Procedure is just a wrapper to allow a set of code to run. These can be nested, called from other applications, driven by parameters passed to them – they are very flexible. The one I’ve created for this process is called dbo.proc_squad_view_load

All the green type are notes that don’t effect the code running – a double minus-sign (–) denotes the note (A block can be denoted by /* */, with the note in between over many rows). Also note we are passing the game date to the procedure as a parameter (@game_date) as that isn’t available in the extract

A quick rundown of the process is as follows;

1. Put a row into batch_t – this is the batch number for the load
2. Clear the table down (TRUNCATE) and write all the player position codes to a hold table via a Temporary table (#POSSETUP) – if we get new ones we need the process to stop so we can add them to the position_name table.
3. If we get rows create a message in the msg_log_t to say what we’ve done – so you can query this table to debug if necessary, update the batch to U for unfinished and exit the procedure
4. If there are no new positions we can go on – next is checking the player_secondary_position_cd field. Repeat steps 2 & 3.
5. Same for player roles (player_AM_best_role_cd) in dbo.position_role_duty_t – Repeat steps 2 & 3
6. The squad_player load – transform the raw data into the finished product. We do lots of good stuff here – Here is a screenprint, we will have a quick look and what is happening

LTRIM(RTRIM(Column)) AS Column – we left and right trim text columns to make sure we have no leading or trailing spaces

CASE WHEN – This is a conditional statement – Do this if x = y else do something else

Dates (player_birth_dt) are stored as text so we need to make a date based on where the – between day and month, and month and year are located as the data from FM removes leading zeroes from a date (1/1/2000 instead of 01/01/2000) – CAST is used here to take the date parts and turn it into a date that is stored in the final table.

p1, p2 etc. are called aliases – the tell you which table the data is coming from – (In this case the position_name_t table) in the Join statement (How the tables work together) at the end:

Our raw data is in dbo.stg_all_data_t – most of the data is coming from there. We have the position id’s coming from the position name table, and we are basing the position we show based on the Assistant Managers view of the best position. We use position_role_duty for id to show the player’s best role and duty. We join again to the position_name table to get the actual position id based on the original position code, and we join to a subquery (The query in brackets) that brings back the secondary position id again from the position name table. We have a WHERE clause saying only bring back data if the Player Unique ID isn’t blank (!= ”) and lastly the joins are LEFT joins, meaning data must be on the table o the left hand side of the Join (So the stg_all_data table) but doesn’t have to be in the table on the right – if the data isn’t there you will get a NULL.

7. Insert a row into the msg_log_t to say the squad_player_t is loaded, and how many rows have been loaded.

8. Load the squad player attributes table – repeat 7 for this

9. Load the squad player statistics table – same

10. Load the squad player injury table – same

11. Load the squad player training table – same

12. Load the squad player contract table – same

13. Load the squad player attributes unpivot table – this is used to unpivot (move the column header data in table rows) for all the attributes so it can be more easily worked with when creating reports.

14 Update the batch table to complete and finish the procedure

There is also some error handling code that will enter info on any errors into the error_msg_log table.

To run this procedure you would use the following code:

EXECUTE [dbo].[proc_squad_view_load] ‘20190901’ – Where 20190901 is the YYYYMMDD for the game date I’m currently on.

The link to download this procedure is here: http://www.mediafire.com/file/w0jvt64v5ymh2pg/proc_squad_view_load.sql/file

When you get it make sure your connection is the PPFM_JM database and click execute – this compiles the procedure – and then you can run the EXECUTE code above to run it. Give it a go, plus take your time to have a look at the code and work out what it is doing.

You will see that you need to add a new role code to the [dbo].[position_role_duty_t] – BWM – You can do this by running the following in a query window:

UPDATE position_role_duty_t SET role_cd = ‘BWM’ WHERE role_nm = ‘ Ball-Winning Midfielder’

Try executing the proc again – It should complete. Now you have lots of lovely data in your tables 🙂

Take your time to have a look at the available data – hopefully you can start thinking of ways that you want to use it 🙂

Next time I’ll show you some go queries for looking at the data, and i’ll look and comment on my data so far, before getting back to FM for a few more games. Have fun 🙂