Reporting, Decision Support, and Data Warehousing Applications
A data warehouse is a database that contains enterprise data representing the business history of an organization. It is used to consolidate information stored in various business systems and heterogeneous platforms. Data in a data warehouse is often structured and optimized for decision support.
Replication becomes an integral part of the data warehousing and decision support environment when it is used during data staging and as a data warehousing management and deployment tool. You can use replication to update data marts and data warehouses, distribute data to read-only databases used for queries and analyses, distribute data to an online analytical processing (OLAP) database, and consolidate data so it can be transformed and moved into the data warehousing environment.
Replication can also be used to partition data that has been consolidated in a data warehousing environment and distribute the data to data marts or databases inside or outside of the data warehousing environment.
Although Microsoft® SQL Server™ does not replicate SQL Server 2000 Analysis Services objects (for example, dimensions or cubes), it can help you distribute data from OLTP databases to data staging databases or databases that will be used for reporting, decision support or analysis purposes, and if needed, you can use the capabilities of Data Transformation Services (DTS) during replication.
Providing consistent data to data warehousing and decision support systems is critical to the success of those operations. Within a reporting, decision support query, or OLAP environment, different user groups have different requirements for the data, and replication provides several options for distributing, updating, and synchronizing data.
Because data used in decision support is predominantly read-only (used for queries and analysis), snapshot replication or transactional replication are often the types of replication used. With snapshot replication, data and database objects are copied and distributed exactly as they appear at a specific moment in time. If data transformations are needed for data that is replicated into a data mart or data warehouse, you can use Data Transformation Services (DTS) as part of the replication process when using snapshot replication or transactional replication.