What is a Data Warehouse?

In a data-driven world in which data has to be generated and stored in more and more systems, data warehouses play a major role in helping companies to store their information for the long term and prepare it for targeted analyses. In contrast to productive systems, such as online transaction processing, where transactions have to be processed in real-time, the data warehouse is used to store data from a wide variety of sources and make it available for storage-optimized analysis.

In this article, we look in detail at the characteristics of data warehouses, their corresponding architecture, as well as their advantages and disadvantages. We also shed light on the individual components and look at the question of what future data warehouses could look like.

What is a Data Warehouse?

A data warehouse is a central data repository in a company or organization that collects, transforms, and ultimately stores structured and historicized data from various sources. The information is transferred from various transaction systems or other relational databases to the data warehouse, where it is available to analysts and decision-makers.

In contrast to operational systems, data warehouses are not optimized for the fast processing of live transactions but are designed to process complex data queries efficiently, even when large amounts of data are involved. For companies, data warehouses are an essential component in their business intelligence and data architecture, fulfilling the following functions, among others:

• Past and current data can be analyzed to identify trends and react accordingly.
• Data from different systems, such as CRM, ERP, and websites, can be consolidated and viewed as a whole.
• Performance-based KPIs can be calculated and measured to make data-based decisions.

It is important to distinguish the data warehouse from the operational systems in which the actual business processes are handled. The data warehouse, on the other hand, is filled with data at regular intervals and no transactions take place in it. It is therefore a copy of the operational systems with the sole purpose of analysis and evaluation.

What Function does a Data Warehouse fulfill?

The data warehouse is used in many areas of the business environment. The database is used company-wide to make data-based decisions or to analyze processes. As the central data warehouse obtains information from many different systems, it is seen as a single point of truth. This is to ensure that all analyses in the company are based on the same data and that decisions are based on this information.

The data warehouse can be used across departments for the following tasks, among others:

• Cost and resource analysis
• Analysis of internal company processes (e.g. production, recruitment, etc.)
• Calculation and provision of company-wide key performance indicators
• Data source for analyses or data mining
• Standardization of company-wide data in a fixed scheme, e.g. conversion of all sales into a central currency.

Another important difference to operational systems is that the data warehouse covers a much longer time horizon, meaning that historical data is stored over a longer period. This means that long-term analyses and trends can be identified and also used for audits and other investigations.

The data warehouse serves as a hub for company-wide data analyses and is the central point of contact for creating evaluations. It enables efficient queries, even on large volumes of data, and also standardizes the data in a central location. This makes it a reliable basis for strategic corporate management and provides data to support decision-making.

What does the Architecture of a Data Warehouse look like?

The architecture of a data warehouse is the basis for merging large volumes of data from different data sources and efficiently querying them for analytical purposes. To this end, it is usually made up of several levels that enable a data flow.

Levels of a data warehouse

There are generally three main layers in a data warehouse:

Data Source (ETL process)

The data comes from various operational sources, such as ERP, CRM, IoT, or web applications. These are based on different structures and formats in which the data is stored, which is why the data has to go through the ETL process:

• Extraction: The raw data is taken from the source systems.
• Transformation: The data is prepared in several steps. This includes, for example, cleansing the data, i.e. replacing incorrect values and filling in missing values. They are also harmonized so that different structures between the sources are adapted and finally they are adapted to the target schema in which they are to be available in the data warehouse.
• Load: The prepared data is imported into the data warehouse and saved.

In addition to this classic process, a so-called ELT approach can also be used, in which the data is first loaded into the data warehouse and only transformed and prepared there. This is particularly common with data warehouses that are stored in the cloud.

Central Data Storage (Data Warehouse)

In this layer, the data is stored permanently and kept available for analysis. It is characterized in particular by the high scalability and performance required for analytical queries. In addition, the data must be prepared here using indices and partitioning so that it can be queried quickly and easily. In partitioning, for example, the data is stored according to common characteristics and divided into data blocks that are often queried together.

Finally, the underlying data model also has a significant influence on the query speed and the storage space required. The star or snowflake schema, in which the database tables are structured in such a way that no or as little data duplication as possible occurs, is one of the options.

Data Provision (Data Marts)

At this level, the data must be transferred to the stakeholders who work with it and create analyses. For example, data marts can be created in which all the data required for a certain analysis is available or in which only data that a certain group of participants can access is available. The various BI tools also access the data at this level.

Different Architecture Models

Depending on company requirements and data volumes, various architecture models can be considered for a data warehouse. These differ primarily in how many servers the data is distributed on and where it is operated. The following two points in particular must be considered:

• Centralized or Distributed: If the data is kept in a single storage location, it can be controlled centrally and also has a high level of consistency, as there can be no other storage location with a different data status. However, you run the risk of no longer being able to access the data if the server fails. In a distributed data warehouse, the information is stored on several servers, making it easier to scale the system by adding new instances. Fail-safety is also higher, as the failure of a server can be compensated for.
• On-Premises vs. Cloud: If companies operate the data warehouse on their servers, they have full control over their data and can guarantee security. However, this requires high investments for the acquisition and expansion of the hardware. With cloud systems, on the other hand, the provider operates the hardware so that the infrastructure costs are incurred every month and the system can be easily scaled. However, the data leaves the company and you have to trust that it is adequately protected by the cloud provider.

The architecture of the data warehouse is the central point that determines performance and scalability. This is a key decision for the company that should be well thought through, as it can only be changed at great expense at a later date. Depending on the data volumes, the required performance, and the IT resources, there is a choice between an on-premises solution with high-security standards and maximum control and a cloud solution, which offers better scaling potential and efficient cost management.

What Features does a Data Warehouse have?

When creating central data warehouses, certain characteristics can be used as a guide to help better define the structure and the necessary data for the warehouse.

Topic Orientation

A data warehouse contains information on a specific topic and not on individual business transactions. These topics can be sales, purchasing, or marketing, for example.

The warehouse aims to support decision-making with the help of business intelligence and specific KPIs. This interpretation is also supported by the fact that information that is not relevant to decision-making or used for analysis does not end up in this central database in the first place.

Integration

The warehouse integrates data from different systems and sources. A common schema must therefore be created for the information so that it is standardized and comparable. Otherwise, central analysis and key figure creation is not possible.

Time Reference

The data warehouse stores data from a specific period and is therefore related to the past. Furthermore, the data is usually aggregated, for example at the daily level, so that the amount of data remains limited. This means that the granularity may not be fine enough, as is the case with operational systems.

Operational systems, on the other hand, are time-based, as they output the information that is currently available. At the same time, the information can be viewed in great detail.

Non-Volatility

Another important feature of central data warehouses is the non-volatility of the data. In operational systems, information is usually only stored temporarily for a short period and as soon as new data is created, old data is overwritten. In a data warehouse, on the other hand, data is stored permanently, and old data is retained even if newer data is added.

These properties characterize data warehouses and distinguish them from other databases that can be created in companies.

What other Components are part of the Data Warehouse?

In addition to the data warehouse itself, other systems and components must be added to an architecture to enable central data storage. These include

• ETL Tool: To ensure retrieval and consolidation from various data sources, a tool is required that queries the sources at regular intervals, consolidates the data, i.e. brings it into a uniform schema, and finally stores it in the data warehouse.

• Metadata Storage: In addition to the actual data, there is also so-called metadata that needs to be stored. This includes, for example, the user rights that define which changes a person may make or which tables may be viewed. It also records the data and table structure, i.e. how the tables relate to each other.
• Additional Tools: The existence of the data in the data warehouse alone does not help. Other tools that use and process the information from it must also be integrated. For example, a suitable visualization tool, such as Microsoft Power BI, is often set up so that data evaluations can be created and aggregations carried out. This is what ensures that knowledge can be generated from the data.

What are the Advantages and Disadvantages of a Data Warehouse?

A data warehouse provides companies with a central platform for storing, managing, and analyzing large volumes of data. This results in many advantages during operation, which we will examine in more detail in this chapter. However, some disadvantages and challenges must also be taken into account during implementation.

By setting up a central data repository, more people can access information more easily and use it in the decision-making process. With operational systems, on the other hand, data may only be accessible to individual employees who are authorized to access it. In addition, the appropriate know-how for the individual tools is required to access the data. In a data warehouse, on the other hand, the data is stored in relational databases, making it accessible to a wider audience. This allows more rational decisions to be made that do not depend solely on the opinions of individuals.

It also offers the possibility of combining information from different sources and thus creating new perspectives. Previously, this was not possible due to the physical separation of the systems. It may be interesting for a company to merge data from the web tracking tool, such as Google Analytics, and sales data from the sales tool, such as Microsoft Navision, and evaluate them together. This allows the performance of the e-commerce store to be evaluated and the user journey to be viewed together with the actual purchase.

Centralized storage makes it easier to ensure data quality, as only one system and the data it contains need to be evaluated. Fragmented data sources distributed throughout the organization, on the other hand, are very difficult to keep track of. The same applies to access control for individuals. If the data is distributed throughout the company and may not even be stored in databases, it is almost impossible to track who has access to data that they may not need to see.

With the help of optimized data models, such as the star or snowflake schema, complex queries are significantly accelerated and data dashboards can be created for management decisions. The central data warehouse also enables the creation of meaningful data science and AI models that can automate processes and identify underlying structures in the data.

However, setting up a data warehouse also has some disadvantages and challenges that should be considered before creating it. The creation of a DWH requires very high initial investments in hardware, software, and licenses for databases or ETL software. Employees also need to be trained on the systems so that they can maintain and further develop them in the long term. Although these one-off investments can be avoided with cloud-based solutions such as Amazon Redshift, technical expertise is still required to operate these systems.

Another disadvantage of data warehouses is the lack of a real-time connection to the data sources, meaning that only history-based evaluations are usually possible. The data is usually updated in batch processes, for example daily or hourly, so there is always a certain time delay. Streaming data processing, which processes the data directly on arrival, is not provided for.

Another challenge when operating a data warehouse is the high security and data protection requirements, which must be adhered to and constantly re-evaluated. Business processes generate personal data that must be processed and stored by the GDPR.

A data warehouse offers many advantages, such as a standardized database, high query performance, and the ability to perform data analyses to identify trends and patterns. However, there are also various challenges due to the high initial investment, the integration effort, and compliance with security and data protection requirements.

What are the Differences between a Data Warehouse and a Data Lake?

The data warehouse can also be supplemented by a data lake, in which unstructured raw data can be temporarily stored at low cost so that it can be used at a later date. The two concepts differ primarily in the data they store and how the information is stored.

Features	Data Warehouse	Data Lake
Data	Relational data from productive systems or other databases.	All data types (structured, semi-structured, unstructured).
Data Schema	Can either be planned before the data warehouse is created or only during the analysis (schema-on-write or schema-on-read)	Exclusively at the time of analysis (schema-on-read)
Query	Very fast query results with local memory	– Decoupling of calculations and memory – Fast query results with inexpensive memory
Data Quality	Pre-processed data from various sources Standardization Single point of truth	Raw data Processed and unprocessed
Costs & Scalability	High storage costs due to structured data Scaling is usually only possible vertically (cost-intensive) Provider: Amazon Redshift, Google BigQuery, Snowflake	Cost-effective storage of large amounts of data, as raw data without conversion Scales horizontally, as additional data can be distributed to additional storage systems Provider: Amazon S3, Microsoft Azure Data Lake, Hadoop HDFS
Applications	Business intelligence and graphical processing of data	Artificial intelligence, analyses, business intelligence, big data

Finally, a data warehouse is suitable for storing structured data, such as sales or product information, while data lakes are used for storing unstructured data. Overall, the data lake offers more flexibility for big data analyses or AI models. Using a hybrid approach, both systems can be used to combine the benefits.

What does the Future of a Data Warehouse look like?

The classic data warehouse is constantly evolving to keep pace with the increasing demands on data volume, performance, and scalability. Several trends have emerged in recent years that could have an impact on the future of data warehouses.

More and more companies are moving their data warehouse to the cloud to take advantage of a flexible, cost-efficient, and scalable solution. The leading providers include:

• Amazon Redshift offers high scalability and can also be easily connected to other AWS services.
• Google BigQuery has a serverless architecture and shines with high speed and the integration of machine learning models.
• Snowflake enables a multi-cloud solution by separating the storage from the computing power and thus achieving optimum performance.
• Microsoft Azure Synapse enables a combination of data warehouse and big data analysis.

Overall, cloud data warehouses offer on-demand scaling combined with lower infrastructure costs and rapid deployment compared to locally operated on-premises solutions.

Traditional data warehouses often work with batch updates, so data is only updated daily or perhaps hourly. This means that real-time analysis is not possible as the data is not up to date. However, companies need real-time data processing to be able to react immediately to customer interactions and other events.

Technologies such as Apache Kafka, Google BigQuery Streaming or Snowflake Stream enable real-time analysis of data, which is immensely important for e-commerce or finance. Real-time data warehousing enables companies to make even faster, data-driven decisions.

Data warehouses are ideal for structured data, but companies are also increasingly generating unstructured and semi-structured data, for example through log files or social media data. With the help of data lakes, the architecture of data warehouses can be expanded so that data can also be stored in raw form and only processed accordingly when required. They also form the basis for complex machine learning models and are more cost-efficient when storing very large volumes of data.

Overall, the world of data warehouses is changing rapidly and the key trends include cloud technologies, real-time analysis, and the addition of big data storage in the form of data lakes.

This is what you should take with you

• The data warehouse is a central repository for structured data in a company that is separate from operational systems.
• Data from various sources is merged and then made available centrally for analyses and reports.
• The architecture of a data warehouse can differ in terms of whether the data is stored centrally or distributed and whether the servers are provided locally or in the cloud.
• The data in a data warehouse is topic-oriented, integrated, relates to a certain period, and is non-volatile.

Other Articles on the Topic of Data Warehouses

• Amazon Web Services provides a good summary here.