ZenotiHelp

Dimension tables: Employees, memberships, and business

ZTZenoti Docs Team Updated October 27, 2026 5 min readGuide

This article covers dimension tables for employees, payroll, memberships, packages, gift cards, financial, campaigns, feedback, loyalty, training, and system reference data. Each dimension table provides descriptive attributes that you join to fact tables using surrogate key (WID) columns for analysis and reporting.

Before you begin

  • Your organization must have Analytics — Amazon S3 enabled with data loaded into your local data warehouse.
  • Use the surrogate key (WID) columns to join these dimension tables to fact tables in queries. The naming convention is: a fact table column named <dimension>wid joins to the dimension table column <dimension>wid.
  • Natural keys (GUIDs) are preserved in each dimension table for cross-referencing back to source system records.
  • To scope results to a specific center or organization, filter by centerid or organizationid.

On this page


Employees and payroll

Bi_DimEmployee

Property Detail
Description Employee dimension with job, department, and compensation details (73+ columns)
Grain One row per employee
Key columns employeewid (surrogate key), userid (natural key), firstname, lastname, jobcode, department
Common use cases Employee performance, payroll reconciliation, staffing analysis

Bi_DimEmployeeAttendance

Property Detail
Description Employee attendance record type definitions
Grain One row per attendance type
Key columns employeeattendancewid (surrogate key), attendancetype

Bi_DimEmployeeBreakCategory

Property Detail
Description Break category definitions with duration and bookability
Grain One row per break category
Key columns employeebreakcategorywid (surrogate key), breakname, breakduration

Bi_DimEmployeeDeputation

Property Detail
Description Employee deputation assignments to centers
Grain One row per deputation
Key columns employeedeputationwid (surrogate key), employeewid, centerwid, startdate, enddate

Bi_DimEmployeeRole

Property Detail
Description SCD tracking employee role assignments over time
Grain One row per employee role period
Key columns employeerolewid (surrogate key), employeewid, roletype, startdate, enddate

Bi_DimEmployeeScheduleStatus

Property Detail
Description Employee schedule status definitions
Grain One row per schedule status
Key columns employeeschedulestatuswid (surrogate key), statusname

Bi_DimOrgPaymentOption

Property Detail
Description Organization payment option configurations
Grain One row per payment option
Key columns orgpaymentoptionwid (surrogate key), organizationid (natural key), paymenttype

Bi_DimTipsPayoutCenterSettings / Bi_DimTipsPayoutEmployeeSettings

Property Detail
Description Tips payout configurations at center and employee level
Grain One row per center / one row per employee

Bi_DimWorkTasks

Property Detail
Description Work task definitions for employee time tracking
Grain One row per work task
Key columns worktaskwid (surrogate key), taskcode, taskname

Memberships and packages

Bi_DimMembership

Property Detail
Description Membership version definitions with pricing, billing, and benefits (52+ columns)
Grain One row per membership version
Key columns membershipwid (surrogate key), membershipid (natural key), membershipcode, membershipname, price

Bi_DimMembershipFeeTypeDetails / Bi_DimMembershipStatus

Property Detail
Description Membership fee types and status definitions (active, frozen, cancelled, etc.)
Grain One row per fee type / one row per status

Bi_DimUserMembership

Property Detail
Description SCD tracking membership subscription lifecycle per user
Grain One row per user membership period
Key columns usermembershipwid (surrogate key), userwid, membershipwid, startdate, enddate, status

Bi_DimAddOnUserMembership / Bi_dimMembershipUser

Property Detail
Description Add-on membership assignments and denormalized membership-user purchase details
Grain One row per add-on / one row per purchase

Bi_DimPackage

Property Detail
Description Package version definitions with pricing, validity, and benefits (66+ columns)
Grain One row per package version
Key columns packagewid (surrogate key), packageid (natural key), packagecode, packagename, price

Bi_DimPackageBenefits / Bi_DimPackageCenter / Bi_DimPackageUser

Property Detail
Description Package benefit items, center-specific pricing, and user purchase details
Grain One row per benefit / center link / user purchase

Financial and collections

Bi_DimCashRegister / Bi_DimDonations / Bi_DimDiscount / Bi_DimGiftCard / Bi_dimGiftCardUser

Property Detail
Description Cash register configuration, donation programs, discount definitions, gift card types, and user gift card purchases
Grain One row per entity

Bi_DimItemType / Bi_DimItemTypeAggr / Bi_DimItemSource / Bi_DimItemSourceAggr

Property Detail
Description Item type and source classification dimensions
Grain One row per type or source

Bi_DimAccountName / Bi_DimTaxGroupDetails

Property Detail
Description Chart-of-account names and tax group details
Grain One row per account / tax group

Campaigns, feedback, loyalty, and issues

Bi_DimCampaign / Bi_DimCampaignCenters / Bi_DimChallenges / Bi_DimMileStones

Property Detail
Description Campaign definitions, center mappings, challenge and milestone definitions
Grain One row per entity

BI_DimFeedback / Bi_DimCustomForms / Bi_DimFormsMetaData

Property Detail
Description Feedback survey configs, custom form definitions with field metadata
Grain One row per config / form / field

Bi_DimIssueCategories / Bi_DimIssueSubCategories / Bi_DimIssueType / Bi_DimIssueStatusType / Bi_DimIssuePriorityType / Bi_DimIssueSource / Bi_DimIssueTreatment

Property Detail
Description Issue/opportunity classification dimensions
Grain One row per category, type, status, priority, source, or treatment

Bi_DimLoyaltyPointActions / Bi_DimLoyaltyPointPrograms / Bi_DimOrgLPTier

Property Detail
Description Loyalty point actions, programs, and tier definitions
Grain One row per action / program / tier

Training and reference

Bi_DimTrainingClass / Bi_DimSession / Bi_DimWorkshop / Bi_DimWorkshopSession

Property Detail
Description Training class, session, and workshop definitions
Grain One row per entity

Bi_DimDate / Bi_DimStatus / Bi_DimAgent / Bi_DimRecordType / Bi_DimReasons / Bi_DimOrgCustomFields / Bi_DimOrgDispositions / Bi_DimOpportunitySource / Bi_DimETLRefreshLog / Bi_DimRecurrenceDetails / Bi_dimProductBatches

Property Detail
Description Reference and system dimension tables
Grain One row per entity

Joining dimension tables to fact tables

Each dimension table uses a surrogate key (WID) — a system-generated integer identifier — as its primary key. Fact tables reference dimensions using foreign key columns that follow the naming convention <dimension>wid.

For example, to report total sales by center grouped by employee name:

SELECT
    d.firstname,
    d.lastname,
    SUM(f.saleprice) AS total_sales
FROM Bi_FactInvoiceItem f
JOIN Bi_DimEmployee d ON f.employeewid = d.employeewid
GROUP BY d.firstname, d.lastname
ORDER BY total_sales DESC;

To filter results to a specific center or organization, join to Bi_DimCenter and apply a WHERE clause on centerwid or organizationid.

Note: Natural keys (GUIDs such as userid, membershipid, packageid) are preserved in dimension tables for cross-referencing to source system records. Always join fact tables to dimension tables using the WID surrogate key columns, not GUIDs.

Was this article helpful?
Your feedback helps us improve our documentation.