Data Management (DMS) provides the logical data warehouse feature that allows you to query and synchronize data across databases based on the unified SQL syntax. The databases may run on different engines, reside in different regions, and belong to different database instances and storage systems such as online transaction processing (OLTP) and online analytical processing (OLAP) systems. This feature can effectively help operations personnel, data warehouse developers, and business intelligence (BI) engineers improve the efficiency of data preparation and data analytics.
Prerequisites
- An Alibaba Cloud account or a RAM user to which the AliyunDTSFullAccess policy is attached is used to log on to the DMS console.
- A database instance that is managed in Security Collaboration mode is available.
Background information
- Insufficient resources and high costs
- To build and manage a data warehouse is costly. In addition, small- and medium-sized enterprises do not have sufficient resources and technical means to build data warehouses for business analytics and decision-making. Therefore, most enterprises have no choice but to give up the data-driven solution.
- The business of large enterprises is complex. This makes the data warehouse development difficult. In addition, large enterprises do not have enough professional developers to help efficiently meet the growing needs for operational analysis. In general, a proper ratio of data warehouse personnel to operations personnel is 1:10, and a proper ratio of data warehouse personnel to data users is 1:30.
- Flexible requirements for business analytics
- Data warehouses standardize business data based on data cleansing, data warehouse modeling, and data centralization. This helps improve the efficiency of data retrieval, statistics collection, and data analytics. However, in scenarios where business iteration is fast, standard data warehouses are not suitable and cannot meet the flexible requirements for business analytics.
- In recent years, growing attention is being paid to the data values in the places where business activities take place. When and where the relevant data plays its role after a business activity takes place can reflect the digitalization degree of an enterprise.
- Data warehouses cannot effectively meet the operational analysis requirements due to various issues such as issues related to data analytics chains and real-time performance.
If you need to persist or periodically update the analytics results, you can materialize the result datasets. Materialized data can facilitate data analytics operations such as data visualization. All data engines support materialization. During the materialization process, DMS automatically processes data, transmits data, and performs scheduling operations. You need to only write SQL statements or perform a few clicks on the GUI in the whole process. This is easy and efficient.
For more information about the SQL syntax for views, see SQL syntax for logical data warehouses.
Terms
Views constitute the core of a logical data warehouse. Views include logical views and materialized views.
Term | Description |
---|---|
Logical data warehouse | A logical data warehouse is a virtual data warehouse that is built on your data sources.
A logical data warehouse consists of logical views and materialized views, and provides
GUIs for you to manage and query views.
You can perform virtualization operations on data in a logical data warehouse to define logical views. You can also materialize virtualization operations to define materialized views. |
Logical view | Similar to a regular view of a database, a logical view defines the data processing
and calculation processes. A logical view does not store data but stores only definitions.
When you query a logical view, the logical view extracts data from the data source, processes the data in the DMS compute engine based on operations such as data joining, aggregation, filtering, and calculation, and then displays the data. This way, cross-database queries and development, such as wide table development, are implemented. |
Materialized view | A materialized view is to store a query result to another table. Logical views can be materialized to generate materialized views. A materialized view can create an entity, such as a table or a file, for the corresponding logical view in a database, automatically generate a synchronization task, and then use the serverless compute and transmission engines of DMS to process and transmit data in a stream-batch unification manner. |
OLAP | OLAP supports complex analytics operations and can present query results in a clear way. |
OLTP | OLTP is mainly used for data operations and supports frequent DML operations. |
Scenarios
This section describes the scenarios of logical data warehouses for different user roles.
- Database and data warehouse developers:
Cross-database query and development, logical archiving of business data, data preparation and supply, lightweight data warehouses, data warehouse construction simulation, agile data warehouse construction, and agile data warehousing
- Operations analysis and BI personnel:
Data preparation and analytics, cross-database query, query and analytics acceleration, periodical reports, and intelligent wide tables
Workflow

Billing rules
When a materialized view is created, a Data Transmission Service (DTS) migration task is also created.- A DTS migration task that is created by default cannot be used to migrate incremental data and does not generate costs.
- If you create a DTS task that continuously runs to migrate incremental data, the task
generates costs. For more information about the pricing of DTS data migration instances,
see Pricing.
Note The created data migration instance is of the small type and uses the pay-as-you-go billing method.
Limits
- Logical views support CREATE and DROP statements, but not ALTER, UPDATE, and DELETE statements.
- Materialized views support only real-time integration.
- DMS supports only DTS-based materialized views. The materialized views support the FILTER statement and specific functions, but not the JOIN, UNION, and AGGREGATE statements.