Should the Report Query a Table or a View?

As of version 2.93 of Blackbaud CRM, the data warehouse tables include prospect plan status information for interactions and prospect plans. But views in the warehouse do not. Views in Blackbaud Data Warehouse support access to warehouse data through a star schema. Stars for major giving and prospects functionality have not been created. There are three options for reporting from the warehouse for this situation.

  1. Query Blackbaud Data Warehouse tables through the report

  2. Extend Blackbaud Data Warehouse with views of the tables and query the views

  3. Extend Blackbaud Data Warehouse with new tables, views of the tables, and query the views

The preferred method to query Blackbaud Data Warehouse is through the views which establish the star schema. If you report off of the tables directly, you run the risk of a breaking change to your report if those tables are changed. Similarly, if you extend the warehouse to create a view of the existing tables, changes to the existing tables may break the view extension and also break the report. However, in this situation, you could fix the view instead of the report.

In order to reduce the number of potential break points due to future changes in Blackbaud Data Warehouse, you could extend the warehouse with new tables and views to support prospect plan status.

To create an extension to Blackbaud Data Warehouse to support a view, you will minimally need a database revisions extension to add the view. An database revision extension and an ETL extension is necessary if you add a table. For information about how to extend Blackbaud Data Warehouse, see BBDW/OLAP Extensibility Model.

Note: Blackbaud Data Warehouse also supports OLAP extensions for the OLAP cube that is fed by the data warehouse. But OLAP reporting and extensions are not within the scope of this discussion.