SAP ETL开发规范「建议收藏」

2022-07-01 15:17:20 浏览数 (1)

大家好，又见面了，我是你们的朋友全栈君。

1 介绍

SAP Business Objects数据服务是一种提取，转换和加载（ETL）工具，用于在源环境和目标环境之间移动和操作数据。 SAP数据服务提供了一个数据管理平台，可支持各种举措，包括商业智能，数据迁移，应用程序集成和更多特定应用程序。 SAP Data Services是应用程序中的可执行组件，可以在批处理或实时（服务）架构中部署。

以下文档详细介绍了有关SAP Data Service产品内开发的最佳实践。这包括：

  1. 一般SAP数据服务命名标准
  2. 设计最佳实践
  3. 性能考虑
  4. 审计和执行框架
  5. 审计数据库架构

本文档未涵盖的相关领域包括：

  • 更改控制和项目迁移
  • 数据建模技术

这是技术文档，仅供开发人员和评审人员缩进。

2 数据服务命名标准

2.1 概述

在SAP Data Services中使用命名约定将有助于以受控方式支持单一或多用户开发环境。它还将通过正确的命名和对象描述来帮助生成文档。数据服务可以通过管理控制台基于Web的应用程序中的自动文档工具生成基于组件的文档。

以下各节介绍了Data Services中每种类型对象的命名约定。

使用命名约定可能会导致长名称被使用。要避免在Data Services Designer的设计工作区中截断非常长的对象名称，可以增加显示对象的字符数。要做到这一点：

DI Designer>工具>选项菜单：

参数“工作区图标名称中的字符数”定义了工作区中显示的最大字符数。将此参数设置为所需的值。

作为一般说明，数据服务对象名称中不应包含以下内容：

· 对象版本（即命名数据流DF_LOAD_SALES_V0.3）版本控制应由中央存储库处理，而不是命名约定。

· 特定于环境的信息（即命名数据存储DS_EDW_DEV_1）。应该使用数据存储配置来配置环境信息，而不是通过为每个数据存储创建不同的名称。

2.2 Server Environment Objects

Object

Naming Convention

Example

DEVELOPMENT

DEV

JS_PRJ_DEV_001

TESTING

QA

JS_PRJ_QA_001

PRODUCTION

PRD

JS_PRJ_PRD_001

2.3 Server Objects

其他服务器端对象的命名约定定义如下：

Object

Naming Convention

Example

Job Server

JS_

JS_PRJ_EDW

Job Server Group(Cluster)

JS_GR_

JS_GR_PRJ_EDW

Data Services Local Repository

DS_LOCAL_

DS_LOCAL_EDW

Data Services Central Repository

DS_CENTRAL_

DS_CENTRAL_EDW

2.4 可重复使用的对象

Object

Naming Convention

Example

Project

PRJ_{Name}

PRJ_Load_DMS

Batch Job

JOB_{Short Name}_{ Description }

JOB_Daily_EDW

Real Time Job

RJB_{Short Name}_{ Description }

RJB_LW_Update_Customers

Work Flow contained in one Job only

WF_{JOB Short Name}_{ Description }

WF_LW_Load_Dimensions

Work Flow that is reused

WF_G_{Description}

WF_G_Load_Dimensions

Data Flow contained in one Job only

DF_{JOB Short Name}_{ Description }

DF_LW_Load_Customer

Data Flow that is reused

DF_G_{Description}

DF_G_Start_Job

Custom Function contained in one Job only

FN_{JOB Short Name}_{ Description }

FN_LW_Customer_Lookup

Custom Function that is reused

FN_G_{ Description }

FN_G_Convert_Time

Server Configuration

{ENV}_{Description}

DEV_07/PRD_34/QA_07

2.5 Sources and Targets

Object

Naming Convention

Example

Datastore that connects to database

DS_{ Description }

DS_DW050

Datastore that connects to web service

DS_WS_{ Description }

DS_WS_Customers

Datastore that connects to custom adapter

DS_{Type} _{ Description }

DS_HTTP_Legacy_Customers

Application Datastore that connects to an application e.g. SAP R/3

AP_{Application}_{ Description }

DS_R3_Manufatory

Application Datastore that connects to SAP BW Source

AP _BW_{ Description }

DS_BW_Sales

File Format Template

FMT_{Delimiter}_{Description} Delimiter = CSV,TAB,FIX

FMT_CSV_Customers

DTD’s

DTD_{Name}

DTD_Customer_Hierarchy

XSD Schema

XSD_{Name}

XSD_Customer_Hierarchy

SAP IDoc

IDC_{Name}

IDC_SAP_Customers

Cobol Copy Book

CCB_{Name}

CCB_Account

2.6 Work Flow Objects

Object

Naming Convention

Example

Script

SCR_{Description}

SCR_Initialise_Variables

Condition

CD_{Description}

CD_Full_Or_Delta

While Loop

WHL_{Description}

WHL_No_More_Files

Try

TRY_{Description}

TRY_Fac_Load_Implant

Catch

CAT_{Description}_{Error group}

CAT_Dim_Load_All

2.7 Variables

Object

Naming Convention

Example

Global Variable

$G_{Description}

$G_Start_Time

Parameter Variable – Input

$P_In_{Description}

$P_In_File_Name

Parameter Variable – Output

$P_Out_{Description}

$P_Out_Customer_ID

Parameter Variable – Input/Output

$P_IO_{Description}

$P_IO_Running_Total

Local Variable

$L_{Description}

$L_SystemDate

2.8 Transforms

Object

Naming Convention

Example

CASE

CSE_{Description}

CSE_Countries

Date Generation

DTE_{Description}

DTE_GENERATION

Data Transfer

DTF_{Description}

DTF_StageData

Effective Date

EFD_{Description}

EFD_Effective_From_Date_Seq

Hierarchy Flattening (Horizontal)

HFH_{Description}

HFH_Customers

Hierarchy Flattening (Vertical)

HFV_{Description}

HFV_Customers

History Preservation

HSP_{Description}

HSP_Products

Map CDC Operation

CDC_{Description}

CDC_Products

Map Operation

MAP_{Description}

MAP_Customer_Updates

Merge

MRG_{Description}

MRG_Customers

Pivot

PVT_{Description}

PVT_Products

Query

QRY_{Description}

QRY_Map_Customers

Reverse Pivot

RPT_{Description

RPT_Products

Row Generation

ROW_{Number of Rows}

ROW_1000

SQL

SQL_{Description}

SQL_Extract_Customers

Table Comparison

TCP_{target table}

TCP_Customer_Dimension

Validation

VAL_{Description}

VAL_Customer_Flatfile

XML Pipeline

XPL_{Description}

XPL_Cust_Hierachy

3 通用设计标准

3.1 Batch Jobs

通常应包含一组相关活动的所有逻辑。每项工作的内容和功能应该由调度要求决定。这种机制通常通过访问源系统和执行频率，即每个需要交付的时期（例如每晚，每周等）。这是因为不同的系统会有不同的可用时间，因此作业会有不同的调度要求。

Jobs也应该建立在以下指导原则之上：

  1. Workflow应该是在工作层面使用的唯一对象。唯一的例外是需要作业级别复制的try和catch和conditionals。
  2. 在作业级别应避免并行工作流程，因为当项目并行时，Try和Catch不能应用。

3.2 Comments

Comments应包括在整个数据服务工作中。

Comments应添加到以下位置：

  • 每个对象的描述字段。每个可重用对象（即作业，工作流，数据流等）都有一个可用的描述字段。这应该包括作者，日期和对象的简短描述。
  • 脚本和函数 – 注释在脚本和函数中用＃表示。任何代码的顶部应该是作者，创建日期和脚本的简短说明。评论应包含在代码中以描述那些不言自明的任务。
  • 注释 – 应该使用注释来描述工作流程或数据流的不是自解释的区域。没有必要用无用的注释来混乱设计区域，例如“此查询加入表格”。
  • 现场评论 – 表格应附有每个字段的评论。这些可以手动输入，从数据库导入，或从任何支持CWM（Common Warehouse Metamodel）的工具导入。

3.3 Global Variables

不应将特定于Workflow或Dataflow的变量声明为全局变量。它们应该声明为局部变量并作为参数传递给依赖对象。这些陈述背后的原因是双重的。

首先，由于Data Services能够在顺序或并行执行框架中运行这些对象，本地变量和参数允许修改值而不影响其他进程。

其次，工作流和数据流可以在多个作业中重复使用，并且通过声明本地变量和参数来中断对作业级别全局变量的依赖，这些全局变量已被配置并分配了适当的值。

应该在本地定义的变量的一些示例是：

  • 要加载的Dataflow的平面文件源的文件名
  • 用于条件或while循环的增量变量

所使用的全局变量应该在整个公司内标准化。有效的全局变量的一些例子是：

Variable

Description

Example

Recovery Flag

用于指示作业的标志应在恢复模式下执行。

$G_Recovery

Start Date-Time

开始时间变量应指示作业应从何时开始加载数据的日期和时间。这通常是上次执行的完成日期。

$G_Start_Datetime

End Time

结束时间变量应指示作业应该结束加载数据的日期和时间。这应该在作业开始时设置，以避免重叠。

$G_End_Datetime

Log

指示作业以日志记录模式运行的标志。

$G_Log

Execution Id

表示当前执行作业的ID。在写入审计表时，这被用作参考点。

$G_Current_LoadID

Job Id

代表作业的ID。在写入审计表时，这被用作参考点。

$G_Job_ID

Database Type

在开发通用作业时，了解底层数据库类型（SQL Server，Oracle等）通常很有用。

$G_DB_Type

3.4 Work Flows

在构建Workflow时应遵循以下准则：

  1. 如果对象不相互依赖，则可以将对象保持不连接状态并行运行。并行执行对于将大量表复制到不同环境中的工作流或平面文件的大量加载（提取作业中常见）特别有用。但是，在运行并行数据流时需要小心，特别是在并行数据流使用相同的源表和目标表时。可以在数据服务设计器工具中的工具 – 选项 – 作业服务器 – 环境设置（默认值为8）下设置可用并行执行流的数量限制。
  2. 工作流程不应该依赖全局变量来完成本地任务; 相反，本地变量应声明为本地并作为参数传递给需要它们的数据流。使用全局变量作为环境和全局引用是可以接受的，但除了启动作业的“初始化”工作流以外，通常工作流应该只引用全局变量，而不是修改它们。

3.5 Data Flows

一般而言，数据流应该被设计成将来自一个或多个源的信息加载到单个目标中。一个数据流通常不应该有多个表作为目标。例外情况是：

  • 写出审计表（即写出行数）。
  • 将无效行写入备份表。
  • 在设计高效清洁的数据流时，应将下列项目视为最佳实践：
  • 所有模板/临时表应在数据库专家进入生产环境之前导入并批准和优化。
  • 应检查“下推式SQL”以确保索引和分区得到有效使用。
  • 所有冗余代码（如无用转换或额外字段）应在释放之前删除。
  • 通常，构建数据流的最有效方法是使用最少数量的变换。

有几种常见的做法可能会导致Dataflow设计中的不稳定性和性能问题。这主要是因为Data Service需要将整个数据集加载到内存中才能完成任务。避免这些问题的一些提示如下：

  1. 确保Dataflow中的所有源表都来自同一个数据存储，从而允许将整个SQL命令下推到数据库。
  2. 每个Dataflow应该使用一个主目标表（这不包括用于审计和被拒绝行的表）
  3. 通常，“下推式SQL”应该只包含一个SQL命令。有些情况下可以接受更多命令，例如，如果其中一个查询表只返回少量行，但通常多个SQL命令将意味着数据服务需要在内存连接中执行，这可能会导致内存问题。
  4. 检查查询中的所有“order by”，“where”和“group by”子句是否包含在下推SQL中。
  5. 如果使用pivot或reverse pivot不见，请检查输入列是否已知且一致，因此可以进行测试。
  6. 如果在查找中使用“PRE_LOAD_CACHE”选项，请确保翻译表数据集足够小以适应内存，并且始终具有相同的大小。
  7. 总是尝试在表格比较中使用“排序后的输入”选项，注意确保输入在“下推式SQL”中排序。

3.6 Try/Catch

通常应该在作业开始时和作业结束时使用try-catch对象。try catch的结尾可用于记录失败的审计表，通知某人失败或提供其他所需的自定义功能。Try-Catch对象可以放置在作业和工作流级别，也可以在脚本语言中以编程方式引用。

通常不应像在数据服务中那样使用典型编程语言（如Java）中的try-catch，如果出现问题，通常最好的方法是停止所有处理和调查。

在“catch”对象中有一个脚本重新引发异常（使用raise_exception（）或raise_exception_ext函数）是很常见的。这样可以捕获并记录错误，同时数据服务管理员作业仍会标记为红灯以指示失败。

3.7 While Loops

While 循环主要用于需要加载一系列平面文件、STA层循环抽取（设置数据抽取超时机制）和xml文件的作业，并在其上执行一些附加功能，例如将它们移动到备份目录并更新控制表以指示加载成功和失败。

关于使用全局变量的相同标准也应该应用于while循环。这意味着需要更新的变量（如迭代变量）应声明为局部变量。应使用参数将局部变量传递给基础数据流。

3.8 Conditionals

条件部件用于选择哪个对象应该用于特定的执行。条件可以包含工作流可以包含的所有对象。它们通常用于以下类型的任务：

  1. 指示作业是否应以恢复模式运行。
  2. 指示工作是否应该是初始或增量负荷。
  3. 指示工作是夜间批次还是每周批次（即每周批次可能有额外的业务处理）。
  4. 指示是否应执行部分工作，例如执行提取，清理和符合步骤，但不执行递送步骤。

3.9 Scripts and Custom Functions

构建脚本和自定义函数时应遵循以下准则：

  1. sql（）函数只能用作最后的手段。这是因为在sql（）函数中访问的表在元数据管理器中不可见。lookup_ext函数可以用于查找相关查询，并且应该为插入/更新/删除查询构建数据流。
  2. 自定义函数应该写在逻辑太复杂的地方，不能直接写入Dataflow的映射部分，或者需要对逻辑进行组件化，重用和更详细的记录。
  3. 全局变量不应该在自定义函数中引用; 它们应该作为参数传入/传出。自定义函数可以在多个作业中共享，因此引用作业级全局变量是不好的做法。

使用自定义功能时请注意以下几点要小心：

  • 通常，自定义函数将导致数据流的下推SQL无法有效生成。在查询的where子句中使用自定义函数时，通常会发生这种情况。
  • 在高容量数据流中调用自定义函数会导致性能下降（特别是在使用并行执行的情况下）。

4 SAP Data Service设计指南

4.1 概述

技术要求应该确定所有的sources, targets, transforms和mappings 。将这些要求转换为SAP Data Services设计的最佳技术是使用ETL推荐的提取，清理，一致和交付技术。这些步骤转化为以下真实世界的例子：

  • 分段（提取） – 从源系统分段信息并将其加载到临时/持久分段区域。
  • 转换（符合） – 转换步骤是数据针对目标系统进行标准化的地方。这一步通常是最复杂的，将包括匹配不同的数据源，重复数据删除，聚合以及将源信息转换为目标数据结构所需的任何其他业务规则。
  • 验证（清洁） – 验证步骤用于检测并记录目标端数据质量错误的存在。
  • 加载（交付） – 这是涉及将信息加载到目标系统或生成平面文件的最后一步

这些步骤中的每一步都可以在SAP Data Service中转换为Dataflow（或用于更复杂操作的一系列Dataflow）。

4.2 数据提取

数据提取目的是获取源数据集并将其加载到等效的STA登台表中。源数据集可以是以下任何一种：

  • 数据库中的表（即Oracle，SQL Server）
  • 固定格式或分隔的平面文件
  • 一个xml文档
  • 支持的应用程序界面（即SAP IDoc）

数据提取应基于以下原则进行设计：

  • STA登台表应该与源数据集近似匹配，并应包括源数据集中的所有字段。包括所有的领域是一个简单的练习，并且可以是有用的，因为在将来需要其他领域时，提取作业不需要被修改和重新测试。
  • 其他增值字段可以添加到登台表中，例如：
  1. 记录的代理键（这对于审计和数据沿袭很有用）
  2. 记录加载到分段的日期/时间
  3. 记录加载到目标系统的日期/时间
  4. 表示记录质量是否有效的标志
  5. 指示记录是否已被处理到目标系统的标志
  6. 记录来自的源系统。

Dataflow通常应该非常简单; 只包含数据源表/源代码，一个查询转换，目标表和任何审计表。

在可能的情况下，应该使用查询转换过滤传入的数据集，以便每次只加载新的或更新的记录（基于源的更改的数据捕获）

5 性能考虑

5.1 概述

在数据集成商内生成稳定高效的数据流的方法是确保流过数据流的数据量最小，并尽可能多地在数据库上执行操作。当这种情况不会发生可能导致流量效率低下的瓶颈时。这些问题的一些典型原因可能是：

  • SQL没有正确地下推到数据库（即where条件，group by和order by命令）
  • 使用不正确的目标表缓存选项的表比较
  • 目标表自动更新
  • 使用Reverse Pivot转换
  • 复杂的XML生成

5.2 下推SQL

对于大型传入数据集来说，确保Data Service执行“push down sql”命令有效运行非常重要。运行尚未优化的大型查询可能会对数据库服务器造成严重影响。

应检查下推SQL中的以下项目：

  • 如果传入的数据集很小，则可能不需要对每个字段进行索引，但是通常索引应该位于所有过滤和加入的字段上（这取决于源环境）。先前描述的提取，清理，一致和交付模型允许我们通过在流程中的各个阶段分级数据来减少源系统对整个ETL过程的影响，并因此允许我们根据需要对数据表进行索引和分区。
  • 数据服务生成的优化SQL应该推到一个命令。如果有多个SQL命令，这通常意味着SDS（Software Defined Storage）需要在作业服务器上执行潜在的内存密集型连接。
  • 应该在优化的SQL中反映查询中的任何Sort，Where和Group By子句。

Where子句不会下推到SQL的一些常见原因包括：

  1. 在Where子句中使用自定义函数或复杂函数。解决方法是在数据流之前在脚本中设置变量值，并在可能的情况下将自定义函数替换为变量。
  2. 将源表路由到多个查询。如果您需要在单个数据流中多次使用相同的源表，则应将源表的多个实例添加到数据流中，并将每个实例连接到相应的Query对象。

上述声明不是严格的规则，并且有许多例外可以通过，而不会影响下推。这些包括：

  • 使用Where子句将数据路由到多个查询（例如，将被拒绝的记录路由到不同的表）
  • 过滤在数据流中派生的值时

5.3 Table Comparison Function

在使用表格比较时，通常应该勾选“排序的输入选项”。替代方案是：

  • 没有缓存 – 这个选项没有任何内存影响，但是它是迄今为止最慢的选项，只有在输入数据集已知非常小时才应该使用。
  • 缓存的比较表 – 该选项的速度与排序的输入选项类似，但这意味着整个比较表将缓存到内存中。

使用“排序输入选项”的关键是确保传入的数据集已排序。这种排序必须在下推SQL中完成，否则与大数据集相关的内存问题仍然会发生。

5.4 Reverse Pivot Transform

Reverse Pivot是一个非常有用的转换，可用于将行值转换为列名称。如果传入数据集由非数据透视列分组，则此转换具有按复选框分组，允许其更有效地执行数据透视表。通常，应该在反向数据透视之前使用查询，以便通过非透视列对数据进行排序（确保此排序反映在下推SQL中）。这将提高性能并降低转换的内存要求。

5.5 Target Table Auto-Update

在更新控制选项中自动更正负载可能是确保不发生主键违规的诱人方法。使用它的问题是，它在异构数据库中执行得非常糟糕（更新所有行，无论它们是否已更改），并且在执行代码审阅时通常不被注意。实现相同功能的更好方法是在加载目标表之前使用表格比较转换。使用表格比较具有以下优点：

  1. 可以定义导致更新的列（而不是仅使用所有列）
  2. 排序后的输入选项和缓存选项可用于提高性能
  3. 它在数据流上更具可读性和清晰度

在Oracle上，自动正确加载选项可以作为合并命令来实现，以提高性能。如果选择自动更正，则通过添加注释来证明数据流中存在这种情况。这将提高数据流的可见性以及支持和维护。

5.6 Case Transforms

Case Transforms不应该简单地用作过滤器。其原因是“下推SQL”不会反映过滤器，不必要的行将从底层数据库提取到SDS（Software Defined Storage）引擎中。更好的方法是使用Query对象中的Where子句从源数据库中过滤需要的数据集，然后使用Case变换来拆分数据集并将数据路由到正确的路径。

6 Job模板和执行框架

SAP Data Services提供了一个数据管理平台（IPS），可支持各种举措，包括商业智能，数据迁移，应用程序集成和更多特定应用程序。SAP Data Services Jobs是应用程序中的可执行组件，可以在批处理或实时（服务）架构中部署。

为确保所有SAP Data Services 作业都遵循一致的策略来存储作业参数，记录作业执行情况（包括消息，统计信息和错误处理），设计了一个框架。该框架包含许多共享组件，可以在多个项目部署和维护中实现通用性，从而提高效率并节约成本。

支持框架所需的数据库模式在以下四种主要方式使用：

1) 参数化作业并将参数值存储在作业和应用程序层外部的数据库结构中

2) 记录SAP Data Services应用程序框架内的作业执行情况，记录模式内的成功执行或失败。执行可以记录在作业或步骤级别

3) 在标准框架中记录作业内的消息，统计数据和参数值，以便进行报告和监控

4) 考虑到多种环境，执行类型，各种执行步骤等，可实现灵活的配置

发布者：全栈程序员栈长，转载请注明出处：https://javaforall.cn/131080.html原文链接：https://javaforall.cn

sqlserver 编程算法 sql 数据库

0 人点赞