Presenting at SQL Saturday Exeter 2015 (YAY!)

stage-fright1

This year I have 3 new year’s resolutions.

  1. Start a blog – Done! You’re reading it 😛
  2. Gain the MCSE in both BI and the data platform – Working on it.
  3. Start presenting at user groups and conferences – Read on

I have been attending conferences for about the last 3 years. SQL Saturday Exeter in 2013 was the first database conference I attended. I had no idea what to expect, I remember thinking that everyone there would be a database guru and I would be so out of my depth. However this was not the case. SQL Saturday Exeter is run by the SQL South West UG, which at the time I had only been attending for about 4 months. The event was a great success! I learnt tonnes  and it sparked an interest that just keeps getting bigger – Presenting. To date, I have delivered lightning talks at SSW but nothing longer than 15 minutes. Since then I have attended about 10 conferences and always learn loads!

Now in its third year SQL Saturday Exeter is running again on the 24th and 25th of April. There is a paid for training day on the Friday and a free day on the Saturday (Click here to see the brilliant selection of sessions).  I am somewhat bias when it comes to SQL Saturday Exeter as I am co-organising the event, this meant I was unsure if I would have enough time to deliver a session. However I bit the bullet and submitted 3 sessions (on PowerPivot, TSQL and the last was on reporting on SQL Server Reporting Services). The last one was selected ! YAY!

So on the 24th of April at 15:00, in Exeter, you can come and see me present for the first time at a conference on “SSRS inception: Reporting on Reporting services” – Abstract below.

Still using Reporting Services? Yeah me too! Does this scenario sound familiar? You already have a large SSRS environment but the users still want more reports. You create a new, all singing, all dancing dashboard that removes the need for a bunch of reports, awesome. You launch it and explain to the business  that you are deprecating the old reports. “WAIT! We still need that for Jill in HR to do her end-of-month”, or “We might still need that” – these types of comments are what leads to having a load of reports that are not being used.

Wouldn’t it be great if you could see who is viewing a report, in what format and how often, all inside your current SSRS environment? With this data wouldn’t it then be even better if we could automate a process to email the owner of that report to say “HA! I told you no one looked at this report!”. In this session we will look at how we can do all of this and more by mining the SSRS DB.

This session came about from when I started working in my current role. I joined a company with a lot of SSRS reports (>150 – A mixture of dashboards and operational reporting). I was sure that there was no possible way that all these reports could be actually being used. So I set about making something that would prove me right. This has now been in production for about 5 months.

  • So what will you learn from this session?
  • You will learn about the SSRS DB and what it holds.
  • How to mine the SSRS DB for gold.
  • How to do this in a way that does not upset Microsoft.
  • A little bit about data warehousing
  • And a little bit of SSIS.

As there is a lot that has gone in to this project and I want it to be easy to use and understand I will write a blog in the next few weeks, that goes in to how each part is created before the session. All scripts, SSIS packages and the slides will be available to download before the session.

I am really looking forward to delivering my first conference session. I hope you like it and I look forward to seeing you in April.

If you cannot make it in April, I was also be presenting this session at SQL South West UG on the 16th of February, and at the Cardiff UG on the 31st of March.

Exeter, UK. Leading the way in Big Data

bigstock-Big-data-concept-in-word-tag-c-49922318

In recent months Big Data seems to have hit Exeter. With the announcement of a 97 million pound investment in to a new big data “Super Computer”, the Exeter based MET Office is set to make the South West a “world-leader in weather and climate change” and Big Data [1].

This increased attention on the South West seems to have prompted a surge of Big Data related training and conferences.

Unlocking Big Data – Investing in Human Capital 28/01/2015 [2]

The aim of the event is to help the Heart of the South West realise its Big Data potential by shaping the conversation and coming together to define the focus for our investment in Big Data Human Capital. It will also discuss possible funding proposals to the European Structural and Investment Fund Strategy.

There are 5 sessions tackling subjects such as building a Big Data team and the Big Data revolution. This is a free to attend conference held at the MET Office on the 28th January, and is organised by the Heart Of The South West (HOSW). The HOSW are expecting this event to be well subscribed so register early. Register here. [2]

Date: 28/05/2015
Start time: 08:45
Location: MET Office

Data visualisation workshop – 28/01/2015 [3]

The workshop is designed to encourage collaborations and discussions between bioscientists, mathematicians, systems biologists, physicists, geographers, medics, engineers, psychologists, computing scientists and anyone else with an interest in visualising large and/or complex data.

Unfortunately this workshop is on the same day as “Unlocking Big Data”. This workshop is being organised as part of Exeter University’s Big Data Challenges Series and The Exeter Imaging Network. This workshop is designed to promote cross-disciplinary networking regarding the challenges of visualising and interpreting data.

You can find details here.

Date: 28/01/2015
Start time: 13:30
Location: Exeter University Washington Singer 219

ExIStA-YSS: Early-Career Stats Showcase – 11/02/2015 [4]

The evening will begin with a series of eight-minute talks from eight speakers whose professions span the statistical spectrum, giving a unique insight into the variety of different careers that are available, and a flavour of the current challenges and future trends within each area.

Following on from the Data vis workshop, Exeter University will be hosting an evening showcasing young-career statisticians work or those working with statistics in the South West. The evening will feature 8 minute talks followed by snacks. Free to attend. Register via their website [4].

Date: 11/02/2015
Start time: 17:00
Location: Exeter University, Meeting Rooms 1-3 of the Forum, on the University of Exeter’s Streatham campus

SQL Saturday Exeter 2015 – 24/04/2015 & 25/04/2015 [5]
(Disclosure, I am an organiser of SQL Saturday Exeter).

Now in its third year, SQL Saturday Exeter is a community driven free to attend event on a huge variety of topics for every type of data professional. Sessions are not announced but in recent years sessions topics have included Parallel data warehousing, data visualisation, R & hadoop for Big Data.

24th April – Friday pre-conference training day. Whole day training on one of a number of sessions from world class SQL Server professionals. Sessions have not been announced yet, for more details follow #SQLSatExeter on Twitter or via the SQLSouthWest website [5]

25th April – SQL Saturday. Approximately 30 training sessions over 4 tracks covering all areas of working with SQL Server. Speakers will be a mixture of MVP’s, international speakers and people making their first appearances as speakers. Admittance to the Saturday event is free, all costs are covered by donations and sponsorships .

Please register soon as attendance is limited and tickets go fast!

Register here [6]

Date: 24/04/2015 & 25/04/2015
Start time: 08:00
Location: Jurys Inn, Exeter.

With 4 events in 4 months, Exeter is storming ahead in the Big Data sphere. If you’re having a big data event let me know in the comments.

[1] http://www.metoffice.gov.uk/news/releases/archive/2014/new-hpc
[2] http://www.eventbrite.co.uk/e/unlocking-big-data-investing-in-human-capital-registration-14775029511
[3] http://www.exeter.ac.uk/news/events/details/index.php?event=2562
[4] http://www.exista.org/events/exista-yss-showcase/#d.en.427409
[5] http://sqlsouthwest.co.uk/sql-saturday-372/
[6] https://www.sqlsaturday.com/372/eventhome.aspx

PIVOT! – Manual Pivot and the PIVOT function

Pivot

(Whenever I hear PIVOT this is what I think of, PIVOTTTT!!!)

Right, to start this series of posts I wanted to write about a method of manipulating data I refer to as a “manual PIVOT”.

For those who have use the TSQL PIVOT you with know it enables you to transpose data like you can do in excel (Rows become columns etc). For those who don’t already use it have you ever looked at the syntax in TSQL? It is horrible and almost impossible to remember (see below).

SELECT,
    [first pivoted column] AS,
    [second pivoted column] AS,
    ...
    [last pivoted column] AS
FROM
    (<select>)
    AS 
PIVOT
(
    ()
FOR
[]
    IN ( [first pivoted column], [second pivoted column],
    ... [last pivoted column])
) AS 
;
</select>

You can however create something similar using conditional logic.

If you’re using a version of SQL Server before SQL2012 then you can use a CASE statement, for those on a version greater than or equal to SQL2012 you can use either a CASE or the new Inline If (IIF). If you are unfamiliar with the CASE statement the syntax is as follows:

CASE WHEN Expression THEN Result WHEN Expression THEN Result ELSE Result END as x

Each “Expression” is a boolean, supplement with conditional logic which evaluates to TRUE.

For all of these examples I will use the AdventureWorks2012 database. If you don’t have this you can grab it from CodePlex

use AdventureWorks2012;
GO
 
SELECT CustomerID, OrderDate, TotalDue FROM Sales.SalesOrderHeader ORDER BY CustomerID

Pivot_Q1
The query above produces a simple list of what each customer has spent ordered by the customerID. What if we want to know how much each customer had spent each year? Well we could write something like this:

SELECT  CustomerID
		, YEAR(OrderDate) as 'OrderYear'
        , SUM(TotalDue) AS 'OrderTotal'
FROM    Sales.SalesOrderHeader
GROUP BY CustomerID, YEAR(OrderDate)
ORDER BY CustomerID

Pivot_Q2

This is fine but what if the requirement was to have years as columns to see the spend trend. This is where we can use the conditional logic to create a manual pivot. Using CASE and the YEAR(OrderDate) we can check to see which year the sales relate to.

SELECT  CustomerID
        , CASE WHEN YEAR(ORDERDate) = 2005 THEN (TotalDue) END AS '2005_OrderTotal'        
		, CASE WHEN YEAR(ORDERDate) = 2006 THEN (TotalDue) END AS '2006_OrderTotal'
        , CASE WHEN YEAR(ORDERDate) = 2007 THEN (TotalDue) END AS '2007_OrderTotal'
        , CASE WHEN YEAR(ORDERDate) = 2008 THEN (TotalDue) END AS '2008_OrderTotal'
        , CASE WHEN YEAR(ORDERDate) = 2009 THEN (TotalDue) END AS '2009_OrderTotal'
FROM    Sales.SalesOrderHeader
ORDER BY CustomerID

Pivot_Q3

The problem with this is that we have multiple rows for our customers. Where a customer has had a spend in 2005, all other years will show NULL. As we are looking for the total amount spent in each year we can simply SUM these totals and GROUP BY the CustomerID.

SELECT  CustomerID
        , SUM(CASE WHEN YEAR(ORDERDate) = 2005 THEN (TotalDue) END) AS '2005_OrderTotal'        
		, SUM(CASE WHEN YEAR(ORDERDate) = 2006 THEN (TotalDue) END) AS '2006_OrderTotal'
        , SUM(CASE WHEN YEAR(ORDERDate) = 2007 THEN (TotalDue) END) AS '2007_OrderTotal'
        , SUM(CASE WHEN YEAR(ORDERDate) = 2008 THEN (TotalDue) END) AS '2008_OrderTotal'
        , SUM(CASE WHEN YEAR(ORDERDate) = 2009 THEN (TotalDue) END) AS '2009_OrderTotal'
FROM    Sales.SalesOrderHeader
GROUP BY CustomerID
ORDER BY CustomerID

Pivot_Q4

This is now displaying one row for each customer with the amounts spent between 2005 and 2009 as columns.

This method works with all sorts of use cases and isn’t restricted to mathematical calculations. You can use this method to to calculate the top 3 products sold for each customer as columns for example.

; WITH RankedProducts AS 
(
SELECT  CustomerID
		, p.Name
		, SUM(OrderQty) OrderQty
		, ROW_NUMBER() OVER (PARTITION BY CustomerID ORDER BY SUM(OrderQty) DESC) OrderRank
FROM    Sales.SalesOrderDetail
		INNER JOIN Sales.SalesOrderHeader ON Sales.SalesOrderHeader.SalesOrderID = Sales.SalesOrderDetail.SalesOrderID
        INNER JOIN Production.Product p ON p.ProductID = Sales.SalesOrderDetail.ProductID
GROUP BY 
	CustomerID
		, p.Name
)
SELECT CustomerID
	, MAX(CASE WHEN OrderRank = 1 THEN Name ELSE NULL END) AS 'ProductSales1'
	, MAX(CASE WHEN OrderRank = 2 THEN Name ELSE NULL END) AS 'ProductSales2'
	, MAX(CASE WHEN OrderRank = 3 THEN Name ELSE NULL END) AS 'ProductSales3'
FROM RankedProducts
GROUP BY 
	CustomerID

Pivot_Q5

This works by apply in a MAX to the Name based on the OrderRank. OrderRank is calculated using a window function for more on window functions, see an upcoming post in this series. MAX works because it treats the Name as alphanumeric, which allows the MAX to take the word over the NULL as that is higher alphanumerically.

You can use this to work out all sorts scenarios.

Thanks for reading.

Lets get started

So I have been meaning to start blogging for the last few years. I love TSQL and I really enjoy solving tricky queries using a combination TSQL tricks. So this blog will focus on these topics as well as BI and DBA related fun.