Period to date functions for time intelligence functionality

• US 8,364,676 B2
• Filed: 01/23/2012
• Issued: 01/29/2013
• Est. Priority Date: 06/15/2009
• Status: Active Grant

• Current Assignee: Microsoft Technology Licensing LLC (Microsoft Corporation)
• Original Assignee: Microsoft Corporation
• Inventors: Handy, Steve, Tomai, Catalin, Lim, Chen-I
• Primary Examiner(s): Ly, Anh
• Application Number:
  US13/356,496
• Publication Number:
  US 20120124043A1
• Time in Patent Office: 372 Days
• Field of Search: 707/736, 707/E17.014, 707/E17.049, 707/758, 707/801, 707/E17.006, 707/E17.036, 707/E17.009, 707/E17.051, 707/759, 707/713
• US Class Current: 707/736
• CPC Class Codes: G06F 16/2455 Query execution

Abstract

A system for extending a Time Intelligence language to provide support for period-to-date functions and for generating member sets in response to data queries is provided. The system may apply member aggregation functions and queries across a plurality of heterogeneous data sources. Each data source is aligned to a reference dimension and is said to organize data according to at least one level of granularity. In some embodiments, a member aggregation function specifies a period (e.g., year, quarter, month) and retrieves data from a data source starting with the current specified period and ending with the most recently completed period equal to the granularity of the data source. The system may allow a user to further customize a member aggregation function by specifying a granularity, a period offset, or a granularity end offset. Additionally, the system may generate a caption to display in association with the retrieved data.

Claims

1. A computer-readable storage medium storing instructions that, if executed by a computing system, cause the computing system to perform operations comprising:

receiving an indication of a reference dimension specifying a plurality of values;

receiving an indication of a first data source organizing data according to at least a first granularity;

receiving an indication of a second data source organizing data according to at least a second granularity, wherein the second granularity is different from the first granularity;

aligning the first data source to the reference dimension by selecting a value of the first data source and associating the selected value of the first data source with a first value of the reference dimension; and

aligning the second data source to the reference dimension by selecting a value of the second data source and associating the selected value of the second data source with a second value of the reference dimension.

2. The computer-readable storage medium of claim 1 wherein the first data source organizes data according to a first unit of time, wherein the second data source organizes data according to a second unit of time, different from the first unit of time, in a second format, different from a first format, wherein aligning the first data source to the reference dimension comprises selecting a value of the first unit of time in the first format of the first data source and associating the selected value of the first unit of time in the first format of the first data source with a first value of the reference dimension, and wherein aligning the second data source to the reference dimension comprises selecting a value of the second unit of time in the second format of the second data source and associating the selected value of the second unit of time in the second format of the second data source with a second value of the reference dimension.

3. The computer-readable storage medium of claim 1, the operations further comprising:

receiving a query specifying a first query value of the reference dimension;

converting the first query value into a first value of the first data source; and

retrieving data from the first data source based at least in part on the first value.

4. The computer-readable storage medium of claim 3, the operations further comprising:

converting the first query value into a second value of the second data source; and

retrieving data from the second data source based at least in part on the second value.

5. The computer-readable storage medium of claim 4, the operations further comprising:

combining the data retrieved from the first source and the data retrieved from the second source into a single data member.

6. The computer-readable storage medium of claim 3, wherein the query specifies a second query value and wherein the operations further comprise:

converting the second query value into a third value of the first data source,

wherein the retrieved data from the first data source is based at least in part on the third value and wherein the first value and the third value represent a range of values of the first data source.

7. The computer-readable storage medium of claim 1 wherein the reference dimension is the Gregorian calendar.

8. A computing system having a memory and a processor for retrieving data from multiple data sources, at least two of the data sources having a different granularity, the computing system comprising:

a component configured to receive an indication of a period-to-date function specifying a period parameter indicating a period, a period offset parameter indicating a period offset, a granularity parameter indicating a granularity, and a granularity end offset parameter indicating a granularity end offset; and

a component configured to retrieve data from each of the multiple data sources at the granularity indicated by the granularity parameter and spanning a range of dates starting with a beginning of a most recent period, offset by the period offset, and an ending with a current date, offset by both the period offset indicated by the period offset parameter and the granularity end offset indicated by the granularity end offset parameter,

wherein at least one of the components comprises computer-executable instructions stored in memory for execution by the processor.

9. The computing system of claim 8, further comprising:

a component configured to send a notification message in response to determining that the granularity indicated by the granularity parameter is more granular than the granularity of at least one of the multiple data sources.

10. The computing system of claim 8, further comprising:

a component configured to align each of the multiple data sources to a reference dimension comprised of a set of monotonically ordered values, wherein the component configured to align is configured to, for each of the multiple data sources, select a value of the data source and associate the selected value of the data source with a value of the set of monotonically ordered values of the reference dimension.

11. The computing system of claim 10, further comprising:

a component configured to receive a request for data from at least two of the multiple data sources; and

a component configured to retrieve data from the at least two of the multiple data sources.

12. The computing system of claim 11, wherein the received request comprises a first value and wherein the component configured to retrieve data from at least two of the multiple data sources comprises:

a component configured to, for each of the at least two data sources, retrieve an alignment of the data source to the reference dimension,

determine a difference between the first value and an aligned value of the reference dimension, and

determine a converted value based on the determined difference an aligned value of the data source.

13. The computing system of claim 8 wherein at least a first one of the multiple data sources comprises tabular data and wherein at least the second one of the multiple data sources comprises online analytical processing data.

14. The computing system of claim 8, further comprising:

a component configured to generate a caption corresponding to the received period-to-date function and based at least in part on the period specified by the period parameter and the current date.

15. A computer-implemented method performed by a computing system having a processor, the method comprising:

receiving an indication of a reference dimension specifying a first plurality of values; and

aligning each of a plurality of data sources to the reference dimension by, for each data source, selecting a value of the data source and associating the selected value of the data source with a value of the reference dimension,

wherein a first data source organizes data according to a first granularity, wherein a second data source organizes data according to a second granularity, wherein aligning the first data source to the reference dimension comprises selecting a value of the first granularity of the first data source and associating the selected value of the first granularity of the first data source with a first value of the reference dimension, and wherein aligning the second data source to the reference dimension comprises selecting a value of the second granularity of the second data source and associating the selected value of the second granularity of the second data source with a second value of the reference dimension.

16. The method of claim 15, further comprising:

aggregating data in the first data source according to a third granularity different from the first granularity; and

aggregating data in the second data source according to a fourth granularity different from the second granularity.

17. A computer-implemented method performed by a computing system having a processor, the method comprising:

receiving an indication of a reference dimension specifying a first plurality of values; and

aligning each of a plurality of data sources to the reference dimension by, for each data source, selecting a value of the data source and associating the selected value of the data source with a value of the reference dimension;

receiving a request for data from at least two of the plurality of data sources, the request specifying a range of values from among the first plurality of values; and

for each of the at least two data sources,

converting the range of values from among the first plurality of values into values of the data source, and

retrieving data from the data source based at least in part on the converted range of values.

18. The method of claim 17, further comprising:

combining the retrieved data into a single data member.

19. The method of claim 18, further comprising:

receiving a request for data from the single data member; and

retrieving results from the single data member based on the received request for data from the single data member.