Connect and collaborate with fellow Blackbaud users.
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.
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.