Revisions Spec: Map Source to Target

The following element, contained within a revisions spec, contains embedded Transact-SQL. The deployment utility runs the Transact-SQL which executes a stored procedure for each line that adds comments to the BBDW database which 'map' the columns to the transactional database (Infinity database).

In order to optimize performance, the BBDW database does not use foreign keys. To compensate for the loss of mapping in absence of foreign keys, the relationship between attributes in BBDW database tables and Infinity database tables is maintained through comments.

The stored procedures, BBDW.USP_SCHEMA_TABLE_SETTABLECOMMENT and USP_SCHEMA_TABLE_SETCOLUMNCOMMENT, execute one of two system stored procedures. If there is a comment property, sys.sp_updateextendedproperty updates the comment with the parameters passed from the execute statement. If there is not a comment property, sp_addextendedproperty, adds the comment with the parameters passed from the execute statement. For more information about these system stored procedures, see Microsoft's MSDN articles at sp_addextendedproperty (Transact-SQL) and sp_updateextendedproperty (Transact-SQL).

The parameters you include to map the source to the target are an extended property, MS_Description. The column names from the OLTP and data warehouse databases, and the comment text. For more information about extended properties, see Microsoft's MSDN article at Viewing Extended Properties.

  <DBRevision ID="15">
    <ExecuteSql>
      <![CDATA[	
exec BBDW.USP_SCHEMA_TABLE_SETTABLECOMMENT 'DIM_CONSTITUENTADDRESS_EXT','The constituent address extension dimension contains information about constituent addresses.';
exec BBDW.USP_SCHEMA_TABLE_SETCOLUMNCOMMENT 'MS_Description', 'DIM_CONSTITUENTADDRESS_EXT','CONSTITUENTADDRESSEXTDIMID','Surrogate key for the constituent address extension dimension.';
exec BBDW.USP_SCHEMA_TABLE_SETCOLUMNCOMMENT 'MS_Description', 'DIM_CONSTITUENTADDRESS_EXT','ADDRESSSYSTEMID','dbo.[ADDRESS].[ID]';
exec BBDW.USP_SCHEMA_TABLE_SETCOLUMNCOMMENT 'MS_Description', 'DIM_CONSTITUENTADDRESS_EXT','CONSTITUENTDIMID','Reference key to the constituent dimension, derived from [dbo].[ADDRESS].[CONSTITUENTID]';
exec BBDW.USP_SCHEMA_TABLE_SETCOLUMNCOMMENT 'MS_Description', 'DIM_CONSTITUENTADDRESS_EXT','CONSTITUENTSYSTEMID','dbo.[ADDRESS].[CONSTITUENTID]';
exec BBDW.USP_SCHEMA_TABLE_SETCOLUMNCOMMENT 'MS_Description', 'DIM_CONSTITUENTADDRESS_EXT','ADDRESSTYPE','dbo.[ADDRESSTYPECODE].[DESCRIPTION]';
exec BBDW.USP_SCHEMA_TABLE_SETCOLUMNCOMMENT 'MS_Description', 'DIM_CONSTITUENTADDRESS_EXT','ADDRESSBLOCK','dbo.[ADDRESS].[ADDRESSBLOCK]';
exec BBDW.USP_SCHEMA_TABLE_SETCOLUMNCOMMENT 'MS_Description', 'DIM_CONSTITUENTADDRESS_EXT','CITY','dbo.[ADDRESS].[CITY]';
exec BBDW.USP_SCHEMA_TABLE_SETCOLUMNCOMMENT 'MS_Description', 'DIM_CONSTITUENTADDRESS_EXT','STATE','dbo.[STATE].[DESCRIPTION]';
exec BBDW.USP_SCHEMA_TABLE_SETCOLUMNCOMMENT 'MS_Description', 'DIM_CONSTITUENTADDRESS_EXT','STATEABBREVIATION','dbo.[STATE].[ABBREVIATION]';
exec BBDW.USP_SCHEMA_TABLE_SETCOLUMNCOMMENT 'MS_Description', 'DIM_CONSTITUENTADDRESS_EXT','COUNTRY','dbo.[COUNTRY].[DESCRIPTION]';
exec BBDW.USP_SCHEMA_TABLE_SETCOLUMNCOMMENT 'MS_Description', 'DIM_CONSTITUENTADDRESS_EXT','COUNTRYABBREVIATION','dbo.[COUNTRY].[ABBREVIATION]';
exec BBDW.USP_SCHEMA_TABLE_SETCOLUMNCOMMENT 'MS_Description', 'DIM_CONSTITUENTADDRESS_EXT','POSTCODE','dbo.[ADDRESS].[POSTCODE]';
exec BBDW.USP_SCHEMA_TABLE_SETCOLUMNCOMMENT 'MS_Description', 'DIM_CONSTITUENTADDRESS_EXT','CART','dbo.[ADDRESS].[CART]';
exec BBDW.USP_SCHEMA_TABLE_SETCOLUMNCOMMENT 'MS_Description', 'DIM_CONSTITUENTADDRESS_EXT','DPC','dbo.[ADDRESS].[DPC]';
exec BBDW.USP_SCHEMA_TABLE_SETCOLUMNCOMMENT 'MS_Description', 'DIM_CONSTITUENTADDRESS_EXT','LOT','dbo.[ADDRESS].[LOT]';
exec BBDW.USP_SCHEMA_TABLE_SETCOLUMNCOMMENT 'MS_Description', 'DIM_CONSTITUENTADDRESS_EXT','ISPRIMARY','dbo.[ADDRESS].[ISPRIMARY]';
exec BBDW.USP_SCHEMA_TABLE_SETCOLUMNCOMMENT 'MS_Description', 'DIM_CONSTITUENTADDRESS_EXT','ISINCLUDED','Flag indicating when data should be included in results.';
exec BBDW.USP_SCHEMA_TABLE_SETCOLUMNCOMMENT 'MS_Description', 'DIM_CONSTITUENTADDRESS_EXT','ETLCONTROLID','ID generated through the ETL process.';
exec BBDW.USP_SCHEMA_TABLE_SETCOLUMNCOMMENT 'MS_Description', 'DIM_CONSTITUENTADDRESS_EXT','SOURCEDIMID','Source system used.';
      ]]>
    </ExecuteSql>
  </DBRevision>

To view a comments for a column, open SQL Server Management Studio and establish a connection to the column's database. Then expand the nodes in the Object Explorer as follows: [Database name] > Tables > [Table name] > [Columns]> Column name. Right-click the column and click Properties. MS_Description comments appear on the Extended Properties page of the Column Properties screen.

For example, the value of MS_Description for PURPOSELEVEL1DIMID on the FACT_REVENUE is:

Reference key to the purpose level dimension, derived from dbo.[DESIGNATION].[DESIGNATIONLEVEL1ID]