Fill a specified partition of a table with a specific query, working on staging table   Read more...

Rebuilds online a table partition with all indexes   Read more...

Why neglecting slowly changing dimensions, failing to capture metadata and overlooking scope creep can be the undoing of a dimensional data warehousing initiative.  Read more...

Read more...

Architecture and data warehousing are not static. From the first notion of a data warehouse to a full-blown analytical processing architecture that includes data marts, ETL, near line storage, exploration warehouses, and other constructs, data warehousing and its associated architecture continue to evolve. In 2008, the book on the latest evolution of data warehousing appeared – DW 2.0: The Architecture for the Next Generation of Data Warehousing (Morgan Kaufman). In that book the general architecture for data warehousing in its highest evolved form appeared.  Read more...

When you need to insert a large set of data using T-SQL, what can you do? New author Melinda Sansone brings us a best practice when using T-SQL to load data for data marts, or warehouses.  Read more...

Recently, I was at a conference, and I heard the following discussion about what a data warehouse was. One person suggested that a data warehouse was really all the old legacy systems connected by software that could access the data. By calling such a contraption a data warehouse, the organization could avoid having to do the hard and complex work of integration. There are so many problems with this federated approach to a data warehouse that they are almost not worth repeating here. But (once again!) here goes.   Read more...

Best-practice advice on software vs. coding, where to integrate, how to capture changed data, when to stage data, where to correct data and what latency levels to shoot for.  Read more...

Given that companies spend on average between 7 and 12 percent of their annual budgets on energy – a focus on reducing energy consumption can have significant bottom-line impact  Read more...

This article outlines a more objective and analytical approach to the ETL and data flow architecture selection based on a set of variables with an objective to enhance the reliability of the overall data warehousing solution.  Read more...

Avoiding getting snared in these traps avoids having to spend a lot of money later to fix problems, Gartner says. In addition, EA benefits can be realized faster and the risk of program failure is reduced.  Read more...

When working with a dimensionally modeled data warehouse it is common for a large number of your queries to follow...  Read more...

In this white paper we discuss two of the new features in SQL Server 2008, Star Join and Few-Outer-Row optimizations. We test the performance of SQL Server 2008 on a set of complex data warehouse queries designed to highlight the effect of these two features and observed a significant performance gain over SQL Server 2005 (without these two features).  Read more...

This paper defines a reference configuration model (known as SQL Server Fast Track Data Warehouse) and a CPU core-balanced approach to implementing a symmetric multiprocessor (SMP)-based SQL Server data warehouse with proven performance and scalability expectations for sequential I/O data workloads.  Read more...

The employee dimension presents one of the trickier challenges in data warehouse modeling. These five approaches ease the complication of designing and maintaining a 'Reports To' hierarchy for ever-changing reporting relationships and organizational structures.  Read more...

This paper provides an overview and guide to SQL Server® Fast Track Data Warehouse, a new set of reference architectures created for scale-up (SMP) SQL Server based data warehouse solutions. It includes a summary of the resources available in the reference configuration, the distinguishing features of the approach, and the steps necessary to take full advantage of the new architectures.  Read more...

When an enterprise first encounters master data management, it often doesn’t have a clear understanding of how MDM will affect the architecture of its business transaction systems or business intelligence systems. This article describes master data patterns in legacy system architectures, a general MDM architecture and some ways the new MDM layer affects the master data patterns in the legacy layers.  Read more...

This document describes common data warehouse implementation strategies and proposes an effective hub-and-spoke architecture using a massively parallel processing system with multiple instances of SQL Server databases.   Read more...

Scalable, cost-effective EDW implementation is an elusive goal for many large organizations. Three common approaches include centralized EDW the “monolithic” approach, decentralized collections of data marts, and attempts at hub-and-spoke architectures that combine the previous two. With the acquisition of DATAllegro and the pending release of Project codename “Madison,” Microsoft is poised to deliver a unique and compelling Massively Parallel Processing (MPP) data warehouse solution that combines the best features of both EDW and decentralized data marts.   Read more...

Stored Procedure to build a MERGE statement in SQL 2008 using dynamic SQL.   Read more...