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
Bi_DimServiceSegment
Bi_DimCategoryServices
Bi_DimResourceServiceAssociation
Products
The following dimension tables describe product definitions, barcodes, center-specific pricing, vendor relationships, and category assignments.
Bi_DimProduct
Bi_DimProductBarcodes
Bi_DimProductCenter
Bi_DimProductVendors
Bi_DimCategoryProducts
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
Bi_DimVendor
Bi_DimEquipment
Bi_DimResource