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.