Revisions Spec: Create Table View

The following element, contained within a revisions spec, contains embedded Transact-SQL. The deployment utility runs the Transact-SQL to create a table view to extend constituent address. When you add table views to a BBDW database, use a revision such as the one in this example. Using revisions to extend the database ensures that the extension is repeatable and easier to upgrade. Otherwise, you would have to manually execute a query to create the table view in the BBDW database after every deployment.

Table views allow you act on Blackbaud Data Warehouse databases as if built on pure star schema.

To adapt the embedded Transact-SQL in this example for the table you want to add, adjust the name specified in the CREATE VIEW statement, the attribute information, and the table referenced in the FROM clause. For more information about the CREATE VIEW statement, see Microsoft's MSDN article at CREATE VIEW (Transact-SQL).

Note: 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.

Note: To maintain consistent naming for views, use the v_ prefix.

  <DBRevision ID="1075">
    <ExecuteSql>
      <![CDATA[
create view [BBDW].[v_DIM_CONSTITUENTADDRESS_EXT] as
  select
    [CONSTITUENTADDRESSEXTDIMID], 
    [CITY], 
    [STATE], 
    [STATEABBREVIATION], 
    [COUNTRY],
    [COUNTRYABBREVIATION],
    [POSTCODE],  
    [CART],
    [DPC],
    [LOT],
    [ISPRIMARY],   
    [ISINCLUDED],
    [ETLCONTROLID],
    [SOURCEDIMID]
  from BBDW.[DIM_CONSTITUENTADDRESS_EXT]
      ]]>
    </ExecuteSql>
  </DBRevision>