v_FACT_REVENUEFUNDRAISER

The revenue fundraiser fact table relates fundraisers to their revenue.

Fields

Field	Field Type	Null	Description
REVENUEFUNDRAISERFACTID	int		BBDW.[FACT_REVENUEFUNDRAISER].[REVENUEFUNDRAISERFACTID]
FUNDRAISERDIMID	int	yes	BBDW.[FACT_REVENUEFUNDRAISER].[FUNDRAISERDIMID]
REVENUEFUNDRAISERAMOUNT	money	yes	BBDW.[FACT_REVENUEFUNDRAISER].[REVENUEFUNDRAISERAMOUNT]
BASECURRENCYDIMID	int	yes	BBDW.[FACT_REVENUEFUNDRAISER].[BASECURRENCYDIMID]
BASEREVENUEFUNDRAISERAMOUNT	money	yes	BBDW.[FACT_REVENUEFUNDRAISER].[BASEREVENUEFUNDRAISERAMOUNT]
REVENUEFACTID	int	yes	BBDW.[FACT_REVENUE].[REVENUEFACTID]
REVENUESEQUENCEID	int	yes	BBDW.[FACT_REVENUE].[REVENUESEQUENCEID]
REVENUECODEDIMID	int	yes	BBDW.[FACT_REVENUE].[REVENUECODEDIMID]
REVENUEFLAGDIMID	int	yes	BBDW.[FACT_REVENUE].[REVENUEFLAGDIMID]
REVENUECHANNELDIMID	int	yes	BBDW.[FACT_REVENUE].[REVENUECHANNELDIMID]
REVENUEDATEDIMID	int	yes	BBDW.[FACT_REVENUE].[REVENUEDATEDIMID]
POSTDATEDIMID	int	yes	BBDW.[FACT_REVENUE].[POSTDATEDIMID]
DESIGNATIONDIMID	int	yes	BBDW.[FACT_REVENUE].[DESIGNATIONDIMID]
PURPOSELEVEL1DIMID	int	yes	BBDW.[FACT_REVENUE].[PURPOSELEVEL1DIMID]
PURPOSELEVEL2DIMID	int	yes	BBDW.[FACT_REVENUE].[PURPOSELEVEL2DIMID]
PURPOSELEVEL3DIMID	int	yes	BBDW.[FACT_REVENUE].[PURPOSELEVEL3DIMID]
PURPOSELEVEL4DIMID	int	yes	BBDW.[FACT_REVENUE].[PURPOSELEVEL4DIMID]
PURPOSELEVEL5DIMID	int	yes	BBDW.[FACT_REVENUE].[PURPOSELEVEL5DIMID]
GLREVENUECATEGORYDIMID	int	yes	BBDW.[FACT_REVENUE].[GLREVENUECATEGORYDIMID]
EVENTDIMID	int		BBDW.[FACT_REVENUE].[EVENTDIMID]
EVENTSTARTDATEDIMID	int		BBDW.[FACT_EVENTREGISTRANT].[EVENTSTARTDATEDIMID]
EVENTENDDATEDIMID	int		BBDW.[FACT_EVENTREGISTRANT].[EVENTENDDATEDIMID]
CONSTITUENTDIMID	int	yes	BBDW.[FACT_REVENUE].[CONSTITUENTDIMID]
CONSTITUENTSYSTEMID	uniqueidentifier	yes	BBDW.[FACT_REVENUE].[CONSTITUENTSYSTEMID]
CONSTITUENTINFORMATIONDIMID	int	yes	BBDW.[DIM_CONSTITUENT].[CONSTITUENTINFORMATIONDIMID]
CONSTITUENTAGE	int	yes	BBDW.[DIM_CONSTITUENT].[AGE]
CONSTITUENTADDRESSDIMID	int	yes	BBDW.[DIM_CONSTITUENT].[CONSTITUENTADDRESSDIMID]
CONSTITUENTADDRESSDETAILDIMID	int	yes	BBDW.[DIM_CONSTITUENT].[CONSTITUENTADDRESSDETAILDIMID]
CONSTITUENTADDRESSFLAGDIMID	int	yes	BBDW.[DIM_CONSTITUENT].[CONSTITUENTADDRESSFLAGDIMID]
CONSTITUENTADDRESSTYPEDIMID	int	yes	BBDW.[DIM_CONSTITUENT].[CONSTITUENTADDRESSTYPEDIMID]
CONSTITUENTDEMOGRAPHICDIMID	int	yes	BBDW.[DIM_CONSTITUENT].[CONSTITUENTDEMOGRAPHICDIMID]
MARKETINGSEGMENTDIMID	int	yes	BBDW.[FACT_REVENUE].[MARKETINGSEGMENTDIMID]
SEGMENTATIONDATEDIMID	int	yes	BBDW.[DIM_MARKETINGSEGMENT].[SEGMENTATIONDATEDIMID]
MARKETINGSOURCECODEDIMID	int	yes	BBDW.[FACT_REVENUE].[MARKETINGSOURCECODEDIMID]
MARKETINGCONSTITUENTFACTID	bigint	yes	BBDW.[FACT_REVENUE].[MARKETINGCONSTITUENTFACTID]
APPEALDIMID	int	yes	BBDW.[DIM_APPEAL].[APPEALDIMID]
APPEALSTARTDATEDIMID	int	yes	BBDW.[DIM_APPEAL].[APPEALSTARTDATEDIMID]
APPEALENDDATEDIMID	int	yes	BBDW.[DIM_APPEAL].[APPEALENDDATEDIMID]
APPEALWEEKDIMID	int	yes	The difference in weeks between the appeal start date and the revenue date. Anything over 104 weeks is labeled 105+.
MARKETINGRESPONSEWEEKDIMID	int	yes	The difference in weeks between the marketing effort date and the revenue date. Anything over 104 weeks is labeled 105+.
FINANCIALTRANSACTIONFACTID	int	yes	BBDW.[FACT_REVENUEFUNDRAISER].[FINANCIALTRANSACTIONFACTID]
FINANCIALTRANSACTIONLINEITEMFACTID	int	yes	BBDW.[FACT_REVENUEFUNDRAISER].[FINANCIALTRANSACTIONLINEITEMFACTID]

Definition

Copy


      CREATE view [BBDW].[v_FACT_REVENUEFUNDRAISER] as
        select
          rf.[REVENUEFUNDRAISERFACTID], 
          rf.[FUNDRAISERDIMID], 
          rf.[REVENUEFUNDRAISERAMOUNT], 
          rf.[BASECURRENCYDIMID], 
          rf.[BASEREVENUEFUNDRAISERAMOUNT], 
          r.[FINANCIALTRANSACTIONLINEITEMFACTID] as [REVENUEFACTID],
          r.[FINANCIALTRANSACTIONSEQUENCEID] as [REVENUESEQUENCEID], 
          r.[REVENUECODEDIMID],
          r.[REVENUEFLAGDIMID], 
          r.[REVENUECHANNELDIMID], 
          r.[FINANCIALTRANSACTIONDATEDIMID] as [REVENUEDATEDIMID], 
          r.[FINANCIALTRANSACTIONPOSTDATEDIMID] as [POSTDATEDIMID], 
          r.[DESIGNATIONDIMID], 
          r.[PURPOSELEVEL1DIMID], 
          r.[PURPOSELEVEL2DIMID], 
          r.[PURPOSELEVEL3DIMID], 
          r.[PURPOSELEVEL4DIMID], 
          r.[PURPOSELEVEL5DIMID], 
          r.[GLREVENUECATEGORYDIMID],          
          isnull(er.[EVENTDIMID], 0) as [EVENTDIMID], 
          isnull(er.[EVENTSTARTDATEDIMID], 0) as [EVENTSTARTDATEDIMID], 
          isnull(er.[EVENTENDDATEDIMID], 0) as [EVENTENDDATEDIMID], 
          r.[CONSTITUENTDIMID], 
          r.[CONSTITUENTSYSTEMID],    
          c.[CONSTITUENTINFORMATIONDIMID], 
          c.[AGE] as [CONSTITUENTAGE], 
          c.[CONSTITUENTADDRESSDIMID], 
          c.[CONSTITUENTADDRESSDETAILDIMID],
          c.[CONSTITUENTADDRESSFLAGDIMID],
          c.[CONSTITUENTADDRESSTYPEDIMID],    
          c.[CONSTITUENTDEMOGRAPHICDIMID],
          r.[MARKETINGSEGMENTDIMID],     
          ms.[SEGMENTATIONDATEDIMID], 
          r.[MARKETINGSOURCECODEDIMID], 
          r.[MARKETINGCONSTITUENTFACTID],  
          a.[APPEALDIMID], 
          a.[APPEALSTARTDATEDIMID], 
          a.[APPEALENDDATEDIMID], 
          case 
            when a.[APPEALSTARTDATE] is null then 0 
            when a.[APPEALSTARTDATE] > r.[FINANCIALTRANSACTIONDATE] then 0 
            when datediff(ww, a.[APPEALSTARTDATE], r.[FINANCIALTRANSACTIONDATE]) > 104 then 105 
            else datediff(ww, a.[APPEALSTARTDATE], r.[FINANCIALTRANSACTIONDATE]) + 1 
          end as [APPEALWEEKDIMID], 
          case 
            when ms.[SEGMENTATIONDATE] is null then 0 
            when ms.[SEGMENTATIONDATE] > r.[FINANCIALTRANSACTIONDATE] then 0 
            when datediff(ww, ms.[SEGMENTATIONDATE], r.[FINANCIALTRANSACTIONDATE]) > 104 then 105 
            else datediff(ww, ms.[SEGMENTATIONDATE], r.[FINANCIALTRANSACTIONDATE]) + 1 
          end as [MARKETINGRESPONSEWEEKDIMID],
          rf.[FINANCIALTRANSACTIONFACTID],
          rf.[FINANCIALTRANSACTIONLINEITEMFACTID]
        from 
          BBDW.[FACT_REVENUEFUNDRAISER] rf
          left join BBDW.[FACT_FINANCIALTRANSACTIONLINEITEM] r 
            on r.[FINANCIALTRANSACTIONLINEITEMFACTID] = rf.[REVENUEFACTID]
          left join BBDW.[DIM_CONSTITUENT] c
            on r.[CONSTITUENTDIMID] = c.[CONSTITUENTDIMID]
          left join BBDW.[DIM_MARKETINGSEGMENT] ms
            on r.[MARKETINGSEGMENTDIMID] = ms.[MARKETINGSEGMENTDIMID]
          left join BBDW.[DIM_APPEAL] a
            on r.[APPEALDIMID] = a.[APPEALDIMID]
          left join BBDW.[FACT_EVENTREGISTRANT] er
            on r.[EVENTREGISTRANTFACTID] = er.[EVENTREGISTRANTFACTID]    
          left join BBDW.[FACT_MEMBERSHIPTRANSACTION] mt
            on r.[FINANCIALTRANSACTIONLINEITEMFACTID] = mt.[REVENUEFACTID]