SAP BW/4HANA学习笔记2

2.Data Modeling BW/4HANA

Data Modeling简介

Data Quality：数据质量问题；

silos（桶仓）：大量重复冗余的主数据，独立计算统计；

数据silos缺点：交流误解，聚合加总错误，不同角度数据差异。

Misunderstandings in discussions；

Aggregation of errors；

Different beliefs about the truth and the recommended behavior；

Differences in values between different public reports；

 

基于不同计算方式，得到不同结果。

不同的数据源，不同筛选器，不同时间，不同源系统都会导致结果集不同。

 

数据建模指令：a guideline regarding what should be done, where, when, with which data, and in which way.

 

Solving Requirement Conflicts:不同用户不同需求。

解决方法:

Deliver a standard report with reliable truth, but allow business users to create a local copy with their changes.

使用工具：Analysis for Office，SAP BusinessObjects Lumira

 

1.Getting to Know the ItelO Case Study

 

2.Understanding the Source System Data Model

 

问题：业务流程报告缺失或不一致，如何分隔数据源？

 

关注点：销售和仓储；

数据来源：ITelO, Retailer King 3000, and other external sources ；

调查存储不同业务部门数据物理表，着重注意包含不同部门数据表；

 

Modeling Approaches in SAP BW/4HANA

Data Warehouse?

 

使用Data Warehouse目的：

Processing Large Data Volumes;

Integrating SAP and non-SAP applications, cloud based applications, and data lakes;;

Meeting high performance expectations;

Supporting agile predictive and real-time analytics;

Using previously unused data;

 

不同Data Warehouse解决方案。

 

SAP BW/4HANA优缺点。

 

Native SQL数据仓库优缺点。

 

云数据仓库优缺点。

 

Key Aspects of SAP HANA Modeling

 

SAP HANA优点：列存储，数据压缩聚合。访问速度快，减少内存占用。

存储位置类型：1.CPU;2.RAM;3.DISK

SAP BW/4HANA objects such as ADSO and InfoObjects内部自动生成列存储表。

列存储优点：Compression；Partitioning and Parallelization；Insert-only on Delta；

减少内存占用方法：减少不必要数据，区分hot data,warm data,cold data。

 

Key Aspects of SAP BW/4HANA Modeling

 

Data Analysis Tools:

SAP Business One Analysis;

Edition for Microsoft Office;

SAP Crystal Reports for SAP Business One) ;

Design Studio Apps;

BI Explorer or SAP Business Objects Lumira;

ABC analysis;

 

InfoObjects Subtypes

Key figures;

Characteristics;

Time Characteristics;

Units;

 

Open ODS View:虚拟数据；

aDSO：实体数据；

Composite Provider：聚合各种info provider;

BAdI Provider:代码获取数据；

 

A Source System is the logical name for a system；

A DataSource (DS) is a structure definition for extracting data；

A DataFlow contains the objects that are used to load into a target；

A Transformation is rule set for transforming data between logical layers in SAP BW/4HANA；

A Data Transfer Process (DTP) is a definition of a data load process between physical layersin SAP BW/4HANA；

 

SAP BW4/HANA支持数据源。

 

除了aDSO其余都是虚拟数据。

 

SAP HANA Compare SAP BW4/HANA

 

SAP HANA Model Focus:

Fast data processing on demand;

Analytical applications;

Flexible SQL based models;

SAP HANA content;

Aggregation on existing tables;

 

SAP BW/4HANA Model Focus:

Planning;

Strategic and tactical reporting in an enterprise data warehouse;

Stable values;

Loading snapshot and delta data on a periodic basis;

Master data reporting;

Complex and data quality improvement scenarios;

Report on a harmonized set of data;

Using SAP BI content;

 

两种建模方式比较。

 

Best Practice Standards in SAP BW/4HANA Modeling

 

A development system (DEV) to develop objects and perform;

A quality assurance system (QAS) for quality and performance tests;

The productive SAP BW/4HANA system (PRD) is used to generate the required reports;

一般PRD系统只能通过transport修改。

Queries,Query views,Precalculation settins可以在PRD直接更改。

InfoProviders,Transformations,DTPs不能定义可更改。

 

Master Data和Transactional Data

Master data refers to slowly changing data that references business objects and their properties as texts (descriptions), attributes (categories, standard values) and hierarchies (levels of aggregation). Master data is shared across the enterprise.

Transactional data describes business processes and operations, usually by status information and key figures (KPIs).

 

存储Master Data到Transactional data缺点：

Redundancy；Inconsistency；Confusion；Bad staging runtime；Fixation；

 

 

 

 

 

 

 

 

 

区分Master data和Transactional data优点：

Generate new insight;

Reduce Storage Space;

Language Support;

Separate Loading Cycles;

Consistency;

Flexibility;

 

Transitive Attributes,Navigation Attributes,Display Attributes.

 

Tracking History

 

If you want to document master data changes, you can define category as a time-dependent attribute of product.

if you do want to display the category values without displaying the product values, choose the navigational attribute option.

 

Scenario A: Historical Truth at the Time of the Sales Order

 

Scenario B: Current View, the category is modeled as a time-independentnavigation attribute of product.

 

Scenario C1: At Any Point in Time, Current Key Date, the category is modeled asa time-dependent navigation attribute of product.

 

Scenario C2: At Any Point in Time, Historical Key Date).

 

Scenario A (Historical Truth) is best in terms of reporting performance and complexity. All values can be read from one table.

Scenario B (Current View) is best in terms of storage space. There is no redundancy. Only the currently needed values are stored. However, you loose all information about historical assignments.

Scenario C (Time-Dependent) is best in terms of flexibility because the business user can choose freely what key date to use.

 

Mapping and Transforming Data

The central task of data warehouse modeling is to integrate and separate data from several sources.

 

方式1：添加Source key值

 

方式2：合并Source和key值

 

方式3：创建新的key值映射关系

 

Try to model as few physical layers as possible using Composite Providers, Info Sources and SAP HANA Calculation Views to combine data。

使用transformations:

Format harmonization；

Key extension；

Deriving missing values；

Applying source-specific mapping tables；

 

Designing an SAP BW/4HANA Layered Scalable Architecture (LSA++)

 

A well-structured data model consists of different layers,and each layer provides its data as a service for the next layer.

 

Agile Data Marts Architecture:

1.Make data available in SAP HANA;

2.Manually design another SAP HANA view that consumes fields from the basic SAP HANA tables or views;

3.Consume the SAP HANA view;

Where possible, reconsume the models in SAP BW/4HANA using an open ODSview or Composite Provider.

SAP BW/4HANA使用ODSView，Composite Provider访问SAP HANA View;

报表层Query：

Queries can provide standard selections and drilldown properties for standard reports.

 

命名约定：

Z: DataSource (based on existing naming conventions in SAP source systems)

O: Open ODS Layer (Open ODS View)

CM: Corporate Memory Layer (Staging aDSO)

DW: Data Warehouse Layer / Delta Calculation (Standard aDSO)

DM: (Architected) Data Mart Layer (Data Mart aDSO)

W: (Virtual) Wrap Layer (Composite Provider)

V: (Virtual) Composition Layer (Composite Provider)

CV: Calculation View (Agile Data Mart Layer)

 

Understanding Physical and Logical Partitioning

 

将大量数据分割成更小数据集，方便并行读取。

使用characteristics作为参数,drill down和filter操作。

技术上分割不同数据建模：

1.Separate applications；

2. Separate namespaces；

3. Separate folders；

In SAP BW/4HANA, use InfoAreas, in SAP HANA, use content packages, and in a source system, use application components.

4. Summary；

 

different InfoProviders with the same structure but different records (called semantic grouping, semantic partitioning, or logical partitioning), or by database partitioning.

 

不同domain建模

Physical Partitioning；

Logical Partitioning；

 

例题

 SAP always recommends that there are at least three technical systems– development, QA, and production.

BW systems are normally an open system because alterations to objects like queries and query views should be possible. Changes to these objects are not seen as harmful.

The key benefits in implementing a virtual data mart layer are enhancingmaster data, flexibly exchanging virtual or physical InfoProviders, and combining virtuallywhat is already available

 

Process of Modeling

没有标准统一的模型，只有结合需求分析和性能优化的折中方案。

 

Strategic reporting：战略报表，未来趋势；

Tactical reporting：战术报表，当前年趋势；

Operational reporting：业务报表，实时的业务信息，例如订单数量；

建模目的：

Increased revenues；

Reduced costs；

Informed management；

Improved customer orientation；

 

1.Project preparation；

 

2.Business blueprint；

 

gathering requirements：

Analyze the business processes.

Analyze planning processes and define the level of planning.

Analyze the information process.

Analyze the reporting concept.

Analyze requirements on the level of data structures.

Data model definition is carried out in the business blueprint phase of a BW project. 

 

3.Realization；

 

4.Final preparation；

 

5.Go live and support；

 

 

 

How to Modeling?

Purpose of a Requirement Analysis:find out which information is needed for reporting.

需求分析点：

Project focus；

Interviews；

Workshops；

Documentation；

Gap analysis；

Reporting requirements；

An architecture overview model is an overview of the objects that need to be created to meet reporting requirements. 

 

SAP BW/4HANA Content Add-On

 

 

 

Introducing ABAP CDS Views provided by SAP BW/4HANA

 

 

 

There are several advantages of separating master data from transactional data. One of these advantages is avoiding redundancy.

 

The facts Open ODS view is an important Open ODS view that provides the key figures;

master data views, which are used for attributes, and text views;

The referenced Open ODS view can be defined based on a table, or based on an SQL view that joins several tables.

The main purpose of an Open ODS view is to support virtual access to original data. The main advantage is real-time reporting.

 

The disadvantages of virtual access are as follows:

Performance;

Workload;

Volatile values in the source;

Limited transformations;

Open ODS Views, aDSOs, and CompositeProviders can be modeled purely field-based (or as a mix with InfoObjects). 

 

Implementing SAP BW/4HANA InfoObject-Based Models

1.List the Tables in the SAP BW/4HANA Data Model

将Table字段拆解为InfoObjects（characteristics, key figures, units, time characteristics, and technical characteristics）。

 

Characteristic是SAP BW/4HANA中最小单位。

 

Characteristics结构

The maximum is 250 characters for the total (compound) length of the key.

Short text: 20 characters

Medium-length text: 40 characters

Long text: 60 characters

Long text with the option Text is extra long and can have up to 1333 characters.

Texts can be language-dependent.

Texts can be time-dependent.

Tcode:SICF

maintain hierarchies and master data for InfoObject

characteristics.

Hierarchy Maintenance: /sap/bc/webdynpro/sap/RSSHWDY_HIERARCHY_MAINT_APP

Master Data Maintenance: /sap/bc/webdynpro/sap/RSDMDM_MD_MAINTENANCE_APP /sap/bc/webdynpro/sap/RSDMDM_MD_NEW_APP

 

Master Data Tables组成。

 

The X table is only generated if at least one time-independent attribute is defined as anavigation attribute.

S table不会生成：

When a characteristic is defined with the Attribute Only option.

When a characteristic is defined as a High Cardinality InfoObject.

As long as the InfoObject contains data,

it is not possible to go change an attribute or text from a time-dependent to a time-independent version.

It is not possible to remove compounded fields.

It is not possible to change the data type or length.

 

Inbound table for attributes: /BIC/D<techn.Name>

Change log for attributes: /BIC/E<techn.Name>

Inbound table for texts: /BIC/F<techn.Name>

Change log for texts: /BIC/G<techn.Name>

Tcode: RSDMD_SETTINGS

make settings for processing requests

 

Advantages of Hierarchies

 

Internal hierarchy(Run Time or Display Hierarchy): A hierarchical display of existing characteristics, for example, in reports.

External hierarchy: Parent-child data stored in SAP-BW/4HANA-related Master Datatables for a specific characteristic.

Hierarchy Tables:

 

Implementing Advanced DataStore Objects (ADSOs)

An Standard aDSO can contain up to 120 key fields。

In a data mart aDSO, there is no limit to the number of InfoObjects or fields.

 

If components of a semantic group already contain data, then

remodeling may be necessary after generation. During the generation process, remodeling jobs are created for the affected components. These jobs need to be manually executed in the RSCNV_MONITOR transaction.

 

 

 

 

 

Some support functions are available in the SAP GUI transaction

RSOADSO:

1.Display data

2.Display data model (Display XML or Display Output button)

3.Check and activate data model

4.Object directory entry

5.Write transport request (menu Edit→Write Transport Request)

 

 

 

Inbound table: Technical name /BIC/A<technical name>1

Table of active data: /BIC/A<technical name>2

Change Log table: /BIC/A<technical name>3

 

 

 

 

 

Standard DataStore Object

 

 

 

 

 

Staging DataStore Object

 

Data Mart DataStore Object

1.all characteristics are in the key;

2.The data is always loaded into the inbound table;

3. the data is copied to the table of active data and grouped according to the aggregation behavior. Afterward, the data is deleted from the inbound table. The change log is not filled;

 

RSDSO_DU_WRITE_API / RSDSO_DU_WRITE_API_RFC: Loads data from an internal table to the table of active data.

RSDSO_DU_DELETE_API_RFC: Deletes data from the table of active data.

RSDSO_DU_CLEANUP_API_RFC: Deletes API requests with errors.

 

Implementing InfoSources and Conversions in Transformations

 

1.Connecting multiple sources and targets；

2.Splitting a complex transformation into simple parts；

3.Reducing the number of transformations；

 

 

 

we recommend that you perform the unit conversion in the transformation and store the result in an aDSO.

 

Conversion Factor Options:

Use reference InfoObject.

Use central units of measure (T006).

Use reference InfoObject if available. Otherwise, use central units of measure (T006).

Use central units of measure (T006) if available. Otherwise, use reference InfoObject.

 

Implementing CompositeProviders

 

Supported InfoProviders using a UNION or JOIN node:

InfoObject

DataStore object (advanced)

Open ODS view

CompositeProvider (if released for use in another CompositeProvider)

SAP HANA Calculation View

 

 

 

 

 

 

 

 

 

 

 

 

 

An currency conversion rate is influenced by source currency, target currency, reference date, and exchange rate type. 

CompositeProviders can contain SQL based filter expressions.

 Internal hierarchies have no link to master data because they are created “on the fly” when the query result is presented to the user. External hierarchies are stored in BW Master Data tables. External hierarchies are not defined in Query Designer, they are stored in the BW hierarchy tables. External hierarchies are external to the query execution and they are not stored in the OLAP server.

 

Implementing Native SAP HANA Views

advantages of modeling these views as SAP HANA calculation views:

1.SAP HANA calculation views provide immediate access to changing values.

2.No additional storage is needed, especially different versions of master data views exist without additional data storage.

3. In calculation views, new values can be derived on the fly, for example as mathematical calculations or as substrings. No results need to be stored.

 

度量和维度

A measure:

(such as price, volume) is a numeric value, such as a price, quantity, volume, onwhich you can process arithmetic or statistics operations, such as sum, average, top Nvalues, and calculations.

An attributeis:

an element that is used to describe a measure, it can be used as a filter or tosplit an aggregated value into single entities

hierarchies：

 

1.Create a new dimension calculation view.

2.In the Details section, on the Hierarchies tab, define the hierarchy.

3.If you make a mistake, use the Move Upand Move Down buttons to correct the order.

4.Activate the calculation view.

 

1.Create a new dimension calculation view.

2.In the Details section, on the Hierarchies tab, define the hierarchy.

3.Choose the columns for the child and parent.

4.Activate the calculation view.

 

星型结构：

 

Required Conversion Tables

 

Temporal conditions can only be used with data types of time stamp,date, and integer.

Implementing Agile Data Marts

This lesson provides a functional overview of the feature for generating external SAP HANA Views based on SAP BW InfoProviders.

 

支持External SAP HANA View:

InfoObjects (for master data)

Advanced DataStore-Objects

Composite Providers

SAP BW Queries (apply the setting in transaction RSDDB for Query Snapshot Index)

Local CompositeProviders modeled in SAP BW/4HANA Workspace Designer

Tcode: RS2HANA_VIEW

定义生成的SAP HANA View所在Package

Tcode: RS2HANA_ADMIN

查看所有已生成SAP HANA View

Tcode: RS2HANA_CHECK

检查SAP HANA View

 

Object privilege: SELECT on _SYS_BI Object privilege

EXECUTE on REPOSITORY_REST(SYS) Package privilege

REPO.READ on the content package where generated SAP HANA views are stored.

 

Implementing Mixed Scenarios

 

 

 

SAP HANA Analysis Process (HAP)

The following PAL functions are supported:

ABC Analysis

Apriori

Double Exponential Smoothing

K-Means

Outliers

Single Exponential Smoothing

Triple Exponential Smoothing

Weighted Score Table

Projection

 

1.Use transaction RSDHAAP or choose SAP HANA Analysis Process from the Data Warehousing Workbench / Modeling Tree.

2.On the Overview tab page, choose a data source.

3.Choose the function or procedure for the data analysis.

4.Choose the data target.

5.Make any further required settings on the Data Source, Definition of the Procedure, Data Analysis, and Data Target tab pages.

6.Activate your analysis process. Once activated, an analysis process can be transported to other SAP BW/4HANA systems.

7.You have the following options for executing the analysis process:

Execute the analysis process directly.

Schedule it in the background.

Insert the analysis process into a process chain (transaction RSPC).

 

Tcode:RSDHAAP_MONITOR is a new transaction for monitoring of HAP runs.

 

 Non-cumulative key figures have summation as their default aggregation.For time characteristics, you must use exception aggregation

Non-cumulatives do not cumulate as normal key figures because theirvalue is dependent upon a stipulation of a time for the accumulation to make sense.

A stock coverage scenario only needs a stock key figure and a demand key figure,and the stock coverage key figure values (duration of availability) are calculated. Unlike for inventory management, no validity period can be set.