TPC-DS标准规范（二）

TPC-DS是一套决策支持系统测试基准，主要针对零售行业。提供99个SQL查询（SQL99或2003），分析数据量大，测试数据与实际商业数据高度相似，同时具有各种业务模型（分析报告型，数据挖掘型等等）。国内目前相关的翻译文章较少。本文尝试对官网的TPC BENCHMARK DS Standard Specification（下称“原文”）进行翻译。翻译主要参照的是2017年发布的2.6.0版本。现在可以在 http://www.tpc.org/tpc_documents_current_versions/current_specifications.asp 查询到最新的版本。

由于原文一共137页，本文在翻译的时候会进行一定的压缩，突出较为关键的信息。本文章节名称，序号，小标题等均严格按照原文翻译排序。

3 数据库填充及标度

本节定义了数据库的填充及如何定义标度

3.1 标度模型

3.1.1 基于dsdgen生成的原始数据的大致尺寸，TPC-DS基准定义了一组离散标度点（“标度因子”）。实际字节计数可能会因个别硬件和软件平台而异。

3.1.2 TPC-DS的标度因子是：1TB，3TB，10TB，30TB，以及100TB。1TB=2^40个字节。进行有效性能测试的测试数据库目前最大为100TB，TPC之后可能会超越这一限制。

3.1.3 每个定义的标度因子都有一个相关的SF值，这是一个无单位的数量，大致相当于数据仓库中存在的数据的千兆字节数。标度因子及SF值的关系如3-1表所示：

3.1.4 提供的标度因子都可以选择使用，没提供的都不能用。

3.1.5 不同数据量下的计算难度不同，因此使用不同的标度因子，其结果之间无法进行比较。

3.2 测试数据库标度

3.2.1 测试数据库是用于进行数据库的负载测试和性能测试的数据库（见第7.4节）。

3.2.2 每个标度因子和测试数据库中的每个表的行数见3-2表中的数据库行数。

3.2.3 行的大小是估算出来的，并且可能会因为数据库不同导致基准间的行信息不同，该数据仅仅作为实现基准的一种参考。

3.3 资格数据库标度

3.3.1 资格数据库(qualification database)是用于查询验证测试的数据库（见第7.3节）。

3.3.2 在性能测试中，针对资格数据库和测试数据库的验证查询，测试出的性能应该一致。因此，资格数据库在几乎每个方面（大小除外）必须与测试数据库相同，包括但不限于：

a) 列定义

b) 数据生成和加载的方法（不是平行程度）

c) 统计聚集方法

d) 数据可访问性的实现

e) 分区类型（不是分区程度）

f) 复制

g) 表的类型（如果表的类型可以选择的话）

h) EADS（比如索引）

3.3.3 资格数据库与测试数据库在每个方面都要相同，除非两者差别与标度的差异直接相关。例如，如果测试数据库使用水平分区（见2.5.3.7），则资格数据库也必须采用水平分区，尽管每种情况下分区数可能不同。同时，资格数据库可以被配置为CPU，存储器和磁盘的子集。如果资格数据库与测试数据库配置不同，则必须将差异标注出来。

3.3.4 资格数据库必须使用dsdgen以及1GB的标度因子。

3.3.5 资格数据库的行数在第3.2节中定义。

3.4 数据库填充与dsdgen

3.4.1 测试数据库和资格数据库必须使用TPC-DS数据生成器dsdgen生成的数据填充，并且dsdgen的主版本号和次版本号必须与TPC-DS规范的版本号相匹配。dsdgen的源代码可以下载（参见附录F）。

3.4.2 dsdgen生成的数据旨在符合表3-2和5-2的要求，如果表与dsdgen生成的数据不同，则以表3-2和5-2为准。

3.4.3 供应商可以修改初始数据库填充部分以及数据维护部分的dsdgen代码，但是结果数据必须符合以下要求：

a) 各列内容必须与dsdgen生成的列一致。

b) 各列数据格式必须与dsdgen生成的列的数据格式一致。

c) 为标度因子生成的行数，必须与表3-2和5-2中指定的行数相同。

如果使用了dsdgen修改后的版本，必须将修改后的源代码全部公开。此外，审核员必须验证所公开的修改后的源代码与基准执行中使用的数据生成程序相匹配。

3.4.4 如果修改仅限于源代码的一部分，则供应商可能只会公开修改源代码。

3.5 数据验证

在数据库初始加载之后，以及任何性能测试之前，要对测试数据库的数据内容的正确性进行验证。使用dsdgen的“-validate”和“-vcount”选项生成验证数据集。“-vcount”的最小值为50，为大多数表生成50行验证数据。但是，“returns”事实表，平均每个表只有5行，其维度表的总行数也少于50行。验证数据集必须全部出现在测试数据库中。

4 查询概述

4.1 查询的要求和定义

4.1.1 查询的定义和可用性

4.1.1.1 查询一般分为以下几种：

a) 商务问题，说明了可以使用查询的商务环境。商务问题列于附录B。

b) TPC提供的查询模板中指定的功能查询定义（功能查询定义部分见4.1.2节）。

c) 替代参数，其描述生成EQT所需的替换值。

d) 在查询验证中使用的答案集（见7.3节）。

4.1.1.2 由于TPC-DS查询集较大，本文档不包含查询的全部内容。获取查询集的信息，请参见表0-1。

4.1.2 功能查询定义

4.1.2.1 每个查询的功能由其查询模板和dsqgen定义。

4.1.3 dsqgen将查询模板转换为功能齐全的SQL，称为可执行查询文本（EQT）。dsqgen的主版本号和次版本号必须与TPC-DS规范的主版本号相同。dsqgen的源代码可以下载（参见表0-1）。

4.1.3.1 查询模板符合SQL1999的核心（OLAP修订），以及以下非标准添加：

1. 模板可以注释返回值的行数。

2. 模板与dsqgen之间有替换标签，单个模板可以生成大量语法不同，功能一致的查询。

4.1.3.2 每个查询的EQT都要从功能查询定义或得到批准的查询变体（见附录C）中获取。除了4.3.2和4.2.5中的允许的特殊情况外，所有查询文本的执行都必须在TPC提供的ull中使用。

4.1.3.3 任何EQT功能与TPC提供的模板生成的相应EQT的功能不匹配的查询模板都是无效模板。

4.1.3.4 所有查询模板及替换参数都应披露。

4.1.3.5 基准测试人员可以修改模板中的语句，从而适应第4.2.3节中定义的产品功能或查询方言的微小差异。

4.1.3.6 如果第4.2.3条允许的更改，无法使基准测试者生成可由DBMS执行的EQT，则可以提交一个备用查询模板（见4.3.2.4节）。

4.1.3.7 如果基准提交中使用的查询模板与TPC提供的模板不一致，则该模板必须满足第4.2.3和4.2.5节的要求。

4.2 查询修改方法

4.2.1 这些查询必须以商业上可用的SQL语言实现。由于ISO SQL语言不断发展，TPC-DS基准规范允许与TPC提供的查询模板中使用的SQL语句有某些偏差。

4.2.2 允许以下四种类型的偏差：

a) 根据第4.2.3节定义的查询小修改。

b) 第4.2.4节定义的限制行数的修改。

c) 第4.2.5节中定义的提取查询的修改。

d) 附录C允许的查询变体。

4.2.3 查询小修改(Minor Query Modifications)

4.2.3.1 基准查询实现需要适应每个特定环境，因此可以做一些特定调整以及针对方言的语法变化。第4.2.3.4中描述的修改有：

1. 被定义为是小修改。

2. 不需要得到批准。

3. 可以与其他小修改结合。

4. 可用于修改功能查询定义或该定义批准的查询变体。

不属于第4.2.3.4节所述范围内的修改不属于小修改，该修改不符合规定。除非它们是附录C中允许的查询变体的组成部分。

4.2.3.2 对功能查询定义或批准的变体的查询小修改，在查询集中的应用必须一致。例如，如果在一个查询中使用了特定的供应商日期表达方式或表名命名方式，则在涉及日期表达式或表名的其他所有查询中必须都使用这一方式。

4.2.3.3 查询小修改的使用应当合法且被披露。

4.2.3.4 以下修改属于小修改：

a) 表：

1. 表名：可以修改每个查询的CREATE TABLE，CREATE VIEW，DROP VIEW和FROM子句中，找到的表和视图名称，以反映SUT的常规命名约定。

2. 表空间引用：可以使用符合第3节要求的表空间引用扩充CREATE TABLE语句。

3. WITH()子句：使用“with()”语法的查询，可以用语义上等效的，表或视图查询替换。

b) Joins：

1. Outer Join：对于外连接查询，可以使用供应商自己的非指定语法。例如，连接表达式“CUSTOMER LEFT OUTER JOIN ORDERS ON C_CUSTKEY = O_CUSTKEY”可以通过将CUSTOMER和ORDERS添加到from子句中，并添加特别标记的JOIN（例如，C_CUSTKEY * = O_CUSTKEY）来替换。

2. Inner Join：对于内连接查询，可以使用供应商自己的非指定语法。例如，连接表达式“FROM CUSTOMER，ORDERS WHERE C_CUSTKEY = O_CUSTKEY”可能被修改为使用JOIN子句的形式，例如“FROM CUSTOMER JOIN ORDERS ON C_CUSTKEY = O_CUSTKEY”。

c) 运算符(Operators)：

1. 显性的ASC-ASC可以被显性添加到ORDER BY子句中的列里。

2. 关系运算符(例如，“>”， “

3. 对于使用字符串连接运算符的查询，可以使用供应商自己的语法（例如，可以用+代替||）。

4. “Rollup(x, y)”形式可以被替换成“x, y with rollup”。

d) 控制语句(Control statements)：

1. 命令分隔符(Command delimiters)：EQT的末尾可以插入附加语法，以便表示查询到此结束并请求执行。 这种命令分隔符的示例是分号或单词“GO”。

2. 事务控制语句(Transaction control statements)：CREATE/DROP TABLE或CREATE/DROP VIEW语句之后可以跟一个COMMIT WORK语句或等效的供应商自己的事务控制语句。

3. 相关视图(Dependent views)：如果实现使用涉及视图的变体，并且实现仅支持“DROP RESTRICT”语法（必须首先删除所有相关对象），则可以添加相关视图的附加DROP语句。

e) 别名(Alias)：

1. Select-list表达式别名：对于包含SELECT-list的项（例如“AS”子句）的别名定义的查询，可以使用供应商自己的非指定语法。可行的例子包括“TITLE ”或“WITH HEADING ”。使用select-list表达式的别名是可选的。

2. GROUP BY和ORDER BY：对于仅仅为了给表达式分组或排序，而使用视图，嵌套表表达式(nested table-expression)或select-list别名的这类查询，供应商可以使用自己的GROUP BY或ORDER BY子句，替换这些视图，嵌套表表达式或select-list。可行的例子包括“GROUP BY”，“GROUP BY ”，“ORDER BY ”和“ORDER BY ”。

3. 相关名称：表名别名可以添加到EQT中。 表名别名之前的关键字“AS”可能会被省略。

4. 嵌套表表达式别名：对于涉及嵌套表表达式的查询，表别名之前的嵌套关键字“AS”可能会被省略。

5. 列别名：列名的别名可能添加到EQT的任何SELECT列表中。这些列别名可以在之后的查询中引用，例如GROUP BY或ORDER BY子句。

f) 表达式与功能：

1. 日期表达式：对于包含涉及操纵日期的表达式的查询（例如，添加/减去日期/月/月/年或从日期中提取年份），可以使用供应商自己的非指定语法。例子包括使用“YEAR（）”从日期列中提取年，或者使用“DATE（）+ 3 MONTHS”将3个月添加到日期列中。

2. 输出格式化功能：仅为影响输出格式（例如处理空字符串）或中间算术结果精度（如COALESCE或CAST）的标量函数，可以放到查询最外面的SELECT中。

3. 聚集函数：标度因子较大时，聚集可能导致越界。因此AVG和COUNT可以被替换为等效的供应商自己的函数以扩展处理的值的范围（例如，AVG_BIG和COUNT_BIG）。

4. 子字符串标量函数：对于使用SUBSTRING()标量函数的查询，可以使用供应商自己的非指定语法替换。例如“SUBSTRING(S_ZIP, 1, 5)”。

5. 标准差函数：对于使用标准差函数(stddev_samp)的查询，可以使用供应商自己的语法（例如stdev，stddev）。

6. 显性转换：可以使用仅为影响整数列或值的运算结果精度的标量函数（如CAST），语法上在修改前后一致，仅运算精度改变。

7. 数学功能：供应商自己的数学表达式可用于替换EQT中的数学函数。替换语法必须实现ISO SQL标准中定义的数学函数的完整语义行为（例如处理NULL）。例如，avg()可以由average()或数学表达式（如sum()/count()替代）。

8. 日期转换：符合2.2.2节中定义的，对日期列或日期常量进行的显性转换，要保证该日期可以完成必要的日期算术结果。替换语法必须具有相同的语义行为。

9. 转换语法：供应商自己的转换语法可替换EQT中存在的转换函数，前提是供应商的转换语法在语义上等同于EQT中提供的语法。

10. 现有标量函数：仅影响输出格式或结果精度的查询模板中的现有标量函数（如CAST）可以被修改。结果语法必须与查询模板中的语义行为一致。

g) 常规

1. 分隔标志符(Delimited identifiers)：如果标识符名称与给定实现中的保留字冲突，则可以使用分隔标识符。

2. 括号：允许在表达式和子查询之间添加或删除括号。必须一起添加或删除前括号“(”及其对应的后括号“)”。

3. 序数词(Ordinals)：序数词与列名引用可以相互替换。如：“select a,b from T order by 2”等价于“select a,b from T order by b”。

4.2.4 行限制修改

4.2.4.1 一些查询要求返回给定数量的行（例如，“返回前10个所选行”）。如果N是要返回的行数，则查询必须完全返回前N行，除非结果集少于N行，否则必须返回所有行。可以通过以下四种方式实现：

1. 可以使用由测试人员的交互式SQL接口支持的，供应商自己的控制语句（例如，SET ROWCOUNT n）来限制返回的行数。

2. 实现特定层来识别控制语句（参见第8.2.4节），从而控制读取行的循环次数，以此限制返回的行数（例如，rowcount

3. 供应商自己的SQL语法可以添加到查询模板的SELECT语句中，以限制返回的行数（例如SELECT FIRST n）。 这种语法的添加不算作查询小修改，因为它完成了功能查询定义的功能需求，并且没有定义标准化语法。 在所有其他方面，查询必须满足4.1.2节的要求。 添加的语法必须仅针对答案集的大小，并且不能对表，索引或访问路径等等进行任何其他显性引用。

4. 使用select子句封装SQL语句（或迭代OLAP查询的语句）并加上行数限制。例如，如果Q是原始查询文本，那么修改可以是：SELECT * FROM（Q） WHERE rownum

测试人员必须使用上述方法中的其中一种，并且对于所有返回行限定都用这一种方式实现。

4.2.5 提取查询修改

4.2.5.1 如第1.4节所述，一些查询会返回大型结果集，结果将被保存以备后续分析。基准测试允许DBMS的替代方法将这些结果行提取到文件。可以通过SQL前端工具处理它们并将行输出到一个文件中。如果对任何流的查询返回10000或更多结果行，则供应商可以使用以下提取工具或方法中的一个，将所有流中的查询的行提取到文件中：

1. 供应商自己的SQL语法可以添加到查询模板的SELECT中，以此重定向返回行，例如“Unload to file ‘outputfile’ Select c1, c2 …”。

2. 供应商可以使用，测试人员SQL交互接口支持的，供应商自己的控制语句，如：

set output_file = ‘outputfile’

select c1, c2…;

unset output_file;

3. 实现特定层（见8.2.4节）来识别控制语句，以用于调用提取工具或方法。

4.2.5.2 如果使用这些替代提取选项中的一个，则输出应格式化为分隔或固定宽度的ASCII文本。

4.2.5.3 如果使用这些替代提取选项之一，则必须满足以下条件：

测试人员可以只选择4.2.5.1中的一个选项。 对于所有需要提取处理的查询，必须使用统一使用该选项。

1. 如果提取语法修改查询SQL，那么查询在所有其他方面必须满足第4.1.2节的要求。添加的语法必须仅处理抽取工具或方法，不得对表，索引或访问路径进行任何额外的显性引用。

2. 测试人员必须证明所使用的文件名称和提取设施本身不向DBMS提供提示或优化，使得查询具有额外的性能收益，而不仅仅只是加速提取行。

所使用的工具或方法必须满足与工具或方法结合使用的查询的所有ACID要求。

4.2.6 查询变体(Query Variants)

4.2.6.1 查询变体是备用查询模板，它是为了让供应商克服特定的功能障碍或产品缺陷而创建的，这些缺陷无法通过查询小修改来解决。

4.2.6.2 在使用新的变体之前，需要先得到批准。批准过程被定义在第4.2.7节。

4.2.6.3 已经被批准的查询变体在附录C中进行了总结。

4.2.7 查询变体批准

4.2.7.1 如果符合以下条件之一，则新的查询变体将被考虑批准：

a) 即使在按照第4.2.3节的要求下进行了查询小修改之后，供应商也不能使用功能性查询定义或已批准的变体成功地针对资格数据库运行EQT。

b) 建议的变体包含新的或增强的SQL语法，与新的ISO SQL标准的批准委员会草案中定义的基准相关。

c) 新变体包含的语法使该的变体更接近ISO SQL标准。

d) 建议的变体包含语法小差异，它们可以直接映射到功能性查询定义中使用的ISO SQL语法，并提供与ISO SQL标准基本类似的功能。

4.2.7.2 要获得批准，变体应具有以下属性——并非所有的属性都是必要的。相反，变体的综合评价将是批准该变体的决定性因素。

a) 变体只是寻求功能兼容性的语法，而不是性能增益。

b) 变体影响的部分很小，只限于修补遗漏的功能。

c) 变体基于对业务问题的了解，而不是基于对SUT的了解或测试数据库中特定数据值的了解。

d) 变体在不同的供应商之间具有广泛的适用性。

e) 变体是非程序性的。

f) 变体是用ISO SQL批准的语法来实现功能性查询定义。

g) 变体是供应商可以实现并希望应用于即将实现的基准中的。

4.2.7.3 拟批准的变体应符合4.2.8节定义的实施准则和4.2.9节定义的代码标准。

4.2.7.4 提议的查询变体的批准将由TPC-DS小组委员会自行决定，并受TPC政策的约束。

4.2.7.5 提交审批的所有提议的查询变体将被记录下来，同时还会记录他们为什么被批准或不被批准的原因。

4.2.8 变体实现指南

4.2.8.1 当提议的查询变体包括创建表格时，数据类型应符合2.2.2节。

4.2.8.2 当所提出的查询变体包括创建新实体（例如，游标，视图或表格）时，实体名称将确保新创建的实体不干扰其他查询会话，并且不在多个查询会话之间共享。

4.2.8.3 在建议的查询变体中创建的任何实体也必须在该变体中删除。

4.2.8.4 如果在建议的查询变体中使用CREATE TABLE语句，则可能包含表空间引用（例如IN ）。在建议的查询变体中创建的所有表必须使用单个表空间。

4.2.9 代码风格

4.2.9.1 实现者可以用任何代码风格编写EQT，包括：

a) 使用换行符，制表符或空格。

b) 使用大写或小写文本。

4.2.9.2 所使用的代码风格对SUT没有影响，且必须从始至终使用同一种风格。

4.3 替代参数生成

4.3.1 每个查询都有一个或多个替代参数，必须使用dsqgen为查询流生成EQT。为了生成所需数量的查询流，dsqgen必须与RNGSEED，INPUT和STREAMS选项一起使用。选择RNGSEED选项的值作为数据库加载时间（加载结束时间）结束的timestamp，以第7.4.3.8节中定义的格式mmddhhmmsss表示。STREAMS选项的值是每个吞吐量测试期间要执行的流数Sq的两倍(S=2*Sq)。INPUT选项的值是一个包含所有99个查询模板，且按照数字顺序排列的文件。

4.3.2 Query_0.sql是在功率测试期间执行的查询序列，query_1.sql到query_ [Sq].sql是在第一个吞吐量测试期间要执行的查询序列，query_ [Sq+1].sql到query_ [2*Sq].sql是在第二个吞吐量测试期间要执行的查询序列。

5 数据维护

5.1 实现要求和定义

5.1.1 数据维护操作是基准执行的一部分，这些操作包括处理刷新。基准中刷新运行的总数等于一次吞吐量测试中的查询流的数量。在每次刷新时，都要执行第5.3节中定义的所有数据维护功能。每个刷新都有自己的dsdgen生成的数据集，必须按照dsdgen生成的顺序使用。数据维护操作与查询分开执行。刷新运行不重叠; 同一时刻最多运行一个刷新。

5.1.2 每次刷新，要在第5.2节中定义的刷新数据上，将5.3节中定义的所有数据维护功能都跑一遍。对于任何一个数据维护功能而言，必须等所有数据维护功能都运行了n次刷新，才能开始运行它的第n+1次刷新（见7.4.8.5）。

5.1.3 数据维护功能可以分解或组合成任何数量的数据库操作，只要满足以下条件，就可以自由选择数据维护功能的执行顺序，同时也可以并行执行。

a) 数据可访问性（见第6.1条）

b) 无论是否被约束强制执行，所有的主/外键关系必须保持不变（见2.5.4）。这并不意味着必须明确定义引用完整性约束。

c) 当数据维护过程完成时，发送带time-stamp的输出消息。

5.1.4 在这些数据维护操作中，必须更新受任何数据维护操作影响的EADS。刷新过程执行的所有更新都可以查询到。

5.1.5 数据维护功能必须在SQL中实现。数据维护的实施过程必须由审核人员审核，审核人员可以要求进一步的测试，以确定数据维护功能是否按照基准要求执行。

5.1.6 分段区域(staging area)是用于实现数据维护功能的数据库对象（例如表，索引，视图等）的可选集合。在分段区域中创建的数据库对象只能在执行数据维护阶段时使用，并且不能在基准测试的任何其他阶段使用。需要在FDR中标明在分段区域中创建的任何对象。

5.1.7 用于分段区域的任何磁盘存储必须定价。必须注明磁盘存储的任何映射或虚拟化。

5.2 刷新数据

5.2.1 刷新数据由一系列数据组成，编号为1，2，3...n。与基准测试的吞吐量测试中的使用流的数量相同。每个刷新数据集由个flat files组成，flat files的内容可用于填充附录A中定义的source schema（并不强制填充）。为每个刷新数据集生成的flat files及其对应的source schema表如下表所示。

5.2.2 表5-1列出了每个flat files的，标度因子为1的刷新数据集的行数。

5.2.3 必须使用dsdgen生成每个数据维护功能的刷新数据集。dsdgen的执行时间不必计时，输出为一个文本文件。刷新数据集的存储应为定价配置的其中一部分。

5.2.4 可以通过以下方式修改dsdgen生成的刷新数据集：刷新数据集中每个表的输出文件可以分为n个文件，其中每个文件包含原始输出的总行数的大约1 / n 文件。必须保留原始输出文件中行的顺序，以使所有n个文件的连接与原始文件相同。

5.2.5 数据维护过程时，读取刷新数据需要计时。在刷新运行的过程中，必须对特定刷新运行的数据集进行加载和计时。数据的加载必须通过数据处理系统中通用的过程，严禁使用专门为TPC-DS设计的加载工具。

5.3 数据维护功能

5.3.1 数据维护功能执行伪码中定义的插入和删除操作，根据维护功能执行的操作和哪种类型的表，将它们分类为方法1-3。

方法1：事实插入数据维护

方法2：事实删除数据维护

方法3：库存删除数据维护

5.3.2 下表列出了所有数据维护功能、其操作类型、以及目标表。视图中的行数必须等于表5-4第6列中列出的source schema表中的行数（列在表5-2中）。

5.3.3 方法1从视图V中读取行（参见第5.3.2节中的视图名称列表），并将行插入数据仓库表T。V和T都被定义为数据维护功能的一部分。T作为数据仓库初始加载的一部分创建。V是不需要实例化的逻辑表。

5.3.4 V的主键在数据维护功能中定义。每个数据维护功能包含一个表，其视图V与其数据仓库表T之间具有列映射关系。V的主键在该映射表的左侧以粗体字表示（例如表5-5）。

5.3.5 商务键在数据维护功能的映射表的右侧以粗体字表示（例如表5-5）。

5.3.6 维度表的主键值为递增序列。如果为维度表生成新的主键值，假设当前主键最大值为x，则新的主键值为x + 1。

5.3.7 方法1：事实表加载

for every row v in view V corresponding to fact table F

get row v into local variable lv

for every type 1 business key column bkc in v

get row d from dimension table D corresponding to bkc

where the business keys of v and d are equal

update bkc of lv with surrogate key of d

end for

for every type 2 business key column bkc in v

get row d from dimension table D corresponding to bkc

where the business keys of v and d are equal and rec_end_date is NULL

update bkc of lv with surrogate key of d

end for

insert lv into F

end for

5.3.8 方法2：销售和退货事实表删除

Delete rows from R with corresponding rows in S

where d_date between Date1 and Date2

Delete rows from S

where d_date between Date1 and Date2

5.3.9 方法3：库存事实表删除

Delete rows from I where d_date between Date1 and Date2

5.3.10 维度表和事实表中，插入或更新行的每个数据维护功能，由以下几部分定义：

a) 描述符，描述数据维护方法的名称。对于维度表，描述符的格式是“DM_”。对于事实表，描述符的格式是“LF_”。扩展名表示使用此数据维护功能填充的数据仓库表。

b) 数据维护方法描述数据维护功能的伪代码。

c) SQL视图V描述source schema需要join哪些表，以加载正确的行。

d) 列映射，定义source schema列映射到哪个数据仓库列。

5.3.11 每个从事实表中删除行的数据维护功能，由以下几部分定义：

a) 描述符，以DF_ 形式表示数据维护功能的名称。该扩展名显示删除行的数据仓库事实表。

b) 表：S和R，在库存情况下是I

c) 两个日期：Date1和Date2

d) 显示数据如何被删除的数据维护方法

5.3.11.1 LF_SS

CREATE view ssv as

SELECT d_date_sk ss_sold_date_sk,

t_time_sk ss_sold_time_sk,

i_item_sk ss_item_sk,

c_customer_sk ss_customer_sk,

c_current_cdemo_sk ss_cdemo_sk,

c_current_hdemo_sk ss_hdemo_sk,

c_current_addr_sk ss_addr_sk,

s_store_sk ss_store_sk,

p_promo_sk ss_promo_sk,

purc_purchase_id ss_ticket_number,

plin_quantity ss_quantity,

i_wholesale_cost ss_wholesale_cost,

i_current_price ss_list_price,

plin_sale_price ss_sales_price,

(i_current_price-plin_sale_price)*plin_quantity ss_ext_discount_amt,

plin_sale_price * plin_quantity ss_ext_sales_price,

i_wholesale_cost * plin_quantity ss_ext_wholesale_cost,

i_current_price * plin_quantity ss_ext_list_price,

i_current_price * s_tax_precentage ss_ext_tax,

plin_coupon_amt ss_coupon_amt,

(plin_sale_price * plin_quantity)-plin_coupon_amt ss_net_paid,

((plin_sale_price * plin_quantity)-plin_coupon_amt)*(1+s_tax_precentage) ss_net_paid_inc_tax,

((plin_sale_price * plin_quantity)-plin_coupon_amt)-(plin_quantity*i_wholesale_cost)

ss_net_profit

FROM s_purchase

LEFT OUTER JOIN customer ON (purc_customer_id = c_customer_id)

LEFT OUTER JOIN store ON (purc_store_id = s_store_id)

LEFT OUTER JOIN date_dim ON (cast(purc_purchase_date as date) = d_date)

LEFT OUTER JOIN time_dim ON (PURC_PURCHASE_TIME = t_time)

JOIN s_purchase_lineitem ON (purc_purchase_id = plin_purchase_id)

LEFT OUTER JOIN promotion ON plin_promotion_id = p_promo_id

LEFT OUTER JOIN item ON plin_item_id = i_item_id

WHERE purc_purchase_id = plin_purchase_id

AND i_rec_end_date is NULL

AND s_rec_end_date is NULL;

5.3.11.2 LF_SR

CREATE view srv as

SELECT d_date_sk sr_returned_date_sk

,t_time_sk sr_return_time_sk

,i_item_sk sr_item_sk

,c_customer_sk sr_customer_sk

,c_current_cdemo_sk sr_cdemo_sk

,c_current_hdemo_sk sr_hdemo_sk

,c_current_addr_sk sr_addr_sk

,s_store_sk sr_store_sk

,r_reason_sk sr_reason_sk

,sret_ticket_number sr_ticket_number

,sret_return_qty sr_return_quantity

,sret_return_amt sr_return_amt

,sret_return_tax sr_return_tax

,sret_return_amt + sret_return_tax sr_return_amt_inc_tax

,sret_return_fee sr_fee

,sret_return_ship_cost sr_return_ship_cost

,sret_refunded_cash sr_refunded_cash

,sret_reversed_charge sr_reversed_charge

,sret_store_credit sr_store_credit

,sret_return_amt+sret_return_tax+sret_return_fee

-sret_refunded_cash-sret_reversed_charge-sret_store_credit sr_net_loss

FROM s_store_returns

LEFT OUTER JOIN date_dim

ON (cast(sret_return_date as date) = d_date)

LEFT OUTER JOIN time_dim

ON (( cast(substr(sret_return_time,1,2) AS integer)*3600

+cast(substr(sret_return_time,4,2) AS integer)*60

+cast(substr(sret_return_time,7,2) AS integer)) = t_time)

LEFT OUTER JOIN item ON (sret_item_id = i_item_id)

LEFT OUTER JOIN customer ON (sret_customer_id = c_customer_id)

LEFT OUTER JOIN store ON (sret_store_id = s_store_id)

LEFT OUTER JOIN reason ON (sret_reason_id = r_reason_id)

WHERE i_rec_end_date IS NULL

AND s_rec_end_date IS NULL;

5.3.11.3 LF_WS

CREATE VIEW wsv AS

SELECT d1.d_date_sk ws_sold_date_sk,

t_time_sk ws_sold_time_sk,

d2.d_date_sk ws_ship_date_sk,

i_item_sk ws_item_sk,

c1.c_customer_sk ws_bill_customer_sk,

c1.c_current_cdemo_sk ws_bill_cdemo_sk,

c1.c_current_hdemo_sk ws_bill_hdemo_sk,

c1.c_current_addr_sk ws_bill_addr_sk,

c2.c_customer_sk ws_ship_customer_sk,

c2.c_current_cdemo_sk ws_ship_cdemo_sk,

c2.c_current_hdemo_sk ws_ship_hdemo_sk,

c2.c_current_addr_sk ws_ship_addr_sk,

wp_web_page_sk ws_web_page_sk,

web_site_sk ws_web_site_sk,

sm_ship_mode_sk ws_ship_mode_sk,

w_warehouse_sk ws_warehouse_sk,

p_promo_sk ws_promo_sk,

word_order_id ws_order_number,

wlin_quantity ws_quantity,

i_wholesale_cost ws_wholesale_cost,

i_current_price ws_list_price,

wlin_sales_price ws_sales_price,

(i_current_price-wlin_sales_price)*wlin_quantity ws_ext_discount_amt,

wlin_sales_price * wlin_quantity ws_ext_sales_price,

i_wholesale_cost * wlin_quantity ws_ext_wholesale_cost,

i_current_price * wlin_quantity ws_ext_list_price,

i_current_price * web_tax_percentage ws_ext_tax,

wlin_coupon_amt ws_coupon_amt,

wlin_ship_cost * wlin_quantity WS_EXT_SHIP_COST,

(wlin_sales_price * wlin_quantity)-wlin_coupon_amt ws_net_paid,

((wlin_sales_price * wlin_quantity)-wlin_coupon_amt)*(1+web_tax_percentage) ws_net_paid_inc_tax,

((wlin_sales_price * wlin_quantity)-wlin_coupon_amt)-(wlin_quantity*i_wholesale_cost)

WS_NET_PAID_INC_SHIP,

(wlin_sales_price * wlin_quantity)-wlin_coupon_amt + (wlin_ship_cost * wlin_quantity)

+ i_current_price * web_tax_percentage WS_NET_PAID_INC_SHIP_TAX,

((wlin_sales_price * wlin_quantity)-wlin_coupon_amt)-(i_wholesale_cost * wlin_quantity)

WS_NET_PROFIT

FROM s_web_order

LEFT OUTER JOIN date_dim d1 ON (cast(word_order_date as date) = d1.d_date)

LEFT OUTER JOIN time_dim ON (word_order_time = t_time)

LEFT OUTER JOIN customer c1 ON (word_bill_customer_id = c1.c_customer_id)

LEFT OUTER JOIN customer c2 ON (word_ship_customer_id = c2.c_customer_id)

LEFT OUTER JOIN web_site ON (word_web_site_id = web_site_id AND web_rec_end_date IS NULL)

LEFT OUTER JOIN ship_mode ON (word_ship_mode_id = sm_ship_mode_id)

JOIN s_web_order_lineitem ON (word_order_id = wlin_order_id)

LEFT OUTER JOIN date_dim d2 ON (cast(wlin_ship_date as date) = d2.d_date)

LEFT OUTER JOIN item ON (wlin_item_id = i_item_id AND i_rec_end_date IS NULL)

LEFT OUTER JOIN web_page ON (wlin_web_page_id = wp_web_page_id AND wp_rec_end_date IS NULL)

LEFT OUTER JOIN warehouse ON (wlin_warehouse_id = w_warehouse_id)

LEFT OUTER JOIN promotion ON (wlin_promotion_id = p_promo_id);

5.3.11.4 LF_WR

CREATE VIEW wrv AS

SELECT d_date_sk wr_return_date_sk

,t_time_sk wr_return_time_sk

,i_item_sk wr_item_sk

,c1.c_customer_sk wr_refunded_customer_sk

,c1.c_current_cdemo_sk wr_refunded_cdemo_sk

,c1.c_current_hdemo_sk wr_refunded_hdemo_sk

,c1.c_current_addr_sk wr_refunded_addr_sk

,c2.c_customer_sk wr_returning_customer_sk

,c2.c_current_cdemo_sk wr_returning_cdemo_sk

,c2.c_current_hdemo_sk wr_returning_hdemo_sk

,c2.c_current_addr_sk wr_returing_addr_sk

,wp_web_page_sk wr_web_page_sk

,r_reason_sk wr_reason_sk

,wret_order_id wr_order_number

,wret_return_qty wr_return_quantity

,wret_return_amt wr_return_amt

,wret_return_tax wr_return_tax

,wret_return_amt + wret_return_tax AS wr_return_amt_inc_tax

,wret_return_fee wr_fee

,wret_return_ship_cost wr_return_ship_cost

,wret_refunded_cash wr_refunded_cash

,wret_reversed_charge wr_reversed_charge

,wret_account_credit wr_account_credit

,wret_return_amt+wret_return_tax+wret_return_fee

-wret_refunded_cash-wret_reversed_charge-wret_account_credit wr_net_loss

FROM s_web_returns LEFT OUTER JOIN date_dim ON (cast(wret_return_date as date) = d_date)

LEFT OUTER JOIN time_dim ON ((CAST(SUBSTR(wret_return_time,1,2) AS integer)*3600

+CAST(SUBSTR(wret_return_time,4,2) AS integer)*60+CAST(SUBSTR(wret_return_time,7,2) AS integer))=t_time)

LEFT OUTER JOIN item ON (wret_item_id = i_item_id)

LEFT OUTER JOIN customer c1 ON (wret_return_customer_id = c1.c_customer_id)

LEFT OUTER JOIN customer c2 ON (wret_refund_customer_id = c2.c_customer_id)

LEFT OUTER JOIN reason ON (wret_reason_id = r_reason_id)

LEFT OUTER JOIN web_page ON (wret_web_page_id = WP_WEB_PAGE_id)

WHERE i_rec_end_date IS NULL AND wp_rec_end_date IS NULL;

5.3.11.5 LF_CS

CREATE view csv as

SELECT d1.d_date_sk cs_sold_date_sk

,t_time_sk cs_sold_time_sk

,d2.d_date_sk cs_ship_date_sk

,c1.c_customer_sk cs_bill_customer_sk

,c1.c_current_cdemo_sk cs_bill_cdemo_sk

,c1.c_current_hdemo_sk cs_bill_hdemo_sk

,c1.c_current_addr_sk cs_bill_addr_sk

,c2.c_customer_sk cs_ship_customer_sk

,c2.c_current_cdemo_sk cs_ship_cdemo_sk

,c2.c_current_hdemo_sk cs_ship_hdemo_sk

,c2.c_current_addr_sk cs_ship_addr_sk

,cc_call_center_sk cs_call_center_sk

,cp_catalog_page_sk cs_catalog_page_sk

,sm_ship_mode_sk cs_ship_mode_sk

,w_warehouse_sk cs_warehouse_sk

,i_item_sk cs_item_sk

,p_promo_sk cs_promo_sk

,cord_order_id cs_order_number

,clin_quantity cs_quantity

,i_wholesale_cost cs_wholesale_cost

,i_current_price cs_list_price

,clin_sales_price cs_sales_price

,(i_current_price-clin_sales_price)*clin_quantity cs_ext_discount_amt

,clin_sales_price * clin_quantity cs_ext_sales_price

,i_wholesale_cost * clin_quantity cs_ext_wholesale_cost

,i_current_price * clin_quantity CS_EXT_LIST_PRICE

,i_current_price * cc_tax_percentage CS_EXT_TAX

,clin_coupon_amt cs_coupon_amt

,clin_ship_cost * clin_quantity CS_EXT_SHIP_COST

,(clin_sales_price * clin_quantity)-clin_coupon_amt cs_net_paid

,((clin_sales_price * clin_quantity)-clin_coupon_amt)*(1+cc_tax_percentage) cs_net_paid_inc_tax

,(clin_sales_price * clin_quantity)-clin_coupon_amt + (clin_ship_cost * clin_quantity) CS_NET_PAID_INC_SHIP

,(clin_sales_price * clin_quantity)-clin_coupon_amt + (clin_ship_cost * clin_quantity)

+ i_current_price * cc_tax_percentage CS_NET_PAID_INC_SHIP_TAX

,((clin_sales_price * clin_quantity)-clin_coupon_amt)-(clin_quantity*i_wholesale_cost) cs_net_profit

FROM s_catalog_order

LEFT OUTER JOIN date_dim d1 ON

(cast(cord_order_date as date) = d1.d_date)

LEFT OUTER JOIN time_dim ON (cord_order_time = t_time)

LEFT OUTER JOIN customer c1 ON (cord_bill_customer_id = c1.c_customer_id)

LEFT OUTER JOIN customer c2 ON (cord_ship_customer_id = c2.c_customer_id)

LEFT OUTER JOIN call_center ON (cord_call_center_id = cc_call_center_id AND cc_rec_end_date IS NULL)

LEFT OUTER JOIN ship_mode ON (cord_ship_mode_id = sm_ship_mode_id)

JOIN s_catalog_order_lineitem ON (cord_order_id = clin_order_id)

LEFT OUTER JOIN date_dim d2 ON

(cast(clin_ship_date as date) = d2.d_date)

LEFT OUTER JOIN catalog_page ON

(clin_catalog_page_number = cp_catalog_page_number and clin_catalog_number = cp_catalog_number)

LEFT OUTER JOIN warehouse ON (clin_warehouse_id = w_warehouse_id)

LEFT OUTER JOIN item ON (clin_item_id = i_item_id AND i_rec_end_date IS NULL)

LEFT OUTER JOIN promotion ON (clin_promotion_id = p_promo_id);

5.3.11.6 LF_CR

CREATE VIEW crv as

SELECT d_date_sk cr_return_date_sk

,t_time_sk cr_return_time_sk

,i_item_sk cr_item_sk

,c1.c_customer_sk cr_refunded_customer_sk

,c1.c_current_cdemo_sk cr_refunded_cdemo_sk

,c1.c_current_hdemo_sk cr_refunded_hdemo_sk

,c1.c_current_addr_sk cr_refunded_addr_sk

,c2.c_customer_sk cr_returning_customer_sk

,c2.c_current_cdemo_sk cr_returning_cdemo_sk

,c2.c_current_hdemo_sk cr_returning_hdemo_sk

,c2.c_current_addr_sk cr_returing_addr_sk

,cc_call_center_sk cr_call_center_sk

,cp_catalog_page_sk CR_CATALOG_PAGE_SK

,sm_ship_mode_sk CR_SHIP_MODE_SK

,w_warehouse_sk CR_WAREHOUSE_SK

,r_reason_sk cr_reason_sk

,cret_order_id cr_order_number

,cret_return_qty cr_return_quantity

,cret_return_amt cr_return_amt

,cret_return_tax cr_return_tax

,cret_return_amt + cret_return_tax AS cr_return_amt_inc_tax

,cret_return_fee cr_fee

,cret_return_ship_cost cr_return_ship_cost

,cret_refunded_cash cr_refunded_cash

,cret_reversed_charge cr_reversed_charge

,cret_merchant_credit cr_merchant_credit

,cret_return_amt+cret_return_tax+cret_return_fee

-cret_refunded_cash-cret_reversed_charge-cret_merchant_credit cr_net_loss

FROM s_catalog_returns

LEFT OUTER JOIN date_dim

ON (cast(cret_return_date as date) = d_date)

LEFT OUTER JOIN time_dim ON

((CAST(substr(cret_return_time,1,2) AS integer)*3600

+CAST(substr(cret_return_time,4,2) AS integer)*60

+CAST(substr(cret_return_time,7,2) AS integer)) = t_time)

LEFT OUTER JOIN item ON (cret_item_id = i_item_id)

LEFT OUTER JOIN customer c1 ON (cret_return_customer_id = c1.c_customer_id)

LEFT OUTER JOIN customer c2 ON (cret_refund_customer_id = c2.c_customer_id)

LEFT OUTER JOIN reason ON (cret_reason_id = r_reason_id)

LEFT OUTER JOIN call_center ON (cret_call_center_id = cc_call_center_id)

LEFT OUTER JOIN catalog_page ON (cret_catalog_page_id = cp_catalog_page_id)

LEFT OUTER JOIN ship_mode ON (cret_shipmode_id = sm_ship_mode_id)

LEFT OUTER JOIN warehouse ON (cret_warehouse_id = w_warehouse_id)

WHERE i_rec_end_date IS NULL AND cc_rec_end_date IS NULL;

5.3.11.7 LF_I：

5.3.11.8

CREATE view iv AS

SELECT d_date_sk inv_date_sk,

i_item_sk inv_item_sk,

w_warehouse_sk inv_warehouse_sk,

invn_qty_on_hand inv_quantity_on_hand

FROM s_inventory

LEFT OUTER JOIN warehouse ON (invn_warehouse_id=w_warehouse_id)

LEFT OUTER JOIN item ON (invn_item_id=i_item_id AND i_rec_end_date IS NULL)

LEFT OUTER JOIN date_dim ON (d_date=invn_date);

5.3.11.9 DF_SS：

S=store_sales

R=store_returns

Date1 as generated by dsdgen

Date2 as generated by dsdgen

5.3.11.10 DF_CS：

S=catalog_sales

R=catalog_returns

Date1 as generated by dsdgen

Date2 as generated by dsdgen

5.3.11.11 DF_WS：

S=web_sales

R=web_returns

Date1 as generated by dsdgen

Date2 as generated by dsdgen

5.3.11.12 DF_I：

I=Inventory

Date1 as generated by dsdgen

Date2 as generated by dsdgen

文章来源：csdn社区 唐犁

特别鸣谢唐犁大神的翻译