qlever

Data Warehouse

The more structured, real data the company has, the better it can attract new customers, develop new strategies and strengthen its market position.

Data Warehouse pulls together data from different sources into a single version of the truth to run powerful analytics for your decision-making advantage.

Data Warehouse (EDW – Enterprise Data Warehouse) is a central repository, specialized and consolidated source of real data that powers reports, dashboards and analytics tools to minimize the data input and output and deliver results quickly to all users simultaneously.

In EDW, data flows from transactional systems, relational databases, and other sources, and is stored in a detailed or aggregated form. Reporting, based on data from the repository, can be managerial, financial, regulatory or analytical.

Modern business analytics combined with a single data warehouse opens up new possibilities for business management.

When EDW can be useful?

Standard queries can be incredibly slow when transaction data is stored in multiple lines. It is much more efficient to use the data warehouse and create summary tables to query quickly

You need to accelerate queries

If you don't want to risk business disruption by working directly with the application database, you can send data automatically to EDW designed for complex requests and process it there.

You need to separate analytical data and transaction data

Some BI tools do not work well with every database. Such data can be first transferred to a data warehouse, where BI applications can access it

Available data source is not suitable for query

EDW structures information in such a way that all business users can use the data quickly and easily and at the same time with controlled (regulated) access. Data is stored in the most useful form for decision making - there are historical records, and there are aggregated values

You need to ensure the security, safety and availability of data

Analyzing is much easier when data from all sources is stored and managed in one central repository

You need to analyze data from different sources

How does EDW work?

EDW can contain multiple databases, within each database, data is organized into tables and columns, within each column, a description of the data can be defined. EDW organizes tables within multidimensional schemas, which are similar to folders. Query tools use the schema to determine which data tables to access and analyze.

What is the difference between a regular database and EDW?

1

Stored data types
Regular databases store data strictly for certain subsystems, for example, the HR database stores data on personnel, but not goods or transactions. EDW usually stores data from different departments - there will be data on goods, personnel, and transactions.
2

Data volumes
Regular database contains only the relevant data that is currently needed for the functioning of a particular system. In EDW, not so many copies of actual states are created, as historical data and aggregated values. For example, the state of stocks of different goods at the end of the shift for the last five years.
3

Place in work processes
Data usually immediately enters the working databases, and from there some records into EDW. EDW, in fact, reflects the state of other databases and processes in the company after changes are made to the working databases.

What is the architecture of EDW?

In the classical scheme, a Data Warehouse architecture is made up of layers:

Primary Data Collection Layer
Core Layer
Data Mart Layer
Service Layer

More about EDW structure

Primary data collection layer

ODS (Operational Data Store) – is a replica zone of a source system. It is the data zone to which copies of the source system are loaded, in the part that is needed to form the store, in order to quickly release the source and not affect it with your requests. This zone is usually filled once a day, after midnight. This happens more frequently: for example, when operational reports are generated from the data in this zone, such as a sales report for the last hour.

Core Layer

All disparate information received by EDW is reduced to the necessary structures and keys. It is such a key component of the warehouse that ensures the integrity and completeness of the data.

Data Mart Layer

This layer is a set of structured data, the necessary and relevant cleaned data received from other company systems. Usually, it is data on a specific topic or task in the company. For example, a customer data mart for a marketing department might contain detailed data on contracts, order and delivery histories, payments, calls, and shipping addresses. Data marts can also be used in enterprises as master data, for example, as directories.

Service Layer

This layer exists for managing the three previous levels. It provides data monitoring and prompt troubleshooting.

Want to make the most of the data?

Sign up for an audit and our experts will help you build a single version of the truth that will enable you to make effective decisions