1. No category

How to Achieve common semantics/vocabulary on
Business information in a Data warehouse
Lasse Bache-Mathiesen
Capgemini Business Information Mangement
 Lasse Bache-Mathiesen
 Capgemini BIM Norway
 130 Employees
 Located in Oslo, Stavanger, Bergen
and Trondheim
 Capgemini BIM Global
 7000 consultants
 Cand. Mag.. Mathematics UIO
 25 years in the BI and reporting area
 Employments
• Norsk Data
• Merkantil Data
• Sysdeco
• Affecto
• Capgemini
Business Information Management
Business Information Service Center | April 2013
Copyright © 2013 Capgemini. All rights reserved.
2
CONTENTS
1. Business Intelligence
2. History of Data Warehouse – ETL problem
3. Data Governance and organization
4. Developing the logical model
5. The implementation
6. Supporting functions
7. Supporting the target architecture
Business Information Management
Business Information Service Center | April 2013
Copyright © 2013 Capgemini. All rights reserved.
3
Business Intelligence
 BI Maturity model
“I have a portfolio of appropriate
options available, at the moment of
contact”
Competitive Advantage
 Interaction between
“What is my best
opportunity?”
“How am I doing
vs. goals?”
“I can explore my data,
but is it correct?”
Value extracted from
Information
Business Information Management
Business Information Service Center | April 2013
Copyright © 2013 Capgemini. All rights reserved.
4
Information Overload
IT systemes in Silos
Systems Engineering - Europe
March 1999.
GME - PRODUCE PRODUCT (PP) AND PRODUCTION CONTROL & LOGISTICS (PC&L) APPLICATIONS
Plan Supply Chain
PC&L 1
Establish Supply Chain
PC&L 2
PC&L 3
PC&L 4
PC&L 5
#15
PC&L 6
PC&L 7
PC&L 8
PC&L 9
#72
PRI M O
PRO M AS
Ser ver
PSS/ G ES Cent r al
# 2 1 1
PSS
Bar code
Appl i cat i on
#229
M M DB
CSI DS
#45
G PS
ADDRESS
Per Box
Docum ent at
i on
#56
#54
#241
Packi ng
Sheet s
#58
#59
#57
#53
G PS
CO NTRACT
S
P
R
O
D
U
C
T
I
O
N
TAD/ TDC Excel W kb
#55
#52
PI BUS
PRO M AS
Host
#67
PSS
Cust om er
#43
# 2 3 3
#239
#238
PRO M AS
#237
#13
#17
# 2 0 0
#11
# 1 9 9
# 1 6
#130
#19
#25
#208
# 2 0 1
#14
#270
SM ARTPCS
#209
#24
#23
#8
C
O
N
T
R
O
L
SUPPLI ER
#10
#240
#271
&
NAO O r der s
( Local )
# 1 8
L
O
G
I
S
T
I
C
S
# 2 6
Secur i t y
Dat abase Local
# 2 1 7
# 2 7
#203
CO SS
( Local )
#202
M PCE - PP
#204
#206
# 2 3 0
Book
Conver si on
#205
# 4 6
# 4 0
# 6 6
# 4 7
# 4 4
# 2 3 6
#90
PSS/ G ES
# 2 3 1
M DPS Cent r al
# 7 0
G PDS
( PDI S)
# 7 1
ESO
# 6 9
# 6 8
ECO S
# 4 2
#65
LPL+LFM S+
C
#193
ETS
ESPS
#190
Cockpi t
ECO S
Need of
information
I
N
T
E
R
F
A
C
E
S
# 2 5 5
# 2 1 0
SERT
SM ART MMS
FI NANCI AL
SYSTEM
TAD / TDC
#198
SALES
#16
7
# 2 4 8
#118
# 7 3
#194
ECO S/ ADL Local
# 3 9
# 4 1
Tailored point to
point integration
SM ART PCS
#213
#212
A
P
P
L
I
C
A
T
I
O
N
S
G M UD - EPL
# 8 2
# 7 5
# 1 9 1
# 2 2 6
# 2 3 4
G PS
P/ DUNS
NCT
Manage Orders
Cust om s
SECURI TY
DATABASE
#195
PP 2
PP 3
#215
PP 4
PP 5
# 2 1 8
PP 1
#196
#216
ECO S /
ALDL
# 5
RO C
Pai nt
Pr ocess
Pl anni ng
Pai nt Shop
FLEX
# 2 5 4
# 2 5 1
#115
EPI CS
( PDC)
EPI CS ( ERS)
Sal es
Pl g. &Pr odn
Rel . S/ m 's
EPI CS
( O HS)
ESO / AAS
G M * DRI VE
#61
# 2 4 6
# 1 5 4
# 1 2 4
# 1 5 8
PREM I D
EDS
O per at i ons
M PC Tel ex 3
RS
# 4 8
# 5 1
# 1 5 9
# 5 0
# 1 6 0
# 4 9
CO P
NSC
PREFEREN
CI NG
#74
#153
#180
SECURI TY
SYSTEM S
NO RC
#179
M DPS
#147
#145
BI LLI NG
SYSTEM S
G M * M O VE
# 1 0 5
Conveyor
Cont r ol
Syst em s
EPI CS
( BCS)
TRAFFI C /
CUSTO M S
# 1 6 8
#114
#108
# 1 6 9
# 1 6 2
# 1 7 5
# 1 7 8
#107
#171
#184
#112
MMS
#183
#111
# 8 0
# 7
RO LS
NSC O RDER
CHANG ES
I N O PEL
TSV
#173
# 2 0
# 1 2 5
# 2 7 2
# 2 2 1
P
R
O
D
U
C
E
HO DLM AYE
R
# 1 0 9
# 2 2
# 2 2 0
# 2 5 8
PRO D
O RDER
CHANG ES
I N G M UD
# 6 2
#189
# 1 0 6
PLANT M I NI
PI S
G ener al
Assem bl y
# 2 1
CACBP&FU
# 6 3
EFFY
#110
#247
NW S
( CO SY)
Body Shop
#187
M PCE- PM S
SM ART PCS & M M S
TSV ( TM S)
# 1 0 4
#163
#157
# 1 8 6
# 1 6 1
# 1 8 5
# 1 7 6
# 4
M f g.
Engi neer i ng
# 1 7 2
#99
#164
#156
M PC St or es
SAP
# 1 7 0
# 6
# 2 4 3
# 1 4 4
# 1 4 3
# 1 5 5
# 2 6 6
#1
M AI NFRAM
E PI S
I NTERFACE
EI SI S
Fi nal
Assem bl y
ECO S
VI S
#188
PREP
( PURCHASE
PAYABLES)
AAS
M odel Code
Schedul e
#146
#166
#165
EPI CS
AVI
CO SCO
# 2 4 5
# 1 7 7
#142
ESO / CO P
#152
# 1 4 9 # 1# 51#121 34 1
SALES- CPO
Sal es O r der
Pl anni ng
#182
#181
M PC Vehi cl e
Schedul i ng
# 1 1 6
M odel Code
Tabl e
# 1 2
MGO
M O DEL
CO DE
TABLE
# 1 5 0
PO M S
# 1 1 7
#140
#250
#119
#84
#121
#60
EPI CSRuss. &
G M * DRI VE
#235
#89
#81
#256
TRAF/
TRAFFI C
#242
G PDS
#214
SM ART
#244
# 1 4 8
# 2 1 9
#197
High Complexity
#120
#97
M PC Tel ex 1
# 1 7 4
# 2 6 7
Manage Material
P
R
O
D
U
C
T
PRO DUCTI O
N PEO PLE
# 8 6
PP 7
PP 8
PP 9
PP 10
# 2 5 9
# 2 6 2
# 2 5 3
# 2 5 2
PASO S
G M UD &
FAM I LY
ENG I NES
M f r . Pr i ce
Fi l e DB
MGO
REQ UI REM E
NTS
MGO
CO M PO NEN
T BO M
#100
#88
#35
#33
# 8 7
# 7 8
# 2 8
# 2 0 7
# 1 3 5
# 1 3 3
I
N
T
E
R
F
A
C
E
S
Scanner s
MGO
I NVENTO RY
# 2 5 7
# 1 3 4
#268
Tr uck &
Reci pi ent
l i st s
#34
Shi ppi ng( C
KD
Bar code)
Shi ppi ng
# 2 2 8
# 8 5
# 9 8
# 2 7 3
CAM AS
# 9 5
Ny systems with
ovelapping
funtionality
#113
CKD
O RDERSASSEM BLY
PLANTS
#9
IE
A
P
P
L
I
C
A
T
I
O
N
S
Fr ei ght I nf o'
CO SS
CO SSO RDERI NG
& SCHED.
ESQ ES
KREBU
SQ A
Fi n. Syst em
- Local SAP
# 7 9
# 2
MGO
ASSEM BLY
BO M
M AI S
# 2 2 3
# 2 3 2
#3
NW S
CAM AS 2. 0
# 9 3
PP 6
#122
Legacy
systemes
# 2 4 9
#269
M G O BO M
#132
W PCS
Suppl i er
Schedul es
#260
#224
#225
CKD
#96
#222
# 9 4
# 1 2 9
St andar d
I nt er f ace
#261
# 3 1
G Q TS
#265
CO SS I NVO I CI NG
#102
# 2 6 4
KREBU
( Account s
Payabl e)
#127
CAM AS 2. 0
( Cor e
M odul e)
#131
#126
SUPPLI ER
ADDRESS
DATA
#83
# 2 6 3
#92
#7
6
# 7 7
#139
#9
1
MGO
# 3 2
#128
CKD
BARCO DI N
G ( S&I )
#227
# 6 4
# 1 9 2
#136
Packi ng
Sl i ps
I ES
CTRG
Car dBoar d
Box Li st
Packi ng Sl i p
Syst em
# 1 0 1
Ref er ence
Tabl e
#103
PASO S I NVO I CI NG /
par t / box
#29
# 3 8
M G OBO M Local Pkg.
Pl ant
M G O Specs
( Assy +
Com p )
CAM AS 2. 0
( Si m ul at i on)
#138
G M E I nv.
Pl t . t o DUNs
#36
Bar Code
and CKD
Shi pm ent s
#137
CKD
Packi ng Sl i p
Syst em
I ndex to Appl i cati ons:
No visibility
#37
I ndex to Busi ness processes:
Producti on Control
& Logi sti cs
Processes
Ext er nal Syst em s
MGO
G PDS
CKD
G PS
G Q TS
ESQ ES
SM ART
PRO M AS
CAM AS
EPI CS
PC&L 1
Devel op
Suppl y Chai n
St r at egy
PC&L 2
Devel op
Capaci t y Pl an
PC&L 3
PC&L 4
PC&L 5
PC&L 6
Devel op Pr oduct Devel op I nbound
Est abl i sh
Est abl i sh
Pr ogr am
Com m odi t y Fl ow
I nbound M at er i al
M at er i al Sour ce
Capaci t y Pl an
Pl an
Packagi ng
PC&L 7
Est abl i sh
I nbound
Logi st i cs
PC&L 8
Devel op I nbound
M at er i al Fl ow
Pl an
PC&L 9
Ensur e
Pr oduct i on
Readi ness
Produce Product
Processes
PP 1
PP 2
Devel op G r oup
Devel op G r oup
M odel Year
M ast er Schedul e
Pr oduct i on Pl an
PP 3
Devel op Pl ant
Pr oduct i on
Schedul e
PP 4
M oni t or
Pr oduct i on
PP 5
Shi p and Del i ver
Pr oduct
PP 6
Schedul e
M at er i al
PP 7
PP 8
Tr anspor t
Assess Suppl i er
M at er i al t o Poi nt
Per f or m ance
of Use
PP 9
M oni t or
I nvent or y
PP 10
M anage
Di sposi t i on of
Packagi ng
CKD Host
BO M
Pr ocessi ng
# 3 0
W ei ght s &
M easur es
I nf o'
High Level Business Processes
No t e :
On ly t h e f o lo win g GMEPP a n d PC & L a p p li c a t io n s wh ic h we r e t h e f o c u s o f t h is s t u d y ,h a v e b e e n ma p p e d a c c o r d in g
t o t h e b u s in e s s p r o c e s s d e f in e d a b o v e : GPDS ,C S I DS ,MMDB,GPS ,PRI MO,PS S , PROMAS ,PI BUS , S MART ,
POMS ,C OP,MGO,AAS ,EPI C S ,FLEX,MMS ,C AMAS , MAI S ,GQT S ,a n d C KD.T h e o t h e r e x t e r n a ls y s t e ms a r e
la id o u t s o a s t o imp r o v e v is ib ilt y a n d k e e p in g in v ie wt h e ir in t e r f a c e s wit h t h e s y s t e ms in f o c u s .
Fi l e Name : Mai denhead Server S:\ Publ i c\ Produce Product DB\ Vi si o-I nputs&Outputs\ Overal l -PP-Landscape. vsd
No agility
Business Information Management
Business Information Service Center | April 2013
© 2007 Capgemini - All
Copyright © 2013 Capgemini. All rights reserved.
5
5
CONTENTS
1. Business Intelligence
2. History of Data Warehouse – ETL problem
3. Data Governance and organization
4. Developing the logical model
5. The implementation
6. Supporting functions
7. Supporting the target architecture
Business Information Management
Business Information Service Center | April 2013
Copyright © 2013 Capgemini. All rights reserved.
6
The data warehouse
Internal data sources
DATA
DATA
Data copy
DATA
Balanced Scorecards
Performance Management
Data
Warehouse
Boardroom
DATA
Data Analysts
External data sources
Business Information Management
Business Information Service Center | April 2013
Copyright © 2013 Capgemini. All rights reserved.
7
Data Warehouse
 Kimball
 Hub and Spoke
 Inmon
 Enterprise Data
Warehouse
Business Information Management
Business Information Service Center | April 2013
Copyright © 2013 Capgemini. All rights reserved.
8
Layered structure Creatating the ETL problem
Operational
Environment
Business Intelligence
Environment
Data Warehouse Environment
Maintenance
8
1
2
Data Source
layer (DS)
3
Staging
layer (SA)
E
T
L
E
T
L
S
A
ODS
Operational
Data
Store
D
W
Relational/
Source like
5
4
Data
Warehouse
layer (DW)
E
T
L
D
W
Source like
Meta
data
Datamart
layer (DM)
D
M
BI layer
(BI)
E
T
L
E
T
L
Relational/
Dimensional
6
Dimensional
B
I
7
Analysis
Reports
Dimensional
Users
Business Information Management
Business Information Service Center | April 2013
Copyright © 2013 Capgemini. All rights reserved.
9
BI Appliances Hadoop






Expensive dedicated HW
Built for performance
Designed for high volumes (e.g. 10s of TB)
High availability
Initially developed using Relational Data bases
Very mature solutions (skills, SW, HW, administration)
 Designed for modelled and structured data
 Business As Usual ways to design, build and deliver
 Teradata, Exadata, Netezza, HANA...






Commodity PCs
Built for extreme scalability (Batch oriented)
Designed for extreme volumes (10s of PB and more)
Very high availability
Initially developed for web ranking
Not as mature
 Hadoop = Data is distributed over many machines
 MapReduce = Computing is distributed and executed
where data is (grid solution)
Business Information Management
Business Information Service Center | April 2013
Copyright © 2013 Capgemini. All rights reserved.
10
Data Federation
DATA
Balanced Scorecards
Performance Management
Internal data sources
DATA
Virtualization
Layer
DATA
Boardroom
DATA
Data Analysts
External data sources
Business Information Management
Business Information Service Center | April 2013
Copyright © 2013 Capgemini. All rights reserved.
11
In-memory is changing the game
An in-memory appliance
40 x86 cores, 1TB of RAM
For only 100 K EUR !
Performance improvement means:
1 to 10 ratio: 10’’ and 20’’ become instantaneous
1 to 100 ratio: 2 minutes become 1 second
1 to 1000: 2 hours are only 10 seconds
 48 hours process should run in 3 minutes !
Business Information Management
Business Information Service Center | April 2013
Copyright © 2013 Capgemini. All rights reserved.
12
How can we support this kind real time architecture
Use of messages
Call Center
Intermediairies
Internet
Integration Broker
Operational
Datastore
Backoffice
Mortgages
Backoffice
Insurance
CRM
Data
Warehouse
BI Server
Business Information Management
Business Information Service Center | April 2013
Copyright © 2013 Capgemini. All rights reserved.
13
Common Logical Information Model
Data is translated to and from a exchange format that is based on the Canonical
Data Model, Canonical Message Model, Canonical Document Type etc.
Business Information Management
Business Information Service Center | April 2013
14
Copyright © 2013 Capgemini. All rights reserved.
14
CONTENTS
1. Business Intelligence
2. History of Data Warehouse – ETL problem
3. Data Governance and organization
4. Developing the logical model
5. The implementation
6. Supporting functions
7. Supporting the target architecture
Business Information Management
Business Information Service Center | April 2013
Copyright © 2013 Capgemini. All rights reserved.
15
Data Governance
 Data governance requires a transfer of responsibility from the IT system- to
the data-dimension:
Data governance
+
Business Information Management
16
Business Information Service Center | April 2013
Copyright © 2013 Capgemini. All rights reserved.
16
The Data governance organisation
CFO
(DG Executive sponsor)
BIS management
(Data governance steering
group)
Data Governance
(Operational team)
Manager,
Data governance
Business-areas
Support-areas
Data stewards
Head of
disciplines
IT
Stakeholders
The Data governance team
Business Information Management
Business Information Service Center | April 2013
Copyright © 2013 Capgemini. All rights reserved.
1
17
CONTENTS
1. Business Intelligence
2. History of Data Warehouse – ETL problem
3. Data Governance and organization
4. Developing the logical model
5. The implementation
6. Supporting functions
7. Supporting the target architecture
Business Information Management
Business Information Service Center | April 2013
Copyright © 2013 Capgemini. All rights reserved.
18
Distinction Between Data
Master Data
Transactional data
 Examples
 Examples
• Customer
• Financial transactions
• Product
• Incident
• Account
• Step in process
• Organization
• Contact Event
• Employee
• Trade order
• Hierarchies ++
Business Information Management
Business Information Service Center | April 2013
19
Copyright © 2013 Capgemini. All rights reserved.
19
Project plan
Subject 1
Subject 4
Subject 2
Subject 3
Subject 7
Subject 5
Subject 6
Subject 8
Subject 9
Business Information Management
Business Information Service Center | April 2013
Copyright © 2013 Capgemini. All rights reserved.
20
Conceptual Model – More detailed
Business Information Management
Business Information Service Center | April 2013
Copyright © 2013 Capgemini. All rights reserved.
21
CONTENTS
1. Business Intelligence
2. History of Data Warehouse – ETL problem
3. Data Governance and organization
4. Developing the logical model
5. The implementation
6. Supporting functions
7. Supporting the target architecture
Business Information Management
Business Information Service Center | April 2013
Copyright © 2013 Capgemini. All rights reserved.
22
Model Layers
Business Information Management
Business Information Service Center | April 2013
23
Copyright © 2013 Capgemini. All rights reserved.
23
CONTENTS
1. Business Intelligence
2. History of Data Warehouse – ETL problem
3. Data Governance and organization
4. Developing the logical model
5. The implementation
6. Supporting functions
7. Supporting the target architecture
Business Information Management
Business Information Service Center | April 2013
Copyright © 2013 Capgemini. All rights reserved.
24
Data governance tool: Business glossary
Effective data governance processes and toolsBusiness Information Management
Business Information Service Center | April 201325
Copyright © 2013 Capgemini. All rights reserved.
25
Data Quality Services
Business Information Management
Business Information Service Center | April 2013
26
Copyright © 2013 Capgemini. All rights reserved.
26
CONTENTS
1. Business Intelligence
2. History of Data Warehouse – ETL problem
3. Data Governance and organization
4. Developing the logical model
5. The implementation
6. Supporting functions
7. Supporting the target architecture
Business Information Management
Business Information Service Center | April 2013
Copyright © 2013 Capgemini. All rights reserved.
27
Reference Architecture
DATAKILDER
DATAVAREHUS
BITJENESTER
PORTAL
nav.no
NÆR SANNTID
Kø
Operativt
datalager
Operative
data
Styringsinformasjon
navet
Stjerneskjema
Analysedata
METALAG
TJENESTER
Kuber
Rapporter
mobilt
andre
kanaler
Simulering/prognoser
…
METADATA
DATA
HISTORISERING
Statistikk og analyser
Kode
verk
Eksterne
FOR- OG
MELLOMKAMMER
DATAVAREHUSKJERNE
DATATORG
FIM
METADATA
Eksport
Interne
Business Information Management
Business Information Service Center | April 2013
Copyright © 2013 Capgemini. All rights reserved.
28
Common Logical Information Model
Data is translated to and from a exchange format that is based on the Canonical
Data Model, Canonical Message Model, Canonical Document Type etc.
Business Information Management
Business Information Service Center | April 2013
29
Copyright © 2013 Capgemini. All rights reserved.
29
www.capgemini.com
The information contained in this presentation is proprietary.
© 2013 Capgemini. All rights reserved.
Rightshore® is a trademark belonging to Capgemini.