Relational Data Warehouse | MSBI Tutorial pdf

Relational Data Warehouse

SQL Server 2005 blurs the lines between relational and multidimensional databases. You can store data in the relational database, in the multidimensional database, or use the new Proactive Cache feature to get the best of both worlds.

Partitioned Tables and Indexes
In SQL Server 2005, table and index partitioning significantly enhance query performance by breaking down large databases into smaller, more manageable pieces. New functionality enables you to partition tables across file groups in a database. Horizontal partitioning enables you to divide a table into smaller groupings so that groups of rows are mapped into individual partitions. Operations performed on the data, such as
queries, are executed as if the entire table or index is a single entity. In addition, partitions can be switched into or out of an existing partition scheme very quickly, eliminating the need to create and index new tables.

Online Index Operations
Online Index Operations enhance the indexing capabilities of earlier releases of SQL Server. With Online Index Operations, you can create, rebuild, or drop an index online without interfering with access to tables or other existing indexes. Other capabilities include the following:
=> Enables concurrent modifications (updates, deletes, and inserts) to any associated indexes during index data definition language (DDL) execution.
=> Provides parallel processing.
=> Performs a restore operation while an instance of SQL Server 2005 is running.
Only the data that is being restored is unavailable. The rest of the database remains online and available.

Transact-SQL Improvements
SQL Server 2005 provides many new language capabilities for developing scalable database applications. The T-SQL enhancements in SQL Server 2005 increase your expressive powers in query writing so you can improve code performance and extend your error management capabilities. New data types can be used to hold extended metadata and other descriptive information in a data warehouse. New analytic functions can be used during data staging to develop valuable data attributes. They also provide basic analytic capabilities within T-SQL, which is useful for enabling user queries in the relational database rather than exclusively through SSAS.

Database Snapshots
SQL Server 2005 introduces database snapshots, a quick and easy way to create readonly views of a database. Database snapshots eliminate the need to create a whole copy of the database and its accompanying storage. You get a stable view without the time or storage overhead required when creating a complete copy of the database. As changes are made to the database, the snapshot receives its own copy of the original page from the database. The snapshot may be used to quickly recover from an accidental change to a database by reapplying the original pages from the snapshot to the primary database.

Data Mirroring
By creating a database snapshot on a database mirror, you effectively create a standby server on a secondary SQL Server system, giving you high system availability. You also create a reporting server. The secondary database is always updated with the current transaction that’s being processed on the primary database server. In the event that your primary database fails, database mirroring enables the second standby database to be almost instantly available. All work is automatically shifted over to the backup server.

Microsoft Business Intelligence in Action
Blue Yonder Airlines stores all reservations, past and present, in its data warehouse. To stay up to date with customers’ needs and to maintain a high level of customer service, the airline cannot afford to have any system failure. One of the main reasons that Claus invested in SQL Server 2005 was because of its high availability. Features such as failover clustering and data mirroring create quick back-up copies should his primary database ever encounter downtime. As a result, the database is always available, helping Blue
Yonder provide better service.

Snapshot Isolation
With SQL Server 2005 snapshot isolation, all the data you read during the course of the transaction is as it was at the start of the transaction, even if the row is locked for updates being made concurrently by other users. For many applications, the very shortterm inaccuracy between access and update is acceptable for queries that are designed to show orders of magnitude rather than to report individual row values.

Support for 64-bit
Optimized for the Intel Itanium processor x64 environments, SQL Server 2005 (64-bit) uses advanced memory addressing capabilities for essential resources such as buffer pools and caches. This functionality reduces the need to perform multiple input and output (I/O) operations to bring data in and out of memory from disk. Greater processing capacity without the penalties of I/O latency opens the door to new levels of application scalability.

SQL Server Management Studio
SQL Server Management Studio integrates the management of Enterprise Manager, Query Analyzer, and Analysis Manager from previous releases of SQL Server into a single environment. Combining easy-to-use graphical tools with rich scripting capabilities, SQL Server Management Studio provides a management infrastructure that can be easily programmed by using SQL Management Objects. BI practitioners will benefit from the extension of the server abilities you expect from the relational engine scalability, reliability, availability, programmability, and so on to the full set of BI platform components.