Extracting Snapshots versus Stabilized Data

A key feature of a data warehouse is that the data contained in it are in a nonvolatile, stable state.
Typically, transaction data are loaded into the warehouse only when the activity on them has been
completed. Potentially important relationships between entities may, however, be absent from data
that are captured in this stable state. For example, information about canceled sales orders will probably
not be reflected among the sales orders that have been shipped and paid for before they are placed in
the warehouse. One way to reflect these dynamics is to extract the operations data in slices of time.
These slices provide snapshots of business activity. For example, decision makers may want to observe
sales transactions approved, shipped, billed, and paid at various points in time along with snapshots of
inventory levels at each state. Such data may be useful in depicting trends in the average time taken to
approve credit or ship goods that might help explain lost sales.

DATABASE CONFIGURATION

Thousands of database tables make up ERP systems. Each table is linked to a set of business operations
that have been programmed into the ERP. By establishing switches in the system, the ERP installation
team, which includes key users and information technology (IT) professionals, selects certain database
tables and procedures. Determining how all the switches should be set for a specific configuration
requires a thorough understanding of the present business processes. Choosing table settings, on the
other hand, frequently entails judgments to reengineer the company's procedures to conform to current
best business practices. In other words, rather of adjusting the ERP to fit the firm, the company usually
adapts its processes to fit the ERP.

BOLT-ON SOFTWARE

ERP software alone cannot drive all processes of a company, as many companies have discovered. These
businesses rely on a range of add-on software from third-party vendors. Most of the major ERP systems
have formed alliances with third-party companies who provide specialized features. Choosing a bolt-on
that has been endorsed by the ERP provider is the least dangerous option. Some groups, on the other
hand, take a more autonomous approach. A good example is Domino's Pizza. Instead of modifying its
business processes, Domino’s Pizza modifies its ERP system.

Supply Chain Management

Another trend in the bolt-on software space is the growing convergence of ERP and bolt-on software
functions. A good example is supply chain management (SCM) software. The supply chain refers to all
the operations involved in getting items from the raw materials stage to the customer. Procurement,
production scheduling, order processing, inventory management, shipping, warehousing, customer
service, and forecasting demand for goods are all part of this process.

SCM systems are a type of software that assists with this process. SCM that works effectively organizes
and integrates these operations into a unified process. SCM connects all the partners in the chain,
including vendors, carriers, third-party logistics businesses, and information technology providers, in
addition to the core functional areas within the organization. Organizations can gain a competitive edge
by more efficiently and effectively linking the activities in their supply chain than their competitors.

Recognizing this necessity, ERP companies have made a concerted effort to include SCM capabilities into
their packages. ERP and SCM systems are now on a collision course. PeopleSoft has bought smaller SCM
suppliers to integrate its SCM software into future releases, while SAP and Oracle have just included an
SCM module. SCM software manufacturers, on the other hand, are increasing their functionality to
resemble more like an  ERP system.

Data Warehousing

Data warehousing is one of the most rapidly evolving IT concerns in today's commercial world. Data
warehousing functionality is being included into all main ERP systems, which is not surprising. A data
warehouse is a multidimensional or relational database that can take up hundreds of gigabytes or even
terabytes of disk space.

The process of data warehousing involves extracting, converting, and standardizing an organization’s
operational data from ERP and legacy systems and loading it into a central archive—the data
warehouse. Data is accessible via various query and analysis tools used for data mining once it has been
stored into the warehouse. Data mining is the process of selecting, exploring, and modeling massive
amounts of data in order to find linkages and global patterns buried amid the vast number of facts in
large databases. This involves the employment of advanced tools such as database queries and artificial
intelligence to model real-world phenomena from warehouse data.

Most organizations implement a data warehouse as part of a strategic IT initiative that involves an ERP
system. Implementing a successful data warehouse involves installing a process for gathering data on an
ongoing basis, organizing it into meaningful information, and delivering it for evaluation. The data
warehousing process has the following essential stages:

1. Modeling data for the data warehouse

2. Extracting data from operational databases
 3. Cleansing extracted data
4. Transforming data into the warehouse model
5. Loading the data into the data warehouse database

1. MODELING DATA FOR THE DATA WAREHOUSE

While a completely normalized database will provide the flexible architecture required to accommodate
multiple users in this dynamic operational environment, it will also add to the complexity by causing
performance inefficiencies.

The Warehouse Consists of Denormalized Data

Because of the vast size of a data warehouse, such inefficiency can be devastating. A three-way join
between tables in a large data warehouse may take an unacceptably long time to complete and may be
unnecessary. In the data warehouse model, the relationship among attributes does not change. Because
historical data are static in nature, nothing is gained by constructing normalized tables with dynamic
links.

Wherever possible, therefore, normalized tables pertaining to selected events may be consolidated into
denormalized tables. Figure 11-6 illustrates how sales order data are reduced to a single denormalized
Sales Order table for storage in a data warehouse system.

2. EXTRACTING DATA FROM OPERATIONAL DATABASES

Data extraction is the process of collecting data from operational databases, flat files, archives, and
external data sources. Operational databases typically need to be out of service when data extraction
occurs to avoid data inconstancies. Because of their large size and the need for a speedy transfer to
minimize the downtime, little or no conversion of data occurs at this point. A technique called changed
data capture can dramatically reduce the extraction time by capturing only newly modified data. The
extraction software compares the current operational database with an image of the data taken at the
last transfer of data to the warehouse. Only the data that have changed in the interim are captured.

The process of extracting data from operational databases, flat files, archives, and other external data
sources is known as data extraction. To avoid data inconsistencies, operational databases are often
taken down during data extraction. Little or no data conversion occurs at this point due to their vast size
and the necessity for a quick transmission to avoid downtime. By capturing just freshly modified data, a
technique known as changed data capture can drastically reduce extraction time. The extraction
software compares the current operating database to a data image taken during the last data transfer to
the warehouse.

Extracting Snapshots versus Stabilized Data

A key feature of a data warehouse is that the data contained in it are in a nonvolatile, stable state.
Typically, transaction data are loaded into the warehouse only when the activity on them has been
completed. Potentially important relationships between entities may, however, be absent from data
that are captured in this stable state. For example, information about canceled sales orders will probably
not be reflected among the sales orders that have been shipped and paid for before they are placed in
the warehouse. One way to reflect these dynamics is to extract the operations data in slices of time.
These slices provide snapshots of business activity. For example, decision makers may want to observe
sales transactions approved, shipped, billed, and paid at various points in time along with snapshots of
inventory levels at each state. Such data may be useful in depicting trends in the average time taken to
approve credit or ship goods that might help explain lost sales.

True or False

Data extraction is the process of collecting data from operational databases, flat files, archives, and
external data sources. - TRUE

Normalizing data in an operational database is necessary to reflect accurately the dynamic interactions
among entities. – TRUE

MC

The following are the essential stages of data warehousing process; EXCEPT:

a. Modeling data for the data warehouse

b. Extracting data from operational databases
c. Cleaning extracted data
 d. Transforming data into the warehouse model
e. Loading the data into the data warehouse database

Answer: c

The software used to fill the insufficiency of ERP system

a. Bolt - in software
b. Bolt - out software
c. Bolt - off software
d. Bolt - on software

Answer: d