v_FACT_MEMBERSHIPREVENUE

Contains information about revenue associated with memberships.

Fields

Field	Field Type	Null	Description
MEMBERSHIPREVENUEFACTID	int		BBDW.[FACT_MEMBERSHIPREVENUE].[MEMBERSHIPREVENUEFACTID]
MEMBERSHIPREVENUESYSTEMID	uniqueidentifier	yes	BBDW.[FACT_MEMBERSHIPREVENUE].[MEMBERSHIPREVENUESYSTEMID]
REVENUEFACTID	int	yes	BBDW.[FACT_MEMBERSHIPREVENUE].[REVENUEFACTID]
ORIGINALMEMBERSHIPTRANSACTIONFACTID	int	yes	BBDW.[FACT_MEMBERSHIPREVENUE].[ORIGINALMEMBERSHIPTRANSACTIONFACTID]
MEMBERSHIPTRANSACTIONFACTID	int	yes	BBDW.[FACT_MEMBERSHIPREVENUE].[CURRENTMEMBERSHIPTRANSACTIONFACTID]
MEMBERSHIPPROGRAMDIMID	int	yes	case when BBDW.[FACT_MEMBERSHIPREVENUE].[CURRENTMEMBERSHIPTRANSACTIONFACTID] = 0 then BBDW.[FACT_MEMBERSHIPREVENUE].[MEMBERSHIPPROGRAMDIMID] else BBDW.[FACT_MEMBERSHIPTRANSACTION].[MEMBERSHIPPROGRAMDIMID] end
MEMBERSHIPACTIONDIMID	int		BBDW.[FACT_MEMBERSHIPTRANSACTION].[MEMBERSHIPACTIONDIMID]
MEMBERSHIPTRANSACTIONDATEDIMID	int		BBDW.[FACT_MEMBERSHIPTRANSACTION].[MEMBERSHIPTRANSACTIONDATEDIMID]
TRANSACTIONMEMBERSHIPEXPIRATIONDATEDIMID	int		BBDW.[FACT_MEMBERSHIPTRANSACTION].[EXPIRATIONDATEDIMID]
PREVIOUSMEMBERSHIPPROGRAMDIMID	int		BBDW.[FACT_MEMBERSHIPTRANSACTION].[PREVIOUSMEMBERSHIPPROGRAMDIMID]
MEMBERSHIPSTATUSDIMID	int		BBDW.[FACT_MEMBERSHIPTRANSACTION].[MEMBERSHIPSTATUSDIMID]
MEMBERSHIPDIMID	int		BBDW.[FACT_MEMBERSHIPTRANSACTION].[MEMBERSHIPDIMID]
MEMBERSHIPSYSTEMID	uniqueidentifier		BBDW.[FACT_MEMBERSHIPTRANSACTION].[MEMBERSHIPSYSTEMID]
MEMBERSHIPJOINDATEDIMID	int		BBDW.[DIM_MEMBERSHIP].[JOINDATEDIMID]
MEMBERSHIPLASTRENEWEDONDATEDIMID	int		BBDW.[DIM_MEMBERSHIP].[LASTRENEWEDONDATEDIMID]
CURRENTMEMBERSHIPEXPIRATIONDATEDIMID	int		BBDW.[DIM_MEMBERSHIP].[EXPIRATIONDATEDIMID]
CURRENTMEMBERSHIPPROGRAMDIMID	int		BBDW.[DIM_MEMBERSHIP].[MEMBERSHIPPROGRAMDIMID]
CURRENTMEMBERSHIPSTATUSDIMID	int		BBDW.[DIM_MEMBERSHIP].[MEMBERSHIPSTATUSDIMID]
FINANCIALTRANSACTIONFACTID	int	yes	BBDW.[FACT_MEMBERSHIPREVENUE].[FINANCIALTRANSACTIONFACTID]
FINANCIALTRANSACTIONLINEITEMFACTID	int	yes	BBDW.[FACT_MEMBERSHIPREVENUE].[FINANCIALTRANSACTIONLINEITEMFACTID]

Definition

Copy


      CREATE view [BBDW].[v_FACT_MEMBERSHIPREVENUE] as
        select
        mr.[MEMBERSHIPREVENUEFACTID],
        mr.[MEMBERSHIPREVENUESYSTEMID],
        mr.[REVENUEFACTID],
        mr.[ORIGINALMEMBERSHIPTRANSACTIONFACTID],
        mr.[CURRENTMEMBERSHIPTRANSACTIONFACTID] [MEMBERSHIPTRANSACTIONFACTID],
        mr.[MEMBERSHIPPROGRAMDIMID],
        isnull(cur.[MEMBERSHIPACTIONDIMID], 0) [MEMBERSHIPACTIONDIMID],
        isnull(cur.[MEMBERSHIPTRANSACTIONDATEDIMID], 0) [MEMBERSHIPTRANSACTIONDATEDIMID],
        isnull(cur.[MEMBERSHIPEXPIRATIONDATEDIMID], 0) [TRANSACTIONMEMBERSHIPEXPIRATIONDATEDIMID],
        isnull(cur.[PREVIOUSMEMBERSHIPPROGRAMDIMID], 0) [PREVIOUSMEMBERSHIPPROGRAMDIMID],
        isnull(cur.[MEMBERSHIPSTATUSDIMID], 0) [MEMBERSHIPSTATUSDIMID],
        isnull(cur.[MEMBERSHIPDIMID], 0) [MEMBERSHIPDIMID],
        isnull(cur.[MEMBERSHIPSYSTEMID], '00000000-0000-0000-0000-000000000000') [MEMBERSHIPSYSTEMID],
        isnull(m.[JOINDATEDIMID], 0) [MEMBERSHIPJOINDATEDIMID],
        isnull(m.[LASTRENEWEDONDATEDIMID], 0) [MEMBERSHIPLASTRENEWEDONDATEDIMID],
        isnull(m.[EXPIRATIONDATEDIMID], 0) [CURRENTMEMBERSHIPEXPIRATIONDATEDIMID],
        isnull(m.[MEMBERSHIPPROGRAMDIMID], 0) [CURRENTMEMBERSHIPPROGRAMDIMID],
        isnull(m.[MEMBERSHIPSTATUSDIMID], 0) [CURRENTMEMBERSHIPSTATUSDIMID],
        mr.[FINANCIALTRANSACTIONFACTID],
        mr.[FINANCIALTRANSACTIONLINEITEMFACTID]
        from bbdw.[FACT_MEMBERSHIPREVENUE] mr
        left join bbdw.[FACT_MEMBERSHIPTRANSACTION] ori on mr.[ORIGINALMEMBERSHIPTRANSACTIONFACTID] = ori.[MEMBERSHIPTRANSACTIONFACTID]
        left join bbdw.[FACT_MEMBERSHIPTRANSACTION] cur on mr.[CURRENTMEMBERSHIPTRANSACTIONFACTID] = cur.[MEMBERSHIPTRANSACTIONFACTID]
        left join bbdw.[DIM_MEMBERSHIP] m on cur.[MEMBERSHIPDIMID] = m.[MEMBERSHIPDIMID]