Product Analytics with Star Schema and Mitzu.io

Star Schema: Simplifying Data Warehousing for Product Analytics

In data warehousing, star schema is an excellent model for organizing all the information about your business. Its simplicity and performance capabilities, particularly in product analytics, make it a go-to structure for analysts and business intelligence professionals.

The star schema's design, characterized by central fact tables connected to surrounding dimension tables, streamlines the querying process and improves insight retrieval speed.Whether you're tracking sales performance, understanding customer behavior, or analyzing product trends, the star schema provides a robust framework that supports detailed and high-performing analytics.This blog post explores how you can use star schema in warehouse native product analytics.

Visualizing Star Schema: A Practical Example

Before diving deeper into this topic, let me demonstrate a simple star schema data model.

Here, we have one fact table and two dimension tables. (See below)

• Fact: page_events - this table contains simulated visitor logs from a non-existing website
• Dimension: dim_countries - this table contains country-level information
• Dimension: dim_items - this table contains product information that is presented on the website

Dimension Tables:

Fact Table:

Notice that the table has country_code and item_id columns as foreign keys.In the fact table, we have two event types:

• page_visit
• element_clicked_on_page

The element_clicked_on_page event has a foreign key item_id that shows which item was clicked on the page. We have information about the items in the corresponding dimension table.

Overcoming Star Schema Limitations in Growing Organizations

Although the star schema is a great model for organizing information, data analysts can easily explore dimensions and facts due to its deduplicated format. Joining facts to dimensions is straightforward for data analysts on a small scale.However, as the company and the complexity of the data grows, a significant challenge surfaces in the data organization: too many fact and dimension tables lead to knowledge-sharing issues, slowing business intelligence and data analytics.

This is especially a problem when it comes to ad-hoc questions. Answering simple questions for the management with SQL will take longer as the business grows in complexity.

Enhancing Data Access: The Power of Denormalization

Data analysts are slow to answer questions primarily because each SQL query requires research on which dimension table holds the required information and how to join to that table.One potential solution to this is the denormalization of datasets.In practice, this means creating so-called enriched fact tables where each dimension is pre-joined to the fact table.This way, the fact tables are enriched with information from different dimensions.

This simple SQL query creates a VIEW for a denormalized fact table.

create or replace view page_events_extended as 
select 
  t1.*, 
  d1.item_name, 
  d1.cost as item_cost,
  d2.country_name,
  d2.currency as original_currency,
  d2.continent
from page_events t1
left join dim_items d1 on d1.item_id = t1.item_id
left join dim_countries d2 on d2.iso_country_code = t1.user_country_code