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

In the process of digitalization, enterprises are increasingly aware of the importance of data as a production factor, and want to use data to drive business growth and decision-making and to further enhance enterprise competitiveness. However, the implementation and efficiency of a data-driven solution is often affected due to the following factors:
  • 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.
To resolve the preceding issues, DMS provides the logical data warehouse feature. You can integrate various disparate data sources in a logical sense and import data from these data sources to a logical data warehouse without the need to migrate the original data. Then, you can use the cross-database query engine that is built in DMS to create logical views for data integration and analytics. The cross-database query engine supports SQL statements. The logical data warehouse feature prevents tedious extract, transform, load (ETL) operations and makes data analytics more agile. In addition, this feature allows users who are familiar with business data to directly manage and analyze data as needed. As a result, the costs of communication between business personnel and developers are reduced.

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

Flowchart

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.

Procedure

  1. Log on to the DMS console V5.0.
  2. Create a logical data warehouse.
    1. In the left-side navigation pane, click the Plus icon icon in the Logical Data Warehouses section.
    2. In the Create Logical Data Warehouse dialog box, set the Logical Data Warehouse Name and Description parameters and click OK.
  3. Go to the SQLConsole tab of the logical data warehouse.
    • In the left-side navigation pane, double-click the logical data warehouse in the Logical Data Warehouses section.
      1. In the top navigation bar, choose SQL Console > Logical Data Warehouses.
      2. In the Select Logical Data Warehouse dialog box, select the logical data warehouse from the drop-down list and click Confirm.
  4. Import databases and tables.
    1. On the Logical Views tab in the left-side pane, move the pointer over the Plus icon icon and select Import Database or Table.
      Note
      • You can import only databases that are managed in Security Collaboration mode.
      • You can select multiple databases.
    2. Specify the import scope for each selected database and click Import.
      • Database: imports all tables in the database.
      • Specific Tables: imports specific tables in the database. If you select this option, you must specify the tables that you want to import.
      Note
      • After you import the databases and tables, you can view the imported databases and tables on the Logical Views tab in the left-side pane.
      • To import more tables from a database to the logical data warehouse, right-click the database on the Logical Views tab and select Import Table. On the Import Table tab, select the tables that you want to import and click Import.
    Logical Views - Import Database or Table
  5. Create a logical view.
    1. On the Logical Views tab in the left-side pane, move the pointer over the Plus icon icon and select Create Logical View.
    2. On the Create Logical View tab, set the View Name and Owner parameters.
    3. In the code editor of the View definition section, enter the SQL statement that is used to define the logical view. For more information about the SQL syntax for views, see SQL syntax for logical data warehouses.
      You can click Try Run to verify the entered SQL statement.
    4. Click Submit.
      Note After the logical view is created, you can view the created logical view in the Custom Views list on the Logical Views tab.
  6. Optional: Query the logical view.
    Go to the SQLConsole tab of the logical view that you created. In the code editor of the SQLConsole tab, enter a SELECT statement and prefix the view name in the statement with lv..

    For example, to query the logical view ProductCustomers, enter SELECT * FROM lv.ProductCustomers in the code editor and click Execute.

    Query the logical view
    Note Alternatively, you can double-click the created logical view on the Logical View tab in the left-side pane. Then, DMS automatically generates the SQL statement for querying the logical view in the code editor of the SQLConsole tab.
  7. Create a materialized view.
    1. Go to the Materialize View tab by using one of the following methods:
      • Method 1: On the Logical Views tab, right-click the logical view for which you want to create a materialized view and select Materialize View. The Materialize View tab appears.
      • Method 2: In the left-side pane, click the Materialize View tab. On the Materialize View tab, click the Plus icon icon to the right of the search box to go to the Create Materialized View tab.
    2. Configure the materialized view.
      Note The SQL syntax of a materialized view can contain only the FILTER statement and specific functions. The JOIN, UNION, and AGGREGATE statements are not supported.
      Parameter Description
      View Name The name of the materialized view.
      Note The name can contain letters, digits, and non-special characters.
      Owner The owner of the materialized view.
      Materialized Table Name The name of the materialized table.
      Target Database The destination database for storing the materialized view. You can enter a keyword in the search box to search for a database and then select the required database from the search results.
      Note After you create the materialized view, the created materialized view is stored in the destination database.
      Logical view reference The logical view for which the materialized view is created. Select a logical view that you have created.

      Then, DMS automatically uses the query statement of the logical view as the query statement of the materialized view.

      Materialization Modes To materialization mode. You can set this parameter only to Real Time.
      Note In real-time materialization mode, DMS synchronizes the data that is queried by the query statement of the logical view to the materialized table in real time.
    3. Click Submit.
      After the materialized view is created, you can view the created materialized view in the Custom Views list on the Materialize View tab. You can also view the corresponding materialized table in the destination database on the Logical Views tab.
  8. Optional: Query the materialized view.
    Go to the SQLConsole tab of the logical view for which the materialized view is created. In the code editor of the SQLConsole tab, enter a SELECT statement and prefix the view name in the statement with mv..

    For example, to query the materialized view mv_ProductCustomers, enter SELECT * FROM mv.mv_ProductCustomers in the code editor and click Execute.