25 yrs. SQL Experience
My credibility as a BI developer is built upon having very good SQL skills. You’re not strong in BI unless you’re strong with SQL. This page lists some chronological highlights to drive home the point that I have been doing this for a long time. There is also an online resume of companies and employment dates. Also, because this timeline speaks mostly to longevity, not necessarily competence, it is followed by an FAQ about my SQL skills and habits.
1994: First SQL Development for online/internet use, developing a newspaper listings database in Butler SQL Server and publishing it online with a graphical interface.
1996: First Microsoft SQL Experience, using SQL Server 6.5 to write custom accounting reports with MS Access as a front end to SQL.
1997: Developed a custom, content management system using SQL Server 7 for web publishing newspaper content.
1999: First use of Data Transformation Services, or DTS (which later became SSIS).
2000: Developed a complete telecom billing platform with an MSSQL 2000 back end utilizing SSIS and SSRS.
2002: Custom development of a web application using MySQL and PHP, one of Biziteks first clients, Travel & Golf.
2004: Completed one of the earliest integrations using Microsoft CRM. It connected CRM to an MRP application.
2006: Used SSRS to extend billing capabilities of PSA platform ConnectWise.
2008: Integrated a law firm’s case management system with a bank for EDI billing using SQL Server Integration Services (SSIS), following the specification provided by the financial institution.
2010: Created search engine on a SQL 2008 database for instant search results from hundreds of millions of public records and dynamically created clickable, force directed graphs from the data.
2012: Architect well rounded solutions combining SSRS reports and ASP.Net web pages to form browser-based corporate solutions.
2014: Devised reporting system on enterprise-wide Cisco Call Manager call traffic with SSIS and SSRS subscriptions using Microsoft SQL Server.
2016: Developed a Data Warehouse published in SSAS (multi-dimensional) using HR data from Peoplesoft for a large medical school in the USA.
2018: Used SSIS to rapidly develop a back-end to Power BI for dashboards.
2019: Developed a Data Warehouse published in SSAS Tabular to replace the above-mentioned data-prep and a legacy DW using multidimensional SSAS.
SQL Skills FAQ
I know that length of experience does not guarantee competency. Therefore I’ve pulled together a few typical SQL interview questions here. This might help further evaluate my competency. That being said, SQL or technical skills themselves are not what make me (or anyone else) valuable.
What is the difference between TRUNCATE and DELETE? When this question is asked, it’s often anticipating two aspects: DELETE can be run conditionally, and DELETE is logged. However, there is a lot more to that. TRUNCATE is minimally logged. Plus there are quite a few other differences, such as triggering and rollback. I am also mindful that TRUNCATE is DDL while DELETE is DML.
What is your favorite SQL function or statement? Lately it’s the windowing functions. You can do so much with them that you can actually define measures in the SQL DW as opposed to needing to do that in SSAS. I don’t think that’s always the best thing to do, but in a situation where a report is calling for a measure, and all you have is SQL, then windowing is fine. It still depends on the I/O of the server, though.
When you develop a solution in SSIS, do you write stored procedures and then call them in SSIS? No, I don’t. I do prefer the code editor in SSMS, though. The reason I don’t write sprocs for SSIS projects is for source control. I want the entire project to be within the source code. As a contractor, this is a very good habit. It prevents the code repository from being out of date with the production code because it’s too easy to edit the procedure in SSMS. Ultimately, though, this is up to the client and I like to be consistent with standards that are already in place.
How do you deal with some of the challenges of dates and time values? I don’t mess around with any supposed easier formats, like 20200101 (Jan 1, 2020). When I create a solution I typically use datetime offset as a data type for date and time data. It not only holds the full datetime value, but it also handles differences in timezones. People usually want to see local time in reports, though, and I am able to present what they want or need to see on the front end, while doing it properly on the back end.
Put My Experience to Work
I’ve listed out 25 years of SQL experience, but I recognize that technical experience alone does not qualify me to work with your organization. If you haven’t already, check out the web page that introduces who I am and how I am perfect for BI development. Please give me a call, and you’ll learn more about me and how I work. Ask me what’s on your mind, and if our conversation goes well, we can talk about the next steps to take with your SQL data.