v_FACT_REVENUE

The revenue fact table contains information about revenue.

Fields

Field	Field Type	Null	Description
REVENUEFACTID	int		BBDW.[FACT_REVENUE].[REVENUEFACTID]
REVENUESYSTEMID	uniqueidentifier	yes	BBDW.[FACT_REVENUE].[REVENUESYSTEMID]
REVENUELOOKUPID	nvarchar(100)	yes	BBDW.[FACT_REVENUE].[REVENUELOOKUPID]
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]
REVENUEAPPLICATIONAMOUNT	money	yes	BBDW.[FACT_REVENUE].[REVENUEAPPLICATIONAMOUNT]
TOTALREVENUEAMOUNT	money	yes	BBDW.[FACT_REVENUE].[TOTALREVENUEAMOUNT]
TOTALREVENUERECEIPTAMOUNT	money	yes	BBDW.[FACT_REVENUE].[TOTALREVENUERECEIPTAMOUNT]
BASECURRENCYDIMID	int	yes	BBDW.[FACT_REVENUE].[BASECURRENCYDIMID]
BASETOTALREVENUEAMOUNT	money	yes	BBDW.[FACT_FINANCIALTRANSACTIONLINEITEM].[FINANCIALTRANSACTIONBASEAMOUNT]
BASEREVENUEAPPLICATIONAMOUNT	money	yes	BBDW.[FACT_FINANCIALTRANSACTIONLINEITEM].[FINANCIALTRANSACTIONLINEITEMBASEAMOUNT]
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+.
CHANGEDDATEDIMID	int	yes	BBDW.[FACT_REVENUE].[CHANGEDDATEDIMID]
CHANGEDDATE	datetime	yes	BBDW.[FACT_REVENUE].[CHANGEDDATE]
APPUSERDIMID	int	yes	BBDW.[FACT_REVENUE].[APPUSERDIMID]
CHECKNUMBER	nvarchar(50)	yes	BBDW.[FACT_REVENUE].[CHECKNUMBER]
CHECKFUZZYDATE	char(8)	yes	BBDW.[FACT_REVENUE].[CHECKFUZZYDATE]
CREDITCARDTYPEDIMID	int	yes	BBDW.[FACT_REVENUE].[CREDITCARDTYPEDIMID]
FINANCIALTRANSACTIONFACTID	int	yes	BBDW.[FACT_FINANCIALTRANSACTIONLINEITEM].[FINANCIALTRANSACTIONFACTID]
FINANCIALTRANSACTIONLINEITEMFACTID	int		BBDW.[FACT_FINANCIALTRANSACTIONLINEITEM].[FINANCIALTRANSACTIONLINEITEMFACTID]
MARKETINGSEGMENTATIONDIMID	int	yes	BBDW.[FACT_FINANCIALTRANSACTIONLINEITEM].[MARKETINGSEGMENTATIONDIMID]

Definition

Copy


      CREATE view [BBDW].[v_FACT_REVENUE] as
      select 
        [FACT_FINANCIALTRANSACTIONLINEITEM].[FINANCIALTRANSACTIONLINEITEMFACTID] as [REVENUEFACTID], 
        [FACT_FINANCIALTRANSACTIONLINEITEM].[FINANCIALTRANSACTIONLINEITEMSYSTEMID] as [REVENUESYSTEMID],
        [FACT_FINANCIALTRANSACTIONLINEITEM].[FINANCIALTRANSACTIONLOOKUPID] as [REVENUELOOKUPID],     
        [FACT_FINANCIALTRANSACTIONLINEITEM].[FINANCIALTRANSACTIONSEQUENCEID] as [REVENUESEQUENCEID], 
        [FACT_FINANCIALTRANSACTIONLINEITEM].[REVENUECODEDIMID],
        [FACT_FINANCIALTRANSACTIONLINEITEM].[REVENUEFLAGDIMID], 
        [FACT_FINANCIALTRANSACTIONLINEITEM].[REVENUECHANNELDIMID], 
        [FACT_FINANCIALTRANSACTIONLINEITEM].[FINANCIALTRANSACTIONDATEDIMID] as [REVENUEDATEDIMID], 
        [FACT_FINANCIALTRANSACTIONLINEITEM].[FINANCIALTRANSACTIONPOSTDATEDIMID] as [POSTDATEDIMID], 
        [FACT_FINANCIALTRANSACTIONLINEITEM].[DESIGNATIONDIMID], 
        [FACT_FINANCIALTRANSACTIONLINEITEM].[PURPOSELEVEL1DIMID], 
        [FACT_FINANCIALTRANSACTIONLINEITEM].[PURPOSELEVEL2DIMID], 
        [FACT_FINANCIALTRANSACTIONLINEITEM].[PURPOSELEVEL3DIMID], 
        [FACT_FINANCIALTRANSACTIONLINEITEM].[PURPOSELEVEL4DIMID], 
        [FACT_FINANCIALTRANSACTIONLINEITEM].[PURPOSELEVEL5DIMID], 
        [FACT_FINANCIALTRANSACTIONLINEITEM].[GLREVENUECATEGORYDIMID],  
        [FACT_FINANCIALTRANSACTIONLINEITEM].[FINANCIALTRANSACTIONLINEITEMAMOUNT] as [REVENUEAPPLICATIONAMOUNT], 
        [FACT_FINANCIALTRANSACTIONLINEITEM].[FINANCIALTRANSACTIONAMOUNT] as [TOTALREVENUEAMOUNT], 
        [FACT_FINANCIALTRANSACTIONLINEITEM].[FINANCIALTRANSACTIONRECEIPTAMOUNT] as [TOTALREVENUERECEIPTAMOUNT], 
        [FACT_FINANCIALTRANSACTIONLINEITEM].[BASECURRENCYDIMID], 
        [FACT_FINANCIALTRANSACTIONLINEITEM].[FINANCIALTRANSACTIONBASEAMOUNT] as [BASETOTALREVENUEAMOUNT],
        [FACT_FINANCIALTRANSACTIONLINEITEM].[FINANCIALTRANSACTIONLINEITEMBASEAMOUNT] as [BASEREVENUEAPPLICATIONAMOUNT],         
        isnull([FACT_EVENTREGISTRANT].[EVENTDIMID], 0) as [EVENTDIMID], 
        isnull([FACT_EVENTREGISTRANT].[EVENTSTARTDATEDIMID], 0) as [EVENTSTARTDATEDIMID], 
        isnull([FACT_EVENTREGISTRANT].[EVENTENDDATEDIMID], 0) as [EVENTENDDATEDIMID], 
        [FACT_FINANCIALTRANSACTIONLINEITEM].[CONSTITUENTDIMID], 
        [FACT_FINANCIALTRANSACTIONLINEITEM].[CONSTITUENTSYSTEMID],    
        [DIM_CONSTITUENT].[CONSTITUENTINFORMATIONDIMID], 
        [DIM_CONSTITUENT].[AGE] as [CONSTITUENTAGE], 
        [DIM_CONSTITUENT].[CONSTITUENTADDRESSDIMID], 
        [DIM_CONSTITUENT].[CONSTITUENTADDRESSDETAILDIMID],
        [DIM_CONSTITUENT].[CONSTITUENTADDRESSFLAGDIMID],
        [DIM_CONSTITUENT].[CONSTITUENTADDRESSTYPEDIMID],    
        [DIM_CONSTITUENT].[CONSTITUENTDEMOGRAPHICDIMID],
        [FACT_FINANCIALTRANSACTIONLINEITEM].[MARKETINGSEGMENTDIMID],     
        [DIM_MARKETINGSEGMENT].[SEGMENTATIONDATEDIMID], 
        [FACT_FINANCIALTRANSACTIONLINEITEM].[MARKETINGSOURCECODEDIMID], 
        [FACT_FINANCIALTRANSACTIONLINEITEM].[MARKETINGCONSTITUENTFACTID],  
        [DIM_APPEAL].[APPEALDIMID], 
        [DIM_APPEAL].[APPEALSTARTDATEDIMID], 
        [DIM_APPEAL].[APPEALENDDATEDIMID], 
        case 
        when [DIM_APPEAL].[APPEALSTARTDATE] is null then 0 
        when [DIM_APPEAL].[APPEALSTARTDATE] > [FACT_FINANCIALTRANSACTIONLINEITEM].[FINANCIALTRANSACTIONDATE] then 0 
        when datediff(ww, [DIM_APPEAL].[APPEALSTARTDATE], [FACT_FINANCIALTRANSACTIONLINEITEM].[FINANCIALTRANSACTIONDATE]) > 104 then 105 
        else datediff(ww, [DIM_APPEAL].[APPEALSTARTDATE], [FACT_FINANCIALTRANSACTIONLINEITEM].[FINANCIALTRANSACTIONDATE]) + 1 
        end as [APPEALWEEKDIMID], 
        case 
        when [DIM_MARKETINGSEGMENT].[SEGMENTATIONDATE] is null then 0 
        when [DIM_MARKETINGSEGMENT].[SEGMENTATIONDATE] > [FACT_FINANCIALTRANSACTIONLINEITEM].[FINANCIALTRANSACTIONDATE] then 0 
        when datediff(ww, [DIM_MARKETINGSEGMENT].[SEGMENTATIONDATE], [FACT_FINANCIALTRANSACTIONLINEITEM].[FINANCIALTRANSACTIONDATE]) > 104 then 105 
        else datediff(ww, [DIM_MARKETINGSEGMENT].[SEGMENTATIONDATE], [FACT_FINANCIALTRANSACTIONLINEITEM].[FINANCIALTRANSACTIONDATE]) + 1 
        end as [MARKETINGRESPONSEWEEKDIMID] ,
        [FACT_FINANCIALTRANSACTIONLINEITEM].[CHANGEDDATEDIMID],
        [FACT_FINANCIALTRANSACTIONLINEITEM].[CHANGEDDATE],
        [FACT_FINANCIALTRANSACTIONLINEITEM].[APPUSERDIMID],
        [FACT_FINANCIALTRANSACTIONLINEITEM].[CHECKNUMBER],
        [FACT_FINANCIALTRANSACTIONLINEITEM].[CHECKFUZZYDATE],
        [FACT_FINANCIALTRANSACTIONLINEITEM].[CREDITCARDTYPEDIMID],
        [FACT_FINANCIALTRANSACTIONLINEITEM].[FINANCIALTRANSACTIONFACTID],
        [FACT_FINANCIALTRANSACTIONLINEITEM].[FINANCIALTRANSACTIONLINEITEMFACTID],
        [FACT_FINANCIALTRANSACTIONLINEITEM].[MARKETINGSEGMENTATIONDIMID]        
      from
        [BBDW].[FACT_FINANCIALTRANSACTIONLINEITEM]  
        inner join [BBDW].[DIM_REVENUECODE] on [FACT_FINANCIALTRANSACTIONLINEITEM].[REVENUECODEDIMID] = [DIM_REVENUECODE].[REVENUECODEDIMID]
        inner join [BBDW].[DIM_REVENUEFLAG] on [FACT_FINANCIALTRANSACTIONLINEITEM].[REVENUEFLAGDIMID] = [DIM_REVENUEFLAG].[REVENUEFLAGDIMID]
        left join [BBDW].[DIM_CONSTITUENT] on [FACT_FINANCIALTRANSACTIONLINEITEM].[CONSTITUENTDIMID] = [DIM_CONSTITUENT].[CONSTITUENTDIMID]
        left join [BBDW].[DIM_MARKETINGSEGMENT] on [FACT_FINANCIALTRANSACTIONLINEITEM].[MARKETINGSEGMENTDIMID] = [DIM_MARKETINGSEGMENT].[MARKETINGSEGMENTDIMID]
        left join [BBDW].[DIM_APPEAL] on [FACT_FINANCIALTRANSACTIONLINEITEM].[APPEALDIMID] = [DIM_APPEAL].[APPEALDIMID]
        left join [BBDW].[FACT_EVENTREGISTRANT] on [FACT_FINANCIALTRANSACTIONLINEITEM].[EVENTREGISTRANTFACTID] = [FACT_EVENTREGISTRANT].[EVENTREGISTRANTFACTID]     
      where    
        [DIM_REVENUECODE].[REVENUETRANSACTIONTYPECODE] in (0,1,2,3,4,5,6,7,8,9,15)
        and [DIM_REVENUECODE].[FINANCIALTRANSACTIONLINEITEMTYPECODE] in (0,98)
        and [DIM_REVENUEFLAG].[ISDELETEDFINANCIALTRANSACTIONLINEITEM] = 0