Relational data Warehouse partitioning strategy in SQL Server (1)

Partitioning a relational data warehouse

The following sections will briefly explain the concept of a relational data warehouse, the benefits of partitioning a relational data warehouse, and the benefits of migrating to Microsoft®sql server™2005 partitions.

About relational data warehouses

Relational data warehouses provide a wide range of data sources and an architecture for building business Intelligence (BI) solutions. In addition, relational data warehouses can be used for report applications as well as for complex and specialized SQL queries.

A typical relational data warehouse consists of a dimension table and a fact table. A dimension table is usually smaller than the fact table and provides detailed information about the attributes that explain the facts. An example of a dimension is goods, stores, and time. The fact table provides a description of the business record, such as information about the sale of goods in all stores. The fact table is constantly updated with data that has recently been collected.

The implementation of a successful relational data warehouse solution involves careful and long-term planning. The following is a list of the elements to consider when building a relational data warehouse:

Amount of data

Data Import window

Index Maintenance window

Workload characteristics

Data aging Strategy

Archiving and backup strategies

Hardware Features

A detailed discussion of the above elements will be available in the remainder of this document.

A relational data warehouse can be implemented using partitioning methods or a (huge) fact table approach. Design choices that use partitioning or no partitioning are primarily dependent on the elements listed earlier. Relational data warehouses can benefit from data partitions. The benefits of partitioning for relational data warehouses are highlighted in the lower part.

Benefits of Partitioning

When a database in an organization expands and contains a large amount of data, it is critical to maintain its high availability while adapting to the need for small database maintenance windows. These requirements make partitioning a tailored technology for a very large database. The key issue highlighted by partitioning technology is that it is easier to manage important tasks such as data import, aging, and archiving by breaking down very large tables into relatively small partitions. Microsoft SQL Server provides zoning technology through partitioned views in SQL Server 7.0/2000 and support for partitioned tables added in SQL Server 2005.