Schema Updates (Tables and Views)

A previous release of Blackbaud CRM (2.93) included updates to the schema for revenue and financial transactions in the transactional database. In order to provide fuller access to that data from Blackbaud Data Warehouse, financial transaction and revenue structures have been updated in the warehouse database and the OLAP cube. The FACT_REVENUE table has been replaced with the FACT_REVENUE view. Previously, the ETL process populated the FACT_REVENUE table. Now the FACT_REVENUE view maps to a set of new tables in the Blackbaud Data Warehouse database.

The FACT_REVENUE_STAGE table was removed because the FACT_REVENUE view is not populated by an ETL process. The procedures to create or drop indices during the ETL process were also removed.

For example, previously the REVENUELOOKUPID column on the FACT_REVENUE table was populated by the ETL process which queried the dbo.[FINANCIALTRANSACTION].[USERDEFINEDID] column in the transactional database. Now the view maps the column to BBDW.[FACT_FINANCIALTRANSACTIONLINEITEM].[FINANCIALTRANSACTIONLOOKUPID], a column in the Blackbaud Data Warehouse database. The new table, BBDW.[FACT_FINANCIALTRANSACTIONLINEITEM] is populated by the ETL process which queries various tables in the transactional database. For a list of new, modified, and removed tables and views, see the What's New reference.

The FACT_MEMBERSHIPSTATUS table was deprecated. The procedure to create or drop indices during an ETL process was also removed. There were no dependencies on the FACT_MEMBERSHIPSTATUS table in the shipped OLAP cube and the table was not populated by the ETL process. Membership status information is available through DIM_MEMBERSHIPSTATUS and v_DIM_MEMBERSHIPSTATUS.

Revenue Post Status codes changed. 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.

Many changes listed as modifications were changes to the MS_Description extended property on database entities such as tables and columns. This metadata was updated throughout the database schema in conjunction with the new validation feature. Other schema updates include adding procedures to support the new validation feature, columnstore indexes, and the new ETL parallelism feature.

For more information, see the Blackbaud Infinity Technical Reference.