v_FACT_MEMBER

The member fact table contains information about constituent members.

Fields

Field Field Type Null Description
MEMBERFACTID int BBDW.[FACT_MEMBER].[MEMBERFACTID]
CONSTITUENTDIMID int yes BBDW.[FACT_MEMBER].[CONSTITUENTDIMID]
CONSTITUENTSYSTEMID uniqueidentifier yes BBDW.[FACT_MEMBER].[CONSTITUENTSYSTEMID]
MEMBERFLAGDIMID int yes BBDW.[FACT_MEMBER].[MEMBERFLAGDIMID]
MEMBERSHIPDIMID int yes BBDW.[FACT_MEMBER].[MEMBERSHIPDIMID]
MEMBERSHIPSYSTEMID uniqueidentifier yes BBDW.[FACT_MEMBER].[MEMBERSHIPSYSTEMID]
MEMBERSHIPPROGRAMDIMID int yes BBDW.[DIM_MEMBERSHIP].[MEMBERSHIPPROGRAMDIMID]
MEMBERSHIPSTATUSDIMID int yes BBDW.[DIM_MEMBERSHIP].[MEMBERSHIPSTATUSDIMID]
JOINDATEDIMID int yes BBDW.[DIM_MEMBERSHIP].[JOINDATEDIMID]
EXPIRATIONDATEDIMID int yes BBDW.[DIM_MEMBERSHIP].[EXPIRATIONDATEDIMID]
LASTRENEWEDONDATEDIMID int yes BBDW.[DIM_MEMBERSHIP].[LASTRENEWEDONDATEDIMID]
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]
MEMBERISPRIMARY bit yes BBDW.[DIM_MEMBERFLAG].[MEMBERISPRIMARY]

Definition

Copy 

CREATE view [BBDW].[v_FACT_MEMBER] as
  select
    m.[MEMBERFACTID], 
    m.[CONSTITUENTDIMID], 
    m.[CONSTITUENTSYSTEMID], 
    m.[MEMBERFLAGDIMID], 
    m.[MEMBERSHIPDIMID], 
    m.[MEMBERSHIPSYSTEMID],
    ms.[MEMBERSHIPPROGRAMDIMID], 
    ms.[MEMBERSHIPSTATUSDIMID], 
    ms.[JOINDATEDIMID], 
    ms.[EXPIRATIONDATEDIMID], 
    ms.[LASTRENEWEDONDATEDIMID], 
    c.[CONSTITUENTINFORMATIONDIMID], 
    c.[AGE] [CONSTITUENTAGE], 
    c.[CONSTITUENTADDRESSDIMID], 
    c.[CONSTITUENTADDRESSDETAILDIMID],
    c.[CONSTITUENTADDRESSFLAGDIMID],
    c.[CONSTITUENTADDRESSTYPEDIMID],       
    c.[CONSTITUENTDEMOGRAPHICDIMID],
    mf.[MEMBERISPRIMARY]
  from
    BBDW.[FACT_MEMBER] m
    inner join BBDW.[DIM_MEMBERFLAG] mf on m.[MEMBERFLAGDIMID] = mf.[MEMBERFLAGDIMID]
    left join BBDW.[DIM_CONSTITUENT] c on m.[CONSTITUENTDIMID] = c.[CONSTITUENTDIMID] 
    left join BBDW.[DIM_MEMBERSHIP] ms on m.[MEMBERSHIPDIMID] = ms.[MEMBERSHIPDIMID]
  where mf.[MEMBERISDROPPED] = 0;