Surrounding the ETL Requirements

November 13, 2004 / Issue TOC

Surrounding the ETL Requirements

Before designing an ETL system, you must first understand all of your business needs.

By Ralph Kimball , Margy Ross

Ideally, the design of your extract, transform, and load (ETL) system begins with one of the toughest challenges: surrounding the requirements. By this we mean gathering in one place all the known requirements, realities, and constraints affecting the ETL system. The list of requirements is pretty overwhelming, but it's essential to lay them on the table before launching a data warehouse project.

The requirements are mostly things you must live with and adapt your system to. Within the framework of your requirements, you'll have many places where you can make your own decisions, exercise your judgment, and leverage your creativity, but the requirements are just what they're named. They are required.

Using Parent Package Variables in Package Configurations

Package configurations are now the prescribed way of being able to set values within your package from an outside source. One of the options for the source is Parent Package Variable. The name is perhaps a little misleading so this article is meant to guide you through this slight confusion and into using them. It also helps to explain a key concept in SQL Server Integration Services

usp_import_from_mysql

Imports data from MySQL Server; works best with mysql driver 3.51.06! You'll have to create a dsn and then a linked server to that dsn. The script checks if the table exists in the mysql catalog (defined in the dsn properties). Too bad it needs administrative privileges to run (dbcc statement) - my advice: don't […]

SQL Server 2000 Reporting Services Activation Process

SQL Server 2000 Reporting Services uses cryptography to protect account data. Learn how SQL Server 2000 Reporting Services generates and stores the keys required to encrypt data.

Using a Subquery in a T-SQL Statement

Sometimes the criteria for determining which set of records will be affected by a SELECT, UPDATE, DELETE and/or INSERT statement cannot be obtained by hard coding the selection criteria. Occasionally there is a need to use the results of a SELECT statement to help determine which records are returned or are affected by a T-SQL statement. When a SELECT statement is used inside another statement, the inside SELECT statement is known as a subquery. Subqueries can help to dynamically control the records affected by an UPDATE, DELETE or INSERT statement, as well as to help determine the records that will be returned by a SELECT statement. This article will show different examples of how to use a subquery to help clarify the records affected or selected by a T-SQL statement.

Full Text Search on SQL 2000 Part 3

In the previous month's articles, Full Text Searching was introduced as a way to query strings with more refinement than the usual TSQL "like" or equal operator statements. With Full Text Searching, a new file system structure is created, storing key words from selected fields into Catalogs. In addition to storing typical character fields from databases, Microsoft Office documents that have been saved as binaries can also be entered into the Catalogs. In this month's edition, we will begin with the maintenance issues required to keep these Catalogs current. Once created, these catalogs can be interrogated for key words that are near each other's proximity, the singular and plural versions of a word, or the noun and verb variations of a word. In addition, search result rankings, or weights, that usually accompany internet search engine returns can also be requested.

Rebuilding SQL Server Cluster Nodes

Active/Passive SQL Server 2000 clustering gives more reliability and fault tolerance to Production SQL Server environments. When a failure occurs, all of the resources fail over from the active node to the passive node and make the passive node active. This article explains how to rebuild the node that failed and attach it back to the cluster.

Full Text Search on SQL 2000 Part 2

In last month's article, Full Text Searching was introduced as a way to query strings with more refinement than the usual TSQL "like" or equal operator statements. With Full Text Searching, a new file system structure is created, storing key words from selected fields into Catalogs. In addition to storing typical character fields from databases, Microsoft Office documents that have been saved as binaries can also be entered into the Catalogs. Once created, these catalogs can be interrogated for key words that are near each other's proximity, the singular and plural versions of a word, or the noun and verb variations of a word can all be searched for. In addition, search result rankings, or weights, that usually accompany internet search engine returns can also be requested.

SQL Server 2005 T-SQL Enhancements

SQL Server 2005 or "Yukon" is going to be a major SQL Server update containing updates to nearly every facet of the program, including T-SQL. In this article I am going to explore some of the new T-SQL features, commands, and capabilities in SQL Server 2005. Because covering everything new in T-SQL would require an entire chapter in a book, I am going to cover some of the more useful and mainstream enhancements.

Full Text Search on SQL 2000 Part 1

Full Text Searching is a free, optional component of MS SQL 2000. When installed, it offers a vast array of additional string querying abilities. Full Text Searching allows for string comparisons similar to internet search engines, returning both results and a matching score or weight. With regular TSQL, string matching is usually limited to an exact match, or a wildcard match with the keyword "LIKE." Full Text Searching exceeds this by searching for phrases, groups of words, words near one another, or different tenses of words, such as run, running, and ran. In addition, if Microsoft Office Word or Excel documents are saved in the database, their contents can be searched like a typical varchar field. Full Text Searching is accomplished by installing a new service (Microsoft Search), and using key words in TSQL designed specifically for text searching. This article will demonstrate installing, configuring and using the Full Text Search engine.