Avoid double counting of mapped database data

Abstract

As disclosed herein a method, executed by a computer, for avoiding double counting in the presence of many-to-many relationships in database data includes receiving a report definition from a data management system, detecting a bridge table defining many-to-many relationships between at least two datasets, generating a query plan with layered sub-queries, and executing the query plan including the layered sub-queries to provide query results corresponding to the report definition. A query plan with layered sub-queries may include instructions to join, partition, normalize, group, and summarize tabular data. Additionally, a conditional selection operation may be used to detect and eliminate duplicated data from a field specified in the report definition. The resulting query plan may be executed as a single query language. A computer program product and an apparatus corresponding to the described method are also disclosed herein.

What is claimed is: 
     
       1. A method, executed by at least one processor, for avoiding double counting of mapped database data, the method comprising:
 receiving a report definition from a data management system, wherein the report definition comprises a request to obtain information from at least two datasets, and wherein the report definition specifies a field in the at least two datasets for deduplication; 
 detecting a fact-less fact table defining many-to-many relationships between at least two datasets; 
 generating automatically, a query plan with layered sub-queries against the at least two datasets, wherein the query plan includes instructions to:
 join the at least two datasets with the fact-less fact table to provide tabular data, 
 partition the tabular data using the fact-less fact table to provide partitioned tabular data, 
 normalize the partitioned tabular data using a conditional selection operation to provide normalized tabular data, wherein the conditional selection operation removes duplicates in the field specified by the report definition, and 
 group and summarize the normalized tabular data to provide summarized tabular data; and 
 
 executing the query plan comprising the layered sub-queries to provide query results corresponding to the report definition. 
 
     
     
       2. The method of  claim 1 , wherein the conditional selection operation comprises selecting a single value from at least two duplicated rows in the partitioned tabular data and selecting a marker value for other rows of the at least two duplicated rows in the partitioned tabular data. 
     
     
       3. The method of  claim 1 , wherein the tabular data does not include a direct projection of a unique identifier of a dimension table or the bridge table. 
     
     
       4. The method of  claim 1 , further comprising independently accumulating a summarization item by aggregating a column in the normalized tabular data. 
     
     
       5. The method of  claim 1 , wherein the query plan is a single query language statement.