ZenotiHelp

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).
email 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 userwid references DimUser (self-referencing for referrals)
  • relmgr userwid / relationshipmanager references DimUser (self-referencing for relationship manager)
  • guest base center / guest base center code references 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.