sap business information warehouse-based enterprise data warehouse … · 2019-11-12 · sap...

SAP TechEd ‘03 Basel

© 2003 SAP AG BW 254, Jürgen Haupt 1

SAP Business Information Warehouse-BasedEnterprise Data Warehouse- More Than a Vision

Jürgen HauptSAP NetWeaver RIG, SAP AG

SAP AG 2003, BW254, Jürgen Haupt/ 2

Learning Objectives

As a result of this workshop, you will be able to:

Understand basic architecture aspects of a corporate BW implementation approachSee the basic differences between a Data Mart driven BW implementation and a BW implementation based on a enterprise data warehousing strategyExamine an existing BW Landscape from a corporate point of view




Agenda

Evolution of BW

Enterprise Data Warehousing BasicsControlled Redundancy

BW Architecture AspectsData Layer Data ModelLandscape

Further Aspects of a Corporate BW StrategyData IntegrationApplication Development

Exercise

Discussion

Summary


Evolution of SAP BW

Strategic Corporate BW Strategic Corporate BW ImplementationsImplementations

Isolated BW Isolated BW ImplementationsImplementations

BW1

BW2

BW3

BWn

BW1

BW2

BWn

GlobalBW

Others

‘‘Headquarter’ Headquarter’ ReportingReportingScenarioScenario

‘Local’BW

‘Local’BW

‘Local’BW

GlobalBW

Architected Architected BW LandscapeBW Landscape

ScenarioScenario

EnterpriseBW

BW EnterpriseBW EnterpriseDataData

WarehouseWarehouseScenarioScenario

Issues:SynergyIntegrationConsistency

SpokeBW

SpokeBW




Basic Corporate BW Landscape Strategies

‘Local’BW

‘Local’BW

‘Local’BW

GlobalBW

OutsideOutside--ININBWBW

LandscapeLandscape

Others

EnterpriseBW

InsideInside--OutOutBW EnterpriseBW Enterprise

DataDataWarehouseWarehouse Spoke

BW

SpokeBW

Others

SpokeBW

U n ileverHPCE

Guarantee Consistency

Reduce Costs


Agenda

Evolution of BW




Exercise

Discussion

Summary




Redundancy and Data Mart (Solution) Focus

Real World

InformationRequirements

(grouped to Scopes)

....Schema-Modeling

InfoCubes/ODS-Objects

Extraction

Sources

Business Rules

Transformation

BW Application Project Team

Successful Data Warehousing means ‘Controlled Redundancy’ !Successful Data Warehousing means ‘Controlled Redundancy’ !Successful Data Warehousing means ‘Controlled Redundancy’ !

Impacts of non-existing corporate BW guidelines:

• Redundant Data• Redundant Extraction• Redundant Transformation• Redundant Business Rules• Redundant Masterdata•......


Redundancy and Multiple BW Landscape

Sub-Division AR/3

Global

Sub-Division B R/3

only Germany

Sales EuropeR/3

BW Local

Global

HQBW

SubDiv BW

MDMS

BWS-America

BWAsia

BWN-America

BWSales Europe

Source Systems

....................DivR/3

DivR/3

DivR/3

12 systemsworldwide

Successful Data Warehousing means ‘Controlled Redundancy’ !Successful Data Warehousing means ‘Controlled Redundancy’ !Successful Data Warehousing means ‘Controlled Redundancy’ !

A Real Life

BW Landscape ExampleImpacts of non-existing corporate BW guidelines:

•Redundant Data Flows•Redundant Extraction•Redundant Development•Redundant Models•......




Controlling Redundancy

Data

Extraction

Staging Processes

Transfer rules

Update rules

Data Model (s)

Applications

...


Agenda

Evolution of BW




Exercise

Discussion

Summary




Aspects of an Enterprise Data Warehouse Architecture

• Data Store Architecture - Data Layer Architecture• Persistently stored data • Data schemas

• Data Architecture – Data Model Architecture• Objects and their relations• Relations between the models

• Data Warehouse Landscape Architecture• The instances and their roles


SAP BW Layer Architecture - Overview

Finance

Logistic Ope

n D

istri

butio

n

SAP Business Information WarehouseSAP Business Information Warehouse

StagingStagingAreaArea

CleansingCleansingTransformTransform

MasterReference

Data

Data Data WarehouseWarehouse

ODSODS

PrimaryPrimaryData Data

ManagementManagement

Extra

ctio

n /

Ope

n St

agin

g

DataDataAcquisitionAcquisition

Data Data DeliveryDelivery

any

targ

et

Ext

ende

d St

ar S

chem

asE

xten

ded

Star

Sch

emas

Flow Control Flow Control

MetaMeta DataData

any

sour

ce TransactionData

ArchitectedArchitectedData MartsData Marts




Data Mart Implementation

Real World


(grouped to Scopes)

Modeling

InfoCubes

Extraction

Sources

Business Rules

Transformation

Incremental, scope-specific


BW Data Mart Layer: Extended Star Schema

FACT Table

Material_Dimension_IDSalesOrg_Dimension_IDTime_Dimension_IDCustomer_Dimension_ID

Sales AmountQuantity

InfoCubeInfoCube

Gebiet 1 Gebiet 2 Gebiet 3

Bezirk 1

Gebiet 3a

Bezirk 2

Region 1

Gebiet 4 Gebiet 5

Bezirk 3

Region 2

Gebiet 6

Bezirk 4

Gebiet 7 Gebiet 8

Bezirk 5

Region 3

Vertriebsorganisation

Material Group

Material Hierarchy Table

Material NumberLanguage Code

Material NumberLanguage CodeMaterial Name

Material Text TableMaterial_Dimension_ID

Material NumberMaterial Dimension

Table

Material Master Table

Material NumberMaterial Number

Material Type

MaterialMaterialDimensionDimension

Shared, globalShared, globalPart of

Material Dimension

LocalLocal Part of Material Dimension

BW Extended Star Schema




Horizontal Consistency –InfoObject Master Data is the Glue

InfoCube

CUSTOMER

MATERIAL

MATERIALmaster

CUSTOMERmaster

Horizontal Consistency:Horizontal Consistency:Conformed Structures Conformed Structures

BW InfoObjectsBW InfoObjectsmaster datamaster data

CUSTOMER

MATERIAL

Horizontal Consistency –InfoObject Master Data is the Glue

InfoCube ODS-Object

CUSTOMER


BW Data Mart Support of Different Historical Views

Constellation 09/1998Customer Mother-Company

AAA XBBB Y

Constellation 10/1998

Customer Mother-Company AAA XBBB X (changedX (changed)

Customer Date Revenue

AAA 09/1998 100BBB 09/1998 100AAA 10/1998 100BBB 10/1998 100

Fact Table

Mother-Comp Rev 09/98 Rev 10/98

X 200 200Y 0 0

I) Report using today‘s (10/98) constellation


X 100 100Y 100 100

II) Report using 09/98 constellation


X 100 200Y 100 0

III) Report showing historical truth


X 100 100Y 0 0

IV) Report showing comparable results

BW supported historical

Views in a single InfoCube

‘Slowly Changing Dimensions’and Reporting Results :




History of Master Data (Slowly Changing Dimensions) –Architected Data Marts

Master Data History and BW Architected Data MartsThe InfoObject master data tables are designed to form the Conformed

Dimensions of the Architected data martsThe BW Architected data marts allow handling all relevant historical views

of master data. • For most scopes, storing the actual relationships in the master data

table is sufficient • Validity periods of relationships offered by the source application may

be stored using the ‘time dependent’ feature.

Avoid storing historical versions of master data relationships using the ‘time-dependent’ feature as this corrupts query performance

It is normally not the task of this layer to handle complete historical master data versions!


Information Completeness and Data Mart Layer (I)-Transaction Data Processing

Customer Dimension Material DimensionA 4712

4713

Customer Material TimeAmount

Company Currency

A 4712 200211 100A 4713 200211 150

Time Dimension200211

Customer Time DocNo Pos MaterialAmount

Local Currency

A 20021107-10am 1 10 4711 100 New bookingA 20021107-3pm 1 10 4712 200 Correction bookingA 20021107-4pm 2 10 4713 300 New booking

Sourcesystem

BW Architected Data Mart Layer

daily

weekly dailymonthly

other InfoCubeother InfoCube

InfoCube




Information Completeness and Data Mart Layer (II)-Master Data Processing

Customer CustomerGroup

A Y after 2nd Load

Customer CustomerGroup

A X 1st Load: 20020101A Y 2nd Load: 20020401

InfoObject Master Data Table

Master Data from Source or Master Data Server

BW Architected Data Marts (ADM) means:

Aggregation OverwritingManipulation

As the ADM layer is reporting driven we lose information,if the information does not support the data mart scope


A Matter of Consistency and Reliability –The BW Data Warehouse (Layer)

Finance

Logistic Ope

n D

istri

butio

n




MasterReference

Data


ODSODS



Extra

ctio

n /

Ope

n St

agin

g

DataDataAcquisitionAcquisition

Data Data DeliveryDelivery

any

targ

et

Ext

ende

d St

ar S

chem

asE

xten

ded

Star

Sch

emas


MetaMeta DataData

any

sour

ce TransactionData





Introducing a BW Data Warehouse Layer

BW Data BW Data WarehouseWarehouse

LayerLayer

ODSODS--Objects,Objects,FlexibleFlexibleStagingStaging

It is not the task of the scope oriented Data Mart Layer to anticipate any kind offuture arising needs – this would overload the schemas and corrupt performanceIt cannot be expected that the data mart project teams have the 360° corporate view

to guarantee:extract once deploy manycontrolled redundancysingle point of truthflexible structures

Make the result of the data transformation and cleansing process persistentin a way that is:

Subject orientedIntegratedGranularNon-volatile (historical)Not (application) flavored

⇒ BW Data Warehouse Layer


Transaction Data Processing in the BW DWH Layer


4713


Company Currency

A 4712 200211 100A 4713 200211 150



Local Currency

Amount Company Currency

A 20021107-10am 1 10 4711 100 50A 20021107-3pm 1 10 4712 200 100A 20021107-4pm 2 10 4713 300 150


Local Currency


Sourcesystem

BW Data Warehouse Layer

daily

weekly dailymonthly



InfoCube




Master Data Processing in the BW DWH Layer

Customer CustomerGroupA Y after 2nd Load

Customer Activ From CustomerGroupA 20020101 X after 2nd LoadA 20020401 Y

Customer CustomerGroupA X 1st Load: 20020101A Y 2nd Load: 20020401

InfoObject Master Data Table

DWH ODS-Object


Data Processing in the BW DWH Layer

DWH layer data processing means:

All data has to pass this layer on it’s path from the source to the Architected Data Marts

The data is not aggregated.

The data is not manipulated to please specific project scopes

Old versions are not overwritten or changed but useful information may be added

Data is integrated as far as possible

The data is normally not the primary target for reporting and analysis

Data is extracted only once and deployed many

Define BW Data Warehouse Layer General GuidelinesCorporate Guidelines must be set up and an administrator must beestablished to achieve benefits from a BW Data Warehouse layer.




Benefits of a BW DWH LayerFlexibility

Create new analysis data marts without extracting data once againA potential fall back line to cover single time unexpected end-user needs (kind of basic ad hoc reporting/ analysis)

Reliability, Audit Trail A Single Point of Truth All data from the source systems have to path this layer on their way to the end-user

Complete History/ Versions

Consistency, Controlled RedundancyData is extracted only once and deployed many timesRedundant staging processes are avoidedRealization of the corporate integration strategy is not a matterof a departmental project team

Integrationsemanticalcommon coding

The BW Data Warehouse Layer is the corporate memory, thecorporate information repository


Vertical Consistency

Real World


(grouped to Scopes)

Modelling

InfoCubes

EDW

Extraction

Sources

Business Rules

Transformation

BW Application Project Team

EDW

Admin

Team

• Controlled Redundancy• Extract once – Deploy many• Single Point of Truth

• Controlled Redundancy• Extract once – Deploy many• Single Point of Truth




The central role of the BW DWH

If BW Data Warehouse layer functionality is established at corporate level you can think of the BW Data Warehouse

as your corporate memory,

your corporation’s information repository.

This is the reason that this layer is often called the

Enterprise Data Warehouse.


BW Persistent Staging Area (PSA)

StagingStagingArea:Area:PSAPSA


BW Data BW Data WarehouseWarehouse

Extra

ctio

n /

Ope

n St

agin

g

BW ODSBW ODSany

sour

ce

Makes a BW DWH Layer the PSA obsolete?

The answer is : just partly

1. Backup for ‘non-mature’ applications2. Uncouple extraction from BW

processing

Best Practice: Reduce Space Requirements Introducing a PSA Lifecycle Concept

The role of the PSA as backup is limited if a BW Data Warehouse layer is used. For backup purposes of mature applications, the PSA is no longer needed. Thus, PSA-element entries could be deleted normally after 2 to 4 weeks. Not deleting the entries may lead after short time to significant disk space usage




Near Real Time Data Warehousing (BW Vers. 4) –BW Operational Data Store

Finance

Logistic Ope

n D

istri

butio

n




MasterReference

Data


ODSODS



Extra

ctio

n /

Ope

n St

agin

gDataData

AcquisitionAcquisitionData Data

DeliveryDelivery

any

targ

et

Ext

ende

d St

ar S

chem

asE

xten

ded

Star

Sch

emas


MetaMeta DataData

any

sour

ce TransactionData



A Matter of Information Perspective –Classic Data Warehousing and Operational Reporting

An information perspective can be classified by4 Granularity 4 Historical background 4 Latency or Load Frequency 4 Reliability (Auditing)4 Target group 4 Information Retrieval Behavior

Information Perspective:Strategic – Tactical - Operational




Classic Data Warehousing and Operational Reporting

The classic data warehousing approach:Dedicated load and staging processesHigh qualityOptimized retrieval structuresReduce workload on OLTP systems

DataMart

Finance


DataMart

Logistic Tact

ical

/ St

rate

gic

MasterReference

DataTransaction

Data


Extra

ctio

n /

Ope

n St

agin

g

Question: And how do I handle my operational reporting in BW?


Operational Reporting in BW I

In most of the cases operational reporting needs can be supportedin BW using the classic data warehouse process

DataMart

Finance

ArchitectedArchitectedData Marts/ Data Marts/ ODSODS--ObjectsObjects

DataMart

Logistic

Tact

ical

/ St

rate

gic

/ Ope

ratio

nal

MasterReference

DataTransaction

Data


Extra

ctio

n /

Ope

n St

agin

g

ODS-ObjectSales Orders

‘..most of the cases..’: classical data warehousing has its limits if we want to provide event-level or close to event-level operational reporting

(near real time reporting) and/ orif we are confronted with huge data volumes for operational reporting




Operational Reporting and Classic DWH – Issues I

Be careful using the same InfoCube for operational and tactical reporting:if the transactional volume is highif you want to report on atomic data

last load

1 year old data

2 years old data

1/2 year old data

day

wee

k

mon

thqu

arte

rye

argranularity

tactical/ strategic

tactical/ strategichistory

notnotusedused

operational

operational

Granularity X

Granularity X

Granularity X

Source

PSA

DWHODS-Object

InfoCube

1:1

1:1

most atomicGranularity X


Operational Reporting and Classic DWH – Issues II

Solution A:

Granularity X

Granularity X

Granularity < X

Source

PSA

DWHODS-Object

InfoCube

1:1

Aggregation


Operational reporting could be done on the DWH-ODS-Object if just list reporting is desired.Tactical reporting is done on the InfoCube, which keeps aggregated dataDrill thru from InfoCube to atomic DWH-ODS-Objectis possible

Issues:• Responsibility ?• DWH-ODS-Object must be reporting enabled• Remember the guidelines for the BW DWH Layer




Operational Reporting and Classic DWH – Issues III

Dedicated InfoCubes/ ODS-Objects with atomic data support operational reporting

directly loaded from PSA life cycle of data normally not more than 6 monththey build an ‘ODS’

An InfoCube with aggregated data supports tactical reporting

Drill thru from InfoCube to detailed data Loading DWH layer several times per day and tactical InfoCube once per day

Mixing operational and tactical reporting using the same InfoCubesmay corrupt BW load and retrieval performance!

Solution B:

Granularity X

Granularity X

Granularity < XLife time >=2 Y

Source

PSA

DWHODS-Object

InfoCube


InfoCube or

ODS-Obj.

Granularity XLife time < 6M

ODSODS Data MartsData Marts

DWHDWH

integrated 1:1rules applied

1 - several loads per day

Aggregation1 load/ day

several loads/day


Transaction Data Processing and Operational Reporting

Customer Time DocNo Pos MaterialAmount Local

CurrencyA 20021107 1 10 4711 100


CurrencyA 20021107 1 10 4712 200A 20021107 2 10 4713 300


CurrencyA 20021107-10am 1 10 4711 100 New bookingA 20021107-3pm 1 10 4712 200 Correction bookingA 20021107-4pm 2 10 4713 300 New booking

Sourcesystem

Operational Data Store

4 times a day

after 11 am load

after 5 pm load




Transaction Data Processing in the BW DWH Layer


4713


Company Currency

A 4712 200211 100A 4713 200211 150



Local Currency

Amount Company Currency

A 20021107-10am 1 10 4711 100 50A 20021107-3pm 1 10 4712 200 100A 20021107-4pm 2 10 4713 300 150


Local Currency


Sourcesystem

BW Data Warehouse Layer

daily

weekly dailymonthly



InfoCube


BW and Operational/ Real Time Reporting

Key-Questions:

Data Latency ?Data Integration ?Workload on OLTP Systems ?

Ul

BWBW

OLTP

BW Data IntegrationBW Data Acquisition

BW Near real timedata warehousing

BW ‘classic’data warehousing

BW remoteInfoCube

BW virtualInfoCube

BW 4.0BW 4.0

Adaptors

JDBC ODBO XMLA SAP Query




Operational / Real Time Reporting

Scenario Data Latency Data Integration Workload on OLTP Systems

Classic BW 1- several loads Y 1 time

per day extraction workload

BW Remote 0 Y query workload

InfoCube

BW Virtual 0 Y query workload

InfoCube

BW near real time ∼ 0 Y 1 time

data warehousing extraction workload

BI direct access 0 N query workload


Data Warehousing and Historical Information

BW ArchitectedData Marts

BW Operational Data Store

BW Data Warehouse

volatilevolatilegranulargranular

nonnon--volatilevolatilegranulargranular

volatilevolatilesummarizedsummarized

The BW Data Warehouse is the only place to store historical informationcompleteunfilteredin various fashions

Various fashions of history → temporal data




SAP BW Layer Architecture - Overview

Finance

Logistic Ope

n D

istri

butio

n




MasterReference

Data


ODSODS



Extra

ctio

n /

Ope

n St

agin

gDataData

AcquisitionAcquisitionData Data

DeliveryDelivery

any

targ

et

Ext

ende

d St

ar S

chem

asE

xten

ded

Star

Sch

emas


MetaMeta DataData

any

sour

ce TransactionData



Agenda

Evolution of BW




Exercise

Discussion

Summary




Operational Data Models and the Data Warehouse Data Model

Not aligned operational data models Consistent enterprise data model

Inconsistent data warehouse data models ⇒stovepipe solutions

Consistent data warehouse data model ⇒long term success

A data warehouse A data warehouse consistency challengeconsistency challenge


SAP Applications and the BW Data Warehouse Data Model

Not aligned operational data models SAP enterprise data model

BW Consistent data warehouse data model forSAP Applications and others ⇒

long term success

A data warehouse A data warehouse consistency challengeconsistency challenge




Enterprise Data Model and BW Expandable Data Model (Business Content) I

Enterprise Data Model:e.g. mySAP Component

Object Models

0MATERIAL

Customer Material Sales Person

Sales Transaction

Material Group Sales ORGMaterial Type

0MATTYPE

0MATGR

BW Data Model0CUSTOMER

0DOCNO

0SALESPERS

0AMOUNT

0SALESORG

Entities, Attributes ->BW InfoObjects

operative entities and attributes ⇒ BW InfoObjects


BW Data Mart Data Model


Object Models


Object Models

0MATERIAL0MATGR0MATTYPE......

BW Data Mart Layer Data Model defined by

Business Content

BW Data Mart Layer Data Model defined by

Business Content


Sales Transaction


BCT InfoSources ≡Subject Areas

0SALESPERS0SALESORG.....

0CUSTOMER00CUSTGR......

0CUSTOMER 0MATERIAL 0AMOUNT

Shared/ Conformed DimensionsShared/ Conformed Dimensions InfoCubes with Local DimensionsInfoCubes with Local Dimensions

BCT Extractors/ DataSource

Master Data Transaction Data

0MATERIAL 0MATGR 0MATTYPE 0DOCNO 0SALESDAT 0CUSTOMER 0SALESPERS 0MATERIAL 0AMOUNT

BW Extended Star SchemasBW Extended Star Schemas




BW Data Warehouse Layer Data Model


Object Models


Object Models

BW Data Warehouse Layer Data Model defined by

Business Content

BW Data Warehouse Layer Data Model defined by

Business Content


Sales Transaction


BCT InfoSources ≡Subject Areas

BCT Extractors/ DataSource

Master Data Transaction Data

0MATERIAL 0MATGR 0MATTYPE 0DOCNO 0SALESDAT 0CUSTOMER 0SALESPERS 0MATERIAL 0AMOUNT

+ Insert Timestamp/ Activity Time Slice

+ Source

EDW InfoObject/ ODS-Object ODS-Object

If Overwrite:Unique Identifier

+Source


Agenda

Evolution of BW




Exercise

Discussion

Summary




BW Basic Topologies

‘Local’BW

‘Local’BW

‘Local’BW

GlobalBW


LandscapeLandscape

Others

EnterpriseBW



BW

SpokeBW

Others

SpokeBW

The Ideal Corporate BW Landscape ?

Strategic Corporate Implementation Options:Strategic Corporate Implementation Options:


Corporate BW Landscape Influencers

There is no ‘one size fits all’ BW landscape!

Organization •Centralized, headquarter oriented companies versus•Companies where local units have a large degree of independency

•Including all intermediate stagesPolitical issues – ownership of dataBusiness

•Companies with a unique business line•Companies with divisions totally diverse in business

Master data integration status and strategyAwareness of important role of Consistent InformationInvestmentSponsorship

•C-Level or otherIT strategy - Operative System LandscapeCompetitive pressure




BW as Central Enterprise Data Warehouse (EDW)

BW Enterprise Data Warehouse:BW Data Warehouse layer as ‘single point of truth’ for the entire corporation

EnterpriseBW



SpokeBW

SpokeBW


EDW Patterns

Organization is centralizedHeadquarter or division headquarter or even regional divisionheadquarter oriented companies

Often a single line of businessMaster data integration exist or at least a strategy to achieve itAwareness of the important role of Consistent Information is high

i.e. bad experience led to the conclusion that a data warehouse is more than a sexy front-end

Sponsorship is highHigh competitive market




Inside-Out: Central BW Instance Covers All

BW EDWBW EDW

BW Architected Data MartsBW Architected Data Marts

tactical/operational like

reporting

strategicreporting

integratednon-volatile

no flavor

Inside-Out:Central EDW BW Instance Covers All

aggregatedless granulargranular

PSAPSASt

agin

g Ar

ea

EnterpriseBW



SpokeBW

SpokeBW

BW ODSBW ODSoperational/ near real time

reportinggranular

External Data MartsExternal Data Marts


Inside-Out: Central EDW BW Instance and BW Spoke Instances

EnterpriseBW



SpokeBW

SpokeBW

BW EDWBW EDW

integratedconsistent

Inside-Out:Central EDW BW Instance as Information HUBArchitected Data Marts Spoke Instances

granular

strategicreporting

Corporate BWCorporate BWData Mart Data Mart

aggregated

PSAPSA

Stag

ing

Area tactical/

operational likereporting

BW ODSBW ODSoperational reporting

granular

BW ODSBW ODSoperational reporting

granular

PSAPSA

BW Architected Data MartsBW Architected Data Marts

tactical/operational like

reporting

less granular

aggregation

BW ODSBW ODSoperational/ near real time

reportinggranular

External Data MartsExternal Data Marts




BW Basic Topologies

‘Local’BW

‘Local’BW

‘Local’BW

GlobalBW


LandscapeLandscape

Others

EnterpriseBW



BW

SpokeBW

Others

SpokeBW

The Ideal Corporate BW Landscape ?

Strategic Corporate Implementation Options:Strategic Corporate Implementation Options:

‘Simply building a central single data warehouse does not address the size and complexity of the problem posed by the need for integrated, historical easily accessible data across a complex global enterprise.’W.H. Inmon ‘Managing Multiple Data Warehouse Development‘

‘Simply building a central single data warehouse does not address the size and complexity of the problem posed by the need for integrated, historical easily accessible data across a complex global enterprise.’W.H. Inmon ‘Managing Multiple Data Warehouse Development‘


Corporate Data Warehouse Landscape based on BW Local and Global Data Warehouses

Divisional

Global

..

BW Region EuropeEurope

Division AA

Local Sites

Regional

ERP/ Legacy

BW Region AsiaAsiaDivision AA

BW Region AmericasAmericas

Division AA

BW Region AsiaAsiaDivision BB

BW Division AA

BW Division BB

BW Headquarter

‘‘local’ BWslocal’ BWs

‘‘global’ BWsglobal’ BWs

..

ERP/ Legacy ERP/ Legacy ERP/ Legacy




The Role of a Global BW without a Central EDW

BWlocal

BWlocal

ERP/ Legacy

othersothers

BW GlobalBW Global

ERP/ Legacy Legacy/ Files

BWlocal

BWlocal

ERP/ Legacy ERP/ Legacy Legacy/ Files

BW EDWBW EDW

BWHeadquarter/

Global

BWHeadquarter/

Global

Focus on Headquarter ReportingFocus on Integration andHeadquarter Reporting

→ Global BW as the Corporate Integrator


Architected Multiple BW LandscapeD

ivisionalG

lobal

BWBW Europe

LocalR

egional Data Warehouse

ODSData Marts

Global BW Global BW Division AA

Global BWGlobal BWHeadquarter

R/3-ERPAmericas I

R/3-ERPAsia

R/3-ERPEurope I

mySAPCRM

Staging

BWBW Americas

Data Warehouse

ODSData Marts

Staging

R/3-ERPAmericas II

BWBW Asia

Data Warehouse

ODSData Marts

Staging

virtualBW Enterprise

Data WarehouseStaging




Agenda

Evolution of BW



Further Aspects of a Corporate BW StrategyApplication DevelopmentData Integration

Exercise

Discussion

Summary


Architected Multiple BW Landscape: BW Template Roll-Out

Divisional

Global

BWBW Europe

LocalR

egional Data Warehouse

ODSData Marts

Global BWGlobal BWHeadquarter

R/3-ERPAmericas I

R/3-ERPAsia

R/3-ERPEurope I

mySAPCRM

Staging

BWBW Americas

Data Warehouse

ODSData Marts

Staging

R/3-ERPAmericas II

BWBW Asia

Data Warehouse

ODSData Marts

Staging

virtualBW Enterprise

Data WarehouseStaging

Architected Multiple BW Landscape: BW Template Roll-Out

R/3 templateroll-out

BW templateroll-out

Global BW Global BW Division AA




Local BW Data Structures

Local BW Data Structures

corporate

local

distilled

source for source for corporate reportingcorporate reporting

standardized corporatecorporate defined

local reporting

local definedlocal definedlocal reportinglocal reporting


Content Delivery at the Customer SiteCustomer Content System (Headquarter)

Subsidiary

InfoObject0MATERIAL

InfoCube/ABC/IC1

InfoObject/ABC/IO01

InfoObject0MATERIAL

InfoCube/ABC/IC1

InfoObject/ABC/IO01

Export (D Version)

import

InfoCube/ABC/IC1

InfoObject/ABC/IO01




Agenda

Evolution of BW



Further Aspects of a Corporate BW StrategyApplication Development Data Integration

Exercise

Discussion

Summary


Master Data and Integration

0MATERIAL 0MATTYPE4711 A4712 B4713 A123 B124 B

MATNR MATTYPE MATNR MATTYPE MATNR MATTYPE4711 A 123 B 4711 C4712 B 124 B

0MATERIAL Master Data Table

R/3 system 1 -data already integrated

R/3 system 2 -data already integrated

R/3 system 3 -data not integrated but

mapping exist

apply mapping:MATNR: 4711 -> 4713MATTYPE: C -> A

The ideal world that anticipates the source systems either offer already integrated master data or that the master data can be mapped to common coded values during staging.

A major deliverable of data warehousing is integrated data regardless whether we talk about Architected Data Marts, ODS or the Data Warehouse layer itself.

SynchronousIntegration




Master Data Integration Issues

Entity values from source systems are not integrated and cannot be mapped atload time to common coded values

The most common reason is the lack of a corporate master data strategyOnly a delayed integration is possible i.e. at load time integrated values are not available until after a certain time spanDue to mergers and acquisitions new, not harmonized (common coded) sources

may appear

Best Practice: Discuss the Status and Strategy of Corporate Common Coding Efforts

The strategy and the status of corporate master data common coding shouldbe discussed at an early stage because of the huge influence to the BW information model and thus all scope implementations.

Checking InfoObject Integration Status

InfoObject Common Coded at Source Sites?

Integration in BW possible at load

time?0MATERIAL PARTLY NO0CUSTOMER NO NO0MATTYPE NO YES0COSTCENTER YES NO ACTION

......


InfoObject Concatenation

Key-value ConcatenationBW-Key

Concatenated-Key

Separator Attribute

U1004711 U1 AUDIU2004711 U2 BMW

U1 U2

004711 AUDI 004711 BMW

Source-System 1 Source-System 2

Build Key Build Key

Separator

Source-Value




InfoObject Automatic Compounding

Automatic Compounding> Source System-ID (0SOURSYSTEM) is Separator

BW-Key

Separator: 0SOURSYSTEM

Source-Key Attribute

U1 004711 AUDIU2 004711 BMW

U1 U2

004711 AUDI 004711 BMW

Source-System 1 Source-System 2

Build Key automatically

Source-Value

0SOURSYSTEM


BW and MDM : E.G. Content Integration

R/3 Material

R/3R/3 R/3

IS: Material

0MATERIAL

BWBW

Description of a master data object:Identifying AttributesOther application specific data

SourcesSources MDMMDM

Matching?

ID Mapping=

Load

0815

0816

0815

0815

0GUID

S1 4712

S3 4711

S2 4711

S1 4711

...0Material

IS: MDM ProductATTR+TEXT




Agenda

Evolution of BW




Exercise

Discussion

Summary


A Real Life BW Landscape Example

Sub-Division AR/3

Global

Sub-Division B R/3

only Germany

Sales EuropeR/3

BW Local

Global

HQBW

SubDiv BW

MDMS

BWS-America

BWAsia

BWN-America

BWSales Europe

Source Systems

....................DivR/3

DivR/3

DivR/3

12 systemsworldwide




Agenda

Evolution of BW




Exercise

Discussion

Summary


Summary

“Controlled Redundancy” is crucial for mid- and long-term success of all (BW) data warehousing activities

BW offers features in all areas to control redundancy

But, you need corporate BW proceedings and corporate BW guidelines that guarantee consistent usage of these features




Further Information

Public Web:www.sap.com biSAP Customer Services Network: www.sap.com/services/

Related Workshops/Lectures at SAP TechEd 2003BW 301, Data Aging with mySAP Business Intelligence, LectureOct. 2, 2003, 10 am, Room L2

Consulting ContactRoy Wood, VP SAP Consulting ([email protected])


Q&A

Questions?




Please complete your session evaluation anddrop it in the box on your way out.

Feedback

Thank You !

The SAP TechEd ’03 Basel Team


No part of this publication may be reproduced or transmitted in any form or for any purpose without the express permission of SAP AG. The information contained herein may be changed without prior notice.

Some software products marketed by SAP AG and its distributors contain proprietary software components of other software vendors.

Microsoft®, WINDOWS®, NT®, EXCEL®, Word®, PowerPoint® and SQL Server® are registered trademarks of Microsoft Corporation.

IBM®, DB2®, DB2 Universal Database, OS/2®, Parallel Sysplex®, MVS/ESA, AIX®, S/390®, AS/400®, OS/390®, OS/400®, iSeries, pSeries, xSeries, zSeries, z/OS, AFP, Intelligent Miner, WebSphere®, Netfinity®, Tivoli®, Informix and Informix® Dynamic ServerTM are trademarks of IBM Corporation in USA and/or other countries.

ORACLE® is a registered trademark of ORACLE Corporation.

UNIX®, X/Open®, OSF/1®, and Motif® are registered trademarks of the Open Group.

Citrix®, the Citrix logo, ICA®, Program Neighborhood®, MetaFrame®, WinFrame®, VideoFrame®, MultiWin® and other Citrix product names referenced herein are trademarks of Citrix Systems, Inc.

HTML, DHTML, XML, XHTML are trademarks or registered trademarks of W3C®, World Wide Web Consortium, Massachusetts Institute of Technology.

JAVA® is a registered trademark of Sun Microsystems, Inc.

JAVASCRIPT® is a registered trademark of Sun Microsystems, Inc., used under license for technology invented and implemented by Netscape.

MarketSet and Enterprise Buyer are jointly owned trademarks of SAP AG and Commerce One.

SAP, R/3, mySAP, mySAP.com, xApps, xApp and other SAP products and services mentioned herein as well astheir respective logos are trademarks or registered trademarks of SAP AG in Germany and in several other countries all over the world. All other product and service names mentioned are the trademarks of their respective companies.

Copyright 2003 SAP AG. All Rights Reserved

sap business information warehouse-based enterprise data warehouse … · 2019-11-12 · sap...

Documents