Transcript DNV presentation at Norsk Informatica Brukerforum

DNV presentation at Norsk Informatica
Brukerforum
Experiences and solution strategies from DNVs use of Informatica
Jan Petter Holmberg and Kristian Ramsrud
03 February 2011
DNV’s main services
DNV presentation at Norsk Informatica Brukerforum
03 February 2011
© Det Norske Veritas AS. All rights reserved.
2
Highly skilled people across the world
300
100
9,000
offices
countries
employees, of which 82% have
university degree
DNV presentation at Norsk Informatica Brukerforum
03 February 2011
© Det Norske Veritas AS. All rights reserved.
3
BICC organisation and roles
HR
Business
Owner
Finance
Business
Owner
Business
Spec
Analyst
Business
spec
Report
Author
Business
process
Report
Author
Analyst
Analyst
Business
Advisor
Business
spec
Report
Author
Analyst
Business
spec
Package
developer
BICC (core)
BI
Architect
Analyst
DWH
DWH
Architect developer
BICC (virtual)
Report
consumers
Report
consumers
External services
Divisions
DNV presentation at Norsk Informatica Brukerforum
03 February 2011
© Det Norske Veritas AS. All rights reserved.
Analyst
4
Business
Owner
DNV Data centred services – common platform
Standardised data capture
and storage for all solutions
Standardised processing and
presentation for all solutions
And internal DNV use
1.Interview
forms
2.Data entry
forms
Standardised portals and
presentation for all solutions
Customer
portal 1-n
DNV DB 1
3.Sensor/
voyage recorder
data import
4.Data from
partners
Vadis
(Cognos)
5.Data from
DNV processes
6.Purchased
data
DNV
Datawarehouse
7.Web traffic
DNV presentation at Norsk Informatica Brukerforum
03 February 2011
© Det Norske Veritas AS. All rights reserved.
5
Standardised export
formats to integrate
with customer
systems
Customer
Performance
management system
BI services
KM
support
BI
Competence
& processe
Quality
management
support
Ext services
support
Cognos
Production
support
Finance
support
Data
Data Q.
support
Warehouse
Capacity &
competence
Efficiency
Management
support
Market
Intelligence
DNV Contracts per year
Number of vessels as of 2008.06.01
800
700
Number of vessels
600
500
400
300
200
100
0
2000
2001
2002
2003
2004
2005
2006
Contract year
DNV presentation at Norsk Informatica Brukerforum
03 February 2011
© Det Norske Veritas AS. All rights reserved.
6
2007
2008
Exp/imp
support
External services
DNV presentation at Norsk Informatica Brukerforum
03 February 2011
© Det Norske Veritas AS. All rights reserved.
7
High level BI architecture
Metadata
Technical
Data
population
Source systems
Internal data
Metadata
DNV DWH
Datamart
Certificate
NPS
Business
Portals
Data access
Vadis
DNV
STD. REPORTS
AD-HOC REPORTS
PERF. MANAGEMENT
Inside
Agresso
Integration
• EAI
• PublishSubscribe
Affinitas
Datamart
Finance
BI Portal
(…)
Datamart
Fleet
ETL
ANALYTICS
PLANNING & CONSOLIDATION
MS OFFICE
External data
DNVX
Datamart
HR
LRF
External
AIS
Integration of
external
data sources
X
External/internal applications
Customers’
DNV
DWH
NPS
Y
Affinitas
Other internal
External
Z
Security
Operations
SharePoint
(NGWP)
Authentication
Capacity
Transport
Authorization
Schedule
Audit
DNV presentation at Norsk Informatica Brukerforum
03 February 2011
© Det Norske Veritas AS. All rights reserved.
8
Connection
Error handling
Backup/archive
Performance
own portals
System landscape
In production Jan 2009 – branded as “Vadis”
200 Reports
Already 1000 distinct users
Components: Cognos 8 BI, Planning,
Consolidation, Metric Studio
ETL
1640 daily run sessions
DW
SQL Server
Used for some
source system
specific BI services
1,6 TB
110 Fact tables
260 Dimensions
DNV presentation at Norsk Informatica Brukerforum
03 February 2011
© Det Norske Veritas AS. All rights reserved.
9
Technical details
 Dev – Test – Prod environments on both PowerCenter and SQL Server
 PowerCenter 8.6.1. Upgrade to PowerCenter 9 before summer
 SQL Server 2005
Upgrade to SQL Server 2008 R2 before summer
 From March 24 core CPU, 164 GB memory on SQL Servers
 Supplementary PowerCenter modules
- RealTime
- Informatica Data Quality
DNV presentation at Norsk Informatica Brukerforum
03 February 2011
© Det Norske Veritas AS. All rights reserved.
10
Use of Informatica in DNV
Source systems
Internal data
Realtime integrations
NPS
Agresso
Affinitas
DNV DWH
Datamart
Certificate
(…)
(…)
Batch integrations
Datamart
Finance
ETL
Datamart
Fleet
External data
LRF
AIS
Integration of
external
data sources
X
Datamart
HR
Y
Z
DNV
DWH
DNV presentation at Norsk Informatica Brukerforum
03 February 2011
© Det Norske Veritas AS. All rights reserved.
11
Accessing source systems
Replicated
Database
Web service
Export tables
Views with data manipulation
1:1 views on base tables
Base Tables
CDC
Folder or FTP
Flat file
Integration hub
DNV presentation at Norsk Informatica Brukerforum
03 February 2011
© Det Norske Veritas AS. All rights reserved.
12
Accessing source systems
Level of data
aggregation/manipulation
Web service
Higher uncertainty, less flexibility and
possibly more maintenance
Export tables
Views with data manipulation
and/or aggregation
1:1 views on base tables
Base Tables
CDC
Need of communication between source
system developers and Data Warehouse
team
DNV presentation at Norsk Informatica Brukerforum
03 February 2011
© Det Norske Veritas AS. All rights reserved.
13
Data access - Preferences and Requirements
 Transactional data are required
 Reliable timestamps (if available)
 Consistent keys – also after source system conversions
 ”Untouched” data
 Complete data sets
From a data warehouse point of view, we prefer access to the base tables
All ways of accessing source system data have elements of risk. Navigating in the
landscape of control, flexibility and stability is a political process.
 Standard methods for accessing data with corporate support.
DNV presentation at Norsk Informatica Brukerforum
03 February 2011
© Det Norske Veritas AS. All rights reserved.
14
Tailor made solutions in DNV
S
S
Sys1
S
S
S
S
S
S
S
DW
S
S
Sys2
S
S
As number of dependencies
increases, the number of threads
and decision points becomes
difficult to deal with.
S
Sys3
S
S
DNV presentation at Norsk Informatica Brukerforum
03 February 2011
© Det Norske Veritas AS. All rights reserved.
15
Ensuring data consistency
 Large number of sessions
 One, consolidated data warehouse
 Data quality and integrity?
 What happens if one source system is down or session fails?
 Informatica’s integrated workflow tools:
- Decision points
- Threads with conditions
- Demanding to maintain when the number of sessions and dependencies grow
 DNVs solution
-
All sessions write to a tailor made log table in the data warehouse
Table and session dependencies are registered
Stored procedures: Fail session if dependent objects are not completed
Dependent on developer’s input to the dependency system
DNV presentation at Norsk Informatica Brukerforum
03 February 2011
© Det Norske Veritas AS. All rights reserved.
16
Dealing with dependencies
Log start
Check dependencies
(Will fail if Session 2 is
depending on Session 1)
Update log:
Set not completed
sessions to ”Failed”
Session 2
x
Session 1
Main workflow
Log start
Check dependencies
No end log due
to session failure
Log workflow start
Check if last main workflow
has completed
Log
workflow end
DNV presentation at Norsk Informatica Brukerforum
03 February 2011
© Det Norske Veritas AS. All rights reserved.
17
Data delivery infrastructure
Customer access area
DW Reporting
DW Staging
DW Reporting
 Source data copy
 Log table
 Log table
 Log table
 Temp tables
 Temp tables
 Object dependencies
 Star schemas
 Star schemas
 Intermediate calculations
 Subscription tables
 Stored procedures
 Star schemas
 Stored procedures
 Stored procedures
DNV presentation at Norsk Informatica Brukerforum
03 February 2011
© Det Norske Veritas AS. All rights reserved.
18
Data delivery infrastructure
 Return of enriched data
Source
system
 Integrations
Integrations
(Pull)
DW load
(Push)
DW Reporting
DW Staging
Subscription tables
Web service
DNV presentation at Norsk Informatica Brukerforum
03 February 2011
© Det Norske Veritas AS. All rights reserved.
19
Information needed around the clock
DNV presentation at Norsk Informatica Brukerforum
03 February 2011
© Det Norske Veritas AS. All rights reserved.
20
Global organisation
 Consitent data while data are loading
 Data have to be available
Minimize time window for loading the data that are used by reports
DNV presentation at Norsk Informatica Brukerforum
03 February 2011
© Det Norske Veritas AS. All rights reserved.
21
Data delivery
 Large number of sessions
 ETL time window is a limited resource
 Utilize the ETL load over time
 Deliver data as soon as they are ready
Strategies for parallell load
DNV presentation at Norsk Informatica Brukerforum
03 February 2011
© Det Norske Veritas AS. All rights reserved.
22
Parallell load
Load objects depending
on more than one source system
Final
Publish
Publish
Load source
system 1
Load source
system 2
Publish
Publish
Load source
system 3
Load source
system 4
Load common
dimensions
Common
DNV presentation at Norsk Informatica Brukerforum
03 February 2011
© Det Norske Veritas AS. All rights reserved.
Load source system
specific objects
23
Disabling source systems when needed
Load
Final
Disabled
Load objects depending
on more than one source system
Publish
Source system 3 is
main data source
(use old SS4 data)
Publish
Load source
system 1
Load source
system 2
Publish
Load source
system 3
Disabled
Load source
system 4
Common
DNV presentation at Norsk Informatica Brukerforum
03 February 2011
© Det Norske Veritas AS. All rights reserved.
Source system 4 is
main data source
24
A table is used for
toggeling
source systems on/off
Publish data to report marts
Session 1
Check for normal load size
Table1_tmp
P(x)
Swap table names
DNV presentation at Norsk Informatica Brukerforum
03 February 2011
© Det Norske Veritas AS. All rights reserved.
25
Table1
Tailor made stored procedures
 Start Session
- Check dependencies, fail session if dependent tables is not completed
- Write to DW log table
- Session start
 End Session
- Write to DW log table
- Session end
- Statistical info
 Truncate table
- Empty table in a secure way.
(PowerCenter’s truncate table option can not be used due to the use of stored procedures)
 Swap Tables
- Swap table names
- Check data integrity
DNV presentation at Norsk Informatica Brukerforum
03 February 2011
© Det Norske Veritas AS. All rights reserved.
26
ETL initiation
 Scheduled load
- Nightly
- Multiple times per day
 Cognos calls web services for initiating ETL load on near real time basis
DNV presentation at Norsk Informatica Brukerforum
03 February 2011
© Det Norske Veritas AS. All rights reserved.
27
Wrap up
 DNV has to deal with a large number of sessions that feeds one, consolidated data
warehouse.
 We have tuned PowerCenter to fit with these special requirements
 Questions or comments?
DNV presentation at Norsk Informatica Brukerforum
03 February 2011
© Det Norske Veritas AS. All rights reserved.
28
Safeguarding life, property
and the environment
www.dnv.com
DNV presentation at Norsk Informatica Brukerforum
03 February 2011
© Det Norske Veritas AS. All rights reserved.
29