Category Archives: Warehousing

Introduction to Analysis Services

By achandler | April 10, 2009

This post is intended to introduce Analysis Services 2005/2008 foundational concepts.
SQL Server Analysis Services is a component included in the Microsoft SQL Server product, and its use is fully covered under the same license (which applies to Integration Services and Reporting Services as well). Like the database engine, SSAS has a range of features stratified [...]

Also posted in Analysis Services, Microsoft, SQL Server | Leave a comment

Partitioned Fact Tables

By achandler | December 4, 2008

Once tables grow into the millions of records, they become candidates for partitioning. Table partitioning offers many benefits, particularly in warehouse environments. Since data is split into smaller units of storage, backups can target filegroups with a higher rate of change. Systems with multiple CPUs see improved query performance as partitioned data leads to greater [...]

Also posted in ETL | Leave a comment

Developing a Dimensional Model w/ conformed dimensions and facts

By ocash | September 23, 2008

Conformed Dimensions and Facts
Data marts are often developed to represent important systems within a company.  Over time additional data marts are added, and eventually there is a desire extract data across multiple marts.  Extracting data across data marts can be cumbersome and some times impossible if the data marts were not designed to share common [...]

Also posted in Data Modeling | 4 Comments

SCD via SQL Stored Procedure

By srichards | September 8, 2008

We are going to revisit the issue of dealing with Slowly Changing Dimensions in a data warehouse. We have seen a demonstration of using the SCD transformation that is available in SQL Server Integration Services (SSIS); however, this is not always the best option for processing an SCD. When the volume of rows you’re dealing [...]

Also posted in ETL | 6 Comments

The SCD Transformation in SSIS

By pross | August 14, 2008

In this post, we will demonstrate the Slowly Changing Dimension (SCD) transformation that is available for use in a Data Flow task within SQL Server Integration Services (SSIS).  We’ll walk through an example of how to use it, explaining the terms as we go. In addition, I’ll demonstrate some “extra effort” kind of modifications that can be [...]

Also posted in ETL | Leave a comment

A Warehousing Overview

By achandler | July 31, 2008

Data warehousing is a big subject. This overview is intended to cover some of the most representative issues on a high level: the nature of OLAP systems, star schemas, facts and dimensions, and differing perspectives (Inmon vs. Kimball) on warehouse design.
OLTP vs. OLAP
OLTP systems are the operational databases supporting applications. They are highly normalized, and [...]

Also posted in Data Modeling | Leave a comment