Automated Joining of Disparate Data for Database Queries

• US 20110320433A1
• Filed: 06/25/2010
• Published: 12/29/2011
• Est. Priority Date: 06/25/2010
• Status: Abandoned Application

• Current Assignee: Microsoft Technology Licensing LLC (Microsoft Corporation)
• Original Assignee: Microsoft Corporation
• Inventors: Qadir, Mahmood Gulam, Handler, Jonathan Alan, Feied, Craig F., Shah, Mehul Y., Mohiuddin, Imran, Miao, Yi, Dove, Bryan Jason
• Application Number:
  US12/823,145
• Publication Number:
  US 20110320433A1
• Time in Patent Office: Days
• US Class Current: 707/714
• CPC Class Codes: G06F 16/24535 of sub-queries or views

Abstract

Described is associating metadata with different sources of data (e.g., database tables) that allows a single view of data from the sources to be created. An administrator creates baseviews corresponding to database tables and associates metadata with the baseviews, including primary key metadata for the baseviews and meta-tags for one or more of the columns of each baseview. A user selects fields (corresponding to table columns) from a starting baseview, along with fields from any other baseview that has metadata that matches the starting baseview'"'"'s metadata. A join mechanism automatically creates the view if a metadata match is detected.

Claims

1. In a computing environment, a method performed on at least one processor, comprising, receiving information that identifies a first data source and a second data source, determining whether metadata associated with the first data source matches metadata associated with the first data source, and if so, joining at least part of the data of the first data source with at least part of the data of the second data source to create a view that shows data from the first and second data sources.

2. The method of claim 1 wherein the first and second data sources correspond to first and second baseviews, and wherein receiving the information that identifies the first data source and the second data source comprise detecting user selection of the first and second baseviews.

3. The method of claim 1 wherein determining whether the metadata associated with the first data source matches the metadata associated with the first data source comprises comparing primary key metadata associated with the first data source against primary key metadata associated with the second data source.

4. The method of claim 1 wherein determining whether the metadata associated with the first data source matches the metadata associated with the first data source comprises, comparing key metadata associated with the first data source against one or more meta-tags associated with the second data source, in which at least some of the meta-tags correspond to table columns of the second data source.

5. The method of claim 1 wherein determining whether the metadata associated with the first data source matches the metadata associated with the first data source comprises comparing primary key metadata associated with the first data source against primary key metadata associated with the second data source, and if no match is determined, secondarily comparing at least one meta-tag associated with the first data source against one or more meta-tags associated with the second data source, in which at least some of the meta-tags correspond to table columns of the second data source.

6. The method of claim 5 wherein the secondarily comparing does not find a match, and further comprising, providing suggested join information for manual user selection of a column.

7. The method of claim 1 further comprising, providing an override mechanism for manual user-selection a column for a join.

8. The method of claim 1 wherein joining at least part of the data of the first data source with at least part of the data of the second data source comprises creating an automatic SQL join query.

9. The method of claim 1 further comprising, receiving information that identifies a third data source, determining whether metadata associated with the third data source matches metadata associated with the first data source or the second data source, and if so, joining at least part of the data of the third data source with the joined data of the first and second data sources to create another view that shows data from the first, second and third data sources.

10. The method of claim 1 further comprising, maintaining the view in a library.

11. In a computing environment, a system comprising:

a user interface, including a mechanism that allows a user to select a first baseview corresponding to a first database table, to add fields corresponding to columns of the first database table to a userview, and to select a second baseview corresponding to a second database table; and

a join mechanism coupled to the user interface, the join mechanism configured to determine whether one or more fields corresponding to one or more columns of the second database table are allowed to be added to the userview based upon first metadata representing join rules associated with the first database table and second metadata representing join rules associated with the second database table.

12. The system of claim 11 wherein the user interface includes a dynamic view builder for adding the fields, including an interactive available fields region that shows fields available for adding by user selection, and a selected fields region that shows any fields that have been selected and added for inclusion in the userview.

13. The system of claim 11 wherein the join mechanism creates the userview when the first metadata matches the second metadata.

14. The system of claim 11 wherein the join mechanism determines whether the one or more fields are allowed to be added to the userview by comparing primary key metadata of the first database table with primary key metadata of the second database table, and allowing the one or more fields to be added if the key metadata matches.

15. The system of claim 11 wherein the join mechanism determines whether the one or more fields are allowed to be added to the userview by comparing at least one meta-tag associated with the first database table with at least one meta-tag associated with the second database table and allowing the one or more fields to be added if a meta-tag matches.

16. The system of claim 11 wherein the join mechanism determines whether the one or more fields are allowed to be added to the userview by comparing primary key metadata of the first database table with primary key metadata of the second database table, and allowing the one or more fields to be added if the key metadata matches, and if the key metadata does not match, by comparing at least one meta-tag associated with the first database table with at least one meta-tag associated with the second database table and allowing the one or more fields to be added if a meta-tag match is detected.

17. The system of claim 11 wherein the join mechanism includes means for providing suggested join information for manual user selection of a column.

18. The system of claim 11 wherein the join mechanism includes an override mechanism for manual user-selection a column for a join.

19. One or more computer-readable media having computer-executable instructions, which when executed perform steps, comprising:

detecting user interaction that identifies columns of a first database table;

accessing first metadata associated with the first database table;

detecting user interaction that identifies columns of a second database table;

accessing second metadata associated with the second database table;

determining whether the first metadata matches the second metadata, and if so, creating a view that presents information from the first database table and the second database table by using a join query.

20. The one or more computer-readable media of claim 19 wherein determining whether the first metadata matches the second metadata comprises comparing primary key metadata of the first database table with primary key metadata of the second database table, and creating a view if the primary key metadata matches, and if the key metadata does not match, by comparing at least one meta-tag associated with the first database table with at least one meta-tag associated with the second database table and creating the view if a meta-tag match is detected.