BI Dimension User Consolidated
ZTZenoti Docs Team Updated April 10, 2026 3 min readGuide
Consolidated dimension table (materialized view) storing user/guest and employee attributes including personal details, contact information, referral data, relationship manager details, and communication preferences.
Columns
| Column Name | Data Type | Nullable | Description |
|---|---|---|---|
| userwid | BIGINT | YES | Warehouse ID uniquely identifying the user record. |
| userid | VARCHAR(36) | YES | Unique identifier for the user. |
| firstname | VARCHAR(256) | YES | First name of the user. |
| lastname | VARCHAR(256) | YES | Last name of the user. |
| middlename | VARCHAR(256) | YES | Middle name of the user. |
| city | VARCHAR(128) | YES | City of the user. |
| zipcode | VARCHAR(128) | YES | ZIP/postal code of the user. |
| usertypename | VARCHAR(128) | YES | Type classification of the user (e.g., Guest, Employee). |
| VARCHAR(512) | YES | Email address of the user. | |
| mobilephone | VARCHAR(64) | YES | Mobile phone number of the user. |
| homephone | VARCHAR(64) | YES | Home phone number of the user. |
| phonecode | INTEGER | YES | Phone country code of the user. |
| usercode | VARCHAR(128) | YES | Code assigned to the user. |
| gender | INTEGER | YES | Gender of the user (integer-encoded). |
| dob | TIMESTAMP WITHOUT TIME ZONE | YES | Date of birth of the user. |
| referredby | VARCHAR(36) | YES | User ID of the referrer. |
| anniversarydate | TIMESTAMP WITHOUT TIME ZONE | YES | Anniversary date of the user. |
| statedispname | VARCHAR(4096) | YES | Display name of the user's state. |
| countrydname | VARCHAR(4096) | YES | Display name of the user's country. |
| void | BOOLEAN | YES | Indicates whether the user record is inactive/voided. |
| usertags | VARCHAR(65535) | YES | Tags associated with the user. |
| guestreferralsource | VARCHAR(3200) | YES | Referral source of the guest. |
| nosalecomments | VARCHAR(65535) | YES | Comments recorded when no sale was made. |
| mandatorybreaktimeperday | INTEGER | YES | Mandatory break time per day in minutes (for employees). |
| refby userwid | BIGINT | YES | Warehouse ID of the referring user. |
| referred by first name | VARCHAR(256) | YES | First name of the referring user. |
| referred by last name | VARCHAR(256) | YES | Last name of the referring user. |
| referred by user code | VARCHAR(128) | YES | User code of the referring user. |
| relmgr userwid | BIGINT | YES | Warehouse ID of the relationship manager. |
| relationship manager first name | VARCHAR(256) | YES | First name of the relationship manager. |
| relationship manager last name | VARCHAR(256) | YES | Last name of the relationship manager. |
| relationship manager user code | VARCHAR(128) | YES | User code of the relationship manager. |
| relationship manager employee - code | VARCHAR(512) | YES | Alternate employee code of the relationship manager. |
| guest base center | VARCHAR(128) | YES | Base center assigned to the guest. Sourced from DimCenter.center_name. |
| guest base center code | VARCHAR(40) | YES | Code of the guest's base center. Sourced from DimCenter.center_code. |
| additionalfield1 | VARCHAR(8000) | YES | Custom additional field 1. |
| additionalfield2 | VARCHAR(8000) | YES | Custom additional field 2. |
| employeecode | VARCHAR(512) | YES | Employee code (when user is also an employee). |
| jobname | VARCHAR(1020) | YES | Job title of the user (when applicable). |
| jobcode | VARCHAR(8000) | YES | Job code of the user (when applicable). |
| employeetags | VARCHAR(65535) | YES | Tags for the employee role. |
| startdate | TIMESTAMP WITHOUT TIME ZONE | YES | Start date of the user/employee. |
| lastdate | TIMESTAMP WITHOUT TIME ZONE | YES | Last active date of the user/employee. |
| address1 | VARCHAR(65535) | YES | Primary address of the user. |
| address2 | VARCHAR(65535) | YES | Secondary address of the user. |
| emailstate | INTEGER | YES | Email subscription state (integer-encoded). |
| receivetransactionalemail | BOOLEAN | YES | Whether the user opted to receive transactional emails. |
| receivemarketingemail | BOOLEAN | YES | Whether the user opted to receive marketing emails. |
| receivetransactionalsms | BOOLEAN | YES | Whether the user opted to receive transactional SMS. |
| receivemarketingsms | BOOLEAN | YES | Whether the user opted to receive marketing SMS. |
| creationdt | TIMESTAMP WITHOUT TIME ZONE | YES | Date and time when the user record was created. |
| ctetllastupdatedon | TIMESTAMP WITHOUT TIME ZONE | YES | Timestamp of the last ETL update for this record. |
| relationshipmanager | VARCHAR(36) | YES | User ID of the relationship manager. |
Primary Key
userwid (BIGINT)
Foreign Keys
referredby/refby userwidreferences DimUser (self-referencing for referrals)relmgr userwid/relationshipmanagerreferences DimUser (self-referencing for relationship manager)guest base center/guest base center codereferences DimCenter
Enumerator Values
| Column Name | Known Values |
|---|---|
| gender | -1 = Not Specified |
| emailstate | Integer-encoded email state values |
| usertypename | Guest, Employee |
Standard ETL metadata columns (EtlLastUpdatedOn, EtlLastUpdatedBy, podname, groupid) are present in the underlying table but omitted from this reference for brevity.
Was this article helpful?
Your feedback helps us improve our documentation.