Creation and configuration of the ODI studio topology

The creation and configuration of topology--a conceptual explanation

Topology Manager is primarily used to manage the following 5 categories of tasks and store the information in the master repository for shared use by all modules.

Physical Architecture:

Define various technologies and their data servers, physical architectures, and physical proxies. Data server blind can build multiple physical scenarios, and the essence of each physical scheme is the schema.

Logical Architecture:

Define the various technologies and their associated logical schemas, logical proxies. A logical scenario needs to be based on one or several physical scenarios, essentially a layer of abstraction, and an alias for a physical scheme.

Context:

The main purpose of the context is to implement many-to-many relationships for the logical scheme Multiphysics scenarios, such as the same logical schema that can correspond to different physical scenarios in different contexts.

language :

Language features that can be used by different technologies.

Agent:

The agent is a Java component component, which is also divided into physical agents and logical agents, usually it needs to run on the target library (also can run on the non-target library), the role is to dispatch the data migration job, usually, it simply retrieves the code from the execution repository and requests the database server, The operating system or script engine executes the code.

Data base:

Contains the master repository and its affiliated work repositories. Master Repository, save all the enterprise IT resources topology, save the project and data Model security information, version information for the ODI graphics module and so on. You typically create one. Master repository should be stored as independently as possible, separate instance, or a separate schema. Work Repository, save the project and data model for use with the ODI graphics module. You can create more than one. A work Repository can only connect to one master Repository. A schema can store only one work Repository, but Master Repository can be installed in the same schema.

Ii. Creating a physical architecture 2.1. Creating the source and target databases

Here, Sakila and SAKILA_DWH are used respectively as the source and target databases.

Reference Sakila--mysql Sample Database parsing

SAKILA_DWH Data Warehouse Example

Create user

CREATE USERSakila identified by 'Sakila'; GRANT  All Privileges  onSakila.*  toSakila;CREATE USERSAKILA_DWH identified by 'SAKILA_DWH'; GRANT  All Privileges  onSakila_dwh.*  toSAKILA_DWH;

2.2. Create a data server

STEP1: Click Topology, Physical architecture, select mysql-> Right-Select the new database server:

STEP2: Specifies the definition of the database server:

Name: Sakila

Database server: localhost

Connection (user and password for the database, the user's permissions should be greater than or equal to the permissions of the schema in which the data resides)

Users: Sakila

Password: Sakila

STEP3: Specifies the JDBC of the database server:

JDBC Driver: com.mysql.jdbc.Driver

JDBC Url:jdbc:mysql://localhost:3306/sakila

Save, click Test Connection Test successfully.

The same operation establishes the data server Sakila_dwh

Final effect

2.3. Create a physical scenario

STEP1: On the new data server Sakila, right-click to select New Physical scenario:

STEP2: Create the physical schema of the source database, select the Sakila that represents the source data in MySQL,

Directory: Sakila (indicates the source data schema to which the physical scheme should be connected, i.e. where the source data is)

Working directory: Sakila (This specifies the location of temporary tables that need to be created during the data migration process)

To configure the context later, click Save All:

Similarly create a physical schema for the target database, choose the sakila_dwh you just created, click Save All

The final effect is as follows

2.4. Create a logical scenario

STEP1: Click Topology, Logical Architecture, select Mysql-> Right-select the new logical scheme:

STEP2: Create a logical Scheme Sakila, where you can use the context global that ODI has built, or use the context that you created yourself. The physical scheme corresponds to the physical schema of the source data that you just created Sakila.sakila, click Save All. When you click on the physical scheme Sakila.sakila, you will see that the context is automatically configured. This actually reflects the direct relationship between the physical scheme-context-logic scheme, which is designed to achieve physical and logical separation. The implementation of a logical scheme can be related to different physical scenarios according to different contexts such as development, build, and the same physical scheme can support the flexible configuration needs of multiple logical schemes.

STEP3: Similarly create the logical scheme SAKILA_DWH, the physical solution corresponds to the original source data physics scheme SAKILA_DWH.SAKILA_DWH, click Save All, the final effect is as follows

Iii. New Context

There is already a context global in the system, and we can either take this context or create a new one.

Fill in the name and code, here develop represents the development environment, the agent does not fill in, save the effect as follows

Iv. Creating an Agent

Agents also need to create physical and logical proxies, and if they are developed locally, there is no need to create proxies for the time being.

4.1 Creating a physical proxy

Refer to configuring the Domain for the Java EE Agent

4.2 Creating a logical Proxy

Context Select the develop that you just created, the physical proxy scheme chooses the oraclediagent you just created

At this point, the topology configuration is complete, the following can create a new project, model, and then design an interface for ETL.

Creation and configuration of the ODI studio topology