postgresql - 可以在数据仓库维度表上存储事务主键以在事实暗淡之间关联吗？

问题描述

我有这样的数据源（postgres 事务系统）（简化，实际表的字段比这更多）：

然后我需要创建一个 ETL 管道，其中所需的报告是这样的：

订单号（从sales_order_header）
项目名称（来自sales_order_lines）
批处理开始和结束（从receiving_batches）
已交货数量、已批准接收数量、已拒绝接收数量（来自receiving_inventories）

我对事实暗淡表的设计是这样的（简化的）。

我不知道的是最佳 ETL 设计。让我们关注如何插入事实，以及事实与dim_sales_orders

如果我有这样的临时表：

ETL 每天运行。22:00之后就没有收货了，所以我可以在23:00运行ETL。然后我可以从sales_order_headerand获取数据sales_order_lines，所以在 23:00，脚本可以运行，有点：

INSERT
    INTO
    staging_sales_orders (
    SELECT
        order_number,
        item_name
    FROM
        sales_order_header soh,
        sales_order_lines sol
    WHERE
        soh.sales_order_id = sol.sales_order_header_id
        and date_trunc('day', sol.created_timestamp) = date_trunc('day', now())
    );

而对于事实表，可以在 23:30 运行，带查询

SELECT
    soh.order_number,
    rb.batch_shift_start,
    rb.batch_shift_end,
    sol.item_name,
    ri.delivered_quantity,
    ri.approved_received_quantity,
    ri.rejected_received_quantity
FROM
    receiving_batches rb,
    receiving_inventories ri,
    sales_order_lines sol,
    sales_order_header soh
WHERE
    rb.batch_id = ri.batch_id
    AND ri.sales_order_line_id = sol.sales_order_line_id
    AND sol.sales_order_header_id = soh.sales_order_id
    AND date_trunc('day', sol.created_timestamp) = date_trunc('day', now())

但是如何以最佳方式将数据加载到事实表中，尤其是事实表中？

我的方法

使用自动增量主键选择staging_sales_orders并插入它们。dim_sales_orders
在插入之前fact_receiving_inventories，我需要知道dim_sales_order_id. 所以在这种情况下，我选择：

SELECT
    dim_sales_order_id
FROM
    dim_sales_orders dso
WHERE
    order_number = staging_row.order_number
    AND item_name = staging_row.item_name

然后插入事实表。

现在我怀疑的是第 2 点（从现有的暗淡中选择）。在这里，我根据 2 个 varchar 列进行选择，这应该会影响性能。由于在规范化形式中，我正在考虑修改临时表，添加sales_order_line_id两个临时表。因此，在上面的第 2 点中，我可以做到

SELECT
    dim_sales_order_id
FROM
    dim_sales_orders dso
WHERE
    sales_order_line_id = staging_row.sales_order_line_id

但作为后果，我需要添加sales_order_line_id到dim_sales_orders，这在教程中并不常见。我的意思是，添加事务表 PK 在技术上是可以完成的，因为我可以访问数据源。但是，添加这样的事务字段（尤其是因为它是 PK）是一个很好的 DW fact-dim 维度吗？还是有任何其他方法，而不是基于 2 varchars 选择现有的暗淡？

如何为事实表优化选择维度 ID？

谢谢

标签： postgresqldata-warehouse

postgresql - 可以在数据仓库维度表上存储事务主键以在事实暗淡之间关联吗？

问题描述

解决方案

推荐阅读