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
• SAP's 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 slide)
• SELECT
• separate slide

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

account day balance delta
A 29-Sep 100 10
B 29-Sep 500  
A 30-Sep 110  
B 30-Sep 500 - 100
A 1-Oct 110  
B 1-Oct 400  
A 2-Oct 110  
B 2-Oct 400  
A 3-Oct 110 - 60
B 3-Oct 400  
A 4-Oct 50  
B 4-Oct 400  
account day ref point delta
A 29-Sep no  10
B 30-Sep no  - 100
A 3-Oct no  - 60
A 4-Oct yes 50
B 4-Oct yes 400
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 ?