Dimension tables: Employees, memberships, and business
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>widjoins 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
centeridororganizationid.
On this page
- Employees and payroll
- Memberships and packages
- Financial and collections
- Campaigns, feedback, loyalty, and issues
- Bi_DimCampaign / Bi_DimCampaignCenters / Bi_DimChallenges / Bi_DimMileStones
- BI_DimFeedback / Bi_DimCustomForms / Bi_DimFormsMetaData
- Bi_DimIssueCategories / Bi_DimIssueSubCategories / Bi_DimIssueType / Bi_DimIssueStatusType / Bi_DimIssuePriorityType / Bi_DimIssueSource / Bi_DimIssueTreatment
- Bi_DimLoyaltyPointActions / Bi_DimLoyaltyPointPrograms / Bi_DimOrgLPTier
- Training and reference
- Joining dimension tables to fact tables
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.