Blackbaud Community
Connect and collaborate with fellow Blackbaud users.
This seems to be in direct relation to an issue with the out of box SSIS package which causes 6 giving years to show regardless of how many years the constituent really has or what is set on the front end for the loyal donor constituency as far as how many years are required to be a loyal donor as configured in Constituents\Constituencies\Donor constituency criteria\Edit\A loyal donor.
The Loyal Donor Constituency gets deleted out of the Blackbaud Data Warehouse only after a full reset and then subsequent refresh, it does not occur after only a refresh.
This happens for all constituents in the Blackbaud Data Warehouse that previously had the Loyal Donor constituency prior to the full reset and refresh occurring.
The Loyal Donor Constituency gets deleted out of the Blackbaud Data Warehouse only after a full reset and then subsequent refresh, it does not occur after only a refresh.
This happens for all constituents in the Blackbaud Data Warehouse that previously had the Loyal Donor constituency prior to the full reset and refresh occurring.
Article Number: 95017
Products:
We are currently evaluating this issue for a fix in a future release.
Steps to Duplicate
1. Take note of a constituent that has the Loyal Donor constituency on their record and note how many giving years they have.
2. Also note the setting for for the loyal donor constituency as far as how many years are required to be a loyal donor as configured in Constituents\Constituencies\Donor constituency criteria\Edit\A loyal donor.
3. Go to Administration\Data warehouses\Blackbaud Data Warehouse\Refresh Data on the left hand side
4. Click on Start ETL refresh and wait for process to complete.
5. When the ETL refresh is completed, go to SQL Server Management Studio and click on New Query and select the Blackbaud Data Warehouse database (OLAP) for the corresponding main CRM\OLTP database.
6. In the query window run the following SQL query:
select constituency, * from bbdw.FACT_CONSTITUENCY fc join bbdw.DIM_CONSTITUENCY dcy on fc.CONSTITUENCYDIMID = dcy.CONSTITUENCYDIMID join bbdw.DIM_CONSTITUENT dc on fc.CONSTITUENTDIMID = dc.CONSTITUENTDIMID where dc.CONSTITUENTLOOKUPID = 'lookupID of constituent
7. Note that the Loyal Donor constituency still remains on the constituent record in the Blackbaud Data Warehouse as viewed in SQL after en ETL refresh.
Part II: Noting that the Loyal Donor constituency is gone after a full reset then ETL refresh.
1. Take note of a constituent that has the Loyal Donor constituency on their record and note how many giving years they have
2. Also note the setting for for the loyal donor constituency as far as how many years are required to be a loyal donor as configured in Constituents\Constituencies\Donor constituency criteria\Edit\A loyal donor.
3. Go to Administration\Data warehouses\Blackbaud Data Warehouse\Refresh Data on the left hand side
4. Click on Reset ETL. Note the message that comes up saying, "If you continue then the next ETL will perform a full refresh which may take a long time. Are you sure you want to reset the incremental refresh so that a full refresh will be performed the next time the ETL is processed?" Click the YES button here.
5. Now click on Start ETL refresh and wait for process to complete.
6. When the ETL refresh is completed, go to SQL Server Management Studio and click on New Query and select the Blackbaud Data Warehouse database (OLAP) for the corresponding main CRM\OLTP database.
7. In the query window run the following SQL query:
select constituency, *
from bbdw.FACT_CONSTITUENCY fc
join bbdw.DIM_CONSTITUENCY dcy on fc.CONSTITUENCYDIMID = dcy.CONSTITUENCYDIMID
join bbdw.DIM_CONSTITUENT dc on fc.CONSTITUENTDIMID = dc.CONSTITUENTDIMID
where dc.CONSTITUENTLOOKUPID = 'lookupID of constituent'
8. Note that the Loyal Donor constituency is now gone in the list of constituencies for the constituent in question. This occurs for any constituent in the Blackbaud Data Warehouse that previously had the Loyal Donor constituency prior to the full reset and refresh occurring.
In the Execute SQL Task name "Loyal Donors", the statement:
declare @YEARMINDATEDIMID int = (select [DATEDIMID] from BBDW.[DIM_DATE] where [ACTUALDATE] = (select cast(dateadd(yy,-[CONSTITUENCYCRITERIA].[LOYALDONORCONSTITUENCYDEFPERIOD], @DATAWINDOWCLOSE) as date) from BBDW.[CONSTITUENCYCRITERIA] where [ID] = 1));
assigns a value to the parameter @YEARMINDATEDIMID by subtracting the number of years in the loyal donor definition ([CONSTITUENCYCRITERIA].[LOYALDONORCONSTITUENCYDEFPERIOD)
For example:
datawindowclose date (7/14/2015) minus 5 years which would equal 7/14/2010. The code then calculates the number of years each constituent has donated since this date. If you look at a constituent that has donated in all of the years, 2010, 2011, 2012, 2013, 2014 and 2015 this is a total of 6. The code, however, filters by this having clause in several places:
having count(*) = [CONSTITUENCYCRITERIA].[LOYALDONORCONSTITUENCYDEFPERIOD]
The count(*) in this case is 6 while the LOYALDONORCONSTITUENCYDEFPERIOD is five, so the constituent is not included in this constituency.
Environment
Blackbaud CRM
4.0
4.0.141.9
