Digital Energy Journal- Getting rid of Excel

Join | Existing Member Login

You are Home » News » View Article

Getting rid of Excel

Thursday, January 17, 2013

A good step to gaining control of the data deluge is to try to avoid the Visual Basic Applications, Excel and Access, suggests Mark Reynolds

Operational teams have embraced Excel workbooks and Access databases because these tools make it easy process data and information. However, today's operations environment, the big-data digital energy environment, must find a better way.

The modern E&P organization has found itself awash in workbooks and standalone databases such as Microsoft Excel and Access.

The need to 'keep the bit turning' has encouraged reactionary solutions to operational challenges.
So this reliance on standalone tools for reporting, estimating, and analysis should be no surprise.

But the magnitude of the reliance and sheer quantity of the resulting workbooks has become a data management nightmare.

Standalone workbooks and databases based on ODBC (Open Database Connectivity) and VBA (Visual Basic for Applications) have been allowed, overlooked, or even encouraged by traditional IT development and support organizations.

As a result, workbooks and databases proliferated throughout the operations organizations, being neither supported nor coordinated within the corporate infrastructure.

These challenges are not unique to the energy industry, but the 'Big Crew Change' is well underway while data collection is increasing at an ever faster rate.

The standalone and personalized approach to handling operational challenges does encourage real and timely solutions within the operations and business units.

But this approach does not encourage source control, code reuse, or robust application development.

Nevertheless, the business units within operations have learned how to perform complex tasks within the confines of ODBC and VBA.

Business Unit Applications

Business unit applications (BU-Apps) are developed within operations to address and solve the challenges in operations.

They are operations-centric software and data systems and technologies. Not financial systems and not accounting systems.

Generally BU-Apps provide solutions to challenges within operations by addressing two specific requirements and expectations:

1) Live-action management of resources and process improvement

2) Documentary artifacts addressing design engineering and as-built product descriptions.

They are distinctly focused on keeping the bit turning and keeping the product flowing, while reducing HSE incidents, reducing NPT, and increasing quality.

Targeted ad hoc solutions to specific or immediate needs have created a paradigm of software and data management with no clear target for the end product. And they may grow in an evolutionary mode for months or years - often with no long-range vision.

Today VBA applications thousands of lines long and complex embedded SQL queries are pervasive within the resulting tools and applications.

New paradigm

Traditional IT teams are strategic and are designed to address long-range business objectives.

Business unit development is tactical and operations-centric; BU-Apps must address challenges to 'keep the bit turning'.

BU Apps are not necessarily wrong or bad. But they can and will produce other challenges for the operations team. So operations-centric, business unit developed applications differ greatly from the traditional application paradigm.

There is one very distinct characteristic of operations and BU-Apps: transactions and journal entries.

Transactional, financial, and journal data may be queried by BU-Apps but these BU-apps should not generate this data.

Applications which input, post, approve, and roll-up transactions and journal entries must (and do) fall under a very specific processes for application development, test, and rollout governance. Such is the mettle of the traditional IT / applications department.

Most BU-Apps in most organizations have, followed this principal of non-journal posting applications and have, therefore remained focused on operational management, process improvement, and documentary artifacts.

New challenges

Isolated development of workbooks and tools to support ad hoc operations reporting and oversight has begun to restrict potential new and enhanced tool development.

Development of operations-centric tools as usual is no longer adequate. The business unit development paradigm must change in light of new expectations, new interfaces, new standards, big data, and the big crew change.

The challenges may be reduced to three driving forces; three forces are pushing the Business Unit away from VBA / ODBC based applications:

1) the need for support and enhancement of an increasing library of BU-Apps,

2) the deliberate corporate migration to enterprise level N-Tier and SOA architecture, and

3) the maturation and adoption of industry standards in interfaces, storage, retrieval, and development.

Supporting BU Apps:

Business Units are now faced with management of hundreds or thousands of replicated tools (workbooks and databases) using deprecated language constructs at a time when the new focus should be on Big Data repositories and the Big Crew Change.

The weight of the ad-hoc solutions, coupled with the increasing volume of data, and the evolving corporate computing architecture is beginning to exhibit pressures on the Business Units - pressures on maintenance, support, and enhancement.

Enterprise N-Tier and SOA Architecture:

IT departments are moving away from widely accessible databases using ODBC (open database connectivity) and SQL queries. Today, isolated n-tier data layers and SOA (Service Oriented Architecture) interfaces are the standard process for enterprise connectivity and data security.

These changes bring with them larger data sets, increasingly integrated enterprise applications, and tightening data audit and control.

Unfortunately, and unlike ODBC, SOA is not compatible with VBA (visual basic applications). (The mechanics of ODBC and SOA are beyond the scope of this article, but suffice it to say that SOA cannot be achieved within the VBA confines of Excel and Access and Microsoft has shown no desire to extend VBA to accommodate it.)

Maturation and Adoption of Standards :

The adoption of standards and processes within Digital Energy are receiving ever increasing wide-acceptance. These standards and processes include WITSML, PRODML, RESQML, and PPDM. Furthermore, the application development industry is moving rapidly toward Agile software methodology. (See sidebar definitions)

Operations-Centric Development

The operations-centric solutions created under the Business Unit Applications paradigm represent a 'seal-team' mentality - focused on a specific tactical objective and assembled by a specialized team intimately familiar with the unique expectations and environment found within the operational business unit.

But since operations-centric applications are not financial or accounting systems, they are not developed, managed or supported like financial and accounting systems.

Operations-centric application development must behave as a rapid reaction strike force - a 'seal-team' of developers. Any tool, technology, or organizational approach that increases quality, reliability, and deliverability must be embraced. By contrast, tools, technologies, and organizations that degrade the strike force must be pruned.

Fortunately, many similarities to the IT industry in general are applicable to the operations-centric business units - when applied properly toward rapid reaction operations. These resources may be broadly divided into industry standards, object oriented n-tier architecture, team development and deployment tools, and empowered development and management personnel.

The solution

Modern technology and development tools, technologies, and organizations permit faster development and increased reliability, but at the cost of a step-change in the skillset resident in the business units. The pain of moving from procedural based programming languages like VBA into object based / team based environments can be difficult but the payoff is tremendous.

Part 2 of this series will address a new development paradigm which will permit operations new insight to the deluge of data through industry standard technologies.

Part 3 will bring the process together using management and organizational concepts tracing roots back to the The Fifth Discipline by Peter Senge and manifest in modern development teams through Agile and Kanban development principals.

Industry Standards

Standards are all around us - electrical power, units of measure, modes of communication, and accepted and regulated weights and measures. Business unit application development is coming to grips with new standards, or widely accepted specifications. These standards take years to develop, refine, and deploy. But once deployed, they make the process simpler through increased interoperability, reduce development cost, improved reliability, and simplified deployment.

WITSML (Wellsite Information Transfer Standard Markup Language) defines the data exchange between drilling and completion operations by defining certain specific objects and protocols - both required and optional - while leaving granular expectations to be defined by the specific implementation. WITSML defines a communication protocol but is commonly used as a template for a data schema.

PRODML (Production Markup Language) and RESQML (Reservoir Markup Language) define similar data exchanges for production standards and reservoir standards, respectively.

PPDM (Professional Petroleum Data Model) defines a well/operations-centric data schema. This schema encompasses all phases of E&P data - drilling, completions, production, land, etc. PPDM defines a data schema, not a communication protocol.

SOA (Service Oriented Architecture) defines a diverse set of communication protocols including binary and XML. There are five common SOA genres, but typically follow a very similar development and deployment scenario. SOA should isolate data from business logic, and may isolate business logic from the user interface.

CREATE A FREE MEMBERSHIP

To attend our free events, receive our newsletter, and receive the free colour Digital Energy Journal.