CN111427626B

CN111427626B - Method and system for rapidly generating report based on dimension data warehouse

Info

Publication number: CN111427626B
Application number: CN202010221499.4A
Authority: CN
Inventors: 孙远壮
Original assignee: Inspur General Software Co Ltd
Current assignee: Inspur General Software Co Ltd
Priority date: 2020-03-26
Filing date: 2020-03-26
Publication date: 2023-07-25
Anticipated expiration: 2040-03-26
Also published as: CN111427626A

Abstract

The invention discloses a method and a system for rapidly generating a report based on a dimension data warehouse, which belong to the field of report generation, and the technical problem to be solved by the invention is how to reduce redundant and repeated coding work of staff, improve report generation efficiency, and adopt the following technical scheme: the method comprises the following steps: s1, developing an intermediate piece for scheduling report visualization tools; s2, the user inputs fields, measurement aggregation modes, filtering conditions and ordering rules related to the report form to the middleware; s3, the middleware automatically reads and generates SQL sentences; s4, the middleware calls the connected data warehouse to acquire data and feeds the data back to the report visualization tool to realize the rapid generation of the report; the middleware comprises a logic area and a physical area, wherein the logic area comprises SCOTT, and the SCOTT comprises a dimension table and a fact table; a connection pool is arranged in the physical area. The system comprises a middleware development unit, an input unit, a reading and generating unit and a data acquisition and feedback unit.

Description

Method and system for rapidly generating report based on dimension data warehouse

Technical Field

The invention relates to the field of report generation, in particular to a method and a system for rapidly generating a report based on a dimension data warehouse.

Background

Traditional report generation needs to write SQL (structured query language) for each report to acquire data in a database or manually fill the data through a third-party report tool (non-automation), especially when a large number of reports are faced and a large number of data sources are in the same position, the query of a large number of databases written by you can be found to be repetitive, and the report generation efficiency is low.

The report visualization tool obtains the data by directly accessing the database, and has the following defects:

(1) the help of IT practitioners must be relied on;

(2) when faced with a large number of reports, it appears to be frustrating and produces a large number of repetitive jobs.

Therefore, how to reduce redundant and repeated coding work of workers and improve report generation efficiency is a technical problem to be solved urgently at present.

Patent document CN109491743a discloses a visual report generating method, device, electronic equipment and storage medium, the method includes: step S11, presetting a visual report template comprising a plurality of charts in a browser page; step S12, acquiring configuration parameters of a report set by a user; step S13, rendering typesetting layout and style of each chart in the browser page according to the configuration parameters; and step S14, generating a visual report corresponding to the user according to the browser page after the rendering is completed. The technical scheme has the defects of complex operation and low report generation efficiency.

Disclosure of Invention

The technical task of the invention is to provide a method and a system for rapidly generating a report based on a dimension data warehouse, which are used for solving the problem of how to reduce redundant repeated coding work of staff and improve report generation efficiency.

The technical task of the invention is realized in the following way, and the method for rapidly generating the report forms based on the dimension data warehouse comprises the following steps:

s1, developing an intermediate piece for scheduling report visualization tools;

s2, the user inputs fields, measurement aggregation modes, filtering conditions and ordering rules related to the report form to the middleware;

s3, the middleware automatically reads and generates SQL sentences;

s4, the middleware calls the connected data warehouse to acquire data and feeds the data back to the report visualization tool to realize the rapid generation of the report;

the middleware comprises a logic area and a physical area, wherein the logic area comprises SCOTT, and the SCOTT comprises a dimension table and a fact table; and a connection pool is arranged in the physical area and is used for configuring connection information and loading the designed dimension table and the fact table.

SCOTT is the name of an example user in the Oracle database. The function of the method is to provide some simple application examples for beginners, but the method is in a locking state by default, and the method is unlocked in a pop-up dialog box-password management and inside after the database configuration assistant interface is completed according to the needs of a user during installation.

SCOTT is an example user inside ORACLE, the default password is tiger, tables emp, dept and the like are arranged below, the relation between the tables demonstrates some basic principles of a relational database, oracle is exemplified by the user, and books and textbooks about Oracle are also explained by the user. It is not necessary for Oracle itself if it does not want to be able to delete (if you do not build other objects under it).

Preferably, the middleware in the step S1 is developed by using microsoft winform technology, which specifically includes the following steps:

s101, loading dimension tables and fact tables of a data warehouse: the connection information is configured in the connection pool, and the designed dimension table and the fact table are loaded;

s102, establishing association between the fact table and the dimension table: double clicking the fact table, and clicking the external key field of the fact table to connect the corresponding main key field of the dimension table, so as to establish a direct association relationship between the fact table and the dimension table;

s103, dragging the dimension table and the fact table of the physical area to the logic area;

s104, uploading to a Langchao BI system: step S1 to step S3 are stored after being completed, and then the stored data is uploaded to a Langchao BI system in an accessory mode;

s105, self-help design of a visual report form through a Langchao BI system.

More preferably, the step S103 of dragging the dimension table and the fact table of the physical area to the logical area is specifically as follows:

s10301, dragging a fact table used by a foreground report in a physical area and all dimension tables associated with the fact table to a logic area;

s10302, renaming table names and fields of the fact table and the dimension table in a logic area to ensure that the named description language is easy to understand by a client;

s10303, selecting an aggregation mode of the measurement indexes based on default dimension attributes according to each measurement index of the double-click fact table.

More preferably, the aggregation in step S10303 includes MAX, MIN, SUM and AVG.

More preferably, after the uploading to the Langchao BI system in the attachment manner in the step S104 is successful, the Langchao BI system automatically reads all configuration information on the middleware;

wherein, all configuration information of the middleware comprises database connection information and fact table configuration information.

A system for quickly generating reports based on a dimension data warehouse, the system comprises,

the middleware development unit is used for developing a middleware for scheduling report visualization tools; the middleware is developed by utilizing a Microsoft Winform technology and comprises a logic area and a physical area, wherein the logic area comprises SCOTT (stream control over the Internet), and the SCOTT comprises a dimension table and a fact table; a connection pool is arranged in the physical area and is used for configuring connection information and loading a designed dimension table and a fact table;

the input unit is used for inputting the fields related to the report, the measurement aggregation mode, the filtering conditions and the ordering rules into the middleware;

the reading and generating unit is used for automatically reading and generating SQL sentences through middleware;

and the data acquisition and feedback unit is used for acquiring data through the middleware calling connection data warehouse and feeding back the data to the report visualization tool to realize the rapid generation of the report.

Preferably, the middleware development unit includes,

the loading module is used for loading the dimension table and the fact table of the designed data warehouse after the connection information is configured in the connection pool;

the association establishing module is used for establishing association between the fact table and the dimension table, specifically, by double clicking on the fact table and then clicking on the external key field of the fact table to connect the corresponding main key field of the dimension table, thereby establishing a direct association relationship between the fact table and the dimension table;

the dragging module is used for dragging the dimension table and the fact table of the physical area to the logic area;

the uploading module is used for uploading the wave BI system in an accessory mode;

and the self-help design module is used for self-help design of the visual report form through the wave BI system.

More preferably, the drag module comprises,

the screening sub-module is used for dragging the fact table used by the foreground report in the physical area and all dimension tables associated with the fact table to the logic area;

a renaming sub-module for renaming the table names and fields of the fact table and the dimension table in the logic area to ensure that the named description language is easy for the customer to understand;

and the aggregation mode selection submodule is used for double-clicking each measurement index of the fact table, and selecting an aggregation mode of the measurement index based on the default dimension attribute.

More preferably, the polymerization modes comprise MAX, MIN, SUM and AVG.

More preferably, after the uploading module is successfully uploaded, the Langchao BI system automatically reads all configuration information on the middleware;

The method and the system for rapidly generating the report form based on the dimension data warehouse have the following advantages:

firstly, a user refers to middleware through the report visualization tool of the user, and data (including aggregated data) with various dimensions can be generated, so that redundant and repeated coding work of staff is reduced, and report generation efficiency is improved;

and secondly, the invention can generate the report more intelligently and rapidly, the middleware acts as a bridge for leading the report visualization tool to the database, IT personnel can configure the report wanted by the IT user by themselves only by establishing the association relation between the dimension and the fact table for the middleware on the basis of the enterprise data warehouse, thus greatly freeing the report development work of the IT personnel and improving the working efficiency.

Drawings

The invention is further described below with reference to the accompanying drawings.

FIG. 1 is a flow chart of a method for rapidly generating a report based on a dimension data warehouse;

FIG. 2 is a schematic diagram of the components of the middleware;

FIG. 3 is a schematic diagram of a fact table in association with a dimension table;

FIG. 4 is a schematic diagram of an aggregation of selection metrics based on default dimension attributes;

FIG. 5 is a schematic diagram of a BI report design area;

FIG. 6 is a diagram showing a report preview effect;

fig. 7 is a schematic diagram of an association relationship between a fact table and a related dimension table in embodiment 3.

Detailed Description

The method and system for rapidly generating reports based on dimension data warehouse of the present invention will be described in detail below with reference to the accompanying drawings and specific embodiments.

Example 1:

as shown in fig. 1, the method for rapidly generating the report based on the dimension data warehouse comprises the following steps:

s1, developing an intermediate piece for scheduling report visualization tools;

s3, the middleware automatically reads and generates SQL sentences;

as shown in fig. 2, the middleware comprises a logic area and a physical area, the logic area comprises SCOTT, and the SCOTT comprises a dimension table and a fact table; and a connection pool is arranged in the physical area and is used for configuring connection information and loading the designed dimension table and the fact table.

The middleware in step S1 is developed by using microsoft winform technology, and specifically includes the following steps:

s101, loading dimension tables and fact tables of a data warehouse: the connection information is configured in the connection pool, and the designed dimension table and the fact table are loaded; such as a dimension table: dw_d_moth, dw_d_product, facts table: dw_f_order;

s102, establishing association between the fact table and the dimension table: double-clicking the fact table DW_F_ORDERS, popping up a dialog box shown in figure 3, and clicking the external key field of the fact table to connect the corresponding main key field of the dimension table, thereby establishing a direct association relationship between the fact table and the dimension table;

s103, dragging the dimension table and the fact table of the physical area to the logic area; the method comprises the following steps:

s10301, dragging a fact table used by a foreground report in a physical area and all dimension tables associated with the fact table to a logic area; the polymerization modes include MAX, MIN, SUM and AVG.

s10303, taking sales of the order fact table as an example, popping up a dialog box shown in figure 4, and selecting an aggregation mode of the measurement indexes based on default dimension attributes.

S104, uploading to a Langchao BI system: step S1 to step S3 are stored after being completed, and then the stored data is uploaded to a Langchao BI system in an accessory mode; after the uploading is successful, the Langchao BI system automatically reads all configuration information on the middleware; all configuration information of the middleware includes database connection information and fact table configuration information.

Wherein database connection information (example):

the username scott password 123456.

Fact table configuration information:

s105, self-help design of a visual report form through a wave BI system; as shown in FIG. 5, three columns of "month", "product name", "sales amount" are dragged to the right side area, and then the BI system daemon combines with the configuration information of the middleware to generate SQL statements, specifically as follows:

select

b1.Month_ID as month,

b2.product NAME,

aggregation function (AMONT) as sales amount

from DW_F_ORDERS a1

left join DW_D_MONTH b1 on a1.MONTH_WID＝b1.MONTH_ID

left join DW_D_PRODUCTS b2 on a1.PRODUCT_WID＝b2.ROW_WID

group by b1.MONTH_ID,b2.PRODUCT_NAME；

A report preview effect is generated as shown in fig. 6.

The IT staff only needs to build a data warehouse and configure middleware, upload the BI system, and then the report demander can independently drag corresponding fields to form a report on a BI report design page.

Example 2:

the invention relates to a system for quickly generating a report based on a dimension data warehouse, which comprises,

the middleware development unit is used for developing a middleware for scheduling report visualization tools; the middleware is developed by utilizing a Microsoft Winform technology and comprises a logic area and a physical area, wherein the logic area comprises SCOTT (stream control over the Internet), and the SCOTT comprises a dimension table and a fact table; a connection pool is arranged in the physical area and is used for configuring connection information and loading a designed dimension table and a fact table; the middleware development unit includes a program code for executing,

the dragging module is used for dragging the dimension table and the fact table of the physical area to the logic area; the drag-and-drop module includes a drag-and-drop module,

the aggregation mode selection submodule is used for selecting an aggregation mode of the measurement indexes based on default dimension attributes for each measurement index of the double-click fact table; the polymerization modes include MAX, MIN, SUM and AVG.

The uploading module is used for uploading the wave BI system in an accessory mode; after the uploading module is successfully uploaded, the Langchao BI system automatically reads all configuration information on the middleware; wherein, all configuration information of the middleware comprises database connection information and fact table configuration information.

Example 3:

introducing fact tables and related dimension tables of a data warehouse through middleware, and then establishing association through a main external key, as shown in fig. 7;

when the user wants to count the situation of selling beverages by the store length of each month in quarter of 2019 through the self report visualization tool, the following table is shown:

year of sale	Sales month	Product category	Shop name	Sales amount
					2019	1	Beverage	A	1200
2019	2	Beverage	A	2000
					2019	1	Beverage	B	3200
2019	3	Beverage	B	2900

The user only needs to input fields (year, month, product category, store name, sales number, sales unit price) to the middleware and determine the measurement data aggregation mode (summary) and filtering conditions (2019, quarter, beverage), and the middleware automatically generates SQL sentences and accesses the database to acquire data. It is suggested to convert the database table and field names referenced by the middleware into daily work terms for the business user in the middleware.

The middleware automatically generates SQL sentences:

select a_2, year, a_2, month, a_3, product category, a_4, store name, sum (a_1, sales quantity) sum (a_1, sales unit price) as sales amount from retail facts table a_1;

left join date dimension table a_2on_a_1. Sales date=a_2. Year, month, day;

left join product dimension table a_3on_a_1. Self-increment id=a_3. Sell product;

left join store clerk dimension table a_4on a_1. Self-increment id=a_4. Store clerks;

left join store dimension table a_5on a_1. Self-increment id=a_5. Store;

whera_1, year of sale=2019 and a_1, quarter of sale=1and a_3, product category= 'beverage';

group by a_2, year, a_2, month, a_3, product category, a_4, store name.

The method replaces complex work of writing SQL by report IT personnel, only needs to concentrate on the demand design of report users, and if middleware is friendly enough, report business personnel can completely design and generate reports by themselves without intervention of the IT personnel.

Finally, it should be noted that: the above embodiments are only for illustrating the technical solution of the present invention, and not for limiting the same; although the invention has been described in detail with reference to the foregoing embodiments, it will be understood by those of ordinary skill in the art that: the technical scheme described in the foregoing embodiments can be modified or some or all of the technical features thereof can be replaced by equivalents; such modifications and substitutions do not depart from the spirit of the invention.

Claims

1. A method for rapidly generating a report based on a dimension data warehouse is characterized by comprising the following steps:

s1, developing an intermediate piece for scheduling report visualization tools; the middleware is developed by utilizing a Microsoft wind technology, and specifically comprises the following steps:

s10302, renaming table names and fields of the fact table and the dimension table in the logic area;

s10303, selecting an aggregation mode of the measurement indexes based on default dimension attributes according to each measurement index of the double-click fact table;

s104, uploading to a Langchao BI system: step S101 to step S103 are stored after being completed, and then the stored data are uploaded to a Langchao BI system in an accessory mode;

s105, self-help design of a visual report form through a wave BI system;

s3, the middleware automatically reads and generates SQL sentences;

2. The method for quickly generating report forms based on dimension data warehouse as claimed in claim 1, wherein the aggregate manner in step S10303 includes MAX, MIN, SUM and AVG.

3. The method for quickly generating report forms based on dimension data warehouse according to claim 1, wherein the wave BI system automatically reads all configuration information on the middleware after the success of uploading to the wave BI system in the attachment manner in step S104;

4. A system for quickly generating reports based on a dimension data warehouse is characterized in that the system comprises,

the data acquisition and feedback unit is used for acquiring data through the middleware calling connection data warehouse and feeding back the data to the report visualization tool to realize the rapid generation of the report;

wherein the middleware development unit comprises a middleware development unit,

the self-help design module is used for self-help design of a visual report through a wave BI system;

wherein the dragging module comprises a dragging module, a dragging module and a dragging module, wherein the dragging module comprises a dragging module,

a renaming sub-module for renaming the table names and fields of the fact table and the dimension table in the logic area;

5. The system for quickly generating a report based on a dimension data warehouse of claim 4, wherein the aggregate manner comprises MAX, MIN, SUM and AVG.

6. The system for quickly generating reports based on the dimension data warehouse of claim 4 or 5, wherein after the uploading module is successful, the Langchao BI system automatically reads all configuration information on the middleware;