How is the role of the 'Analyst' changing in a data driven world?

     I remember the days when I was considered a top notch resource when it came to answering data driven questions. My Excel sheets were sparkling, my PowerPoint slides were beautiful, and my MS Access database was driving it all. In those days, when you needed to know how many widgets the sales team had sold, you asked them for their results sheet, and probably received it by email. When you needed to see attendance records for the call centers, they replied with their own spreadsheet, and a few paper based updates perhaps. After you had all your data collected and entered or imported to Access you could do with it what you pleased, and get some results worth sharing. But those days are gone, over the last 10 years I have witnessed an amazing transition from the scenario I describe above to the new paradigm of the 'Analyst' as power user and SQL dabbler. The data companies house internally is larger and more broad than ever before and growing quickly, and in order to present informed conclusions about a business, market or customer group, you need to be able to intelligently ask questions of these large stores of data.
     Enter the new 'Analyst', someone who is expected to know how to interact with databases like never before and to collate and present data from varied sources. Where a job description from those bygone days might have read "requires advanced MS Office suite knowledge" it is typical now to see requirements similar to "Must be familiar with SQL querying and tools such as SQL Server, Toad for SQL or PC SAS in order to gather results".

Searching Across SQL Server Part 3

To give other users access to use your SQL search script, but keep it centrally managed and compiled on your server, you need to implement it as a stored procedure or function. The choice between stored procedure and function for most installations is largely personal preference, I chose to go with a stored procedure, if you're interested in the relative merits of each there is some good reading here:

I have placed the code for the full stored procedure on git hub here: GitHub Full Procedure Code
I will only display the changes I made in the interest of keeping this post from filling your screen with code.
/*
Created by SQLWorks blog 2012
http://SQLWorks.blogspot.com
SQLWorkers@gmail.com
*/ 

CREATE PROCEDURE Usp_search @search_str VARCHAR(1000)
AS
    SET nocount ON 

...

I added the CREATE statements in the beginning, and made a couple of tweaks to the dynamic SQL in order to prevent duplicate results, see comments in the full code for details.

The usage is different of course, now you will use EXECUTE to run your new procedure and pass in the string you are searching for as a parameter as shown below.

EXECUTE Usp_search 'Employees' 


Which yields the following results showing this keyword is present in two object names:

Let's pass in a keyword present in column names:

EXECUTE Usp_search 'EmployeeID' 

The results yielded are shown below:

Now you can incorporate this procedure into jobs or other processes on your database server or in your applications, or base a report on it to allow users to search for items in your databases as well.

Part 4 coming up next, creating an SSRS report around this procedure

Go back to Part 1
Go back to Part 2

NTILE ranking functions in SQL Server

Following the previous post on the ROW_NUMBER function, lets look at other rank type functions in SQL Server and how you can use them.
Books Online does a good job of explaining the syntax, so I will link to it here and not go into too much detail beside to place this link. Ranking functions via MSDN

So, now that you have the syntax, where would you use these functions? what kind of questions can they help you answer with your data? Here is a scenario, suppose your boss asks you to apply the 10-80-10 rule (placing employees into performance groups comprising 10% (high) 80% (medium) 10% (low) ) to a group of employees every week, so they can evaluate who is performing above and below expectations. You could use any of the ranking functions to achieve this, but the quartile function works the best because it will always divide the group of employees into the number of quartiles you specify, so if people come and go you wont have to worry about counting how many are in each rank to determine over all percentile. Example below.

Here is the test data first:

CREATE TABLE dbo.EmployeePerf
(
employeeid INT NOT NULL,
employeename VARCHAR(25) NOT NULL,
sales INT,
timeperiod INT
)

INSERT INTO employeeperf VALUES (1, 'Tony', 100, 1)

INSERT INTO employeeperf VALUES (1, 'Tony', 300, 2)

INSERT INTO employeeperf VALUES (1, 'Tony', 200, 3)

INSERT INTO employeeperf VALUES (1, 'Tony', 150, 4)

INSERT INTO employeeperf VALUES (1, 'Meg', 900, 1)

INSERT INTO employeeperf VALUES (1, 'Meg', 400, 2)

INSERT INTO employeeperf VALUES (1, 'Meg', 500, 3)

INSERT INTO employeeperf VALUES (1, 'Meg', 850, 4)

INSERT INTO employeeperf VALUES (1, 'Ben', 100, 1)

INSERT INTO employeeperf VALUES (1, 'Ben', 40, 2)

INSERT INTO employeeperf VALUES (1, 'Ben', 50, 3)

INSERT INTO employeeperf VALUES (1, 'Ben', 90, 4)
INSERT INTO employeeperf VALUES (1, 'Jim', 1000, 1)

INSERT INTO employeeperf VALUES (1, 'Jim', 3000, 2)

INSERT INTO employeeperf VALUES (1, 'Jim', 2000, 3)

INSERT INTO employeeperf VALUES (1, 'Jim', 1500, 4)

INSERT INTO employeeperf VALUES (1, 'Abigail', 90, 1)

INSERT INTO employeeperf VALUES (1, 'Abigail', 40, 2)

INSERT INTO employeeperf VALUES (1, 'Abigail', 500, 3)

INSERT INTO employeeperf VALUES (1, 'Abigail', 80, 4)

INSERT INTO employeeperf VALUES (1, 'Mike', 1000, 1)

INSERT INTO employeeperf VALUES (1, 'Mike', 840, 2)

INSERT INTO employeeperf VALUES (1, 'Mike', 350, 3)

INSERT INTO employeeperf VALUES (1, 'Mike', 190, 4)

INSERT INTO employeeperf VALUES (1, 'John', 10, 1)

INSERT INTO employeeperf VALUES (1, 'John', 30, 2)

INSERT INTO employeeperf VALUES (1, 'John', 20, 3)

INSERT INTO employeeperf VALUES (1, 'John', 10, 4)

INSERT INTO employeeperf VALUES (1, 'Deb', 500, 1)

INSERT INTO employeeperf VALUES (1, 'Deb', 400, 2)

INSERT INTO employeeperf VALUES (1, 'Deb', 200, 3)

INSERT INTO employeeperf VALUES (1, 'Deb', 180, 4)

INSERT INTO employeeperf VALUES (1, 'Peter', 100, 1)

INSERT INTO employeeperf VALUES (1, 'Peter', 740, 2)

INSERT INTO employeeperf VALUES (1, 'Peter', 650, 3)

INSERT INTO employeeperf VALUES (1, 'Peter', 890, 4)

INSERT INTO employeeperf VALUES (1, 'Bill', 230, 1)

INSERT INTO employeeperf VALUES (1, 'Bill', 450, 2)

INSERT INTO employeeperf VALUES (1, 'Bill', 245, 3)

INSERT INTO employeeperf VALUES (1, 'Bill', 180, 4)

INSERT INTO employeeperf VALUES (1, 'Julie', 50, 1)

INSERT INTO employeeperf VALUES (1, 'Julie', 40, 2)

INSERT INTO employeeperf VALUES (1, 'Julie', 20, 3)

INSERT INTO employeeperf VALUES (1, 'Julie', 10, 4)

INSERT INTO employeeperf VALUES (1, 'Paul', 1000, 1)

INSERT INTO employeeperf VALUES (1, 'Paul', 700, 2)

INSERT INTO employeeperf VALUES (1, 'Paul', 600, 3)

INSERT INTO employeeperf VALUES (1, 'Paul', 590, 4)

Now to divide these employees up into the requested 10-80-10 we can apply the NTILE() function like this:

SELECT employeeid, employeename, sales, timeperiod, Ntile(10) OVER (ORDER BY sales) AS Quartile
  FROM employeeperf 
  WHERE timeperiod = 1

This gives us our results, the employees group in 10% increments by sales for the time period we care about, but what if you don't want to re-run it for each time period, and you only want to display those in the top and bottom 10% brackets? A common need for performance management, lets change the code to the following:

;WITH salescte 
AS 
( 
SELECT employeeid, employeename, sales, timeperiod, 
     Ntile(10) OVER (partition BY timeperiod ORDER BY sales) AS Quartile  -- added PARTITION BY
  FROM employeeperf 
  --WHERE TimePeriod = 1    <-- removed to include all periods 
) 

SELECT employeeid, employeename, sales, timeperiod, quartile 
  FROM salescte 
  WHERE quartile IN (1, 10) 
    ORDER BY timeperiod, quartile ASC


And the results are exactly what we wanted, the top and bottom 10%

My random test data is a bit skewed, as you can see some sales are radically different, if you were in a more normalized environment the number of people in each quartile would be more even. Hope this is useful.

ROW_NUMBER and other ranking functions in SQL Server

Have you ever needed to select the records in one column based on the contents of another column? It's a pretty common request, but what to do if you need to select from one column based on the max or min values from another column? Lets base this on a table like this:

CREATE TABLE dbo.SalesRecords
 (
 SalesPersonID int NOT NULL,
 SaleDate datetime,
 SaleCategory smallint,
 SaleAmount money
 )

INSERT INTO dbo.SalesRecords VALUES(100, '10/2/2012', 1, 200.00)
INSERT INTO dbo.SalesRecords VALUES(100, '10/1/2012', 2, 200.00)
INSERT INTO dbo.SalesRecords VALUES(100, '9/1/2012', 1, 300.00)
INSERT INTO dbo.SalesRecords VALUES(100, '9/2/2012', 2, 400.00)
INSERT INTO dbo.SalesRecords VALUES(100, '9/15/2012', 2, 100.00)
INSERT INTO dbo.SalesRecords VALUES(100, '9/20/2012', 1, 250.00)
INSERT INTO dbo.SalesRecords VALUES(200, '10/2/2012', 1, 200.00)
INSERT INTO dbo.SalesRecords VALUES(200, '10/1/2012', 2, 200.00)
INSERT INTO dbo.SalesRecords VALUES(200, '9/1/2012', 1, 300.00)
INSERT INTO dbo.SalesRecords VALUES(200, '9/2/2012', 2, 400.00)
INSERT INTO dbo.SalesRecords VALUES(200, '9/15/2012', 2, 100.00)
INSERT INTO dbo.SalesRecords VALUES(200, '9/20/2012', 1, 250.00)

You may be asked to return a list of the date and amount of first sale made in each month by each sales person. We could try to do that like this, by using the MIN function on the date, but this gives us the SaleAmount for each day, not the min for each month.

SELECT SalesPersonID, saleamount, MIN(saledate)
 FROM salesrecords
 WHERE saledate between '9/1/2012' and '9/30/2012'
  GROUP BY SalesPersonID, saleamount, saledate
  HAVING saledate = MIN(saledate)

A more effective way to handle this is using ROW_NUMBER() as follows:

;WITH ROW_NUM as
(
SELECT SalesPersonID, 
       SaleDate, 
       SaleCategory, 
       SaleAmount, 
       ROW_NUMBER() OVER (PARTITION BY SalesPersonID, datepart(MM,saledate) ORDER BY SaleDate ASC ) as 'Sequence'
 FROM SalesRecords
)

If we just select all from the common Table Expression ROW_NUM, the results look like this:

So you can see the ROW_NUMBER function has created sequential numbers which restart at the beginning of each month, for each SalesPersonID. In the PARTITION clause of your ROW_NUMBER function you may specify one, or multiple columns to define how the sequential numbers are ordered. Since we chose SalesPersonID and the Month part of sale date, the numbers restart at each change in Month for each SalesPersonID. In order to answer our question on the first sale of each month for each SalesPersonID now we only need to run the following against our CTE:

SELECT SalesPersonID, SaleAmount, SaleDate 
FROM ROW_NUM
WHERE sequence = 1
ORDER BY SaleDate DESC

And it returns a list of the SaleAmount of the first sale of each month for each SalesPersonID as shown here:

If you were interested in the last sale of the month instead of the first you could simply change the ORDER BY hint from ASC to DESC in your ROW_NUMBER() function and you would get the opposite results, sequence number 1 would be the last sale instead of the first.

Part 2 coming later...other ranking functions in SQL Server

Part 2 is now up...NTILE() function usage

Integrating Sharepoint List data into SQL Server via SSIS Package

Have you ever wanted to be able to query your SharePoint lists from SQL Server? 

This seems to be a fairly common request so I wanted to demonstrate how to best accomplish this, this post was created using Visual Studio 2010 on a computer running SQL Server 2012, but will work back to SQL Server 2005. So by default your list of sources and destinations in an SSIS data flow task looks something like this, with variations between the versions. Notably absent is SharePoint...

So to remedy this, you COULD use a script task to write a web service call in a .NET language that pulls information from the SharePoint web service, if you have access and your SharePoint admin set it up correctly, however there is a much easier way. The SharePoint List adapter add-in for SSIS, available here on CodePlex . This is open source, so getting your IT dept to allow its use in the enterprise can take some work, but it is worth it. After a very quick install (follow the wizard...), your new source and destination options will include SharePoint Lists as shown below. I am not going into all the details of how to install this, codeplex covers all that already. If you have problems leave a comment and I'll try to help. MSDN agrees, this is the best way.

This is the result, just drag and drop these like any other source or destination in a data flow task. I have added some very helpful red arrows to illustrate whats going on here
.

jump to next post on using your list's GUID to avoid name change issues

UPDATE 10/17/2013

In yesterday's SQL PASS live stream Matt Masson mentioned in the SSIS talk that this SharePoint List adapter is the most popular codeplex item! So everyone who has been using this, you're doing it right. Until they release the OData source for SSIS and this is no longer needed of course...