v_FACT_REVENUERECOGNITION

The revenue recognition fact table contains information revenue recognitions.

Fields

Field Field Type Null Description
REVENUERECOGNITIONFACTID int BBDW.[FACT_REVENUERECOGNITION].[REVENUERECOGNITIONFACTID]
REVENUERECOGNITIONAMOUNT money yes BBDW.[FACT_REVENUERECOGNITION].[REVENUERECOGNITIONAMOUNT]
BASECURRENCYDIMID int yes BBDW.[FACT_REVENUERECOGNITION].[BASECURRENCYDIMID]
BASEREVENUERECOGNITIONAMOUNT money yes BBDW.[FACT_REVENUERECOGNITION].[BASEREVENUERECOGNITIONAMOUNT]
EFFECTIVEDATEDIMID int yes BBDW.[FACT_REVENUERECOGNITION].[EFFECTIVEDATEDIMID]
REVENUERECOGNITIONTYPEDIMID int yes BBDW.[FACT_REVENUERECOGNITION].[REVENUERECOGNITIONTYPEDIMID]
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_REVENUERECOGNITION].[CONSTITUENTDIMID]
CONSTITUENTSYSTEMID uniqueidentifier yes BBDW.[FACT_REVENUERECOGNITION].[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_REVENUERECOGNITION].[FINANCIALTRANSACTIONFACTID]
FINANCIALTRANSACTIONLINEITEMFACTID int yes BBDW.[FACT_REVENUERECOGNITION].[FINANCIALTRANSACTIONLINEITEMFACTID]
ADDEDDATE datetime yes BBDW.[FACT_REVENUERECOGNITION].[ADDEDDATE]
ADDEDDATEDIMID int yes BBDW.[FACT_REVENUERECOGNITION].[ADDEDDATEDIMID]
CHANGEDDATE datetime yes BBDW.[FACT_REVENUERECOGNITION].[CHANGEDDATE]
CHANGEDDATEDIMID int yes BBDW.[FACT_REVENUERECOGNITION].[CHANGEDDATEDIMID]

Definition

Copy 

CREATE view [BBDW].[v_FACT_REVENUERECOGNITION] as
  select
    rr.[REVENUERECOGNITIONFACTID], 
    rr.[REVENUERECOGNITIONAMOUNT], 
    rr.[BASECURRENCYDIMID], 
    rr.[BASEREVENUERECOGNITIONAMOUNT], 
    rr.[EFFECTIVEDATEDIMID], 
    rr.[REVENUERECOGNITIONTYPEDIMID], 
    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], 
    rr.[CONSTITUENTDIMID], 
    rr.[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],
    rr.[FINANCIALTRANSACTIONFACTID],
    rr.[FINANCIALTRANSACTIONLINEITEMFACTID],
    rr.[ADDEDDATE],
    rr.[ADDEDDATEDIMID],
    rr.[CHANGEDDATE],
    rr.[CHANGEDDATEDIMID]
  from
    BBDW.[FACT_REVENUERECOGNITION] rr
    left join [BBDW].[FACT_FINANCIALTRANSACTIONLINEITEM] r
      on r.[FINANCIALTRANSACTIONLINEITEMFACTID] = rr.[REVENUEFACTID]
    left join BBDW.[DIM_CONSTITUENT] c
      on rr.[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];