Revisions Spec Exercise Steps: Create Table

1. Open the revisions file to edit.

2. Add a section to create a table. For more information, see the example at Revisions Spec: Create Table

3. Use "10" to number the ID for that revision.

   <DBRevision ID="10">
   </DBRevision>

4. Within <DBRevision ID="10">, add element to embed Transact-SQL code.

       <ExecuteSql>
         <![CDATA[
       ]]>
       </ExecuteSql>

   For more information about CDATA sections, see Microsoft's MSDN article at CDATA Sections [XML Standards].

5. Within that, add Transact-SQL code to create a fact table.

   create table [BBDW].[FACT_INTERACTIONRESPONSE_EXT]
   ) on [BBRPT_FACTGROUP]

   To maintain consistent naming that avoids conflicts with out-of-the-box tables, apply the _EXT suffix to names for extension tables. If the extension is for a staging table, also apply the _STAGE suffix. Always apply _EXT first. Appropriate suffixes are _EXT and _EXT_STAGE.

   [BBDW] is the database schema.

   For more information about the CREATE TABLE statement, see Microsoft's MSDN article at CREATE TABLE (Transact-SQL).

   In this case, ON indicates that the table is to be a part of the [BBRPT_FACTGROUP] filegroup. For more information about filegroups, see Files and Filegroups Architecture.

6. Include these columns which mirror the FACT_INTERACTIONRESPONSE table in a Blackbaud Data Warehouse database exactly:

   1. INTERACTIONRESPONSEFACTID an integer column that is an identity column that has a constraint to not accept NULLvalues.

      For more information about the integer data type, see Microsoft's MSDN article at int, bigint, smallint, and tinyint (Transact-SQL). For more information about identity columns, see IDENTITY (Property) (Transact-SQL). For more information about null values, see Null Values.

   2. INTERACTIONRESPONSESYSTEMID a unique identifier that accepts NULL

      For more information about the uniqueidentifier data type, see Microsoft's MSDN article at uniqueidentifier (Transact-SQL).

   3. INTERACTIONFACTID an integer that accepts NULL

   4. RESPONSEDIMID an integer that accepts NULL

   5. INTERACTIONRESPONSEDATEDIMID an integer that accepts NULL

   6. INTERACTIONRESPONSEDATE a date/time that accepts NULL

      For more information about the datetime data type, see Microsoft's MSDN article at datetime (Transact-SQL).

   7. ISINCLUDED a bit that accepts NULL

      For more information about the bit data type, see Microsoft's MSDN article at int, bigint, smallint, and tinyint (Transact-SQL).

   8. ETLCONTROLID an integer that accepts NULL

   1. SOURCEDIMID a tiny integer that accepts NULL

      For more information about the bit data type, see Microsoft's MSDN article at int, bigint, smallint, and tinyint (Transact-SQL).

        [INTERACTIONRESPONSEFACTID] [int] identity(1, 1) not null,
        [INTERACTIONRESPONSESYSTEMID] [uniqueidentifier] null,
        [INTERACTIONFACTID] [int] null,
        [RESPONSEDIMID] [int] null,
        [INTERACTIONRESPONSEDATEDIMID] [int] null,
        [INTERACTIONRESPONSEDATE] [datetime] null,
        [ISINCLUDED] [bit] null,
        [ETLCONTROLID] [int] null,
        [SOURCEDIMID] [tinyint] null,

7. Within the CREATE TABLE statement, add a primary key constraint.

     constraint [PK_FACT_INTERACTIONRESPONSE_EXT] primary key clustered 
     ( 
       [INTERACTIONRESPONSEFACTID] asc 
     )

   To avoid conflicts with out-of-the-box members of the [BBRPT_FACTIDXGROUP] filegroup, use the _EXT suffix for all primary key constraints.

   For more information about constraints, see Microsoft's MSDN article at Constraints. For more information about primary key constraints in particular, see PRIMARY KEY Constraints.

   asc indicates the column is to be sorted in ascending order.

8. Save your revisions file.

   The revision should look like this:

     </DBRevision>
   
     <DBRevision ID="10">
       <ExecuteSql>
         <![CDATA[
   create table [BBDW].[FACT_INTERACTIONRESPONSE_EXT]
   (
     [INTERACTIONRESPONSEFACTID] [int] identity(1, 1) not null,
     [INTERACTIONRESPONSESYSTEMID] [uniqueidentifier] null,
     [INTERACTIONFACTID] [int] null,
     [RESPONSEDIMID] [int] null,
     [INTERACTIONRESPONSEDATEDIMID] [int] null,
     [INTERACTIONRESPONSEDATE] [datetime] null,
     [ISINCLUDED] [bit] null,
     [ETLCONTROLID] [int] null,
     [SOURCEDIMID] [tinyint] null,
   
     constraint [PK_FACT_INTERACTIONRESPONSE_EXT] primary key clustered 
     ( 
       [INTERACTIONRESPONSEFACTID] asc 
     ) 
   ) on [BBRPT_FACTGROUP]
       ] ]>
       </ExecuteSql>
     </DBRevision>