Migrate data from OLTP to OLAP

Converts OLTP data to provide acceptable performance in the OLAP system, which requires a series of operations.

Merge data

First, we must be able to process all relevant information of a specific project (products, customers, employees) from multiple OLTP (on-line transaction processing, online transactions) the system is merged into an OLAP (Online Analytical Processing, Online Analytical Processing) system. The merging process must solve the encoding differences between different OLTP systems. For example, a system may assign an ID to each employee, while other systems do not. The merging process must be able to match common employee data in two systems, most of which can be achieved by comparing employee names and addresses. In addition, the merge process must be able to convert the data stored in different data types in each OLTP system into a single data type used in a certain OLAP system.

The systems that provide input data for OLAP systems are not limited to traditional OLTP systems in central locations. Important information can be stored in multiple legacy locations. In some cases, relatively small data sources, such as Microsoft Excel, can be stored on file sharing.

Clear Data

After the OLTP data is merged into the data warehouse, it provides an opportunity to clean up the data. You may find that different OLTP systems spell items in different ways, or the merge process may not overwrite previous unknown spelling errors. Other inconsistencies may also be found, for example, the addresses of the same store, employee, or customer are different. Before you can load data to a data warehouse for use by the OLAP system, you must identify these inconsistencies.

Aggregate Data

OLTP data records all transaction details. OLAP queries usually need to summarize data or data that has been aggregated in some way. If the database only contains a summary line showing the daily or hourly sales of each product, you can only query the total monthly sales of each product in the past year, this is much faster than scanning detailed records of each transaction in the past year.

The degree of data aggregation in a data warehouse depends on many design factors, such as the speed requirements of OLAP queries and the granularity required for analysis. For example, if you summarize the sales details to a daily summary instead of an hourly summary, OLAP queries run faster, however, this can be done only when you do not need to analyze the hourly sales volume.

To a multi-dimensional data set

The organization of relational OLTP data increases the difficulty of analysis and processing, and is extremely time-consuming. When OLTP data is migrated to a data warehouse, the structure must be converted to better support decision-making and analysis. The process of generating a Data Warehouse involves reorganizing OLTP data stored in a relational table into data stored in a multidimensional dataset.

Conversion phase and data warehouse Components

OLAP applicationsProgramThe process of making data available generally goes through the following three stages: extracting data from OLTP or legacy data sources to the intermediate storage area; converting data into a more suitable format for OLAP systems. This involves data cleaning and aggregation, and loading data to a data warehouse or data set. The process of extracting data from OLTP and legacy data sources and converting it to the warehouse server is called the ETL process, which is usually run regularly, such as once a week or once a month. Once the data is loaded into the data warehouse, an important function of the OLAP system is to facilitate the access and analysis of data in the data warehouse and data set.