WO2012169807A2

WO2012169807A2 - Method and system for building database using data warehouse

Info

Publication number: WO2012169807A2
Application number: PCT/KR2012/004507
Authority: WO
Inventors: 백승호
Original assignee: Baek Seung Ho
Priority date: 2011-06-07
Filing date: 2012-06-07
Publication date: 2012-12-13
Also published as: WO2012169807A3; KR20120135665A; KR101253335B1

Abstract

A method for building a database using a data warehouse is comprised by generating the data warehouse based on items of source data, which are provided by at least one source system. The method for building the database comprises the steps of: configuring an operational data store (ODS) by refining at least a portion of the items of source data; comprising the data warehouse (DW) by integrating the ODS data and generating a reference relationship between the items of data which have an associative relationship; comprising a business logic by enabling at least one business rule for analysis; and comprising a data mart by generating a multi-dimensional model per analysis subject with regard to the ODS or the DW data, based on the at least one business rule.

Description

Database construction method and system using data warehouse

The disclosed technology relates to database construction technology, and more particularly to a database technology using a data warehouse that can apply new business rules without reconfiguring the data warehouse by configuring the business logic as an independent area of the data warehouse. .

Data Warehouse (DW) system is a system for analyzing data of various operating systems operated by users such as companies. While the operating system supports certain functions for operation such as inventory management, accounting information, and sales system, the data warehouse may provide a function for integrating and analyzing data generated by various operating systems.

Conventional data warehouse technology has performed analysis by providing data constructed in the data warehouse to data marts according to specifically programmed business logic. Therefore, in order to perform different analysis, the program itself has to be re-analyzed and recoded in order to change or modify business logic, which requires a lot of cost and time, and has a low probability of error occurrence.

Among the embodiments, a database building method using a data warehouse is achieved by creating a data warehouse based on source data provided from at least one source system. The database construction method using a data warehouse comprises the steps of refining at least some of the source data to construct an Operational Data Store (ODS), integrating the data of the ODS, and generating a reference relationship between the related data. Configuring a data warehouse (DW), implementing business logic by implementing at least one business rule for analysis, and analyzing the data of the ODS or DW based on any one of the at least one business rule. Creating a multi-dimensional model to construct a data mart.

Among embodiments, a database system using a data warehouse constructs a database based on source data provided from at least one source system. The database system using the data warehouse includes an Operational Data Store (ODS) for refining at least some of the source data, and a DW (Data Warehouse) for integrating and associating data of the ODS and generating reference relationships. The data mart includes a business logic for managing at least one business rule for analysis and a multi-dimensional model for data of the ODS or DW based on any one of the at least one business rule.

Among the embodiments, the work system building method using a database is implemented using a database generated from at least one source system. In the method of building a business system using the database, integrating the at least one source data and generating an association to construct a data warehouse (DW), and construct at least one business logic according to at least one business rule for analysis. And selecting at least one of the at least one business rule from the business logic, and integrating the data of the DW using the selected at least one business rule to perform multidimensional modeling to construct a data mart. .

1 is a schematic diagram illustrating a database system using a data warehouse to which the disclosed technology can be applied, and an operating system and a user system associated therewith.

2 is a block diagram illustrating a database system using a data warehouse according to an embodiment of the disclosed technology.

3 is a schematic diagram illustrating an embodiment of configuring an ODS.

4 is a schematic diagram illustrating an embodiment of configuring logic data.

5 is a block diagram of an embodiment of a database construction method using a data warehouse to which the disclosed technology can be applied.

FIG. 6 is a flowchart of an embodiment of the database building method of FIG. 5.

Description of the disclosed technology is only an embodiment for structural or functional description, the scope of the disclosed technology should not be construed as limited by the embodiments described in the text. That is, the embodiments may be variously modified and may have various forms, and thus the scope of the disclosed technology should be understood to include equivalents capable of realizing the technical idea.

On the other hand, the meaning of the terms described in the present application should be understood as follows.

Terms such as "first" and "second" are intended to distinguish one component from another component, and the scope of rights should not be limited by these terms. For example, the first component may be named a second component, and similarly, the second component may also be named a first component.

When a component is referred to as being "connected" to another component, it should be understood that there may be other components in between, although it may be directly connected to the other component. On the other hand, when a component is referred to as being "directly connected" to another component, it should be understood that there is no other component in between. On the other hand, other expressions describing the relationship between the components, such as "between" and "immediately between" or "neighboring to" and "directly neighboring to", should be interpreted as well.

Singular expressions should be understood to include plural expressions unless the context clearly indicates otherwise, and terms such as "comprise" or "have" refer to features, numbers, steps, operations, components, parts, or parts thereof described. It is to be understood that the combination is intended to be present and does not exclude in advance the possibility of the presence or addition of one or more other features or numbers, steps, operations, components, parts or combinations thereof.

In each step, an identification code (e.g., a, b, c, etc.) is used for convenience of description, and the identification code does not describe the order of the steps, and each step clearly indicates a specific order in context. Unless stated otherwise, they may occur out of the order noted. That is, each step may occur in the same order as specified, may be performed substantially simultaneously, or may be performed in the reverse order.

All terms used herein have the same meaning as commonly understood by one of ordinary skill in the art unless otherwise defined. Generally, the terms defined in the dictionary used are to be interpreted to coincide with the meanings in the context of the related art, and should not be interpreted as having ideal or excessively formal meanings unless clearly defined in the present application.

1 is a schematic diagram illustrating a database system using a data warehouse to which the disclosed technology can be applied, and an operating system and a user system associated therewith. Hereinafter, a description will be collectively referred to as a database system including a data warehouse (DW) and a data mart.

Referring to FIG. 1, the database system 100 configures a data warehouse 120 having an entity-relational model with respect to operational data (hereinafter, source data) stored in an operating system (hereinafter, referred to as a source system), and configured data warehouse. The data mart 140, which is a plurality of multidimensional models, may be configured based on the house 120. Detailed description of the database system 100 according to the disclosed technology will be described later with reference to FIG. 2.

The source system may include various systems or databases (eg, inventory management system, purchasing management system, inventory management system, retail system, etc.) for business processing being used in the enterprise.

The user system can obtain analyzed information about the source data using the data mart 140. Although FIG. 1 illustrates an online analytical processing (OLAP) server and a web server, various other systems may be used according to the needs of those skilled in the art.

Hereinafter, a database system 100 and a configuration method thereof according to the disclosed technology will be described in detail with reference to FIGS. 2 to 6.

2 is a diagram illustrating a database system according to an embodiment of the disclosed technology, FIG. 3 is a schematic diagram illustrating an embodiment of configuring an ODS, and FIG. 4 illustrates an embodiment of configuring a DW. It is a schematic diagram to make.

2 to 4, the database system 100 includes an ODS 110, a data warehouse (DW) 120, a business logic 130, a data mart 140, and the like. Includes metadata 160. In one embodiment, the database system 100 may further include a staging area (not shown) and / or summary report 150.

The staging area (not shown) is a temporary area for receiving source data to the DW 120. In other words, the staging area may temporarily store the data in order to load the source data into the DW 120. Since the staging area may not require a predetermined format for storing data, the staging area may store various types of data such as a table form or a file form. The staging area is not an essential component of the database system 100 in accordance with the disclosed technology and may be added according to implementation needs.

Operational Data Store (ODS) 110 is an intermediate data store for configuring DW 120. The ODS 110 may store source data of the source system and perform some purification or processing on the data.

In one embodiment, the ODS 110 may store the data without changing the format (model) of the source data, and then correct (resolve) the error data by verifying code standardization and data integrity. For example, after storing the sales data of the source system, the consistency can be verified (eg, the sum of annual sales and the sum of sales from January to December). The ODS 110 may be configured by performing a predetermined ETT (Extraction, Processing, Transformation, Transport) procedure on the source data. In general, ETT means extracting, purifying, processing, transmitting, or loading data, and the like, and in the configuration of the ODS 110, loading and purification of source data may be performed. 3 shows an example of loading data of a source system into the ODS 110, and shows customer data (customer 01, customer 021, customer 03) in different source systems SYS2, SYS3, SYS5 as shown. It can be loaded, and the error data can be corrected by verifying the consistency of the loaded customer data. In one embodiment, the ODS 110 may be configured by refining the source data based on business rules for each operating system, not integrated rules. By using the ODS 110, the data system can be easily processed, converted, and purified in configuring the database system 100.

DW 120 is integrated data of an ER (Entity-Relationship) model constructed by performing a predetermined ETT procedure on the ODS 110. In other words, the DW 120 may integrate and standardize various source data purified by the ODS 110 as one model (ER model).

For example, Company A has a purchasing management system, an inventory management system, a retail system, and a wholesale system to carry out both retail and wholesale operations, and to provide computer support. Suppose you are building a house. In other words, it is assumed that the data warehouse is used to analyze sales and compare with purchase, retail, wholesale, and inventory, and adjust unnecessary inventory to reduce costs. To build a data warehouse based on these assumptions, data from multiple source systems (purchasing management systems, inventory management systems, retail systems, and wholesale systems) can be designed and built into one unified model. Here, if the retail and wholesale systems each have corresponding customer information, and the retail and wholesale customers can be at least partly the same, then the data warehouse is managed by different source systems (retail and wholesale systems). With respect to retail and wholesale customer data, one data model can be constructed with the theme of customer. When one customer data is modeled and constructed as described above, only one data exists in the DW even though Hong Gil-dong customers are duplicated in the main system (retail system and wholesale system). As such, DW can be built by performing integrated and standardized modeling on the source data.

In more detail, the DW 120 may integrate data (purified source data) constituting the ODS 110 and generate a reference relationship with respect to data that is related to each other. In one embodiment, DW 120 may maintain the key structure of the data of ODS 110. In one embodiment, DW 120 may maintain a link for reference to data in ODS 110. Since the "customer L1" data shown in FIG. 4 is data integrating the "customer 01", the "customer 03", and the "customer 03" data of the ODS 110, it is possible to maintain a link for reference as shown in the arrow. have. Data of the DW 120 may refer to other data in the DW 120. It can be seen that the "customer L2" data in FIG. 4 refers to the "customer L1" and "performance L1" data in the DW 120, and the "customer L3" data refers to the "customer L2" data. Here, for example, the "customer L2" data may be a customer rating (L2) given based on the last three months of performance, and the "customer L3" data may refer to a customer rating ( L3). In one embodiment, the DW 120 may perform certain data processing to exclude duplication of data and to maintain consistency.

Business logic 130 is an area for managing business rules. In the conventional case, the data mart is designed by reflecting the business rules, so the business rules themselves are not managed as an independent area but only reflected in the data mart design process. The disclosed technology manages business rules in business logic 130, which is one independent area of database system 100, and business logic 130 may provide the managed business rules to data mart 140.

In more detail, the business logic 130 may implement at least one business rule necessary for analysis without duplication, and the data mart 140 receives the corresponding business rule from the business logic 130 to perform multidimensional data modeling. can do. Here, the business rule is a predetermined rule for organizing the data mart by subject. For example, if you try to construct a data mart for retail and wholesale, the business rules include: (i) determining the number of retail sales in the market code for domestic and traditional markets only; It is the same as deciding when to trade as a wholesaler. Since the at least one business rule implemented in the business logic 130 may be determined according to the needs of those skilled in the art according to the source system and the required analysis, the disclosed invention is not limited to the specific business rule. That is, since the disclosed technology includes the business logic 130 as an independent area for directly implementing and managing business rules, the disclosed technology is not limited to each business rule itself implemented in the business logic 130.

The data mart 140 may be configured as multi-dimensional modeled data based on an analysis subject for the integrated data of the DW 120 based on any one of at least one business rule implemented in the business logic 130. For example, the data mart 140 may be configured as statistics on analytical topics such as CRM, statistics, and the like as shown in FIG. 2. In another example, the data mart 140 is a model rule for the data required for analysis, such as the number of items (dimension) inventory, sales, purchases, retail, wholesale (Fact; Measure) for inventory analysis Can be collected according to. In one embodiment, the data mart 140 analyzes the criteria (configuration data) implemented in the ODS 110 and the DW 120 in accordance with the business rules of the business logic 130 (eg, analysis). Can be grouped by topic). Here, the data mart 140 may not perform processing of data other than simply aggregation (for example, grouping) analysis criteria implemented in the ODS 110 and the DW 120. In one embodiment, since the data mart 140 is composed of a multi-dimensional model, it may represent a nesting relationship for each subject. To this end, elements of the data mart 140 may be represented by a star schema or a snow flake schema. Here, it is preferable that the elements of the data mart 140 do not have any reference relations except the subject-specific nesting relations.

The summary report 150 is a summary table configured by performing a predetermined ETT procedure on the data mart 140. The summary report 150 may design the data so that it can be displayed on a standardized screen. The summary report 150 preferably organizes the table to be summarized from one subject.

The metadata 160 may maintain metadata for at least one of the ODS 110 and the summary report 150. In configuring the ODS 110 to the summary report 150, each element may be configured using the metadata 160.

FIG. 5 is a block diagram of an embodiment of a database building method using a data warehouse to which the disclosed technology can be applied, and FIG. 6 is a flowchart of an embodiment of the database building method of FIG. 5.

Referring to FIG. 5, in a method of constructing a database using a data warehouse, at least a part of source data is purified to form an operational data store (ODS) (step S510), data of which the data of the ODS are integrated and associated with each other. Creating a reference relationship between the data warehouse (DW) (step S520), implementing the at least one business rule for analysis to construct the business logic (step S530), and at least one of the business rules Comprising one step of generating a multi-dimensional model for each subject of analysis on the data of the ODS 110 or DW 120 based on one (step S510).

6, the ODS 110 is configured based on the source data provided from the source system (step S610). Referring to step S610 in more detail, the ODS 110 may be configured by loading at least a portion of the source data and verifying the integrity of the data with respect to at least some of the loaded source data (refining or ETT). Here, the ETT refers to combining at least one of the extraction, transformation, or transportation of the source data. The specific contents of the ETT may vary depending on the type of the source system, the extraction cycle of the data, the amount of data, the loading speed, the quality of the source data, the format of the historical data, the requirements of the user, and the role of the source system. In the step S610 of configuring the ODS, the ETT may be performed only by a refinement process of determining the consistency of the loaded source data and correcting the error data.

When the ODS 110 is configured, the DW 120 may be configured by integrating the data of the ODS 110 and generating a reference relationship between related data (ETT) (step S620). The ETT performed here may include a series of processes of integrating data included in the ODS and processing to generate a reference relationship between the data having an association. Here, the reference relationship may be configured as an entity-relationship (ER) model. In an embodiment, the step S620 of constructing the DW 120 may include: (i) generating a reference relationship with the data included in the ODS 110 for any data (first data) in the DW 120; and (ii) generating a reference relationship with data (second data) in DW 120. In other words, when generating a relation model constituting the DW 120, not only a reference to data in the ODS 110 associated with the corresponding data, but also a reference relation with other data of the DW 120 may be generated. . In an embodiment, the step S620 of configuring the DW 120 may further include performing data processing to exclude duplication of data included in the DW 120. By removing the duplicated data, the performance of the entire database system 100 may be improved, and a false reference may be prevented. In one embodiment, the DW 120 generated by step S620 may be configured using the key structure of the purified data included in the ODS 110 as it is. By using such a same key structure, even if a reference relationship is made between each other, compatibility in data processing can be achieved.

Once the DW 120 is configured, the business logic 130 may be configured by implementing at least one business rule for analysis (step S630). In one embodiment, the step of configuring business logic (step S630) selects at least one business rule for analysis, checks for duplicates of at least one selected business rule, and then implements a non-overlapping business rule. Can be performed. That is, the business rules implemented in the business logic 130 preferably do not have redundancy, and according to an embodiment, predetermined steps of checking and removing such redundancy may be performed. Here, the step S630 of configuring the business logic is a unique step of the disclosed technology, and in the description, the step S630 is described after the DW is configured (step S620), but this is for convenience of description and the step S630 of configuring the business logic. May be performed at any point in time prior to configuring the data mart.

When the business logic 130 is configured, the data mart 140 is generated by generating a multi-dimensional model for each analysis subject on the data of the ODS 110 or the DW 120 based on one of the business rules of the business logic 130. Can be configured (step S640). Here, the data mart 140 may be configured as a multi-dimensional model generated for at least one analysis subject, and the multi-dimensional model generated for each analysis subject is configured by using data constituting the ODS 110 or the DW 120. Can be.

In one embodiment, step S640 of configuring the data mart 140 selects a business rule in the business logic 140 and at least one of the data of the ODS 110 or the DW 120 based on the business rule. The method may include constructing a multidimensional model for each analysis subject.

In one embodiment, the multi-dimensional model constituting the data mart 140 may be composed of a star schema or a snow flake schema.

When the data mart 140 is configured, a summary table may be configured on at least one subject based on the data mart 140 (step S650).

The disclosed technique can have the following effects. However, since a specific embodiment does not mean to include all of the following effects or only the following effects, it should not be understood that the scope of the disclosed technology is limited by this.

The database system according to an embodiment of the disclosed technology may reduce the cost of redundant development when creating each data mart, and greatly reduce the possibility of error by eliminating such duplication.

A database system according to one embodiment of the disclosed technology can ensure a user access to more useful data. This is because the logic data may be composed of a set of related data, and each model of the data mart may be configured for each problem (issue) to be solved.

A database system according to one embodiment of the disclosed technology may ensure consistency between the source data and the data of the data warehouse. This is because the processing of the data is minimized in constructing the data of the ODS.

A database system according to one embodiment of the disclosed technology can build a data warehouse that is insensitive to changes in the source data system. This is because logic data can act as a buffer between the ODS and the data mart.

Although described above with reference to the preferred embodiment of the present application, those skilled in the art various modifications and changes to the present application without departing from the spirit and scope of the present application described in the claims below I can understand that you can.

Claims

A database construction method for creating a data warehouse based on source data provided from at least one source system,

Refining at least some of the source data to construct an Operational Data Store (ODS);

Integrating data of the ODS and generating a reference relationship between related data to construct a data warehouse (DW);

Constructing business logic by implementing at least one business rule for analysis; And

And constructing a data mart by generating a multi-dimensional model for each analysis subject on the data of the ODS or the DW based on any one of the at least one business rule.
The method of claim 1, wherein configuring the ODS

Loading at least some of the source data;

Verifying the consistency of data with respect to at least some of the loaded source data; And

And if the error data exists, correcting the error data, as a result of verifying the consistency.
The method of claim 1, wherein the step of constructing the DW

Creating a reference relationship with at least one purified data included in the ODS; And

And generating a reference relationship with the data in the DW.
The method of claim 3, wherein configuring the DW comprises:

And deleting the duplicated data with respect to the data included in the DW.
The method of claim 1, wherein the DW

And a key structure of the data loaded in the ODS.
The method of claim 1, wherein configuring the business logic

Selecting at least one business rule for analysis; And

And confirming whether or not the selected at least one business rule is a duplicate.
The method of claim 1, wherein configuring the data mart

Selecting a business rule in the business logic; And

And constructing a multi-dimensional model of at least one of the ODS or the DW based on the business rule based on the business rule.
The method of claim 1, wherein the multi-dimensional model

A method of building a database using a data warehouse, comprising a star schema or a snow flake schema.
The method of claim 1, wherein the database construction method using the data warehouse

And constructing a summary table based on the data mart.
A database system constructed based on source data provided from at least one source system,

An Operational Data Store (ODS) that purifies at least some of the source data;

A data warehouse (DW) for integrating data of the ODS and generating a reference relationship between the related data;

Business logic for managing at least one business rule for analysis; And

A database system using a data warehouse including a data mart for constructing a multi-dimensional model for each analysis subject of the data of the ODS or DW based on any one of the at least one business rule.
11. The method of claim 10, wherein the data warehouse

And a summary table configured based on the data mart for each analysis subject.
11. The method of claim 10, wherein the business logic

And implementing at least one business rule for the analysis without duplication, respectively, and providing the implemented at least one business rule to the data mart.
In the method of building a business system using a database generated from at least one source system,

Integrating the at least one source data and generating an association to construct a data warehouse (DW);

Constructing at least one business logic in accordance with at least one business rule for analysis; And

Selecting at least one of the at least one business rule from the business logic, and integrating the data of the DW using the selected at least one business rule to perform multidimensional modeling to construct a data mart How to build a business system using.