BW Basic Architecture

1
BW Basic Architecture

• Klaus MajenzSAP Product Line BI

2
Overview

• complete DW BI product comprising ...
• ETL tools (extractors transformation
  monitoring scheduling ...)
• OLAP engine
• data mining engine
• repository
• analytical front-end (web- or Excel-based
  agents GIS ...)
• prepacked models built by SAP application
  departments
• client-server architecture
• SAP web application servers
• database server 7 commercial RDBMS platforms
  supported (Oracle MS 4IBM SAP)
• part of SAP Netweaver
• SAPs open integration and application platform
• more details http//www.sap.com/solutions/netweav
  er/

3
Overview
4
Scenario (1)
5
Scenario (2)

• Dimension Time
• Day
• Month
• Year
• Dimension Region
• City
• Region
• Country
• Dimension Sales Org
• Sales Person
• Division
• Distribution Channel
• Sales Organization

• Dimension Product
• Product
• Product Group
• Key Figures
• Quantity (in pieces)
• Profit (in US)

6
An adequate BW Infocube IUSALES

• Dimension IUSALEST
• 0CALDAY
• 0CALMONTH
• 0CALYEAR
• Dimension IUSALES1
• IUCITY
• IUREGION
• IUCOUNTRY
• Dimension IUSALES2
• IUSALPER
• IUDIV
• IUDCHAN
• IUSALORG

• Dimension IUSALES3
• IUPROD
• IUPRODGRP
• Key Figures
• IUQUAN
• IUPROFIT

7
Data Flow in BW
Aggregate
Initial Fill Roll-Up
Infocube E fact table
Cube Query
Compression
Infocube F fact table
Infocube Upload (from PSA)
Infocube Upload (from ODS)
Operational Data Store (ODS)
ODS Query
ODS Activate
ODS Upload
Persistent Staging Area (PSA)
V.P. Query
Extraction
Source System (e.g. R/3 other DB File ...)
V.P. Query
8
Data Flow in BW what we will look at
Aggregate
Initial Fill Roll-Up
Infocube E fact table
Infocube
Cube Query
Compression
Infocube F fact table
Infocube Upload (from PSA)
Operational Data Store (ODS)
ODS
ODS Activate
ODS Upload
Persistent Staging Area (PSA)
PSA
Extraction
Source System (e.g. R/3 other DB File ...)
9
PSA
10
PSA table

• huge number of individual INSERTs
• no UPDATE
• SELECT FROM WHERE REQUEST
• mass deleteion DELETE WHERE PARTNO /
  DROP PARTITION

11
ODS
12
ODS object 3 tables

• active data /BIC/AOIUSALES00
• modified data (activation queue)
  /BIC/AOIUSALES40
• delta data (change log) /BIC/B0008215000
  (PSA)
• ODS upload
• INSERT INTO /BIC/AOIUSALES40
• ODS data activation
• UPSERT /BIC/AOIUSALES00
• delta records INSERT INTO /BIC/B0008215000
• (mass) DELETE FROM /BIC/AOIUSALES40
• infocube delta upload from ODS
• SELECT FROM /BIC/B0008215000

13
ODS tables /BIC/AOIUSALES00 /BIC/AOIUSALES40
active data
same as in PSA table
modified data
14
ODS Object (BW 3.0)
Active data
Change log
Req.ID I Pack.ID I Rec.No
Doc.No I Value
Activation
Activation queue
Req1
Req2
Req3
Staging Engine
15
Infocube
16
InfoCube Star Schema
(1) Fact Table (2) Dimension (3)
time-independent-SID time-dependent-SID
master SID Char (4) SID Attr
Y
X
S
F E
D
S
17
Infocube IUSALES
Facttable
Dimension 1
S (Population)
X (City)
18
Infocube Indexing (1) Oracle
line item dimension
Facttable
Dimension 1
S (Population)
X (City)
19
Infocube Indexing (2) MS SQL Server
line item dimension
Facttable
Dimension 1
S (Population)
X (City)
20
Infocube Indexing (3) Oracle
F Facttable
partitioning column (for E facttable)
E Facttable
P-index

• single column indexes support queries
• P-index compress
• additional bitmap index on part. column

21
Infocube Indexing (4) MS SQL Server
F Facttable
Does not exist on MS-SQL
E Facttable
P-index

• single column indexes support queries
• P-index compress

22
Infocube Operations (1)

• INSERT only F facttable
• array INSERT
• if array INSERT fails UPSERT logic
• DELETE request (mass deletion) only F facttable
• DELETE FROM /BIC/FIUSALES WHERE KEY_IUSALESP
  
• alternatively DROP PARTITION
• DELETE specified data
• DELETE FROM WHERE
• UPSERT only E facttable
• infocube compression (separate)
• SELECT
• separate

23
Infocube Compression (ex. request 3)
before
after
24
Infocube Compression (2)

• Oracle (via stored procedure on DB server)
• loop over rows for request REQ in F facttable
• attempt UPDATE of E facttable
• if UPDATE fails then INSERT rowid into temporary
  table INS
• do mass INSERT INTO E facttable using INS
• DROP PARTITION corresponding to REQ in F
  facttable
• MS SQL-Server (via ABAP via application server)
• loop over rows for request REQ in F facttable
• attempt UPDATE of E facttable
• if UPDATE fails then attempt INSERT
• DELETE FROM F facttable WHERE requestid REQ

25
Aggregate Fill

• INSERT INTO /BIC/E100010
• SELECT D1.SID_IUCITY AS KEY_1000101
• D2.SID_IUSALPER AS KEY_1000102
• 0 AS KEY_100010P
• SUM (F./BIC/IUPROFIT)
• SUM (F./BIC/IUQUAN)
• COUNT() AS FACTCOUNT
• FROM /BIC/FIUSALES F
• /BIC/DIUSALES1 D1
• /BIC/DIUSALES2 D2
• /BIC/DIUSALESP DP
• WHERE F.KEY_IUSALES1 D1.DIMID AND
• F.KEY_IUSALES2 D2.DIMID AND
• F.KEY_IUSALESP DP.DIMID AND
• DP.SID_0CHNGID 0 AND
• ( F.KEY_IUSALESP 0 OR
  F.KEY_IUSALESP 2 ) AND
• DP.SID_0REQUID BETWEEN 0 AND 40
• GROUP BY D1.SID_IUCITY
• D2.SID_IUSALPER

26
Aggregate Roll-Up

• INSERT INTO /BIC/F100011
• SELECT D1.SID_IUCITY AS KEY_1000111
• D3.SID_IUPROD AS KEY_1000112
• 7 AS KEY_100011P
• SUM (F./BIC/IUPROFIT)
• SUM (F./BIC/IUQUAN)
• COUNT() AS FACTCOUNT
• FROM /BIC/FIUSALES F
• /BIC/DIUSALES1 D1
• /BIC/DIUSALES3 D3
• /BIC/DIUSALESP DP
• WHERE F.KEY_IUSALES1 D1.DIMID AND
• F.KEY_IUSALES3 D3.DIMID AND
• F.KEY_IUSALESP DP.DIMID AND
• DP.SID_0CHNGID 0 AND
• F.KEY_IUSALESP 5 AND
• DP.SID_0REQUID 498
• GROUP BY D1.SID_IUCITY
• D3.SID_IUPROD

27
Infocube Query Example Infocube IUSALES
city
region
country
(1) Fact Table (2) Dimensions (3)
Characteristics (simplified)
sales person
day
division
month
distribution channel
year
sales organization
product
product group
28
Query Example Processing (under Oracle)
region
country US
(1) Fact Table (2) Dimensions (3)
Characteristics (simplified)
month
year 98-99
product group
29
Step 1 Restrictions Master Data è Dimensions
region
country US
(1) Fact Table (2) Dimensions (3)
Characteristics (simplified)
month
year 98-99
product group
Typical Query Processing
30
Step 2 Restrictions Dimensions è Fact Table
(1) Fact Table (2) Dimensions (3)
Characteristics (simplified)
bitmap index
bitmap index
product group
Typical Query Processing
31
Step 3 Assemble Result
region
country US
(1) Fact Table (2) Dimensions (3)
Characteristics (simplified)
small subset of facttable
month
year 98-99
product group
Typical Query Processing
32
Query Example (1) simple

• SELECT DT.SID_0CALMONTH AS S____081
• DT.SID_0CALYEAR AS S____083
• D1.SID_IUCOUNTRY AS S____520
• D3.SID_IUPRODGRP AS S____524
• COUNT( ) AS 1ROWCOUNT
• SUM ( F./BIC/IUPROFIT ) AS
  IUPROFIT
• SUM ( F./BIC/IUQUAN ) AS IUQUAN
• FROM /BIC/FIUSALES F
• /BIC/DIUSALEST DT
• /BIC/DIUSALES1 D1
• /BIC/DIUSALES3 D3
• /BIC/DIUSALESP DP
• WHERE F.KEY_IUSALEST DT.DIMID AND
• F.KEY_IUSALES1 D1.DIMID AND
• F.KEY_IUSALES3 D3.DIMID AND
• F.KEY_IUSALESP DP.DIMID AND
• ( DT.SID_0CALMONTH 200007 AND
• DT.SID_0CALYEAR 2000 AND
• DP.SID_0REQUID lt 745 )

33
Query Example (2) navigational attribute

• SELECT DT.SID_0CALMONTH AS S____081
• DT.SID_0CALYEAR AS S____083
• D1.SID_IUCOUNTRY AS S____520
• X1.S__IUCOLOR AS S____530
• COUNT( ) AS 1ROWCOUNT
• SUM ( F./BIC/IUPROFIT ) AS
  IUPROFIT
• SUM ( F./BIC/IUQUAN ) AS IUQUAN
• FROM /BIC/FIUSALES F
• /BIC/DIUSALEST DT
• /BIC/DIUSALES1 D1
• /BIC/DIUSALES3 D3
• /BIC/XIUPROD X1
• /BIC/DIUSALESP DP
• WHERE F.KEY_IUSALEST DT.DIMID AND
• F.KEY_IUSALES1 D1.DIMID AND
• F.KEY_IUSALES3 D3.DIMID AND
• D3.SID_IUPROD X1.SID AND
• F.KEY_IUSALESP DP.DIMID AND
• ( DT.SID_0CALMONTH 200007 AND
  DT.SID_0CALYEAR 2000 AND

34
Query Example (3) external hierarchy

• SELECT DT.SID_0CALYEAR AS S____083
• DT.SID_0CALMONTH AS S____081
• D1.SID_IUCOUNTRY AS S____520
• H1.PRED AS S____524
• COUNT( ) AS 1ROWCOUNT
• SUM ( F./BIC/IUPROFIT ) AS
  IUPROFIT
• SUM ( F./BIC/IUQUAN ) AS IUQUAN
• FROM /BIC/FIUSALES F
• /BIC/DIUSALES3 D3
• /BIC/DIUSALEST DT
• /BIC/DIUSALES1 D1
• /BIC/DIUSALESP DP
• /BI0/0300148611 H1 / This is a
  (UNION) view! /
• WHERE F.KEY_IUSALES3 D3.DIMID AND
• F.KEY_IUSALEST DT.DIMID AND
• F.KEY_IUSALES1 D1.DIMID AND
• F.KEY_IUSALESP DP.DIMID AND
• D3.SID_IUPRODGRP H1.SUCC AND
• ( DT.SID_0CALYEAR 2000 AND
  DP.SID_0REQUID lt 745 AND

35
Examples of Conceptual Modeling in SAP BW
36
Examples

• Reveal why pure RDBMS technology ...
• sometimes requires an additional conceptual
  layer on top
• is not sufficient is some cases
• has no chance in some situations because it has
  to be more general than necessary.
• Examples
• example 1 infoproviders in SAP BW
• uniform view on differing physical layouts
• example 2 non-cumulative key figures in SAP BW
• semantic relationship between table columns
• example 3 aggregates in SAP BW
• could be implemented by using materialized views
  (or equivalent)
• but they have proved to be inferior

37
Example 1 Infoprovider (1)

• An infoprovider in SAP BW ...
• comprises a reporting scenario
• is the entity on which a query is defined
• combines (aggregated or non-aggregated)
  operational data with master data (e.g. product
  customer ... data)
• or constitutes a master data entity

38
Example 1 Infoprovider (2) Examples

• Example A
• a cube is an infoprovider
• fact table holds operational data on certain
  granularity
• dimensions hold master data
• Example B
• customer master data can be an infoprovider
• same UI as for other infoproviders
• selections projections summaries using
  attributes (e.g. address customer category
  region ...)

39
Example 1 Infoprovider (3) -- Overview (SAP BW
3.x)
Infoprovider
40
Example 2 Non-Cumulative Key Figures (1)

• also semi-additive measures
• example account balance
• conceptually

• physically

41
Example 2 Non-Cumulative Key Figures (2)

• non-cumulative key figures / semi-additive
  measures
• balance can be reconstructed for any moment in
  the past
• that information has not to be physically
  stored
• advantages
• significantly reduced data volumes
• better performance
• more flexibility
• however algorithms are required for
• reconstruction read
• insertion load

42
Example 3 Aggregates in SAP BW

• SAP BW constraints
• only SUM MIN MAX aggregations are materialized
• uploaded data (in an infocube) can still be
  identified
• delta roll-ups are simple
• Materialized or Indexed Views / Automatic Summary
  Tables
• could be used in theory
• however maintenance is considerably slower
• ... due to expensive tracking and logging
  mechanisms that are necessary if the general case
  has to be covered

43
Summary
44
Summary

• brief introduction to SAP BW
• three examples
• an additional conceptual layer on top of the
  relational one
• a semantical pattern that is frequently used in
  business
• an object that might suffer from the generic
  approach
• Do the examples reveal shortcomings of RDBMS or
  are they application domain specific