CN104731791A - Marketing analysis data market system - Google Patents

Abstract

The invention provides a marketing analysis data market system. The marketing analysis data market system comprises a data access layer, a data extraction module, a data conversion module, a data cleaning module, a log and alarm sending module and a data downloading module, a data packet of the data access layer contains office data, external data and service data, and models of the system includes a data logic model and a data physics module. Firstly, necessity in designing the marketing data market is analyzed and ETL data processing including noise data processing, data uniformity and data quality and the like is analyzed by discussing a data integration method, and various data sources can be reorganized and processed by a data transition tool. In addition, the physics model in the data market is realized according to the physic list structure of the logic model. Finally, application prospect of the data market in the marketing analysis is expected.

Description

Data Mart system is analyzed in a kind of market sale

Technical field

The present invention relates to business datum analytic system, particularly relate to a kind of convenient business datum is transformed into rapidly and market is continued to optimize, promote the sales analysis Data Mart system of the responding ability to turn of the market.

Background technology

At present, how business datum is transformed into rapidly market, cognition to operation situation in enterprise, thus auxiliary enterprises decision-making, continue to optimize decision-making management flow process, promote the responding ability to turn of the market, become the problem that sales department is in the urgent need to address.Therefore need the department level data platform utilizing Data Mart Erecting and improving, integrate existing system data and external data, reflect the situation of market sale timely and effectively, for decision analysis support provides foundation.

But, mainly there is the problem of following several respects in market sale analytic system:

1, data scatter, exchanges data is too complicated

The subsystem of current on-line operation has multiple, and wherein most of subsystem software and hardware configuration is different, and ubiquity is for depositing respective management data and the local data base of historical data; And the exchange of source data between each built-in system is too complicated, period is also mingled with the exchanges data of carrying out with external data source.

Owing to lacking unified data interchange platform, the scattered shortage system management of data, define complicated exchanges data chain, once certain link goes wrong in chain, part system can be caused to be affected, data are inquired about in time and also can be lost efficacy thereupon.In addition data can not be completely shared at each Inter-System Information, use middle data also to fail to realize closed loop to utilize, cause between each database and there is mass of redundancy data, the consistance of data is poor, data volume is huge and unordered, had a strong impact on the collaborative of business to carry out, to the accuracy of sales analysis decision-making and ageing effect all very limited.

In order to obtain successfully under new race condition, business department must rely on the unification of the factor of sales related information and market competition information and information timely to a great extent, such as: client's buying, order, product information, degree of contention and marketing effectiveness etc.But only have these information not enough, business department also will be delivered to appropriate information in the hand of the intra-company relevant personnel.

2, form is static, and query performance difference is large

User cannot detect data in form to obtain more detailed information by interactive mode, and such as they can process Microsoft Office Excel pivot table.Although existing one group of predefine form is enough to for many users, more senior user needs to carry out direct queried access to database, to carry out interactive inquiry and access single purpose statement.But because current Database Systems are very complicated, therefore, this kind of user needs the cost plenty of time to grasp how to create effective query.

User, when operating database, because involved business datum amount is different, some inquiry will be caused only to need just can very rapidly return results a few second, and other inquiries needs a few minutes just can return results.

3, Aggregation Table be difficult to management, information be difficult to examination & verification

Shorten in query responding time in trial, technician generates several Aggregation Table in a database.Such as, they generate a kind of table monthly gathering sales volume.But, although these Aggregation Tables can significantly improve query performance, they generate for safeguarding that within a period of time these foundation structures shown but easily are destroyed and occur mistake.

Current database is mainly used as the data source of carrying out Large Copacity inquiry by business department.Then, then download data in Single Electron form, and spend the plenty of time to prepare data and process electrical form.Therefore, be difficult to prepare in whole department, examination & verification and the Sales Reports of administrative authority.

Meanwhile, service-user is difficult to some dedicated query of structure, to combine two relevant information sets (as sales volume and sell quota).This type of inquiry can take a large amount of database spaces.In addition, because these forms are very complicated, therefore user is reluctant to attempt these forms of amendment.

4, comprehensive analysis can be more weak

At present in existing system, substantially achieve the systemic-function of statistical query.Using statistics inquiry system, can be daily management business and provides analytical statement, and query function to a certain degree.But current statistical analysis system designs based on online transaction processing system (OLTP), the demand of the little portfolio of the current low level of main reflection, this design cannot provide powerful function support for the synthesis of data, analysis and synthesis, cannot dynamic reflection many levels, the information of many granularities; But, along with the raising of sales analysis management expectancy, the demand of statistics and inquiry is also improved constantly and is developed.Especially, higher level, be constantly suggested with the comprehensive inquiry statistical demand of analytical property.On the other hand, the development adjustment of market sale is needed to the forecast analysis of the science of carrying out.These demands, with current system architecture, cannot meet above-mentioned requirements.So setting up suitable data architecture platform, improve sales management comprehensive analytical capacity, is task very urgent in present sales management.

In sum, for the defect existed in prior art, a kind of market sale of special needs analyzes Data Mart system, to solve the deficiencies in the prior art.

Summary of the invention

The object of this invention is to provide a kind of market sale and analyze Data Mart system, by the necessity in analysis and designation sales data fairground, then by the discussion to data integrating method, analyze ETL data processing, comprise the problem such as consistance and the quality of data of noise data process, data, and realize reorganizing various data source and processing, to solve the deficiencies in the prior art by Data Migration Tools.

The technical scheme that the present invention adopts for its technical matters of solution is,

Data Mart system is analyzed in a kind of market sale:

This system module comprises: data access layer, data extraction module, data conversion module, data cleansing module, daily record and warning sending module, Data import module;

The data of data access layer include office data, external data, business datum;

Data extraction module includes the identical data source process of the Database Systems of depositing DW, data source, incremental update that DW Database Systems are different;

In data conversion module, inconsistent data conversion, the conversion of data granularity, the calculating of business rule are carried out to data;

Data cleansing module includes three major types: the data of incomplete data, mistake, the data of repetition;

Daily record is with daily record when warning sending module register system to run and send warning to system manager;

Data import module includes data encasement unit, Data import way selection unit, Data import unit in enormous quantities;

The model of this system comprises mathematical logic model and Data Physical model;

Mathematical logic model carries out analysis subject area, granularity distinguishing hierarchy, determines that data-splitting strategy, relation schema define;

Data Physical model includes storage organization unit, index policy unit, storage policy unit.

Further, the data of described data access layer include the office system data that office data mainly refers to sales and marketing department's door, these data divide electronic data and non-electronic data two kinds, with the data that electronic data mode is preserved, main finger electrical form, the data that the form such as database and word processing file is preserved, non-electronic data mainly refer to those files, the official documents such as notice, from the version of data, office data has plenty of the structural data represented with bivariate table case form, have plenty of with the structural data of word or file process representation of file, therefore the data structure in office data source is very complicated, this just gives the data pick-up of Data Mart, load and add very large difficulty, sometimes even need after artificial treatment, just can be loaded in Data Mart,

External data refer to those not for market sale department is operated, the data that have, control, the electronic form that these data have, if third party information service business is with Web Service mode XML data, having is non electronic version, as the relevant report file etc. that retail trader provides, the use difficulty of these data sources is roughly the same with processing mode and office data;

Business datum refers to that the transaction processing system there from running at present is collected, and is saved in the data of transaction processing system database, and to business datum, which data of Water demand should be loaded in Data Mart.

Further, it is easy at design comparison that described data extraction module includes this kind of several source in the identical data source process of the Database Systems of depositing DW, DBMS (comprises SQL Server, Oracle) all can provide data basd link connection function, between DW database server and former operation system, set up direct linking relationship just can write Select statement and directly access;

This kind of data source of the data source that DW Database Systems are different generally also can be linked by the mode building database of ODBC, between Oracle and SQL Server, if can not link by building database, two kinds of modes can be had to complete, one is, by instrument, source data is exported to .txt or .xls file, and then these origin system files are imported in ODS, another method has been come by routine interface;

For the system that data volume is large in incremental update, increment extraction must be considered, generalized case, the time that market sale operation system meeting record traffic occurs, the mark of increment can be used as, first judge before each extraction to record the maximum time in ODS, then go in operation system database, to get all records being greater than this time according to this time.

Further, described data conversion module: inconsistent data conversion is in market sale analytic system, there is the inconsistent situation of data content in the data from different pieces of information source, this process just needing establishment one to integrate, and the data of the identical type of different business systems are unified;

The conversion of data granularity generally stores very detailed data in operation system, and the data in Data Mart are used to analysis, do not need very detailed data, generally, operation system data can be polymerized according to Data Mart granularity;

The calculating of business rule also exists different business rules in market sale analytic system, different data targets, these indexs are not that simple plus-minus just can complete sometimes, be stored in Data Mart, for analysis after needing this time in ETL process, these data targets to have been calculated.

Further, incomplete data in described data cleansing module are some due loss of learnings, as the title of supplier, the title of branch office, the area information disappearance of client, master meter can not mate with detail list in operation system, needs by this class data filtering out, writes different Excel file respectively submit to client by the content lacked, require completion in official hour, after completion, be then written to Data Mart;

Mainly operation system is not well established for the Producing reason of the data of mistake, the background data base that do not carry out judging writing direct after receiving input causes, have after such as numeric data defeated one-tenth full-shape numerical character, string data that a carriage return, date format are incorrect, the date crosses the border, these class data also will be classified, there is the problem of not meeting personally character can be found out by the mode writing SQL statement for being similar to before and after double byte character, data, then requiring that client extracts after operation system correction; This class mistake that date format incorrect or date crosses the border can cause ETL to run unsuccessfully, and this class mistake needs the mode of operation system database SQL to pick out, and gives business department and revises, extract after correction again;

The data problem repeated is more common in dimension table, all fields that records of the data of repetition is derived, then allows business department confirm and arrange.

Further, described daily record contains three classes with the log packet in warning sending module:

The first kind is implementation daily record, is the record often performing a step in ETL implementation, and the initial time of each step of record each run, have impact on how many row data, day-to-day account form;

Equations of The Second Kind is error log, when certain module error time, need write error daily record, records the time of at every turn makeing mistakes, the module of makeing mistakes and the information etc. of makeing mistakes;

3rd class daily record is overall daily record, only the record ETL start time, end time whether successful information;

Warning is sent in after ETL makes mistakes, and not only will write ETL and to make mistakes daily record but also will send warning to system manager, the mode sending warning has multiple, and conventional sends mail to system manager exactly, and encloses the information of makeing mistakes, and facilitates keeper to investigate mistake.

Further, in described Data import module:

Data encasement unit: because the data pick-up of Data Mart is analyzed in market sale, cleaning, load and need the longer time, therefore a volatile data base as data encasement district will be set when processing data, be specifically designed to data pick-up, cleaning and the operation loaded, can setting data extract in data encasement district, cleaning and the restart mechanism loaded, in the extraction of data, cleaning and loading procedure in, usually because the reason of system or some other unpredictable factor cause these movable failures, if after failure, restart the ample resources of waste system, for this reason, can setting data extract, cleaning and the monitoring mechanism loaded, dynamic monitoring is carried out to these activities, once failure, just can from unsuccessfully restarting, and need not start anew, as the extraction of a certain business datum, cleaning and loading needs 8 steps just can complete, when system completes 6 steps wherein, after entering the 7th step, load unsuccessfully, system is after restarting, just can restart in the 7th step, and need not start anew, for completing this mechanism, need the extraction of data, cleaning and loading activity are divided into some steps clearly, and when entering a certain step, retain current state,

Data import way selection unit: the mode of Data import generally considers batch processing, because the system resource that the loading activity of data relates to is more, need the processor of data source and Data Mart, internal memory and External memory equipment, and most of data source is used in transaction processing system, need for user provides real time service by day, therefore the Data import of Data Mart is often selected to carry out in festivals or holidays or night, and this coordinates with regard to needing the Data import process business processing relevant to other;

Data import unit in enormous quantities: the data source that market sale is analyzed to be had in Data Mart is prohibited to load for simple Large Volume Data, this just needs the technology adopting some special to process the loading of mass data, the use restricted problem of system resource is also related in mass data loading procedure, need the processor of data source and Data Mart simultaneously, the support of network and internal memory each side, and these precious resources can run into considerable restraint in the application, the loading that data in enormous quantities in Data Mart are analyzed in market sale realizes by adopting Data Replication Technology in Mobile, the reproduction technology of data can ensure the integrity constraint in data load process, the impact of the accident factors such as thrashing can not be subject to, and process can be optimized to the transport process of data.

Further, described mathematical logic mould:

Carry out analysis subject area: in Conceptual Model Design, we determine several basic subject area, but the method for designing of Data Mart is the process of a Stepwise Refinement, when designing, being generally next theme or once progressively completing several themes; So we must analyze the several basic theme territories determined in Conceptual Model Design step, select the subject area first will implemented in the lump; First topic territory institute is selected to want it is considered that it will enough greatly, to make this subject area can turn an applicable system into; It is also enough little, so that develop and implement quickly; If selected subject area very greatly and very complicated, we even can develop for its an one significant subset, in feedback procedure each time, all will carry out the analysis of subject area, the most crucial theme that Data Mart is analyzed in market sale is product sales analysis commercially;

Granularity distinguishing hierarchy a: major issue that will solve in Data Mart logical design is the granularity division level in determination data fairground, whether suitable granularity distinguishing hierarchy is directly have influence on data volume in Data Mart and the query type that is applicable to, when determining granularity level in Data Mart, need to consider some factors like this: the analysis type that accept, the minimum granularity of acceptable data and the data volume that can store, analyze in Data Mart in market sale, adopt the mode of double data granularity, the larger combined data of granularity is only retained to the sales data that the time is far away, recent sales data and combined data is preserved with low granularity data, so both can sell recent developments and carry out detail analysis, combined data can be utilized again to analyze sales trend,

Determine data-splitting strategy: in this step; select the standard of suitable Data Segmentation; main consideration following several respects factor: the actual conditions of data volume (and non-recorded line number), Data Analysis Services, simple and granularity division strategy etc., the size of data volume is the principal element determining whether to carry out Data Segmentation and how to split; The requirement of Data Analysis Services is the Main Basis selecting Data Segmentation standard, because Data Segmentation follows the object of Data Analysis Services to be closely connected; We also will consider that selected Data Segmentation standard should be natural, easy to implement: also will consider that the standard of Data Segmentation and granularity division level adapt to simultaneously;

Relation schema definition includes the design that the fact table model of Data Mart, the dimension table model of market sale analysis Data Mart are analyzed in market sale;

The fact table model of Data Mart is analyzed in market sale: after completing the Star Model design based on the market sale analytic system Data Mart of business intelligence, need with determining further in Data Mart, what kind of granularity data could meet the needs of managerial personnel to Data Mart sales analysis, generally those atomic datas obtained due to business processing are first considered in the design of Data Mart, because those atomic datas have height dimension structuring, true metric is trickleer, more there is atomicity, just can reflect the more fact definitely, therefore atomic data can provide dirigibility to greatest extent for administrative analysis, various forms of constraint can be accepted, and user can be presented to various possible form, meet the various inquiry needs of user at any time,

Market sale analyze Data Mart dimension table model design in include the date dimension, product dimension, retail trader dimension, area dimension, account title dimension, business department dimension.

The invention has the advantages that, the necessity in the present invention first analysis and designation sales data fairground, then by the discussion to data integrating method, analyze ETL data processing, comprise the problems such as noise data process, the consistance of data and the quality of data, and realize reorganizing various data source and processing by Data Migration Tools.On this basis, at conceptual model according to subject analysis needs, determine the multidimensional model of Data Mart.Wherein determine to be the theme with the sales analysis by product of enterprise in logical model, using client, product, time and area etc. as the dimension of Data Mart, adopt star-like and snowflake type data model to combine, granularity division level and the data-splitting strategy of each dimension is discussed.Then that the physics list structure set up according to logical model realizes in the physical model of Data Mart.Finally, the application prospect of Data Mart in market sale is analyzed is looked forward to.

Accompanying drawing explanation

The present invention is described in detail below in conjunction with the drawings and specific embodiments:

Fig. 1 is that the present invention proposes configuration diagram;

Embodiment

The technological means realized to make the present invention, creation characteristic, reaching object and effect is easy to understand, below in conjunction with diagram and specific embodiment, setting forth the present invention further.

See Fig. 1, Data Mart system is analyzed in a kind of market sale that the present invention proposes:

This system module comprises: data access layer, data extraction module, data conversion module, data cleansing module, daily record and warning sending module, Data import module;

The data of data access layer include office data, external data, business datum;

Data extraction module includes the identical data source process of the Database Systems of depositing DW, data source, incremental update that DW Database Systems are different;

In data conversion module, inconsistent data conversion, the conversion of data granularity, the calculating of business rule are carried out to data;

Data cleansing module includes three major types: the data of incomplete data, mistake, the data of repetition;

Daily record is with daily record when warning sending module register system to run and send warning to system manager;

Data import module includes data encasement unit, Data import way selection unit, Data import unit in enormous quantities;

The model of this system comprises mathematical logic model and Data Physical model;

Mathematical logic model carries out analysis subject area, granularity distinguishing hierarchy, determines that data-splitting strategy, relation schema define;

Data Physical model includes storage organization unit, index policy unit, storage policy unit.

Further, the data of described data access layer include the office system data that office data mainly refers to sales and marketing department's door, these data divide electronic data and non-electronic data two kinds, with the data that electronic data mode is preserved, main finger electrical form, the data that the form such as database and word processing file is preserved, non-electronic data mainly refer to those files, the official documents such as notice, from the version of data, office data has plenty of the structural data represented with bivariate table case form, have plenty of with the structural data of word or file process representation of file, therefore the data structure in office data source is very complicated, this just gives the data pick-up of Data Mart, load and add very large difficulty, sometimes even need after artificial treatment, just can be loaded in Data Mart,

External data refer to those not for market sale department is operated, the data that have, control, the electronic form that these data have, if third party information service business is with Web Service mode XML data, having is non electronic version, as the relevant report file etc. that retail trader provides, the use difficulty of these data sources is roughly the same with processing mode and office data;

Business datum refers to that the transaction processing system there from running at present is collected, and is saved in the data of transaction processing system database, and to business datum, which data of Water demand should be loaded in Data Mart.

Data in market sale, after extracting, clear up, changing, carry out fundamental analysis, and by Data import in data encasement region, with the data entering also to extract from other system, they are loaded in data encasement region in the lump.Data one are to preparing region, and market sale data have to pass through multiprogrammable process perhaps, but also need according to user's request, and filter unwanted data further, filtration duty also can perform to during data encasement region at Data import sometimes.After all data are processed into available form, then be assembled into the data of dimension table.

Also comprise the rear loading processing to data in ETL system, rear loading processing comprises the legacy data that backup exceedes Data Mart time window, sets up Aggregation Table and to database again permutation index, confirms the validity loading data recently simultaneously.

Further, it is easy at design comparison that described data extraction module includes this kind of several source in the identical data source process of the Database Systems of depositing DW, DBMS (comprises SQL Server, Oracle) all can provide data basd link connection function, between DW database server and former operation system, set up direct linking relationship just can write Select statement and directly access;

This kind of data source of the data source that DW Database Systems are different generally also can be linked by the mode building database of ODBC, between Oracle and SQL Server, if can not link by building database, two kinds of modes can be had to complete, one is, by instrument, source data is exported to .txt or .xls file, and then these origin system files are imported in ODS, another method has been come by routine interface;

For file type data source (.txt, xls), the assemblies such as the planar data source can served by the SSIS of SQL SERVER2005 and planar target import in ODS and go.

Data source in Sales Analysis System has the Excel file coming from business personnel greatly and provide, in routine duties, could show correct form content after business personnel needs to adjust some data, this just needs these data importing Data Mart systems.

Can show data read operation in XML file easily in fact by the XML Source control of SSIS instrument.In source data tool set, select XML data source component, then specify position corresponding to XML file, by arranging screening conditions, be polymerized the methods such as corresponding data item for target database and provide specification effective data.

For the system that data volume is large in incremental update, increment extraction must be considered, generalized case, the time that market sale operation system meeting record traffic occurs, the mark of increment can be used as, first judge before each extraction to record the maximum time in ODS, then go in operation system database, to get all records being greater than this time according to this time.

Further, described data conversion module: inconsistent data conversion is in market sale analytic system, there is the inconsistent situation of data content in the data from different pieces of information source, this process just needing establishment one to integrate, and the data of the identical type of different business systems are unified;

The conversion of data granularity generally stores very detailed data in operation system, and the data in Data Mart are used to analysis, do not need very detailed data, generally, operation system data can be polymerized according to Data Mart granularity;

The details of retail trader's product purchasing are stored in market sale operation system, sometimes can be divided into many data according to product category in an order and carry out record, if these data are all drawn in current data fairground, a lot of redundant data can be brought to Data Mart, bring serious impact can to the performance of Data Mart simultaneously.Therefore need to changing in units of sky in operation system, be polymerized after, be then stored in Data Mart and go.

The calculating of business rule also exists different business rules in market sale analytic system, different data targets, these indexs are not that simple plus-minus just can complete sometimes, be stored in Data Mart, for analysis after needing this time in ETL process, these data targets to have been calculated.

Managerial personnel as market sale department compare and pay close attention to the management state that sales volume reaches some retail traders, corresponding incentive measure can be taked proceed cooperation, to avoid retail trader to be seized by rival, thus ensure product core competitiveness in the market.

In market sale analytic system, the task of data cleansing filters those undesirable data, gives business department by the result of filtration simultaneously, is confirmed whether to filter out or extracts by after service unit correction again.In market sale analytic system, undesirable data mainly have the data of incomplete data, mistake and the data three major types of repetition.

Incomplete data are some due loss of learnings, as the title of supplier, the title of branch office, the area information disappearance of client, master meter can not mate with detail list in operation system, need by this class data filtering out, write different Excel file respectively by the content of disappearance to submit to client, require completion in official hour, after completion, be then written to Data Mart;

Mainly operation system is not well established for the Producing reason of the data of mistake, the background data base that do not carry out judging writing direct after receiving input causes, have after such as numeric data defeated one-tenth full-shape numerical character, string data that a carriage return, date format are incorrect, the date crosses the border, these class data also will be classified, there is the problem of not meeting personally character can be found out by the mode writing SQL statement for being similar to before and after double byte character, data, then requiring that client extracts after operation system correction; This class mistake that date format incorrect or date crosses the border can cause ETL to run unsuccessfully, and this class mistake needs the mode of operation system database SQL to pick out, and gives business department and revises, extract after correction again;

The data problem repeated is more common in dimension table, all fields that records of the data of repetition is derived, then allows business department confirm and arrange.

Data cleansing in market sale analytic system is a process repeatedly, can not complete in a short time, only have and constantly discover problems and solve them.For whether filtering, whether revising General Requirements business department personnel confirm; For the data filtered out, write Excel file or filtering data is write tables of data, regularly send the mail of filtering data at the initial stage of ETL exploitation to business personnel, impel them to correct mistakes as soon as possible, simultaneously also can as the foundation of verification msg in future.Data cleansing it should be noted that not fallen by useful data filtering, conscientiously verifies for each filtering rule, and after wanting business personnel to confirm.

To the cleaning process of customer information in market sale analytic system; when extracting the customer information in operation system and external data; owing to there is customer information in Data Mart; first the data in source data and Data Mart are mated completely; if mate unsuccessful; then adopt the mode of fuzzy matching, as ignored ".; :-" ' &/@! () <> [] { } | #*^% " these pointing informations etc.; similarity is set simultaneously; as 70%; the data of fuzzy matching merged with the data of mating completely; then upgrade the customer information in Data Mart; for the unsuccessful customer information of fuzzy matching, go by being inserted into after packet aggregation in customer information table.

In the process of implementation, because the quality of data, network reason can cause tasks carrying failure, this just needs to carry out record to error message ETL in market sale analytic system, and sends to relevant persons in charge to process log information,

Daily record contains three classes with the log packet in warning sending module:

The first kind is implementation daily record, is the record often performing a step in ETL implementation, and the initial time of each step of record each run, have impact on how many row data, day-to-day account form;

Equations of The Second Kind is error log, when certain module error time, need write error daily record, records the time of at every turn makeing mistakes, the module of makeing mistakes and the information etc. of makeing mistakes;

3rd class daily record is overall daily record, only the record ETL start time, end time whether successful information;

Warning is sent in after ETL makes mistakes, and not only will write ETL and to make mistakes daily record but also will send warning to system manager, the mode sending warning has multiple, and conventional sends mail to system manager exactly, and encloses the information of makeing mistakes, and facilitates keeper to investigate mistake.

Further, in described Data import module:

Data encasement unit: because the data pick-up of Data Mart is analyzed in market sale, cleaning, load and need the longer time, therefore a volatile data base as data encasement district will be set when processing data, be specifically designed to data pick-up, cleaning and the operation loaded, can setting data extract in data encasement district, cleaning and the restart mechanism loaded, in the extraction of data, cleaning and loading procedure in, usually because the reason of system or some other unpredictable factor cause these movable failures, if after failure, restart the ample resources of waste system, for this reason, can setting data extract, cleaning and the monitoring mechanism loaded, dynamic monitoring is carried out to these activities, once failure, just can from unsuccessfully restarting, and need not start anew, as the extraction of a certain business datum, cleaning and loading needs 8 steps just can complete, when system completes 6 steps wherein, after entering the 7th step, load unsuccessfully, system is after restarting, just can restart in the 7th step, and need not start anew, for completing this mechanism, need the extraction of data, cleaning and loading activity are divided into some steps clearly, and when entering a certain step, retain current state,

Data import way selection unit: the mode of Data import generally considers batch processing, because the system resource that the loading activity of data relates to is more, need the processor of data source and Data Mart, internal memory and External memory equipment, and most of data source is used in transaction processing system, need for user provides real time service by day, therefore the Data import of Data Mart is often selected to carry out in festivals or holidays or night, and this coordinates with regard to needing the Data import process business processing relevant to other;

Data import unit in enormous quantities: the data source that market sale is analyzed to be had in Data Mart is prohibited to load for simple Large Volume Data, this just needs the technology adopting some special to process the loading of mass data, the use restricted problem of system resource is also related in mass data loading procedure, need the processor of data source and Data Mart simultaneously, the support of network and internal memory each side, and these precious resources can run into considerable restraint in the application, the loading that data in enormous quantities in Data Mart are analyzed in market sale realizes by adopting Data Replication Technology in Mobile, the reproduction technology of data can ensure the integrity constraint in data load process, the impact of the accident factors such as thrashing can not be subject to, and process can be optimized to the transport process of data.

Data model be design data fairground, the prerequisite of carrying out Data Integration operation.Data model is to the reflection of real things and abstract, more clearly can reflect objective world.Traditional OLTP system is the model according to being used for setting up it.That is, OLTP system is application oriented.And Data Mart is subject-oriented, generally carry out modeling according to theme.Theme is a standard of data being carried out sorting out at higher level, the analysis field that the basic correspondence one of each theme is macroscopical, meets the needs of this field decision-making.Consider from the angle of whole market Sales Analysis System herein, its data model is no longer towards indivedual application, but towards the theme of whole market Sales Analysis System, as being determine according to the requirement analyzed the extraction of the themes such as retail trader, product, area, time, market sale.

The design that Data Mart is analyzed in market sale is not an easy to do thing.This needs experience one from actual environment to abstract model, the process from abstract model to specific implementation.Complete this process, various different data model must be relied on.In from reality to abstract process, need the support relying on conceptual model, the decision analysis environment of reality is abstracted into a conceptual data model.Then, by this conceptual model logically.Finally, then the physical model of logical model to Data Mart is transformed, once complete the physical model of Data Mart, just can say that the specific implementation of Data Mart has had reliable design proposal.

Logic modeling is the important ring that market sale analyzes in Data Mart implementation process, because it directly can reflect the demand of market sale department, has important directive function to the physical implementation of system simultaneously.The data blueprint of enterprise is really sketched the contours of by entity and relation.The work carried out in this step mainly contains:

Carry out analysis subject area: in Conceptual Model Design, we determine several basic subject area, but the method for designing of Data Mart is the process of a Stepwise Refinement, when designing, being generally next theme or once progressively completing several themes; So we must analyze the several basic theme territories determined in Conceptual Model Design step, select the subject area first will implemented in the lump; First topic territory institute is selected to want it is considered that it will enough greatly, to make this subject area can turn an applicable system into; It is also enough little, so that develop and implement quickly; If selected subject area very greatly and very complicated, we even can develop for its an one significant subset, in feedback procedure each time, all will carry out the analysis of subject area, the most crucial theme that Data Mart is analyzed in market sale is product sales analysis commercially;

Granularity distinguishing hierarchy a: major issue that will solve in Data Mart logical design is the granularity division level in determination data fairground, whether suitable granularity distinguishing hierarchy is directly have influence on data volume in Data Mart and the query type that is applicable to, when determining granularity level in Data Mart, need to consider some factors like this: the analysis type that accept, the minimum granularity of acceptable data and the data volume that can store, analyze in Data Mart in market sale, adopt the mode of double data granularity, the larger combined data of granularity is only retained to the sales data that the time is far away, recent sales data and combined data is preserved with low granularity data, so both can sell recent developments and carry out detail analysis, combined data can be utilized again to analyze sales trend,

Determine data-splitting strategy: in this step; select the standard of suitable Data Segmentation; main consideration following several respects factor: the actual conditions of data volume (and non-recorded line number), Data Analysis Services, simple and granularity division strategy etc., the size of data volume is the principal element determining whether to carry out Data Segmentation and how to split; The requirement of Data Analysis Services is the Main Basis selecting Data Segmentation standard, because Data Segmentation follows the object of Data Analysis Services to be closely connected; We also will consider that selected Data Segmentation standard should be natural, easy to implement: also will consider that the standard of Data Segmentation and granularity division level adapt to simultaneously;

Relation schema definition includes the design that the fact table model of Data Mart, the dimension table model of market sale analysis Data Mart are analyzed in market sale;

Each theme of Data Mart is realized by multiple table, relies on the common code binding of theme to be tied, form a complete theme between these tables.When Conceptual Model Design, just determine the basic theme of Data Mart, and the public code key, substance etc. of each theme are described.In this step, we will carry out mode division to the theme of selected current enforcement, form multiple table, and determine each relation schema shown.

To the assessment of logical model, it is exactly the investigation to logical model quality, require not only the simple business rule of finger merely to model quality, also comprise the degree that model meets customer analysis demand, it is one and comprises rich connotation, has the comprehensive concept of multi-dimensional factors.

In market sale business, relate to several main operational indicator (KPI), as sales volume, sales volume, tank farm stock and stockpile number etc., they accumulate over a long period, substantial amounts.When design using the tolerance of these indexs as fact table.Sell occur time, retail trader and sale be the factors such as which kind of commodity are the visual angles analyzing selling operation, using them as dimension.In granularity division, time dimension can per diem be counted, also can by week, monthly, quarterly and per year, according to " minimum particle size principle ", time dimension has been refine to the level of " day "; For commodity dimension, time owing to analyzing, leveled demand is comparatively obvious, can be divided into the levels such as commodity list product, disaggregated classification, subclassification, middle classification and macrotaxonomy.Other dimensions can be determined according to similar method, finally can obtain the logical model of sales analysis Data Mart.

The Design Mode of the main employing Star Model of Data Mart is analyzed in market sale.It is primarily of the composition such as subject heading list and date dimension table, retail trader's dimension table, product dimension table, regional dimension table, account title dimension table, business department's dimension table comprising market sale data.

The fact table model of Data Mart is analyzed in market sale: after completing the Star Model design based on the market sale analytic system Data Mart of business intelligence, need with determining further in Data Mart, what kind of granularity data could meet the needs of managerial personnel to Data Mart sales analysis, generally those atomic datas obtained due to business processing are first considered in the design of Data Mart, because those atomic datas have height dimension structuring, true metric is trickleer, more there is atomicity, just can reflect the more fact definitely, therefore atomic data can provide dirigibility to greatest extent for administrative analysis, various forms of constraint can be accepted, and user can be presented to various possible form, meet the various inquiry needs of user at any time,

Analyze in theme in market sale, optimal atomic data is the sales services data from operation system.Meanwhile, before the model of construction data fairground, go back Water demand by which angle, namely need by which dimension investigate, selective selling scheme.Generally, when determining market analysis strategy, managerial personnel are analyzed sale scheme by six dimensions such as date, retail trader, product, area, account title, business departments, understand availability and the effect of sale scheme.

In the logical model design of the sales analysis Data Mart based on business intelligence, after determining fact table model, also need the dimension module determining Data Mart.In Conceptual Model Design, determine that the sale Subject Concept model of market sale analysis Data Mart is Star Model, just needed here to determine the concrete dimension in dimension module and hierarchical structure further.

The design that the dimension table model of Data Mart is analyzed in market sale includes date dimension, product dimension, retail trader's dimension, area dimension, account title dimension, business department's dimension.

(1) date dimension

Date dimension module is the conventional dimension in the application of many Data Marts, and its design and other most dimension module do not have difference.During specific design, date dimension table can deposit the data line of 5-10, also the data line of 3-4 can be tieed up content as the date.If all stored the every day in 10 years, also only need 3650 row, this is a quite little dimension table.

The specific date often arranged representated by row of date dimension table defines.The date that market sale is analyzed in Data Mart carries out showing according to the financial date of company, because product sells in the scope across multiple time zone, and the impact adopting unified financial time dimension can eliminate the sales data across time zone to analyze.

Some such attributes are contained: date key, financial year, Fiscal Quarter, the finance moon, finance week, date etc. in date dimension table.

(2) product dimension

The market sale data analyzed in the product dimension of Data Mart are mainly derived from the data of operation system, and when the product list in operation system changes, just from operation system, extracted data also exists in product dimension table.The hierarchy attributes to product classification is also comprised, the attributes such as the platform belonging to product, classification, subclass in product dimension.

Product dimension is one of the dimension the most substantially in Data Mart, has the data analysis of complete product dimension attribute guarantee user correct.When operating this dimension, needing SC, product dimension integrality, the especially hierarchical structure of type of merchandize can not be destroyed.

(3) retail trader's dimension

Wesy of retail trader is responsible in describing the information that product carries out each retail trader of selling in the market.Market sale analyzes retail trader's dimension of Data Mart as shown, and it contains some essential informations of retail trader, also comprises the information such as channel belonging to retail trader and channel classification.Retail trader is ingredient important during market sale is analyzed, and by the analysis to distributor sales information, can obtain the contribution situation of retail trader to production marketing.

(4) area dimension

Area dimension is one of the dimension the most substantially in Data Mart, and in sales analysis Data Mart, in the dimension of area, data are mainly derived from operation system, and it contains the hierarchical structure of regional information, as Asia, East Asia, China, Shanghai.By the hierarchical structure that area is tieed up, the analysis demand of upper volume to data and lower brill can be realized.Generally, the information in the dimension of area remains unchanged.

(5) account title dimension

The market sale account title dimension analyzed in Data Mart mainly comprises and sells relevant account title essential information and the hierarchical information of account title classification.

(6) business department's dimension

Market sale analyzes business department's dimension table of Data Mart for describing each sale subdivision of responsible different product different regions.By the analysis tieed up business department, just can recognize the sales achievement situation of each subdivision, provide foundation for supvr implements incentive measure, also sell distribution for product at interzone simultaneously and foundation in decision-making is provided.

Data Physical model: the physical model of Data Mart is exactly the implementation pattern of Data Mart logical model in physical system.Which includes specializing of various entity list in logical model, the type of data structure such as shown, index strategy, deposit data position and data storage allocation etc.When the design carrying out physical model realizes, consider because have: the cost of I/O access time, space availability ratio and maintenance.

For determining the physical model of Data Mart, designer must do such several respects work: first will fully understand selected data base management system (DBMS), particularly storage organization and access method; Next understands data environment, the frequency of utilization of data, use-pattern, data scale and response time requirement etc., and these are all the important evidence balancing Time and place efficiency and optimize; Finally also need the feature understanding External memory equipment.Only in this way could obtain balance between the two in the storage demand of data and External memory equipment condition.

1, storage organization unit

When physical Design, usually to classify by the importance of data, frequency of utilization and the requirement to the reaction time, and dissimilar data are stored in different memory devices respectively.To the reaction time, high, the frequent access of importance also requires that high deposit data is on high-speed processing apparatus; The data that frequency of access is low or low to access response time requirement then can leave in low speed storage device.In addition, the layout of data on particular memory medium to also be considered when designing.To note following following principle when the layout of design data.

(1) not often needing several the tables connected to be placed on same memory device, the parallel work-flow function of memory device can be utilized like this to accelerate the speed of data query.

(2) if the connection between a few station server can cause the problem of serious network traffic, then server replicates form to be considered, because the data cube computation between different server can bring heavy data transmission burden to network.

(3) consider that the detail data whole enterprise shares is placed on main frame or other centralized servers, improve the operating speed of these shared data.

(4) form and their index are not put on the same device.Generally can leave on high-speed processing apparatus by index, form then leaves on general memory device, to accelerate the inquiry velocity of data.

(5) often to carry out the work of a large amount of wait data in magnetic disk when processing server, now, RAID(Redundant Array of Inexpensive Disk can be used in systems in which, Redundant Arrays of Inexpensive Disks).

2, index policy unit

The data volume of Data Mart is very large, thus needs carefully design the access path of data and select.Due to data generally little renewal of Data Mart, so index structure can be designed to improve data access efficiency.In Data Mart, designer can consider to set up special index and complicated index to each data storage, to obtain higher access efficiency, needs to pay certain cost although set up them, does not generally need too much maintenance after setting up.

Table in Data Mart sets up more index than the table in online transaction processing system (OLTP) usually, and the largest index number applied in table should be directly proportional to the scale of form.Data Mart is a read-only environment, sets up index and can obtain dirigibility, very favourable to performance.If but table has a lot of index, so the Data import time will extend, and therefore the foundation of index needs to carry out comprehensive consideration.When setting up index, the frequency that can use according to index progressively be added from high to low, until after a certain index adds, when making the overlong time of Data import or restructuring table, just terminate the interpolation of index.

At first, be all generally set up index by primary key and most of foreign key, usually do not add other a lot of indexes.After a large amount of indexes set up by table, his-and-hers watches carry out analyzing etc. when specifically using, and may need many indexes, and this can cause the maintenance time shown also to increase thereupon.If embark index from primary key and foreign key, and add other indexes as required, will avoid first setting up the consequence that a large amount of indexes brings.If form is excessive, and need to increase index in addition, so table can be carried out dividing processing.If all row used are all in indexed file in a table, just need not access fact table, as long as access index just can reach the object of visit data, reduce I/O with this and operate.If table is too large, and often will scan for a long time it, so will consider that interpolation summary table is with the scan task reducing data.

3, storage policy unit

After determining the storage organization of data and the index structure of table, need memory location and the storage policy of determining data further, to improve the I/O efficiency of system.Introduce several frequently seen storage optimization method below.

(1) merger shown.When the record dispersion of several table is left in several physical block, the access of multiple table and the cost of attended operation can be very large.At this moment can by the table needing simultaneously to access physically order deposit, or directly to be put together by the public keyword record that will be mutually related.

The merger of table just has good effect when only having very strong access correlativity between access sequence often occurs or shows, and for the access sequence seldom occurred and the table not having strong correlation, the merger of use table does not have effect.

(2) redundancy is introduced: some attribute of some tables all will may be used in many places, is copied to by these attributes in multiple theme, can reduce the number of access list during process.

(3) additive method: except above 2 kinds of main methods, also has following 3 kinds of methods can be optimized storage allocation.

Set up data sequence: process one group of data record according to a certain fixing sequential access.Data are stored in continuous print physical block according to processing sequence, form data sequence.

The physical segmentation of table: each attribute access frequency in each theme is different.The frequency that a table is accessed by each attribute is divided into 2 or multiple table, will there is the Organization of Data of similar access frequency together.

Data are sent in generation: summarize on the basis of raw data or calculate, data are sent in generation, can these be directly used to send data in the application, reduce by I/O number, remove calculating or aggregation step from, higher level establishes public data source, avoids the issuable deviation of different user double counting.

Physical Design carries out the optimum choice of index based on the logical schema of Data Mart and working load, and needs the special data access structure considering the data base management system (DBMS) adopted, and the selection of index plays key effect [4] to Data Mart performance.Index problem obtains good solution in many existing Data Mart solutions, the Data Mart solution provided as SQLServer2005, the Data Mart solution of statistical analysis system (Statistics Analysis System, SAS).The solution that market sale is analyzed selected by Data Mart is SQLServer2005 Data Mart solution.

Create market sale by BI instrument and analyze Data Mart:

User seldom directly carries out the access of fact table to the access of Data Mart, user operates Data Mart often through the cube in visit data fairground.Therefore, the foundation of cube is indispensable part during Data Mart creates.

1, the establishment of Data Mart database

It is carry out constructs database according to Data Mart design phase determined Data Mart physical model that the physics of Data Mart creates, and MS SQL Server mainly utilizes relational database to build Data Mart.In Data Mart database creation process, need to realize basic structure-fact table and the Wei Biao of Data Mart according to determined data model in Data Mart design, analyze in Data Mart database in market sale, mainly comprise the establishment of production marketing fact table, date dimension table, regional dimension table, product dimension table, retail trader's dimension table, account title dimension table, business department's dimension table etc.Data in Data Mart database are through that ETL process obtains, and As time goes on, in these tables, data can carry out full dose renewal, incremental update.

2, the establishment of Data Mart dimension

Analyze in Data Mart in market sale, mainly comprise date dimension, retail trader's dimension, product dimension, the area dimension type such as dimension, account title dimension, business department's dimension.As in date dimension, realizing the definition of the hierarchical structure to time, season, month, like this when carrying out multidimensional analysis to sales data, just by selecting different date dimension hierarchies, the sales situation in different time granularity can be observed.

3, the generation of Data Mart data cube

After completing all dimension establishments, just fact table and dimension can be shown to form cube, i.e. cube.

Analyze in Data Mart in market sale, by navigational aids, fact table is associated with dimension table, and select relevant degree reason value, as product sales, the sales volume of the product, profit etc., thus generate the cube of Star Model.

The dimensions updating process of Data Mart:

Dimension can mainly be divided into unchanged dimension, slowly change dimension and acute variation dimension according to change severe degree.The relevant information of such as company, the information data such as company code, Business Name belongs to constant part, address and scope of business belong to slow changing unit, and employee information, inventory information and production marketing etc. belong to a certain extent and sharply change field [17].

For acute variation dimension, being all divided into two under normal circumstances and processing, the part wherein seldom changed is released separately as a dimension table, processing according to slowly changing dimension mode; A part also extracts separately in addition, and the attribute usually used as dimension processes.

The migration in time of most of dimension table is slowly change.Such as add new product, or the id number of product have modified, or product adds a new attribute, now, it is capable that dimension table will be modified or increase new record.Like this, in design dimension with in the process of use dimension, the process of slowly change dimension will be considered.

The slow change of dimension has 3 kinds of different situations, and the disposal route of its correspondence is also different.

1, historical data needs amendment

There is mistake in the data that this situation mainly occurs in Service Database, needs amendment in analytic process.

Treating method uses direct cladding process, and namely use UPDATE method revises the data in dimension table.

2, newly-increased data dimension member changes attribute

If certain dimension member newly adds 1 row, these row can not be browsed based on it in the historical data, and can browse according to it in data in current data and future.Solution now increases data line to record newcomer.Storing process or the new dimensional attribute of Program Generating can be used, check based on new attribute in follow-up data.

3, historical data retains, and newly-increased data also will retain

Solution under this demand creates extra field to record the relation between these data, such as this dimension is stamped timestamp, the time period of coming into force by historical data is as its attribute, to associate according to the time period when generating fact table with original match, its maximum advantage of this method is when data change, do not need to create extra data line, do not need to change the key value structure in dimension table yet, therefore can check all historical records in existing data line.And maximum shortcoming judges that the data upgraded are difficult to inquiry by time point, if data often change, then the method being not suitable for.

The historical record of process dimension is a reason of ETL solution more complicated.For sales analysis system, dimension load condition not only relates to process historical status and Alternative Attribute, also relate to dimension change type and with dimension associate cannot be synchronous factual data.

The process of gradual change dimension:

Can substantially reach this effect by SSIS instrument, have a guide in SSIS, it is based on source dimension framework and target dimension framework, makes developer can determine the feature changed by series of steps.Then this guide sets up the conversion of process required for this dimension.Even if require to change, also can this guide of re invocation, by allowing the original selection of amendment to process new process [18].

For Sales Analysis System, gradual change dimension instrument is advantageous.Except a star schema dimension table, other all star schema dimension tables all use gradual change dimension transformation.Highly shortened the development time that dimension process is used.In order to show the working method of gradual change dimension guide, Store dimension provides the most comprehensive using method of this guide.Requiring to include of Store dimension:

(1) the new dimension member of newly-built dimension Cheng person – adds in source;

(2) change the Class1 row change before dimension Shu –, wherein during the change of each source train value, historical record is capped;

(3) type 2 before history dimension Shu – arranges, and before wherein by interpolation one new dimension record historical record being saved in change next time, the new dimension record of interpolation is associated with all new fact data records;

(4) infer member – namely before factual data process runs dimension member be not yet loaded into the situation in dimension table, can add a placeholder record like this, once complete source dimension can be used, this placeholder will upgrade subsequently.

Unique dimension process:

Unique dimension does not use the dimension handle packet of gradual change dimension transformation to be Item dimension.Its requirement is unique, and its size needs to carry out special processing to scalability.

The fact table update process of Data Mart:

Factual data list processing (LISP) is different from dimension process to a great extent.Further, a factual data list processing (LISP) and the next one also have a great difference.But most of fact table pack processing is containing the capable contrast of factual data and dimension key inquiry.

Mainly two parts are comprised to the extraction of fact table: all source is extracted, that wherein cannot identify change or new record; Increase progressively extraction, wherein only extract new record and the record of change.

Whole source is extracted:

On origin system, these records are included in table, and this table does not identify record that is new or that revised, and therefore ETL process must compare the time that the record between stock source and fact table occurs to identify change.Then correctly process is inserted or is upgraded.

The method taked uses all to merge to source table and object table to be connected and complete data set.All be connected with the time helping identification record and add the time in source to or delete completely.For this solution, require that the Merge Join conversion of specifying the source record of deleting to need position-use when being tracked as data base initialize to be configured to all connect in fact table meets this requirement.

Increase progressively source to extract:

When leaching process can be isolated one group of renewal and be inserted in origin system, this greatly can improve the performance of relevant ETL process.If need whole data source (such as 20,000,000 records) to process daily change, then will have insufficient time to other tasks of process in one day.But because data can increase progressively extraction, therefore processing window is reduced to a window being highly susceptible to managing.

Increasing progressively leaching process can use caching query to help determine that incremental record is the record or the record of renewal that insert.What contribute to this process is middle fragmentation procedure, and it is used for filter record in queries, optimization process, thus contributes to overall realization.

The design of the client end interface of Data Mart is analyzed in market sale:

After completing the design of Data mart model, be just faced with the problem how data in Data Mart being supplied to user and using.Generally, be do not allow user directly to enter into Data Mart carry out browsing of data and use.Therefore need the Data Mart application function of general user to design in advance, the form of formation customization and browsing data are sent to client provides user to use.

To the use of Data Mart, user mainly concentrates on that theme cube represents, data mining results check browse, the prediction of management decision and the dynamic queries of Data Mart content.

(1) the representing of theme cube.Cube is the major way in user's usage data fairground, and the user in market sale analytic system, by different dimensional, the upper volume of different levels, lower brill to theme cube, can check the content in Data Mart easily.

(2) the checking and browse the user of Data Mart very important of data mining results, many valuable management decision schemes are often from the result of data mining.Such as: in merchandising analysis of strategies, by representing data mining results, user may find, the application of some promotion strategy can play splendid effect on certain areas, some time period, some commodity; In some other area, some other time period then may be not obvious, even invalid to the effect of other commodity.This will impel management decision personnel to the formulation of promotion strategy more accurately with effective.

The prediction of management decision.Be main contents in user's usage data fairground to the prediction of management decision, marketing management personnel needed some successful promotion strategies for unused product and unused time period.The management decision layer of now market sale department is predicted the result of use of promotion strategy with regard to needing, to determine whether to adopt these promotion strategies.

(4) dynamic queries of Data Mart content.The management decision layer of market sale department usually needs the effect of closely observing the promotion strategy or performed, to determine whether continue to perform the promotion strategy started, whether strengthen the implementation dynamics of promotion strategy according to the implementation effect of promotion strategy.This dynamic queries is for the part management decision-maker being daily management mission.User should be noted that the refresh time of data in Data Mart when dynamic queries is carried out in usage data fairground, if the data in Data Mart load every day to refresh once, that management decision layer greatly can the authenticity of relieved dynamic queries result; If the data in Data Mart even monthly just load weekly to refresh once, that just needs management decision-maker to note the impact of promptness on management decision of data query data.

After determining the content that client end interface represents, just need respectively each to be represented content assignment and represent on interface, as the foundation representing interface specific design to concrete.That must note when designing and representing interface providing hommization for user represents interface simultaneously, user is enable to understand data content required for him and its mutual relationship from representing interface easily, the effect making them be easy to recognize management decision from these data and the commercial trend be hidden in after these data.

Native system adopts the main application of ETL to comprise following several respects:

1, the data from heterogeneous data memory block are merged

Integration Services comprises some data source component, and these assemblies are responsible for extracting data from the table in the flat file comprised connected data source, excel spreadsheet lattice, XML document and relational database and view.Then, usually by the translation function that Integration Services comprises, data are changed.After data are converted to compatible format, just its physics can be merged into a data centralization.Data merge successfully and apply conversion after, usually can be loaded into one or more target.Integration Services comprises Data import to target used when flat file, source document and relational database.Data also can be loaded in the record set in internal memory, for the access of other bag elements.

2, padding data warehouse and Data Mart

Data in data warehouse and Data Mart understand frequent updating usually, and therefore Data import amount usually can be very large.Can with the dimension table in SSIS bag loading of databases and fact table.If the source data of dimension table is stored in multiple data source, these data can be merged into a data centralization by bag, and load dimension table in individual process, instead of use independent process for each data source.IntegrationServices can also before Data import to its target computing function.

3, to clear data and by data normalization

No matter data are loaded into Transaction Processing (OLTP), on-line analytical processing (OLAP) database, excel spreadsheet lattice or are loaded into file, all need data to be carried out clearing up and standardization before loading.

Integration Services comprises some built-in conversions, can be added in bag with clear up data and by the capital and small letter of data normalization, change data, convert data to dissimilar or form or create new train value according to expression formula.Such as, bag and can rank surname row to connect into and singlely entirely to rank, then character is changed to capitalization.

Integration Services bag can also use accurately search or fuzzy search to find the value in reference list, by the value that replaces with in reference list of value in row is cleared up data.Usually, first bag uses accurately searches, if this searches mode failure, re-uses fuzzy search.Such as, the ProductName first attempting being searched by the Major key of use product in reference list is wrapped.If this search cannot find ProductName, bag reattempts and uses ProductName fuzzy matching mode to search for.

4, business intelligence is inserted data conversion process

Data conversion process needs logic built to carry out the data of its access of dynamic response and process.

May need to gather data according to data value, change and distribute.According to the assessment to train value, this process even may need to refuse data.

Integration Services provides container for business intelligence being inserted SSIS bag, task and conversion.A data set can also be sent to multiple target, then different Transform Sets be applied to this identical data.Such as, one group of conversion can gather these data, and another group conversion is by the value of searching in reference list and the data of adding other sources expand this data.

5, management function and Data import robotization is made

Keeper often wishes management function robotization, such as backup-and-restore database, copy SQLServer database and the object comprised thereof, copy SQL Server object and load data.Integration Services bag can perform these functions.

More than show and describe ultimate principle of the present invention, principal character and advantage of the present invention.The technician of the industry should understand; the present invention is not restricted to the described embodiments; what describe in above-described embodiment and instructions just illustrates principle of the present invention; the present invention also has various changes and modifications without departing from the spirit and scope of the present invention, and these changes and improvements all fall in the claimed scope of the invention.Application claims protection domain is defined by appending claims and equivalent thereof.

Claims (8)

1. a Data Mart system is analyzed in market sale, it is characterized in that:

This system module comprises: data access layer, data extraction module, data conversion module, data cleansing module, daily record and warning sending module, Data import module;

The data of data access layer include office data, external data, business datum;

Data extraction module includes the identical data source process of the Database Systems of depositing DW, data source, incremental update that DW Database Systems are different;

In data conversion module, inconsistent data conversion, the conversion of data granularity, the calculating of business rule are carried out to data;

Data cleansing module includes three major types: the data of incomplete data, mistake, the data of repetition;

Daily record is with daily record when warning sending module register system to run and send warning to system manager;

Data import module includes data encasement unit, Data import way selection unit, Data import unit in enormous quantities;

The model of this system comprises mathematical logic model and Data Physical model;

Mathematical logic model carries out analysis subject area, granularity distinguishing hierarchy, determines that data-splitting strategy, relation schema define;

Data Physical model includes storage organization unit, index policy unit, storage policy unit.

2. Data Mart system is analyzed in a kind of market sale according to claim 1, it is characterized in that, the data of described data access layer include the office system data that office data mainly refers to sales and marketing department's door, these data divide electronic data and non-electronic data two kinds, with the data that electronic data mode is preserved, main finger electrical form, the data that the form such as database and word processing file is preserved, non-electronic data mainly refer to those files, the official documents such as notice, from the version of data, office data has plenty of the structural data represented with bivariate table case form, have plenty of with the structural data of word or file process representation of file, therefore the data structure in office data source is very complicated, this just gives the data pick-up of Data Mart, load and add very large difficulty, sometimes even need after artificial treatment, just can be loaded in Data Mart,

External data refer to those not for market sale department is operated, the data that have, control, the electronic form that these data have, if third party information service business is with Web Service mode XML data, having is non electronic version, as the relevant report file etc. that retail trader provides, the use difficulty of these data sources is roughly the same with processing mode and office data;

Business datum refers to that the transaction processing system there from running at present is collected, and is saved in the data of transaction processing system database, and to business datum, which data of Water demand should be loaded in Data Mart.

3. Data Mart system is analyzed in a kind of market sale according to claim 1, it is characterized in that, it is easy at design comparison that described data extraction module includes this kind of several source in the identical data source process of the Database Systems of depositing DW, DBMS (comprises SQL Server, Oracle) all can provide data basd link connection function, between DW database server and former operation system, set up direct linking relationship just can write Select statement and directly access;

This kind of data source of the data source that DW Database Systems are different generally also can be linked by the mode building database of ODBC, between Oracle and SQL Server, if can not link by building database, two kinds of modes can be had to complete, one is, by instrument, source data is exported to .txt or .xls file, and then these origin system files are imported in ODS, another method has been come by routine interface;

For the system that data volume is large in incremental update, increment extraction must be considered, generalized case, the time that market sale operation system meeting record traffic occurs, the mark of increment can be used as, first judge before each extraction to record the maximum time in ODS, then go in operation system database, to get all records being greater than this time according to this time.

4. Data Mart system is analyzed in a kind of market sale according to claim 1, it is characterized in that, described data conversion module: inconsistent data conversion is in market sale analytic system, there is the inconsistent situation of data content in the data from different pieces of information source, this process just needing establishment one to integrate, the data of the identical type of different business systems are unified;

The conversion of data granularity generally stores very detailed data in operation system, and the data in Data Mart are used to analysis, do not need very detailed data, generally, operation system data can be polymerized according to Data Mart granularity;

The calculating of business rule also exists different business rules in market sale analytic system, different data targets, these indexs are not that simple plus-minus just can complete sometimes, be stored in Data Mart, for analysis after needing this time in ETL process, these data targets to have been calculated.

5. Data Mart system is analyzed in a kind of market sale according to claim 1, it is characterized in that, incomplete data in described data cleansing module are some due loss of learnings, as the title of supplier, the title of branch office, the area information disappearance of client, master meter can not mate with detail list in operation system, need by this class data filtering out, write different Excel file respectively by the content of disappearance to submit to client, require completion in official hour, after completion, be then written to Data Mart;

Mainly operation system is not well established for the Producing reason of the data of mistake, the background data base that do not carry out judging writing direct after receiving input causes, have after such as numeric data defeated one-tenth full-shape numerical character, string data that a carriage return, date format are incorrect, the date crosses the border, these class data also will be classified, there is the problem of not meeting personally character can be found out by the mode writing SQL statement for being similar to before and after double byte character, data, then requiring that client extracts after operation system correction; This class mistake that date format incorrect or date crosses the border can cause ETL to run unsuccessfully, and this class mistake needs the mode of operation system database SQL to pick out, and gives business department and revises, extract after correction again;

The data problem repeated is more common in dimension table, all fields that records of the data of repetition is derived, then allows business department confirm and arrange.

6. Data Mart system is analyzed in a kind of market sale according to claim 1, it is characterized in that, described daily record contains three classes with the log packet in warning sending module:

The first kind is implementation daily record, is the record often performing a step in ETL implementation, and the initial time of each step of record each run, have impact on how many row data, day-to-day account form;

Equations of The Second Kind is error log, when certain module error time, need write error daily record, records the time of at every turn makeing mistakes, the module of makeing mistakes and the information etc. of makeing mistakes;

3rd class daily record is overall daily record, only the record ETL start time, end time whether successful information;

Warning is sent in after ETL makes mistakes, and not only will write ETL and to make mistakes daily record but also will send warning to system manager, the mode sending warning has multiple, and conventional sends mail to system manager exactly, and encloses the information of makeing mistakes, and facilitates keeper to investigate mistake.

7. Data Mart system is analyzed in a kind of market sale according to claim 1, it is characterized in that, in described Data import module:

Data encasement unit: because the data pick-up of Data Mart is analyzed in market sale, cleaning, load and need the longer time, therefore a volatile data base as data encasement district will be set when processing data, be specifically designed to data pick-up, cleaning and the operation loaded, can setting data extract in data encasement district, cleaning and the restart mechanism loaded, in the extraction of data, cleaning and loading procedure in, usually because the reason of system or some other unpredictable factor cause these movable failures, if after failure, restart the ample resources of waste system, for this reason, can setting data extract, cleaning and the monitoring mechanism loaded, dynamic monitoring is carried out to these activities, once failure, just can from unsuccessfully restarting, and need not start anew, as the extraction of a certain business datum, cleaning and loading needs 8 steps just can complete, when system completes 6 steps wherein, after entering the 7th step, load unsuccessfully, system is after restarting, just can restart in the 7th step, and need not start anew, for completing this mechanism, need the extraction of data, cleaning and loading activity are divided into some steps clearly, and when entering a certain step, retain current state,

Data import way selection unit: the mode of Data import generally considers batch processing, because the system resource that the loading activity of data relates to is more, need the processor of data source and Data Mart, internal memory and External memory equipment, and most of data source is used in transaction processing system, need for user provides real time service by day, therefore the Data import of Data Mart is often selected to carry out in festivals or holidays or night, and this coordinates with regard to needing the Data import process business processing relevant to other;

Data import unit in enormous quantities: the data source that market sale is analyzed to be had in Data Mart is prohibited to load for simple Large Volume Data, this just needs the technology adopting some special to process the loading of mass data, the use restricted problem of system resource is also related in mass data loading procedure, need the processor of data source and Data Mart simultaneously, the support of network and internal memory each side, and these precious resources can run into considerable restraint in the application, the loading that data in enormous quantities in Data Mart are analyzed in market sale realizes by adopting Data Replication Technology in Mobile, the reproduction technology of data can ensure the integrity constraint in data load process, the impact of the accident factors such as thrashing can not be subject to, and process can be optimized to the transport process of data.

8. Data Mart system is analyzed in a kind of market sale according to claim 1, it is characterized in that, described mathematical logic mould:

Carry out analysis subject area: in Conceptual Model Design, we determine several basic subject area, but the method for designing of Data Mart is the process of a Stepwise Refinement, when designing, being generally next theme or once progressively completing several themes; So we must analyze the several basic theme territories determined in Conceptual Model Design step, select the subject area first will implemented in the lump; First topic territory institute is selected to want it is considered that it will enough greatly, to make this subject area can turn an applicable system into; It is also enough little, so that develop and implement quickly; If selected subject area very greatly and very complicated, we even can develop for its an one significant subset, in feedback procedure each time, all will carry out the analysis of subject area, the most crucial theme that Data Mart is analyzed in market sale is product sales analysis commercially;

Granularity distinguishing hierarchy a: major issue that will solve in Data Mart logical design is the granularity division level in determination data fairground, whether suitable granularity distinguishing hierarchy is directly have influence on data volume in Data Mart and the query type that is applicable to, when determining granularity level in Data Mart, need to consider some factors like this: the analysis type that accept, the minimum granularity of acceptable data and the data volume that can store, analyze in Data Mart in market sale, adopt the mode of double data granularity, the larger combined data of granularity is only retained to the sales data that the time is far away, recent sales data and combined data is preserved with low granularity data, so both can sell recent developments and carry out detail analysis, combined data can be utilized again to analyze sales trend,

Determine data-splitting strategy: in this step; select the standard of suitable Data Segmentation; main consideration following several respects factor: the actual conditions of data volume (and non-recorded line number), Data Analysis Services, simple and granularity division strategy etc., the size of data volume is the principal element determining whether to carry out Data Segmentation and how to split; The requirement of Data Analysis Services is the Main Basis selecting Data Segmentation standard, because Data Segmentation follows the object of Data Analysis Services to be closely connected; We also will consider that selected Data Segmentation standard should be natural, easy to implement: also will consider that the standard of Data Segmentation and granularity division level adapt to simultaneously;

Relation schema definition includes the design that the fact table model of Data Mart, the dimension table model of market sale analysis Data Mart are analyzed in market sale;

The fact table model of Data Mart is analyzed in market sale: after completing the Star Model design based on the market sale analytic system Data Mart of business intelligence, need with determining further in Data Mart, what kind of granularity data could meet the needs of managerial personnel to Data Mart sales analysis, generally those atomic datas obtained due to business processing are first considered in the design of Data Mart, because those atomic datas have height dimension structuring, true metric is trickleer, more there is atomicity, just can reflect the more fact definitely, therefore atomic data can provide dirigibility to greatest extent for administrative analysis, various forms of constraint can be accepted, and user can be presented to various possible form, meet the various inquiry needs of user at any time,

Market sale analyze Data Mart dimension table model design in include the date dimension, product dimension, retail trader dimension, area dimension, account title dimension, business department dimension.