Skip to main content

Advanced Dimension Design Techniques

Premium

These techniques incorporate elements from fact tables to create analytically rich dimensions, particularly useful in large-scale environments.

Periodic snapshot fact tables with dimensional attributes

Periodic snapshot fact tables capture the state of the system at regular intervals, combining fact measures with dimensional attributes for trend analysis.

Characteristics:

  • Captures system state at regular intervals.
  • Incorporates both fact measures and dimensional attributes.
  • Allows for trend analysis from a single table.
  • Reduces need for complex joins.

User activity periodic snapshot fact table

user_keydate_keyuser_statustotal_purchasesactive_dayslogin_count
100120230630Gold520512
100120230623Silver32048
100220230630Silver15035
100220230623Bronze8023

Slowly changing dimensions with embedded facts

Enhancing SCDs with embedded facts can improve query efficiency by including frequently accessed measures within dimension tables.

Characteristics:

  • Implements SCD techniques (typically Type 2) for tracking attribute changes.
  • Includes selected fact measures within the dimension table.
  • Enables efficient querying of both current and historical states.

Enhanced user dimension with embedded facts

user_keynamestatuseffective_dateend_datelifetime_purchasesavg_daily_logins
1001John DoeSilver2023-01-012023-06-303202.5
1001John DoeGold2023-07-019999-12-318403.2
1002Jane SmithBronze2023-01-012023-06-30801.2
1002Jane SmithSilver2023-07-019999-12-312301.8

Cumulative user dimensions

Cumulative dimension design aggregates historical data into arrays or JSON structures, allowing efficient querying of long time periods.

Daily snapshot table

user_idis_active_todaynum_likesnum_commentsnum_sharessnapshot_date
115232022-01-01
213412022-01-01

Cumulative table

user_idis_daily_activeis_weekly_activeis_monthly_activeactivity_arraylike_arraycomment_arrayshare_arraynum_likes_7dnum_comments_7dnum_shares_7dnum_likes_30dnum_comments_30dnum_shares_30dsnapshot_date
1111[1,1,0,...][5,3,...][2,1,...][3,0,...]15794521272022-01-01
2111[1,0,1,...][3,2,...][4,0,...][1,2,...]188124020302022-01-01

Reduced fact table

A typical cumulative user dimension might look like this:

user_keyuser_idsignup_datecurrent_statusmonthly_activity
1user_0012023-01-15active[{month: '2023-01-31', logins: 5, purchases: 2, total_spend: 150.00}, {month: '2023-02-28', logins: 10, purchases: 3, total_spend: 200.00}, {month: '2023-03-31', logins: 8, purchases: 1, total_spend: 75.00}]
2user_0022023-02-10inactive[{month: '2023-02-28', logins: 3, purchases: 1, total_spend: 50.00}, {month: '2023-03-31', logins: 4, purchases: 0, total_spend: 0.00}]
Next Lesson