v_FACT_MEMBERSHIPADDON

Contains information about add-ons associated with memberships.

Fields

Field	Field Type	Null	Description
MEMBERSHIPADDONFACTID	int		BBDW.[FACT_MEMBERSHIPADDON].[MEMBERSHIPADDONFACTID]
MEMBERSHIPDIMID	int	yes	BBDW.[FACT_MEMBERSHIPADDON].[MEMBERSHIPDIMID]
MEMBERSHIPSYSTEMID	uniqueidentifier	yes	BBDW.[FACT_MEMBERSHIPADDON].[MEMBERSHIPSYSTEMID]
ADDONDIMID	int	yes	BBDW.[FACT_MEMBERSHIPADDON].[ADDONDIMID]
QUANTITY	int	yes	BBDW.[FACT_MEMBERSHIPADDON].[QUANTITY]
PURCHASEPRICE	money	yes	BBDW.[FACT_MEMBERSHIPADDON].[PURCHASEPRICE]
TOTALPURCHASEPRCE	money		BBDW.[FACT_REVENUE].[REVENUEAPPLICATIONAMOUNT]
BASECURRENCYDIMID	int	yes	BBDW.[FACT_MEMBERSHIPADDON].[BASECURRENCYDIMID]
BASEPURCHASEPRICE	money	yes	BBDW.[FACT_MEMBERSHIPADDON].[BASEPURCHASEPRICE]
BASETOTALPURCHASEPRCE	money		BBDW.[FACT_REVENUE].[BASEREVENUEAPPLICATIONAMOUNT]
MEMBERSHIPTRANSACTIONFACTID	int	yes	BBDW.[FACT_MEMBERSHIPADDON].[MEMBERSHIPTRANSACTIONFACTID]
MEMBERSHIPPROGRAMDIMID	int	yes	BBDW.[FACT_MEMBERSHIPTRANSACTION].[MEMBERSHIPPROGRAMDIMID]
MEMBERSHIPACTIONDIMID	int	yes	BBDW.[FACT_MEMBERSHIPTRANSACTION].[MEMBERSHIPACTIONDIMID]
MEMBERSHIPSTATUSDIMID	int	yes	BBDW.[FACT_MEMBERSHIPTRANSACTION].[MEMBERSHIPSTATUSDIMID]
MEMBERSHIPTRANSACTIONDATEDIMID	int	yes	BBDW.[FACT_MEMBERSHIPTRANSACTION].[MEMBERSHIPTRANSACTIONDATEDIMID]
PREVIOUSMEMBERSHIPPROGRAMDIMID	int	yes	BBDW.[FACT_MEMBERSHIPTRANSACTION].[PREVIOUSMEMBERSHIPPROGRAMDIMID]
NEXTMEMBERSHIPTRANSACTIONDATEDIMID	int	yes	BBDW.[FACT_MEMBERSHIPTRANSACTION].[NEXTMEMBERSHIPTRANSACTIONDATEDIMID]
TRANSACTIONMEMBERSHIPEXPIRATIONDATEDIMID	int	yes	BBDW.[FACT_MEMBERSHIPTRANSACTION].[EXPIRATIONDATEDIMID]
MEMBERSHIPJOINDATEDIMID	int	yes	BBDW.[DIM_MEMBERSHIP].[JOINDATEDIMID]
MEMBERSHIPLASTRENEWEDONDATEDIMID	int	yes	BBDW.[DIM_MEMBERSHIP].[LASTRENEWEDONDATEDIMID]
CURRENTMEMBERSHIPEXPIRATIONDATEDIMID	int	yes	BBDW.[DIM_MEMBERSHIP].[EXPIRATIONDATEDIMID]
CURRENTMEMBERSHIPPROGRAMDIMID	int	yes	BBDW.[DIM_MEMBERSHIP].[MEMBERSHIPPROGRAMDIMID]
CURRENTMEMBERSHIPSTATUSDIMID	int	yes	BBDW.[DIM_MEMBERSHIP].[MEMBERSHIPSTATUSDIMID]
REVENUEFACTID	int	yes	BBDW.[FACT_MEMBERSHIPADDON].[REVENUEFACTID]
REVENUESEQUENCEID	int		BBDW.[FACT_REVENUE].[REVENUESEQUENCEID]
REVENUECODEDIMID	int		BBDW.[FACT_REVENUE].[REVENUECODEDIMID]
REVENUEFLAGDIMID	int		BBDW.[FACT_REVENUE].[REVENUEFLAGDIMID]
REVENUECHANNELDIMID	int		BBDW.[FACT_REVENUE].[REVENUECHANNELDIMID]
REVENUEDATEDIMID	int		BBDW.[FACT_REVENUE].[REVENUEDATEDIMID]
POSTDATEDIMID	int		BBDW.[FACT_REVENUE].[POSTDATEDIMID]
DESIGNATIONDIMID	int		BBDW.[FACT_REVENUE].[DESIGNATIONDIMID]
PURPOSELEVEL1DIMID	int		BBDW.[FACT_REVENUE].[PURPOSELEVEL1DIMID]
PURPOSELEVEL2DIMID	int		BBDW.[FACT_REVENUE].[PURPOSELEVEL2DIMID]
PURPOSELEVEL3DIMID	int		BBDW.[FACT_REVENUE].[PURPOSELEVEL3DIMID]
PURPOSELEVEL4DIMID	int		BBDW.[FACT_REVENUE].[PURPOSELEVEL4DIMID]
PURPOSELEVEL5DIMID	int		BBDW.[FACT_REVENUE].[PURPOSELEVEL5DIMID]
GLREVENUECATEGORYDIMID	int		BBDW.[FACT_REVENUE].[GLREVENUECATEGORYDIMID]
CONSTITUENTDIMID	int		BBDW.[FACT_REVENUE].[CONSTITUENTDIMID]
CONSTITUENTSYSTEMID	uniqueidentifier		BBDW.[FACT_REVENUE].[CONSTITUENTSYSTEMID]
CONSTITUENTINFORMATIONDIMID	int		BBDW.[DIM_CONSTITUENT].[CONSTITUENTINFORMATIONDIMID]
CONSTITUENTAGE	int		BBDW.[DIM_CONSTITUENT].[AGE]
CONSTITUENTADDRESSDIMID	int		BBDW.[DIM_CONSTITUENT].[CONSTITUENTADDRESSDIMID]
CONSTITUENTADDRESSDETAILDIMID	int		BBDW.[DIM_CONSTITUENT].[CONSTITUENTADDRESSDETAILDIMID]
CONSTITUENTADDRESSFLAGDIMID	int		BBDW.[DIM_CONSTITUENT].[CONSTITUENTADDRESSFLAGDIMID]
CONSTITUENTADDRESSTYPEDIMID	int		BBDW.[DIM_CONSTITUENT].[CONSTITUENTADDRESSTYPEDIMID]
CONSTITUENTDEMOGRAPHICDIMID	int		BBDW.[DIM_CONSTITUENT].[CONSTITUENTDEMOGRAPHICDIMID]
MARKETINGSEGMENTDIMID	int		BBDW.[FACT_REVENUE].[MARKETINGSEGMENTDIMID]
SEGMENTATIONDATEDIMID	int		BBDW.[DIM_MARKETINGSEGMENT].[SEGMENTATIONDATEDIMID]
MARKETINGSOURCECODEDIMID	int		BBDW.[FACT_REVENUE].[MARKETINGSOURCECODEDIMID]
MARKETINGCONSTITUENTFACTID	bigint		BBDW.[FACT_REVENUE].[MARKETINGCONSTITUENTFACTID]
APPEALDIMID	int		BBDW.[DIM_APPEAL].[APPEALDIMID]
APPEALSTARTDATEDIMID	int		BBDW.[DIM_APPEAL].[APPEALSTARTDATEDIMID]
APPEALENDDATEDIMID	int		BBDW.[DIM_APPEAL].[APPEALENDDATEDIMID]
APPEALWEEKDIMID	int	yes	The difference in weeks between the appeal start date and the installment date. Anything over 104 weeks is labeled 105+.
MARKETINGRESPONSEWEEKDIMID	int	yes	The difference in weeks between the marketing effort date and the installment date. Anything over 104 weeks is labeled 105+.
FINANCIALTRANSACTIONFACTID	int	yes	BBDW.[FACT_MEMBERSHIPADDON].[FINANCIALTRANSACTIONFACTID]
FINANCIALTRANSACTIONLINEITEMFACTID	int	yes	BBDW.[FACT_MEMBERSHIPADDON].[FINANCIALTRANSACTIONLINEITEMFACTID]

Definition

Copy


      CREATE view [BBDW].[v_FACT_MEMBERSHIPADDON] as
      select
        ma.[MEMBERSHIPADDONFACTID],
        ma.[MEMBERSHIPDIMID],
        ma.[MEMBERSHIPSYSTEMID],
        ma.[ADDONDIMID],
        ma.[QUANTITY],
        ma.[PURCHASEPRICE],
        isnull(r.[FINANCIALTRANSACTIONLINEITEMAMOUNT], 0) [TOTALPURCHASEPRCE],
        ma.[BASECURRENCYDIMID],
        ma.[BASEPURCHASEPRICE], 
        isnull(r.[FINANCIALTRANSACTIONLINEITEMBASEAMOUNT], 0) [BASETOTALPURCHASEPRCE],
        ma.[MEMBERSHIPTRANSACTIONFACTID],
        mt.[MEMBERSHIPPROGRAMDIMID],
        mt.[MEMBERSHIPACTIONDIMID],
        mt.[MEMBERSHIPSTATUSDIMID],
        mt.[MEMBERSHIPTRANSACTIONDATEDIMID],
        mt.[PREVIOUSMEMBERSHIPPROGRAMDIMID],
        mt.[NEXTMEMBERSHIPTRANSACTIONDATEDIMID],
        mt.[MEMBERSHIPEXPIRATIONDATEDIMID] [TRANSACTIONMEMBERSHIPEXPIRATIONDATEDIMID],
        dm.[JOINDATEDIMID] [MEMBERSHIPJOINDATEDIMID], 
        dm.[LASTRENEWEDONDATEDIMID] [MEMBERSHIPLASTRENEWEDONDATEDIMID],  
        dm.[EXPIRATIONDATEDIMID] [CURRENTMEMBERSHIPEXPIRATIONDATEDIMID],
        dm.[MEMBERSHIPPROGRAMDIMID] [CURRENTMEMBERSHIPPROGRAMDIMID],
        dm.[MEMBERSHIPSTATUSDIMID] [CURRENTMEMBERSHIPSTATUSDIMID],  
        ma.[REVENUEFACTID],     
        isnull(r.[FINANCIALTRANSACTIONSEQUENCEID], 0) [REVENUESEQUENCEID], 
        isnull(r.[REVENUECODEDIMID], 0) [REVENUECODEDIMID],
        isnull(r.[REVENUEFLAGDIMID], 0) [REVENUEFLAGDIMID], 
        isnull(r.[REVENUECHANNELDIMID], 0) [REVENUECHANNELDIMID], 
        isnull(r.[FINANCIALTRANSACTIONDATEDIMID], 0) [REVENUEDATEDIMID], 
        isnull(r.[FINANCIALTRANSACTIONPOSTDATEDIMID], 0) [POSTDATEDIMID], 
        isnull(r.[DESIGNATIONDIMID], 0) [DESIGNATIONDIMID], 
        isnull(r.[PURPOSELEVEL1DIMID], 0) [PURPOSELEVEL1DIMID], 
        isnull(r.[PURPOSELEVEL2DIMID], 0) [PURPOSELEVEL2DIMID], 
        isnull(r.[PURPOSELEVEL3DIMID], 0) [PURPOSELEVEL3DIMID], 
        isnull(r.[PURPOSELEVEL4DIMID], 0) [PURPOSELEVEL4DIMID], 
        isnull(r.[PURPOSELEVEL5DIMID], 0) [PURPOSELEVEL5DIMID], 
        isnull(r.[GLREVENUECATEGORYDIMID], 0) [GLREVENUECATEGORYDIMID],          
        isnull(r.[CONSTITUENTDIMID], 0) [CONSTITUENTDIMID], 
        isnull(r.[CONSTITUENTSYSTEMID], '00000000-0000-0000-0000-000000000000') [CONSTITUENTSYSTEMID],       
        isnull(c.[CONSTITUENTINFORMATIONDIMID], 0) [CONSTITUENTINFORMATIONDIMID], 
        isnull(c.[AGE], 0) as [CONSTITUENTAGE], 
        isnull(c.[CONSTITUENTADDRESSDIMID], 0) [CONSTITUENTADDRESSDIMID], 
        isnull(c.[CONSTITUENTADDRESSDETAILDIMID], 0) [CONSTITUENTADDRESSDETAILDIMID],
        isnull(c.[CONSTITUENTADDRESSFLAGDIMID], 0) [CONSTITUENTADDRESSFLAGDIMID],
        isnull(c.[CONSTITUENTADDRESSTYPEDIMID], 0) [CONSTITUENTADDRESSTYPEDIMID],    
        isnull(c.[CONSTITUENTDEMOGRAPHICDIMID], 0) [CONSTITUENTDEMOGRAPHICDIMID],
        isnull(r.[MARKETINGSEGMENTDIMID], 0) [MARKETINGSEGMENTDIMID],     
        isnull(ms.[SEGMENTATIONDATEDIMID], 0) [SEGMENTATIONDATEDIMID], 
        isnull(r.[MARKETINGSOURCECODEDIMID], 0) [MARKETINGSOURCECODEDIMID], 
        isnull(r.[MARKETINGCONSTITUENTFACTID], 0) [MARKETINGCONSTITUENTFACTID],  
        isnull(a.[APPEALDIMID], 0) [APPEALDIMID], 
        isnull(a.[APPEALSTARTDATEDIMID], 0) [APPEALSTARTDATEDIMID], 
        isnull(a.[APPEALENDDATEDIMID], 0) [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],
        ma.[FINANCIALTRANSACTIONFACTID],
        ma.[FINANCIALTRANSACTIONLINEITEMFACTID]
      from
        BBDW.[FACT_MEMBERSHIPADDON] ma
        left join BBDW.[FACT_MEMBERSHIPTRANSACTION] mt
          on ma.[MEMBERSHIPTRANSACTIONFACTID] = mt.[MEMBERSHIPTRANSACTIONFACTID]
        left join BBDW.[DIM_MEMBERSHIP] dm 
          on mt.[MEMBERSHIPDIMID] = dm.[MEMBERSHIPDIMID]
        left join [BBDW].[FACT_FINANCIALTRANSACTIONLINEITEM] r
          on r.[FINANCIALTRANSACTIONLINEITEMFACTID] = ma.[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]