Group (embedded ETL Tools) financial statement System Solutions

GroupCompany (embedded ETL tool) Financial Reporting SystemSolution Solutions

                                       

a,Project background:

a Group company is a company with more than a large group of subsidiaries, its subsidiaries involved in various industries, including: gold, copper, real estate, chemical fiber and so on. Due to the differences in the business of subsidiaries, the financial statements of subsidiaries have many differences. Therefore, each subsidiary needs to make the report template according to its own situation, and then deploy it to the group server for unified management.

The group is using SAP, where all subsidiaries generate large amounts of data and are kept together by this system. In addition, the subsidiaries also have their own systems, so each of them will produce some data.

For reasons of data security, the data of each subsidiary is uniformly concentrated in the database of the group headquarters. Report developers of subsidiaries who use FineReport to connect to the headquarters database can only see data within their own permissions.

two,Project construction Target:

Regularly import the data from the total data source into the database tables in each subsidiary, at this time the subsidiaries in the development of the report only need to connect their own database tables, so that the control of data rights, but also better the data of the subsidiaries in the various subsidiaries of the database table.

three,Project Construction Plan:1Kettle Introduction to the tools used

Kettle is a foreign open source ETL tool, written in pure Java, can be in Window, running on Linux,Unix , green without installation, efficient and stable data extraction .

ETL is the process of data extraction, transformation and loading. It is an important part of building a data warehouse. A data warehouse is a topic-oriented, integrated, stable, and time-varying collection of data to support the decision-making process in business management. Kettle 's Chinese name is called a kettle, meaning that you want to put all kinds of data into a pot and then flow out in a specified format.

Kettle This ETL toolset, which allows you to manage data from different databases by providing a graphical user environment to describe what you want to do, rather than what you want to do.

kettle , Transformation and job Span style= "font-family: ' The song Body '; >, transformation complete the base transformation for the data, job

As an important part of Pentaho, it is now increasingly used in domestic projects. FineReport reporting software enables the perfect integration of applications with kettle tools.

2. Advantages of Kettle Tools:

(1)Kettle Open source, efficient

(2)Kettle very powerful and easy to use design interface

(3) has a very strong and numerous user groups, widely used  

(4) use java for integrated development:

provides a basis for JAVA the footstep writing function, can be flexibly customized ETL process, making it possible to customize, batch, and so on, which is what a programmer needs to do, not just like using Word same Operation Kettle user interface.

3. How the Kettle tool Works:

(1) four modules of Kettle :

650) this.width=650; "src=" Http://s5.51cto.com/wyfs02/M02/79/31/wKioL1aLYHbDsKdaAAA-2ScGgxQ014.png "title=" Picture 1.png "alt=" Wkiol1alyhbdskdaaaa-2scggxq014.png "/>

Spoon  is a graphical user interface that allows you to run transformations or tasks where conversion is done with pan tool to run, task is to use kitchen to run. pan is a data transformation engine that can perform many functions, such as reading, manipulating, and writing data from different data sources. KITCHEN  is a can be run using the XML 

The following is a simple ETL process:

650) this.width=650; "src=" Http://s3.51cto.com/wyfs02/M02/79/31/wKioL1aLYIqTUYHhAAFdAAIqa3k971.png "title=" Picture 2.png "alt=" Wkiol1alyiqtuyhhaafdaaiqa3k971.png "/>

( 2 ) Principle Summary:

Kettle enables data conversion, primarily through the JDBC driver to establish connections, load data, transform data, and store data.

4. Using kettle to implement timed export data scheme

Realize the timing of export data, mainly to achieve two off-built points:

1. Timing Function

2. extracting data from the source database into the target database

The above two points can be easily achieved with the kettle tool.

1. Timer function:

Kettle is a mature professional ETL Tool, the realization of the timing function only need to set the job to the appropriate settings, as shown below, representing the daily performs a synchronization task on a full point.

650) this.width=650; "src=" Http://s1.51cto.com/wyfs02/M00/79/33/wKiom1aLYJXy-RjpAAEkA9ouB8k919.png "title=" Picture 3.png "alt=" Wkiom1alyjxy-rjpaaeka9oub8k919.png "/>

2. extracting data from the source database into the target database

Kettle takes the data out of the database simply by configuring it, as shown in.

650) this.width=650; "Src=" Http://s3.51cto.com/wyfs02/M02/79/33/wKiom1aLYLXjdQyxAAHRPHOuLWg232.png " title= "Picture 4.png" alt= "Wkiom1alylxjdqyxaahrphoulwg232.png"/>

where ${company} is the name of the company variable and is similar to the target database, simply configure it

The detailed usage configuration is shown in the following example.

5. Application Examples of timing data extraction for kettle tools:

( 1 ) Instance Features:

The database tables of each subsidiary are periodically queried and data is inserted and updated through data extraction and comparison.

( 2 ) Example Analysis:

A total of 3 files are required for the entire job , and the names and corresponding functions are shown in the following table:

650) this.width=650; "src=" Http://s1.51cto.com/wyfs02/M02/79/33/wKiom1aLYQiDu_e9AAAnT2gbmgw800.png "title=" Picture 1.png "alt=" Wkiom1alyqidu_e9aaant2gbmgw800.png "/>

( 1 ) detailed description of the file:

MAINTRANS.KTR:

Generate different database connection information and other variable information in MAINTRANS.KTR.

In the example, to enter data manually, you can also save the information in text or in the database, set the corresponding input. As shown below, table is the name of the tables in the subsidiary database, Company counterpart, and the following values are the connection information for the subsidiary database.

650) this.width=650; "src=" Http://s1.51cto.com/wyfs02/M02/79/31/wKioL1aLYVOgB7o7AACl1cezYzA148.png "title=" Picture 5.png "alt=" Wkiol1alyvogb7o7aacl1cezyza148.png "/>

there is a java script in Maintrans.ktr that takes the trans1.ktr file, sets the variable, and executes it.

There are several records on it that will perform several trans1.ktr.

TRANS1.KTR:

Perform the exported file specifically, the MySQL database in the example, the table name and the field are also specially set, please make the corresponding modification according to your own environment.

Table Input:

The table input is configured with the total database information that you want to export, and theSQL Example statement is:

SELECT * from kettletb WHERE company=' ${ Company} '

to select only the data for the corresponding subsidiary, where ${company} the name of the company to be passed in.

Specific configuration pages such as:

650) this.width=650; "src=" Http://s4.51cto.com/wyfs02/M00/79/31/wKioL1aLYXzzWj3kAAHSTxMMlTY497.png "title=" Picture 6.png "alt=" Wkiol1alyxzzwj3kaahstxmmlty497.png "/>

Table output:

The configuration here needs to be set according to the variable, the target table is ${table} to get the name of the table to be exported. The other configurations are as follows, where the values correspond to the data generated in MAINTRANS.KTR (where Password is ${password} ):

650) this.width=650; "src=" Http://s3.51cto.com/wyfs02/M00/79/31/wKioL1aLYZiDkN1dAACKrkV91fQ600.png "title=" Picture 7.png "alt=" Wkiol1alyzidkn1daackrkv91fq600.png "/>

Insert Update:

In the example, the ID and name fields in the database tables are queried, and the data used in the original database table is filtered out by comparing the IDs. In this way, only the latest changes in the data can be inserted, greatly improving the efficiency of data conversion.

650) this.width=650; "src=" Http://s3.51cto.com/wyfs02/M01/79/33/wKiom1aLYZHSS8EwAALZorr6iVM059.png "title=" Picture 8.png "alt=" Wkiom1alyzhss8ewaalzorr6ivm059.png "/>

Set The timing time of the MAIN.KJB and run. Set page references such as:

650) this.width=650; "src=" Http://s5.51cto.com/wyfs02/M01/79/31/wKioL1aLYc3SQAEOAAEq3aYGz_4416.png "title=" Picture 9.png "alt=" Wkiol1alyc3sqaeoaaeq3aygz_4416.png "/>

After-Sales service system:

650) this.width=650; "src=" Http://s4.51cto.com/wyfs02/M00/79/33/wKiom1aLYcHRL_l7AAL3knWjaGM566.png "title=" Picture 10.png "alt=" Wkiom1alychrl_l7aal3knwjagm566.png "/>

Group (embedded ETL Tools) financial statement System Solutions