CN106991183B - A kind of packaging method and system of business intelligence ETL - Google Patents
A kind of packaging method and system of business intelligence ETL Download PDFInfo
- Publication number
- CN106991183B CN106991183B CN201710227461.6A CN201710227461A CN106991183B CN 106991183 B CN106991183 B CN 106991183B CN 201710227461 A CN201710227461 A CN 201710227461A CN 106991183 B CN106991183 B CN 106991183B
- Authority
- CN
- China
- Prior art keywords
- data
- dimension
- etl
- erp
- analysis
- Prior art date
- Legal status (The legal status is an assumption and is not a legal conclusion. Google has not performed a legal analysis and makes no representation as to the accuracy of the status listed.)
- Active
Links
Classifications
-
- G—PHYSICS
- G06—COMPUTING; CALCULATING OR COUNTING
- G06F—ELECTRIC DIGITAL DATA PROCESSING
- G06F16/00—Information retrieval; Database structures therefor; File system structures therefor
- G06F16/20—Information retrieval; Database structures therefor; File system structures therefor of structured data, e.g. relational data
- G06F16/25—Integrating or interfacing systems involving database management systems
- G06F16/254—Extract, transform and load [ETL] procedures, e.g. ETL data flows in data warehouses
Abstract
The present invention relates to the packaging methods and system of a kind of business intelligence ETL.This method can be based on several ERP data sources, construct by the customized data cube of ERP.Since the industry of each enterprise, accounting system, management system, ERP supplier, data source quantity etc. are not quite similar, the field Current commercial intelligence ETL status is to carry out standard packaging for specific ERP system common segment data, or it all must be by professional IT personnel in the customized ETL logic of database code rank, it is realized so as to cause ETL and safeguards or produce little effect or enterprise at high cost, substantially constrain business intelligence in the universal of the field medium-sized and small enterprises ERP.The present invention makes common ERP user be detached from profession IT personnel assistance, foundation, configuration, building and the update data cube in business intelligence data warehouse can be also completed, in case Intellectual analysis is used.And pertinent literature is not found in the OLAP financial statement Chinese and English retrieval in the present invention.
Description
Technical field
The present invention relates to data processing field, the specially a kind of packaging method and system of business intelligence ETL.
Background technique
ETL (Extraction-Transformation-Loading, extraction-conversion-load) is BI (Business
Intelligence, business intelligence) important component, due to BI show tool (such as: Tableau, Microsoft
BI, Qlikview etc.) it is highly developed and perfect, the realization period of a business intelligence project largely determines with effect
Design cycle and effect in ETL module, if the data that cannot be needed on demand to enterprise are accurately extracted and are stored,
So business intelligence project inevitably becomes mirage.
Inventor has found business intelligence in existing practice, and there are the following problems: due to the industry of each enterprise, accounting core
Calculation system, management system, the phase not to the utmost such as focal point, analytical model of ERP system supplier, data source quantity and client
Together, business intelligence project be usually all project type deliver, i.e., according to client analysis purpose, design BI analysis model, according to point
Analysis model writes code by professional IT personnel and realizes ETL, and when maintenance also needs to realize by code.Certainly, there are also ERP supplies
Quotient has been devoted to the standardization of business intelligence product, but ETL encapsulates general execution logic according to the absolute general character of enterprise,
Perhaps customization is not supported to configure or also need to be adjusted by professional IT personnel modification program code, SQL code completely
The execution logic of ETL, and preset ETL logic only supports the entry level of all enterprise's general character to analyze.To sum up, customization
ETL logic means cannot be general, and general ETL logic means can only support coarse and crude entry level to analyze, to most enterprises
Break-up value is very limited, by taking financial analysis as an example: accounting item is arranged several levels, under some accounting item whether configuration item
Mesh is calculated, is configured with several Project Financial Analysis, specifically which Project Financial Analysis, and whether the enterprise is customized for Project Financial Analysis.Collection is not said
Different enterprises can be different under group, also can be different in the different operating cycles of the same enterprise, and more leisure opinion enterprise is customized
Why individuation data can exist, just as these data, there are management value, the value of analysis is self-evident.
And in ERP Intellectual analysis field, professional due to the especially financial field in the field ERP, IT personnel are usual
It is difficult to understand the professional knowledge of ERP, and ERP user can not generally also grasp IT code language required for ETL, to cause
Technical barrier and communication disorders cause the part ETL in business intelligence project to realize that the period is long, and data check and correction is difficult, and effect is not
Ideal, is also difficult to adjust and safeguard after business intelligence system is online or even the change of ERP system data, may cause cured
ETL module generates indiscoverable mistake, often needs to modify ETL code, can correctly execute ETL logic.Above-mentioned ETL's
Status results in business intelligence project implementation enterprise at high cost, and it is heavy to popularize difficulty.
Summary of the invention
The purpose of the present invention is to provide the packaging methods and system of a kind of business intelligence ETL, can be compatible with common
ERP system (such as: Yongyou Software, JinDie Software, smooth prompt logical etc.), and as the adjustment of ERP system data source regenerates point
Dimension type or analysis dimension data are analysed, the ERP data of isomery can be merged together, and can be by ERP user according to enterprise
Industry is needed directly to configure ETL scheme and be generated data cube (customizing true table).
To achieve the above object, the technical scheme is that a kind of packaging method of business intelligence ETL, including it is as follows
Step,
S1, the selected ERP data source of analysis and extraction and analysis dimension, if multiple ERP data sources, to analysis dimension into
Row duplicate removal merges；The analysis dimension for analysis dimension type or is analyzed in dimension data the two at least according to the actual situation
One；
S2, alternatively parameter selects to user and saves as ETL scheme to the analysis dimension for generating S1, is provided according to S1
Analysis dimensional properties it is different, that is, at least one in analysis dimension type or analysis dimension data the two is provided；According to reality
Situation, if it also has multiple factual data types, factual data type can alternatively parameter be supplied to user simultaneously
It selects and is saved in ETL scheme together；
S3, the analysis dimension based on the saved ETL scheme of user in step S2 and factual data type are (if ETL scheme
Then it is the default factual data type of encapsulation not comprising factual data type) building generation data cube, it saves to data bins
In library, each ETL scheme, which has and only corresponds to data cube, (while to be included dimension and the fact, can be T-SQL language
The objects such as sentence, view or database table), for the calling such as BI demonstration tool, report tool.
In an embodiment of the present invention, in the step S2,
The calculating standard of factual data can also be provided in addition to selecting the analysis dimension, factual data type parameter in user
(i.e. load parameter) is selected as parameter to user；
User can modify above-mentioned ETL scheme at any time, when new departure saves or scheduled, will reconstruct simultaneously according to new departure
Update data cube.
In an embodiment of the present invention, in the step S3,
It saves in data cube to data warehouse, according to the characteristic of ERP data source, creation analysis dimension and true number
According to storing framework in order to data storage, it is specific:
The ERP data source refers to the database of the various information management system of enterprises and institutions, the information management system
System includes financial system, supply chain, human resources, the manufacturing, COST system etc.；
The storage unit of equivalent amount is generated according to the corresponding organization's number of ERP data source (or data source number), often
A storage unit includes several consistent dimension tables of storage organization and true table, that is, the same property of all storage units
The number of columns of table be the same with attribute；
User can modify ERP data source at any time, and after the adjustment of ERP data source or when ETL scheme is scheduled, which will
It is rebuild according to new ERP data source.
In an embodiment of the present invention, after in the step S3, further include a data conversion step S4: dimension is virtually weighed
Group, be used for zero code recombination analysis dimension of user, i.e., user can carry out classification dimension it is customized, and by configured in S2 several
ETL scheme maps to the detail dimension in customized classification dimension, will be according to multistage customized when scheme saves or is scheduled
Classification dimension and corresponding ETL scheme in dimension construct and updates data cube, for BI demonstration tool calling.
In an embodiment of the present invention, it for financial analysis, in dimension map, can specify simultaneously each customized
Whether detail dimension deducts item, and to support financial statement olap analysis, the scheme that dimension virtually recombinates is supported according to financial statement
Report item be arranged, the corresponding report of a scheme (such as: balance sheet, profit and loss statement etc.) or report
Partial Elements (such as: assets class is total, be in debt and owner's equity etc.), specifically include:
Financial statement data cube (the example that can support olap analysis is directly generated based on the virtual reorganization scheme of the dimension
Such as: assets add up to-current assets-money-capital-cash in banks-XX bank, and support summarizes step by step, drills through step by step, being sliced stripping and slicing
Deng), which is the consolidated statement of the individual statements of individual enterprise, several enterprises, or data are offset in load, becomes conjunction
And report.
The present invention also provides the package systems of business intelligence ETL a kind of, including,
ETL component is pre-processed, for analyzing specified N number of ERP data source, and generates analysis dimension type or analysis dimension
Degree is according at least one in the two, when if multiple ERP data sources, copes with the analysis dimension type or analysis dimension data
Carry out duplicate removal merging；Wherein, N is the integer more than or equal to 1；
ETL layout structure, for showing that the pretreatment ETL component merges the analysis dimension type generated (analysis dimension
Selector) or analysis dimension data (loading environment selector), the selector of encapsulation support user configuration above-mentioned parameter is with new
Increase, modify and stores customization ETL scheme；According to the actual situation, if it also has multiple factual data types, true number
According to type (factual data selector) can alternatively parameter be supplied to user's selection and be saved in the customization side ETL together simultaneously
Case；
True table layout structure is customized, according to the customization ETL scheme of ERP user configuration (if in such ETL scheme not
Comprising factual data type, then for program encapsulation the fact data type) building generates data cube, ETL scheme and data
Cube corresponds；This sentences database table that is, customizes true table as data cube, or with T-SQL sentence, view
Etc. alternate datas library table as data cube；
Customization ETL component generates according to the customization ETL scheme constructs of ERP user configuration and customizes true table or T-
SQL statement, view etc..
In an embodiment of the present invention, the ETL layout structure may also display load parameter and select to user；
ERP user can modify used ETL scheme (or the virtual reorganization scheme of dimension), and save the program, with
Continue ETL layout structure calling and modification after an action of the bowels and customize true table resetting component and is rebuild to true table is customized accordingly,
And call customization ETL component.
In an embodiment of the present invention, further include that dimension virtually recombinates component, enable a user to carry out customized
It is classified dimension setting, and several ETL scheme virtual maps are given to the customized detail dimension configured, concrete function includes:
User according to analysis demand, can by several specified ETL schemes distinguish virtual map to user configuration its
In customized detail dimension in the middle virtual reorganization scheme of dimension, the cured dimension membership of recombination ERP system, reconstruct
" multistage customized dimension-ETL Scheme Choice dimension " this completely new dimension, and the condition loaded referring to data in ETL scheme
And parameter, complete customized analysis dimension；
For financial analysis, by by each report member of financial statement (such as: asset-liabilities, owner's equity)
Whether element is set as the classification dimension of different levels, and each customized detail dimension is arranged and deducts item, realizes olap analysis wealth
Business report.
It in an embodiment of the present invention, further include a data warehouse frame layout structure, data warehouse frame resetting structure
Part, one customize true table resetting component and a data permission control member,
The data warehouse frame layout structure, for analyzing the data structure characteristics of selected ERP data source, and according to
Characteristic creation analysis dimension (such as: accounting item, Project Financial Analysis, true type etc.) storing framework, it is corresponding according to ERP data source
Organization's number (or data source number) generate equivalent amount storage unit, each storage unit include storage organization it is consistent
Several dimension tables and true table；
The data warehouse frame resets component, when changing for ERP data source, reconstructs data warehouse storage frame；
The true table of the customization resets component, is used for when user ETL scheme or the virtual reorganization scheme of dimension change, weight
Structure customizes true table；
The data permission control member authorizes when customizing true table for exporting and arrive BI demonstration tool according to user,
The fact that output has authorized data.
In an embodiment of the present invention, the data warehouse frame layout structure also has following function:
The database table structure for assessing the database on specified database server is identified according to the storage organization feature of database
ERP system supplier and product type, it is automatic to construct ERP data source list, in case ERP user configuration；
The ERP data source refers to the various information Management System Data library of enterprises and institutions, above- mentioned information management system
Including financial system, supply chain, human resources, the manufacturing, COST system etc.；
For the different ERP system products of each ERP system supplier, the mating product in pretreatment ETL component is called
ETL process method carries out data prediction.
Compared to the prior art, the present invention can support the ERP system number of several isomeries (or isomorphism) of several enterprises
According to source, multiple or single ERP data sources of some enterprise can be also supported, pass through pretreatment ETL component transmitting analysis dimension type
It updating and stores with analysis dimension data to data warehouse sub-unit, user passes through ETL layout structure rapid configuration ETL scheme,
Customization ETL is by user's scheme constructs and updates the true table of customization；
It is emphasized that the present invention is supported the isomate of different suppliers, with identical storage architecture by data
Merging is drawn into the same data warehouse, to realize that the data of isomery ERP data source merge, makes using multiple suppliers'
The conglomerate of ERP system, or replaced ERP system and existed simultaneously the enterprise of heterogeneous data source, isomery can be fast implemented
More ERP data sources merge, and provide support for subsequent customization ETL；
It is clear that this method can also support the conglomerate for possessing different industries subordinate enterprise simultaneously, it is either multiple
Or individual data source, and the fact that Intellectual analysis calling tables of data, all pass through ETL layout structure or dimension by user
Virtual recombination zero code configuration of component of degree, is greatly reduced the cost of implementation of the field ERP business intelligence project ETL, while relative to
For universal standardization ETL encapsulation, data analytical effect is greatly improved；
Especially it is emphasized that OLAP financial statement signified in the present invention, refers in particular to OLAP balance sheet (branch
Summarizing step by step for such as " assets adding up to-current assets-money-capital-cash in banks-XX bank " is held, is drilled through step by step, slice is cut
Block) and OLAP profit and loss statement (support such as " total profit-operating profit-income from main operation-operation cost-travel charge " by
Grade summarizes, and drills through step by step, is sliced stripping and slicing) it is the customized completely new things of an inventor, it can not find at present with Chinese and English retrieval
Relevant any document.
Detailed description of the invention
Fig. 1 passage ETL method configuration diagram.
The ETL system configuration diagram of Fig. 2 one embodiment of the present of invention.
The ETL system flow diagram of Fig. 3 one embodiment of the present of invention.
The ETL configuration selector schematic diagram of Fig. 4 one embodiment of the present of invention.
Fig. 5 lists dimension and virtually recombinates component operation principle schematic diagram for reconstructing subject analysis dimension.
Of the invention another of Fig. 6 is suitable for the ETL method flow schematic diagram of the embodiment of single data source.
Specific embodiment
The packaging method and system of the business intelligence ETL in order to better understand the present invention, below in conjunction with Fig. 2-figure
5 pairs of of the invention specific embodiments carry out the description of system.Each component and selector according to the present invention are
It is encapsulated and is realized by the method for programming (including but not limited to: java, H5, PHP, C#, VB), and provide operation interface to user
(especially ERP user) carries out operation control.
It is as shown in Figure 3:
Step 301: firstly, providing interface by user configuration and connects ERP data source server, the configuration of data warehouse frame
Component analyzes the storage organization of database on server, and stores feature and data according to the system of ERP supplier
Database table structure identifies the supplier of ERP, the classification of system, the version of system.
If the ERP data source support a set of account of group (such as: NC, U9 of Yongyou Software, EAS, K/3 of JinDie Software
Cloud), then institutional framework table is constructed according to the institutional framework table of ERP data source.
If ERP data source does not provide the function of institutional framework, one data source of an enterprise (such as: Yongyou Software
The T series of products of U8, Chang Jietong, K/3 WISE of Kingdee, KIS series of products), then it is mentioned by data warehouse frame layout structure
For an interface for users maintenance organization structure table, and ERP data source is mapped to specified organization by user.Due to one
There may be multiple ERP data sources (such as one data source of a fiscal year of UF U8 early production, Huo Zhegai for a enterprise
Become base profile and build account again), so must support user that multiple ERP data sources are mapped to the same enterprise in organization
Industry.
Step 302: preferably, the whole system in order to make ETL more efficiently should will analyze dimension type, analyze dimension
Data, factual data etc. first carry out an ETL and are saved in data warehouse.It is true in order to construct rationally applicable storing framework
Table and dimension table divide storage unit according to organization or data source and store, and are each organization (or ERP data source)
A virtual individual storage unit, the unit include the consistent correlation analysis dimension table of storage organization and underlying fact tables, visitor
When family ETL component is called, according to ETL scheme or the virtual reorganization scheme aggregate statistics preprocessed data of dimension, customization is updated
True table.
Analysis dimension table refers to specific analysis dimension data, such as: Operating Chart of Accounts, Project Financial Analysis table are (when it is implemented, view is pre-
It is detachable if data volume is larger depending on the amount of counting are as follows: commodity list, customer table, supplier's table, department table, staff table etc.),
By taking subject dimension table as an example, data warehouse frame reset component to the subject level of each ERP data source, whether configure project core
The characteristics such as calculation are analyzed, and create compatible all data source section purpose storing frameworks.
Step 303: when ERP data source is saved by modification and come into force, then data warehouse frame being called to reset component.
Step 304: the loading range that pretreatment ETL component is specified according to timestamp or user, by specified ERP data source
Analysis dimension type update to dimension type list, or dimension data will be analyzed and update the analysis dimension to corresponding storage unit
Table, or said two devices are handled simultaneously, factual data cleans and is re-loaded to the underlying fact tables of corresponding storage unit.
Whether pretreatment ETL component can be already present on data warehouse according to the data characteristics identification data of analysis dimension,
If there is no the analysis dimension data is then synchronized, with bound data integrality.
Step 305: if data source increases or adjustment, data warehouse frame resetting component can clean all dimension tables, and
Call data warehouse frame layout structure rebuild data warehouse storage frame (or according to circumstances while cleaning true table, and
Reset time stamp), which can also be called as needed by user.
Step 306: user can pass through the 1- in selector selection analysis dimension, factual data, loading environment, load parameter
4 projects, specific embodiment is subsequent carefully to be stated referring to attached drawing 4.
Step 307: user can by configuring the virtual reorganization scheme of dimension, and ETL layout structure it is configured several
ETL scheme (including analysis dimension, factual data, loading environment, load parameter) is mapped to specified customized detail dimension.Tool
The embodiment of body is subsequent carefully to be stated referring to attached drawing 5.
Step 308: checking that the true table of the corresponding customization of ETL scheme whether there is, customize the fact if there is no then calling
Table layout structure.Otherwise, it turns to and checks whether ETL scheme adjusts.
Step 309: customizing true table layout structure and allocation plan type is verified, protected if it is ETL layout structure
The scheme deposited, then the analysis dimension specified according to user and factual data building customize true table.
The scheme of component preservation is virtually recombinated if it is dimension, then according to the customized dimension of multistage of user configuration and reservation
ETL scheme dimension and factual data building the virtual reorganization scheme of dimension corresponding to customization fact table.
Step 310: when ETL scheme is saved by modification and come into force, component then calls the true table resetting component of customization.And it resets
The timestamp of customized detail dimension is mapped in the associated virtual reorganization scheme of dimension.
Step 311: customization ETL component verifies allocation plan type, the side saved if it is ETL layout structure
Case, then the analysis dimension specified according to user and factual data extract data from sub-unit data warehouse, and carry out aggregate statistics.
The scheme of component preservation is virtually recombinated if it is dimension, then is tieed up according to the customized dimension of user configuration and the ETL scheme of reservation
Degree customizes true table from corresponding several and extracts data, and carries out aggregate statistics, construct and update corresponding customization and is true
Table.
One ETL scheme or the virtual reorganization scheme of dimension will construct one and only construct a data cube, preferably
Ground, which is to customize true table, as described above, the customization fact table data or derive from sub-unit data warehouse,
Or source and others customize true table, do not use classical star-like storing framework, but make to customize true table to include one
The data cube (DataCube) for including entirely, that is, analysis dimension level and factual data have been selected comprising all simultaneously.Citing
For, for financial analysis, such as comprising selected: all kinds of times, each level institutional framework, each level subject, each
The column such as level Project Financial Analysis, remaining sum and amount incurred, to support to be directly used in Intellectual analysis；The data cube for including entirely
Benefit essentially consists in the complicated incidence relation that avoids between star-like storing framework database table, and (the Left join Left-wing Federation connects, Right
Couple in the right connection of join, Inner join, Full join couples entirely, Cross join cross product, Primary key master
Key, Foreign key external key, Join field join field etc.) etc. caused by amateur IT personnel can not be by true table and dimension
Spending table becomes olap analysis data source by query designer association, is common in order to make the purpose of core of the invention after all
ERP user can configure ETL scheme completely and construct the true custom table of oneself expection, to be directly used in Intellectual analysis, institute
Not select traditional star-like storing framework.
Alternately select, can also be not based on physics mode generate tables of data, and only construct one section of T-SQL sentence using as
Business intelligence data source, but this method is selected, when data reach certain magnitude, performance will be difficult to ensure, it is contemplated that final
The user of business intelligence is usually enterprise owner or decision-making level, so the present invention is preferred true to be stored in dimension and uniformly one
The storing framework of a physical table.
After step 312:ETL scheme changes, the corresponding fact table that customizes cannot be true by customization only according to update of time stamp
Table resets component refactoring and customizes true table, and calls customization ETL component.True table resetting component is customized to determine what needs reconstructed
It makes true table to be cleaned, calling customizes true table layout structure reconstruct and customizes fact table after the completion of cleaning.Preferably, thing is customized
Real table resetting component can also check whether the ETL scheme is associated with by the virtual reorganization scheme of dimension, if there is association scheme, then
Customization ETL component is called also to carry out clearly the corresponding data of the associated customization fact table generated by the virtual reorganization scheme of dimension
It washes and heavily loaded.
As described in step 306, the design of selector in ETL layout structure, the configuration side ETL that can be flexible and efficient with user
For the purpose of case, select ERP user commonly various controls as interface operation carrier.User can increase newly and save new ETL
Scheme, can also preset component or the scheme of user's history configuration is adjusted and saves.Preferably, in order in the side ETL
The operating efficiency of user is improved when case substantial amounts, ETL layout structure allows user to the customized classification of ETL scheme, and by ETL
Scheme is stored by the classification that user specifies, and allows user search and selection ETL scheme.
As shown in figure 4, the selector in ETL layout structure includes following part, wherein module 403 and 405 should be deposited at least
At one:
Module 401: it is main comprising analysis dimension selector and factual data selector, it will affect the number for customizing true table
According to column.
Module 402: mainly comprising loading environment selector and load parameter selector, it will affect the number for customizing true table
According to row, or influence the true data value in data line.
Module 403: analysis dimension selector will affect the dimension data column for customizing true table, the data column of the selector
The dimension type that table is generated from pretreatment ETL component, for selecting which dimension to participate in analysis.Pass through combo box, list
Which dimension the selection of the controls such as choice box will analyze, for financial analysis, such as: current customization fact table
Theme is selling charges analysis, can choose analysis " department+item sale " combination dimension, can also selection analysis " office worker "
Dimension.
Module 404: factual data selector will affect the fact that customization fact table data and arrange.Pass through list choice box etc.
Which factual data control selection will analyze, for financial analysis, such as: beginning balance, debit is more than the beginning
Volume, credit side's beginning balance, debit's amount incurred, credit amount, ending balance, debit's ending balance, credit side's ending balance, the profit and loss
Volume etc..Different true data can also be directly packaged into several operation interfaces, such as profit and loss class ETL arrangements forms,
Directly encapsulation amount of excess and deficit access, without by user's select facts data.
Module 405: loading environment selector will affect the data line for customizing true table, and the data list of the selector comes
The dimension data generated derived from pretreatment ETL component, for screening the data of specified dimension.It is selected by tree-shaped choice box, date
It selects the controls such as frame and specifies the data area currently to be analyzed.Such as: time range is selected by date choice box, passes through tree-shaped choosing
Select frame is specified only to count which mechanism, specific organization or which specific accounting item etc..Certainly, all in order to control
The loading environment of company's (or data source), dimension here, it may be that the merging dimension of the entire group after duplicate removal merging.With meeting
For counting subject, using subject code as key column, duplicate removal merging is carried out to the ERP data source of entire group, forms group's subject
Table, so that the tree-shaped selector of subject is selected.
Module 406: load parameter selector will affect the data line for customizing true table, or influence the fact in data line
Data value.It designates whether to load specific data by controls such as list choice boxs, such as: the state of voucher is (unexamined
Core has been audited, has been transferred items), and by the controls such as check box selection load parameter, for financial analysis, such as: whether examine
Consider combined offset is true, whether calculates percentage of shares etc..
5 pairs of dimensions virtually recombinate component and are carefully stated with reference to the accompanying drawing, by taking financial analysis as an example, on the one hand, enterprise is
More efficient management data, it will usually reduce the level of subject or Project Financial Analysis, and the more huge level-one dimension of quantity is set
It spends (subject, Project Financial Analysis), by taking subject as an example, level-one subject usually has more than hundred, and object is too many, unsuitable data comparison point
Analysis；On the other hand, the base profile classification of ERP system is typically based on daily management needs, in addition to this cured level is subordinate to
Category relationship, enterprise usually also need according to other statistical method analyzing financial datas, for can be by the ETL method of user configuration
For, user can be supported separately to configure multistage customized dimension, and several specified ETL schemes (are contained by virtual map
Analyze dimension, factual data, loading environment and load parameter) to specified customized detail dimension, generation is new to divide virtual map
It is vital for analysing dimension.
Dimension virtually recombinates component, supports the virtual reorganization scheme of the customized dimension of user, and by user's virtual map side ETL
Case, expands analysis dimension, customization ETL component is expanded again according to virtual map after the customized dimension of multistage in ETL scheme
Dimension and the true table of the corresponding customization of ETL scheme, building and update and customize true table, thus by the effect of customization ETL
It is promoted to a New step.
Do not have tangible membership between customized detail dimension and analysis dimension, virtually recombinates component according only to dimension
In specify association, form virtual map relationship, an ETL scheme can be mapped to the virtual reorganization scheme of multiple dimensions, and be somebody's turn to do
The common practice in field are as follows: directly write code and loading environment is specified to summarize dimension data upwards, under the mode, analysis dimension is not
It is expanded, does not support user flexibility to configure, less support olap analysis (summarize step by step, drill through step by step, be sliced stripping and slicing etc.), and
Invention passes through the virtual reorganization scheme of dimension of user configuration, realizes the recombination and expansion of analysis dimension.Both it easily realized by dimension
The virtual recombination of degree carries out various collect statistics analyses to data, and also supporting will " multistage customized dimension-multistage in olap analysis
ETL scheme dimension " is completely through forming the organic whole of a complete analysis chain, support olap analysis.
For financial statement analysis, the virtual reorganization scheme support of dimension is arranged according to the report item of financial statement, and one
The corresponding report of a scheme or Partial Elements, and each customized detail dimension of user setting is allowed whether to deduct item, structure
Part can be directly generated based on the virtual reorganization scheme of the dimension can support olap analysis financial statement (such as: balance sheet,
Profit and loss statement etc.) data cube, these financial statements can be the individual statements of monomer enterprise or some subordinate enterprise, group,
" the considering combined offset " being also possible in the consolidated statement of several enterprises, or selection load parameter, data are offset in load,
As consolidated accounts.
As described in step 307, dimension, which virtually recombinates component, can recombinate the dimension of customer analysis.Dimension virtually recombinates component
Mainly include two cores: dimension virtually recombinates configurator, allows user newly-increased or the configuration virtual reorganization scheme of dimension, often
A dimension virtually recombinates the customized dimension for supporting several ranks.Preferably, user can classify to dimension scheme, and
Search function is provided simultaneously.To improve the efficiency of user search and selection scheme.
Dimension virtually recombinates mapper, allows user that the ETL scheme saved is mapped in the virtual reorganization scheme of dimension
The customized dimension of most detail, to form virtual associated relationship, it is preferable that configurator support user select original ETL scheme in
Analysis dimension whether continue to retain, and retain dimension level, thus allow user it is more targeted customization and deposit
Storage customizes true table, dimension virtually recombinate component when stored can according to customized dimension, be arranged to the ETL scheme retained and tie up
Degree and corresponding dimension level, expand and form new customized analysis dimension, and customization ETL component calls dimension virtually to recombinate
When scheme, it will call the customized analysis dimension, corresponding several of aggregate statistics original ETL scheme customize true table.It generates
New customization fact table.
Preferably, component supports the virtual reorganization scheme of dimension by each customized detail dimension storage time stamp, passes through ratio
The virtual reorganization scheme of dimension is verified and updated to renewal time of ETL scheme stamp and the customized detail dimensions updating timestamp
The corresponding data for customizing true table.If user reconfigures ETL scheme simultaneously, ETL layout structure will also reset corresponding
Customized detail dimension timestamp, the virtual reorganization scheme of dimension to have had modified ETL scheme section can completely update.
How to be mapped between ETL scheme in order to further illustrate that dimension is virtually recombinated, Fig. 5 illustrates mapping
The rough schematic of expansion is the customized dimension of one group of three level in figure, listed in figure assets it is total-current assets-
Accounts receivable, wherein accounts receivable is customized detail dimension, one ETL scheme of virtual map, due to needing according to subject and
Client carries out reclassification to remaining sum, so analysis dimension selector has selected subject and client (if receivable class subject does not have item
Mesh is calculated, only scarabaeidae mesh, then only selects subject), also since it is desired that reclassification, balance type have selected debit's ending balance,
And accounts receivable and the balance due of Deposit received belong to accounts receivable, so subject has selected the two level-one subjects, and
It loads parameter selection consideration percentage of shares and considers combined offset.The OLAP balance sheet namely provided is big shareholder's power
The consolidated accounts of beneficial part.
The BI product of mainstream has considerable part not support data permission to control, or needs to the permission of user accesses data
It just needs to define user by script in the Qlikview of script level exploitation realization, such as one of BI leading manufacturer addressable
Data permission.Based on this status, it is preferable that the present invention can provide data output by modes such as API or Web Service
Interface customizes true table to export.
On the one hand, as shown in Fig. 2, this system can provide data permission control member, it is authorized addressable only to export user
Data: data model, organization, analysis dimension (such as client, supplier, department) etc. permissions, BI system pass through interface
User name is returned, according to the user data authority of passback before output, only exporting the user and having authorized allows to access interface routine
Data.
More importantly BI call data access interface, before output data, the interface can verify ETL scheme with
Whether the corresponding renewal time stamp for customizing true table of the virtual reorganization scheme of dimension is consistent with pretreatment ETL timestamp, if inconsistent,
It calls customization ETL component to update and customizes true table.To avoid planned dispatching or the slow caused business of update is manually specified
The non-current data shown in intelligence.It is clear that if the product of BI and ETL as a whole exists, only in offer
Portion's access interface or method.
Fig. 6 is a kind of second embodiment of business intelligence ETL method, and this method can be used for the ETL of single ERP data source:
Step 601: obtaining the analysis dimension (actual number of analysis dimension type or some analysis dimension of ERP data source
According to or the two generate simultaneously), in case step 602 in ETL layout structure selection.
Step 602: user can pass through the 1- in selector selection analysis dimension, factual data, loading environment, load parameter
4 projects, specific embodiment are shown in one embodiment carefully stating for attached drawing 4.
Step 603: checking that ETL configures the true table of corresponding customization and whether there is, customize the fact if there is no then calling
Table layout structure.
Step 604: customizing analysis dimension and factual data building customization thing that true table layout structure is specified according to user
Real table.
Step 605: when ETL scheme is saved by modification and come into force, component then calls the true table resetting component of customization.
Step 606: the analysis dimension and factual data that customization ETL component is specified according to user are extracted from ERP data source
Data, and data are updated to the true table of customization.
After step 607:ETL scheme changes, the corresponding fact table that customizes cannot be true by customization only according to update of time stamp
Table resets component refactoring and customizes true table, and calls customization ETL component.
In order to keep description of the invention brief and concise, the embodiment of the present invention is only to finance mould most common in ERP product
Block is described and illustrates, it should be apparent that, packaging method described in the invention (in addition to OLAP financial statement) is same suitable
For the other systems module of ERP product, including but not limited to: supply chain, production system, COST system, budgeting system, manpower
Resource system.
By above-mentioned two embodiment it is found that above description is the embodiment of the present invention and the technical principle used,
Those skilled in the art is clearly understood that the present invention, and can be by way of programming encapsulation, with software product, interface
Or the mode of service realizes the present invention, or is modified based on the present invention or modification.So those skilled in the art incite somebody to action this
Method and system described in inventing carry out formal modification or conception under this invention makes a change, generated function
When can act on the spirit still covered without departing from specification and attached drawing, protection scope of the present invention should be belonged to.
Claims (10)
1. a kind of packaging method of business intelligence ETL, it is characterised in that: include the following steps,
S1, the selected ERP data source of analysis and extraction and analysis dimension go analysis dimension if multiple ERP data sources
It is overlapped simultaneously；The analysis dimension is according to the actual situation in analysis dimension type or analysis dimension data the two at least one
It is a；
S2, alternatively parameter selects to user and saves as ETL scheme to the analysis dimension for generating S1, point provided according to S1
It is different to analyse dimensional properties, that is, at least one in analysis dimension type or analysis dimension data the two is provided；According to the actual situation,
If it also has multiple factual data types, factual data type can alternatively parameter be supplied to user's selection simultaneously simultaneously
It is saved in ETL scheme together；
S3, the analysis dimension based on the saved ETL scheme of user in step S2 and the building of factual data type generate data cube
Body, and save into data warehouse, each ETL scheme has and only corresponds to a data cube, for subsequent calls.
2. method according to claim 1, it is characterised in that: in the step S2,
The calculating standard conduct of factual data can also be provided in addition to selecting the analysis dimension, factual data type parameter in user
Parameter is selected to user；
User can modify above-mentioned ETL scheme at any time and will reconstruct and update according to new departure when new departure saves or scheduled
Data cube.
3. method according to claim 1, it is characterised in that: in the step S3,
It saves in data cube to data warehouse, according to the characteristic of ERP data source, creation analysis dimension and factual data
Storing framework is specific in order to data storage:
The ERP data source refers to the database of the various information management system of enterprises and institutions, the information management system packet
Include financial system, supply chain, human resources, the manufacturing, COST system；
The storage unit of equivalent amount is generated according to the corresponding organization's number of ERP data source, each storage unit includes storage
Several consistent dimension tables of structure and true table, that is, the number of columns and attribute of the table of the same property of all storage units
It is the same；
User can modify ERP data source at any time, and after the adjustment of ERP data source or when ETL scheme is scheduled, the storing framework is by basis
New ERP data source is rebuild.
4. method according to claim 1, it is characterised in that: further include a data conversion step after in the step S3
S4: dimension virtually recombinates, and is used for zero code recombination analysis dimension of user, i.e. it is customized can to carry out classification dimension by user, and by S2
Several ETL schemes of middle configuration map to the detail dimension in customized classification dimension, will when scheme saves or is scheduled
Data cube is constructed and updated according to the dimension in multistage customized classification dimension and corresponding ETL scheme, shows work for BI
Tool calls.
5. method according to claim 4, it is characterised in that: for financial analysis, in dimension map, can refer to simultaneously
Whether fixed each customized detail dimension deducts item, and to support financial statement olap analysis, the scheme that dimension virtually recombinates is supported
It is arranged according to the report item of financial statement, the Partial Elements of a corresponding report of scheme or a report, specifically
Include:
The financial statement data cube that can support olap analysis, the finance are directly generated based on the virtual reorganization scheme of the dimension
Report is the consolidated statement of the individual statements of individual enterprise, several enterprises, or data are offset in load, become consolidated accounts.
6. a kind of package system of business intelligence ETL, it is characterised in that: including,
ETL component is pre-processed, for analyzing specified N number of ERP data source, and generates analysis dimension type or analysis number of dimensions
According at least one in the two, when if multiple ERP data sources, the analysis dimension type or analysis dimension data should be carried out
Duplicate removal merges；Wherein, N is the integer more than or equal to 1；
ETL layout structure, for showing that the pretreatment ETL component merges the analysis dimension type generated or analysis number of dimensions
According to encapsulation supports user configuration analysis dimension type or analyzes the selector of dimension data to increase newly, modify and store client
Change ETL scheme；According to the actual situation, if it also has multiple factual data types, factual data type can conduct simultaneously
Selection parameter is supplied to user and selects and be saved in customization ETL scheme together；
True table layout structure is customized, generates data cube, the side ETL according to the customization ETL scheme constructs of ERP user configuration
Case and data cube correspond；This sentences database table that is, customizes true table as data cube, or with T-SQL language
Sentence, view alternate data library table are as data cube；
Customization ETL component generates according to the customization ETL scheme constructs of ERP user configuration and customizes true table or T-SQL language
Sentence, view.
7. system according to claim 6, it is characterised in that:
The ETL layout structure may also display load parameter and select to user；
ERP user can modify used ETL scheme, and save the program, so that subsequent ETL layout structure calls and modifies
And it customizes true table resetting component and the true table of corresponding customization is rebuild, and call customization ETL component.
8. system according to claim 6, it is characterised in that: further include that dimension virtually recombinates component, so that user
It is able to carry out multistage customized dimension setting, and gives several ETL scheme virtual maps to configured detail dimension, specific function
Can include:
Several specified ETL schemes can be distinguished virtual map to wherein the one of user configuration according to analysis demand by user
In customized detail dimension in the virtual reorganization scheme of dimension, the cured dimension membership of recombination ERP system reconstructs new dimension
Degree, and the condition and parameter that are loaded referring to data in ETL scheme, complete customized analysis dimension；
For financial analysis, the classification dimension of different levels is set as by each report element by financial statement, and
Each customized detail dimension is arranged whether to deduct item, realizes olap analysis financial statement.
9. the system according to claim 6 or 8, it is characterised in that: further include a data warehouse frame layout structure, a number
True table resetting component and a data permission control member are customized according to warehouse frame resetting component, one,
The data warehouse frame layout structure, for analyzing the data structure characteristics of selected ERP data source, and according to characteristic
Creation analysis dimension storing framework generates the storage unit of equivalent amount according to the corresponding organization's number of ERP data source, each
Storage unit includes several consistent dimension tables of storage organization and true table；
The data warehouse frame resets component, when changing for ERP data source, reconstructs data warehouse storage frame；
The true table of the customization resets component, for when user ETL scheme or the virtual reorganization scheme of dimension change, reconstruct to be fixed
Make true table；
The data permission control member authorizes, only output when customizing true table for exporting and arrive BI demonstration tool according to user
The fact that authorized data.
10. system according to claim 9, it is characterised in that: the data warehouse frame layout structure also has as follows
Function:
The database table structure for assessing the database on specified database server identifies ERP according to the storage organization feature of database
Systems provider and product type, it is automatic to construct ERP data source list, in case ERP user configuration；
The ERP data source refers to the various information Management System Data library of enterprises and institutions, and above- mentioned information management system includes
Financial system, supply chain, human resources, the manufacturing, COST system；
For the different ERP system products of each ERP system supplier, the data of the mating product in pretreatment ETL component are called
ETL method carries out data prediction.
Applications Claiming Priority (2)
| Application Number | Priority Date | Filing Date | Title |
|---|---|---|---|
| CN2017101892391 | 2017-03-27 | ||
| CN201710189239 | 2017-03-27 |
Publications (2)
| Publication Number | Publication Date |
|---|---|
| CN106991183A CN106991183A (en) | 2017-07-28 |
| CN106991183B true CN106991183B (en) | 2019-09-06 |
Family
ID=59415907
Family Applications (1)
| Application Number | Title | Priority Date | Filing Date |
|---|---|---|---|
| CN201710227461.6A Active CN106991183B (en) | 2017-03-27 | 2017-04-10 | A kind of packaging method and system of business intelligence ETL |
Country Status (1)
| Country | Link |
|---|---|
| CN (1) | CN106991183B (en) |
Families Citing this family (12)
| Publication number | Priority date | Publication date | Assignee | Title |
|---|---|---|---|---|
| CN107358376A (en) * | 2017-08-25 | 2017-11-17 | 袁也 | Corporation finance system and method based on performance management and financial analysis |
| US11593402B2 (en) * | 2017-09-29 | 2023-02-28 | Oracle International Corporation | System and method for enabling multiple parents with weights in a multidimensional database environment |
| CN107633094B (en) * | 2017-10-11 | 2020-12-29 | 北信源系统集成有限公司 | Method and device for data retrieval in cluster environment |
| CN107943863B (en) * | 2017-11-09 | 2021-09-28 | 北京许继电气有限公司 | Agile modeling method and system based on business intelligence |
| CN110209422B (en) * | 2018-05-09 | 2021-08-27 | 腾讯科技（深圳）有限公司 | Service processing method, computer equipment and client |
| CN110069561A (en) * | 2019-04-29 | 2019-07-30 | 金瓜子科技发展（北京）有限公司 | Account acquisition methods, system, electronic equipment and computer-readable medium |
| CN112488580A (en) * | 2020-12-18 | 2021-03-12 | 江苏苏宁云计算有限公司 | Intelligent dynamic dimension ordering method and system based on multi-dimensional characteristic parameters |
| CN112764788B (en) * | 2021-01-19 | 2023-06-16 | 南京大学 | Software dynamic update hot patch synthesis method based on program source code slice recombination |
| CN113254546A (en) * | 2021-05-24 | 2021-08-13 | 北京无线电测量研究所 | Supply chain management and control system and method and electronic equipment |
| CN113361126A (en) * | 2021-06-24 | 2021-09-07 | 浪潮软件科技有限公司 | Business modeling analysis method based on big data |
| CN117273400A (en) * | 2023-11-21 | 2023-12-22 | 领先未来科技集团有限公司 | Enterprise resource planning intelligent merging and upgrading early warning method and system |
| CN117785984A (en) * | 2024-02-28 | 2024-03-29 | 广州思迈特软件有限公司 | Data extraction method, device, electronic equipment and storage medium |
Citations (3)
| Publication number | Priority date | Publication date | Assignee | Title |
|---|---|---|---|---|
| CN101710280A (en) * | 2009-11-05 | 2010-05-19 | 金蝶软件（中国）有限公司 | Packaging method and devices of BI product members |
| CN103093322A (en) * | 2013-02-21 | 2013-05-08 | 用友软件股份有限公司 | System and method for impromptu analyzing business data |
| CN105787059A (en) * | 2016-02-29 | 2016-07-20 | 四川长虹电器股份有限公司 | Data warehouse based financial data integration method |
-
2017
- 2017-04-10 CN CN201710227461.6A patent/CN106991183B/en active Active
Patent Citations (3)
| Publication number | Priority date | Publication date | Assignee | Title |
|---|---|---|---|---|
| CN101710280A (en) * | 2009-11-05 | 2010-05-19 | 金蝶软件（中国）有限公司 | Packaging method and devices of BI product members |
| CN103093322A (en) * | 2013-02-21 | 2013-05-08 | 用友软件股份有限公司 | System and method for impromptu analyzing business data |
| CN105787059A (en) * | 2016-02-29 | 2016-07-20 | 四川长虹电器股份有限公司 | Data warehouse based financial data integration method |
Non-Patent Citations (1)
| Title |
|---|
| ERP与商业智能整合应用的研究;冯瑞芳;《中国优秀硕士学位论文全文数据库 信息科技辑》;20060715(第7期);第28页、第39-41页、第46-49页 |
Also Published As
| Publication number | Publication date |
|---|---|
| CN106991183A (en) | 2017-07-28 |
Similar Documents
| Publication | Publication Date | Title |
|---|---|---|
| CN106991183B (en) | A kind of packaging method and system of business intelligence ETL | |
| US10853387B2 (en) | Data retrieval apparatus, program and recording medium | |
| Măruşter et al. | Redesigning business processes: a methodology based on simulation and process mining techniques | |
| Paim et al. | DWARF: An approach for requirements definition and management of data warehouse systems | |
| CN101794226B (en) | Service software construction method and system adapting to multiple business abstraction levels | |
| Tannock et al. | Data-driven simulation of the supply-chain—Insights from the aerospace sector | |
| US20070027919A1 (en) | Dispute resolution processing method and system | |
| US20070276755A1 (en) | Systems and methods for assignment generation in a value flow environment | |
| US20120150820A1 (en) | System and method for testing data at a data warehouse | |
| CN107924406A (en) | Selection is used for the inquiry performed to real-time stream | |
| US20110040801A1 (en) | System and methods for generating manufacturing data objects | |
| CN110717320A (en) | Form/report designer and method suitable for multiple platforms and information management system | |
| CN104375943A (en) | Embedded software black-box test case generation method based on static models | |
| US20200311657A1 (en) | Supply chain optimization and inventory management system | |
| CN104102670A (en) | Performance indicator analytical framework | |
| US20130124265A1 (en) | Enterprise System/Process Modeling System and Method | |
| Danilczuk et al. | Computer-aided material demand planning using ERP systems and business intelligence technology | |
| CN113723822A (en) | Power supply service data management system | |
| CN104541297A (en) | Extensibility for sales predictor (SPE) | |
| Bruzzone et al. | An application methodology for logistics and transportation scenarios analysis and comparison within the retail supply chain | |
| US20150363711A1 (en) | Device for rapid operational visibility and analytics automation | |
| US20150120397A1 (en) | General enterprise modeling system and methodology for constructing enterprise applications | |
| CN107451846A (en) | The management method and managing device of business rule | |
| US20140149186A1 (en) | Method and system of using artifacts to identify elements of a component business model | |
| Dumitriu | Modelling and simulation software solutions as a premise for enhancing processes’ quality and business overall value |
Legal Events
| Date | Code | Title | Description |
|---|---|---|---|
| PB01 | Publication | ||
| PB01 | Publication | ||
| SE01 | Entry into force of request for substantive examination | ||
| SE01 | Entry into force of request for substantive examination | ||
| GR01 | Patent grant | ||
| GR01 | Patent grant |