Sql server® 2012 t-sql fundamentals

 

CHAPTER 1

 

Background to T-SQL Querying and Programming

 

 11

could be implemented as a single ProductDim table instead of three normalized tables: Products, 

ProductSubCategories, and ProductCategories. If you normalize a dimension table, which results 

in multiple tables representing that dimension, you get what’s known as a snowflake dimension. A 

schema that contains snowflake dimensions is known as a snowflake schema (as opposed to a star 

schema).

The fact table holds the facts and measures such as quantity and value for each relevant combina-

tion of dimension keys. For example, for each relevant combination of customer, product, employee, 

and day, the fact table would have a row containing the quantity and value. Note that data in a data 

warehouse is typically preaggregated to a certain level of granularity (such as a day), unlike data in an 

OLTP environment, which is usually recorded at the transaction level. 

Historically, early versions of SQL Server mainly targeted OLTP environments, but eventually SQL 

Server also started targeting data warehouse systems and data analysis needs. You can implement a 

data warehouse as a SQL Server database and manage and query it with T-SQL.

The process that pulls data from source systems (OLTP and others), manipulates it, and loads it into 

the data warehouse is called extract, transform, and load, or ETL. SQL Server provides a tool called 

Microsoft SQL Server Integration Services (SSIS) to handle ETL needs.

Often the ETL process will involve the use of a data staging area (DSA) between the OLTP and the 

DW. The DSA usually resides in a relational database such as a SQL Server database and is used as the 

data cleansing area. The DSA is not open to end users.