Crime and weather a curious insight

I had a few days between projects, so spent the time getting a bit more familiar with Power Query. I have used crime data to animate a Thermal Map in Power Maps before Here. But I was playing around with Azure Data Market to access the freely available data sets, one of which was the UK Met office weather data to look at animating weather using Power Map. Then i had a bit of a brain wave, I had two data sets, that matched time and space and had a look to see if the weather affected crime. The above graph indicates that the number of Anti Social Behaviour incidents follows the temperature. This isn't a deep statistical analysis, just a quick eyeball of the flow of the graphs but it is suggestive that something is going on for 2012 at least.

What is Big Data?

The party wasn’t a very good one, 75% of them where teachers for some reason, one person who I sort of knew from other gatherings worked for the HMRC and I was trying to avoid his gaze, not for any financial wrong doings I may add, just knowing governments need cash, and the next thing you know that they have made a mistake in my great grandfather’s PAYE stuff, and as governments really need the money and rather go after international tech companies, I’d get it in the wallet for the compound interest of a shillings mistake 120 years ago. The other person I was avoiding was my lovely wife who was giving me that look which was one of (or combination) of a few options.

1) So I guess I’m driving
2) I can’t believe you said that
3) Don’t mention that bit of gossip about you know what to you know who
4) You mentioned that bit of gossip about you know what to you know who

Somehow I had managed to be talking to the only other people at the party who also worked in IT, so it was a bit of casual shop talk for a bit, and the subject of ‘Big Data’ came up, and strangely all three of us had three different definitions of it. I tend to think of big data in terms of volume, tables with billions of rows or objects that need some sort of business intelligence around it.
The other guy was working for a marketing company and was seeing Big Data in terms of social media, and aggregations from a wide variety of websites and un-structured data.
The third was a business analyst and was talking to about Big Data in term of analytics on the volume or types of data.

Weirdly looking at Big Data definitions we were all correct, but me being me I like to think I was more correct than the others. One of the issues that the marketing guy had was processing the data and the strain on development and hardware resources in processing their customer data to find new insights. It’s not a new problem, when starting a project that would end up driving their industry changing store card, Tesco found that their hardware systems where struggling with processing the large amount of customer data. The data volumes where to just too big, but they hired some data experts who told them they could throw away 95% of the data and just use the remaining 5% to found out the shopping habits of the rest of the population. That has not changed in many business areas, but with the drive of targeted promotions and deeper analysis, there is also a need to process all the data and excuse the metaphor, squeeze as much of the juice as possible out of the customer orange.
But the other issue of Big Data is the mix of the types of data, you can have structured and unstructured data in the mix. Most business have structured data that tells them that they sold a product to this customer at this point in time and shipped it at this date. It’s the unstructured data that is the issue for a number of businesses. What is unstructured data? Well it is quite a mix of types, photos, social media posts, documents and other random data that is not normally time and space specific.

Typically mixing those two types of data has always been tricky, structured data is well known as SQL based technology, relational tables for your related data. Unstructured data has been called ‘No-SQL’ getting rid of the SQL concept to some degree, and using more complex languages such as java to query data sources. With the normal marketing hype, ‘No-SQL’ was the SQL killer, all databases would soon be No-SQL but this hasn’t quite happened, as each type of database is being used for the right job, however some companies have found that after implementing a ‘No-SQL’ solution, they returned back to a regular SQL database model. Another thing that has happened with a number of tools sets such as Microsoft SQL Server and Teradata, is that unstructured data has now been brought into the SQL tool sets, so you can query unstructured data just like structured data. (Technically Unstructured data is a bit of a myth, it all has structure, more in a later post)

Strangely companies using the right tool for the right job is not always done. I was once in a meeting were the Chief Information Officer had been talking to a social media expert and was looking to get this brand new social media platform implemented, which had enabled companies like HP, BT, and other organisations reduce their support costs, by offering a social platform were the users could post their issues and get help from the online community. Thus leveraging the fact that 5% of the users on the social media platforms are pathological helpful. However the question put to him stopped the vision from becoming a reality, as it questioned the use of this technology in this company’s customer engagement and market sector. That question was ‘Erm…. Exactly how do you support a sandwich?’

Anyway back to the party, after some lamentations by the other two I saw the answer to their problems in three words Parallel Data Warehouse (PDW). Oh and the Cloud, that’s four words. Normally when something is chewing some data on a computer its one computer querying the database hosted on it. With PDW the data is split among lots of computers (nodes) all doing the bit of data that it holds, then after a bit of aggregation returns the dataset. Or to put it other way it’s like the difference of if it takes one man to build a wall in 3 hours, how long will it take 12 men?

PDW has been called Microsoft’s best kept secret in terms of analytics, and is hosted in the cloud, which has access to loads of nodes on the server farms around the word. PDW can take tables of billions and billions of rows of data and return it a lot quicker than normal SQL Server, it also deals with unstructured data the same way, delivering insight from all types of data. That just one of the benefits, it also has the added benefit of the usual Microsoft Clouds options, of only paying for what you need when you use it.
I told the other two about it, once again proving that the MD should approve my request to change my job title from ‘Senior Consultant’ to ‘Data visionary and information guru who pushes back the boundaries of ignorance’, the glow of being awesome stopped only when I got in the car with my wife on the drive back home!

Warning Business Intelligence

Ten years have now gone by, I can tell the story of how my IT Skills may have made people lose their jobs.

It’s about 2006/07 and I was working as a Customer Service Team Engineering Gatekeeper at what was then Finning Materials Handling. Basically it was a fancy job title for controlling the field engineer’s assigned forklifts and other equipment. The details got updated, I ran reports and generally tried to chat up temps, and anything with a XX chromosome really. Despite being in such a target rich environment I got a batting average of zero.
Not one.
Nothing.
Nada.
I also sat next to the most excellent Steve Rowlands, who together we had chats and great bant’s.
Anyway, I should stick to the point, as we had acquired another forklift company called Lex Harvey the previous year, we were running two IT systems. One for the Caterpillar equipment (Finning stuff) and one for the Lex Harvey kit (All sorts of tat). What the issue was is that they were separate systems, no communication between the two so it required a bit of a brain melting issue to log even a breakdown for a customer.
Reporting from it was a right pain as it normally took about a day and a half to produce stuff, however I had a few tricks up my sleeve, mainly to automate the process with use of a VBA type script that could record me doing the reports once. Then all I had to do was a search and replace on the dates every week to run the reports. The process then took me about 2 mins, I just left the script to run on both systems, and boom, leaving me more time to check out the temps, ‘Hi how you doing, would you like a coffee? No it’s no hassle, I like my women how I like my coffee… thrown over me’
So one of the days I had nothing much to do, and realised that I could work out the total equipment numbers of all the teams in the UK, and do some analytics around it. Total numbers, ratio of engineers to forklifts stuff like that.
So I exported all the data into Excel 2003 (Feels like old school stuff) and quickly built up some numbers, and sent it on to the Service Area Managers. It went down a treat. The two from the north, whose names I forget but one of the guys looked like the ‘Cigarette smoking man’ out of the X-Files, went nuts over it. The North West one had done something similar, the North East guy (Mulder knows too much) had also done something similar, but one for the Finning side and one for the Lex side. So kudos all round for Mr Lunn for displaying initiative, technical skill and being awesome. I’d been promoted 3 times in 3 years, maybe this would be the path to the next one. We all got together and sorted out a few issues with the numbers, there were a few items no on the systems which one of our customer owned but we sorted out the servicing.
They then presented it to some other people, then tried to take the credit. It didn’t work…. Hahahaha fuckers!
Any hoo… the report got kicked up to higher management, then even higher management, then to the Chiefs, COO, CIO, CFO, and the big man, the CEO.
They loved it, they went nuts for it, I had some senior people come up to me, and say stuff like ‘Great piece of work Jon’, pats on the back and handshakes all round.
It then got serious.
Really fucking serious.
They started asking questions about the fleet numbers, detailed reports on it, and how I came to those numbers. Sure OK, here’s how I did it, showed them my working out, more comments like ‘It’s a great driver for the business’ and ‘It’s really important that the number reflect the reality on the ground’. So I showed them everything. They all nodded and said stuff like ‘Great, we’re very happy with the numbers now’.
Behind the scenes some meetings started going on, serious meetings, with serious questions, about serious decisions.
They dropped the bombshell, they were cutting back the number of engineers in the field and making redundancies.
Shit.
Fuck.
Shit fuck.
I felt like a right massive C word. I’d worked running engineers in the south, the midlands, but mostly the north east. They liked me, I was chatty, funny and I used to drive a forklift so knew what drivers did to piss them off when it comes to repairing them. So when they all come down for the meetings and HR stuff, they came and saw me, handed me their paperwork, I shook their hands said the usual platitudes.
Felt like an even more super massive C.
Anyway, it turned out I wasn’t the trigger for it, little did I know is that Finning were in talks with another company called Briggs, which would then purchase the forklift division, and I understand is that Finning wanted to make the company a bit leaner and improve the books. I, however, to some degree had helped.
So the company got took over, I got promoted to IT and Technical Analyst – Business Intelligence. However slightly more careful about what reports I ran.

SQL Server 2016 T-SQL updates

Looking through the T-SQL updates for SQL Server 2016 this one caught my eye DROP IF EXISTS.

So when you normally drop a table for example you use IF OBJECT_ID:

IF OBJECT_ID(N'dbo.MyTable') IS NOT NULL

DROP TABLE dbo.MyTable

now you can use:

DROP TABLE IF EXISTS dbo.MyTable

It’s not just for tables you can use it for object such as:

AGGREGATE
ASSEMBLY
DATABASE
DEFAULT
FUNCTION
INDEX
PROCEDURE
ROLE
RULE
SCHEMA
SECURITY
SEQUENCE
SYNONYM
TABLE
TRIGGER
TYPE
USER
VIEW

Not in the SQL Server Blog Post that announced this update but you can also drop COLUMN and CONSTRAINT

Be Colour aware

As I’m required to create visually creative and stunning dashboards and reports, and sometimes don’t have access to the Ridgian resident Lead Creative, and also sometimes the customer doesn’t have a defined corporate colour palette  (or Color to our across the pond friends) I have to throw one together, if the standard out of the box items doesn’t quite fit the bill. One drawback to this I’m ridiculously colour-blind, Red/Green Blue/Purple and many more it seems. One of my stock websites to go to is the Adobe colour wheel, with colour rules, like shades, complementary etc.
Great for those, “mmmh what goes with lime green?” moments. (Note: nothing goes with lime green).
Another great link is the W3 schools one, good for getting those Hex colour values that SSRS & Power BI likes.

Random Interactions

I was in London the other week, working away from home for a few days, and at the end of a busy day with a client I was catching up on my e-mails in a local coffee shop. It was quite busy and I sat in a space at a bench table arrangement, as it seemed more chic/trendy/cool not to have chairs, and when I looked up I noticed that I was the only PC laptop. The person next to me caught my slightly confused gaze and I remarked ‘What’s with all the Mac books?’ they replied ‘Mac books are more productive’ with a slight sneer at my good old reliable Dell. ‘Really?’ using the word with an inflection that only the British can do, ‘Mac’s are more productive?’ I replied ‘I can’t help but noticing that you are using Microsoft Word, that sort of defeats the point of your argument’. Checkmate!

Delimited Strings, CTE’s and Recursion

I was looking through a client’s SSIS package and noticed that they had a C# script task that pulled out data from a column with a delimited string using the good old semi-colon ‘;’. After looking at it for a bit, I thought it was a bit over engineered and didn’t really need a C# script to do it. So I’ve came up with a way of doing it in T-SQL, using a Common Table Expression (CTE) and an interesting feature of CTE’s, that they can self-reference themselves….but how. Let’s create the table and insert some data.

-- Create a table to hold the data
CREATE TABLE #SourceData
( SomeColumnOne INT
, SomeColumnTwo INT
, StringData  VARCHAR(MAX)
)
GO

-- Insert some data to use
INSERT #SourceData
VALUES
 (1, 2, '100;101;102')
, (3, 4, '200;201;202;203')
, (5, 6, '300;301;302;303;304') 
GO

-- Check everything is ok
SELECT SomeColumnOne
,  SomeColumnTwo
,  StringData
FROM
  #SourceData

SomeColumnOne	SomeColumnTwo	StringData
1	2	100;101;102
3	4	200;201;202;203
5	6	300;301;302;303;304

It may look like a little bit of data, but it was roughly the same data size coming from the customer’s source data, hence why I did think it was a bit of overkill in the first place. Here’s the code that I used:

;WITH
CTE_Source (SomeColumnOne, SomeColumnTwo, StringExtract, StringData) AS 
 ( SELECT SomeColumnOne
  , SomeColumnTwo
  , LEFT(StringData, CHARINDEX(';', StringData + ';') -1) 
  , STUFF(StringData, 1, CHARINDEX(';', StringData + ';'), '')  FROM 
   #SourceData
    
  UNION ALL
  -- This table references the cte, while in the cte!
  SELECT SomeColumnOne
  , SomeColumnTwo
  , LEFT(StringData, CHARINDEX(';' , StringData + ';') -1) 
  , STUFF(StringData, 1, CHARINDEX(';', StringData + ';'), '') 
  FROM 
   CTE_Source
  WHERE 
   StringData > ''
 )
SELECT
SomeColumnOne, SomeColumnTwo, String from CTE_Source

But what does it do?

Well the first SELECT statement just returns the first occurrence of the delimitated string:

SELECT SomeColumnOne
, SomeColumnTwo
, LEFT(StringData, CHARINDEX(';', StringData + ';') -1) AS StringExtract
, STUFF(StringData, 1, CHARINDEX(';', StringData + ';'), '') AS StringData
FROM 
 #SourceData

Which gives us

SomeColumnOne	SomeColumnTwo	StringExtract	StringData
1	2	100	101;102
3	4	200	201;202;203
5	6	300	301;302;303;304

as we are performing a union on the CTE itself with the second SELECT statement, it is iterating thought the string, the CHARINDEX & STUFF statements reduces down the string with each pass, until it returns ”.

So the second final result is

SomeColumnOne	SomeColumnTwo	String	StringData
1	2	100	101;102
1	2	101	102
1	2	102
3	4	201	202;203
3	4	202	203
3	4	203
3	4	200	201;202;203
5	6	300	301;302;303;304
5	6	301	302;303;304
5	6	302	303;304
5	6	303	304
5	6	304

You can get through a far bit of data this way, however you can run into the default recursion limit of 100, but there is a way to override this by using setting the max recursion option at the end of the CTE, which is OPTION(MAXRECURSION 0). Careful though, this setting is normally used stop a CTE from causing an infinite loop. In your face C#, T-SQL still rules!

Further reading:
CTE’s
CHARINDEXSTUFF