CN111427626A

CN111427626A - Method and system for rapidly generating report based on dimensional data warehouse

Info

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

Abstract

The invention discloses a method and a system for rapidly generating a report based on a dimension data warehouse, belonging to the field of report generation, and aiming at solving the technical problems of reducing redundant and repetitive coding work of workers and improving report generation efficiency, the method adopts the technical scheme that S1 is used for developing a middleware for scheduling a report visualization tool, S2 is used for inputting fields, measurement aggregation modes, filtering conditions and sequencing rules related to the report to the middleware by a user, S3 is used for automatically reading and generating an SQ L statement by the middleware, S4 is used for calling the connected data warehouse by the middleware to acquire data and feeding the data back to the report visualization tool to realize rapid generation of the report, wherein the middleware comprises a logic area and a physical area, the logic area comprises an SCOTT, the SCOTT comprises a dimension table and a fact table, and a connection pool is arranged in the physical area.

Description

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

Technical Field

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

Background

Traditional report generation needs to compile SQ L for each report to acquire data in a database or manually fill in reports through a third-party report tool (non-automation), and especially when a large number of reports are faced and a large number of data sources are in the same position, a large number of database queries written by a user are found to be repetitive, and report generation efficiency is low.

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

①, the assistance of IT staff is required;

②, is inattentive when faced with a large number of reports, and produces a large amount of repetitive work.

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

Patent document No. CN109491743A discloses a visual report generation method, apparatus, electronic device and storage medium, and the method includes: step S11, a visual report template containing a plurality of charts is preset in a browser page; step S12, obtaining configuration parameters of the report set by the user; step S13, rendering the layout and the 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 rendered browser page. 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 reports based on a dimensional data warehouse, so as to solve the problems of reducing redundant and repetitive coding work of workers and improving report generation efficiency.

The technical task of the invention is realized in the following way, namely a method for rapidly generating a report based on a dimensional data warehouse, which comprises the following steps:

s1, developing a middleware for scheduling the report visualization tool;

s2, the user inputs the fields, the measurement aggregation mode, the filter condition and the sequencing rule related to the report into the middleware;

s3, automatically reading by the middleware to generate an SQ L statement;

s4, the middleware calls the connected data warehouse to obtain data and feeds the data back to a report visualization tool, so that the report can be rapidly generated;

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

SCOTT is the name of an example user in an Oracle database. The role of the method is to provide some simple application examples for beginners, but the default is a locking state, and when the method is installed, according to the needs of users, after a database configuration assistant interface is completed, a pop-up dialog box, password management and unlocking are carried out.

SCOTT is an example user inside ORAC L E, with default password of tiger, tables emp, dept, etc. below, and the relationships between these tables and tables demonstrate some basic principles of relational databases, which are commonly used by Oracle for illustration, and are also commonly used by Oracle for teaching on books and textbooks about Oracle.

Preferably, the middleware in step S1 is developed by using microsoft winform technology, and specifically, the following steps are performed:

s101, loading a dimension table and a fact table of a data warehouse: configuring connection information in a connection pool, and loading a designed dimension table and a fact table;

s102, establishing association between the fact table and the dimension table: double-clicking the fact table, and then clicking the external key field of the fact table to connect with the corresponding main key field of the dimension table, thereby establishing the 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 a logic area;

s104, uploading to a Langchao BI system: step S1 to step S3 are saved and uploaded to the Langchao BI system in an attachment mode;

and S105, designing the visual report by the aid of the Langchao BI system.

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

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

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

s10303, double clicking each metric index of the fact table, and selecting an aggregation mode of the metric index based on the default dimension attribute.

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

Preferably, after the information is successfully uploaded to the romance BI system in the step S104 in the attachment manner, the romance 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 rapidly generating reports based on a dimension data warehouse comprises,

the middleware development unit is used for developing a middleware for scheduling the report visualization tool; the middleware is developed by using Microsoft winform technology and comprises a logic area and a physical area, wherein the logic area comprises a SCOTT, and the SCOTT comprises a dimension table and a fact table; a connection pool is arranged in the physical area and used for configuring connection information and loading a designed dimension table and a designed fact table;

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

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

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

Preferably, the middleware development unit includes,

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

the association establishing module is used for establishing the association between the fact table and the dimension table, and particularly, the direct association relationship between the fact table and the dimension table is established by double clicking the fact table and then clicking the external key field of the fact table to connect with the corresponding main key field of 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 to the Langchao BI system in an attachment mode;

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

Preferably, the dragging module comprises,

the screening submodule is used for dragging the fact table used by the foreground report in the physical area and all the dimension tables associated with the fact table to the logical area;

the renaming submodule is used for renaming the table names and the fields of the fact table and the dimension table in the logic area so as to ensure that a named description language is easy to understand by a client;

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

More preferably, the aggregation mode includes MAX, MIN, SUM, and AVG.

Preferably, after the uploading module successfully uploads, the wave BI system automatically reads all configuration information on the middleware;

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

firstly, a user can generate data (including aggregated data) with various dimensions by referring to the middleware through a report visualization tool of the user, so that the redundant and repetitive coding work of workers is reduced, and the report generation efficiency is improved;

according to the invention, the report can be generated more intelligently and rapidly, the middleware plays a role of a bridge of a report visualization tool to the database, IT personnel only need to establish the association relation between the dimensionality and the fact table for the middleware on the basis of an enterprise data warehouse, and non-IT users can configure the report wanted by themselves, so that the report development work of the IT personnel is greatly liberated, and the working efficiency is improved.

Drawings

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

FIG. 1 is a schematic flow chart of a method for rapidly generating reports based on a dimensional data warehouse;

FIG. 2 is a schematic diagram of the components of the intermediate member;

FIG. 3 is a diagram of the association of fact tables with dimension tables;

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

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

FIG. 6 is a report preview effect diagram;

fig. 7 is a schematic diagram illustrating association relationships established between fact tables and related dimension tables in embodiment 3.

Detailed Description

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

Example 1:

as shown in fig. 1, the method for rapidly generating a report based on a dimension data warehouse of the present invention specifically includes:

s1, developing a middleware for scheduling the report visualization tool;

s3, automatically reading by the middleware to generate an SQ L statement;

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

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

s101, loading a dimension table and a fact table of a data warehouse: configuring connection information in a connection pool, and loading a designed dimension table and a fact table; for example dimension tables: DW _ D _ MONTH, DW _ D _ cause, fact table: DW _ F _ ORDERS;

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 the attached figure 3, and then clicking the foreign key field of the fact table to connect with the corresponding main key field of the dimension table, thereby establishing the 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 a logic area; the method comprises the following specific steps:

s10301, dragging a fact table used by a foreground report in the physical area and all dimension tables associated with the fact table to a logical area; the aggregation mode comprises MAX, MIN, SUM and AVG.

s10303, double-clicking each metric index of the fact table, and using the sales volume of the order fact table as an example, popping up a dialog box as shown in fig. 4, and selecting an aggregation mode in which the metric index is based on the default dimension attribute.

S104, uploading to a Langchao BI system: step S1 to step S3 are saved and uploaded to the Langchao BI system in an attachment mode; after 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):

username scott password 123456.

Fact table configuration information:

s105, designing a visual report by the aid of the Langchao BI system in a self-help mode, dragging three columns of 'month', 'product name' and 'sales amount' to a right area as shown in the attached drawing 5, and then generating an SQ L statement by the aid of a BI system background program in combination with configuration information of a middleware, wherein the specific steps are as follows:

select

b1.MONTH _ ID as month,

product NAME of 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；

And generating a report form preview effect, as shown in fig. 6.

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

Example 2:

the invention discloses a system for rapidly generating reports based on a dimension data warehouse, which comprises,

the middleware development unit is used for developing a middleware for scheduling the report visualization tool; the middleware is developed by using Microsoft winform technology and comprises a logic area and a physical area, wherein the logic area comprises a SCOTT, and the SCOTT comprises a dimension table and a fact table; a connection pool is arranged in the physical area and used for configuring connection information and loading a designed dimension table and a designed fact table; the middleware development unit comprises a middleware development unit,

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

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 default dimension attributes; the aggregation mode comprises MAX, MIN, SUM and AVG.

The uploading module is used for uploading to the Langchao BI system in an attachment mode; after the uploading module successfully uploads, 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 the fact tables and the related dimension tables of the data warehouse through the middleware, and then establishing association through a main foreign key, as shown in FIG. 7;

when the user wants to count the beverage sales of the store leader in each month of the quarter of 2019 through the report visualization tool, the following table is shown:

year of sale	Selling moon	Product classification	Shop leader name	Amount of sale
					2019	1	Beverage and its preparing process	A	1200
2019	2	Beverage and its preparing process	A	2000
					2019	1	Beverage and its preparing process	B	3200
2019	3	Beverage and its preparing process	B	2900

The user only needs to input fields (year, month, product category, store name, number of sales and sales unit price) into the middleware and determine the measure data aggregation mode (summary) and the filter condition (2019, one quarter, beverage), the middleware automatically generates SQ L statement and accesses the database to obtain data, and the database table and field name referred by the middleware are suggested to be transferred into the daily work term of the business user in the middleware.

Among them, the automatically generated SQ L statement of the middleware:

select a _2. year, a _2. month, a _3. product category, a _4. store owner name, sum (a _1. sales quantity) sum (a _1. sales unit price) as sales amount from retail fact table a _ 1;

l eft join date dimension table a _2on a _1, sale date a _2, year, month and day;

l eft join product dimension table a _3on a _1. self-increment ID is a _3. sell products;

l eft join clerk dimension table a _4on a _1. self-increment ID a _4. clerk;

l eft join store dimension table a _5on a _1. self-increment ID a _5. store;

where a _1. year 2019and a _1. quarter 1and a _3. product category 'beverage';

group by a _2. year, a _2. month, a _3. product category, a _4. store owner name.

The method replaces the tedious work of compiling SQ L by report IT personnel, only needs to be concentrated on the requirement design of report users, if the middleware is friendly enough, report business personnel can completely design and generate reports by themselves without intervention of IT personnel.

Finally, it should be noted that: the above embodiments are only used to illustrate the technical solution of the present invention, and not to limit the same; while the invention has been described in detail and with reference to the foregoing embodiments, it will be understood by those skilled in the art that: the technical solutions described in the foregoing embodiments may still be modified, or some or all of the technical features may be equivalently replaced; and the modifications or the substitutions do not make the essence of the corresponding technical solutions depart from the scope of the technical solutions of the embodiments of the present 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 a middleware for scheduling the report visualization tool;

s3, automatically reading by the middleware to generate an SQ L statement;

2. The method for rapidly generating reports based on the dimension data warehouse of claim 1, wherein the middleware in the step S1 is developed by using microsoft winform technology, and the method specifically comprises the following steps:

and S105, designing the visual report by the aid of the Langchao BI system.

3. The method for rapidly generating reports based on the dimension data warehouse according to claim 2, wherein in step S103, dragging the dimension table and the fact table of the physical area to the logical area specifically includes the following steps:

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

4. The method for rapid generation of reports based on the dimension data warehouse of claim 3, wherein the aggregation manner in the step S10303 includes MAX, MIN, SUM and AVG.

5. The method for rapidly generating reports based on the dimensional data warehouse as claimed in claim 2 or 3, wherein the romance BI system automatically reads all configuration information on the middleware after uploading to the romance BI system in the step S104 in an attachment manner is successful;

6. A system for rapidly generating reports based on a dimension data warehouse is characterized by comprising,

7. The system for rapid generation of reports based on the dimension data warehouse of claim 6, wherein the middleware development unit comprises,

8. The system for rapidly generating reports based on the dimension data warehouse of claim 7, wherein the dragging module comprises,

the renaming submodule is used for renaming the table names and the fields of the fact table and the dimension table in the logic area;

9. The system for rapid generation of reports based on the dimension data warehouse of claim 8, wherein the aggregation means comprises MAX, MIN, SUM, and AVG.

10. The system for rapidly generating reports based on the dimensional data warehouse as claimed in claim 7 or 8, wherein after the uploading module successfully uploads, the Langchao BI system automatically reads all configuration information on the middleware;