Reporting and data visualization are essential to nonprofit organizations around the world. They allow for the sharing of information between various departments within an organization, board members, and constituents. Quick, well-designed reporting also allows organizations to make informed decisions that can save time and money while increasing the effectiveness of its fundraising.
Blackbaud CRM allows its users to build queries, surface pre-built reports, and create customizations to use their stored data to its fullest. However, creating customizations requires technical knowledge (and likely access to the back-end SQL Server database) as well as developer tools such as Visual Studio. On top of this, the transactional database behind Blackbaud CRM contains thousands of tables which create a labyrinth of data that must be navigated.
Blackbaud Data Warehouse
Thankfully, Blackbaud has provided the Blackbaud Data Warehouse to its clients. The Blackbaud Data Warehouse (BBDW) uses a denormalized structure and fewer tables (hundreds instead of thousands) to allow for the most popular reporting and data visualization tools to access key information from the database quickly. This smaller database and simplified structure allows for customizations to be written more quickly and usually with less difficulty. To developers this means a saving of time, to end users it can mean faster more timely reporting, and to database managers it means less workload for your transactional database. Overall, it’s a win-win-win situation.
But the BBDW isn’t a perfect solution. To make the BBDW smaller and more efficient, some data had to be deemed extraneous. Product development teams reviewed many reports and customization requests to determine which tables, fields, calculations, etc. were most commonly used and most important for reporting purposes. This data – such as constituent bio and address information, or fundraising and transactional data – would be brought over during the Extract, Transform, and Load (ETL) process, but other data would not. One of the most frustrating things that can happen while creating a customization against the BBDW is discovering that a required piece of data doesn’t exist.
However, with access to tools like Visual Studio and SQL Server Integration Services (SSIS), the Blackbaud SDK allows informed developers to build additions, known as ‘extensions’, to the BBDW. This process involves creating multiple files that build new tables, explain to the ETL process how to load these tables, and files that surface data for these extension tables in the Blackbaud CRM UI for end users to access, such as custom query views. This process can be time consuming and costly, and ultimately may not be worth the effort for a single new field or table to be added to the BBDW.
Blackbaud Data Warehouse Express from Zuri Group
That’s where our BBDW Express (BBDWE) tool can help! With the Blackbaud SDK, each modification to the BBDW requires SSIS packages and revision packages to be migrated to servers and redeploys of the warehouse done (sometimes multiple times).
Part of the full-suite of solutions available to our Expert Access program subscribers, the BBDWE allows developers can utilize a more user-friendly interface to build data warehouse extensions without ever leaving CRM and SSMS.
No Visual Studio or SSIS developer tools required.
BBDWE allows for the creation and modification of extensions that can be deployed via its own business process without the need for uploading additional artifacts or running Blackbaud’s redeploy process. It also provides its own ETL process, which can be scheduled to run just like the out of box process, so keeping these extension tables up to date is all part of the same tool.
Have multiple database instances?
No problem! If our BBDWE customization is installed on these instances, the extensions created through the tool can be exported from one database and imported into another. This means your local, DEV, TEST, and PROD environments can be updated quickly and easily by sharing the exported files.
Have an extension that you think every branch of your organization would find useful? Deploy, Export, and Import.
If the source data exists, the new extension can be up and running across your organization without the need for additional development, localization, deploys, and downtime.
Here’s how it works:
Creating a new BBDW extension within CRM without external tools
Defining a new BBDW table
Defining the logic behind how the new table will be mapped and populated.
Importing a previously built extension
Because of these features and the ability to take the customization of the BBDW into your own hands, the BBDWE from Zuri Group should be a staple for any organization who needs to go above and beyond the standard out-of-the-box deliverable.
Brian Harris is a Senior Developer here at Zuri Group and has more than 15 years of experience with Blackbaud software and methodology, specializing in custom reporting and development.