Identifying data relationships from a spreadsheet
Proposed are concepts for identifying data relationships from a spreadsheet. Such a concept may transform formulae by replacing the variables in each formula with descriptive labels. This may, for example, expressing the transformed formulae in terms that have more meaning to a user, the facilitating understanding and/or analysis that would otherwise not be possible with the existing tools.
1. A computer-implemented method for identifying data relationships from a spreadsheet comprising a plurality of formulae, the method comprising:
grouping semantically equivalent formulae of the spreadsheet to define a first group of semantically equivalent formulae, each of the formulae in the first group expressing a same concept; and
transforming each formula of the first group by replacing variables in each formula with descriptive labels by:
identifying one or more tables comprising numerical data from the spreadsheet by identifying tabular structures in the spreadsheet;
determining labels from extracted one or more tables, the labels determined using a natural language processing algorithm to determine labels from identified tabular structures in the spreadsheet;
grouping the determined labels into OLAP dimensions by identifying relationships between columns or rows of the table based upon a minimum distance, and merging columns or rows of the table based on the identified relationships; and
replacing cell references of each formula with corresponding members of the OLAP dimensions.
2. The method of claim 1 , wherein identifying tabular structures within the spreadsheet further comprises:
classifying each the identified tabular structures as one of a column-based table and a crosstab type.
3. The method of claim 1 , wherein replacing cell references of each formula further comprises:
identifying a cell reference in a formula; and
replacing the identified cell reference with an OLAP dimension of the corresponding table.
4. The method of claim 3 , wherein replacing cell references of each formula further comprises:
removing an OLAP dimension of a formula if the formula is present in all member assignments of the OLAP dimension.
5. A system for identifying data relationships from a spreadsheet comprising a plurality of formulae, the system comprising:
a formula analysis component configured to group semantically equivalent formulae of the spreadsheet to define a first group of semantically equivalent formulae, each of the formula in the first group expressing a same concept; and
a transformation unit configured to transform each formula of the first group by replacing variables in each formula with descriptive labels, the transformation unit comprising:
a table identification unit configured to identify one or more tables comprising numerical data from the spreadsheet by identifying tabular structures in the spreadsheet;
a table analysis component configured to determine labels from the extracted one or more tables, the labels determined using a natural language processing algorithm to determine labels from the identified tabular structures in the spreadsheet;
a label analysis component configured to group the determined labels into OLAP dimensions; and
a formula processing unit configured to replace cell references of each formula with corresponding members of the OLAP dimensions, the formula processing unit configured to identify a cell reference in one or more formulae and replace the identified cell reference with an OLAP dimension of the corresponding table.
6. The system of claim 5 , wherein the table identification unit is configured to:
classify each the identified tabular structures as one of a column-based table and a crosstab type.
7. The system of claim 5 , wherein the label analysis component is configured to:
for each of the one or more tables: identify relationships between columns or rows of the table based upon a minimum distance; and merge columns or rows of the table based on the identified relationships.
8. The system of claim 5 , wherein the formula processing unit is configured to:
remove an OLAP dimension of a formula if the formula is present in all member assignments of the OLAP dimension.