CN106201539A - A kind of method realizing accounting item dynamic calculation in Star Model - Google Patents
A kind of method realizing accounting item dynamic calculation in Star Model Download PDFInfo
- Publication number
- CN106201539A CN106201539A CN201610566160.1A CN201610566160A CN106201539A CN 106201539 A CN106201539 A CN 106201539A CN 201610566160 A CN201610566160 A CN 201610566160A CN 106201539 A CN106201539 A CN 106201539A
- Authority
- CN
- China
- Prior art keywords
- computing formula
- factor
- sql
- star model
- dynamic calculation
- 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.)
- Pending
Links
Classifications
-
- G—PHYSICS
- G06—COMPUTING; CALCULATING OR COUNTING
- G06F—ELECTRIC DIGITAL DATA PROCESSING
- G06F8/00—Arrangements for software engineering
- G06F8/30—Creation or generation of source code
- G06F8/31—Programming languages or programming paradigms
Landscapes
- Engineering & Computer Science (AREA)
- Software Systems (AREA)
- General Engineering & Computer Science (AREA)
- Theoretical Computer Science (AREA)
- Computing Systems (AREA)
- Physics & Mathematics (AREA)
- General Physics & Mathematics (AREA)
- Financial Or Insurance-Related Operations Such As Payment And Settlement (AREA)
Abstract
The invention discloses a kind of method realizing accounting item dynamic calculation in Star Model, it realizes process and is: be first directed to computing formula, will computing formula manually or the mode made a report on imports in data base；Then extract and calculate the factor, generate target section purpose computation sequence according to this calculating factor；According to foregoing, generate the sql like language of target subject calculating and store.The a kind of of the present invention realizes the method for accounting item dynamic calculation compared with prior art in Star Model, and the formula provided according to accounting event personnel without developer writes SQL, then by configurator, generates the SQL process logic meeting Star Model.It is particularly suitable in data warehouse financial data fairground, has the calculative sight of a large amount of accounting item, can accomplish to reduce development amount, accelerate ETL exploitation and test process.
Description
Technical field
The present invention relates to field of computer technology, specifically a kind of in Star Model, realize accounting item dynamically count
The method calculated.
Background technology
In prior art, finance, accounting, in business administration, especially play vital effect in large enterprise's management.
In Construction of Data Warehouse, Data Mart or data analysis that finance are relevant are indispensable parts.But financial analysis
Often relate to a large amount of subject need to calculate, so can bring huge workload to developer, and the cycle researched and developed equally can
The work efficiency causing accounting in enterprise reduces, and based on this, existing offer one realizes accounting item in Star Model and dynamically counts
The method calculated, the subject computing formula that this method can provide according to business personnel, by configuring SQL generation logic, self-service batch
Amount generates SQL.Enhance Procedure Haleness, decrease exploitation, test job amount.
Summary of the invention
The technical assignment of the present invention is for above weak point, it is provided that a kind of accounting item that realizes in Star Model is moved
The method that state calculates.
A kind of method realizing accounting item dynamic calculation in Star Model, it realizes process and is:
It is first directed to computing formula, will computing formula mode that is manual or that make a report on import in data base；
Then extract and calculate the factor, generate target section purpose computation sequence according to this calculating factor；
According to foregoing, generate the sql like language of target subject calculating and store.
The computing formula imported is made up of target subject code and expression formula, i.e. computing formula is taken advantage of by subject code, plus-minus
Divided-by symbol combination in any.
Use regular expression to extract the calculating factor in computing formula, and the calculating factor extracted is updated number
According in storehouse；Then use recursive function, combining target subject code and calculate the factor, determine computing formula computation levels and
Sequentially.
Generating subject calculating sql like language process is: reads subject code, calculate the factor, computing formula, passes through regular expressions
SQL fragment assembly is become one to have two-layer nested structure and complete S QL supporting ETL instrument to call, ETL instrument here by formula
Including Informatica Powercenter, Kettle instrument.
Compared to the prior art a kind of of the present invention realize the method for accounting item dynamic calculation in Star Model, has
Following beneficial effect:
A kind of method realizing accounting item dynamic calculation in Star Model of the present invention, can calculate public affairs according to subject
Formula is extracted subject and is calculated the factor；Factor generation section purpose computation sequence can be calculated according to the subject extracted；The method is based on number
According to warehouse Star Model, decrease ETL complexity；The SQL of generation standard, can be by storing process, Informatica
Other ETL instruments such as Powercenter call, and the method is easily maintained, convenient enforcement, practical, applied widely, easily
In popularization.
Detailed description of the invention
Below in conjunction with specific embodiment, the invention will be further described.
The present invention provides a kind of method realizing accounting item dynamic calculation in Star Model, real based on data model
Execute, and data model complies fully with Star Model framework, be made up of a metric and some dimensions.
It implements process:
It is first directed to computing formula, will computing formula mode that is manual or that make a report on import in data base；
Then extract and calculate the factor, generate target section purpose computation sequence according to this calculating factor；
According to foregoing, generate the sql like language of target subject calculating and store.
The computing formula imported is made up of target subject code and expression formula, i.e. computing formula is taken advantage of by subject code, plus-minus
Divided-by symbol combination in any.
Use regular expression to extract the calculating factor in computing formula, and the calculating factor extracted is updated number
According in storehouse；Then use recursive function, combining target subject code and calculate the factor, determine computing formula computation levels and
Sequentially.
Generating subject calculating sql like language process is: reads subject code, calculate the factor, computing formula, passes through regular expressions
SQL fragment assembly is become one to have two-layer nested structure and complete S QL supporting ETL instrument to call, ETL instrument here by formula
Including Informatica Powercenter, Kettle instrument.
Embodiment:
The data warehouse model of design financial analysis.
This model includes some dimension tables and a true table:
| Table name | Table type | Core field |
| Organizational structure table | Dimension table | Mechanism ID, Institution Code, organization names, great Qu, member company |
| Code of Account table | Dimension table | Subject ID, Code of Account, Course in English, unit, ratio |
| Product table | Dimension table | Product IDs, product coding, product father node coding, name of product |
| Channel table | Dimension table | Channel ID, channel coding, channel father node coding, channel title |
| Scene table | Dimension table | Scene ID, coding, title |
| View table | Dimension table | View ID, coding, title |
| Currency type table | Dimension table | Currency type ID, coding, title |
| Financial analysis table | True table | Organizational structure ID, subject ID, view view ID, the amount of money |
Financial affair work personnel are needed to provide subject computing formula, including subject code and computing formula.Dynamic calculation table is tied
Structure is as follows:
Update dynamic calculation table, calculating section purpose is set and calculates the factor
Some algorithm is as follows:
//to_dy_calculat: this table is computing formula table
Update to_dy_calculat t1set t1.factors=(select listagg (factors, ' #')
within group(order by rownum)from(select distinct regexp_substr(formula,'A[0-
9]{4}',1,level)as factors from to_dy_calculat t2
connect by regexp_substr(formula,'A[0-9]{4}',1,level)is not null and
T1.formula=t2.formula) T)；
Use level recursive function, determine section's purpose computation levels:
A, use regular expressions further, generate the SQL that dynamic calculation is called:
So far, the SQL called required for dynamic calculation is generated.These SQL can transfer to Informatica
The main flow ETL instruments such as Powercenter, Kettle call.
The formula that the present invention provides according to accounting event personnel without developer writes SQL, then by configurator,
Generate the SQL process logic meeting Star Model.It is particularly suitable in data warehouse financial data fairground, has a large amount of accounting item
Calculative sight, can accomplish to reduce development amount, accelerates ETL exploitation and test process.
The present invention, in the middle of data warehouse financial analysis module data processing procedure, can be significantly reduced exploitation and test
Workload, is greatly improved the maintainability of ETL program.
By detailed description of the invention above, described those skilled in the art can readily realize the present invention.But should
Working as understanding, the present invention is not limited to above-mentioned detailed description of the invention.On the basis of disclosed embodiment, described technical field
Technical staff can the different technical characteristic of combination in any, thus realize different technical schemes.
In addition to the technical characteristic described in description, it is the known technology of those skilled in the art.
Claims (4)
1. the method realizing accounting item dynamic calculation in Star Model, it is characterised in that it realizes process and is:
It is first directed to computing formula, will computing formula mode that is manual or that make a report on import in data base；
Then extract and calculate the factor, generate target section purpose computation sequence according to this calculating factor；
According to foregoing, generate the sql like language of target subject calculating and store.
A kind of method realizing accounting item dynamic calculation in Star Model the most according to claim 1, its feature exists
In, the computing formula of importing is made up of target subject code and expression formula, and i.e. computing formula is by subject code, addition subtraction multiplication and division symbol
Combination in any.
A kind of method realizing accounting item dynamic calculation in Star Model the most according to claim 1, its feature exists
In, use regular expression to extract the calculating factor in computing formula, and the calculating factor extracted is updated data base
In；Then recursive function is used, combining target subject code and calculate the factor, determine the computation levels of computing formula and suitable
Sequence.
A kind of method realizing accounting item dynamic calculation in Star Model the most according to claim 1, its feature exists
In, generating subject calculating sql like language process is: reads subject code, calculate the factor, computing formula, will by regular expression
SQL fragment assembly becomes one to have two-layer nested structure and complete S QL supporting ETL instrument to call, and ETL instrument here includes
Informatica Powercenter, Kettle instrument.
Priority Applications (1)
| Application Number | Priority Date | Filing Date | Title |
|---|---|---|---|
| CN201610566160.1A CN106201539A (en) | 2016-07-18 | 2016-07-18 | A kind of method realizing accounting item dynamic calculation in Star Model |
Applications Claiming Priority (1)
| Application Number | Priority Date | Filing Date | Title |
|---|---|---|---|
| CN201610566160.1A CN106201539A (en) | 2016-07-18 | 2016-07-18 | A kind of method realizing accounting item dynamic calculation in Star Model |
Publications (1)
| Publication Number | Publication Date |
|---|---|
| CN106201539A true CN106201539A (en) | 2016-12-07 |
Family
ID=57493055
Family Applications (1)
| Application Number | Title | Priority Date | Filing Date |
|---|---|---|---|
| CN201610566160.1A Pending CN106201539A (en) | 2016-07-18 | 2016-07-18 | A kind of method realizing accounting item dynamic calculation in Star Model |
Country Status (1)
| Country | Link |
|---|---|
| CN (1) | CN106201539A (en) |
Cited By (1)
| Publication number | Priority date | Publication date | Assignee | Title |
|---|---|---|---|---|
| CN110019385A (en) * | 2017-09-04 | 2019-07-16 | 北京国双科技有限公司 | The querying method and device of parameter |
Citations (5)
| Publication number | Priority date | Publication date | Assignee | Title |
|---|---|---|---|---|
| CN101187929A (en) * | 2007-12-05 | 2008-05-28 | 浪潮集团山东通用软件有限公司 | Formula definition assembly for flexible mathematical aiming at large data of database table |
| CN101377768A (en) * | 2008-09-09 | 2009-03-04 | 金蝶软件（中国）有限公司 | Method and system for calculating salary |
| CN101582138A (en) * | 2009-06-23 | 2009-11-18 | 用友软件股份有限公司 | Dynamic business processing system and method |
| CN105045576A (en) * | 2015-06-29 | 2015-11-11 | 用友优普信息技术有限公司 | Management method and apparatus for calculation formulae in business document |
| CN105447063A (en) * | 2014-09-30 | 2016-03-30 | 远光软件股份有限公司 | Database subject retrieval method and retrieval device thereof |
-
2016
- 2016-07-18 CN CN201610566160.1A patent/CN106201539A/en active Pending
Patent Citations (5)
| Publication number | Priority date | Publication date | Assignee | Title |
|---|---|---|---|---|
| CN101187929A (en) * | 2007-12-05 | 2008-05-28 | 浪潮集团山东通用软件有限公司 | Formula definition assembly for flexible mathematical aiming at large data of database table |
| CN101377768A (en) * | 2008-09-09 | 2009-03-04 | 金蝶软件（中国）有限公司 | Method and system for calculating salary |
| CN101582138A (en) * | 2009-06-23 | 2009-11-18 | 用友软件股份有限公司 | Dynamic business processing system and method |
| CN105447063A (en) * | 2014-09-30 | 2016-03-30 | 远光软件股份有限公司 | Database subject retrieval method and retrieval device thereof |
| CN105045576A (en) * | 2015-06-29 | 2015-11-11 | 用友优普信息技术有限公司 | Management method and apparatus for calculation formulae in business document |
Cited By (1)
| Publication number | Priority date | Publication date | Assignee | Title |
|---|---|---|---|---|
| CN110019385A (en) * | 2017-09-04 | 2019-07-16 | 北京国双科技有限公司 | The querying method and device of parameter |
Similar Documents
| Publication | Publication Date | Title |
|---|---|---|
| CN108280365B (en) | Data access authority management method, device, terminal device and storage medium | |
| US9672272B2 (en) | Method, apparatus, and computer-readable medium for efficiently performing operations on distinct data values | |
| US9619549B2 (en) | Reporting and summarizing metrics in sparse relationships on an OLTP database | |
| US20180210934A1 (en) | Systems and methods for interest-driven business intelligence systems including event-oriented data | |
| US7856416B2 (en) | Automated latent star schema discovery tool | |
| CN107704625A (en) | Fields match method and apparatus | |
| CN103577440B (en) | A kind of data processing method and device in non-relational database | |
| CN103748579B (en) | Data are handled in MapReduce frame | |
| CN106570008A (en) | Recommendation method and device | |
| WO2016134580A1 (en) | Data query method and apparatus | |
| CN108415964A (en) | Tables of data querying method, device, terminal device and storage medium | |
| CN103262076A (en) | Analytical data processing | |
| US10089354B2 (en) | Cardinality estimation of a join predicate | |
| US20230244653A1 (en) | Semantic compliance validation for blockchain | |
| CN112507098B (en) | Question processing method, question processing device, electronic equipment, storage medium and program product | |
| CN104036051A (en) | Database mode abstract generation method based on label propagation | |
| CN102819589A (en) | ETL (Extract Transform Load)-based data optimization method and equipment | |
| CN111125266A (en) | Data processing method, device, equipment and storage medium | |
| US10055421B1 (en) | Pre-execution query optimization | |
| Faisal et al. | Handling slowly changing dimensions in data warehouses | |
| CN108960672A (en) | The air control method, apparatus and computer readable storage medium of limit limit time | |
| CN106844320A (en) | A kind of financial statement integration method and equipment | |
| CN106201539A (en) | A kind of method realizing accounting item dynamic calculation in Star Model | |
| CN107066532A (en) | A kind of method and system for generating enterprise's transverse and longitudinal graph of a relation | |
| CN105574764A (en) | Intellectual property evaluation system |
Legal Events
| Date | Code | Title | Description |
|---|---|---|---|
| C06 | Publication | ||
| PB01 | Publication | ||
| C10 | Entry into substantive examination | ||
| SE01 | Entry into force of request for substantive examination | ||
| RJ01 | Rejection of invention patent application after publication |
Application publication date: 20161207 |
|
| RJ01 | Rejection of invention patent application after publication |