A mixed data source for optimizing application structure of run-dry set calculation report

in a report project, report source data often comes from a variety of heterogeneous data sources. For example: relational database (Oracle,Db2,Mysql),NosqlDatabase (Mongodb),httpdata Source,Hadoop(Hive,Hdfs) Even theExcelor a text file. The usual practice is to useEtltools to synchronize these data sources into the Data warehouse. But the problem with this is:1, complex configuration, difficult;2and high cost;3, the data can not be accessed in real time, it needs a long time delay;4, the construction and management of data warehouse are more complicated;5, if the amount of data is very inefficient, and constantlyEtlto each application system synchronization data;6, the Data Warehouse is also the traditional database technology, if the load is large, the cost of expansion is higher.

If you use the run-dry aggregate reporting tool, you can easily and directly implement a hybrid data source report. Run dry set calculation report directly read a variety of mixed data sources through the built-in set engine, can solve the above problems, so that the data in the most appropriate way to store, at a small cost to present a hybrid data source-based real-time report. The structure of ETL method and the method of the aggregate report is as follows:

650) this.width=650; "src=" http://s3.51cto.com/wyfs02/M00/59/EF/wKiom1TwDJ7zhhDRAAFXb4NC998522.jpg "style=" float: none; "title=" report5_structure_mixed_datasource_1.jpg "alt=" Wkiom1twdj7zhhdraafxb4nc998522.jpg "/>

Below, through the "State sales Sales Report" to look at the specific implementation steps. Reports such as:

650) this.width=650; "src=" http://s3.51cto.com/wyfs02/M01/59/EC/wKioL1TwDavRdnMVAAGec4yL1CQ749.jpg "style=" float: none; "title=" report5_structure_mixed_datasource_2.jpg "alt=" Wkiol1twdavrdnmvaagec4yl1cq749.jpg "/>

the sales data of the report comes from the MongoDB database of the sales system , and the salesperson's information comes from the DB2 database of the Human resources system . The report data source does not need to be synchronized periodically, and there is no time lag when using a mixed data source for the collection report.

The first step is to write the script in the Collector designer and save it as statesales.dfx as follows:

650) this.width=650; "src=" http://s3.51cto.com/wyfs02/M01/59/EF/wKiom1TwDJ6yLg_AAAGx7fLH5WA464.jpg "style=" float: none; "title=" report5_structure_mixed_datasource_3.jpg "alt=" Wkiom1twdj6ylg_aaagx7flh5wa464.jpg "/>

A1: Connect a pre-configured db22 data source.

A2: reads the Employee Order table from the data source state= "California"according to predefined parameters .

A3,A4: reads the Orders order table from MongoDB inthe sales system .

A5: Use the switch function of the collector to switch the Sellerid field in the Orders table to the Employee order table sellerid= the records associated with the Eid. the @i option means that if the corresponding record is not found, the row is deleted.

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

A7,A8: Closes the database connection.

A9: 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/M02/59/EC/wKioL1TwDauScwGTAAD4OVCzols533.jpg "style=" float: none; "title=" report5_structure_mixed_datasource_4.jpg "alt=" Wkiol1twdauscwgtaad4ovczols533.jpg "/>

Four, the design report is as follows:

650) this.width=650; "src=" http://s3.51cto.com/wyfs02/M02/59/EF/wKiom1TwDJ7QUtBIAAChuE7Aqy0842.jpg "style=" float: none; "title=" report5_structure_mixed_datasource_5.jpg "alt=" Wkiom1twdj7qutbiaachue7aqy0842.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 set calculation report. the specific practices for parameter templates and DB2,mongodb data source configuration are described in tutorials and other documentation, which are not mentioned here.

It should be explained that if the data source structure changes, such as the new on-line sales system with the Oracle database, as long as the modified statesales.dfx A1 to : >hrdb=connect ("ora")

copy The Oracle JDBC driver and configure the connection parameters of the Oracle database at the same time. Report system configuration and changes are very small.

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

A mixed data source for optimizing application structure of run-dry set calculation report