What if organizations processed all their data into decision support? What would happen if they used specialized software that served for the presentation of the information and the analysis of the same? We will give some Data Warehouse examples to answer these questions . Examples of Data Warehouse
Data Warehouse Examples
In the first place, it is important to differentiate between two terms that, due to their abbreviation, can easily confuse us, and from the beginning the intention is for the user to know what to expect and to know some basic concepts that they are going to face. Here we will show infinite examples that serve so that the individual has the tools to distinguish these elements.
Definition Examples of Data Warehouse
Given the difference between the two terms, we will proceed to define them formally, since it is a process that extracts, transforms, consolidates and integrates the data of an organization, both internal and external, in order to make them accessible and useful in decision-making .
In the same way, the Data warehouse can also be defined as a base with electronic file system information, which stores the necessary data for information analysis and decision-making. Its difference is that it is business-oriented, integrated, time-varying and non-volatile.
Basically, Data Warehousing (DWH) is a process and Data Warehouse (DW) is a database.
features Examples of Data Warehouse
There are several aspects that characterize the Data warehouse that provide the necessary tools for its optimal use, thus complying with programmed guidelines that generate the tools for its use in the best possible way. We will detail the characteristics of a Data Warehouse:
Only relevant data is entered into the Data Warehouse for analysis and decision making. That is, data that has no analytical value, such as room addresses, postal codes, email addresses, among others, are not considered. But they are of variable interest such as type of client, geographical location, age, etc.
High-level entities are managed, such as clients, products, items, areas, and others. The data is stored in a multidimensional way, that is, in fact and dimension tables.
All data from heterogeneous sources is consolidated to guarantee its quality and cleanliness. The main data sources are:
According to the type of user.
- Operational: It produces a large amount of data on a daily basis, but by itself it is of little relevance to the required analysis. For example, product sales.
- Medium: Generates data with implication in the short and medium term, based on operational data. A good example of this concept is inventory generation.
- Managerial: Uses data resulting from the integration and transformation process. In turn, it generates new information. It basically refers to the user of the Data Warehouse.
According to the area or department of the organization
- Areas: Each one has well-defined responsibilities. They produce their own data that is shared with the other areas.
- Subdivisions: They are usually geographic. They provide location data, which must be incorporated together with the others.
According to the source
- Internal: They generate their own data, coming from the daily activities of the company.
- External: They complement internal data, for example censuses and statistics.
Variant in time
It allows access to different versions of the same situation, since current data is stored together with historical data, in the data warehouse examples.
It guarantees the stability of the information, since once the data enters, it does not change. That is, the data is manipulated only when it is entered and when it is consulted.
Handles data in volume, a consequence of the accumulation of historical, current and aggregated data, from various sources.
It places the entire volume of data in a single centralized database. Structure the data in a multidimensional way.
Due to its characteristics and qualities, the Data Warehouse presents the following benefits:
- It reduces the minimum time required to collect all the relevant data on a specific topic.
- Provides analysis tools.
- Many reports and analyzes are user-defined.
- It allows you to directly access, analyze and monitor the organization’s indicators.
- It helps to identify the factors that affect the operation of the company.
- It allows to advance and determine the future behavior of the institution.
- Users can query data quickly and easily.
In short, the Data Warehouse helps the organization answer essential questions for decision making. This achieves competitive benefits that optimize their position in the market in which they operate. Some of these questions are:
- What is the profile of the clients?
- How is their behavior?
- What is the profitability of the business?
- What is the risk to the organization?
- What services and products do you use and how can you increase them?
Area of application
A Data Warehouse can be adapted to any organization, regardless of its size and complexity. This is as a consequence of the agenda of any institution, company or organization when making pertinent decisions regarding the data it produces.
It requires a large investment on the part of the organization. The benefits of its implementation are not seen in the short term, but in the medium and long term.
The manipulation of data threatens the manipulation of sensitive data.
Aspects to take into account
As mentioned at the beginning, there are several aspects that must be taken into account for the application of these elements for the use of a server. Among them we can mention the following:
A data Warehouse carries construction, operating and support costs. The construction cost implies the costs of human resources, time and technology, while that of operation and maintenance, considers the costs of evolution, growth and those produced by changes in the origin of the data.
Impact on people
The application of a Data Warehouse always generates expectations in users, who will necessarily have to acquire new skills. The success of this type of data depends on active use and feedback from users.
Impact on business and decision-making processes
With the application of a Data Warehouse, certain deficiencies in business processes can be revealed, but at the same time confidence in the decisions taken based on the results obtained by it increases.
The general architecture of an example data Warehouse is shown in the figure above. As can be seen, this system involves a series of interactions between its components. In this regard and as a summary, its operation can be described as follows:
- The data is taken from various sources, such as web services, files and other databases, both internal and external.
- Once the data is extracted, it is integrated, transformed and cleaned, to later be loaded into the Data Warehouse.
- In order to generate tactical and strategic information, reports and analysis are obtained from the loading of the data.
- Finally, users can consult and explore the reports and analyzes generated.
We are now going to describe some of the elements that can be evaluated in the Data Warehouse that should be of our consideration.
Data Warehouse Sources
Generally, they are the result of the daily activity of the company, in which case they are called internal sources. When data is taken from, for example, web servers, these are considered external sources. They are different from each other, because they depend on their origin, format, function, etc.
Extraction, transformation and loading
Known as ETL, it is the process that includes all the tasks that are carried out from when the data is obtained until it is loaded into the Data Warehouse. These are: extraction, manipulation, control, integration, data cleaning, loading and updating.
It includes techniques focused on obtaining, from various sources, only the relevant data and keeping it in internal storage. This type of storage allows data to be manipulated without intervening or altering the sources or the Data Warehouse with more data, creating an extraction layer between reading and loading, storing and managing the metadata generated in the process and facilitating integration.
The extraction is based on the needs of the users and the requirements defined for the solution.
These are the techniques in charge of making the different formats compatible, as well as filtering and classifying the data, and relating sources.
This function is responsible for applying all the appropriate commands in relation to the data, in order to promote them in a strong and reasonable way that is compatible and consistent with the Data Warehouse. In addition, it is responsible for the cleanliness and quality of the data.
Load Examples of Data Warehouse
Regarding the techniques of the initial loading of the data and the periodic updating of the Data Warehouse.
- The initial load refers to the first load of data that the Data Warehouse receives. Generally, it is very time consuming due to the large number of records belonging to long periods of time.
- Periodic updating refers to the insertion of small volumes of data. Your goal is to add only the data that was generated from the last update to the data warehouse samples. It depends on the needs and requirements of the user.
In short, through the data loading process, the maintenance of the Data Warehouse is guaranteed.
As a summary, it can be said that the ETL process is carried out as follows:
- The data, once extracted from the relevant sources, is deposited in internal storage.
- While the data is kept in internal storage, it is integrated and transformed.
- When the data is cleaned, after the previous step, it is passed to the Data Warehouse.
Reports Examples of Data Warehouse
The reports are graphical tools that allow the user to obtain detailed reports on the information of your company. The way to interact with these reports is quite simple for the user, since they are easy-to-follow instructions. Basically, you must select options from a menu, referring to the conditions and specifications of the subject presented.
OLAP Examples of Data Warehouse
It is the most powerful component of the Data Warehouse, since it contains the specialized multidimensional query engine of the system.
It allows the analysis of the organization from different historical scenarios. It projects its behavior and evolution from a multidimensional vision, that is, by combining different perspectives, topics of interest or dimensions. This allows trends to be deduced by discovering relationships between perspectives that would be difficult to find at first glance.
Data Mining Examples of Data Warehouse
It is primarily a statistical tool, through which predictions can be made. It is about inferring behaviors, without there being pre-established rules. It generates reports in the form of tables and graphs, among others, which promote decision-making in a proactive way. It works on the basis of information that has already been fully processed.
Difference between OLAP and Data Mining
Once the main aspects of OLAP and the Data Minig have been considered, a basic difference between them can be established.
- Using OLAP, the current situation of the company is interpreted, giving quick answers that facilitate decision-making.
- The Data Minig predicts situations, based on the study of hidden knowledge that provoke certain types of behaviors.
Consequently, both systems deal with solving different types of analytic situations.
Data Minig and its relationship with Data Warehouse Examples of Data Warehouse
A Data Minig system is a support technology for the end user, whose objective is to extract useful information from the information contained in the companies’ database. In other words, the origin of the information used by the Data Minig algorithms is usually historical data contained in a Data Warehouse.
There must be an integration between Data Minig techniques and the processes involved in the Data Warehouse. In other words, in order to carry out the business analysis, there must be agreement between the Data Minig, the Data Warehouse and the OLAP server.
Every time Data Warehouse provides new results, the company can reapply Data Minig to optimize decision making.
In short, the Data Minig and the Data Warehouse are fully compatible tools. The Data Warehouse provides memory, and the Data Minig intelligence.
Traditional databases vs Data Warehouse
The analysis of the aspects exposed so far, leads us to understand that a Data Warehouse differs from the databases that support the daily transactions of organizations. Here the basic differences
- In traditional databases the information is organized so that it can be easily retrieved and updated. A Data Warehouse is organized and oriented towards the end user, who can only make inquiries.
- Transactional databases take care of the day-to-day processing of the data. The Data Warehouse works with historical data, that is, corresponding to long periods of time.
- Traditional databases are accessed several times during a working day. In a Data Warehouse, the readings and queries are minimal, as it is accessed sporadically.
- The volume of data that a Data Warehouse manages is much greater than that managed in traditional databases.
- The structure of the transactional bases is stable. The structure of a Data Warehouse varies according to its own evolution and use.
Next, we will establish some examples of Data Warehouse.
Data Warehouse Examples
A nationwide company, dedicated to the sale of cleaning supplies at the wholesale and retail level, also considered medium-sized due to its sales volume, has the main goal of maximizing its profits. Similarly, in order to get more customers, you want to expand to a new market level and, later, expand your product line. One of its main policies is to continuously improve to get a better position with respect to its competitors of the data warehouse samples.
The application of a Data Warehouse offers the following benefits to the organization.
- It allows users to have an overview of the business.
- Transform operational data into analytical information, focused on decision making.
- Generate dynamic reports that facilitate your analysis.
- It facilitates the formation of strategies for the fulfillment of the goals of the organization.
- It benefits the stability of the company structure.
Another example of an everyday data warehouse refers to the management of an educational institution, which has deficiencies in terms of communication with its students. Similarly, it lacks a unified information center that has all their information. The objective of the institution is to accompany students during their career and after graduation, to offer new proposals that enhance the performance of the organization and the development of students.
With the application of a Data Warehouse we seek to respond to the needs of the university. In principle, eliminating the duplication of information and the presence of erroneous details about the students, as well as all the information that, in general, is considered of poor quality and that is not relevant. Additionally, all the information is integrated, forming a unified record of students that serves as the basis for the proper development of the institution’s project.
Finally, marketing activities are promoted, giving the university greater benefit and helping its growth through the correct management of information. Examples of Data Warehouse