插入星型图 [英] Insert into a star-schema

问题描述

我已经阅读了很多有关星型模式，事实/决策表，选择语句以快速报告数据的信息，但是对我来说，将数据输入星型模式的问题似乎并不重要.一个理论上"如何将数据输入星型数据库?同时维护事实表.是带有20个参数的巨型存储proc中的一系列INSERT INTO语句的唯一选择(以及如何填充事实表). 非常感谢.

I've read a lot about star-schema's, about fact/deminsion tables, select statements to quickly report data, however the matter of data entry into a star-schema seems aloof to me. How does one "theoretically" enter data into a star-schema db? while maintaining the fact table. Is a series of INSERT INTO statement within giant stored proc with 20 params my only option (and how to populate the fact table). Many thanks.

推荐答案

首先从尺寸开始-一对一.使用ECCD(提取，清洁，符合要求，交付)方法.

Start with dimensions first -- one by one. Use ECCD (Extract, Clean, Conform, Deliver) approach.

请确保每个维度都有一个BusinessKey，该BusinessKey可以唯一标识维度行描述的对象"，例如用于某人的电子邮件.

Make sure that each dimension has a BusinessKey that uniquely identifies the "object" that a dimension row describes -- like email for a person.

加载尺寸后，准备关键查找管道.通常，对于每个维表，您都可以准备一个关键查找表(BusinessKey，PrimaryKey).一些设计人员选择直接查找维表，但是键查找通常可以轻松地缓存到内存中，从而可以更快地加载事实.

With dimensions loaded, prepare key-lookup pipeline. In general, for each each dimension table you can prepare a key lookup table (BusinessKey, PrimaryKey). Some designers choose to lookup the dimension table directly, but the key-lookup can be often easily cached into memory which results in faster fact loading.

也将ECCD用于事实数据. ECC部分发生在登台区域中，您可以根据需要为ECC的每个步骤选择(帮助)表或平面文件.

Use ECCD for fact data too. The ECC part happens in the staging area, you can choose (helper) tables or flat files for each step of the ECC, as you prefer.

在交付事实表时，将事实行中的每个BusinessKey替换为从键查找表中获得的匹配的PrimaryKey.将所有BusinessKey替换为其匹配的PrimaryKey后，将其插入事实表.

While delivering fact tables, replace each BusinessKey in the fact row with the matching PrimaryKey that you get from a key-lookup table. Once all BusinessKeys are replaced with their matching PrimaryKeys, insert the row into the fact table.

不要浪费您的时间，请使用ETL工具.您可以免费下载Pentaho Kettle(社区版)-它具有实现此目的所需的一切.

Do not waste you time, use ETL tool. You can download Pentaho Kettle (community edition) for free -- it has everything one needs to achieve this.

这篇关于插入星型图的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！