ZenotiHelp

Dimension tables: Services, products, and catalog

ZTZenoti Docs Team Updated October 27, 2026 3 min readGuide

This article covers the dimension tables for services, products, and the unified item catalog in Analytics β€” Amazon S3. Use these tables to enrich fact table queries with descriptive attributes such as service names, product pricing, category assignments, and vendor details.

Audience: Data analysts who query the Analytics β€” Amazon S3 star schema to build reports and dashboards.


On this page


Before you begin

  • Your organization must have Analytics β€” Amazon S3 enabled with data loaded into your S3 bucket.
  • Use the surrogate key (WID) columns to join these dimension tables to fact tables. The naming convention is: the fact table column <dimension>wid joins to the dimension table column <dimension>wid. For example, Bi_FactInvoiceItem.servicewid joins to Bi_DimService.servicewid.
  • CSV files delivered to your S3 bucket do not contain column headers. Use the schema files in the SchemaFull folder in your bucket to determine column order.
  • Data refreshes approximately every 1–2 hours. Queries run shortly after a refresh cycle may not reflect the most recent changes.

How dimension joins work

Dimension tables describe what was sold, who was involved, or where a transaction took place. Fact tables record the measures β€” amounts, counts, durations. To produce a meaningful report, you join one or more dimension tables to a fact table via WID columns.

The general SQL pattern is:

SELECT d.<descriptive_column>, SUM(f.<measure_column>)
FROM Bi_Fact<TableName> f
JOIN Bi_Dim<TableName> d ON f.<dimension>wid = d.<dimension>wid
GROUP BY d.<descriptive_column>;

Example β€” service revenue by category:

SELECT ds.categoryname, SUM(f.saleprice) AS total_revenue
FROM Bi_FactInvoiceItem f
JOIN Bi_DimService ds ON f.servicewid = ds.servicewid
GROUP BY ds.categoryname
ORDER BY total_revenue DESC;

To scope results to a specific center or organization, filter using centerid or organizationid on the dimension table. To add calendar-based breakdowns, join date columns to Bi_DimDate.

Services

The following dimension tables describe service definitions, segments, categories, and resource associations.

Bi_DimService

Property Detail
Description Service catalog with definitions, pricing, duration, and category
Grain One row per service
Key columns servicewid (PK), serviceid, servicecode, servicename, categoryid, duration, price
Common use cases Service revenue analysis, menu engineering, pricing optimization

Bi_DimServiceSegment

Property Detail
Description Individual segments composing a service
Grain One row per service segment
Key columns servicesegmentwid (PK), servicewid, segmentorder, duration, type

Bi_DimCategoryServices

Property Detail
Description Service-to-category mappings
Grain One row per service-category link
Key columns categoryserviceswid (PK), serviceid, categoryid

Bi_DimResourceServiceAssociation

Property Detail
Description Resource-to-service associations
Grain One row per resource-service link
Key columns resourceservicewid (PK), resourcewid, servicewid

Products

The following dimension tables describe product definitions, barcodes, center-specific pricing, vendor relationships, and category assignments.

Bi_DimProduct

Property Detail
Description Product catalog with definitions, pricing, brand, and vendor
Grain One row per product
Key columns productwid (PK), productid, productcode, productname, brand, price
Common use cases Product sales analysis, inventory management, vendor performance

Bi_DimProductBarcodes

Property Detail
Description Product barcode associations
Grain One row per barcode
Key columns productbarcodewid (PK), productwid, barcode

Bi_DimProductCenter

Property Detail
Description Product-center associations with center-specific pricing
Grain One row per product-center link
Key columns productcenterwid (PK), productwid, centerwid, saleprice

Bi_DimProductVendors

Property Detail
Description Vendor-product associations with purchase pricing
Grain One row per product-vendor link
Key columns productvendorwid (PK), productwid, vendorid, purchaseprice

Bi_DimCategoryProducts

Property Detail
Description Product-to-category mappings
Grain One row per product-category link
Key columns categoryproductswid (PK), productid, categoryid

Catalog and reference

The following dimension tables provide the unified item catalog, vendor master data, equipment, and room or resource definitions used across multiple fact tables.

Bi_DimItems

Property Detail
Description Unified item reference spanning services, products, memberships, packages, and gift cards
Grain One row per item
Key columns itemwid (PK), itemid, itemname, itemtype

Bi_DimVendor

Property Detail
Description Vendor master data with contact and tax details
Grain One row per vendor
Key columns vendorwid (PK), vendorid, vendorname, contactinfo

Bi_DimEquipment

Property Detail
Description Equipment details per center
Grain One row per equipment record
Key columns equipmentwid (PK), centerwid, equipmentname, capacity

Bi_DimResource

Property Detail
Description Room and resource dimension with capacity and service associations
Grain One row per resource
Key columns resourcewid (PK), centerid, resourcename, capacity
Was this article helpful?
Your feedback helps us improve our documentation.