Run-Dry Set calculation report optimizing the application structure of data library storage

Source: Internet
Author: User
Tags db2

in a report project, it is possible that the report source data comes from a different database. This is because the same report may fetch data from multiple business systems. For example: Employee information is removed from the HR system and sales data is removed from the sales system. Another possibility is that the database load of the same application system is too large to be divided into multiple databases. For example, the sales system data is divided into the current library and the History Library.

report tools may need to connect to the same type of database, such as Oracle or DB2, ordifferent types of databases.

in the report application, the solution of data storage is as follows:1.Building a specialized data warehouse;2. Using cross-Library access technology.

the construction and management of specialized data warehouses is more complex. If the amount of data is very inefficient, and the constant ETL to the application system to synchronize data. And the Data warehouse uses the traditional database technology, if the load is large, also facing the problem of sub-Library. The Data Warehouse approach is structured as follows:

650) this.width=650; "src=" http://s3.51cto.com/wyfs02/M01/5A/2F/wKioL1T5QxfzAKr7AACdno6GnSk762.jpg "style=" float: none; "title=" report5_structure_multidatabase_1.jpg "alt=" Wkiol1t5qxfzakr7aacdno6gnsk762.jpg "/>

If you are using cross-library access technologies, such as There are also limitations to Oracle 's transparent gateways,DB2, and other federated queries. More common problems are:1, configuration is more troublesome, and often require database write permissions. 2. To configure aliases for tables across libraries. 3, different types of database data types are inconsistent, more difficult to handle. 4,SQL statements are limited, it is difficult to achieve complex calculations. The structure of this approach is as follows:

650) this.width=650; "src=" http://s3.51cto.com/wyfs02/M00/5A/2F/wKioL1T5QxfRkqz7AACsilkQFik590.jpg "style=" float: none; "title=" report5_structure_multidatabase_2.jpg "alt=" Wkiol1t5qxfrkqz7aacsilkqfik590.jpg "/>

This situation can consider the use of run-dry set calculation report, its built-in set-up engine can connect multiple databases, after the number of unified data calculation, can better solve the report data from different database problems. The structure of the collection report solves the problem of library storage as follows:

650) this.width=650; "src=" http://s3.51cto.com/wyfs02/M00/5A/33/wKiom1T5QgDBkTRTAACaZC05URA345.jpg "style=" float: none; "title=" report5_structure_multidatabase_3.jpg "alt=" Wkiom1t5qgdbktrtaacazc05ura345.jpg "/>

Here, through the Salesperson Sales report, take a look at the process of collecting reports to solve the problem of data storage. Reports such as:

650) this.width=650; "src=" http://s3.51cto.com/wyfs02/M01/5A/33/wKiom1T5QgDRi8bKAAF5V622bNI946.jpg "style=" float: none; "title=" report5_structure_multidatabase_4.jpg "alt=" Wkiom1t5qgdri8bkaaf5v622bni946.jpg "/>

The sales order data in the report comes from the DB2 database of the sales system, and the employee information comes from the DB2 database of the human resources system. The process of developing this report using the run-dry set of reports is as follows:

first, two data sources are configured in the collection report and the collector, the Sales system database "db2sales", and the Human Resources database "db2hr".

Second, define the grid parameter state in the collector and write the calculation script:


A
1 >salesdb=connect ("Db2sales")
2 >hrdb=connect ("db2hr")
3 =salesdb.query ("select* from Sales")
4 =hrdb.query ("select* from Employee")
5 =a3.run ([email protected] (EID:A3. Sellerid))
6 =a5.select (Sellerid. State==state)
7 =a6.new (Orderid,client,sellerid.name:sellername,amount,orderdate)
8 >salesdb.close ()
9 >hrdb.close ()
10 Result A7

A1: Connect a pre-configured db2sales data source.

A2: Connect a pre-configured db2hr data source.

A3,A4: reads the Sales Order table and the Employee Order tablefrom both data sources, respectively .

A5: Use the object reference mechanism of the collector to associate the Sales Order table and the Employee order table with the Sellerid=eid .

A6: state= the "California" filter Order table according to the parameters.

A7: Generate a New order table and get the required fields.

A8,9: Closes the database connection.

A10: Returns to the collection report.

third, define the parameter Argstate in Report Designer and configure the set to calculate the data set:


650) this.width=650; "src=" http://s3.51cto.com/wyfs02/M01/5A/33/wKiom1T5QgCSyyYVAAELpXDsViU431.jpg "style=" float: none; "title=" report5_structure_multidatabase_6.jpg "alt=" Wkiom1t5qgcsyyyvaaelpxdsviu431.jpg "/>

Four, the design report is as follows:

650) this.width=650; "src=" http://s3.51cto.com/wyfs02/M02/5A/2F/wKioL1T5QxeCsarfAAC5L0K1LUM551.jpg "style=" float: none; "title=" report5_structure_multidatabase_7.jpg "alt=" Wkiol1t5qxecsarfaac5l0k1lum551.jpg "/>

Once you have entered the parameter calculation, you can get the report you wanted earlier. The query button at the top of the report is the parameter template feature provided by the collection report, as described in the tutorial, which is not mentioned here.


This article is from the High performance report data calculation blog, so be sure to keep this source http://report5.blog.51cto.com/8028595/1617920

Run-Dry Set calculation report optimizing the application structure of data library storage

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.