Write Search Lists against the Data Warehouse

The MartKey attribute is now available on search list specs so that you can create stored procedures that query the Blackbaud Data Warehouse database instead of the transactional database. And within the SPSearchList element, you can also now use the SPSchema attribute to store those stored procedures in the data warehouse schema.

The MartKey element was previously available on other spec types, including query view specs and data list specs, and you implement it the same way for all of these spec types.

To build a search list against the data warehouse, you add the MartKey attribute to the root SearchListSpec element and set its value to "BBDW." This designates the Data Warehouse as the target of the search list's stored procedure. If you do not include the MartKey attribute, the search list's stored procedure queries the transactional database by default.

<SearchListSpec
    xmlns="bb_appfx_searchlist"
    xmlns:common="bb_appfx_commontypes"
    ID ="702e8d47-67d0-49b3-999d-83ada99899e1"
    Name="Search"
    Description="A search list for the data warehouse"
    Author="Technical Training"
    RecordType="Constituent"
    TranslationFunctionID="80AC9831-FEDA-4918-BA38-AB4C2D025166"
    SecurityUIDisplayFeature="false"
    MartKey="BBDW"
  >

Then to designate the Data Warehouse as the database schema for the stored procedure, you add the SPSchema attribute to the SPSearchList element and set its value to "BBDW." Most database objects in the data warehouse live within the BBDW schema, so we recommend that you use it for stored procedures that query the data warehouse. If you set this attribute to "BBDW," make sure you also update the CREATE line of the stored procedure from the default "dbo" to "BBDW."

  <SPSearchList SPName="USP_STOREDPROCEDURE_EXAMPLE" SPSchema="BBDW">

With these attributes in place on a search list spec, you can create a stored procedure in the SPSearchList element that queries the data warehouse. That stored procedure will perform better than a search list that queries the transactional database, but its search results will not be up to the minute because the warehouse is updated on a scheduled basis by the ETL process.

Tip: For more information about the MartKey attribute on search list specs, see MartKey Attribute.