v_FACT_INTERACTION

The interaction fact table contains information about constituent interactions.

Fields

Field	Field Type	Null	Description
INTERACTIONFACTID	int		BBDW.[FACT_INTERACTION].[INTERACTIONFACTID]
INTERACTIONLOOKUPID	nvarchar(100)	yes	BBDW.[FACT_INTERACTION].[INTERACTIONLOOKUPID]
INTERACTIONSEQUENCE	int	yes	BBDW.[FACT_INTERACTION].[INTERACTIONSEQUENCE]
INTERACTIONOBJECTIVE	nvarchar(100)	yes	BBDW.[FACT_INTERACTION].[INTERACTIONOBJECTIVE]
INTERACTIONCOMMENT	nvarchar(4000)	yes	BBDW.[FACT_INTERACTION].[INTERACTIONCOMMENT]
ISFIRSTINTERACTION	bit	yes	BBDW.[FACT_INTERACTION].[ISFIRSTINTERACTION]
ISLASTINTERACTION	bit	yes	BBDW.[FACT_INTERACTION].[ISLASTINTERACTION]
CONSTITUENTDIMID	int	yes	BBDW.[FACT_INTERACTION].[CONSTITUENTDIMID]
CONSTITUENTSYSTEMID	uniqueidentifier	yes	BBDW.[FACT_INTERACTION].[CONSTITUENTSYSTEMID]
FUNDRAISERDIMID	int	yes	BBDW.[FACT_INTERACTION].[FUNDRAISERDIMID]
FUNDRAISERCONSTITUENTDIMID	int	yes	BBDW.[FACT_INTERACTION].[FUNDRAISERCONSTITUENTDIMID]
INTERACTIONDATEDIMID	int	yes	BBDW.[FACT_INTERACTION].[INTERACTIONDATEDIMID]
INTERACTIONDATE	datetime	yes	BBDW.[FACT_INTERACTION].[INTERACTIONDATE]
INTERACTIONSTARTTIMEDIMID	int	yes	BBDW.[FACT_INTERACTION].[INTERACTIONSTARTTIMEDIMID]
INTERACTIONSTARTTIME	UDT_HOURMINUTE	yes	BBDW.[FACT_INTERACTION].[INTERACTIONSTARTTIME]
INTERACTIONENDTIMEDIMID	int	yes	BBDW.[FACT_INTERACTION].[INTERACTIONENDTIMEDIMID]
INTERACTIONENDTIME	UDT_HOURMINUTE	yes	BBDW.[FACT_INTERACTION].[INTERACTIONENDTIME]
INTERACTIONDIMID	int	yes	BBDW.[FACT_INTERACTION].[INTERACTIONDIMID]
ACTUALDATEDIMID	int	yes	BBDW.[FACT_INTERACTION].[ACTUALDATEDIMID]
ACTUALDATE	datetime	yes	BBDW.[FACT_INTERACTION].[ACTUALDATE]
ACTUALSTARTTIMEDIMID	int	yes	BBDW.[FACT_INTERACTION].[ACTUALSTARTTIMEDIMID]
ACTUALSTARTTIME	UDT_HOURMINUTE	yes	BBDW.[FACT_INTERACTION].[ACTUALSTARTTIME]
ACTUALENDTIMEDIMID	int	yes	BBDW.[FACT_INTERACTION].[ACTUALENDTIMEDIMID]
ACTUALENDTIME	UDT_HOURMINUTE	yes	BBDW.[FACT_INTERACTION].[ACTUALENDTIME]
EXPECTEDDATEDIMID	int	yes	BBDW.[FACT_INTERACTION].[EXPECTEDDATEDIMID]
EXPECTEDDATE	datetime	yes	BBDW.[FACT_INTERACTION].[EXPECTEDDATE]
EXPECTEDSTARTTIMEDIMID	int	yes	BBDW.[FACT_INTERACTION].[EXPECTEDSTARTTIMEDIMID]
EXPECTEDSTARTTIME	UDT_HOURMINUTE	yes	BBDW.[FACT_INTERACTION].[EXPECTEDSTARTTIME]
EXPECTEDENDTIMEDIMID	int	yes	BBDW.[FACT_INTERACTION].[EXPECTEDENDTIMEDIMID]
EXPECTEDENDTIME	UDT_HOURMINUTE	yes	BBDW.[FACT_INTERACTION].[EXPECTEDENDTIME]
ADDEDDATEDIMID	int	yes	BBDW.[FACT_INTERACTION].[ADDEDDATEDIMID]
ADDEDDATE	datetime	yes	BBDW.[FACT_INTERACTION].[ADDEDDATE]
CHANGEDDATEDIMID	int	yes	BBDW.[FACT_INTERACTION].[CHANGEDDATEDIMID]
CHANGEDDATE	datetime	yes	BBDW.[FACT_INTERACTION].[CHANGEDDATE]
TIMEZONEDIMID	int	yes	BBDW.[FACT_INTERACTION].[TIMEZONEDIMID]
EVENTDIMID	int	yes	BBDW.[FACT_INTERACTION].[EVENTDIMID]
EVENTENDDATEDIMID	int	yes	BBDW.[DIM_EVENT].[EVENTENDDATEDIMID]
EVENTSTARTDATEDIMID	int	yes	BBDW.[DIM_EVENT].[EVENTSTARTDATEDIMID]
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]

Definition

Copy


CREATE view [BBDW].[v_FACT_INTERACTION] as
  select
    i.[INTERACTIONFACTID], 
    i.[INTERACTIONLOOKUPID],
    i.[INTERACTIONSEQUENCE],
    i.[INTERACTIONOBJECTIVE],
    i.[INTERACTIONCOMMENT],
    i.[ISFIRSTINTERACTION],
    i.[ISLASTINTERACTION],
    i.[CONSTITUENTDIMID], 
    i.[CONSTITUENTSYSTEMID], 
    i.[FUNDRAISERDIMID], 
    i.[FUNDRAISERCONSTITUENTDIMID],
    i.[INTERACTIONDATEDIMID], 
    i.[INTERACTIONDATE], 
    i.[INTERACTIONSTARTTIMEDIMID],
    i.[INTERACTIONSTARTTIME],
    i.[INTERACTIONENDTIMEDIMID],
    i.[INTERACTIONENDTIME],
    i.[INTERACTIONDIMID],
    i.[ACTUALDATEDIMID],
    i.[ACTUALDATE],
    i.[ACTUALSTARTTIMEDIMID],
    i.[ACTUALSTARTTIME],
    i.[ACTUALENDTIMEDIMID],
    i.[ACTUALENDTIME],
    i.[EXPECTEDDATEDIMID],
    i.[EXPECTEDDATE],
    i.[EXPECTEDSTARTTIMEDIMID],
    i.[EXPECTEDSTARTTIME],
    i.[EXPECTEDENDTIMEDIMID],
    i.[EXPECTEDENDTIME],
    i.[ADDEDDATEDIMID],
    i.[ADDEDDATE],
    i.[CHANGEDDATEDIMID],
    i.[CHANGEDDATE],
    i.[TIMEZONEDIMID], 
    i.[EVENTDIMID], 
    e.[EVENTENDDATEDIMID], 
    e.[EVENTSTARTDATEDIMID], 
    c.[AGE] as [CONSTITUENTAGE], 
    c.[CONSTITUENTINFORMATIONDIMID], 
    c.[CONSTITUENTADDRESSDIMID], 
    c.[CONSTITUENTADDRESSDETAILDIMID],
    c.[CONSTITUENTADDRESSFLAGDIMID],
    c.[CONSTITUENTADDRESSTYPEDIMID],    
    c.[CONSTITUENTDEMOGRAPHICDIMID]
  from     
    BBDW.[FACT_INTERACTION] i
    left join BBDW.[DIM_CONSTITUENT] c
      on i.[CONSTITUENTDIMID] = c.[CONSTITUENTDIMID]
    left join BBDW.[DIM_EVENT] e
      on i.[EVENTDIMID] = e.[EVENTDIMID];