My background is as a software engineer, which prior to becoming certified for SQL Server, included 11 years as a Windows C++ developer at PricewaterhouseCoopers and 8 years as a software developer, database developer and Oracle DBA at Seven Telematics Ltd.
The latter included significant Oracle PL/SQL development as well as full installation, administration and support of their Linux-based Oracle 11g RAC cluster.
After leaving Seven Telematics in December 2015, I took the year off in 2016 to combine a career break with the study of Microsoft SQL Server, T-SQL, SSIS, SSRS and SSAS.
Following this I worked full time at IQVIA in Brighton, as a SQL Server database developer with a focus on ETL.
BI and ETL are ideal business areas for me because I can make full use of my software development expertise, my database expertise and my experience from many projects over the years in understanding and synthesising end-user requirements.
Focusing on SSIS for example, software development includes understanding the importance and use of source control, of designing modular, efficient logic that doesn’t repeat itself, of having well-defined interfaces, of meaningful testing and self-documenting workflows, and of designing for performance and restartability right from the start. (Purely meeting the business requirements for the ETL is a given!)
And regarding the database side of SSIS, my understanding of efficient SQL query design, together with fundamentals such as indexing, locking and performance tuning, helps with both ends of the ETL as well as with any staging in between. In particular I focus on keeping any T-SQL lean and minimal, e.g. by using T-SQL window functions to simplify queries where appropriate, as well as to maximise performance.
Because I wanted to continue my education by learning something new, and also because knowing both Oracle & SQL Server makes me a more rounded database developer than knowing just one or the other. And the whole Microsoft BI stack is a very attractive product suite in terms of the design & functionality of its component products, as well as the integration between them and the core SQL Server product itself.
The main reason for my work behind my blog posts was to give myself focused, hands-on experience of different aspects of the SQL Server BI Stack whilst I was prep’ing for the SQL Server certification exams. It’s impossible to pass these exams just by reading books or manuals, since they’re heavily oriented around testing real-world experience of the product. So I gave myself typical ETL & BI requirements & problems to research & solve, which I then implemented and documented via this website.
A common theme throughout my posts is that I’m implementing patterns and techniques designed by acknowledged experts in the field. So I’m not re-inventing the wheel each time with the risk of running into unanticipated pitfalls. Instead, I’m taking tried & tested solutions to problems and doing the work to implement them and to become familiar with the details of their internals.
Definitely – I passed the following between May 2016 and January 2017:
The first 3 were for my MCSA certification
and 70-466 is the first step towards MCSE Business Intelligence certification.
For me, the greatest benefit from doing these exams was being obliged to cover a huge breadth of functionality across all aspects of SQL Server and the BI Stack. In a work environment, time pressures can result in using only what you already know to solve a problem, whether it provides an optimal solution or not. Whereas the study required for these exams has given me a very broad knowledge base to work from, especially when combined with my prior work experience.
Very good – it was worth attending just for the full day pre-con session on SSIS Performance Design Patterns
, given by Microsoft’s Matt Masson. Matt’s a former member of the SSIS development team and a worldwide authority on the product.
Throughout the day Matt shared his practical experience gained from helping Microsoft customers implement high performance SSIS solutions. Topics covered included overall best practice guidelines (e.g. keeping data types narrow and designing algorithms to minimise the amount of work required), different degrees of parallelism (i.e. at the package, control flow & data flow levels) and how the internals of the SSIS data flow affect performance (e.g. synchronous / asynchronous transforms and buffer sizing).
After the pre-con day, other conference sessions I attended included T-SQL development, Biml (in the context of SSIS), accessing SSIS from PowerShell, optimising bulk database loads, and new features of the SQL Server 2016 BI stack.