Revisions Spec Exercise Steps: Create Staging 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 "25" to number the ID for that revision.

    <DBRevision ID="25">
    </DBRevision>
  4. Within <DBRevision ID="25">, 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 staging table for a fact table.

    create table [BBDW].[FACT_INTERACTIONRESPONSE_EXT_STAGE]
    ) on [BBRPT_STAGEGROUP]

    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_STAGEGROUP] filegroup. For more information about filegroups, see Files and Filegroups Architecture.

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

    the absence of the INTERACTIONRESPONSEFACTID identity column added to the warehouse table.

    1. INTERACTIONRESPONSESYSTEMID a unique identifier that accepts NULL

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

      the absence of the INTERACTIONFACTID column added to the warehouse table. But the presence of INTERACTIONSYSTEMID, a unique identifier.

    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).

      the absence of the INTERACTIONFACTID column added to the warehouse table. But the presence of INTERACTIONSYSTEMID, a unique identifier.

    3. INTERACTIONSYSTEMID a unique identifier that accepts NULL

    4. RESPONSEDIMID an integer that accepts NULL

    5. INTERACTIONRESPONSEDATEDIMID an integer that accepts NULL

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

      INTERACTIONRESPONSEDATE a date/time that accepts NULL

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

    6. ISINCLUDED a bit that accepts NULL

    7. ETLCONTROLID an integer that accepts NULL

    8. SOURCEDIMID a tiny integer that accepts NULL

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

  7. Save your revisions file.

    The revision should look like this:

      <DBRevision ID="25">
        <ExecuteSql>
          <![CDATA[
    create table [BBDW].[FACT_INTERACTIONRESPONSE_EXT_STAGE]
    (
      [INTERACTIONRESPONSESYSTEMID] [uniqueidentifier] null,
      [INTERACTIONSYSTEMID] [uniqueidentifier] null,
      [RESPONSEDIMID] [int] null,
      [INTERACTIONRESPONSEDATEDIMID] [int] null,
      [INTERACTIONRESPONSEDATE] [datetime] null,
      [ISINCLUDED] [bit] null,
      [ETLCONTROLID] [int] null,
      [SOURCEDIMID] [tinyint] null
    ) on [BBRPT_STAGEGROUP]
        ]]>
        </ExecuteSql>
      </DBRevision>