I was looking for the best way to capture historical data in HANA for master data tables without the VALID_TO and VALID_FROM fields.
我一直在寻找在没有VALID_TO和VALID_FROM字段的情况下在HANA中捕获主数据表的历史数据的最佳方法。
From my understanding, we have 2 options here.
根据我的理解，我们在这里有两个选择。
-
Create a custom history table and run a stored procedure that populates this history table from the original table. Here we compromise with the real-time reporting capability on top of this table.
创建自定义历史记录表并运行从原始表填充此历史记录表的存储过程。在这里，我们在此表的顶部妥协了实时报告功能。
-
Enable the History table flag in SLT for this table so that SLT creates this as a history table which solves this problem.
在SLT中为此表启用History表标志，以便SLT将其创建为解决此问题的历史表。
Option 2 looks like a clear winner to me but I would like your thoughts on this as well.
选项2看起来对我来说是一个明显的赢家，但我也希望你对此有所了解。
Let me know.
让我知道。
Thanks,
谢谢，
Shyam
希亚姆
1 个解决方案
#1
1
You asked for thoughts... I would not use history tables for modeling time dependent master data. That's not the way history tables work. Think of them as system versioned temporal tables using commit IDs for the validity range. There are several posts on this topic in the SAP community.
你问过想法......我不会使用历史表来建模与时间相关的主数据。这不是历史表的工作方式。将它们视为使用有效范围的提交ID的系统版本化时态表。 SAP社区中有几个关于此主题的帖子。
Most applications I know need application time validity ranges instead (or sometimes both). Therefore I would rather model the time dependency explicitly using valid from / valid to. This gives you the opportunity e.g. to model temporal joins in CalcViews or query the data using "standard" SQL. The different ETL tools like EIM SDI or BODS have also options for populating such time dependent tables using special transformations like "table comparison" or "history preserving". Just search the web for "slowly changing dimensions" for the concepts.
我知道的大多数应用程序都需要应用程序时间有效范围（有时两者都有）因此，我宁愿使用有效的from / valid来明确地模拟时间依赖性。这为您提供了机会，例如在CalcViews中建模时间连接或使用“标准”SQL查询数据。不同的ETL工具（如EIM SDI或BODS）还可以使用特殊转换（如“表格比较”或“历史保留”）来填充此类时间相关表。只需在网页上搜索概念的“缓慢变化的维度”。
In the future maybe temporal tables as defined in SQL 2011 could be an option as well, but I do not know when those will be available in HANA.
在将来，SQL 2011中定义的时态表也可能是一个选项，但我不知道何时可以在HANA中使用它们。
#1
1
You asked for thoughts... I would not use history tables for modeling time dependent master data. That's not the way history tables work. Think of them as system versioned temporal tables using commit IDs for the validity range. There are several posts on this topic in the SAP community.
你问过想法......我不会使用历史表来建模与时间相关的主数据。这不是历史表的工作方式。将它们视为使用有效范围的提交ID的系统版本化时态表。 SAP社区中有几个关于此主题的帖子。
Most applications I know need application time validity ranges instead (or sometimes both). Therefore I would rather model the time dependency explicitly using valid from / valid to. This gives you the opportunity e.g. to model temporal joins in CalcViews or query the data using "standard" SQL. The different ETL tools like EIM SDI or BODS have also options for populating such time dependent tables using special transformations like "table comparison" or "history preserving". Just search the web for "slowly changing dimensions" for the concepts.
我知道的大多数应用程序都需要应用程序时间有效范围（有时两者都有）因此，我宁愿使用有效的from / valid来明确地模拟时间依赖性。这为您提供了机会，例如在CalcViews中建模时间连接或使用“标准”SQL查询数据。不同的ETL工具（如EIM SDI或BODS）还可以使用特殊转换（如“表格比较”或“历史保留”）来填充此类时间相关表。只需在网页上搜索概念的“缓慢变化的维度”。
In the future maybe temporal tables as defined in SQL 2011 could be an option as well, but I do not know when those will be available in HANA.
在将来，SQL 2011中定义的时态表也可能是一个选项，但我不知道何时可以在HANA中使用它们。