v_FACT_INSTALLMENT

The installment fact table contains information about pledge, matching gift claim, and planned giving installments.

Fields

Field	Field Type	Null	Description
INSTALLMENTFACTID	int		BBDW.[FACT_INSTALLMENT].[INSTALLMENTFACTID]
INSTALLMENTDIMID	int	yes	BBDW.[FACT_INSTALLMENT].[INSTALLMENTDIMID]
REVENUESCHEDULEDIMID	int	yes	BBDW.[FACT_INSTALLMENT].[REVENUESCHEDULEDIMID]
REVENUESCHEDULESTARTDATEDIMID	int	yes	BBDW.[FACT_INSTALLMENT].[REVENUESCHEDULESTARTDATEDIMID]
REVENUESCHEDULEENDDATEDIMID	int	yes	BBDW.[FACT_INSTALLMENT].[REVENUESCHEDULEENDDATEDIMID]
INSTALLMENTDATEDIMID	int	yes	BBDW.[FACT_INSTALLMENT].[INSTALLMENTDATEDIMID]
INSTALLMENTAMOUNT	money	yes	BBDW.[FACT_INSTALLMENT].[INSTALLMENTAMOUNT]
BASEINSTALLMENTAMOUNT	money	yes	BBDW.[FACT_INSTALLMENT].[BASEINSTALLMENTAMOUNT]
BASECURRENCYDIMID	int	yes	BBDW.[FACT_INSTALLMENT].[BASECURRENCYDIMID]
INSTALLMENTSTATUSDIMID	int		Calculation that determines when the installment will occur relative to the current date. 0 = Past Due, 1 = Up to 1 year, 2 = 1 - 5 years, 3 = 5+ years.
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]
CONSTITUENTDIMID	int	yes	BBDW.[FACT_REVENUE].[CONSTITUENTDIMID]
CONSTITUENTSYSTEMID	uniqueidentifier	yes	BBDW.[FACT_REVENUE].[CONSTITUENTSYSTEMID]
MARKETINGSEGMENTDIMID	int	yes	BBDW.[FACT_REVENUE].[MARKETINGSEGMENTDIMID]
MARKETINGSOURCECODEDIMID	int	yes	BBDW.[FACT_REVENUE].[MARKETINGSOURCECODEDIMID]
MARKETINGCONSTITUENTFACTID	bigint	yes	BBDW.[FACT_REVENUE].[MARKETINGCONSTITUENTFACTID]
GLREVENUECATEGORYDIMID	int	yes	BBDW.[FACT_REVENUE].[GLREVENUECATEGORYDIMID]
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]
SEGMENTATIONDATEDIMID	int	yes	BBDW.[DIM_MARKETINGSEGMENT].[SEGMENTATIONDATEDIMID]
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 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+.
SOURCEREVENUESEQUENCEID	int	yes	[BBDW].[FACT_INSTALLMNET].[SOURCEREVENUESEQUENCEID]
FINANCIALTRANSACTIONFACTID	int	yes	BBDW.[FACT_INSTALLMENT].[FINANCIALTRANSACTIONFACTID]
FINANCIALTRANSACTIONLINEITEMFACTID	int	yes	BBDW.[FACT_INSTALLMENT].[FINANCIALTRANSACTIONLINEITEMFACTID]
INSTALLMENTBALANCE	money	yes	BBDW.[FACT_INSTALLMENT].[INSTALLMENTBALANCE]

Definition

Copy


CREATE view [BBDW].[v_FACT_INSTALLMENT] as
  select 
    i.[INSTALLMENTFACTID], 
    i.[INSTALLMENTDIMID], 
    i.[REVENUESCHEDULEDIMID], 
    i.[REVENUESCHEDULESTARTDATEDIMID], 
    i.[REVENUESCHEDULEENDDATEDIMID], 
    i.[INSTALLMENTDATEDIMID], 
    i.[INSTALLMENTAMOUNT], 
    i.[BASEINSTALLMENTAMOUNT], 
    i.[BASECURRENCYDIMID], 
    case
      when i.[INSTALLMENTDATE] < d.[ACTUALDATE] then 0 
      when dateadd(yy, -1, d.[ACTUALDATE]) < current_timestamp then 1 
      when dateadd(yy, -5, d.[ACTUALDATE]) < current_timestamp then 2 
      else 3 
    end as [INSTALLMENTSTATUSDIMID], 
    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.[CONSTITUENTDIMID], 
    r.[CONSTITUENTSYSTEMID], 
    r.[MARKETINGSEGMENTDIMID], 
    r.[MARKETINGSOURCECODEDIMID], 
    r.[MARKETINGCONSTITUENTFACTID], 
    r.[GLREVENUECATEGORYDIMID], 
    c.[CONSTITUENTINFORMATIONDIMID], 
    c.[AGE] as [CONSTITUENTAGE], 
    c.[CONSTITUENTADDRESSDIMID], 
    c.[CONSTITUENTADDRESSDETAILDIMID],
    c.[CONSTITUENTADDRESSFLAGDIMID],
    c.[CONSTITUENTADDRESSTYPEDIMID],   
    c.[CONSTITUENTDEMOGRAPHICDIMID], 
    ms.[SEGMENTATIONDATEDIMID], 
    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],
    i.[SOURCEREVENUESEQUENCEID] as [SOURCEREVENUESEQUENCEID],
    i.[FINANCIALTRANSACTIONFACTID],
    i.[FINANCIALTRANSACTIONLINEITEMFACTID],
    i.[INSTALLMENTBALANCE]
  from 
    BBDW.[FACT_INSTALLMENT] i
    inner join BBDW.[DIM_DATE] d on d.[ISCURRENTDATE] = 1
    left join BBDW.[FACT_FINANCIALTRANSACTIONLINEITEM] r
      on i.[REVENUEFACTID] = r.[FINANCIALTRANSACTIONLINEITEMFACTID]
    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];