SQL/ETL/BI 101

I'm going to compile a list of articles that provide introductions to topics in SQL and BI.

Introduction to Indexes, by Gail Shaw

Index of MS-SQL Articles on DatabaseJournal.com, a gold mine of introductory articles.

"What a Data Warehouse is Not" by Bill Inmon.

SSIS Data Cleansing Webinar

I watched a good webinar on SSIS Data Cleansing, by Brian Knight and the good folks of Pragmatic Works of Jacksonville FL. "In this session with SQL Server MVP, Brian Knight, you'll learn how to cleanse your data and apply business rules to your data in SSIS. Learn how to solve complex data problems quickly in SSIS using simple techniques in the data flow. Brian will start by showing you the Data Profiling Task. Then he'll show how to use transforms like Fuzzy Grouping to de-duplicate your data and SSIS scripts to satisfy common scenarios he sees in the industry."

Help, my database is corrupt. Now what?

Found a good article on SSC about database corruption by Gail Shaw:

"What to do when the database is corrupt.

1. Don't panic
2. Don't detach the database
3. Don't restart SQL
4. Don't just run repair.
5. Run an integrity check
6. Afterwards, do a root-cause analysis"
   

Don't have a corrupt database, but still want to play in the sandbox? Click here and here for a couple of ways to corrupt your database. (Don't do this to production data!)

And if all else fails, there's a product called Recovery for SQL Server to help fix your files.

SQL Server Execution Plans

Here's a link on SSC to a free PDF download of Grant Fitchley's "SQL Server Execution Plans", which is, funnily enough, about how to interpret and act upon an execution plan in SQL Server. "Every day, out in the various SQL Server forums, the same types of questions come up again and again: why is this query running slow? Why isn't my index getting used? And on and on. In order to arrive at the answer you have to ask the same return question in each case: have you looked at the execution plan? " – Grant Fritchey

Kimball University: Six Key Decisions for ETL Architectures

SSC posted a link to a good article titled "Kimball University: Six Key Decisions for ETL Architectures", by Bob Becker. Although written for directors/managers, I think developers will also find many of the points useful to understanding the context of an ETL project.

The ‘Subscription List’ SQL Problem

SQL ServerCentral now has a "Stack-Overflow"-type forum for SQL questions, ask.sqlservercentral.com. Phil Factor posted an interesting problem here that asks developers to post solutions to a report on a subscription list.

Celko on Stats

Click here for a really good article by Joe Celko on statistics. He discusses the difference between causation and correlation, and how to compute such things in SQL.

T-SQL Challenge #15

T-SQL Challenge #15 is out. It requires the use of PIVOT; an example can be found on MSDN at the bottom of this entry.

Database Space Capacity Planning

Good article on "Database Space Capacity Planning" by Chad Miller, that uses Powershell to query the space capacity of every SQL Server on your network. From his summary: "The need to monitor and forecast database and volume space is a critical task for database administrators. You can use the process described in this article to create a consolidated space forecasting report, which focuses on a "days remaining" metric. In addition, the use of PowerShell to collect data and load into a SQL table as demonstrated in this article, provides a solution you can easily adapt to many database administration problems."

Are Row Based DBs the Problem in BI?

"Using a traditional, row-based database to run critical reporting and analytics systems is like entering a delivery truck in a Grand Prix race. It's just not what it was designed to do." - Dan Lahl, director of analytics for Sybase. Interesting article by charlesb2k on the pros and cons of transactional, star-schema, and columnar databases for BI and analytics: "Are Row Based DBs the Problem in BI?"