Find Data in the Data Warehouse Database
Blackbaud Data Warehouse Tables and Major Giving Stages
A Blackbaud Data Warehouse database has these tables:
DIM_INTERACTION: The Interaction dimension contains information about constituent interactions.
FACT_INTERACTION: The Interaction fact relates information to constituent interactions.
DIM_PROSPECTPLAN: Contains information about prospect plans.
DIM_PROSPECTPLANSTATUS: Contains information about prospect plant status codes.
DIM_INTERACTION includes these columns and mappings among others:
INTERACTIONSTATUSCODE: dbo.[INTERACTION].[STATUSCODE]
INTERACTIONSTATUS: dbo.[INTERACTION].[STATUS]
ISINTERACTIONCOMPLETED: dbo.[INTERACTION].[COMPLETED]
FACT_INTERACTION includes these columns and mappings among others:
CONSTITUENTDIMID: Reference key to the constituent dimension, derived from dbo.[INTERACTION].[CONSTITUENTID]
CONSTITUENTSYSTEMID: dbo.[INTERACTION].[CONSTITUENTID]
INTERACTIONDIMID: Reference key to the interaction dimension, derived from dbo.[INTERACTION].[INTERACTIONTYPECODEID], dbo.[INTERACTION].[INTERACTIONSUBCATEGORYID], dbo.[INTERACTION].[STATUSCODE], dbo.[INTERACTION].[ISALLDAYEVENT], dbo.[INTERACTION].[ISINTERACTION], dbo.[INTERACTION].[COMPLETED], and dbo.[INTERACTION].[ISCONTACTREPORT]
PROSPECTPLANDIMID: Reference key to the prospect plan dimension, derived from dbo.[INTERACTION].[PROSPECTPLANID]
PROSPECTPLANSTATUSDIMID: Reference key to the prospect plan status dimension, derived from dbo.[INTERACTION].[PROSPECTPLANSTATUSCODEID]
DIM_PROSPECTPLAN includes these columns and mappings among others:
CONSTITUENTSYSTEMID: dbo.[PROSPECTPLAN].[PROSPECTID]
CONSTITUENTDIMID: Reference key to the constituent dimension, derived from dbo.[PROSPECTPLAN].[PROSPECTID]
PROSPECTSTATUS: dbo.[PROSPECTPLANSTATUSCODE].[DESCRIPTION]
PROSPECTPLANSTATUS: dbo.[PROSPECTPLANSTATUSCODE].[DESCRIPTION]
DIM_PROSPECTPLANSTATUS includes these columns and mappings among others:
PROSPECTPLANSTATUSSYSTEMID: dbo.[PROSPECTPLANSTATUSCODE].[ID]
PROSPECTPLANSTATUS: dbo.[PROSPECTPLANSTATUSCODE].[DESCRIPTION]
Later in the document, the core of our first set of data warehouse queries will use the DIM_PROSPECTPLAN, DIM_INTERACTION, FACT_INTERACTION, and DIM_PROSPECTPLANSTATUS tables as shown in the following database diagram created in SQL Server Management Studio. Notice there are no foreign key relationships. This is a characteristic of the Blackbaud Data Warehouse data warehouse database. However, if you look at the primary keys for each of the dimension tables, they correspond to columns in the fact table. The relationships exist. But the database is oblivious. Removing foreign keys creates a performance gain for the warehouse.
Blackbaud Data Warehouse Views and Major Giving Stages
Warning: Prospect Plan Stage (PROSPECTPLANSTATUSCODE) information is not a part of these views.
A Blackbaud Data Warehouse database has these views:
v_DIM_INTERACTION: The interaction dimension contains information about interactions.
v_FACT_INTERACTION: The interaction fact table contains information about constituent interactions.
v_DIM_INTERACTION includes these columns and mappings among others:
INTERACTIONSTATUSCODE: BBDW.[DIM_INTERACTION].[INTERACTIONSTATUSCODE]
INTERACTIONSTATUS: BBDW.[DIM_INTERACTION].[INTERACTIONSTATUS]
ISINTERACTIONCOMPLETED: BBDW.[DIM_INTERACTION].[ISINTERACTIONCOMPLETED]
v_FACT_INTERACTION includes these columns and mappings among others:
CONSTITUENTDIMID: BBDW.[FACT_INTERACTION].[CONSTITUENTDIMID]
CONSTITUENTSYSTEMID: BBDW.[FACT_INTERACTION].[CONSTITUENTSYSTEMID]
INTERACTIONDIMID: BBDW.[FACT_INTERACTION].[INTERACTIONDIMID]