DSV Tables Extensions Specs

References tables for this spec are located at DSV Tables Spec Schema Table

Data Source View (DSV) Tables are part of a connection from a BBDW OLAP cube to views in a BBDW database. This approach, looking at views of a BBDW database, enables you to access the BBDW database as if it were built according to a pure star schema.

Measure Groups Extensions specs and Fact Extensions specs reference DSV Tables.

With DSV Tables Extensions specs, you can specify to create DSV tables to use within the cube. The spec defines the view name and Transact-SQL which defines the attributes of a view from which to select. The spec also indicates column information in separate nodes for each column. Once processed, a DSV Table based on the spec is added to the cube.

In the embedded Transact-SQL contained within <DSVSQL></DSVSQL>, there is a SELECT statement. SELECT is a Data Manipulation Language (DML) statement that retrieves rows from a database. For more information about SELECT statements, see Microsoft's MSDN article at SELECT (Transact-SQL).

The Transact-SQL code in the example that follows is designed to retrieve several columns from a Dimension Extension view in a BBDW database. The first item in the SELECT list is e.[EVENTDIMID]. EVENTDIMID, a column which holds IDs for the rows. e is alias for BBDW.[v_DIM_EVENT_EXT]. The alias is defined in the FROM clause of the SELECT statement:

from BBDW.[v_DIM_EVENT_EXT] e

The rows retrieved by the embedded Transact-SQL are then mapped to columns in the new DSV table. For example, EVENTDIMID is defined for the DSV table as follows:

<DSVTableColumn>
	<ColumnName>EVENTDIMID</ColumnName>
	<DataType>Int32</DataType>
	<Key>true</Key>	
</DSVTableColumn>

<ColumnName>EVENTDIMID</ColumnName> indicates the column name. Recall that this is the first column name in the embedded Transact-SQL statement. <DataType>Int32</DataType> indicates that the data type for the column is a form of integer. <Key>true</Key> indicates that the column is key. Similar mapping is defined for each column. For mappings of data warehouse data types to DSV specs data types, see Warehouse Data Type to DSV Spec Data Type

Once the spec is processed by the deployment utility, the cube contains a DSV table called DIM_EVENT_EXT. This DSV table corresponds to a view in a BBDW Database with the name, v_DIM_EVENT_EXT.

Views in a Blackbaud Data Warehouse database have the prefix v_.

A DSV Tables Extensions spec looks like this:

<DSVDeploymentInfo>
  <DSVTableName>DIM_EVENT_EXT</DSVTableName>
  <DSVSQL>
    select
      e.[EVENTDIMID],
      e.[EVENTSYSTEMID],
      e.[PARENTEVENTDIMID],
      e.[EVENTLOOKUPID],
      e.[EVENTNAME],
      e.[EVENTDESCRIPTION],
      e.[EVENTCAPACITY],
      e.[EVENTCATEGORY],
      e.[EVENTSTARTDATE],
      e.[EVENTENDDATE],
      e.[EVENTISACTIVE]
    from BBDW.[v_DIM_EVENT_EXT] e
  </DSVSQL>
  <DSVTableColumns>    
    <DSVTableColumn>
      <ColumnName>EVENTDIMID</ColumnName>
      <DataType>Int32</DataType>
      <Key>true</Key>
    </DSVTableColumn>
    <DSVTableColumn>
      <ColumnName>EVENTSYSTEMID</ColumnName>
      <DataType>Guid</DataType>
    </DSVTableColumn>
    <DSVTableColumn>
      <ColumnName>PARENTEVENTDIMID</ColumnName>
      <DataType>Int32</DataType>
    </DSVTableColumn>
    <DSVTableColumn>
      <ColumnName>EVENTLOOKUPID</ColumnName>
      <DataType>String</DataType>
      <DataSize>100</DataSize>
    </DSVTableColumn>
    <DSVTableColumn>
      <ColumnName>EVENTNAME</ColumnName>
      <DataType>String</DataType>
      <DataSize>100</DataSize>
    </DSVTableColumn>
    <DSVTableColumn>
      <ColumnName>EVENTDESCRIPTION</ColumnName>
      <DataType>String</DataType>
      <DataSize>255</DataSize>
    </DSVTableColumn>
    <DSVTableColumn>
      <ColumnName>EVENTCAPACITY</ColumnName>
      <DataType>Int32</DataType>
    </DSVTableColumn>
    <DSVTableColumn>
      <ColumnName>EVENTCATEGORY</ColumnName>
      <DataType>String</DataType>
      <DataSize>255</DataSize>
    </DSVTableColumn>
    <DSVTableColumn>
      <ColumnName>EVENTSTARTDATE</ColumnName>
      <DataType>DateTime</DataType>
    </DSVTableColumn>
    <DSVTableColumn>
      <ColumnName>EVENTENDDATE</ColumnName>
      <DataType>DateTime</DataType>
    </DSVTableColumn>
    <DSVTableColumn>
      <ColumnName>EVENTISACTIVE</ColumnName>
      <DataType>Boolean</DataType>
    </DSVTableColumn>
  </DSVTableColumns>
</DSVDeploymentInfo>