ZenotiHelp

Membership User Dimension Table

ZTZenoti Docs Team Updated April 10, 2026 4 min readGuide

Dimension table storing denormalized membership-user subscription details including payment information, credit amounts, renewal tracking, and closure status. Populated from membership purchase and lifecycle transactional data.

Columns

Column Name Data Type Nullable Description
usermembershipwid BIGINT IDENTITY No Surrogate warehouse key for the membership user dimension
usermembershipid VARCHAR(36) Yes Unique identifier of the user-membership association
userid VARCHAR(36) Yes Identifier of the guest/user who holds the membership
membershipid VARCHAR(36) Yes Identifier of the membership plan
creationdate TIMESTAMP Yes Timestamp when the user membership record was created
startdate TIMESTAMP Yes Effective start timestamp of the membership
enddate TIMESTAMP Yes Effective end timestamp of the membership
void SMALLINT Yes Soft-delete flag; 1 means the record is logically deleted
creditamount DOUBLE PRECISION Yes Total credit amount available on the membership
invoiceid VARCHAR(36) Yes Identifier of the original sale invoice
transactioninvoiceno VARCHAR(256) Yes Human-readable invoice number of the transaction
subscriptionstatus INTEGER Yes Numeric code representing subscription status
subscriptionsource INTEGER Yes Numeric code representing the source of subscription creation
cancelleddate VARCHAR(256) Yes Date/timestamp when the membership was cancelled (stored as string)
recurring INTEGER Yes Flag indicating whether the membership has recurring billing
cancellationsource VARCHAR(256) Yes Source or channel through which the cancellation was initiated
firstsale SMALLINT Yes Flag indicating whether this is the first sale for the user
closed SMALLINT Yes Flag indicating whether the membership has been closed out
closedby VARCHAR(144) Yes Identifier of the user who closed the membership
closedon TIMESTAMP Yes Timestamp when the membership was closed
closedinvoice VARCHAR(144) Yes Identifier of the invoice associated with the closure
groupusermembershipid VARCHAR(36) Yes Identifier linking group/family memberships together
recurrenceorder INTEGER Yes Order of the recurrence cycle
membershipstatus INTEGER Yes Numeric code representing the overall membership status
acceptedon TIMESTAMP Yes Timestamp when the membership agreement was accepted
acceptedfromip VARCHAR(2048) Yes IP address from which the membership agreement was accepted
cancelledby VARCHAR(144) Yes Identifier of the user who cancelled the membership
noofcyclespaid INTEGER Yes Number of billing cycles paid to date
renewalmembershipid VARCHAR(144) Yes Identifier of the membership plan used for renewal
previoususermembershipid VARCHAR(36) Yes Identifier of the prior user membership (for renewals/upgrades)
childinvoiceid VARCHAR(36) Yes Identifier of a child invoice related to the membership
redeempostexpiry SMALLINT Yes Flag indicating whether credits can be redeemed after expiry
servicecreditamt DOUBLE PRECISION Yes Credit amount allocated for services
productcreditamt DOUBLE PRECISION Yes Credit amount allocated for products
othercreditamt DOUBLE PRECISION Yes Credit amount allocated for other item types
renewmembershipamount DOUBLE PRECISION Yes Amount charged for membership renewal
renewaldiscountappliedby VARCHAR(144) Yes Identifier of the user who applied a renewal discount
renewaldiscountappliedon TIMESTAMP Yes Timestamp when the renewal discount was applied
numvisits INTEGER Yes Number of visits included or tracked for the membership
salecenterid VARCHAR(36) Yes Identifier of the center where the sale occurred
membershipname VARCHAR(512) Yes Display name of the membership plan
code VARCHAR(256) Yes Short code of the membership plan
displayprice VARCHAR(512) Yes Formatted display price of the membership
businessunitid VARCHAR(36) Yes Identifier of the business unit
firstname VARCHAR(1024) Yes First name of the membership holder
lastname VARCHAR(1024) Yes Last name of the membership holder
primarypaymenttype NVARCHAR(800) Yes Payment type of the primary payment method on file
primarypaymentbrand NVARCHAR(256) Yes Brand of the primary payment card (e.g., Visa, MasterCard)
backuppaymenttype NVARCHAR(800) Yes Payment type of the backup payment method on file
backuppaymentbrand NVARCHAR(256) Yes Brand of the backup payment card
cancellationinitiatedon DATETIME Yes Timestamp when the cancellation process was initiated
primarypaymentcardtype NVARCHAR(50) Yes Card type of the primary payment method (e.g., Credit, Debit)
backuppaymentcardtype NVARCHAR(50) Yes Card type of the backup payment method
isprimarycardfromvault BOOLEAN Yes Flag indicating whether the primary card is stored in a payment vault
isbackupcardfromvault BOOLEAN Yes Flag indicating whether the backup card is stored in a payment vault
primaryisscotiasupported BOOLEAN Yes Flag indicating whether the primary card supports Scotia processing
backupisscotiasupported BOOLEAN Yes Flag indicating whether the backup card supports Scotia processing
paymentamount FLOAT Yes Recurring payment amount for the membership
discountamount FLOAT Yes Discount amount applied to the membership
taxamount FLOAT Yes Tax amount on the membership payment
annualfeeamount FLOAT Yes Annual fee amount for the membership
annualfeediscount FLOAT Yes Discount applied to the annual fee
annualfeetaxamount FLOAT Yes Tax amount on the annual fee
graceperioddate TIMESTAMP Yes Date until which the membership grace period extends

Note: All dimension tables also include standard ETL metadata columns (podname, organizationid, groupid, etlLastUpdatedOn, etlLastUpdatedBy) which are omitted from this reference for brevity.

Primary Key

usermembershipwid

Foreign Keys

Column References
userid Bi_DimUser / Bi_DimGuest
membershipid Bi_DimMembership
invoiceid Fact invoice reference
salecenterid Bi_DimCenter
businessunitid Bi_DimBusinessUnit
previoususermembershipid Self-referencing (prior membership)
renewalmembershipid Bi_DimMembership (renewal target)
groupusermembershipid Self-referencing (group membership link)

Enumerator Values

Column Values
subscriptionstatus 1 = Active
3 = Frozen
4 = Cancelled
5 = Expired
6 = Closed
7 = Not Started
8 = Suspended
subscriptionsource 0 = Store
1 = Mobile
2 = Web
3 = Import
membershipstatus 1 = Active
3 = Frozen
4 = Cancelled
5 = Expired
6 = Expired with Prepaids
8 = Suspended
recurring Recurring billing flag (integer)
Was this article helpful?
Your feedback helps us improve our documentation.