Error: Data cannot be updated in a table with a columnstore index - when running the data warehouse ETL

When running a full or partial refresh of the Blackbaud CRM or Direct Marketing data warehouse the following error may be encountered:

[xxxxx] statement failed because data cannot be updated in a table with a columnstore index.  Consider disabling the columnstore index before issuing the [xxxxx] statement, then rebuilding the columnstore index after the [xxxxx] is complete.

This may occur for insert, update or merge SQL statements and may be encountered whenever the data warehouse ETL process runs which could include the deployment.
Columnstore indexes were added in SQL Server version 2012 and offer significant performance benefits over traditional indexes.  The drawback is that tables with non-clustered indexes cannot be updated.  SQL Server version 2014 allows for updates to clustered columnstore indexes.  Blackbaud CRM version 4.0 adds a non-clustered columnstore index to all out-of-box DIM and FACT tables.  Any attempt to insert, update or merge into these tables will need to take into account the columnstore index or this error will be encountered.

The Blackbaud data warehouse provides methods to drop and create columnstore indexes via the BBDW.USP_DROPCOLUMNSTOREINDEX and the BBDW.CREATECOLUMNSTOREINDEX stored procedure.  Each accepts a schema and table name.  These can be used to drop a columnstore index for a particular table prior to issuing any insert, update or merge statements.  
  • Be sure to create the columnstore index if dropping it to ensure optimal performance.
  • If using the DIM_TEMPLATE.dtsx package this error may be encountered.  This package is provided as a template representing best practices for a DIM table package and is not intended to be deployed.

Note: We provide links to third-party websites in an effort to help you resolve your issue. We are not responsible for the information on third-party websites and we cannot assist with implementing resolutions from these websites.




 

Steps to Duplicate

  1. Create an BBDW ETL Extension using the Blackbaud Infinity SDK tools.
  2. In the extension package, create a task that updates data in the DIM_APPEAL table
  3. Deploy the package and run the ETL for a full or partial refresh

Environment

 SQL Server 2012;SQL Server 2014;SQL Server 2016
 Blackbaud CRM
 4.0

Was this article helpful?