BBDW

BBDW Refresh/ETL performance hints and tips

By: Alan Eager

Posted on: March 5, 2014

This article is to help out with increasing your BBDW refresh/ETL (Extract, Transform and Load) times.  Things to consider are as follows: 1)      Updating Stats on the CRM Database Just by running the SQL Server Stored Procedure sp_updatestats on the BB CRM database and letting it run (potentially for a few minutes) can potentially save hours for a BBDW refresh. exec sp_updatestats Some ETL performance work I've been doing recently at a client with larger data volumes (6 Million+ constits, 25 Million+ gifts, 60 Million+ gift attributes) initially resulted in Financial Transaction and Financia... Read More

Improving segmentation performance using BBDW

By: Ron Frum

Posted on: September 27, 2013

Putting together a direct marketing effort that sends the right package to the right people is a lot of work. It often involves writing dozens of queries, validating the results, tweaking the queries, building segments, refreshing segments, building the marketing effort, validating the results and then activating the marketing effort. Refreshing a segment involves refreshing the static selection for each selection in the query which can take a while on large databases with complex queries.  In Blackbaud CRM 3.0, we added the ability to build ad-hoc queries against the Blackbaud Data Warehouse.... Read More
Categories: ,

Summary of BBDW Infinitization

By: Ron Frum

Posted on: May 22, 2013

The last couple releases of Blackbaud CRM have included the ability for certain spec types to work against the Blackbaud Data Warehouse by specifying MartKey="BBDW" at the root of the spec. I wanted to provide a quick chart for help in understanding what capabilities are available in each version. Spec Version Notes Report 2.93 View Data Form 2.94 Data List 2.94 Can also be used to drive UI Widgets Query View 2.94 Non-root query views that can drive ListBuilders Query View 3.0 Root query views for ad-hoc queries including selections Smart Query 3.0 SQL Function 3.0 SQL Stored Procedure 3.0 SQL... Read More
Categories:

BBDW 3.0 – Optimized for SQL Server 2012

By: Ron Frum

Posted on: May 20, 2013

One of the most exciting features of SQL Server 2012 is columnstore indexes. MSDN describes a columnstore index as: An xVelocity memory optimized columnstore index, groups and stores data for each column and then joins all the columns to complete the whole index. This differs from traditional indexes which group and store data for each row and then join all the rows to complete the whole index. Columnstore indexes can improve query performance in many reporting scenarios and also reduce the total size of indexes for a table. In Blackbaud CRM 3.0, we’ve added columnstore indexes to all fact tab... Read More
Categories: ,

BBDW 3.0 – Smart Query

By: Ron Frum

Posted on: May 15, 2013

One of the more under-utilized options in the Infinity developer’s toolbox is the Smart Query. When building a smart query, a developer determines all of the output fields needed for a query, defines the business logic and exposes some parameters to the user. The result is that a user only has to pick their parameter values and run the query without having to build out the filter and output fields as they would in ad-hoc query. My favorite smart query examples are the LYBUNT (Last Year But Unfortunately Not This year) and SYBUNT (Some Year But Unfortunately Not This year) smart queries. Buildi... Read More
Categories:

BBDW 3.0 – Optimizing ETL Performance

By: Ron Frum

Posted on: May 8, 2013

I wanted to take a moment to highlight a new Advanced Setting in the BBDW deployment utility that can have a profound impact on your ETL performance in Blackbaud CRM 3.0. The BBDW ETL process is controlled by the main BBDW_ETL.dtsx file. This package executes all of the child packages that actually move the data from the Infinity database to the data warehouse. In previous versions, it used the BBDW_PackageList.txt file to determine the packages to run and the order that they should run in. The BBDW_ETL package would read the first package from this file, run the package and once it completed,... Read More
Categories: ,

BBDW 3.0 – SQL Objects

By: Ron Frum

Posted on: April 18, 2013

In versions of Blackbaud CRM prior to 3.0, if you wanted to add or alter a stored procedure, function or view to the Blackbaud Data Warehouse, you would create a BBDW Database Revision that creates or alters the database object and then run the BBDW deployment utility to run the revision against BBDW. This works fine and continues to be supported but can be cumbersome during report development where you may need to go through several iterations of a stored procedure before a report is ready for production. Having to run the BBDW deployment each time can slow down report development particularl... Read More
Categories:

BBDW 3.0 – Validation

By: Ron Frum

Posted on: April 9, 2013

When visiting the Data Mart page in Blackbaud CRM 3.0, you may notice some new validation tasks on the left side. The Blackbaud Data Mart Validation process is a new diagnostic tool that identifies common data integrity issues, missing database objects and ensures that our best practices are enforced. The process includes two options: Include Extensions – If this box is checked, the process will include custom tables in its battery of tests. If the box is not checked,  table names must start with USR_ or end with _EXT will be excluded from the tests. Run after ETL completes – If this box is ch... Read More
Categories:

BBDW 3.0 – Ad-Hoc Query

By: Ron Frum

Posted on: April 5, 2013

I’m very excited about the release of Blackbaud CRM 3.0. My favorite new feature is the ability to build ad-hoc queries against the Blackbaud Data Warehouse using query views. Query views against BBDW can do most of the things that query views against the Infinity database can do including: Save queries for later re-use Export directly from the query results grid Create an export process Create an export definition Create a smart query based on an ad-hoc query Create a report using Report Builder Save a static selection (note that dynamic selections are not supported at this time) Use a static... Read More

BBDW – Importing Attributes and Smartfields

By: Alan Eager

Posted on: March 14, 2013

In BBDW, you can configure SmartFields and Attributes to come over into the Data Warehouse.  This can be done using the CRM Admin Data Marts screen. Click on the “Select eligible smart fields” or “Select eligible attribute categories” on the top left in the action bar. SmartFields will show Constituent and Revenue SmartFields with Value groups: Attributes will only show attribute data types Table and Yes/No: Select the Smartfield and/or attribute you need so it appears on the right hand selection box then when the next ETL Refresh occurs, you will have data in the various tables in BBDW: DIM_A... Read More
Categories:

Privacy Policy | Sitemap | © 2011 Blackbaud, Inc. All Rights Reserved

Digital Ocean