‘The’ African Oil Company - Data Warehouse
Implementation
• Engen Petroleum
• Why Engen use Data Warehousing
• Data Warehousing - Scenario
• What is Different
• Conclusion
Engen Petroleum Limited
• ‘The’ African Oil Company
• Annual Turnover - $2.25 Billion
• Staff Complement - 3000
• Countries of Operation
• Operating Units
Data Warehouse
Why
• Organisations world-wide recognise that
information is an invaluable enterprise asset
• One source of Enterprise Management
Information
• Provide the ability to monitor data integrity and
quality through its use
• Delivery of analysis for Balanced Score Card,
Customer Profiling, Target Marketing, Marketbasket Analysis, Data Cleansing and Data Mining
Data Warehouse
Resource
Intensive
Processes
not in
place
Nonintegrated
Data
Organisation
Structure
Inadequate
Hardware
Why
•Automated Environment
•Skilled IW team
•Sound Hardware Platform
•All management processes
in place
•Data Integration
mechanism implemented
•High level of business
involvement
•Deployed to 400 + business
people
Data Warehouse
Individually
Structured
Operational Systems
Scenari
Front-end
o
Departmentally
Structured
Data Warehouse
External
OLAP
Organisationally Structured
SAP
PeopleSo
ft
TDC
Transformation/
Integration
Programs
Back-end / ETL
Archived Detail
Metadata
Operational
Enterprise Data
Warehouse
Data
Acquisition
Data Access
Analysis
Environmen
t
Environment
Data Propagation
External
Environment
Information Warehouse Applications
Platfor
m
DBMS
Systems Management
Slide is intellectual property of Gavroshe
Network
Data Warehouse
Organisation Structure
Executive
Business
Analysts /
Consultants
Warehouse
Information Systems
Knowledge Worker 4
Back End Developer
Retail 2
Front End Developer
1
Database Administrator
International
Data Architect
1
System Administrator
Etc …...
User Support
/ Help Desk
Refinery 1
Data Warehouse
All Members
Personal attributes
•
•
•
•
Team player & Work in project environment
Self confident & Self motivated
Willingness to ‘go the extra mile’
Ability to keep up to date with constant change in BI
environment
Data Warehouse
Knowledge Worker
Required
• Business degree & business experience
• Excellent understanding of PC, Networks & data
environments etc.
• Power user skills in windows environment
• Prior Systems Development in a functional role
Operations
• Educate users
• Analyse of information requirements & data quality
• Identify data sources
• Formulate transformation & business rules and routines
• Verify the data in the Warehouse and datamarts
Data Warehouse
Knowledge Worker
Operations
Continued
•
•
•
•
Develop Warehouse applications and web deliveries
Document the applications through metadata
Gain consensus on common data definitions
Evaluate warehousing software tools
Leadership
• Able to lead a project or user group
Data Warehouse
Front-end Developer
Required
• Degree in Computer Science / Information Systems etc.
• Strong in analytical, problem solving and programming
• Skilled in Front End Application development
• Some business knowledge
• Effective communication skills, verbal & written
Operations
• Code and test closed-loop applications
• Evaluate warehouse software tools
• Search for causes of incompatibility between the
Warehouse and other systems
Data Warehouse
Front-end
Developer
Operations
Continued
• Develop and maintain DSS and EIS applications
• Develop and maintain internet and intranet delivery
applications
• Test and document the applications
• Identify data sources
• Develop and document test plans for the applications
Interpersonal
• Able to interact at all levels in the organisation
Data Warehouse
Data Architect
Required
• Degree in Computer Science / Information Systems etc.
• Strong in analytical, problem solving and programming
• Data Modelling
• SQL experience
Operations
• Owner and developer of BIW Data structures and the
modelling thereof
• Data modelling – Logical and Physical
• Process owner of metadata management system and
information directories
Data Warehouse
Data Architect
Operations
• Implement & assist in definition the overall architectures and
standards.
• Integration of all subject areas to maintain a fully integrated
and architected Warehouse
• Gaining consensus among user department on common
data definitions
• Assist the IS DBA’s in Database administration
• Developing monitoring systems to ensure data has been
updated as expected
Data Warehouse
User Support
Operations
• New/existing user requests
• Educating users
• Monthly Audit, Load and Delivery monitoring
• IW Help/Call Desk Administration
• Maintain user access management system
Data Warehouse
Backend
Developer
Operations
• Access data sources as agreed with BIW
• Develop and Document test plans
• Develop, Automate, Maintain and update acquisition,
transformation and loading processes
• Test and correct applications
• Evaluate and select data transformation / cleaning software
and middleware
• Schedule data updates and report generation
• Develop and/or acquire metadata management systems and
information directories
Data Warehouse
It’s not what you do
But How You Do It
Conclusion
Title - ‘The’ African Oil Company Data Warehouse
Implementation
Author - Richard Magnin - Engen Petroleum
Monday, 17 April 2000
Engen Petroleum
Engen’ s vision is to be the leading African oil company' committed to
meeting the agreed needs of its customers in order to increase its
business interests and profit share in Africa.
History - Engen was previously the wholly owned subsidiary of the US
multinational , Mobil Oil Corporation , up until 1989 when it was sold to
GENCOR a South African company. It subsequently became the first
listed oil company on the JSE . Petronas, the Malaysian Oil Company,
purchased a 30% shareholding in Engen in 1997 and 1999 acquired
the remaining shareholding . The company was de-listed from the JSE
and in late 1999 World Wide Africa, a black empowerment group
acquired a 20% shareholding in Engen.
Engen has an annual turnover of $2.25 Billion
Engen in currently represented in over 25 African countries including Namibia,
Botswana, Lesotho, Swaziland, Mozambique, Zimbabwe, Malawi,
Kenya, Burundi, etc
Engen is an integrated oil company operating in both Upstream and
Downstream activities in the industry. Upstream activities ( exploration
and production ) are conducted through Engen’s subsidiary Energy
Africa. Downstream activities ( refining and marketing ) have resulted in
Engen capturing 23% market share in South Africa and include :
Crude Purchasing
Shipping
Refinery (100 thousand barrels per day)
Oil and Grease Blend Plants
Transport
Chemicals & special products
Convenience Stores
Why Engen use Data Warehousing
Data warehousing in Engen has a fairly long history. We started by setting up
a file server in 1992 on which we stored Dbase (dbf) file structures. By
1994 we found limitations with files of 1 million plus records and moved
on to an Oracle database on a Novel server.
We developed an Executive Information System (EIS) at this time but found
the warehouse processing capability too small and thus did all
processing for the EIS on the mainframe in SAS. Then bring the results
to the network and delivering from dbf files using a product call Pilot
Lightship (now Pilot Decision Support Software) as the front-end.
A marketing Management IS was developed in 1997 using the same
approach but delivering with a Visual Fox application developed inhouse.
This fragmentation of environments & developments, a database not readily
available to all, resource intensive application maintenance and of data
collection caused a rethink of our approach.
An initiative to use the Balanced Scorecard (BSC) as a means of measuring
the progress of Engen towards its strategic direction was started and
this clearly identified the business information requirement from the
Chief Executive Office down to levels 3 and 4 within Engen.
The clarification of the BSC, the identifying of the technical and physical
environment, the need to deliver to at least 400 users and the
fragmentation of the business and technical metadata identified that
our then current data warehouse strategy would not deliver the
business needs in the medium to long term.
Data Warehousing - Scenario
Data warehousing must be seen in a complete picture and the designed
architecture must incorporate this.
All aspects of data warehousing should be included from Sourcing, Extracts,
transformations, loads, user access through query tools as well as
deliveries required and agreed to by the business users.
When proposing tools or explaining the operation of a warehouse it must be
remembered that users are mainly interested in Interface and Usability
of the analysis environment and applications and not in the tool set
itself.
An internal group was set up to identify and establish the Ideal and Engen
specific data warehouse infrastructure. Including the technical and
physical environment, the types of tools and identify the personnel
required. Consultants guided us.
All levels of DW Architecture and Data Modelling was done with an
independent consultant who then trained our current data architect
Selection of software was done by 3 members of the data warehouse and the
final choice was presented to the IT group (to ensure technical support)
and to the General Manager level steering committee.
Selection of staff for the data warehouse was then made.
Implementation of the data warehouse followed
What is different
A business driven solution using IS technical skills to support the
infrastructure but business analysts to develop and support the
information deliveries.
The warehouse therefore requested that the following individuals continue to
report to and be managed by the IS Manager.
Database and System administration support requirement would not be full
time and a sharing of these resources, with SLA's, would serve Engen
best.
Organisation Structure
Data warehouse consist of:
A Front-end Developer
A Data Architect
4 Knowledge Workers
A User support / Admin and Help Desk
A Back-end Developer on assignment
!
Personal attributes of all members
Team player & Work in project environment
Self confident & Self motivated
Willingness to ‘go the extra mile’
Ability to keep up to date with constant change in BI environment
!
Knowledge Workers Required:
A Business degree & some business experience
Excellent understanding of PC, Networks & data environments etc.
Power user skills in windows environment
Prior Systems Development in a functional role
Operations include:
Educate users
Analyse of information requirements & data quality
Identify data sources
Formulate transformation & business rules and routines
Verify the data in the Warehouse and Datamarts
Develop Warehouse applications and web deliveries
Document the applications through metadata
Gain consensus on common data definitions
Evaluate warehousing software tools
Leadership:
Able to lead a project or user group
!
Front-end Developer
Required:
Degree in Computer Science / Information Systems etc.
Strong in analytical, problem solving and programming
Skilled in Front End Application development
Some business knowledge
Effective communication skills, verbal & written
Operations:
Code and test closed-loop applications
Evaluate warehouse software tools
Search for causes of incompatibility between the Warehouse and other
systems
Develop and maintain DSS and EIS applications
Develop and maintain Internet and Intranet delivery applications
Test and document the applications
Identify data sources
Develop and document test plans for the applications
Interpersonal:
Able to interact at all levels in the organisation
!
Data Architect
Required:
Degree in Computer Science / Information Systems etc.
Strong in analytical, problem solving and programming
Data Modelling
SQL experience
Operations:
Owner and developer of BIW Data structures and the modelling thereof
Data modelling – Logical and Physical
Process owner of metadata
Implement & assist in definition the overall architectures and standards.
Integration of all subject areas to maintain a fully integrated and designed
Warehouse
Gaining consensus among user department on common data definitions
Assist the IS DBA’s in Database administration
Developing monitoring systems to ensure data has been updated as
expected
!
User Support
Operations:
New/existing user requests
Educating users
Monthly Audit, Load and Delivery monitoring
IW Help/Call Desk Administration
Maintain user access management system
!
Backend Developer - remain in IS as this is their preferred career path
and, after initial set up, the support requirement would be low.
Operations:
Access data sources as agreed with BIW
Develop, Automate, Maintain and update acquisition, transformation
and loading processes
Evaluate and select data transformation / cleaning software and
middleware
Schedule data updates and report generation
Conclusion
It's not WHAT you do
But HOW you do it.