在数据集市中填充事实表的想法
我正在寻找在数据集市中填充事实表的想法。假设我有以下维度
- Physician
- Patient
- date
- geo_location
- Patient_demography
- test
我使用了两个 ETL 工具来填充维度表 - Pentaho 和 Oracle Warehouse Builder。日期、患者人口统计和地理位置不会从运营存储中提取数据。所有维度表都有自己的新代理键。
我现在想用患者就诊的详细信息填充事实表。当患者在特定日期去看医生时，他会要求进行测试。这是事实表中的信息。为了简单起见，我还省略了其他措施。
我可以使用来自源系统的事实表中的所有必需列创建单个联接。但是，我需要存储患者、医生、测试等维度表中的键。实现此目的的最佳方法是什么？
ETL 工具可以帮助解决这个问题吗？
谢谢 克里希纳
I am looking for ideas to populate a fact table in a data mart. Lets say i have the following dimensions
- Physician
- Patient
- date
- geo_location
- patient_demography
- test
I have used two ETL tools to populate the dimension tables- Pentaho and Oracle Warehouse Builder. The date, patient demography and geo locations do not pull data from the operational store. All dimension tables have their own NEW surrogate key.
I now want to populate the fact table with the details of a visit by a patient.When a patient visits a physician on a particular date, he orders a test. This is the info in the fact table. There are other measures too that I am omitting for simplicity.
I can create a single join with all the required columns in the fact table from the source system. But, i need to store the keys from the dimension tables for Patient, Physician, test etc.. What is the best way to achieve this?
Can ETL tools help in this?
Thank You
Krishna
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（1）
每个维度表都应该有一个 BusinessKey，用于唯一标识表行描述的对象（人员、日期、位置）。在加载事实表的过程中，您必须根据 BusinessKey 从维度表中查找 PrimaryKey。您可以选择直接查找维度表，或者在加载事实表之前为每个维度创建一个键查找表。
Pentaho Kettle 具有用于此目的的“数据库值查找”（转换步骤）。您可能还想查看 Kimball 数据仓库 ETL 工具包的“交付事实表”部分。
Each dimension table should have a BusinessKey that uniquely identifies the object (person, date, location) that a table row describes. During loading of the fact table, you have to lookup the PrimaryKey from the dimension table, based on the BusinessKey. You can choose to lookup the dimension table directly, or create a key-lookup table for each dimension just before loading the fact table.
Pentaho Kettle has the "Database Value Lookup" (transformation step) for the purpose. You may also want to look at the "Delivering Fact Tables" section of Kimball's Data Warehouse ETL Toolkit.