top of page

Blackbaud Data Warehouse Tips & Tricks

If you've implemented Blackbaud CRM™, you're probably using the Blackbaud Data Warehouse™ (BBDW™) for reporting. When using the BBDW™, there are a few things to keep in mind:

  • If you want to use smart fields and attributes in the BBDW™, make sure you have chosen to include them in the data warehouse so that these items are usable in the BBDW™ after the ETL Refresh - they are not included by default.

  • Our best practice recommendation is to not schedule the ETL Refresh in your production environment until after the smart fields and any other necessary business process run. This ensures accurate data and only a one-day lag data. If the ETL refresh runs before the smart fields finish, you'll have two day old data (at least.)

  • Setup a backup schedule for the BBDW™.

  • Setup a maintenance plan to keep the indices updated and rebuild them as necessary. This will help you as you continue to rely on the BBDW™ for reporting.

  • Keep a naming convention separate from the out of the box BBDW tables so that you do not lose the extensions during hot-fixes and upgrades. If you add extensions to the BBDW™

  • There is no history kept in the out of the box tables. Remember that a change made in Blackbaud CRM™ on a given day will not show up in the BBDW™ until the ETL Refresh occurs. Any reporting using the BBDW will reflect this. Make sure users understand the time delay when using BBDW™ driven reports.

  • Indices can be added to the BBDW to help with reporting and performance. If you have a lot of records in the table you added an index to you might want to drop the index before the ETL Refresh. Dropping any new indices before the ETL Refresh will help with not impacting the runtime of the ETL Refresh. One way of handling this is to setup a job that runs a few minutes before the ETL Refresh to drop the extra indices. Then, to recreate the indices, a SSIS package could be setup to check if the index exists and create the index if it does not exist.

If you have another Blackbaud CRM™ environment such as Stage or DEV and refresh that environment from Production on a schedule, or for any reason, and a BBDW™ is setup as well, at a minimum after each refresh of the Blackbaud CRM™ database a Reset ETL and ETL Refresh will need to be performed on the BBDW™ (Note: Depending on how the environment is setup, a redeploy of the BBDW™ might have to take place before the Reset ETL and ETL Refresh. If the Reset ETL and ETL Refresh does not occur, there would be a mixture of old data with new data.) This would cause confusion during any use of the BBDW™ and create issues for report end users.

Following these simple tips will help you maintain the BBDW™ for your organizations reporting needs. For more information about the BBDW™ or Blackbaud CRM™ contact the BrightVine Team.

bottom of page