v_FACT_CONSTITUENTAPPEAL

The constituent appeal fact table contains information about constituents in appeals.

Fields

Field Field Type Null Description
CONSTITUENTAPPEALFACTID bigint BBDW.[FACT_CONSTITUENTAPPEAL].[CONSTITUENTAPPEALFACTID]
CONSTITUENTDIMID int yes BBDW.[FACT_CONSTITUENTAPPEAL].[CONSTITUENTDIMID]
CONSTITUENTSYSTEMID uniqueidentifier yes BBDW.[FACT_CONSTITUENTAPPEAL].[CONSTITUENTSYSTEMID]
APPEALDIMID int yes BBDW.[FACT_CONSTITUENTAPPEAL].[APPEALDIMID]
MARKETINGSEGMENTDIMID int yes BBDW.[FACT_CONSTITUENTAPPEAL].[MARKETINGSEGMENTDIMID]
FINDERNUMBER bigint yes BBDW.[FACT_CONSTITUENTAPPEAL].[FINDERNUMBER]
CONSTITUENTAGE int yes BBDW.[DIM_CONSTITUENT].[AGE]
CONSTITUENTINFORMATIONDIMID int yes BBDW.[DIM_CONSTITUENT].[CONSTITUENTINFORMATIONDIMID]
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]
APPEALSTARTDATEDIMID int yes BBDW.[DIM_APPEAL].[APPEALSTARTDATEDIMID]
APPEALENDDATEDIMID int yes BBDW.[DIM_APPEAL].[APPEALENDDATEDIMID]
SEGMENTATIONDATEDIMID int yes BBDW.[DIM_MARKETINGSEGMENT].[SEGMENTATIONDATEDIMID]
MARKETINGCONSTITUENTFACTID bigint yes BBDW.[FACT_MARKETINGCONSTITUENT].[MARKETINGCONSTITUENTFACTID]
MARKETINGSOURCECODEDIMID int BBDW.[FACT_MARKETINGCONSTITUENT].[MARKETINGSOURCECODEDIMID]
MARKETINGSEGMENTATIONDIMID int yes BBDW.[DIM_MARKETINGSEGMENT].[MARKETINGSEGMENTATIONDIMID]

Definition

Copy 

CREATE view [BBDW].[v_FACT_CONSTITUENTAPPEAL] as
  select
    ca.[CONSTITUENTAPPEALFACTID], 
    ca.[CONSTITUENTDIMID], 
    ca.[CONSTITUENTSYSTEMID], 
    ca.[APPEALDIMID], 
    ca.[MARKETINGSEGMENTDIMID], 
    ca.[FINDERNUMBER],
    c.[AGE] as [CONSTITUENTAGE], 
    c.[CONSTITUENTINFORMATIONDIMID], 
    c.[CONSTITUENTADDRESSDIMID], 
    c.[CONSTITUENTADDRESSDETAILDIMID],
    c.[CONSTITUENTADDRESSFLAGDIMID],
    c.[CONSTITUENTADDRESSTYPEDIMID],
    c.[CONSTITUENTDEMOGRAPHICDIMID], 
    a.[APPEALSTARTDATEDIMID], 
    a.[APPEALENDDATEDIMID], 
    ms.[SEGMENTATIONDATEDIMID], 
    mc.[MARKETINGCONSTITUENTFACTID], 
    isnull(mc.[MARKETINGSOURCECODEDIMID], 0) as [MARKETINGSOURCECODEDIMID],
    ca.[MARKETINGSEGMENTATIONDIMID]
  from 
    BBDW.[FACT_CONSTITUENTAPPEAL] ca
    left join BBDW.[DIM_CONSTITUENT] c
      on ca.[CONSTITUENTDIMID] = c.[CONSTITUENTDIMID]
    left join BBDW.[DIM_APPEAL] a
      on ca.[APPEALDIMID] = a.[APPEALDIMID] 
    left join BBDW.[DIM_MARKETINGSEGMENT] ms
      on ca.[MARKETINGSEGMENTDIMID] = ms.[MARKETINGSEGMENTDIMID] 
    left join BBDW.[FACT_MARKETINGCONSTITUENT] mc
      on ca.[CONSTITUENTDIMID] = mc.[CONSTITUENTDIMID] and ca.[MARKETINGSEGMENTDIMID] = mc.[MARKETINGSEGMENTDIMID]