v_FACT_MEMBERSHIPTRANSACTION

The membership transaction fact table contains information about transactions for a membership.

Fields

Field	Field Type	Null	Description
MEMBERSHIPTRANSACTIONFACTID	int		BBDW.[FACT_MEMBERSHIPTRANSACTION].[MEMBERSHIPTRANSACTIONFACTID]
MEMBERSHIPDIMID	int	yes	BBDW.[FACT_MEMBERSHIPTRANSACTION].[MEMBERSHIPDIMID]
MEMBERSHIPSYSTEMID	uniqueidentifier	yes	BBDW.[FACT_MEMBERSHIPTRANSACTION].[MEMBERSHIPSYSTEMID]
MEMBERSHIPPROGRAMDIMID	int	yes	BBDW.[FACT_MEMBERSHIPTRANSACTION].[MEMBERSHIPPROGRAMDIMID]
MEMBERSHIPACTIONDIMID	int	yes	BBDW.[FACT_MEMBERSHIPTRANSACTION].[MEMBERSHIPACTIONDIMID]
MEMBERSHIPTRANSACTIONDATEDIMID	int	yes	BBDW.[FACT_MEMBERSHIPTRANSACTION].[MEMBERSHIPTRANSACTIONDATEDIMID]
NEXTMEMBERSHIPTRANSACTIONDATEDIMID	int	yes	BBDW.[FACT_MEMBERSHIPTRANSACTION].[NEXTMEMBERSHIPTRANSACTIONDATEDIMID]
TRANSACTIONMEMBERSHIPEXPIRATIONDATEDIMID	int	yes	BBDW.[FACT_MEMBERSHIPTRANSACTION].[EXPIRATIONDATEDIMID]
PREVIOUSMEMBERSHIPPROGRAMDIMID	int	yes	BBDW.[FACT_MEMBERSHIPTRANSACTION].[PREVIOUSMEMBERSHIPPROGRAMDIMID]
MEMBERSHIPSTATUSDIMID	int	yes	BBDW.[FACT_MEMBERSHIPTRANSACTION].[MEMBERSHIPSTATUSDIMID]
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]
ISGIFTMEMBERSHIP	bit	yes	BBDW.[FACT_MEMBERSHIPTRANSACTION].[ISGIFTMEMBERSHIP]
GIVENBYCONSTITUENTDIMID	int	yes	BBDW.[FACT_MEMBERSHIPTRANSACTION].[GIVENBYCONSTITUENTDIMID]
GIVENBYCONSTITUENTSYSTEMID	uniqueidentifier	yes	BBDW.[FACT_MEMBERSHIPTRANSACTION].[GIVENBYCONSTITUENTSYSTEMID]
CONSTITUENTDIMID	int	yes	BBDW.[FACT_MEMBER].[CONSTITUENTDIMID]
CONSTITUENTSYSTEMID	uniqueidentifier	yes	BBDW.[FACT_MEMBER].[CONSTITUENTSYSTEMID]
CONSTITUENTINFORMATIONDIMID	int	yes	BBDW.[v_FACT_MEMBER].[CONSTITUENTINFORMATIONDIMID] when BBDW.[v_FACT_MEMBER].[MEMBERISPRIMARY] = 1
CONSTITUENTAGE	int	yes	BBDW.[v_FACT_MEMBER].[CONSTITUENTAGE] when BBDW.[v_FACT_MEMBER].[MEMBERISPRIMARY] = 1
CONSTITUENTADDRESSDIMID	int	yes	BBDW.[v_FACT_MEMBER].[CONSTITUENTADDRESSDIMID] when BBDW.[v_FACT_MEMBER].[MEMBERISPRIMARY] = 1
CONSTITUENTADDRESSDETAILDIMID	int	yes	BBDW.[v_FACT_MEMBER].[CONSTITUENTADDRESSDETAILDIMID] when BBDW.[v_FACT_MEMBER].[MEMBERISPRIMARY] = 1
CONSTITUENTADDRESSFLAGDIMID	int	yes	BBDW.[v_FACT_MEMBER].[CONSTITUENTADDRESSFLAGDIMID] when BBDW.[v_FACT_MEMBER].[MEMBERISPRIMARY] = 1
CONSTITUENTADDRESSTYPEDIMID	int	yes	BBDW.[v_FACT_MEMBER].[CONSTITUENTADDRESSTYPEDIMID] when BBDW.[v_FACT_MEMBER].[MEMBERISPRIMARY] = 1
CONSTITUENTDEMOGRAPHICDIMID	int	yes	BBDW.[v_FACT_MEMBER].[CONSTITUENTDEMOGRAPHICDIMID] when BBDW.[v_FACT_MEMBER].[MEMBERISPRIMARY] = 1
FINANCIALTRANSACTIONFACTID	int	yes	BBDW.[FACT_MEMBERSHIPTRANSACTION].[FINANCIALTRANSACTIONFACTID]
FINANCIALTRANSACTIONLINEITEMFACTID	int	yes	BBDW.[FACT_MEMBERSHIPTRANSACTION].[FINANCIALTRANSACTIONLINEITEMFACTID]

Definition

Copy


      CREATE view [BBDW].[v_FACT_MEMBERSHIPTRANSACTION] as
      select 
        mt.[MEMBERSHIPTRANSACTIONFACTID], 
        mt.[MEMBERSHIPDIMID], 
        mt.[MEMBERSHIPSYSTEMID],
        mt.[MEMBERSHIPPROGRAMDIMID], 
        mt.[MEMBERSHIPACTIONDIMID], 
        mt.[MEMBERSHIPTRANSACTIONDATEDIMID], 
        mt.[NEXTMEMBERSHIPTRANSACTIONDATEDIMID],
        mt.[MEMBERSHIPEXPIRATIONDATEDIMID] [TRANSACTIONMEMBERSHIPEXPIRATIONDATEDIMID],  
        mt.[PREVIOUSMEMBERSHIPPROGRAMDIMID], 
        mt.[MEMBERSHIPSTATUSDIMID],     
        dm.[JOINDATEDIMID] [MEMBERSHIPJOINDATEDIMID], 
        dm.[LASTRENEWEDONDATEDIMID] [MEMBERSHIPLASTRENEWEDONDATEDIMID], 
        dm.[EXPIRATIONDATEDIMID] [CURRENTMEMBERSHIPEXPIRATIONDATEDIMID], 
        dm.[MEMBERSHIPPROGRAMDIMID] [CURRENTMEMBERSHIPPROGRAMDIMID],
        dm.[MEMBERSHIPSTATUSDIMID] [CURRENTMEMBERSHIPSTATUSDIMID],    
        mt.[ISGIFTMEMBERSHIP],  
        mt.[GIVENBYCONSTITUENTDIMID],  
        mt.[GIVENBYCONSTITUENTSYSTEMID],    
        m.[CONSTITUENTDIMID],
        m.[CONSTITUENTSYSTEMID],
        m.[CONSTITUENTINFORMATIONDIMID],
        m.[CONSTITUENTAGE],
        m.[CONSTITUENTADDRESSDIMID],
        m.[CONSTITUENTADDRESSDETAILDIMID],
        m.[CONSTITUENTADDRESSFLAGDIMID],
        m.[CONSTITUENTADDRESSTYPEDIMID],
        m.[CONSTITUENTDEMOGRAPHICDIMID],
        mt.[FINANCIALTRANSACTIONFACTID],
        mt.[FINANCIALTRANSACTIONLINEITEMFACTID]
      from
        BBDW.[FACT_MEMBERSHIPTRANSACTION] mt
        left join BBDW.[DIM_MEMBERSHIP] dm on mt.[MEMBERSHIPDIMID] = dm.[MEMBERSHIPDIMID]
        left join BBDW.[DIM_MEMBERSHIPACTION] ma on mt.[MEMBERSHIPACTIONDIMID] = ma.[MEMBERSHIPACTIONDIMID] 
        left join BBDW.[DIM_MEMBERSHIPSTATUS] ms ON mt.[MEMBERSHIPSTATUSDIMID] = ms.[MEMBERSHIPSTATUSDIMID]
        left join BBDW.[v_FACT_MEMBER] m on mt.[MEMBERSHIPDIMID] = m.[MEMBERSHIPDIMID] and m.[MEMBERISPRIMARY] = 1;