Memberships fact table
This table lists the fact table columns used for the memberships data model.
bi_dimusermembership
Field | Field Type | Description | PrimaryKey | MappedDimensionTable DimensionTableField | |
|---|---|---|---|---|---|
cancellationreasonfk | bigint | unique identifier for cancellation reason | |||
centerid | varchar(36) | uniquq identifier for center | bi_dimcenter centerid | ||
dimusermembershipid | numeric(19) | unique identifier for the row. | |||
dimusermembershipwid | bigint | unique identifier for the row. Primary key | YES | ||
enddate | date | The end date of the time period corresponding to the "membership status". | |||
etlcreatedby | integer | ETL system admin user who inserted or updated the corresponding record in this table. | |||
etllastupdatedby | varchar(50) | ETL system admin user who inserted or updated the corresponding record in this table. | |||
etllastupdatedon | timestamp | Latest timestamp at which the corresponding record was either inserted or updated in this table. | |||
hassetupfee | boolean | Indicates whether the membership version has a Setup Membership associated with it. - True: Setup membership associated - False: Setup membership not associated | |||
invoiceitemid | varchar(36) | The Invoice item id of the membership. If the mmembership type is a 'Setup Membership', this field always points to the Main Membership that the Setup is associated to. tm.membershiptype = 1 then 'Recurring' when tm.membershiptype = 2 then 'Setup Membership' when tm.membershiptype = 3 then 'Annual Membership' else 'Non-Recurring' end as MembershiptypeDisplayValue | |||
isautorenew | varchar(10) | Indicates whether the membership is set to auto renew for the guest. - True: Is set to auto renew - False: Is not set to auto renew | |||
iscurrentrecord | boolean | DO NOT USE- THIS FIELD IS BEING DEPRECATED | |||
issetupfee | boolean | DO NOT USE- THIS FIELD IS BEING DEPRECATED | |||
membershipenddate | date | The expiry date of the membership for the user, irrespective of the 'membership status' for that row. This remains constant throughout the membership lifecycle. Even if the membership gets cancelled before the expiry date, this remains constant. | |||
membershipid | varchar(36) | A unique identifier for the membership.This maps to the ID in the URL in the Membership configuration page of the core system. If the mmembership type is a 'Setup Membership', this field always points to the Main Membership that the Setup is associated to. | |||
membershipstartdate | date | The initial start date of the membership for the user, irrespective of the 'membership status' for that row. If a membership involves setup, this is the date on which the set up membership started. This remains constant throughout the membership lifecycle. Even if the membership gets cancelled before the expiry date, this remains constant. | |||
membershipversionid | varchar(36) | A unique identifier for every version of a membership.This maps to the version ID in the URL in the Membership configuration page of the core system | |||
organizationid | varchar(36) | unique identifier for the organization | bi_dimcenter organizationid | ||
podid | integer | Internal to the ETL, says from which POD data is being synced the table | |||
podname | varchar(20) | Internal to the ETL, says from which POD data is being synced the table | |||
recurringcancelleddate | date | The date from which the recurring payment for a membership is no longer carried out Note: This date doesn't signify the cancellation of membership. It signifies the date of cancellation of payment | |||
setupusermembershipid | varchar(36) | The usermembershipid of the Setup Membership | |||
startdate | date | The start date of the time period corresponding to the "membership status". | |||
status | smallint | Status of the membership between "membership start date" and "membership end date". Possible values: - Active - Frozen - Suspended - Expired with prepaids remaining - Upgrade (when the current membership is opted for an upgrade) - Downgrade (when the current membership is opted for an downgrade) - Upgraded (when the opted upgrade has actually started) - Downgraded (when the opted downgrade has actually started) - Not Applicable (when there are multiple status changes on the same day. These are not considered valid statuses when that happens | |||
terminationreason | integer | The reason for the termination of a membership. Possible values: - Expired - Expired with prepaids remaining - Suspended - Cancelled | |||
upgradedowngradefrom | varchar(36) | The source usermembershipid for the current membership. Which membership did the current membership get upgraded/ downgraded from | |||
upgradedowngradeinto | varchar(36) | The target usermembershipid for the current membership. Which membership is the current membership getting upgraded/ downgraded to | |||
userid | varchar(36) | A unique identifier for the guest. This is a non-blank value that gets populated for every guest | bi_dimuser userid | ||
usermembershipid | varchar(36) | A unique identifier for every membership sold. If the mmembership type is a 'Setup Membership', this field always points to the Main Membership that the Setup is associated to | |||
void | boolean | Indicates if the row should be inserted or deleted. 1- delete, 0-insert | |||