Download - Non Cumulative
-
8/3/2019 Non Cumulative
1/97
Non-cumulatives /Stock Handling
SAP AG
-
8/3/2019 Non Cumulative
2/97
SAP AG 2004, Non-cumulatives / Stock Handling, 2
Agenda
1. Definition
2. Creation
3. Loading and compression
4. Validity table
5. Query execution
6. Modeling guidelines
7. Information resources
-
8/3/2019 Non Cumulative
3/97
SAP AG 2004, Non-cumulatives / Stock Handling, 3
Definition
Cumulative values are those key figures that are cumulated using all
characteristics, thus also using time.
Non-cumulative values are those key figures that are measured in
relation to a period in time; that is to say they cannot be meaningfully
cumulated over time. Non-cumulative values are summarized over
time using so-called exception aggregation.
Example: stock quantities/-values, number of employees,account balances, etc.
Example: sales revenue, weight, etc.
-
8/3/2019 Non Cumulative
4/97
SAP AG 2004, Non-cumulatives / Stock Handling, 4
Agenda
1. Definition
2. Creation
3. Loading and compression
4. Validity table
5. Query execution
6. Modeling guidelines
7. Information resources
-
8/3/2019 Non Cumulative
5/97
SAP AG 2004, Non-cumulatives / Stock Handling, 5
Creation
Key figures
InfoCubes
Aggregates
MultiCubes
-
8/3/2019 Non Cumulative
6/97
SAP AG 2004, Non-cumulatives / Stock Handling, 6
Creation of non-cumulative key figures
Two possibilities: With delta handling
With separate in- and outflow
Specification of excpt.
aggregat.:
MIN
MAX
FIRST
LAST
AVG (number of days)
AVG (number of working days)
corresponding objects have to:
be defined before
match the type / unit
have aggregation SUM / SUM
must not be used by other NCUM
key figures
-
8/3/2019 Non Cumulative
7/97
SAP AG 2004, Non-cumulatives / Stock Handling, 7
Types of non-cumulative key figures
Different types of non-cumulative key figures
"Non-cumulativekey figure"
= cumulative keyfigure with excep.
aggregation
Non-cumulativekey figure with
associated singledelta (cumulative)
Non-cumulativekey figure with
associated in- andoutflow
(cumulative)
.
The non-cumulative key figure is used in the definition ofthe InfoCube, but does NOT exist in the facttable. In thefact table only the delta or inflow/outflow key figuresexist.
+/- + -
-
8/3/2019 Non Cumulative
8/97
SAP AG 2004, Non-cumulatives / Stock Handling, 8
Creation
Key figures
InfoCubes
Aggregates
MultiCubes
-
8/3/2019 Non Cumulative
9/97
SAP AG 2004, Non-cumulatives / Stock Handling, 9
Creation of non-cumulative InfoCubes
An InfoCube is called NCUM
InfoCube if it contains at least one
NCUM key figure
NCUM key figure can be used
exactly as a normal key figure
corresponding CUM key figures are
added automatically (these key
figures must not be removed from
the InfoCube)
Special treatment for NCUM
InfoCubes:
time reference characteristic
validity objects
-
8/3/2019 Non Cumulative
10/97
SAP AG 2004, Non-cumulatives / Stock Handling, 10
An NCUM InfoCube can contain several time characteristics, but there must be
a time reference characteristic (checked during InfoCube activation)
time reference characteristic can be determined using the following
hierarchy:
incomplete time characteristics like e.g. 0CALMONTH2, 0CALQUART1,...
can be used but can not be time reference characteristic ("normal
characteristics")
Time reference characteristic
A time reference characteristic is a time characteristic which determines the
values of all other time characteristics
0CALDAY 0CALWEEK
0CALYEAR0CALMONTH
0FISCPER 0FISCYEAR
-
8/3/2019 Non Cumulative
11/97
SAP AG 2004, Non-cumulatives / Stock Handling, 11
Update rules
InfoCube 0IC_C03
InfoSource 2LIS_03_BF (Movements)InfoSource 2LIS_03_BX (Stocks)
Example:
Stock key figure:0BLOCKEDSTK (blocked stock)
Assigned movement key figures:
0ISSBLOSTCK (Issued quantity blocked stock) =outflow
0RECBLOSTCK (Received quantity blocked stock) =inflow
0CPQUABU from 2LIS_03_BX updates0RECBLOSTCK
in 0IC_C03 (Receipt)
0CPQUABU from 2LIS_03_BFupdates
0RECBLOSTCK or 0ISSBLOCSTCK in0IC_C03 (Receipt or Issue)
Stock key figures 0BLOCKEDSTK is not a InfoObject (field) on the InfoCube.During the creation of the opening balance the initial stock (0CPQUABU from 2LIS_03_BX) is updatedinto the inflow key figure (0RECBLOSTCK in 0IC_C03).
So the stock key figure is never available in update rules!
Please note that the application examples in these slides are based on
the R/3 Inventory Management use case! But all the technics are alsodeployable for other scenarios (accounts, headcounts, etc.).
-
8/3/2019 Non Cumulative
12/97
SAP AG 2004, Non-cumulatives / Stock Handling, 12
Creation
Key figures
InfoCubes
Aggregates
MultiCubes
-
8/3/2019 Non Cumulative
13/97
-
8/3/2019 Non Cumulative
14/97
SAP AG 2004, Non-cumulatives / Stock Handling, 14
Creation
Key figures
InfoCubes
Aggregates
MultiCubes
-
8/3/2019 Non Cumulative
15/97
SAP AG 2004, Non-cumulatives / Stock Handling, 15
NCUM InfoCubes can be used in a MultiCube
Restrictions:
the time-dimension of a MultiCube have to match exactly the
time-dimension of every NCUM InfoCube which is part of the MultiCube
all NCUM InfoCubes which belong to the same MultiCube have
to have identical time-dimensions
the time-dimension of normal InfoCubes have to contain at least the
time-characteristics of the MultiCube
MultiCubes
BW 2.x
Aggregation over multicubes / validity objects is consistent
Time-dimension of NCUM-InfoCubes can differ, but there must be one reference time
characteristic for the MultiCube
Example: C1 contains 0CALDAY, C2 contains 0CALWEEK and C3 contains 0CALMONTH
as reference time characteristic.
M1 = C1 + C2 + C3 is allowed but
M2 = C2 + C3 is not allowed
BW 3.x
-
8/3/2019 Non Cumulative
16/97
SAP AG 2004, Non-cumulatives / Stock Handling, 16
MultiCubes and Inventory InfoCubes in BW 3.0
No limitation concerning the time dimensions of various
InfoCubes that should be combined in a MultiCube.
Example I: (Week and Month)
Week Year Material Inflow
51 2001 4711 10
52 2001 4711 20
1 2002 4711 10
2 2002 4711 30
Month Year Material Inflow
Dez 2001 4711 20
Jan 2002 4711 50
Week in [51.2001 02.2002]
Week Stock Stock Cube1 Stock Cube2
51 10 10
52 30 30
1 40 40
2 70 70
Month Stock Stock Cube1 Stock Cube2
Dez 20 20
Jan 70 70
Month in [Dez.2001 Jan.2002]
Year Stock Stock Cube1 Stock Cube2
2001 50 30 20
2002 140 70 70
Year in [2001 2002]
-
8/3/2019 Non Cumulative
17/97
SAP AG 2004, Non-cumulatives / Stock Handling, 17
Agenda
1. Definition
2. Creation
3. Loading and compression
4. Validity table
5. Query execution
6. Modeling guidelines
7. Information resources
-
8/3/2019 Non Cumulative
18/97
SAP AG 2004, Non-cumulatives / Stock Handling, 18
Loading and Compression
Data/InfoSources
InfoPackage
Update rules
Business Content Data/InfoSources
Compression
Upload scenario
Reference point (marker)
-
8/3/2019 Non Cumulative
19/97
SAP AG 2004, Non-cumulatives / Stock Handling, 19
Data/InfoSources
Two Data/InfoSources possible:
Loading of initialization: Only ncum key figures (optional)
Loading of deltas: Only cum key figures
Remarks / Restrictions:
Initialization is not necessary
Initialization has to take place before loading of deltas
For every key combination only one initialization is possible Loading of initialization via delta DataSource is possible
-
8/3/2019 Non Cumulative
20/97
SAP AG 2004, Non-cumulatives / Stock Handling, 20
DataSources for initialization
R/3 DataSource:
flag is loaded from OLTP
Flat-file DataSource:flag has to be set explicitly
-
8/3/2019 Non Cumulative
21/97
SAP AG 2004, Non-cumulatives / Stock Handling, 21
Loading and Compression
Data/InfoSources
InfoPackage
Update rules
Business Content Data/InfoSources
Compression
Upload scenario
Reference point (marker)
-
8/3/2019 Non Cumulative
22/97
SAP AG 2004, Non-cumulatives / Stock Handling, 22
InfoPackage for initialization
Special conversions:
value of the reference time
characteristic is set to the maximum
value (e. g. 99991231)
value of all the other time
characteristics are automatically
derived from this
technical infoobject 0RECORDTP is
set to 1
Do not change these fields in the
update rules by yourself!
-
8/3/2019 Non Cumulative
23/97
SAP AG 2004, Non-cumulatives / Stock Handling, 23
Loading and Compression
Data/InfoSources
InfoPackage
Update rules
Business Content Data/InfoSources
Compression
Upload scenario
Reference point (marker)
-
8/3/2019 Non Cumulative
24/97
SAP AG 2004, Non-cumulatives / Stock Handling, 24
Update rules
Recommendation:
Because of the necessity to derive all
other time characteristics from the
reference time characteristic, only thereference time characteristic should be
filled via the InfoSource and all other
time characteristics should be
computed via automatic time
conversion.
-
8/3/2019 Non Cumulative
25/97
SAP AG 2004, Non-cumulatives / Stock Handling, 25
Loading and Compression
Data/InfoSources
InfoPackage
Update rules
Business Content Data/InfoSources
Compression
Upload scenario
Reference point (marker)
-
8/3/2019 Non Cumulative
26/97
SAP AG 2004, Non-cumulatives / Stock Handling, 26
Business Content Data/InfoSources
- 2LIS_03_BX Material stocks
- 2LIS_03_BF Material movements
- 2LIS_03_UM Revaluations
BW 3.0
-
8/3/2019 Non Cumulative
27/97
SAP AG 2004, Non-cumulatives / Stock Handling, 27
Standard R/3 Inventory Management in BW
2LIS_03_BFMaterial Movements
(as of BW 2.0B)
2LIS_03_UMRevaluations
(as of BW 2.0B)
2LIS_03_BXMaterial Stock
(as of BW 2.0B)
Initial Stock transfer
Material Movements
Revaluations
0IC_C03Material Movements
0IC_MC01MultiCube
Slow Movers
0IC_MC02MultiCubeDays of Supply
components
0PP_C02Period-Specific
Order/Material View
0MATERIALMaterial Master Data
0IC_MC03MultiCube
Days of Supply WIP
0PP_C05Plan/Actual
Comparison MaterialConsumption
BW 3.0
-
8/3/2019 Non Cumulative
28/97
SAP AG 2004, Non-cumulatives / Stock Handling, 28
Other BW Content Areas in Inventory Management
1. Consumer Products: 0CP_IC_C1
2. Retail: 0RT_C35-39
3. AFS: 0AFMM_C02
Old: InfoCubes 0IC_C01/02 and Data/InfoSource2LIS_40_S278
-
8/3/2019 Non Cumulative
29/97
SAP AG 2004, Non-cumulatives / Stock Handling, 29
Loading and Compression
Data/InfoSources
InfoPackage
Update rules
Business Content Data/InfoSources
Compression
Upload scenario
Reference point (marker)
C
-
8/3/2019 Non Cumulative
30/97
SAP AG 2004, Non-cumulatives / Stock Handling, 30
Compression
Reference point (marker)Start point for all calculations - when building an inventory scenario
the first reference point might be the opening balance.
Technically, the reference point is the value per characteristiccombination after having compressed your Info Cube.
When compressing an InfoCube containing non-cumulatives, thereference point is (per default) updated.
"normal" compression, i.e. with update of reference point Requests are moved to request 0
Reference point is updated
If all requests are compressed actual value can be computed by onlyreading the reference point
Compression without update of reference point The requests are only moved to request 0
Useful to load historic data into an InfoCube
Request(s) with historical data must be compressed, otherwise you getwrong query results
Wh t d fi th t f i ?
-
8/3/2019 Non Cumulative
31/97
SAP AG 2004, Non-cumulatives / Stock Handling, 31
Where to define the type of compression?
Administrator Workbench InfoProvider (InfoCube) Manage
L di d C i
-
8/3/2019 Non Cumulative
32/97
SAP AG 2004, Non-cumulatives / Stock Handling, 32
Loading and Compression
Data/InfoSources
InfoPackage
Update rules
Business Content Data/InfoSources
Compression
Upload scenario
Reference point (marker)
O er ie Steps for the pload
-
8/3/2019 Non Cumulative
33/97
SAP AG 2004, Non-cumulatives / Stock Handling, 33
Overview: Steps for the upload
Activate extract structures MC03BF0/MC03UM0 for correspondingDataSources 2LIS_03_BF/2LIS_03_UM in the logistics cockpit.
Initialization of the opening balance in the R/3 source system(DataSource 2LIS_03_BX).
Setup of the historical material movements/revaluations in the R/3source system (DataSources 2LIS_03_BF/2LIS_03_UM).
Loading the opening balance (InfoSource 2LIS_03_BX), compressionwith marker update (compression mandatory).
Loading the historical movements (InfoSource 2LIS_03_BF) andrevaluations (InfoSource 2LIS_03_UM), compression without marker(compression mandatory).
Setup V3 update in R/3 system.
Upload deltas subsequently (InfoSources 2LIS_03_BF/2LIS_03_UM),compression with marker update (compression optional, butrecommended for performance reasons).
Activate extract structures in logistics cockpit
-
8/3/2019 Non Cumulative
34/97
SAP AG 2004, Non-cumulatives / Stock Handling, 34
Activate extract structures in logistics cockpit
R/3 source system LO cockpit (transaction LBWE)
Initialization of the opening balance
-
8/3/2019 Non Cumulative
35/97
SAP AG 2004, Non-cumulatives / Stock Handling, 35
Initialization of the opening balance
R/3 source system transaction MCNBAlways currents stockNo postings during this run
Setup of the historical movements/revaluations
-
8/3/2019 Non Cumulative
36/97
SAP AG 2004, Non-cumulatives / Stock Handling, 36
Setup of the historical movements/revaluations
R/3 source system transactions OLI1BW (material movements) andOLIZBW (revaluations)By selections it has to be guaranteed that the values of the documents
lie before the initialization run for the opening balance (if still nopostings take place all documents can be selected).
Situation after these steps in the R/3 source system
-
8/3/2019 Non Cumulative
37/97
SAP AG 2004, Non-cumulatives / Stock Handling, 37
Situation after these steps in the R/3 source system
Past Opening Actual
balance
Time
MC03BF0SETUP
MC03UM0SETUPVBDATA
2LIS_03_BX
Loading the opening balance (InfoSource 2LIS 03 BX)
-
8/3/2019 Non Cumulative
38/97
SAP AG 2004, Non-cumulatives / Stock Handling, 38
Loading the opening balance (InfoSource 2LIS_03_BX)
BW system InfoPackage for InfoSource
2LIS_03_BX
Generata Initial Status
-
8/3/2019 Non Cumulative
39/97
SAP AG 2004, Non-cumulatives / Stock Handling, 39
Generata Initial Status
Example II (PSA):
Material 0CALDAY Initial stock
4711 01 Jun 2002 1000
4712 01 Jun 2002 2000
Validity table:
0REQID || 0CALDAY FROM 0CALDAY TO
-1 || 20020601 20020601
1234 || 20020601 20020601
InfoCube:
Material RT 0CALDAY Inflow kyf 0REQID
4711 1 31.12.9999 1000 1234
4712 1 31.12.9999 2000 1234
Note:
- For simplification without plant!- Initial stock will be updated into thereceipt movement key figure!
This mode is only available if it is a stock InfoCube. A stock
InfoCube has a validity table. This is created by theaddition of a stock key figure in the InfoCube definition.
Rt = Record type
Kyf = Key figure
/BI0/L or
/BIC/L
(transaction RSDV)
Compression of this request with marker update
-
8/3/2019 Non Cumulative
40/97
SAP AG 2004, Non-cumulatives / Stock Handling, 40
Compression of this request with marker update
BW system Inventory InfoCube Manage
Loading the historical movements/revaluations(InfoSources 2LIS 03 BF/2LIS 03 UM)
-
8/3/2019 Non Cumulative
41/97
SAP AG 2004, Non-cumulatives / Stock Handling, 41
(InfoSources 2LIS_03_BF/2LIS_03_UM)
BW system InfoPackages for InfoSources 2LIS_03_BF/2LIS_03_UM
Initialize delta process
-
8/3/2019 Non Cumulative
42/97
SAP AG 2004, Non-cumulatives / Stock Handling, 42
p
Example II (PSA):
Material 0CALDAY Inflow Outflow
4711 04 Jun 2002 100
4711 05 Jun 2002 50
4712 07 Jun 2002 200
Validity table :
0REQID || 0CALDAY FROM 0CALDAY TO
-1 || 20020601 20020607
1234 || 20020601 20020601
1235 || 20020604 20020607
InfoCube:
Material RT 0CALDAY 0CALMONTH Inflow kyf Outflow kyf 0REQID4711 1 31.12.9999 0 1000 1234
4712 1 31.12.9999 0 2000 1234
4711 04.06.2002 06.2002 100 1235
4711 05.06.2002 06.2002 50 1235
4712 07.06.2002 06.2002 200 1235
Movements can be initialized and then provided with deltas.
Compression of these requests without markerupdate
-
8/3/2019 Non Cumulative
43/97
SAP AG 2004, Non-cumulatives / Stock Handling, 43
update
BW system Inventory InfoCube Manage
Setup V3 control in R/3 source system
-
8/3/2019 Non Cumulative
44/97
SAP AG 2004, Non-cumulatives / Stock Handling, 44
R/3 source system LO cockpit (transaction LBWE)Prerequisite: successful initialization of DataSources 2LIS_03_BF and2LIS_03_UM (check with transaction RSA7 after this new postings can
take place)
Upload deltas subsequently (InfoSources2LIS 03 BF and 2LIS 03 UM)
-
8/3/2019 Non Cumulative
45/97
SAP AG 2004, Non-cumulatives / Stock Handling, 45
2LIS_03_BF and 2LIS_03_UM)
BW system InfoPackages for InfoSources 2LIS_03_BF/2LIS_03_UM
Compression of these requests with marker update
-
8/3/2019 Non Cumulative
46/97
SAP AG 2004, Non-cumulatives / Stock Handling, 46
BW system Inventory InfoCube Manage
Loading and Compression
-
8/3/2019 Non Cumulative
47/97
SAP AG 2004, Non-cumulatives / Stock Handling, 47
Data/InfoSources
InfoPackage
Update rules
Business Content Data/InfoSources
Compression
Upload scenario
Reference point (marker)
Reference point (marker): Scenario
-
8/3/2019 Non Cumulative
48/97
SAP AG 2004, Non-cumulatives / Stock Handling, 48
Example III:
- 31.03.2002
- You have setup an opening balance of 100 units on01.01.2002 and loaded it into the stock InfoCube.
- Historical material movements from the three previousmonths (October 2001:10 units; November 2001: 20 units;December 2001: 10 units) are loaded into the BW.
- Since this point, successive material movements have beentransferred into the BW in the delta process. Delta requeststransferred at the end of January (20 units) and February (10units) were already compressed after successful validation, the
last delta request from the end of March (10 units) is still in theInfoCube in uncompressed form.
After upload of the opening balance
-
8/3/2019 Non Cumulative
49/97
SAP AG 2004, Non-cumulatives / Stock Handling, 49
Sample:
last value = 20 + 10 + 5 = 35
value in January = 35 - 5 = 30
Sample:
last value = 35
value in January = 35 - 5 = 30
Time
100
Referencepoint 1
Oct Nov Dec Jan Feb Mar Time
-
8/3/2019 Non Cumulative
50/97
After upload of the deltas for material movements
-
8/3/2019 Non Cumulative
51/97
SAP AG 2004, Non-cumulatives / Stock Handling, 51
+10
+20
Time
130
Referencepoint 2
Oct Nov Dec Jan Feb Mar Time
+10
Compressed requests
+10+10+20
Agenda
-
8/3/2019 Non Cumulative
52/97
SAP AG 2004, Non-cumulatives / Stock Handling, 52
1. Definition
2. Creation
3. Loading and compression
4. Validity table
5. Query execution
6. Modeling guidelines
7. Information resources
Motivation
-
8/3/2019 Non Cumulative
53/97
SAP AG 2004, Non-cumulatives / Stock Handling, 53
For normal key figures: the query result only contains entries which have
been loaded into the InfoCube before.
As the NCUM value of a key figure is computed by reading a reference point
and then using a backward computation with the deltas, it is possible to
compute a value for every point in time. Define an interval which limits the validity (by default the minimal and
the maximal value of the time characteristic of all entries which have been
loaded into the InfoCube)
Sometimes a single interval is not sufficient (besides the most detailed time
characteristic, additional characteristics for the InfoCube can be included in
the validity table): plan/actual indicator (the plan values are valid for the whole year, actual values
are loaded monthly) = add the plan-actual sign to the validity table) plants, or branches which open resp. close at different points in time or
plants, or branches which deliver their data at different points in time (the values
for different plants are loaded at different times) = add characteristic plant to the
table
!! But validity objects are no workaround for termination !!
Definition
-
8/3/2019 Non Cumulative
54/97
SAP AG 2004, Non-cumulatives / Stock Handling, 54
Validity-Determining Characteristics: Characteristics thatdetermine the temporal validity period of the non-cumulative in anInfoCube.
SUM: 70 70 70 185 270 295 305 350 (with validity)
SUM: 250 250 250 265 270 295 305 350 (without validity)
Jan. Feb. Mrc. Apr. Mai Jun. Jul. Aug. infinite time
plant A:70 [+10 +5 +10 +5 ] 100
plant B: 90 [ +10 +20 +30 ] 150
plant C: 80 [ +5 +5 +10 ] 100
Example IV:"initial stock" "final stock"
Non-cumulative value parameters (maintenance inInfoCube definition)
-
8/3/2019 Non Cumulative
55/97
SAP AG 2004, Non-cumulatives / Stock Handling, 55
- time reference characteristic is mandatory (determined in the InfoObjectdefinition of the stock key figure)
- additionally besides the stock relevant characteristic 0MATERIAL anadditional non-cumulative value parameter, like 0PLANT, can be defined(see example for the necessity one slide before)
Maintenance of user intervals I
-
8/3/2019 Non Cumulative
56/97
SAP AG 2004, Non-cumulatives / Stock Handling, 56
(Transaction RSDV)
Normally the validity table is adapted when loading data (min. and max. time for
every combination of validity InfoObjects)
User defined specification of the interval for each combination possible:
Fix: absolute value for the time reference characteristic
Relative: convert SY-DATUM into time reference characteristic and add /subtract specified value
Maintenance of user intervals II
-
8/3/2019 Non Cumulative
57/97
SAP AG 2004, Non-cumulatives / Stock Handling, 57
Request 0 correspond to requests in cube Request -1 re-computed intervals for reporting
Request -2 user-specified intervals
SID_0REQUID SID_0VC_NUMC11 SID_0CALDAY_F SID_0CALDAY_T MODEFROM MODEFRO M
5 3 20000331 20000331
2 3 20000101 20000131
2 1 20000201 200002280 2 20000101 20000131
0 1 20000101 20000131
-1 1 20000101 20000228
-1 2 20000101 20000131
-1 3 20000101 20000131-2 2 20000101 20001231 F F
-2 3 1 R
/BI0/L0BWVC_C04
Assumption: Requests with requid 2 are reporting relevant
Maintenance of the validity table
-
8/3/2019 Non Cumulative
58/97
SAP AG 2004, Non-cumulatives / Stock Handling, 58
1.) Initial definition of the validity table during InfoCube definition
2.) If InfoCube is filled then adaptation of validity table (remove/addition of
validity objects) possible with report RSDG_CUBE_VALT_MODIFY
3.) If validity table is wrong, then rebuild possible using the function module:
RSDV_VALID_RECREATE
I_REBUILD_FROM_FACT
= space : recomputation of the values for request -1 by aggregation of all
reporting relevant requests (only reading of validity table)
= X : recomputation of all non-user (complete facttable must be read)
ValidityValidityValidityValidity tabletabletabletable in BW 3.0in BW 3.0in BW 3.0in BW 3.0
-
8/3/2019 Non Cumulative
59/97
SAP AG 2004, Non-cumulatives / Stock Handling, 59
Jan Feb Mar Apr May ref. pointExample V:
Plant A: +30 +20 +40 +10 100
Plant B: +10 +20 150
Plant C: +20 +30 50
Without validity object: LAST
Plant A: 30 30 50 90 100 | 100
Plant B: 120 120 130 150 150 | 150
Plant C: 0 0 0 20 50 | 50
--------------------------------------------
SUM: 150 150 180 260 300 | 300
Validity interval
With validity object: LAST
Plant A: 30 30 50 90 100 | 100
Plant B: [120][120] 130 150 [150] | 150
Plant C: [0] [0] [0] 20 50 | 50
--------------------------------------------
SUM: 150 150 180 260 300 | 300
With validity object (filter on Plant B): LAST
Plant A: 30 30 50 90 100 | 100
Plant B: 130 150 | 150
Plant C: 20 50 | 50
Drill-down according to a validity object is now well defined; thatmeans the result is independent of the drilling history.
BW 3.0
Agenda
-
8/3/2019 Non Cumulative
60/97
SAP AG 2004, Non-cumulatives / Stock Handling, 60
1. Definition
2. Creation
3. Loading and compression
4. Validity table
5. Query execution
6. Modeling guidelines
7. Information resources
Query executionQuery executionQuery executionQuery execution calucationcalucationcalucationcalucation (Example III)(Example III)(Example III)(Example III)
Algorithm to compute inventory
-
8/3/2019 Non Cumulative
61/97
SAP AG 2004, Non-cumulatives / Stock Handling, 61
+10+20
Time
130
Referencepoint 2
Oct Nov Dec Jan Feb Mar Time
+10
Compressed requests
+10+10+20
value (t) =
tupel +
tupel -
tupelRequid = 0 ANDRecordtp = 1 ANDref Time =
Requid > 0 Ref Time > t
last value
Deltas forbackwards
calculation
Ref. point Non-compresseddeltas
Stock (End of Jan) = 130 + 10 10 10 = 120
Algorithm to compute inventoryvalue:
last value = reference point + noncompressed requests
value in month x = last value - allmonths larger than x
Query execution aggregation (Example II)
-
8/3/2019 Non Cumulative
62/97
SAP AG 2004, Non-cumulatives / Stock Handling, 62
Material Month Stock kyf Inflow kyf Outflow kyf
4711 06.2002 1050 1100 50
- normal aggregationalbehaviour (1000+100)
- stock key figure(Assumption: expeptional
aggregation "last value" inInfoObject definition)
- is calculated by OLAPprocessor by inflow andoutflow key figures (keyfigure is not stored
physically on theInfoCube)
Query execution validity table (Example II)
-
8/3/2019 Non Cumulative
63/97
SAP AG 2004, Non-cumulatives / Stock Handling, 63
Material Day Stock kyf Inflow kyf Outflow kyf
4711 01 Jun 2002 1000 1000 0
4711 02 Jun 2002 1000 0 0
4711 03 Jun 2002 1000 0 0
4711 04 Jun 2002 1100 100 0
4711 05 Jun 2002 1050 0 50
- Such a report is possible although there are only three data recordsstored in the InfoCube for Material 4711 (if you do not use thistechnology calculating stocks you would need to keep the stock on a
daily basis in the InfoCube this would mean five data records here).
- The time slice in the validity table determines the time range wherestocks can be calucated (see chapter before how this validity tableworks).
Query execution BW 3.0
P f i t
-
8/3/2019 Non Cumulative
64/97
SAP AG 2004, Non-cumulatives / Stock Handling, 64
Performance improvements
in backward calculation and OLAP processing
optimization of the reconstruction algorithm
preventing memory overflow by portion wise reconstruction
Agenda
-
8/3/2019 Non Cumulative
65/97
SAP AG 2004, Non-cumulatives / Stock Handling, 65
1. Definition
2. Creation
3. Loading and compression
4. Validity table
5. Query execution
6. Modeling guidelines
7. Information resources
Modelling Guidelines
-
8/3/2019 Non Cumulative
66/97
SAP AG 2004, Non-cumulatives / Stock Handling, 66
Aggregation and exceptional aggregation
InfoCubes
Queries
Performance
Non-cumulative key figures
-
8/3/2019 Non Cumulative
67/97
SAP AG 2004, Non-cumulatives / Stock Handling, 67
... have ...- always aggregation
behavior SUM
- to be set with anexceptionalaggregation (seechapter 5, example IIhow this works)
- no possibility for anaggregation referencecharacteristic,because it isdetermined by thestandard referencetime characteristic in
the validity table
Cumulative key figures
have
-
8/3/2019 Non Cumulative
68/97
SAP AG 2004, Non-cumulatives / Stock Handling, 68
have
- the possibility to enteraggregation SUM,MAX or MIN that hasimpact on updaterules, roll up and
compression- the option to define
an exceptionalaggregation behavior(see chapter 5,
example II how thisworks)
- usually then anaggregation timereference
characteristic (seenext when it canmakes sense to namehere characteristicthat are not of typetime)
Aggregation reference characteristic not of typetime
Example VI: Reference characteristic that is not a time characteristic
-
8/3/2019 Non Cumulative
69/97
SAP AG 2004, Non-cumulatives / Stock Handling, 69
p
InfoCube:
Employee no Type of appraisal Year No of employee appraisals
4711 Potential assesment 1999 1
4711 Target achievement 1999 0
4711 Performance appraisal 1999 1
4712 Potential assesment 1999 1
4712 Target achievement 1999 0
Query:Employee no Year No of employee appraisals
4711 1999 2
4712 1999 1
Query new:
Employee no Year No of employee appraisals
4711 1999 1
4712 1999 1
Double counting!!!
Solution: keyfigure No ofemployeeappraisals
getsexceptionalaggr. AVG forvalues notequal 0 overcharacteristictype ofappraisal(aggregationreferencecharacteristic)
Using another exception aggregation
-
8/3/2019 Non Cumulative
70/97
SAP AG 2004, Non-cumulatives / Stock Handling, 70
With a new caluclated key figure many execption aggregation typesare possible for the same basis key figure
FIRST and LAST aggregation
-
8/3/2019 Non Cumulative
71/97
SAP AG 2004, Non-cumulatives / Stock Handling, 71
FIRST is using opening values while all other aggregations still useclosing values.
Example VII: Date Month Material Inflow
29.01.02 Jan 4711 3030.01.02 Jan 4711 10
31.01.02 Jan 4711 20
01.02.02 Feb 4711 30
02.02.02 Feb 4711 20
Facttable:
Query:
Date Stock (LAST) Stock (FIRST)
28.01.02 0 0
29.01.02 30 0
30.01.02 40 30
31.01.02 60 40
01.02.02 90 60
02.02.02 110 90
03.02.02 110 110
BW 3.0
Modelling Guidelines
-
8/3/2019 Non Cumulative
72/97
SAP AG 2004, Non-cumulatives / Stock Handling, 72
Aggregation and exceptional aggregation
InfoCubes
Queries
Performance
InfoCubes
-
8/3/2019 Non Cumulative
73/97
SAP AG 2004, Non-cumulatives / Stock Handling, 73
Compression
Update rules
Validity table
Data Model
Compression
General rule: Creation of opening balance has to be
-
8/3/2019 Non Cumulative
74/97
SAP AG 2004, Non-cumulatives / Stock Handling, 74
p g
compressed with marker update, movements that havelead to this stockhave to be compressed with no markerupdate, new movements can be compressed, if they arecompressed then mandatory with marker update. If this isnot considered, query results get wrong (see exampleVIII).
Combination of characteristics values in the historicalmovements have to match the combination ofcharacteristics values in the opening balance. Otherwhisesome stock markers are missing. Then stock values can
not be caluclated (with this combination in query drilldown). As requests with historical movements can only becompressed without stock marker update it is notpossible to produce them automatically (see example IX).
Example VIII: Results of wrong compression (I)
-
8/3/2019 Non Cumulative
75/97
SAP AG 2004, Non-cumulatives / Stock Handling, 75
PSA 2LIS_03_BX -> compression with marker update
Date Quantity
22 Mrc 2003 6050
PSA 2LIS_03_BF INIT -> compression with no marker update
Date Quantity +/-
21 Jan 2003 100 -
PSA 2LIS_03_BF Delta -> compression with marker updateDate Quantity +/-
2 Apr 2003 6050 -
Example VIII: Results of wrong compression (II)
-
8/3/2019 Non Cumulative
76/97
SAP AG 2004, Non-cumulatives / Stock Handling, 76
InfoCube Result after compression WRONG
Date Quantity ISS Quantity REC RecordType
2 Apr 2003 6050 0 0
21 Jan 2003 100 0 0
31129999 0 -100 1
InfoCube Result after compression RIGHT
Date Quantity ISS Quantity REC RecordType
2 Apr 2003 6050 0 021 Jan 2003 100 0 0
31129999 0 0 1
Example VIII: Results of wrong compression (III)
Query Result WRONG
Month Stock Issue Receipt
-
8/3/2019 Non Cumulative
77/97
SAP AG 2004, Non-cumulatives / Stock Handling, 77
Month Stock Issue Receipt
12/2002 6050 - -
01/2003 5950 100 0
02/2003 5950 0 0
03/2003 5950 0 0
04/2003 -100 6050 0
---------------------------------------------------------------------------------------------------------------------------------------------------------------------
Result -100 6150 0
Query Result RIGHT
Month Stock Issue Receipt
12/2002 6150 - -01/2003 6050 100 0
02/2003 6050 0 0
03/2003 6050 0 0
04/2003 0 6050 0---------------------------------------------------------------------------------------------------------------------------------------------------------------------Result 0 6150 0
The historical stock gets wrong by the amount of the historical movement !!!
The error of all calculated stocks has the value of the wrong compressed
historical movements (in this case 100) !!
Example IX: Missing stock markers
Stock init
Material Plant Storage Location Date Value Compression with
-
8/3/2019 Non Cumulative
78/97
SAP AG 2004, Non-cumulatives / Stock Handling, 78
4711 1000 0001 1st Jan 2004 +1000
Historical movement
Material Plant Storage location Date Value
4711 1000 1st Dec 2003 +1000
marker update
Cannot be handled!
Solution: make sure thatthe char. value combinationof the movements fits tothe stock init char. Value
combination!
Drill downby storagelocation!
As these request can only be compressed with no marker update, marker procuctionincl. storage location is not possible!
Compression with no
marker update
Special Case: Backdating
Let's assume the initial stock is loaded with on January 1st 2004.
-
8/3/2019 Non Cumulative
79/97
SAP AG 2004, Non-cumulatives / Stock Handling, 79
You are setting up then the normal procedure with initializing the historicalmovements (historical request with movements < January 1st 2004,compression with no marker update).
Now new movements (> January 1st 2004) are coming in with a delta request .
This request contains a record that has a posting date with 31st December 2003and a recording date with 2nd January 2004 (note that the posting date is the
relevant date in most of such scenarios).
When compressing this delta request with marker update, does the values getwrong (because we have learned that historical movements (< January 1st 2004)
have to be compressed with no marker update)?
The answer is no because at the time the initialization took place (January 1st
2004) the stock init extractor has to fit with the movement extractor.
If the movement extractor is delta enabled he adds this new posting into a delta
request. As such delta requests can be compressed with marker update thestock marker gets adjusted accordingly and the stock calculation algorithm canwork correctly.
You also need to avoid that such documents are covered by the setup report ofthe historical movements (possible if no posting block exists during this run or
report runs with selections on the date).
Special Case: Future Postings
Future postings have similar issues like backdatings.
-
8/3/2019 Non Cumulative
80/97
SAP AG 2004, Non-cumulatives / Stock Handling, 80
If you try to cover the historical movements that have build up the initial stockin a setup report by date selection and there are movements that relevant(posting) date is higher then the stock init date you won't cover them (if the
upper bound corresponds to the stock init date).
They will also not be covered by the delta request.In such cases you need to enlarge the upper bound accordingly or you select all
documents. In both cases keeping the posting block is mandatory.
InfoCubes
-
8/3/2019 Non Cumulative
81/97
SAP AG 2004, Non-cumulatives / Stock Handling, 81
Compression
Update rules
Validity table
Data Model
Update rules
-
8/3/2019 Non Cumulative
82/97
SAP AG 2004, Non-cumulatives / Stock Handling, 82
- calculate all other time characteristicsautomatically from the reference timecharacteristic
InfoCubes
-
8/3/2019 Non Cumulative
83/97
SAP AG 2004, Non-cumulatives / Stock Handling, 83
Compression
Update rules
Validity table
Data Model
Validity table
-
8/3/2019 Non Cumulative
84/97
SAP AG 2004, Non-cumulatives / Stock Handling, 84
Use validity objects only if you really need them
Do never use fine granular objects like e.g. material,
customer, ... as validity object (No Termination!)
Do never aggregate over validity objects
60 or 45 ?45SUM: 40 50
AVG
30 = (10 + 50) / 2
30
Jan. Feb.
Plant A: [ 10 50 ]
Plant B: [ 30 ]
Example X:
InfoCubes
-
8/3/2019 Non Cumulative
85/97
SAP AG 2004, Non-cumulatives / Stock Handling, 85
Compression
Update rules
Validity table
Data Model
Data Model
-
8/3/2019 Non Cumulative
86/97
SAP AG 2004, Non-cumulatives / Stock Handling, 86
If you want to look at reference points with transaction LISTCUBE , you
have to include the technical characteristic 0RECORDTP into the list of
fields for selection and you have to add a restriction of 0RECORDTP=1.
Sometimes it might be more feasible to use asnapshot scenario than the non-cumulative valuesapproach. Please follow the guideline in the howto paper mentioned in the last chapter to decide
on this.No ODS support for non-cumulative values, only with a snapshotscenario ODS usage makes sense.
If the amount of data is very huge think about a "logical" partitioning tospread data over several InfoCubes (but don't use time characteristics, thisis not supported due to the stock marker logic). Use therefore ideally anadditional non-cumulative value parameter of the validity table like 0PLANT.
Modelling Guidelines
-
8/3/2019 Non Cumulative
87/97
SAP AG 2004, Non-cumulatives / Stock Handling, 87
Aggregation and exceptional aggregation
InfoCubes
Queries
Performance
Query definition
N diff b t l k fi d NCUM k fi
-
8/3/2019 Non Cumulative
88/97
SAP AG 2004, Non-cumulatives / Stock Handling, 88
No difference between normal key figures and NCUM key figures If a query contains normal and NCUM key figures both parts are processed
separately by the OLAP-processor but this is transparent for the user.
virtual key figures (OLAP BADI) can only use key figures which have the
same type (normal or NCUM)
Modelling Guidelines
-
8/3/2019 Non Cumulative
89/97
SAP AG 2004, Non-cumulatives / Stock Handling, 89
Aggregation and exceptional aggregation
InfoCubes
Queries
Performance
Performance guidelines
All requests or at least most of the requests should be compressed.
Use as few validity objects as possible
-
8/3/2019 Non Cumulative
90/97
SAP AG 2004, Non-cumulatives / Stock Handling, 90
Use as few validity objects as possible. Use tight restrictions on the time characteristics. If possible do only request
the actual value.
If you have a drill down by time, key figures with First/Last aggregation can
be processed more efficiently than key figures with AVG aggregation ifpossible use different queries for key figures with First/Last resp. AVG
aggregation (separate First/Last/etc. key figures from other key figures into
separate queries if semantically possible).
Do not misuse validity objects for termination. Do not use partial times (like e.g. 0FISCPER3) if not needed.
If not needed suppress the lines with the sum.
Agenda
1. Definition
-
8/3/2019 Non Cumulative
91/97
SAP AG 2004, Non-cumulatives / Stock Handling, 91
2. Creation
3. Loading and compression
4. Validity table
5. Query execution
6. Modeling guidelines
7. Information resources
Online Documentation NetWeaver BW I
Non-Cumulatives
BI Platform OLAP
-
8/3/2019 Non Cumulative
92/97
SAP AG 2004, Non-cumulatives / Stock Handling, 92
BI Platform OLAP
Performance Optimization
Non-cumulatives
Validity table
Data Warehousing Administrator Workbench Modeling
InfoCube
Additional Functions in InfoCube Maintenance
Non-Cumulative Value Parameter Maintenance
Compression
Data Warehousing Data Warehouse Management Process Management
Managing Data Targets Managing InfoCubes
Compressing InfoCubes
Aggregation
Data Warehousing Administrator Workbench Modeling InfoObject
Creating InfoObjects: Key Figures
Tab Page: Aggregation
Online Documentation NetWeaver BW II
Exceptional Aggregation
BI Platform OLAP
-
8/3/2019 Non Cumulative
93/97
SAP AG 2004, Non-cumulatives / Stock Handling, 93
BI Platform OLAP
Special OLAP Functions and Services
Aggregation
Scenarios for the Use of an Exception Aggregation
CSN Note
586163 - Composite SAP Note on SAP R/3 inventory management in SAP BW
-
8/3/2019 Non Cumulative
94/97
SAP AG 2004, Non-cumulatives / Stock Handling, 94
Central entry point, from there allrelevant notes are mentioned!!!
How to paper
http://service sap com
-
8/3/2019 Non Cumulative
95/97
SAP AG 2004, Non-cumulatives / Stock Handling, 95
http://service.sap.comalias BW
in the right navigation area: SAP BW InfoIndex click:(N) Non-cumulatives How to Handle inventorymanagement scenarios in SAP BW 3.x (pdf)
No part of this publication may be reproduced or transmitted in any form or for any purpose without the expresspermission 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
Copyright 2004 SAP AG. All Rights Reserved
-
8/3/2019 Non Cumulative
96/97
SAP AG 2004, Non-cumulatives / Stock Handling, 96
Some software products marketed by SAP AG and its distributors contain proprietary software components of othersoftware vendors.
Microsoft, WINDOWS, NT, EXCEL, Word, PowerPoint and SQL Server are registered trademarks ofMicrosoft 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 andother 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 inventedand implemented by Netscape.
MarketSet and Enterprise Buyer are jointly owned trademarks of SAP AG and Commerce One.
SAP, SAP Logo, R/2, R/3, mySAP, mySAP.com 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 trademarks of their respectivecompanies.
Weitergabe und Vervielfltigung dieser Publikation oder von Teilen daraus sind, zu welchem Zweck und in welcherForm auch immer, ohne die ausdrckliche schriftliche Genehmigung durch SAP AG nicht gestattet. In dieser
Publikation enthaltene Informationen knnen ohne vorherige Ankndigung gendert werden.
Copyright 2004 SAP AG. Alle Rechte vorbehalten
-
8/3/2019 Non Cumulative
97/97
SAP AG 2004, Non-cumulatives / Stock Handling, 97
Die von SAP AG oder deren Vertriebsfirmen angebotenen Softwareprodukte knnen Softwarekomponenten auchanderer Softwarehersteller enthalten.
Microsoft, WINDOWS, NT, EXCEL, Word, PowerPoint und SQL Server sind eingetragene Marken derMicrosoft 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, Informixund Informix Dynamic ServerTM sind Marken der IBM Corporation in den USA und/oder anderen Lndern.
ORACLE ist eine eingetragene Marke der ORACLE Corporation.
UNIX, X/Open, OSF/1 und Motif sind eingetragene Marken der Open Group.
Citrix, das Citrix-Logo, ICA, Program Neighborhood, MetaFrame, WinFrame, VideoFrame, MultiWin und
andere hier erwhnte Namen von Citrix-Produkten sind Marken von Citrix Systems, Inc. HTML, DHTML, XML, XHTML sind Marken oder eingetragene Marken des W3C, World Wide Web Consortium,
Massachusetts Institute of Technology.
JAVA ist eine eingetragene Marke der Sun Microsystems, Inc.
JAVASCRIPT ist eine eingetragene Marke der Sun Microsystems, Inc., verwendet unter der Lizenz der vonNetscape entwickelten und implementierten Technologie.
MarketSet und Enterprise Buyer sind gemeinsame Marken von SAP AG und Commerce One.
SAP, SAP Logo, R/2, R/3, mySAP, mySAP.com und weitere im Text erwhnte SAP-Produkte und -Dienst-leistungen
sowie die entsprechenden Logos sind Marken oder eingetragene Marken der SAP AG in Deutschland und anderenLndern weltweit. Alle anderen Namen von Produkten und Dienstleistungen sind Marken der jeweiligen Firmen.