Parts of a Revisions Spec

The header identifies the spec as an XML document: <?xml version="1.0" ?>

The first child's attribute identifies the bb_appfx_dbrevisions namespace: <DBRevisions xmlns="bb_appfx_dbrevisions">.

<DBRevison> contains an attribute that identifies order within the spec: <DBRevision ID="1">

Note: The ID attribute for DBRevision only indicates order within the spec. It does not indicate the placement of the spec in the revision order of all specs. The revision order of all spec is determined by the filename of the spec.

A <Comment> identifies the revisions spec as for and extension to the database:

<Comment>Extended Database Schema</Comment>

Subsequent <DBRevision> elements contain embedded Transact-SQL nested in an <ExecuteSQL> element. Common SQL actions include resetting ETL, creating tables, mapping columns from the BBDW database to the OLAP cube, dropping and adding indexes, and altering tables.

Examples of some common database revisions are included in this document. A simple revision to create a stored procedure is the first example. The stored procedure, RESETETL_EXT is designed to hold statements to truncate tables and drop indexes. Truncating tables and dropping indexes as a reset mechanism clears to the data warehouse for an update from the OLTP database. For more information, see Revisions Spec: Reset.

Another example revision, to modify the RESETETL_EXT stored procedure, populates the stored procedure with the necessary statements to truncate tables and drop indexes. This revision should include statements for all table truncations and index drops, not just those for the most recently added. For more information, see Revisions Spec: Truncate Tables and Drop Indexes.

Example revisions to create the stored procedures called by RESETETL_EXT are also included. A separate stored procedure to drop indexes on each table is necessary. Creating these stored procedures with revisions makes the process repeatable. For more information, see Revisions Spec: Drop and Create Indexes.

Because BBDW database tables do not contain foreign keys, an alternate way of mapping OLTP tables to data warehouse tables is necessary. Commenting is used to maintain mapping information. An example revision to establish comments for mapping is included. For more information, see Revisions Spec: Map Source to Target.

Finally, example revisions to create tables and views of those tables in the warehouse are included. For more information, see Revisions Spec: Create Table View