OTT流媒体企业Looker中多星型Schema事实表关联方案咨询

核心场景 #

你基于Kimball设计指南搭建了两套星型模型：

• fact_user_subscription：用户订阅事实表，记录激活/到期日期、订阅维度与度量
• vod_watch_fact：观看行为事实表，粒度为单会话/单交易行

需求是用订阅维度（如购买渠道）过滤观看事件，但不想构建全量宽表，且Looker不支持multipass，已尝试Explore层级的relationship关联，需确认可行性及其他方案。

方案1：规范配置Explore层级的Relationship关联（你的尝试方向正确，需注意细节） #

直接在Looker Model中关联两个事实表，但必须解决粒度冲突与数据重复问题：

• 关联逻辑需加入时间有效性校验，确保观看事件落在订阅周期内，同时指定关联关系类型避免数据膨胀：

explore: vod_watch_fact {
  join: fact_user_subscription {
    sql_on: ${vod_watch_fact.user_id} = ${fact_user_subscription.user_id} 
      AND ${vod_watch_fact.watch_timestamp} BETWEEN ${fact_user_subscription.activation_date} AND ${fact_user_subscription.expiration_date} ;;
    relationship: many_to_many
  }
}

• 若用户存在多份有效订阅，会导致观看事件重复计数，需将度量改为distinct_count(${vod_watch_fact.session_id})，或在关联时增加ORDER BY s.activation_date DESC LIMIT 1取最新订阅记录。

方案2：通过统一用户维度表桥接（符合Kimball建模规范） #

如果已有dim_user统一维度表，可将两个事实表分别关联至该维度：

1. 在Model中配置vod_watch_fact与dim_user、fact_user_subscription与dim_user的关联
2. 在Explore中启用三张表的关联，业务用户可先通过fact_user_subscription筛选符合条件的用户，再查看这些用户的vod_watch_fact数据

• 此方式规避了事实表直接关联的粒度问题，完全贴合星型模型的关联逻辑。

方案3：Looker派生表实现轻量级整合 #

无需在Dataform中构建全量宽表，而是在Looker中创建按需计算的派生表，仅整合业务所需字段：

view: watch_with_subscription {
  derived_table: {
    sql:
      SELECT 
        w.*,
        s.subscription_channel,
        s.subscription_type
      FROM vod_watch_fact w
      LEFT JOIN fact_user_subscription s
        ON w.user_id = s.user_id
        AND w.watch_timestamp BETWEEN s.activation_date AND s.expiration_date
      ;;
    refresh: {
      schedule: "daily" # 或设置为按需刷新
    }
  }
}

• 派生表可灵活配置刷新策略，既减少ELT维护成本，又能给业务用户提供整合后的视图。

方案4：子查询式维度过滤 #

通过子查询在维度中实现订阅条件的过滤，无需显性关联两张事实表：

• 先创建判断用户是否有有效订阅的维度：

dimension: has_valid_subscription {
  type: yesno
  sql: EXISTS (
    SELECT 1 
    FROM fact_user_subscription s
    WHERE s.user_id = ${vod_watch_fact.user_id}
      AND ${vod_watch_fact.watch_timestamp} BETWEEN s.activation_date AND s.expiration_date
  ) ;;
}

• 再创建按订阅渠道过滤的维度：

dimension: subscription_channel {
  type: string
  sql: (
    SELECT s.subscription_channel
    FROM fact_user_subscription s
    WHERE s.user_id = ${vod_watch_fact.user_id}
      AND ${vod_watch_fact.watch_timestamp} BETWEEN s.activation_date AND s.expiration_date
    LIMIT 1
  ) ;;
}

• 业务用户可直接通过这些维度筛选观看事件，无需关注底层关联逻辑。

内容的提问来源于stack exchange，提问作者Anatolii Kyrychenko