Star schema relationships
Navigate and query the star schema structure in Analytics β Amazon S3 using dimension-fact relationships and surrogate keys.
Before you begin
- Your organization must have Analytics β Amazon S3 enabled and configured.
- You need access to your organization's S3 bucket with the exported data files.
- You should have a SQL-compatible analytics tool (such as Tableau, Power BI, or a SQL client) to query the data.
- Basic understanding of star schema concepts (dimensions, facts, and joins) is recommended.
Steps
Understand the star schema structure
Analytics β Amazon S3 organizes data using a star schema design with dimension tables (Bi_Dim*) containing descriptive attributes and fact tables (Bi_Fact*) containing measurable events and transactions.
All relationships between dimension and fact tables use surrogate key columns called WIDs (Warehouse IDs). These numeric keys provide consistent joins across the entire data warehouse.
Join fact tables to dimensions using WID columns
Identify the fact table containing the measures you need (for example,
Bi_FactInvoiceItemfor sales data).Determine which dimensions provide the descriptive attributes you want (for example,
Bi_DimCenterfor center names,Bi_DimUserfor guest details).Join the tables using the WID naming convention:
- Fact table column:
<dimension>wid - Dimension table column:
<dimension>wid
For example: `sql SELECT d.centername, SUM(f.saleprice) AS total_sales FROM Bi_FactInvoiceItem f JOIN Bi_DimCenter d ON f.centerwid = d.centerwid WHERE f.void = 0 GROUP BY d.centername
Use the user dimension for multiple relationship types
The Bi_DimUser dimension serves as the primary user dimension and connects to fact tables through multiple WID columns:
- userwid β References the guest who received the service.
- therapistwid β References the provider who performed the service.
- createdbywid β References the staff member who created the record.
- soldbywid β References the sales representative.
Join to the same dimension table multiple times using table aliases:
SELECT
guest.firstname AS guest_name,
provider.firstname AS provider_name,
f.saleprice
FROM Bi_FactInvoiceItem f
JOIN Bi_DimUser guest ON f.userwid = guest.userwid
JOIN Bi_DimUser provider ON f.therapistwid = provider.userwid
WHERE f.void = 0
Join to the date dimension for time-based analysis
Connect fact tables to the Bi_DimDate dimension using date columns and the datewid key:
Identify date columns in your fact table (such as
startdateincenter,enddateincenter,creationdateincenter).Join to
Bi_DimDateto access calendar attributes like month names, quarters, and fiscal periods:
`sql SELECT d.monthname, d.year, COUNT(*) AS appointment_count FROM Bi_FactAppointment f JOIN Bi_DimDate d ON f.appointmentdatewid = d.datewid WHERE f.void = 0 GROUP BY d.monthname, d.year
Use the center dimension for location-based filtering
The Bi_DimCenter dimension appears as a foreign key (centerwid) in virtually all fact tables. Use it to filter data by specific centers or analyze performance across locations:
SELECT
c.centername,
c.timezone,
COUNT(a.appointmentwid) AS total_appointments
FROM Bi_FactAppointment a
JOIN Bi_DimCenter c ON a.centerwid = c.centerwid
WHERE a.void = 0
GROUP BY c.centername, c.timezone
Use consolidated tables for simplified queries
Instead of writing multi-table joins, query consolidated tables that pre-join fact and dimension data:
Identify the appropriate consolidated table (for example,
Sales_Fact_Consolidated).Query the consolidated table directly without joins:
`sql SELECT centername, guestfirstname, servicename, saleprice, invoicedate FROM Sales_Fact_Consolidated WHERE void = 0 AND invoicedate >= '2024-01-01'
Consolidated tables include denormalized dimension attributes with human-readable column names, eliminating the need for WID-based joins.
Tips
- Always use WID columns for joins between dimension and fact tables β avoid joining on natural keys (GUIDs) for better performance.
- The
centerwidforeign key appears in almost every fact table, making it the most commonly used dimension join. - When joining to multiple instances of the same dimension (like different user roles), use descriptive table aliases to keep your queries readable.
- Consolidated tables offer simpler queries but may not include all available dimensions β use star schema joins when you need complete flexibility.