Data Warehouse (DW): The Usable Data Store with Business Objectives

Why is my company interested in implementing a data warehouse?

The Data Warehousing Information Center answers:

To perform tasks associated with queries and reports on servers not used by transactional systems. The most cost-effective and agile way to obtain fast answers is to implement a data storage architecture that uses separate servers.

To utilize data models and accelerate information queries. There are data modeling methods that typically speed up queries and reports (for example, a star schema), which may not be appropriate for transaction processing because the modeling technique will slow down and complicate transaction processing.

To perform queries and reports without database knowledge.

To clean data from transactional processing systems.

To facilitate regular querying and reporting of data from multiple transactional systems.

Definition of DW (Data Warehouse) and what it should be

A data warehouse (data warehouse) is a collection of data oriented toward a specific scope (company, organization, etc.), integrated, non-volatile, and time-variant, which aids decision-making in the entity where it is used. Above all, it is a complete record of an organization, beyond transactional and operational information, stored in a database designed to favor efficient data analysis and dissemination (especially OLAP, online analytical processing). Data storage should not be used with current-use data. Data warehouses often contain large amounts of information that are sometimes subdivided into smaller logical units depending on the entity subsystem they originate from or for which they are needed.

In a data warehouse, the goal is to contain data that is necessary or useful for an organization; that is, it is used as a data repository to subsequently transform it into useful information for the user. A data warehouse must deliver the right information to the right people at the optimal time and in the appropriate format. The data warehouse responds to the needs of expert users, using Decision Support Systems (DSS), Executive Information Systems (EIS), or tools for making queries or reports. End users can easily perform queries on their data warehouses without touching or affecting the system operation. The following ideas are very important in the operation of a data warehouse:

• Integration of data from databases distributed across different units of the organization, which will often have different structures (heterogeneous sources). A global description and comprehensive analysis of the entire organization must be facilitated in the data warehouse.

• Separation of data used in daily operations versus data used in the data warehouse for dissemination purposes, decision support, analysis, and control operations. Both types of data should not coincide in the same database, as they serve very different objectives and could interfere with each other.

Data is routinely imported into the data warehouse from the entity’s various Enterprise Resource Planning (ERP) systems and other business-related software systems for subsequent transformation. It is common practice to normalize data before combining it in the data warehouse using extraction, transformation, and loading (ETL) tools. These tools read the primary data (often a business’s OLTP databases), perform the transformation process to the data warehouse (filtering, adaptation, format changes, etc.), and write to the warehouse. In this sense, the book “The Data Warehouse Toolkit” (Ralph Kimball) is an essential reference as it determines what it should be in any company:

• It must make the organization’s information easily accessible and simple.
• It must present the organization’s information consistently.
• It must be adaptable and resilient to change.
• It must be a secure bastion that protects our information.
• It must serve as a basis for improved decision-making.
• Users must adopt it voluntarily in their professional dynamics.

Data Warehouse Design

Metadata

One of the most important components of a data warehouse architecture is metadata. It is commonly defined as “data about data,” in the sense that it is data that describes the structure of the data to be stored and how it relates. Metadata documents, among other things, what tables exist in a database, what columns each table has, and what type of data can be stored. Data is of interest to the end user; metadata is of interest to the programs that must handle this data. However, the role metadata plays in a data warehouse environment is very different from the role it plays in operational environments. In the field of data warehouses, metadata plays a fundamental role: its function consists of collecting all the organization’s definitions, and the concept of data in the data warehouse must contain all information concerning:

• Tables

• Table columns

• Relationships between tables

• Data Hierarchies and Dimensions

• Entities and Relationships

ETL Functions (Extraction, Transformation, and Loading)

The processes of extraction, transformation, and loading (ETL) are important as they are the way data is saved in a data warehouse (or in any database). They involve the following operations:

• Extraction. The action of obtaining the desired information from data stored in external sources.

• Transformation. Any operation performed on the data so that it can be loaded into the data warehouse or migrated from it to another database.

• Loading. Consists of storing the data in the final database, for example, the standard target data warehouse.

Middleware

Middleware is a generic term used to refer to all types of connectivity software that offers services or operations that enable the functioning of distributed applications across heterogeneous platforms. These services function as a distributed software abstraction layer, situated between the application layers and the lower layers (operating system and network). Middleware can be seen as an API layer, which serves as a foundation for programmers to develop applications that work in different environments without worrying about the network and communication protocols in which they will run. In this way, it offers a better cost/performance ratio through the development of more complex applications in less time. The function of Middleware in the context of data warehouses is to ensure connectivity between all components of a data warehouse architecture.

Share on Facebook
Share on Google
Share on Twitter
Share on LinkedIn
Share on Pinterest
Share on Print
Share via Email