Introduction and FAQ

Foreign Keys

Question: I have seen diagrams of the Blackbaud Data Warehouse database that contain foreign keys. But when I look at the database, there are no foreign keys. Why is that?

Answer: To support optimization, there are no foreign keys. But to express the conceptual relationship between tables, a diagram may indicate a foreign key or use a directional arrow. However this documentation does not do that. For more information, see BBDW Database andView Information about Fact and Dimension Relationships in a BBDW Database.

Fact tables

Question: What is the purpose of a given fact table?

Answer: You can look at the Extended Properties of a given table in a Blackbaud Data Warehouse database to see an MS_Description that describes the table. Also, the warehouse installation ships with schema documentation. The file location for the schema CHM file, BBDW_DM_Schema.chm, is Install\SDK\Documentation. For more information about how to view Extended Properties from SQL Server Management Studio, see View Information about Fact and Dimension Relationships in a BBDW Database.

No measures

Question: Why would a fact table contain no measures?

Answer: Some fact tables, referred to by Ralph Kimball as factless fact tables, contain no measures. These factless fact tables facilitate many-to-many relationships. For example, there are many Constituencies with many Constituents and many Constituents with many Constituencies. Microsoft's Analysis Services model uses intermediate tables (Measure Groups or Facts) to accomplish this. So factless fact tables ensure that there is a record of every constituent and constituency that exists. For an explanation of many-to-many relationships with respect to extending the BBDW OLAP cube, see BBDW OLAP Extensions: Advanced Dimensional Usage.

Dimensions joined to dimensions

Question: Why would a dimension join to another dimension?

Answer: This structure is characteristic of a Snowflake schema. For more information about Snowflakes, see Snowflakes.

Single column surrogate keys

Question: Are you using single column surrogate keys in your table?

Answer: Yes, keys are identifiable by the nomenclature <TableName>FactID.

"Natural key" / grain

Question: Which columns make up the "natural key" / grain?

Answer: Firstly look at those with the nomenclature <TableName>SystemID. Also, look at the BBDW_DeletedIDTables.txt. This file maintains a list of audit tables to ensure that data deleted from the source tables is also deleted from the warehouse tables during the ETL process. There is also BBDW_DeletedIDTables_EXT.txt file which performs the same function for extensions. For more information, see ETL Process, File Locations (extensibility), and SSIS Deployment Files (extensibility).

Design methodology

Question: Is there a particular design methodology?

Answer: Blackbaud Data Warehouse design is influenced by the Kimball methodology. The warehouse is built on a modified star schema. For more information about that, see Basic Data Model Concepts and Data Model for Blackbaud Data Warehouse and OLAP. Depending on your version of Blackbaud CRM, you will see Transact-SQL Views at the data warehouse database level that abstract the data warehouse database into a pure star schema. Prior to that, the abstraction into a star schema was accomplished with data source views at the cube level.

Design methodology

Question: Is that why some things might not look like they conform to standard star schema design approaches?

Answer: Yes, when you look at the data warehouse database at the table level, there are relationships that create a modified star schema. But starting with versions above 2.91 of Blackbaud CRM, Transact-SQL Views abstract those variations into a pure star schema.

Source

Question: What is the purpose of the DIM_SOURCE dimension?

Answer: DIM_SOURCE describes all data sources which feed the data warehouse. By default there is only one, BBEC. Some of the information comes from the INSTALLATIONINFO table.

ISINCLUDED, ETLCONTROL, and SOURCEDIMID

Question: What is the purpose of the ISINCLUDED, ETLCONTROLID, and SOURCEDIMID?

Answer:

• ISINCLUDED indicates when data should be included in results. Blackbaud Data Warehouse does not use ISINCLUDED as of versions above 2.91 of Blackbaud CRM. ISINCLUDED was added for future development.
• ETLCONTROLID is an ID generated through the ETL process.
• SOURCEDIMID is the primary key of the DIM_SOURCE table.

DIM_CONSTITUENCY and FACT_CONSTITUENCY

Question: What is the difference between DIM_CONSTITUENCY and FACT_CONSTITUENCY?

Answer: The Constituency dimension contains information about constituencies. This includes user defined constituencies as well as system defined ones (Volunteer, Board Member, Fundraiser, Group Member, Staff, and Committee). The Constituency fact associates constituents to constituencies. This includes user defined constituencies as well as system defined ones (Volunteer, Board Member, Fundraiser, Group Member, Staff, and Committee).

Multicurrency

Question: How does Blackbaud Data Warehouse handle Multicurrency fields?

Answer: Generally, within the data warehouse database and the OLAP cube, "amount" refers to organization amount. Usually when "amount" refers to base amount or transaction amount, that is specified with a prefix on the column name. For example, in FACT_REVENUE there are columns for BASECURRENCYDIMID, BASETOTALREVENUEAMOUNT, and BASEREVENUEAPPLICATIONAMOUNT.

Although there are amount fields that reflect a base currency in Blackbaud Data Warehouse, there is no special logic behind them. Nothing is calculated.

Recurring gifts (revenue fact IDs)

Question How does Blackbaud Data Warehouse handle revenue fact ID (REVENUEFACTID) for recurring gifts?

Answer: In Blackbaud Data Warehouse databases, revenue fact IDs are usually taken from the revenue split level or in more recent versions of Blackbaud CRM that use the financial transaction model, at the financial transaction line item level. But recurring gifts exist at the revenue level. With recurring gifts, designations may vary with each instance. The revenue fact ID for a recurring gift is taken from the first revenue split or financial transaction line item for the recurring gift.

Warning: For recurring gifts, do not perform joins based on designation information.

Security

Question: How does Blackbaud Data Warehouse handle security?

Answer: The data warehouse and cube are populated using administrator level privileges. This means that the security roles defined in the core system are not explicitly respected by the reporting tools. You control access to specific data for your end users by crafting reports with the appropriate filters and then exposing those reports to the appropriate system role.

ETL Parallelism

Question: Can Blackbaud Data Warehouse perform parallel processing of SSIS packages?

Answer: As of version 3.0, yes. You can select to process up to four lanes of packages which move data from the transactional database to the Blackbaud Data Warehouse database. You enter how many lanes (1-4) to use from the Edit Advanced Data Mart Settingsscreen for the deployment utility. The field is Max ETL Parallelism under Deployment Options.

Schema Validation

Question: Is there a way to validate aspects of the schema for a Blackbaud Data Warehouse including extensions to the database?

Answer: As of version 3.0, yes. From the data mart page for a Blackbaud Data Warehouse, you can run a validation process which checks dozens of qualities of the database schema. There is an option to include extensions as a part of the checks.

Schema Updates (Tables and Views)

Question: Were tables and views in Blackbaud Data Warehouse updated in the 3.0 release of Blackbaud CRM?

Answer: Yes, Financial Transaction and Revenue structures have been updated in the warehouse database and the OLAP cube. For more information, see the technical reference here.

Blackbaud Infinity SDK Feature Specs

Question: With the 3.0 release of Blackbaud CRM, does the Blackbaud Infinity SDK support accessing Blackbaud Data Warehouse databases in additional specs?

Answer: Yes, the MartKey attribute was added to the Smart Query spec. This optional attribute in the SmartQuerySpec element can specify to create Transact-SQL functions in the Blackbaud Data Warehouse database instead of the transactional database. This enables Blackbaud CRM features to execute smart queries from the warehouse database.

The MartKey attribute was also added to the specs for SQL Functions, SQL Stored Procedures, and SQL Views.

Revenue Post Status Codes

Question: With the 3.0 release of Blackbaud CRM, why did Revenue Post Status codes change?

Answer: Prior to the Blackbaud CRM 3.0 release, the codes were: 0-Do Not Post; 1-Posted; 2-Not Posted. Revenue Post Status codes are now as follows: 1-Not Posted; 2-Posted; 3-Do Not Post. This change was made to ensure the information is synched between the data warehouse and the transactional database.