Content uploaded by Yinshan Tang
Author content
All content in this area was uploaded by Yinshan Tang on Dec 21, 2015
Content may be subject to copyright.
Journal of Computing and Information Technology. [Accepted, recommended minor corrections made and
submitted].
1
Design of a Unified Data with Business Rules
Storage Model for OLTP and OLAP Systems
Stephen Opoku-Anokye and Yinshan Tang
Informatics Research Centre, Henley Business School,
University of Reading, Whiteknights, Reading, RG6 6UD, UK.
E-mail: s.opoku-anokye@pgr.reading.ac.uk; y.tang@henley.reading.ac.uk
This paper reviews the literature concerning the practice of using Online Analytical
Processing (OLAP) systems to recall information stored by Online Transactional Processing
(OLTP) systems. Such a review provides a basis for discussion on the need for the
information that are recalled through OLAP systems to maintain the contexts of transactions
with the data captured by the respective OLTP system. The paper observes an industry trend
involving the use of OLTP systems to process information into data, which are then stored
in databases without the business rules that were used to process information and data stored
in OLTP databases without associated business rules. This includes the necessitation of a
practice, whereby, sets of business rules are used to extract, cleanse, transform and load data
from disparate OLTP systems into OLAP databases to support the requirements for complex
reporting and analytics. These sets of business rules are usually not the same as business
rules used to capture data in particular OLTP systems. The paper argues that, differences
between the business rules used to interpret these same data sets, risk gaps in semantics
between information captured by OLTP systems and information recalled through OLAP
systems. Literature concerning the modeling of business transaction information as facts
with context as part of the modelling of information systems were reviewed to identify
design trends that are contributing to the design quality of OLTP and OLAP systems. The
paper then argues that; the quality of OLTP and OLAP systems design has a critical
dependency on the capture of facts with associated context, encoding facts with contexts
into data with business rules, storage and sourcing of data with business rules, decoding data
with business rules into the facts with the context and recall of facts with associated
contexts. The paper proposes UBIRQ, a design model to aid the co-design of data with
business rules storage for OLTP and OLAP purposes. The proposed design model provides
the opportunity for the implementation and use of multi-purpose databases, and business
rules stores for OLTP and OLAP systems. Such implementations would enable the use of
OLTP systems to record and store data with executions of business rules, which will allow
for the use of OLTP and OLAP systems to query data with business rules used to capture the
data. Thereby ensuring information recalled via OLAP systems preserves the contexts of
transactions as per the data captured by the respective OLTP system.
Keywords: OLTP Systems Design • OLAP Systems Design • Facts with Contexts Modelling
• Data with Business Rules Modelling•
Journal of Computing and Information Technology. [Accepted, recommended minor corrections made and
submitted].
2
1 Introduction
Requirements for Online Analytical Processing (OLAP) are usually not considered during
the design of systems to satisfy Online Transactional Processing (OLTP) requirements.
Traditionally, OLTP requirements gathering exercises are focused mainly on transaction
processing. The need for analytical processing becomes apparent, only when OLTP systems
are in use and begin to generate large volumes and complex varieties of data at high
frequencies. It is at this point that OLAP systems are designed specifically to extract,
transform and load data from OLTP systems into OLAP databases to support analytical
requirements. The lack of consideration for the transaction processing requirements together
with analytical processing requirements presents many issues, challenges and problems
when OLAP systems are used to interpret data stored in OLTP databases. The facts and
contexts of information captured during transaction processing are usually stored and
interpreted by computers in the form data and business rules (i.e. rule flow and logic).
Traditionally, the design of systems to support OLTP and OLAP requirements usually
followed a two tier architecture design pattern (i.e. an application tier that embeds the
business rules used for transactions or analytical processing and a data tier used for storage
or sourcing of data). Sometimes, the components that make up these two tiers could be
separated into several layers. An example is described by Fowler (2003) as three principal
layers (i.e. presentation, domain and data source) and Microsoft (2009) as three software
design layers (i.e. presentation, business and data). Another example is the five software
designed layers described by Sun Microsystems (2005) as the client, presentation, business,
services and data. Irrespective of how many layers the design of an OLTP system or OLAP
system may be divided into, business rules are often stored as an integral part of the core of
OLTP and OLAP systems. Data, however, are most often stored in databases that are
separate to the core of the OLTP system or OLAP system. These are contributing to the
necessity of practice whereby data are extracted from different OLTP systems into databases
dedicated for use by OLAP systems, in order to support the organizational need for complex
reporting and analytics. The data in these databases exclude original business rules used by
particular OLTP systems to process data. The use of these databases by OLAP systems for
analytical processing is, therefore, achieved with only transactional data. Extraction of data
with business rules used to process the data, however, becomes very difficult, and if at all
possible, their use to maintain the context of information captured during transaction
processing may not be possible. Business rules are usually recreated within OLAP systems,
resulting in a possible difference between the semantics of information captured by OLTP
systems and information recalled via OLAP systems.
Design of OLTP or OLAP databases focuses primarily on efficient and effective ways of
implementing systems for either transactional or analytical use. The primary focus of OLTP
systems is on capturing, processing and storing of data as and when business transactions
occurs. Database systems that support OLTP requirements are often designed to improve on
the efficiency of processing transactions, collecting information about processed
transactions and storing capture data to a persistent storage. Limitations in computer
Journal of Computing and Information Technology. [Accepted, recommended minor corrections made and
submitted].
3
technology constrain the design of persistent data storage for OLTP systems. For example,
duplications of data that are stored in the persistent storage are reduced in order to increase
the efficiency of processing business transaction. This is to reduce the number of times that
an OLTP system will have to save data entities or objects into a persistent store (i.e. in the
case of an OLTP system the ideal number of times is once). An array of information systems
modelling methods and techniques are adopted during OLTP systems design to help achieve
these objectives. The use of most of these modelling methods and techniques to design
OLTP systems do render the use of OLAP systems to access data stored in persistent storage
that were designed for use by OLTP systems inefficient. Especially the requirements to
support organizational needs for complex reporting, querying and analytics. In some cases,
OLTP databases may even be unsuitable for use by OLAP systems, given the structure of
data stored by the particular OLTP System. Database systems that support OLAP
requirements are often designed to improve on the efficiency of processing, analytical data,
retrieving information about processed transactions, consolidating and aggregating data
stored in multiple data repositories. OLAP systems have a primary focus to extract,
transform and load data after business transactions have occurred for the purposes of
reporting, querying and analytics. During the design of OLAP systems, this process is
popularly known as extract, transform and load (ETL). The ETL process involves the
extraction of data from several OLTP persistent data stores, cleansing, transforming and
loading data into a consolidated persistent data store that is mainly for OLAP use. The
extraction, cleansing and transformation of data involves the use of business rules that are
elicited for the sole purpose of ETL. Such business rules usually have no direct relationship
with the business rules used to capture data into respective OLTP systems.
The current common industry practice involves the use of OLTP systems to process
information into data, which are then stored in databases without the business rules that were
used to process information. The use of these data without business rules does exclude
critical details about the facts of information captured. These exclusions include information
about what, how, when, where and why a particular piece of information was processed
during business operations and decision making cycles. Business operational contexts of
these captured facts exist within the data stored and business rules that are used to process
the information. Thus, data stored in OLTP databases without associated business rules, may
not have the same context as original information captured. In order to maintain business
operational contexts of information captured, OLTP systems need to capture facts with
contexts, encode these facts and contexts into data using business rules and then store the
data with associated executions of business rules. In this paper, we review the modeling of
business transaction information as facts with context. We propose an approach to the co-
design of data with business rules storage for OLTP and OLAP purposes. The proposed
design model provides the opportunity for the implementation and use of multi-purpose
databases, and business rules stores for OLTP and OLAP systems. The integration of data
storage with business rule executions would provide support for the use of OLAP systems to
query data with the business rules that were used to capture such data. These ensure the facts
of information recalled through OLAP systems maintain the business operational context of
information captured by particular OLTP systems.
Journal of Computing and Information Technology. [Accepted, recommended minor corrections made and
submitted].
4
2 Modeling of Facts with Business Operational Context
The information required to support business operations and decision making include signs
used to represent such information (syntax), meaning of these signs (semantics) and
purposeful use of these signs (pragmatics). Signs are usually in the form of text, numbers,
symbols, “words, image, sounds, gestures, and objects” (Chandler 2007). These are the raw
data that represent the syntax of the information processed by OLTP systems during
business transaction processing and also information processed by OLAP systems during
complex reporting, querying and analytics. Davenport and Pruzak (2000), define data as “a
set of discrete, objective facts about events”, whiles Newell et al. (2009), define data as “a
record of signs and observations collected from different sources”. Information, however, is
described by Newell et al. (2009), as data that are “presented in a particular way in relation
to a particular context of action.” Contexts of actions in an OLTP system are, as per the
processing of business transactions. Semantics of data captured by OLTP systems during
processing of business transaction are embedded in relevant facts and associated contexts.
Professor Henderson quoted in (Parsons 1949) refers to a Fact as "empirically verifiable
statement about phenomena". In the case of OLTP systems, transactions are phenomena that
occur during business operations and decision making cycles. The ‘contexts of actions’ for
these phenomena include the life cycle and value chain of products produced as a result of
business operations; organizations involved in producing these products and their
organizational structure, processes, management and strategy; and the operating
environments and relationships with all stakeholders internal or external. Dey (2001),
defines context as "any information that can be used to characterize the situation of an
entity." Contexts of actions captured by OLTP systems during transaction processing,
encodes details about the circumstances and conditions within which transactions occur; and
thus influences the meaning and purposeful use of data stored in OLTP databases (see
Figure 1).
Journal of Computing and Information Technology. [Accepted, recommended minor corrections made and
submitted].
5
Capture
Encode
Store Source
Decode
Recall
Recording QueryingInformation
Pragmatics
Syntax
Semantics
Facts
Contexts
Facts with Context
Data with Business Rules Data with Business Rules
Facts with Context
Figure 1. Information Recording and Querying with OLTP and OLAP Systems
Information system modeling is a system analysis and design technique used to elicit,
analyze and specify business and solution requirements, and also to design and document
OLTP and OLAP systems. Halpin and Bloesch (1999) describe information systems
modeling from two perspectives, i.e. (1) data perspective and (2) process and behavioral
perspective. OLTP and OLAP systems represent implementations of computer-supported
information systems within and across organizations. Their designs thus include modeling
from data point of view and the point of view of process and behavior. Modelling from the
data perspective focuses designers’ attention to what information is to be encoded (Soames
1989). Information is encoded into data when an OLTP system is used to process business
transactions. OLAP systems, however, are used to decode data stored in databases optimized
for OLTP use into information for the purposes of business reporting, querying and analysis.
From a data modelling perspective, the transaction can be seen as what Connolly and Begg
(2005) described as “an action or series of actions, carried out by a single user or application
program, which accesses or change the content of the database.” OLTP systems can be
described as application programs that carry out actions or support end-users to carry out a
series of actions, which accesses or change the contents of a database or databases. OLAP
systems, on the other hand, can be described as application programs that are used to access
and extract data from disparate operational databases into an analytical database or databases
and then change the content of the analytical database for the purpose of reporting, querying
and analytics. As explained by Davenport and Pruzak (2000) “data becomes information
when its creator add meaning” and also data is translated into information “by adding value
in various ways” including contextualization, categorization, calculation, correction and
condensation. OLTP systems use business rules to create and store data during processing of
Journal of Computing and Information Technology. [Accepted, recommended minor corrections made and
submitted].
6
transactions. These business rules are thus required by OLAP systems in order to add
meaning to data created and stored by OLTP systems. The modeling of process and
behavioral, focus designers’ attention on executions of business rules used to process
transactions and how these business rules are associated with the data created and stored by
processing of such transactions. A model for execution of business rules during transaction
processing provide abstractions that represent the realities of situations and conditions
within which transactions are processed. This includes modelling of business rules that are
used to process, record or query information. Business rules are ‘assertions that constrain
patterns’ of process executions during business operations and decisions making cycles.
They include policies and standards that govern business operations (Morabito et al. 1999,
Bajec and Krisper 2005). So, the business rules model can be used to represent abstractions,
structuring, formalization and transformations (Oberweis 1996) of business processes,
practices and behaviors.
Beynon-Davies (2002) describes the process of modeling an information system as
construction of sign-systems for reasons of communication, representation and abstraction
of conditions in the real world. A model in this sense is an approximation of realities in
business operations and decision making environments. Use of modeling, therefore, enable
system analysts and designers to explore and improve their understanding of organizations,
associated systems, business processes, practices and the wider business environments (Stair
and Reynolds 2011). The industry committee commonly known as ANSI/SPARC published
in 1978 documents that described components of a three-level architecture for information
systems, which include external, conceptual and internal models. The external model is a
model for application programs that are used to access and change contents of a database.
External models are used to communicate, represent and to provide an abstraction of how
OLTP and OLAP system users view and use information. External modelling of OLTP and
OLAP systems can thus be referred to as the modelling of application programs. This
includes designing for information processing (i.e. encoding information into data and
decoding data into information), integration and presentation. Internal models for OLTP and
OLAP systems provide abstract views of where and how information is stored or sourced. It
is a model for the contents and structure of a database or databases. Although not often
considered as part of the internal model, the contents and structure of business rules stores
are part of the internal. Internal modelling of OLTP or OLAP systems includes logical and
physical data modelling and also logical and physical modeling of business rules. The
physical distribution of internal models can be different from the understanding of their
logical distributions (Moss and Atre 2003). Conceptual models provide for the mapping of
external models to internal models, as such; they are used as representations of system
specifications and to communicate system designs to developers and information systems
users alike. This includes detailed specifications of requirements for OLTP and OLAP
systems to record or query information. Conceptual models are independent of any
technology used to implement external and internal models.
We argue the quality of OLTP and OLAP systems design has a critical dependency on the
capture of facts with associated context, encoding facts with contexts into data with business
Journal of Computing and Information Technology. [Accepted, recommended minor corrections made and
submitted].
7
rules, storage and sourcing of data with business rules, decoding data with business rules
into the facts with the context and recall of facts with associated contexts. These should be
modeled at the conceptual level and are best designed with particular attention to facts
captured during transaction processing, contexts within which these facts are captured,
business rules used for processing transactions and associated data. Therefore, it is important
for systems designers consider not just the data, but rather data with the execution of
business rules during external, conceptual and internal modeling of OLTP or OLAP
systems. Co-design of data models and business rules execution models ensure the linkage
of facts with the contexts of transactions to storage of data and execution of business rules.
Parsons (1949), clarified a fact is not in itself a phenomenon, "but a proposition about one or
more phenomena". It is thus possible for a particular fact to be associated with one or more
business operational context. Likewise, data stored in OLTP databases may be associated
with one or many executions of business rules. The execution of business rules is another
category of records that are fundamental to the use of OLTP systems to support the
processing of business transactions. Storage of business rule executions includes information
about when they were triggered and how they affect the data stored (Bajec and Krisper
2005). Storage of business rules and associated executions with data will provide
capabilities for the use of OLAP systems to study interactions among business rules (Rai
and Anantaram 2004), and also the interactions between business rules and associated data.
Such study can be used in business intelligence, which aims to provide information to
support evidence based decision making.
3 Unified Information Recording and Querying Model
We propose the design of OLTP and OLAP systems be based on a unified business
information recording and querying (UBIRQ) and the principles of computer-supported
information recording (i.e. capture, encode and store) and querying (i.e. source, decode and
recall) as described in Figure 1. The design approach (see Figure 2) is composed of three
models, i.e. Online Transactional Processing (OLTP), Online Analytical Processing (OLAP)
and a unified data with business rules store (UDBS). Design of OLTP models considers
capture, display and storage of data with business rules to support processing of business
transactions. OLAP design models consider, reporting, querying and analysis of data with
business rules that were used to process the data. The UDBS model can be used to help co-
design data with business rules storage to support OLTP and OLAP requirements. UDBS
allows data that are linked to business rules used to produce such data to be designed for
storage in databases that are separate from the core of OLTP or OLAP systems. Just as data,
business rules are also designed to be stored in business rule stores that are separate from the
core of the respective OLTP or OLAP systems. This does not only make possible the
extraction of data with business rules used to create the data, but also make possible their
use to preserve the context of information captured during processing of business
transactions. The use of OLAP systems to process data captured by OLTP systems is,
therefore, achieved with, not just the data captured but in the context within which
Journal of Computing and Information Technology. [Accepted, recommended minor corrections made and
submitted].
8
transactions were processed. Linkage of data with business rules during systems design
focuses the attention of designers on the use of business rules by OLTP systems to capture
and encode facts with contexts into data for storage, as well as, use of business rules to
source, decode and recall data into the facts with contexts that are presented to end-users.
OLTP Model OLAP Model
Capture and Recall Recall
Encode and Decode
Facts FactsBusiness Operational Contexts Business Operational Contexts
Meta-Data Meta-Business- Rules
Capture Facts
with Contexts Recall Facts
with Context
Decode
Meta-Data Meta-Business-Rules
Capture Facts
with Contexts
UDBS Model
Record Data with
Business Rules Execution Query Data with associated
Business Rules Query Data with associated
Business Rules
Data Business Rules Key Business RulesBusiness Rules Key
Data Stores Business Rules Stores
Figure 2. Unified Data with Business Rules Storage Model for OLTP and OLAP Systems
Computer-supported OLTP and OLAP systems are not capable of understanding facts with
context in the same way as humans do. Thus, the need to encode the facts with contexts into
data with business rules for a computer to process. Also, the need to decode the data with
business rules into the facts with contexts for the people in the organization to aid their
understanding. OLTP systems are modeled to encode facts with transactional context into
data using business rules stored in a unified business rules repository for use by both OLTP
and OLAP systems. Data with associated executions of any of these business rules are then
recorded into a database. This enables OLTP and OLAP systems to source data with
business rules from databases and business rules stores and then to decode them into facts
Journal of Computing and Information Technology. [Accepted, recommended minor corrections made and
submitted].
9
with contexts. Encoding facts with contexts into data with business rules and decoding data
with business rules into the facts with contexts are achieved by the use of meta-data and
meta-business-rules. Metadata contains information about data stored within respective
databases; whiles meta-business-rules contain information about business rules that are
stored within respective business rule stores. Data stored in databases are linked to the
associated business rules in particular business rules stores by having a business rule
executions keys for each data record link to business rules keys in respective business rules
store. Two types of meta-data and meta-business-rules are required during OLTP systems
design that is based on the UBIRQ model. These are (1) meta-data and meta-business-rules
that describe how information about transactions processed are captured, encoded and stored
in data with business rules stores; and (2) meta-data and meta-business-rules that describe
how data with business rules are sourced from their respective storage for decoding and
display as facts with contexts. These meta-business-rules could be designed using a locking
and unlocking mechanism with associated business rules key that is stored together with the
data in the unified data store. During the design of OLAP systems, consolidated meta-data
and meta-business-rules that describe how data with business rules are sourced from the
unified storage for decoding and presentation as facts with contexts. This ensures the
information contained in reports, dashboards, scorecards and query results from OLAP
systems could be interpreted within the relevant business operational context. The co-design
and unified approach to the storage of data with business rules enable business operational
context of information captured by OLTP systems to be preserved when such information
are being recalled either through OLTP or OLAP systems (see Figure 2).
Advances in technologies in recent years, has meant, varieties of technologies are now
available for supporting implementations of OLTP or OLAP systems design using the
UBIRQ approach. These include technologies and techniques that allow:
Use of in-memory database architectures and faster disk-drive architectures to offer fast
read-write to and from database management systems (DBMS).
Implementation of business rules stores using business rules management systems
(BRMS) together with storage and retrieval of business rule executions.
OLTP and OLAP systems to use meta-data and meta-business-rules for recording and
querying information to and from DBMS and BRMS.
The above mentioned technologies and techniques make it possible for the implementation
of OLTP and OLAP systems that make use of databases and business rules stores designed
based on a physical implementation of UDBS model. These do also make possible physical
implementations of multiple and disparate OLTP and OLAP systems based on the logical
designs of OLTP or OLAP model.
Journal of Computing and Information Technology. [Accepted, recommended minor corrections made and
submitted].
10
4 Discussions
Today, organizations have information that is typically stored in databases optimized for
transactional (i.e. OLTP systems) or analytical (OLAP systems) use. OLTP systems are
designed to process large volumes of business transactions at high frequencies. As such
OLTP databases are designed to capture and store large amounts of data as and when
generated during processing of business transaction. Design efforts for OLTP systems focus
on how to provide support for a more efficient and productive operations (Duan and Xu
2012). The main issues considered during OLTP design are processing, display and storage
of data. Capture of business rules and logic are not considered as important. Databases for
OLTP use are designed primarily to support processing of concurrent transactions usually in
their hundreds, thousands or millions depending on the size of the organization or enterprise.
Data is usually extracted from OLTP systems into OLAP systems for transformation into
information, purposefully to support the business need for complex reporting, querying and
analytics. Often, the data extracted and transformed are loaded into databases dedicated for
the purpose of generating management reports and analytical use. These databases include
data warehouses, data marts, data cubes and many other data storage systems. It can be
argued from an OLAP design perspective that, information stored about transactions by
OLTP systems are usually formed of two parts, i.e. business rules (including rule flow and
logic used to process the transaction) and data created pre, during and post processing of
transactions. Data stored within databases designed for OLTP systems can also be argued to
be the footprints of business transactions were processed the OLTP system. Such data
represent what ‘action or series of actions’ have been carried out. Making data created by
OLTP systems available and accessible for use by OLAP systems, provide support for a
process that was described by (Golfarelli et al. 2004) as “turning data into information and
then into knowledge”. Such data or information or knowledge embeds that facts with
contexts that allow decision makers across organizations answer questions relating to what
has happened in relation to the processing of business transactions. Business rules, which
include logic and rule flows that are used to process transactions, can be said to represent
abstract views of processing steps for business transactions. These business rules are
representations of decisions that are or should be taken by OLTP systems on whether or not
an ‘action or series of actions’ are carried out during the processing of particular business
transactions. Therefore, making business rules used by OLTP systems to process business
transactions available, accessible and in formats that are usable in OLAP systems, supports
the use of OLAP systems to decode data into information using business rules that were
used by the respective OLTP system to encode the data. Such information would not only
allow decision makers’ answer questions relating to what has happened in relation to
business transactions; but also allow them to answer questions relating to how a particular
business transactions are processed. In some cases, these may also make available
information concerning why particular business transactions are processed.
Data modelling exercises are undertaken during the design of OLTP systems to develop
abstract models that allow physical implementation of OLTP systems to process, collect,
Journal of Computing and Information Technology. [Accepted, recommended minor corrections made and
submitted].
11
store and retrieve data for the purposes of supporting business transactions processing.
During the design of OLAP systems, data modelling exercises are undertaken to develop
abstract models that allow physical implementation of OLAP systems to source data and
present facts with context. Such presentation of facts with contexts enable stakeholders
explore, analyze; query and synthesis of available data into useful information to support
operational, tactical and strategic decision making. It is important for designers who design
data models for OLTP systems to understand organizational requirements for OLAP, to
allow them make design decisions that include: (1) access strategies for data that would be
captured and stored by the OLTP system, (2) information about data stored (i.e. meta-data)
and why and how the data were processed (i.e. meta-business-rules) and (3) presentation of
information to end-users for use to satisfy analytical requirements. Designers need to
question repeatedly whether or not data that are captured and stored by OLTP system and
associated business rules used for processing the data would support OLAP requirements,
and also how up to date these data with associated business rules need to be in relations to
processing of transactions, in order to satisfy requirements for OLAP and complex
reporting. Repeat questions of applicability of captured information for OLAP purposes
during the design of OLTP systems provide answers that reshape design strategies for data
access, meta-data, meta-business-rules, data retrieval and facts with contexts presentation.
For example, the design of unified data with business rules storage model, for use by both
OLTP and OLAP systems; do offer a better solution to the business requirements for
complex reporting and OLAP that dictate for the availability of real time information.
Design of OLAP systems, therefore, need to include identification of OLTP data and
business rules sources that contain information required to satisfy analytical need. Designers
of data models for OLAP systems need to give due consideration to the appropriateness of
OLTP data and business rules sources and reuse of OLTP data models for presenting facts
with contexts to business users.
Models and technologies that are currently used to implement OLTP systems does make a
typical OLTP database and business rules store very complex and often unsuitable for
complex reporting and analytics. Entity Relationship (ER) modelling of data is a technique
traditionally used by designers’ of data models for OLTP systems to help analyze OLTP
data requirements and designing their logical and physical data structures. Dimensional data
model design is a technique predominately used for logical design of data structures for
OLAP systems. Kimball (1997) described it as “logical design technique that seeks to
present the data in a standard, intuitive framework that allows for high-performance access.”
The focus for dimensional data model design is placed on the provision of improved
capabilities for complex reporting and also for business users to analyze and visualize data.
The two main primary assumptions that are usually made during dimensional data model
design include (1) the notion that captured transaction data are records of business
operational activities and results of information processed by respective OLTP systems, and
(2) the notion that OLTP data models are organized abstractions of business operational
data. These emphasize the use of data models a natural and better method for understanding,
communicating and managing business data. It does, however, exclude business rules used
for processing transactions and analytical information. Models for these business rules are
Journal of Computing and Information Technology. [Accepted, recommended minor corrections made and
submitted].
12
an important part of the information processed and as such are required to add context to the
data captured and stored during transaction processing. Dimensional data model design
technique is commonly used in industry to design data structures for data marts, data
warehouses and historical operational data stores, to make them dimensionally aware data
sources. Another current use of the dimensional data model design technique is a meta-data
model design technique known as ‘OLAP over Relational’, which is used to make OLTP
data sources that are not dimensionally aware data sources appear to an OLAP system, as if
they were dimensionally aware data sources.
Business rules govern organizational processes and activities and are used to process by
OLTP systems, databases and OLAP systems to process data. Despite their importance,
business rules are not captured, stored and treated with similar importance to that of data. It
is common practice for business rules to be hard coded into the fabrics of by OLTP systems,
OLAP systems and their respective databases. This makes it very difficult to capture and
store business rules used in OLTP systems for OLAP purposes. Even if OLAP developers
go through the tedious process of extracting these business rules (i.e. Logic and rule flow)
from their respective OLTPs systems, their use to satisfy the organizational need for
complex reporting and analytics will be very limited. The capture of data into databases by
OLTP systems without associated execution of business rules governing the processing of
such data exclude the context through which the data were generated. Data without context
in which it was generated allows for the data to be misinterpreted or interpreted in the wrong
context. Consequently, the information obtained through any OLAP system that is based on
data without the business rules used will be context deficient in terms of the circumstances
in which the information was captured. Without the original context by which the data was
processed, captured and stored, attempts to transform the data into meaning information
using OLAP may result to various degrees of semantic gaps between information as existed
and processed by the respective OLTP system and the information obtained through
reporting, exploration and analysis using OLAP systems. The origins of gaps in semantics
between OLTP and OLAP can be summarized into three main categories, which are:
(1) The operative terms of reference and measurement units used by different functions of
an organization may be varied.
(2) Multiple stakeholder groups use of OLAP systems to transform captured data and stored
within OLTP systems into information to support decision making at different levels of
management and information granularity.
(3) The use of OLAP systems to interpret data captured and stored within databases
designed for OLTP systems without associated business rules used to generate the data.
Semantics gaps based on categories (1) and (2) could be resolved within the business
domain by implementing common standards for operational terms of reference and
measurement units. Semantics gaps based on category (3) could be resolved when the
following two conditions are met: (1) OLTP system captures, stores and treat business rules
with similar importance to that of data, and (2) information obtained through OLAP systems
are sourced from data in association with the business rules used to process such data. The
UBIRQ model proposed in this paper, provide a solution to category (3) based semantic
gaps if the model is used as a blueprint for designing of data with business rules storage for
Journal of Computing and Information Technology. [Accepted, recommended minor corrections made and
submitted].
13
OLTP and OLAP purposes. It provides logical unification of business rules stored, their
association with and use by OLTP and OLAP systems, and having each executed business
rule key linked to each data record stored within a logically unified business data store.
UBIRQ enables data stored within OLTP systems to be associated the business rules to
process the data and thereby allowing for the use of OLAP systems to interpret data captured
and stored within OLTP databases with associated business rules used to generate the data.
5 Conclusion
Business operational information that is captured and stored by OLTP systems represents
footprints of business transactions and activities. The practice of extracting data from
disparate OLTP systems for transformation and loading into OLAP databases are meant to
satisfy business requirements to provide information for the purposes of supporting
organizational decision making. OLAP that is based on data without business rules used to
capture data enable decision makers to answer questions relating to what has happened
during business operations and decision making cycle. Business rules, which include rule
flow and logic, contain details of decisions taken by OLTP systems and people during
transaction processing, including situations and conditions under which data were or are
being generated. Thus, OLAP that is based on data with business rules used to capture data
enable decision makers to answer questions relating to the circumstances and conditions
within which transactions were or are being processed. Storage of business rules together
with data as part of records kept by OLTP systems will ensure facts with context recalled
using OLAP systems are based on data with business rules used to capture the data. UBIRQ
model enables co-design of OLTP and OLAP databases as well as business rule stores.
Thereby insure subsequent implementation allows for a unified approach to recording and
querying of information relating to business transactions. Ensuring facts recalled from
OLAP systems will have the same contexts of actions as facts captured and stored by OLTP
systems. This helps to preserve business operational context of information capture during
transaction processing, irrespective of whether it is being queried by OLTP or OLAP
systems.
References
Bajec, M. & Krisper, M., 2005. A methodology and tool support for managing business
rules in organisations. Information Systems, 30 (6), 423-443.
Beynon-Davies, P., 2002. Information systems: An introduction to informatics in
organisations: Palgrave.
Chandler, D., 2007. Semiotics: The basics Abingdon, UK: Routledge.
Connolly, T.M. & Begg, C.E., 2005. Database systems: A practical approach to design,
implementation, and management, 4th Edition. London, UK: Addison-Wesley.
Journal of Computing and Information Technology. [Accepted, recommended minor corrections made and
submitted].
14
Davenport, T.H. & Pruzak, L., 2000. Working knowledge: How organizations manage what
they know: Harvard Business Press.
Dey, A.K., 2001. Understanding and using context. Personal and ubiquitous computing, 5
(1), 4-7.
Duan, L. & Xu, L., 2012. Business intelligence for enterprise systems: A survey. Industrial
Informatics, IEEE Transactions on, PP (99), 1-1.
Fowler, M., 2003. Patterns of enterprise application architecture: Addison-Wesley
Professional.
Golfarelli, M., Rizzi, S. & Cella, I., 2004. Beyond data warehousing: What's next in business
intelligence? Proceedings of the 7th ACM international workshop on Data
warehousing and OLAP. Washington, DC, USA: ACM, 1-6.
Halpin, T. & Bloesch, A., 1999. Data modeling in uml and orm: A comparison. Journal of
Database Management (JDM), 10 (4), 4-13.
Kimball, R., 1997. A dimensional modeling manifesto. DBMS, 10 (9), 58-70.
Microsoft, 2009. Microsoft application architecture guide, 2nd Edition.
Morabito, J., Sack, I. & Bhate, A., 1999. Organization modeling: Innovative architectures
for the 21st century: Prentice Hall PTR.
Moss, L.T. & Atre, S., 2003. Business intelligence roadmap: The complete project lifecycle
for decision-support applications: Addison-Wesley Professional.
Newell, S., Robertson, M., Scarbrough, H. & Swan, J., 2009. Managing knowledge work
and innovation: Palgrave Macmillan.
Oberweis, A., 1996. An integrated approach for the specification of processes and related
complex structured objects in business applications. Decision support systems, 17
(1), 31-53.
Parsons, T., 1949. The structure of social action: Free Press, New York.
Rai, V.K. & Anantaram, C., 2004. Structuring business rules interactions. Electronic
Commerce Research and Applications, 3 (1), 54-73.
Soames, S., 1989. Semantics and semantic competence. Philosophical Perspectives, 3, 575-
596.
Stair, R.M. & Reynolds, G.W., 2011. Principles of information systems: Cengage Learning.
Sun Microsystems, I., 2005. Sun java enterprise system 2005q1 technical overview
