master data maintenance in bw
DESCRIPTION
This presentation delineates the structure adopted by SAP to arrange the master data and implement MDM (Multi Dimensional Medeling).TRANSCRIPT
InfoCube Components
Infocube
FactTable
Dimension Table
Dimension Table
Dimension Table
Dimension Table
Characteristic Tables
Characteristic Tables
Characteristic Tables
Characteristic Tables
InfoCube Components
Infocube
FactTable
Dimension Characteristic 1 Characteristic 2 Charcteristic N
SIDTable
SIDTable
SIDTable
Master Data Table
Master Data Table
Master Data Table
Characteristic Values
Tables - P or Q Attribute Values
Key Figure Values
InfoCube Components
Infocube
FactTable
Dimension Characteristic 1 Characteristic 2 Charcteristic N
SIDTable
SIDTable
SIDTable
Attribute SID Table
Attribute SID Table
Attribute SID Table
Characteristic Values
Tables - X or YNavigable Attribute SID
Key Figure Values
(for customer-defined characteristics)/BIC/...<technical name of the characteristic>
(for SAP standard characteristics)/BI0/...<technical name of the characteristic>
Naming conventions in the Master data tables
Q /BI0/QCS_UNIT - Time Dependent Master Data Table
P /BI0/PCS_UNIT - Time Independent Master Data Table
M /BI0/MCS_UNIT – View of Q and P tables
Master Data and SID Tables
Master data for a characteristic is stored in the tables :
BW populates the following characteristic SID table during data loads:
S /BI0/SCS_ITEM - Traditional SID Table
X /BI0/XCS_ITEM – Time Independent Attribute SID Table
Y /BI0/YCS_ITEM – Time Dependent Attribute SID Table
BW populates the following characteristic attribute SID tables during data loads:
T /BI0/TCS_UNIT - Time Dependent Master Data Table
Text & Hierarchy Tables
Text for a characteristic is stored in the tables :
BW populates the following Hierarchy tables during data loads:
H /BI0/HCS_ITEM – Primary Hierarchy Table
K /BI0/KCS_ITEM – Hierarchy Node SID Table (Negative Values)
I /BI0/ICS_ITEM – Hierarchy Structure SID Table
J /BI0/JCS_ITEM – Time Intervals for the Hierarchy Nodes
Master Data Tables
The P- and Q-tables are linked to the S-table using the original key from the source system.The SID is used to link to the dimension tables.
/BIC/COSTC PROFIT_CTR
K100 P100K200 P200K300 P100K400 P100
/BIC/COSTC DATETO DATEFROM RESP_PERS
99991231 10000101K100 99991231 10000101 H. MüllerK200 19980930 10000101 H. MüllerK200 99991231 19981001 H. MeierK300 99991231 10000101 H. MeierK400 99991231 10000101 H. Meier
/BIC/COSTC SID
0K100 1K200 2K300 3K400 4
/BIC/PCOSTC
/BIC/QCOSTC
/BIC/SCOSTC
Key
... /BIC/... SID CHCKFL DATAFL INCFL
0 X X X
Structure: /BIC/S...
P-Table / Q-TableTime independent display attributes are generated in the P-table.Time dependent display attributes are generated in the Q-table
time..
DIS0PROFIT_CTR
DIS0EVCURRCOST
DIS0ENTRYDATE
NAV0BUS_AREA
NAV0COMP_CODE
TypeAttribute
Key
{...} /BIC/… OBJVERS DATETO DATEFROM CHANGED <Attribute>¹ ...
A 99991231 10000101
Structure: /BIC/Q...
With master data?
/BIC/MCOSTC##
/BIC/PCOSTC##
View of MasterDtaTbles
Master Data Table
/BIC/QCOSTC##
/ BIC/YCOSTC##
Master. Table. Time.dep.
Time-dep. Attr. SID Table
Key
/BIC/… OBJVERS CHANGED <Attribute>¹ <Attribute>² <Attribute>³ ...
A
Structure: /BIC/P...
X-Table for Navigation Attributes
SID /BIC/COSTC S__PROFIT_CTR
0 01 K100 111112 K200 222223 K300 111114 K400 11111
PROFIT_CTR SID
0P100 11111P200 22222P100 11111P100 11111
/BIC/XCOSTC /BI0/SPROFIT_CTR
Key
SID OBJVERS /BIC/<...> CHANGED S__NAV
0 A 0
Structure: /BIC/X...
Read Step, S- to Y-Table
Y-Table for Navigation Attributes
SID DATETO DATEFORM /BIC/COSTC S__PROFIT_PERS
0 99991231 10000101 01 99991231 10000101 K100 10102 19980930 10000101 K200 10102 99991231 19981001 K200 20203 99991231 10000101 K300 20204 99991231 10000101 K400 2020
RESP_PERS SID
0H. Müller 1010H. Müller 1010H. Meier 2020H. Meier 2020H. Meier 2020
/BIC/YCOSTC /BI0/SRESP_PERS
Key
SID OBJVERS DATETO DATEFROM /BIC/<...> CHANGED S__NAV
0 A 99991231 10000101 0
Structure: /BIC/Y...
Read Step, S- to Y- Table
Navigation Attributes in the Extended Star Schema
F-Table
Material
Package Time Unit Material AMOUNT2 3 2 6 1002 3 2 7 1002 3 2 8 1002 3 2 9 1002 4 2 6 1002 4 2 7 1002 4 2 8 1002 4 2 9 1002 4 2 10 100
DIMID SID_MAT_TH6 11
...
/BIC/MAT_TH_C SID DATETO DATEFROM S__0MATL_GROUP
AAA 11 31.12.9999 01.01.1996 143...
MATH_TH_C Y-Table
MATL_GROUP SID
FOOD 143...
CALMONTH SID
200001 178200002 179
...
0MATL_GROUPSID-Table
Key date 15.02.2000
...
100100Food
02.200001.2000MATL_GROUP
DIMID SID_CALMONTH3 1784 179
...
Time0CALMONTH SID-Table
Characteristic
CharacteristicNavigation Attribute
Dimension Dimension
3
4
52
1
Text TablesOnly one text table is generated for each characteristic.
With texts
Short text exists
Medium-length text exists
Long text exists
Texts language-dependent
Texts are time-dependent
Text Table /BIC/T<...>
Key
{...} /BIC/?…? LANGU DATETO DATEFROM TXTSH TXTMDStructure: /BIC/T?...?
CO_AREA /BIC/TCOSTC## LANGU DATETO DATEFROM TXTSH TXTMD
1000 T900000001110 D 31.12.9999 01.01.1994 Board Board
Key
Example
Hierarchy TablesThe hierarchy table (H table) is used to store the hierarchical relationships between
characteristic values if external hierarchies are used for the characteristic..
Hierarchy H Table Example
Hierarchy Interval J Table
If an indicator has been set in the INTERVL field, in H Table, intervals are permitted in this hierarchy and these are modelled in the J table.
Hierarchy SID TablesIf the With hierarchies indicator has been set in the maintenance screens for
characteristics, the SID tables are always generated in addition to the H table.
K – Hierarchy Node SID Table In the SID Table for Nodes, negative SID values are assigned to the nodes.
I (Inclusion Table) – Hierarchy Structure SID TableThe leaves (characteristic values) are assigned positive SID values and the nodes are assignednegative SID values (K table).
Hierarchy Tables Anatomy
Linking Tables and Views in BW Star Schema
S
/BIC/SCOSTC##
DIM
/BIC/DCUBE#Q
/BIC/QCOSTC##
P
/BIC/PCOSTC##
X
/BIC/XCOSTC##
Y
/BIC/YCOSTC##
M
/BIC/MCOSTC##
S/BIO/SCOMP_CODE
P
QM
/BIO/MCOMP_CODE
S/BIC/SCOST_VERA
P
QM
/BIC/COST_VERA
Tables forInfoObjectCOSTC##
Tables Time-IndependentNavigation Attribute
Tables Time-DependentNavigation Attribute
Tim
e-D
epen
dent
Tim
e-In
depe
nden
t
How the Star Schema Works: Make a Query
Step1: Browse the dimension tables
– Access the customer dimension tables and select all records with city = ‚New York‘
– Access the Product Dimension and select all record with Material Group = ‚telephones‘
– Access the Time Dimension Table and select all record with Year = ‚1997‘
Step2: Accessing the Fact Table
– Using the key values evaluated during Browsing,
– Select all records in the Fact Table which have these values in common in the Fact Table record key.
„Show me the revenue for customers located in New York with Product group „telephones“ in the Year 1997“
How the Star Schema Works: Update into Cube
Time
DAY CUSTOMER PRODUCT REVENUE ORDER ENTRY
20.10.2002 4711 4422 1000 10
TIMEID DAY MONTH YEAR QUARTER WEEK1 20.10.2002 200210 2002 20024 200247
PRODUCT ID PRODUCT PRODUCT GROUP DIVISION1 4422 22 1
CUSTOMER ID CUSTOMER ACCOUNT GROUP1 Smith 12
TIMEID CUSTOMER ID ORG ID PRODUCT ID REVENUE ORDER ENTRY1 1 1 1 1000 10
Customer
Product
Facts
Customer Smith buys a telephone at 21.10.2002.
How the Star Schema Works: Update into Cube
Time
DAY CUSTOMER PRODUCT REVENUE ORDER ENTRY
20.10.2002 4711 4422 1000 10
TIMEID DAY MONTH YEAR QUARTER WEEK1 20.12.2002 200210 2002 20024 200247
PRODUCT ID PRODUCT PRODUCT GROUP DIVISION1 4422 22 1
CUSTOMER ID CUSTOMER ACCOUNT GROUP1 Smith 12
TIMEID CUSTOMER ID ORG ID PRODUCT ID REVENUE ORDER ENTRY1 1 1 1 1000 10
Customer
Product
Facts
Customer Smith buys a telephone at 21.10.2002.
Changes in the new world„Facts“: Changes in fact table
Slowly changes:Changes in attributes of
Business subjects
CUSTOMER ID CUSTOMER ACCOUNT GROUP1 Smith New customer
PRODUCT ID PRODUCT PRODUCT GROUP DIVISION1 Telephones xy Communications Direct