# US 2020/0394201 A1 · Automatic modeling method and classifier for olap data model

> Agents: this data is available as JSON from the patentgrep API and MCP server — see https://patentgrep.com/llms.txt

- Number: US 2020/0394201 A1
- Document ID: US20200394201A1
- Status: Published application
- Kind: A1
- Application number: 15/769,397
- Filed: 2018-01-19
- Published: 2020-12-17
- Earliest claimed: 2017-12-29
- Assignees: Shanghai Kyligence Information Technology Co., Ltd.
- Applicants: SHANGHAI KYLIGENCE INFORMATION TECHNOLOGY Co.,Ltd.
- Inventors: Dong LI; Yifan ZHANG; Yang LI; Qing HAN
- Web page: https://patentgrep.com/patent/US20200394201A1

## CPC

- G06F 16/212: {with details for data modelling support}
- G06F 16/254: {Extract, transform and load [ETL] procedures, e.g. ETL data flows in data warehouses}
- G06F 16/2282: {Tablespace storage structures; Management thereof}
- G06F 16/2455: Query execution
- G06F 16/258: {Data format conversion from or to a database}
- G06F 16/283: {Multi-dimensional databases or data warehouses, e.g. MOLAP or ROLAP}
- G06F 16/285: {Clustering or classification}
- G06N 5/04: Inference or reasoning models
- G06N 20/00: Machine learning
- G06F: ELECTRIC DIGITAL DATA PROCESSING (computer systems based on specific computational models G06N)
- G06N: COMPUTING ARRANGEMENTS BASED ON SPECIFIC COMPUTATIONAL MODELS

## Abstract

The automatic modeling method and a classifier for OLAP data model includes acquiring a SQL query statement; parsing the SQL query statement and judging whether any syntax error or lexical error exists in the SQL query statement; extracting query structures from the SQL query statement to obtain at least N sets of query patterns, if no syntax error or lexical error exists; classifying the at least N sets of query patterns; merging the query patterns in each class to obtain a corresponding pattern relation tree; and converting the pattern relation tree into a corresponding data model. The classifier includes a static rule classification module, and a statistical rule and preset learning rule classification module. A data model generated with the method and classifier provided in the present invention supports all inputted SQL query statements and has certain generic capabilities to full support Cube operations and quick queries.

## Claims

### Claim 1

An automatic modeling method for OLAP data model, comprising the steps of:
S1. acquiring a SQL query statement;
S2. parsing the SQL query statement and judging whether any syntax error or lexical error exists in the SQL query statement according to a parsing result;
S3. extracting query structures from the SQL query statement according to column information and table information related with the source data used by the SQL query statement to obtain at least N sets of query patterns, if no syntax error or lexical error exists, where, N is a natural number greater than or equal to 1;
S4. classifying said at least N sets of query patterns;
S5. merging the query patterns in each class to obtain a corresponding pattern relation tree; and
S6. converting the pattern relation tree into a corresponding data model.

### Claim 2 (depends on 1)

The method according to claim 1,
wherein the column information refers to all columns used in the SQL query statement,
wherein each column comprises: name of column, table of column, type of column, and number of occurrences of the column,
wherein the table information refers to all tables used in the SQL query statement, and
wherein each table comprises: name of table, type of table, and information on association between the table and a fact table.

### Claim 3 (depends on 2)

The method according to claim 2,
wherein the operation of classifying said at least N sets of query patterns in the step S4 comprises the steps of:
S41: judging the differences between any two sets of query patterns among said at least N sets of query patterns according to static rules,
wherein the static rules refer to:
calculating the degree of difference between identical tables in any two sets of query patterns and judging whether the degree of difference between the identical tables is greater than a first preset threshold, and the two sets of query patterns cannot be classified into the same class if the degree of difference is greater than the first preset threshold; and
calculating the degree of difference between identical columns in any two sets of query patterns and judging whether the degree of difference is greater than the first preset threshold after the degree of difference between the tables are calculated, and the two sets of query patterns can't be classified into the same class if the degree of difference is greater than the first preset threshold; and
S42: performing clustering for said at least N sets of query patterns according to statistical rules and preset learning rules, after the differences are judged.

### Claim 4 (depends on 3)

The method according to claim 3,
wherein the operation of performing clustering of said at least N sets of query patterns according to statistical rules and preset learning rules in the step S42 comprises the steps of:
S421: calculating an eigenvector of each set of query patterns according to column information and table information used in each set of query patterns; and
S422: performing clustering of the eigenvectors according to a clustering algorithm in unsupervised machine learning and the preset learning rules.

### Claim 5 (depends on 4)

The method according to claim 4,
wherein the preset learning rules refer to:
judging whether the clustering result meets preset criteria after the clustering,
adjusting the clustering result with a supervised machine learning algorithm, and
logging the adjusted clustering result if the clustering result doesn't meet the preset criteria.

### Claim 6 (depends on 3)

The method according to claim 3,
wherein the operation of merging the query patterns in each class to obtain a corresponding pattern relation tree in the step S5 comprises the steps of:
calculating the degree of difference between identical tables in any two sets of query patterns in each class and judging whether the degree of difference is smaller than a second preset threshold, and merging the two sets of query pattern to obtain a corresponding pattern relation tree if the degree of difference is smaller than the second preset threshold; and
calculating the degree of difference between identical columns in any two sets of query patterns in each class, judging whether the degree of difference is smaller than the second preset threshold, and merging the two sets of query patterns to obtain a corresponding pattern relation tree if the degree of difference is smaller than the second preset threshold, after the degree of difference between tables in each class is calculated.

### Claim 7 (depends on 3)

The method according to claim 3,
wherein the operation of converting the pattern relation tree into a corresponding data model in the step S6 comprises the steps of:
converting the table information in the pattern relation tree to corresponding table information in the data model; and
converting the column information in the pattern relation tree into corresponding column information in the data model.

### Claim 8 (depends on 7)

The method according to claim 7,
wherein the operation of converting the column information in the pattern relation tree into corresponding column information in the data model further comprises the steps of:
carrying out partitioned scoring of the columns in the pattern relation tree, to ascertain partitioned columns in the data model,
wherein the partitioned scoring of the columns in the pattern relation tree is carried out with the following calculation formula:
PartScore(i)=PartFunc(Score(i),Stats(i)),
where, PartFunc( ) is the scoring function, Score(i) is the score of column (i) in each set of query patterns, Stats(i) is the characteristic statistical value of column (i), and, if the partitioned score PartScore(i) of column (i) exceeds a preset score threshold, the column (i) will be set as a partitioned column.

### Claim 9

A classifier, comprising:
a static rule classification module; and
a statistical rule and preset learning rule classification module,
wherein the static rule classification module is configured to judge the differences between any two sets of query patterns among said at least N sets of query patterns according to static rules,
wherein the static rules refer to:
calculating the degree of difference between identical tables in any two sets of query patterns and judging whether the degree of difference between the identical tables is greater than a first preset threshold, and the two sets of query patterns cannot be classified into the same class if the degree of difference is greater than the first preset threshold, where N is a natural number greater than or equal to 1; and
calculating the degree of difference between identical columns in any two sets of query patterns and judging whether the degree of difference is greater than the first preset threshold after the degree of difference between the tables are calculated, and the two sets of query patterns can't be classified into the same class if the degree of difference is greater than the first preset threshold, and
wherein the statistical rule and preset learning rule classification module is configured to perform clustering of said at least N sets of query patterns according to statistical rules and preset learning rules, after the differences are judged.

### Claim 10 (depends on 9)

The classifier according to claim 9,
wherein the preset learning rules refer to:
judging whether the clustering result meets preset criteria after the clustering,
adjusting the clustering result with a supervised machine learning algorithm, and
logging the adjusted clustering result if the clustering result doesn't meet the preset criteria.
