Statistical Analysis for Data Science

To begin learning about Data Analysis and what tools are available understanding terminology is useful.

Data analysis is a body of methods that help to describe facts, detect patterns,
develop explanations, and test hypotheses. It is used in all of the sciences. It
is used in business, in administration, and in policy (Levine, J.H.)

Some of the Data Analysis tools around:

Statistical Analysis with R and Microsoft SQL Server 2012
http://blog.sqltrainer.com/2011/12/statistical-analysis-with-r-and.html

The R Project for Statistical Computing
R is a free software environment for statistical computing and graphics.
http://www.r-project.org/ . R Journal here: http://journal.r-project.org/current.html

RStudio
RStudio IDE is a powerful and productive user interface for R. 
http://www.rstudio.com/

R tutorial

Introductory tutorials for R which simplify many statistical computations and can be a powerful tool.  http://www.cyclismo.org/tutorial/R/

10 R Packages Every Data Scientist Should Know About
http://blog.yhathq.com/posts/10-R-packages-I-wish-I-knew-about-earlier.html

    sqldf (for selecting from data frames using SQL)
    forecast (for easy forecasting of time series)
    plyr (data aggregation)
    stringr (string manipulation)
    Database connection packages RPostgreSQL, RMYSQL, RMongo, RODBC, RSQLite
    lubridate (time and date manipulation)
    ggplot2 (data visulization)
    qcc (statistical quality control and QC charts)
    reshape2 (data restructuring)
    randomForest (random forest predictive models)

MSBI Academy
http://msbiacademy.com/
Learn Microsoft's BI software with an expert, using a library of free instructional videos. Topics cover the full range of Microsoft BI technologies from Data Modeling to Dashboard Design. 

Database Landscape Map

The latest visualization of the number of  databases in the market place by 451 Group. This is an interesting graphic showing the vast number of database engines in the database space at the current time. The graphic can be found here

http://blogs.the451group.com/information_management/2013/06/10/updated-database-landscape-map-june-2013/

SQL Relay 2013

I attended and helped at the SQL Server Relay  event  in Reading. It is an event run by community members who have a passion for SQL Server.  The day was full of great techincal sessions.




The Keynote SQL Server 2014 and the Data Platform was delivered by Anthony Saxby. SQL Server 2014 features were shared with technology that will help transform the data centre, unlock insight on data , enable people centric IT and increase mission critical performance. The In Memory feature can give performance gains, on average 10 times faster, without having to rewrite code. SQL Server 2014 is for the OLTP workload to compliment SQL Server 2012 and the BI workload enhancements. Many other features were discussed including the platform for hybrid cloud.

Hadoop, Big Data and SQL Server session delivered by Justin Langford discussed Hadoop, Horton Works and HD Insight and the paradigm shift of data being stored and then the structure being overlaid later. The session covered the characteristics of data and a comparison of the traditional database warehouse and map reduce.

Chris Webb  shared the Microsoft BI strategy, What’s New for BI in Excel 2013. The tools that are now available in Excel 2013 covered PowerPivot (part of the UI now), Power View (for creating dashboards), GeoFlow (3D Maps) and Data Explorer at public CTP. You need the Professional Plus edition license for these functions.

SQL Server Troubleshooting and Diagnostics by Neil Hambley shared a troubleshooting methodology using the SQLCAT flowchart “Troubleshooting SQL Server 2005/2008 Query Performance/Scalability Issues.” http://sqlcat.com/files/folders/280/download.aspx. The flowchart provides a structure to work with for troubleshooting performance issues which can be customised to each environment.

The Era of the Cloud OS is all about transforming the Data Centre. The transformation trends of cloud computing, new social media, consumerization of IT and data explosion have brought the need for building blocks and multi tenanted architectures. Automation with PowerShell, commodity hardware and virtualization are all tools linking with the cloud. Most SQL Server workloads can now be supported on virtualization with only a 6% overhead.

A great session was shared on getting the most from your SAN – file and file group design patterns. Stephen Archibold gave some useful references on articles to read to gain further insight.

Fast Track Data Warehouse 3.0 Reference Guide
http://msdn.microsoft.com/en-us/library/gg605238.aspx
Diagnosing and Resolving Latch Contention on SQL Server
http://sqlcat.com/whitepapers/archive/2011/07/05/diagnosing-and-resolving-latch-contention-on-sql-server.aspx
Maximizing SQL Server 2008 R2 Table scan speed from DSI Solid State Storage
http://henkvandervalk.com/maximizing-sql-server-2008-r2-table-scan-speed-from-dsi-solid-state-storage
How to Specify SQL Storage Requirements to your SAN Dude
http://blogs.prodata.ie/post/How-to-Specify-SQL-Storage-Requirements-to-your-SAN-Dude.aspx
Videos
The Art of War-Fast Track Data Warehouse & Fragmentation
http://sqlbits.com/Sessions/Event9/The_Art_of_War-Fast_Track_Data_Warehouse_and_Fragmentation
Partial Database Availability & Online Piecemeal Restore
http://technet.microsoft.com/en-us/sqlserver/gg545009.aspx

The last session of the day was on Parallel Data Warehouse by Ryan Simpson which shared details about the massively parallel processing platform. The appliance is Data Warehousing best practices in a box. The disk layout and scale out designs were shared. 

Announcements at TechEd North America

Announcements were made today at TechEd North America about SQL Server 2014 and Azure.  The next Microsoft data platform is SQL Server 2014 which is for the enterprise cloud era and the role of data. This will be available in preview later this month.

The major components of SQL Server 2014 will be

• Hekaton’s in-memory OLTP in the box
• High-availability technologies that extend seamlessly into Windows Azure
• Simplified cloud backup
• Easy migration to Windows Azure Virtual Machines
• Improvements to AlwaysOn features, scale of deployment and ease of adoption
• Hadoop Connectivity
• Redfined engineering security processes, CC certification and compliance

Microsoft are looking at the role of data and how it integrates with the business. As a result they are  evolving analytics techniques for  Real-time data integration and adding more fluid visualizations .

Read about more the announcement details http://msft.it/6037kZqh and their summary image denotes the evolution of  SQL Server.















To register for trial evaluation of SQL Server 2014 and read more  http://www.microsoft.com/en-us/sqlserver/sql-server-2014.aspx

The announcements for Azure at TechEd can be found here: http://weblogs.asp.net/scottgu/archive/2013/06/03/windows-azure-announcing-major-improvements-for-dev-test-in-the-cloud.aspx

Microsoft unveils what’s next for enterprise IT below:
http://www.microsoft.com/en-us/news/Press/2013/jun13/06-03TechEd13PR.aspx

These are all enhancements to the other recent new data platform features codename 'Data Explorer', HDInsight, Polybase in Parallel Data Warehouse and codename 'Geoflow'.

SQLBits XI

SQLBits took place at the East Midlands Conference Centre in Nottingham between 2-4 May 2013. It is now the largest SQL Server conference outside of North America. It was the usual amazing conference full of deep dive training sessions, parallel tracks and networking.

I attended the excellent deep dive Extreme Data Recovery session by Argenis Fernandez. This session was great for drilling down into the actual database pages on disk and discussing the different algorithms used for page verification. He also mentioned an alert 829 to be aware of used by AlwaysOn for page verification errors. In SQL Server 2012 these 829 alerts are automatically fixed so it is important to monitor these. He gave a reminder that having a play book with scripts and tested resolutions to use give a sense of calm when dealing with the inevitable failures that occur from time to time. It contains a simple documented approach to be followed to resolution. A great resource can be found in the 31 Days of Disaster Recovery blogs.

The Keynote was delivered by Conor Cunnigham on Architecting Large Scale Services on Azure. Windows Azure SQL  Database (WASD) currently has a database size limit of 150GB, runs from commodity hardware and is used to scale out rather than scale up. Sharding the data requires a central metadata database to keep track of the shard and shardlets. Availability on WASD is 99.9% and there are automatic mechanisms to fail over databases when the load is too high, a machine dies or the rolling out of patches to the service happens. Key things to think about when architecting solutions to reside in the cloud is that database engine throttling can occur within a multi-tenant environment and it is necessary to design for unavailability.  Potentially cache more data and use defensive programming techniques such as implementing retry logic. Due to the charging models some database architecture designs need a complete rethink. WASD distributed database system uses an eventually consistent model. More details about how this works can be found in the Windows Azure SQL Database Performance and Elasticity Guide . This was an amazing keynote sharing a new way of thinking about database architecture design.

The day continued with Karen Delaney’s session Seeking SQL Server Secrets. This covered all manner of trace flags and undocumented stored procedures for 2008R2 and 2012. I attended various other sessions on troubleshooting on Azure, Inside xVelocity InMemory Engine, database design: size does matter, query tuning, ETL with Hadoop and MapRedure etc.

The diversity of sessions at SQLBits and hybrid tools available for the engine show this as an unprecedented time, where the database engine is rapidly growing and changing to meet the demands of the data market.
 
 

SQL Events 2013

The number of SQL Server events in the UK are still increasing in 2013.

SQLBits XI Nottingham 2–4 May 
http://sqlbits.com/

SQL in the City, London 21 / 22 June
http://sqlinthecity.red-gate.com/london-2013/

SQL Relay 2013
http://www.sqlrelay.co.uk/events/2013.html
Glasgow - Monday 17th June 2013   
Leeds  -  Tuesday 18th June 2013   
Birmingham -  Wednesday 19th June 2013   
Norwich  - Thursday 20th June 2013   
Cardiff  - Monday 24th June 2013   
Southampton  -  Tuesday 25th June 2013   
Reading -  Wednesday 26th June 2013   
London   - Thursday 27th June 2013

PASS SQL Saturday
http://www.sqlsaturday.com/
Jun 08, 2013  SQLSaturday #202 - Edinburgh 2013
Jun 22, 2013  SQLSaturday #229 - Dublin 2013
Sep 28, 2013 SQLSaturday #228 - Cambridge 2013

SQL Server User Groups  
http://sqlserverfaq.com 

SQL Lunch
London, Cardiff

Other Non UK Events 
 
PASS Business Analytics Conference
http://www.passbaconference.com/
April 10-12- 2013 Chicago, IL

SQL PASS 
http://www.sqlpass.org/summit/2013/  
October 15-18 2013, Charlotte, NC
 
PASS SQLRally Amsterdam
Amsterdam on November 6-8 2013
http://www.sqlpass.org/sqlrally/2013/amsterdam/

PASS SQLRally Nordic
Stockholm on November 4-6 2013
http://www.sqlpass.org/sqlrally/2013/nordic/

PASS SQL Saturday #194 Exeter

The second SQL Saturday in the UK was held in Exeter at the weekend. This was an excellent event for SQL professionals to learn new skills and network.  This event helped extend the breadth and depth of my knowledge on the data platform.

I attended a precon on High Availability Data Platforms for SQL Server 2012 with Windows 2012. This day was packed full of technical details and architectural design patterns for failover clustering and availability groups  including the current state of Windows 2012 features not yet supported on SQL Server 2012.  The new windows 2012 features, Resilient File Systems (Refs) and Data Deduplication are 2 features are not fully supported. Refs doesn’t support instance file initialisation or CHECKDB. 

On the SQL Saturday I attended sessions such as layered partitioning management of large data sets, a session on Parallel Data Warehouse (PDW ) and Polybase.  Polybase sits between the relational and non-relational engine and allows data to be queried using T-SQL.  Then I also attended a couple of sessions within the BI landscape on PowerView and MDX .

On a non technical track I attended a couple of sessions on professional development. These sessions covered the importance of having a learning plan for the year and the other on how to be a successful architect.  A few architecture models were mentioned such as ITIL service Management Application Lifecycle, TOGAF and Perspective Based Architecture (PBA). A few of the resources mentioned were

Microsoft Application Architecture Guide
http://msdn.microsoft.com/en-gb/library/ff650706.aspx
Microsoft Patterns and Practices, proven practices for predicable results
http://msdn.microsoft.com/en-us/library/ff921345.aspx

This was a great SQL event to start the year with.