Research Article, J Comput Eng Inf Technol Vol: 6 Issue: 5
Data Warehouse System in Shell Corporation Oil and Gas Upstream Market
Department of Information Technology, Cardiff Metropolitan University, Cardiff, United Kingdom
Received: June 21, 2017 Accepted: July 11, 2017 Published: July 16, 2017
Citation: Mosheshe J (2017) Data Warehouse System in Shell Corporation Oil and Gas Upstream Market. J Comput Eng Inf Technol 6:5. doi: 10.4172/2324-9307.1000185
The paper seeks to provide a broad analysis and deep evaluation of the present and future profitability, availability and financial stability of the oil and gas upstream market with regards to how they are managed using the data ware house system as supported by the senior system analyst in Shell Corporation, in order to improve the performance of the company’s proceedings. This document explains the stakeholder’s analysis and the company’s scope statement such as requirements, characteristics and deliverables of the project. Several assumptions were made on the project cost and benefits in regards to the data warehouse system and its benefits estimate per annum. Other calculations included equipment bought, training of staffs, as well as loses in regards to hours and sales. The result of the analysed data show that, all cost was below the company’s average income especially for the fact that comparative market performance is poor in the areas of quantity, cost and benefits. Furthermore, the company’s prospect in its present position is weak in regards to the upstream market and an improvement may be realised by the introduction of the data warehouse system, creating a training program for staffs to enable easy incorporation of the data warehouse system, and reconstructing the entire forecast potential growth of Shell Corporation.
Keywords: Data warehouse system; Oil and gas upstream market; Databases; Shell corporation; Key players
With the rapid rise and advances in information technology globally, it has become much more important to find a very lucrative as well as profitable alternative in other to accomplish a peculiar task or project due to the increased need of information technology in various companies in the world at large. In this project, the data warehouse was closely looked into as well as their uses and major suppliers in regards to how they relate with the upstream market in Shell Corporation, and how they may enable the Shell Corporation in managing their company’s forecast potential growth. Also, the key players in Shell Corporation were discussed at length, the initiation, planning and execution of the entire project were explained, as well as how the desired goal was achieved were critically looked into. In the computing field, which includes information technology, computer engineering, computer science, information systems and software engineering, a data warehouse system, which can also be referred to as an enterprise data warehouse system, is a database system utilized for analysing and reporting, and can be termed as an essential part of business intelligence .
Concept of data warehouse system
Data warehouse systems can be described as the paramount repositories of unified data that were derived from several divergent sources, which accumulate both current and already documented data in a single location, and are utilised for developing analytical reports for the purpose of enabling knowledge workers gain access to needed data in an entire organisation . The accumulated data located in the company’s warehouse system can be usually uploaded from company’s operational systems such as the sales or marketing department. The accumulated data may be passed using an operational data store, which may need adequate data cleansing for the purpose of enabling an improved company operations, in order to guarantee the quality of the accumulated data, before it can be utilized in the data warehouse system for further analysis and reporting . The regular ETL- based data warehouse system, also known as extract, transform, load, utilizes the data integration, staging database and access layers to accommodate its major functionalities. The staging database accumulates raw data that has been derived from every of the divergent source of the data systems. The integration layer incorporates the divergent data sets through converting the derived data from the staging database, frequently storing the already converted data into an operational data store database . The incorporated data can then be relocated to another database, known as the data warehouse database system, were the derived data are organised into a hierarchy, usually referred to as dimensions, after which the derived data can be then transformed into accumulated and raw organisational facts. The combination consolidation of both the raw and accumulated facts and dimensions are thereby referred to as the star schema. Moreover, the access layer enables end users in retrieving the accumulated data from the data warehouse database system when needed . The major purpose of the data warehouse system is that it enables the accumulated data to be transformed, cleansed, organized, and made available for end users by the company’s management and several other marketing professionals for online analytical processing, data mining, decision support, and for organisational research purposes . Moreover, the channel in analysing, retrieving, transforming, extracting, loading, and managing the derived data stored in the data dictionary can be also termed as being a relatively significant ingredient of the data warehouse system. The data warehouse system encompasses various business intelligence tools, which includes the tools used in extracting, transforming, and loading the derived data into the data repository, as well as tools used in retrieving and managing various metadata. The copy of each information derived from the raw data is maintained by the data warehouse system, which is usually located in the transaction systems source . This architectural ramification of the data warehouse system supplies the chance for companies to be able to accomplish numerous organisational goals, such as:
• Integrating data from one or more sources into a particular data model and database.
• Mitigating the challenges of database confinement level lock controversy in TCP, also known as the transaction processing systems, originated from efforts made in running large queries, analysis queries, or long running queries in the transaction processing database systems.
• Maintaining the data history, even when the source transaction systems are unable to successfully obtain this task.
• Integrating the associated derived data from diverse source systems, in order to facilitate a major view in the entire organisation.
• Improving the quality of the derived data, through supplying consistent descriptions and codes, flagging, and even repairing corrupted data.
• Consistently providing the company’s information when needed.
• Supplying a single regular data model for every needed data, irrespective of the associated data source.
• Restructures raw data in order to gain useful meanings to the end users, by delivering an excellent query performance without obstructing operational systems.
• Enables ease in writing decision support queries.
• Enables database administrators in organizing and disambiguating repetitive data found in the database .
The most successful organisations presently found in the world market, are those that are able to adequately respond to the market opportunities and changes in a flexible and speedy manner. A principal strategy to this swift response is deriving the ability to incorporate data warehouse system in operating company’s upstream market endeavours, such that it creates an efficient and effective utilization of organisational data and information by the company’s management and analysts and managers, respectively .
Concept of upstream market
These can be described as the different stages of operations in oil and gas companies that include production and exploration. They trade primarily with the exploration stages of the oil and gas industry with other upstream companies and take the first steps to locate, drill and them first for oil and gas. After which reserves are proven and these upstream companies would take out any oil and gas that was found from the reserves. In most cases oil and gas companies are usually separated into three various parts such as the upstream, midstream and downstream. As a matter of fact a lot of very big oil companies are known to be integrated because they join the upstream operations with midstream and downstream. All of these can only be accomplished only after the production phase has been completed to the point of sales .
Upstream companies primarily deal with the fundamental production and exploration phases of the oil and gas sector. Majority of the big oil and gas organisations can be referred to as being integrated companies, due to the fact that they merge a mixture of the upstream undertakings with the downstream and midstream proceedings that transpires usually after the company’s production stages, by the utilization of the point of sale. The upstream region of the oil and gas company contains every step involved in the undertaking from start to finish, which encompasses the preparatory and exploratory stages to the extracting of resources stages. Most upstream organisations can either be involved in every step of the entire life cycle phases of the oil and gas company undertakings, or the upstream organisation may only deal with the specific stage that involves only the upstream pathway .
The upstream oil and gas stages, which can be also referred to as the production and exploration sector is a significant aspect of the upstream market, since it entails the exploration of petroleum utilising very refined techniques, as well as the information technology process that is accessible for exploration petroleum is rapidly advancing. Typically, exploration begins in a region that has great potential to contain adequate resources, which are normally checked by the geology in the surrounding area for established petroleum deposits around the region. When the surrounding area has a greater resource potential, additional exploration is obtained in order to identify a resource. Analysis obtained by geochemists and geophysics can be derived by utilizing various techniques, which includes, assaying and drilling, induced polarization surveys, and electrical currents. The actual goals of the exploration and production phases are to identify and assess the precise potential of the given resources, such that when a location exhibits the potential to accommodate a resource, then exploratory holes may be drilled in testing the potency of the given resources .
The test drilling is a significant aspect of the exploration and production stages, whereby a successful exploration of resources may lead to the construction of holes and in order to deracinate the resource. Furthermore, upstream companies engage the holes through which the resources, which includes natural gas or crude oil to surface level for better extraction. Thus, after the extraction process has been completed, the work of the upstream company has been accomplished, and thereby completed. This enables the midstream companies in accumulating the derived raw resources, and relocates these resources through various means such as pipelines, tanker truck, and railways to various refineries, which are the downstream companies in the oil and gas sector. The refineries may endeavour to process the raw resources into their desired outcome, such as processing crude oil into petroleum commodity, as well as the selling and distribution of the refined resources to end users for profit, which involves the natural gas and appropriate commodities obtained from the crude oil .
Shell Corporation which is one of the world’s leading energy and gas companies by assisting in meeting the world’s growing energy demand in the environment, economy and social areas is a company that performs duties for business transactions without having any significant assets or operations for its own self. It is a corporation that lacks active business operations or significant assets for its owns selfdue to the fact that it is serving as a vehicle. The company is neither illegitimate nor illegal, since they frequently serve an important for potential start-ups . Also, the company acts as tax avoidance for legitimate businesses and can be called international business company, front company and personal investment company. They are mostly formed before operations start in other to obtain sponsorship financially. The company is legal in virtually all countries but sometimes fraudsters try to use them in the gray or black market activities .
The company makes a very big contribution to the UK economy by investing in its economy, providing various products and services to the masses and also by employing people and helping in the area of tax revenues. The company has employed over 6,400 people in the UK and over millions in the world at large. They are the major provider of this energy that billions of people use in heating and powering their properties in the UK. They are also leading globally in the area of upstream for over 30years now and has benefits in over 50 fields, they operate over 30 platform installations, one FPSO vessel, 30 subsea installations, a very large marine terminal and three onshore gas plants as a matter of fact this company is undoubtedly successful but still needs a major improvement in their upstream market .
Key players in shell corporation
Key players can be described as are those politically sharp individuals which have the ability to access situations critically and accurately make things happen in an organization. They can be identified by their ability to make instant decisions without having to refer anyone to a higher rank than themselves.
Figure 1 states the names and positions of all the key players in the shell corporation company which has been critically analyzed in the above diagram, and encompasses a few major positions namely:
• The director: These are responsible for the long term decisions that go on in the company. They act on behalf of the shareholders to run the day to day affairs of the company and provide report on what the company’s future plans, strategies and how they can expand .
• The office of public affairs: These people are responsible for coordinating and facilitating the company’s relationships with their neighbouring communities, local, state, media, general public and the federal government .
• The office of security: These are the people who take care of the companies risk management system by managing their long and short term risks through avoidance, transference, mitigation and acceptance. They are also involved with the companies standards, policy and they secure the company as well as every human connected it .
Data warehouse system in shell corporation
Data warehouse is a database system that is used for reporting and analysing data. It is a central repository of data which is created by integrating data from one or more disparate sources. In a nutshell it is relational database designed for query and analysis of data instead of transactional processing. It mostly has historical data gotten from transactional data and sometimes it also includes data from other various sources . It divides analysis workload from that of transaction workload and also empowers a company to consolidate data from various sources. Also its environment includes transportation, extraction, transformation, and loading solution also known as ETL solution, client analysis tools, online analytical processing engine also known as OLAP engine and other applications which can be used to manage the process of gathering data and delivering to the end users . Figure 2 below shows the data warehouse system.
Need for dataware house in shell coporation
• It can be used to hand over quality business intelligence for the company
• It is used to access critical data quickly
• It is used to improve the quality and consistency of the company’s data since it converts data from different system sources to a common format
• It provides historical intelligence by analyzing different time and period in the company’s record in other to make future predictions .
Popular suppliers of data warehouse
Sun micro system: These are the major suppliers of data warehouse. They supply commercial package and open souse data warehouse.
MySQL: These set of suppliers supply performance schema build configuration and performance schema start-up configuration security.
Oracle: These set of suppliers supply SQL model calculations, partitioning enhancements and materialized view refresh enhancements.
ERP: This is also known as the enterprise resource planning, it is a software that integrates all area of the company’s operation such as its product planning, product development, sale processing, manufacturing and marketing of the company’s data .
Key Critical Observation/Commentary
My observation is that I have been able to distinguish between the potential financial benefits, risk and opportunities that are involved in the data warehouse and I have realized that it is better to use the data warehouse system for a company to grow than any other software system namely:
Potential financial benefits
• They enhance business intelligence
• They enhances Quality and Consistency
• They provides historical intelligence
• The data warehouse saves processing time .
• They do support business requirements
• They help users perform adequately
• They increase the number of mixed applications
• They assist in providing optimal warehousing performance .
Potential negative risks
• They are very expensive to purchase and maintain
• Their performance can crash at any time
• They can be hacked into causing weak security .
In Table 1 the analyst is identifying the various parties that will likely affect or be affected by any action that will be taken as they try to achieve their desired relationship in other to fulfil the projects goal .
|Interested Party||Current Relationship||Desired Relationship||Interfaces||Key Messages|
|Shell Corporation||Project sponsors and one of the company’s founders||Project Initialisation||Very high on the project||Changing the way it delivers services in order to provide a more effective and efficient service, ensuring that the right service is delivered first time.|
|System Analyst||Performing data warehouse system analysis||Construct a robust system which will meet user requirement||Influential on the successful outcome of the project||Analysing all services in the most efficient manner possible, allowing us to provide maximum protection with the resources available to us.|
|Project Manager||Managing project team, meeting the project committee||Make sure the project meet quality and company forecast potential growth||Influential on how the resource are distributed and managed||With a high performing force that has been extremely successful in increasing levels of supervision for staffs|
|Shell Employees||Providing information for data warehouse software development||Providing the right information||Developing a system that will fit user requirement||We are committed to providing the right information, in order to reduce errors and setbacks.|
|Hardware Vendor||Supplies some hardware instruments||Providing a quality system||Low, other vendors available||We work together to ensure that the customer’s priorities and services, are delivered continuously with great success.|
Table 1: Stakeholder analysis of shell corporation.
This (Table 2) explains how the project initialized its desired goals by sorting out all the needed requirements and envisioning how the final outcome of the project will be .
|•Data are combined from multiple sources
•Data copied must not be changed
•Large amount of data can be stored in the company
•It has single view of operational entity
•It has a great deal of speed
•It acts as a centralised repositioning of a company’s data
•It provides a comprehensive and homorganic view of the company
•The best swap space must be double the physical memory
•Monitor display should be 1024 x 768 colour resolution using 256 colours
•Microsoft Windows 2008 Standard Edition, Enterprise Edition with Service Pack 1
•Red Hat Enterprise Linux 5 GA with Update 1
•10/100M Ethernet card with IPv4 or IPv6 protocol
•10/100M Ethernet card with IPv4 or IPv6 protocol
|•Researching of information for report
•Making the final report
Table 2: Shell corporation scope statement.
Assumptions on Product Cost and Benefits
There are a number of general default assumptions that will be used to guide the result analysis of the project cost and benefits (Table 3), below are a few points:
|Cost/Benefits Analysis for Data warehouse System|
|Equipment||Workstation C/W Software||4||£500||£2,000|
|Network Cables Installations||1||£3,500||£500|
|Training||Basic Computer Skills||8||£200||£1,600|
|Customer Service System||8||£150||£1,200|
|Other||Man Hours Lost||150||£15||£2,250|
|Sale Lost during Initial Stage||£10,000||£10,000|
|Benefits Estimate Per Annum|
|Postal Campaign and Telesales||£50,600|
|Accuracy of Client Information||£20,000|
|Improvement in Management Efficiency||£35,000|
Table 3: Cost/Benefit analysis of data warehouse system.
• The project takes into consideration the impact of this project across all sectors of its economy by reflecting on the interrelationship between these sectors.
• The researcher has also included all tangible and intangible cost and benefits where they can be estimated reliably .
Incorporating Data Warehouse System
There are various oil and gas industries around the world, having various upstream and downstream market, as well as, numerous key players in the various oil and gas industries around the globe such as BP, Gas prone, Saudi Oil Company, and the shell corporation. These various company’s make use of data warehouse systems, utilizing different software in saving and retrieving data, as well as utilizing various software providers . However, for Shell Corporation to be able to be ahead in the competitive world market, and achieve their company’s objectives, the following must be observed as indicated below:
• Retail sector: In the retail sector the loyalty of the customer as well as good market planning will help project success
• Financial sector: In the financial sector detecting fraud and risk management precautions will help project success
• Manufacturing sector: In the manufacturing segment reducing cost and logistics management will aid project success
• Utilities sector: In the utility sector management asset and managing of resources will help project success
• Government sector: In governance, controlling cost as well as manpower planning will help project success .
The project contributed largely in the area of creating the company’s a scope statement, team contract, stakeholder’s analysis and in making assumptions for the project cost and benefits by bringing new ideas in other to achieve the company’s desired goals. The use of data warehouse in various companies has significantly impacted our world today in a very big way . A lot of companies such as the Shell Corporation may greatly realize the need and usefulness of the data warehouse system, and may be able to take good advantage of it, if fully incorporated. Whereas, several smaller companies with lower revenues may not be able to make use of it because of its high cost of purchase. Hopefully in years to come, the data warehouse system prices may go down, and more companies may be able join and enjoy the fast growing effectiveness of the data warehouse system, which will in turn help to meet the company’s needs and most importantly their customer’s needs .
- Schwalbe K (2007) Information Technology Project Management. (6th edtn), Course Technology, Boston, United States
- Hanai Y, Aoyama M, Suzuki M (1988) An Integrated Software Maintenance Environment Bridging Configuration Management and Quality Management. IEEE Computer society press, Washington, D.C., United States.
- List B, Bruckner RM, Machaczek K, Schiefer J (2002) A Comparison of Data Warehouse Development Methodologies Case Study of the Process Warehouse, Proceedings of the 13th International Conference on Database and Expert Systems Applications.
- Lechtenbörger J (2001) Data warehouse schema design. Technical Report 79, DISDBIS.
- Kimball R, Reeves L, Thornthwaite W , Ross M, Thornwaite W (1998) The Data Warehouse Lifecycle Toolkit: Expert Methods for Designing, Developing and Deploying Data Warehouses with CD Rom, John Wiley & Sons, Inc., New York, United States.
- Inmon WH (1992) Building the data warehouse, QED Information Sciences, Inc., Wellesley, Hoboken, New Jersey, United States
- Hüsemann B, Lechtenbörger J, Vossen G (2000) Conceptual data warehouse design. Proceedings of the International Workshop on Design and Management of Data Warehouses (DMDW’2000), Stockholm, Sweden.
- Golfarelli M, Rizzi S, Saltarelli E (2000) WAND: A case tool for workload-based design of a data mart. Proceedings of SEBD, Portoferraio, Italy.
- Golfarelli M, Rizzi S (1998) A methodological framework for data warehouse design, Proceedings of the 1st ACM international workshop on Data warehousing and OLAP, Washington, D.C., USA.
- Olaguer EP, Erickson M, Wijesinghe A, Neish B, Williams J, et al (2016) Updated methods for assessing the impacts of nearby gas drilling and production on neighborhood air quality and human health. J Air Waste Manag Assoc 66: 173-183.
- Eapi GR, Sabnis MS, Sattler ML (2014) Mobile measurement of methane and hydrogen sulfide at natural gas production site fence lines in the Texas Barnett Shale. J Air Waste Manag Assoc 64: 927-944.
- Allen DT (2016) Emissions from oil and gas operations in the United States and their air quality implications. J Air Waste Manag Assoc 66: 549-575.
- Data warehousing concepts (2013), Oracle 9i Warehousing Guide.
- Schwalbe K (2010) Managing Information Technology Projects (6th Edtn), Augsburg College, PMP certification, Minneapolis, United States.
- Ralph K (2013) Practical Techniques for Building Dimensional Data Warehouses, The Data Warehouse Toolkit, John Wiley & Sons, Inc. New York, NY, USA.
- Shell at a glance (2013).
- Ryan HW, Sargent SR, Boudreau TM, Arvanitis YS, Stanton J (1998) Practical Guide to Client Server Computing, (2nd edtn), Auerbach Publications, New York, United states.
- Walliman N (2005) Your Research Project: A Step-by-Step Guide for the First-Time Researcher. (2nd Edtn) SAGE, Thousand Oaks, California, United States.
- Sharp JA, Peters J, Howard K (2002) The Management of Student Research Projet. (3rd Edtn),Gower, Germany
- Georgina P, Nicchiarelli E, Mylopoulos J, Sebastiani R (2003) Formal reasoning techniques for goal models. J Data Semant, Springer 2800:1-20
- Bruckner R, List B, Schiefer J(2001) Developing requirements for data warehouse systems with use cases. In Proceedings 7th Americas Conference on Information Systems, Boston, United states.
- Golfarelli M, Maio D, Rizzi S (1998) The dimensional fact model: A conceptual model for data warehouses. Int. J. Coop. Info. Syst 7: 215-247
- Olaguer EP, Knipping E, Shaw S, Ravindran S (2016) Microscale air quality impacts of distributed power generation facilities. J Air Waste Manag Assoc 66: 795-806.
- Dardenne A, Lamsweerde Av, Fickas S (1993) Goal-directed requirements acquisition, Sci Comput Program 20:3-50.
- Bulos D (1999) Designing OLAP with ADAPT. Technical report, Atos Origin.
- Castro J, Kolp M, Mylopoulos J (2002) Towards requirements-driven information systems engineering: the Tropos project, Information Systems 27: 365-389.
- Public affairs (2013) Stanford University.
- Smeets J, Boyer S (1998) Internet and Client Server Patent Information Systems: New Services From Derwent Commercially Developed Systems/World Patent Information, 20: 136-139.
- Tsuda H, Uchino K, Kunio (1998) WorkWare: WWW-based Chronologic Document Organizer IEEE, Shonan Village Center, Japan
- Cramer D (2003) Advanced Quantitative Data Analysis, McGraw-Hill Education, United Kingdom