Consolidating data source queries for multidimensional scorecards

• US 8,126,750 B2
• Filed: 04/27/2006
• Issued: 02/28/2012
• Est. Priority Date: 04/27/2006
• Status: Active Grant

• Current Assignee: Microsoft Technology Licensing LLC (Microsoft Corporation)
• Original Assignee: Microsoft Corporation
• Inventors: Tien, Ian, Hulen, Corey, Lim, Chen-I
• Primary Examiner(s): Jarrett, Scott L
• Application Number:
  US11/412,458
• Publication Number:
  US 20070254740A1
• Time in Patent Office: 2,133 Days
• Field of Search: 705/7, 705/11
• US Class Current: 705/7
• CPC Class Codes: G06F 16/24539 using cached or materialised query results
• CPC Class Codes: G06F 16/24542 Plan optimisation
• CPC Class Codes: G06F 16/2465 Query processing support for facilitating data mining operations in structured databases
• CPC Class Codes: G06Q 10/063 Operations research, analysis or management

Abstract

Data retrieval from data sources associated with elements of a scorecard is optimized by combining the queries for the same data sources into a single query. The querying process is further optimized based on an attribute and/or condition of a data source. Query parameters and execution schedule may be adjusted based on the elements, data sources, or subscriber identity. Retrieved data is cached at element, combination of elements, data source, or scorecard level for rapid and efficient building and maintenance of scorecards.

Claims

1. A method to be executed at least in part in a computing device for optimizing data retrieval in a scorecard service, the method comprising:

determining a first data source and a second data source associated with a first scorecard element and a second scorecard element, respectively, the first scorecard element and the second scorecard element being part of a scorecard comprising scorecard elements associated with multiple data sources;

receiving a first query for the first scorecard element associated with the first data source;

receiving a second query for the second scorecard element associated with the second data source;

determining whether the first data source and the second data source are the same;

dynamically generating, by a computer, a combined query to retrieve data associated with the first scorecard element and the second scorecard element, when the first data source and the second data source are the same, wherein dynamically generating the combined query comprises rewriting the first query and the second query into the combined query comprising;

a) determining the identity associated with the subscriber requesting one of the first scorecard element and the second scorecard element;

b) employing attributes associated with the identity of the subscriber associated with the combined query and a corresponding permission attribute associated with the subscriber;

c) determining a condition associated with one of the first data source and the second data source, the condition comprising one of;

a measure of data transfer reliability, a time of day, a geographic location of the data source, and a data transfer capacity;

d) optimizing the combined query based on the determined condition;

retrieving results associated with the first scorecard element and the second scorecard element by executing the dynamically generated combined query; and

providing an optimized result set for a scorecard matrix comprising the first scorecard element and the second scorecard element based on the retrieved results of the combined query.

2. The method of claim 1, further comprising:

determining an attribute associated with one of the first data source and the second data source; and

optimizing the combined query based on the determined attribute.

3. The method of claim 2, wherein determining the attribute associated with one of the first data source and the second data source comprises determining the attribute comprising one of:

a data source type and a security protocol associated with the data source.

4. The method of claim 1, further comprising:

adjusting at least one timeout associated with the generated query based on at least one of the following;

an attribute and a condition of the first data source and the second data source.

5. The method of claim 1, further comprising:

assigning at least one timeout associated with the generated query based on at least one of an attribute and a condition of the first data source and the second data source.

6. The method of claim 1, further comprising:

caching the retrieved data at one of;

an element level, a combination of elements level, a data source level, and a scorecard level.

7. The method of claim 6, wherein caching the retrieved data comprises caching the retrieved data to update an existing scorecard.

8. The method of claim 1, wherein dynamically generating the query further comprises generating the combined query to provide an optimized result set for a scorecard matrix based on an attribute associated with one of the first data source and the second data source.

9. The method of claim 1, wherein dynamically generating the combined query comprises adjusting the query based on at least one page filter parameter associated with a scorecard that includes the first scorecard element and the second scorecard element.

10. The method of claim 1, further comprising:

generating the query to accommodate a third scorecard element that is associated with the first data source and the second data source.

11. A computer-readable medium having a set of instructions which when executed performs a method for optimizing data retrieval in a scorecard service, the method executed by the set of instructions comprising:

determining a first data source and a second data source associated with a first scorecard element and a second scorecard element, respectively, the first scorecard element and the second scorecard element being part of a scorecard comprising scorecard elements associated with multiple data sources;

receiving a first query for the first scorecard element associated with the first data source;

receiving a second query for the second scorecard element associated with the second data source;

determining whether the first data source and the second data source are the same;

dynamically generating, by a computer, a third query to retrieve data associated with the first scorecard element and the second scorecard element, wherein dynamically generating the third query comprises rewriting the first query and the second query into a combined query comprising;

a) optimizing the query based on an identity associated with a subscriber requesting one of the first scorecard element and the second scorecard element and a corresponding permission attribute associated with the subscriber;

b) determining a condition associated with one of the first data source and the second data source, the condition comprising one of;

a measure of data transfer reliability, a time of day, a geographic location of the data source, and a data transfer capacity;

c) optimizing the third query based on the determined condition;

comprising;

retrieving results associated with the first scorecard element and the second scorecard element by executing the third combined query; and

providing an optimized result set for a scorecard matrix comprising the first scorecard element and the second scorecard element based on the retrieved results of the third query.

12. The computer-readable medium of claim 11, further comprising dynamically adjusting at least one of a timeout and an execution time for the query based on the condition of each data source.

13. A system for optimizing data retrieval in a scorecard service, the system comprising:

a memory storage; and

a processing unit coupled to the memory storage, wherein the processing unit is configured to;

determining a first data source and a second data source associated with a first scorecard element and a second scorecard element, respectively, the first scorecard element and the second scorecard element being part of a scorecard comprising scorecard elements associated with multiple data sources;

receive, from a subscriber, a first query for the first scorecard element associated with a first scorecard element tied to a first data source,

receive, from the subscriber, a second query for the second scorecard element associated with a second element tied to a second data source,

determine that the first data source and the second data source are the same data source,

determine an identity of the subscriber requesting one of the first scorecard element and the second scorecard element,

determine a permission attribute corresponding to the subscriber, wherein the permission attribute is employed to determine that the subscriber is permitted access to the requested data associated with the first element and the second element,

generate a combined query dynamically to retrieve data associated with the first scorecard element and the second scorecard element, when the first data source and the second data source are the same, based on;

the first query, the second query, and the permission attribute corresponding to the subscriber,

the generate a combined query dynamically comprises rewriting the first query and the second query comprising;

a) determine a condition associated with one of the first data source and the second data source, the condition comprising one of;

a measure of data transfer reliability, a time of day, a geographic location of the data source, and a data transfer capacity,

b) optimize the query based on the determine condition,

retrieve results associated with the first element and the second element by executing the query; and

provide an optimized result set for a scorecard matrix comprising the first element and the second element based on the retrieved results of the query.

14. The system of claim 13, wherein the processing unit is further operative to generate the query based on the attribute and the condition of the data source.

15. The system of claim 13, wherein information about an association of each scorecard metric with a corresponding data source is included in metadata associated with the scorecard metric.