Methods to identify related data in a multidimensional database

Methods that identify data that is related to and associated with data that has been selected from a multidimensional database. The overwhelming amount of data in a multidimensional database that may be viewed by a user, such as a data analyst, is reduced to the selected and associated data by use of index data and related index data, according to the present invention. The views of selected data and related data may be highlighted and formatted for presentation to the user. Further, irrelevant data is filtered out and not presented to the user. Existing systems have not been able to efficiently and adequately identify data that is related to and associated with selected data in a multidimensional database.

CROSS-REFERENCE TO RELATED APPLICATION

In co-pending U.S. application Ser. No. 09/565,132 entitled “Navigating an Index to Access a Subject Multi-Dimensional Database,” by William Earl Malloy et. al, assigned to the assignee of the present invention, and incorporated herein in its entirety by this reference, there is described a method, apparatus, and article of manufacture for using an index to access a subject multidimensional database. Although not limited thereto, the present invention employs such a method in one of its preferred embodiments.

In U.S. application Ser. No. 09/564,344 entitled “Using an Index to Access A Subject Multi-Dimensional Database,” by William Earl Malloy et. al, assigned to the assignee of the present invention, and incorporated herein in its entirety by this reference, there is described a method, apparatus, and article of manufacture for using an index to access a subject multidimensional database. Although not limited thereto, the present invention employs such a method in one of its preferred embodiments. U.S. application Ser. No. 09/564,344 issued as U.S. Pat. No. 6,915,289 on Jul. 5, 2005, and a Statutory Disclaimer was filed for U.S. Pat. No. 6,915,289 on Jan. 10, 2007.

In U.S. patent application Ser. No. 09/998,955, “Systems, Methods, and Computer Program Products to Interpret, Explain, and Manipulate Exceptions in Multidimensional Data,” by Kelkar et al., assigned to the assignee of the present invention, there is described a method, apparatus, and article of manufacture for interpreting, explaining, and manipulating exceptions in multidimensional data on a computer system. Although not limited thereto, the present invention employs such a method in one of its preferred embodiments. U.S. application Ser. No. 09/998,955 issued as U.S. Pat. No. 6,654,764 on Nov. 25, 2003, and a Statutory Disclaimer was filed for U.S. Pat. No. 6,654,764 on Jan. 10, 2007.

BACKGROUND OF THE INVENTION

1. Field of the Invention

The present invention is directed to the field of computer-based multidimensional data modeling. It is more particularly directed to identifying data that is related to selected data in a multidimensional database on a computer system.

2. Description of the Background Art

On-Line Analytical Processing (OLAP) is a computing technique for summarizing, consolidating, viewing, analyzing, applying formulae to, and synthesizing data according to multiple dimensions. OLAP software enables users, such as analysts, managers, and executives, to gain insight into performance of an enterprise through rapid access to a wide variety of data “views” or “dimensions” that are organized to reflect the multidimensional nature of the enterprise performance data. An increasingly popular data model for OLAP applications is the multidimensional database (MDDB), which is also known as the “data cube.” OLAP data cubes are often used by a data analyst for interactive exploration of performance data. New opportunities associated with the enterprise may be discovered by identifying relationships and associations in the data.

OLAP functionality is characterized by dynamic multidimensional analysis of data supporting end user analytical and navigational activities including: calculation and modeling applied across dimensions through hierarchies or across members, trend analysis over sequential time periods, slicing subsets for on-screen viewing of the multidimensional data, drill-down to deeper levels of consolidation of the multidimensional data, reach-through to underlying detail data, and rotation to new dimensional comparisons in the viewing area associated with the multidimensional data. It is frequently difficult to efficiently analyze multidimensional data due to the lack of referential information about the association of the data to other neighboring and possibly related multidimensional data.

A multidimensional OLAP system typically has multiple dimensions and may have members within each dimension. A member may be considered a name of a category used in multidimensional analysis. That is, a member may be a label associated with an edge that edge being a dimension in a multidimensional data cube. For example, “March” could be a member that identifies information that was stored relating to the month of March. Such a system that supports a multidimensional data cube is often very large, and it may be difficult to identify where the most interesting features are in a vast pool of data. More particularly it is often difficult and time consuming to identify and analyze the most interesting features when the relationship and association between the data in a multidimensional data cube is unclear.

In order to facilitate access of information in a data cube, an index that may be represented in an index data cube and that references data in the data cube may be generated. The index may be used to access selected information in a data cube more efficiently than access techniques that do not employ an index. Given an index that is used to access and select particular multidimensional data, it would be useful to present the context in which the selected multidimensional data is located as a representation of neighboring or associated multidimensional data.

From the foregoing it will be apparent that there is still a need to improve OLAP data analysis by determining the relationship between selected multidimensional data and neighboring or associated multidimensional data on a computer system. More particularly, existing systems have not been able to adequately and efficiently determine the relationship between neighboring or associated data that may be configured in a database and that is associated with selected multidimensional data.

SUMMARY OF THE INVENTION

An embodiment of the present invention relates to systems, methods, and computer products that identify data that are related to selected data in a database on a computer system. The present invention is related to the field of computer-based multidimensional data modeling often used by data analysts.

The present invention assists the data analyst by efficiently identifying and more clearly presenting data that is related to or associated with selected multidimensional data such as selected data that is discussed with reference to U.S. patent application Ser. No. 09/565,132, “Navigating an Index to Access a Subject Multi-Dimensional Database.” Existing systems have not been able to efficiently and adequately identify data that is related to, or associated with, selected data in a multidimensional database.

The preferred embodiment of the present invention operates efficiently with an index that includes location and magnitude information about features in a subject multidimensional database. More particularly, selected data has been extracted from the subject multidimensional database and the preferred embodiment of the present invention uses a Related Data Identifier Module to access information in the index that is used to identify data that is related to and associated with the selected data. The overwhelming amount of data in a multidimensional database that may be viewed by a user, such as a data analyst, is efficiently and advantageously reduced, by the preferred embodiment of the present invention, to the selected and related data.

The preferred embodiment of the present invention operates on features of selected data that may be presented in a two-dimensional view or multi-dimensional view, and enhances the view with associated data that may be uniquely formatted. The associated data is related to the selected data along the dimensions of the view that are used to present the selected data. Further, the preferred embodiment of the present invention may identify the associated data as sibling data of the selected data. The sibling data is at the same level of aggregation in the subject multidimensional database as the selected data. The preferred embodiment of the present invention novelly accesses the selected data by using index data that is not necessarily at the same hierarchy level and the index data is not necessarily represented as a multidimensional database.

An embodiment of the present invention is achieved by systems, methods, and articles of manufacture that use an index that identifies data that is related to selected data in a multidimensional database on a computer system. The method of the preferred embodiment of the present invention comprises: (a) obtaining selected data, typically from a subject multidimensional database by the use of index data; (b) creating a view of the selected data; (c) creating a query that accesses the index data to obtain related index data; and (d) obtaining a view of the identified related data via the use of the related index data. Further the method may comprise: (e) creating a view of the related data; (f) formatting the view of the related data and the selected data for presentation to a user; and (g) highlighting attributes of the related data and the selected data in the view based on highlighting criteria. One embodiment of the present invention may also suppress information about the selected data in the subject database that is not interesting, such as empty data points. This enables the user to view related data without the inclusion of irrelevant data.

DESCRIPTION OF THE INVENTION

As shown in the drawings and for purposes of illustration, the preferred embodiment of the invention novelly identifies data that is related to and associated with data that has been selected from a multidimensional database. The overwhelming amount of data in a multidimensional database that may be viewed by a user, such as a data analyst, is reduced to the selected data that is presented along with multidimensional data that is related to and associated with selected data. According to the present invention, irrelevant data is filtered out and not presented to the user thereby providing a simplified view of the data for analysis. Existing systems have not been able to efficiently and adequately provide contextual information about selected data by identifying data that is related to and associated with selected data in a multidimensional database.

As shown inFIG. 1and in element100, the preferred embodiment of the present invention may operate in a client server computer system configuration. It will be appreciated that other computer system environments may be used to practice the present invention and the described computer system environment should not be considered limiting. Therefore, a client computer system104may communicate with a server computer system102during the operation of the present invention. The related Data Identifier Module120operates in either the client104or the server102to perform the preferred embodiment of the present invention. For example, information may be communicated to either the server102or the client104via the user interface117. Through such communication, data selection criteria132and data report highlight criteria130may be established and may subsequently be used by the Related Data Identifier Module120to manipulate data108, such as multidimensional data110. The user interface117may communicate with the preferred embodiment of the present invention, either via batch input119or user input118.

Further, an OLAP data cube106may be configured in the memory558of either the client104or the server102. Alternatively, the OLAP data cube106may be configured in computer storage such as a disk122. Typically, the OLAP data cube106is configured in computer storage of a disk122associated with the server102, and the client104typically accesses portions of the multidimensional data110. The terms “OLAP data cube,” “data cube,” and “multidimensional database” will be used interchangeably herein. Element558is described with reference toFIG. 5.

The operation of the present invention uses a subject data cube126and an index data cube124. The subject data cube126is the multidimensional database110that is the subject of analysis by the user. The index data cube124is used to access selected data304in the subject data cube126. The index data cube124and the subject data cube126may be located in the memory558of a computer system500or on another computer media, such as the disk122. The preferred embodiment of the present invention novelly uses a data cube106to represent the index data cube124so that a multidimensional query may be used to determine the data108that is related to and associated with the information in the subject data cube126element304is described with reference toFIG. 3, and element500is described with reference toFIG. 5.

FIG. 2includesFIG. 2A,FIG. 2B, andFIG. 2C. As shown inFIG. 2A, an OLAP data cube106is suitably configured for operation with the present invention. Therefore, by means of explanation, an example of the operation of the present invention is described. The view212that may be represented as a row or column may be included in an OLAP data cube106, and is organized to reflect the multidimensional nature of the enterprise performance data108. In the present invention views212, particularly two-dimensional views212that are used to view selected data304in context, are generated by selecting pairs of dimensions213from the subject data cube126and slicing the subject data cube126along those dimensions213so that the data point from the subject data cube126is included in the slice. For example, in the present example the three dimensions213are included in planes that are sliced by either a row or a column and create the following views212: view i*j, as shown in element232, view I*k, as shown in element230, and view j*k, as shown in element234. Elements106,108, and126are described with reference toFIG. 1, and element304is described with reference toFIG. 3.

Further, a dimension is a set or collection of related categorical elements, as defined by an analytic model. For example, all time periods in a fiscal calendar (including defined groups of periods such as weeks, months, quarters, and years) might form a Time Dimension208. In the same way all product identifiers (including groups of products) might form a Product Dimension202. Herein the Market Dimension216may be used to identify all product markets, such as East256, West258, and Central260.

A multidimensional data cube106typically includes data108in a hierarchical structure that may be represented as aggregated levels. In the present example, the actual name of the Time dimension208is Year240. The Year dimension240may be decomposed into levels. In the present example the Time dimension208includes a quarter level with: Qtr1, as shown in element242, Qtr2, as shown in element244, Qtr3, as shown in element246, and Qtr4, as shown in element248that are siblings with respect to the hierarchical structure represented. The Product dimension202may be decomposed into information about the following products: Soda250, Milk252, and Wine254.

FIG. 2Bis a block diagram that illustrates the conceptual structure of a subject multidimensional database126. Therefore, by means of example the subject multidimensional database126includes information about the Year240, Products202, and Market216. The information may be organized in a hierarchical fashion. Therefore the information about a Year240may include information about quarters such as: Qtr1as shown in element242, Qtr2as shown in element244, Qtr3as shown in element246, and Qtr4as shown in element248. Information at the same hierarchy level may also be presented for Product data202and Market data216. The Product data202may include information about: Soda250, Milk252, Wine254, and Beer255. The Market information216may include information about: the East256market, the West258market, and the Central260market. The subject database126often includes a Measures Dimension290that enumerates the quantities that are stored in the multidimensional database110, such as Profit, Income, Expense, and Inventory. In the present example, the Measures Dimension290has a single member, Sales292.

As shown inFIG. 2C, the Related Data Identifier Module120includes elements used in the preferred embodiment of the present invention. The Related Data Identifier Module120is typically program code that may be embodied as a computer program542(as shown inFIG. 5), and by means of example an embodiment of the Related Data Identifier Module120is described in U.S. patent application Ser. No. 09/565,132, “Navigating an Index to Access a Subject Multi-Dimensional Database.” The Index Module270is used by an embodiment of the present invention to determine an index that is used to access the subject data cube126. The preferred embodiment of the present invention creates the index data cube124that is used to access the subject data cube126. Elements124and126are described with reference toFIG. 1.

The Dimension Identifier Module272is used to identify selected data304that is represented as dimensions213for a preferred projection of multidimensional data110, such as is described in U.S. patent application Ser. No. 09/998,955, “Systems, Methods, and Computer Program Products To Interpret, Explain, and Manipulate Exceptions in Multidimensional Data.” The related Data Capture Module274is used to access the index data cube124and identify related data306that is associated with the selected data304by use of the related index data310. The Query Generator276is used to generate a query that is executed against the index data cube124to determine the related index data310. The Report Generator278is used to generate a report of the selected data304and the related data306. Such a report is shown in Table 6 herein. Further, the Highlight Generator280is used to highlight certain attributes of the selected data304and related data306that meet highlight criteria130. Elements110and130are described with reference toFIG. 1, element213is described with reference toFIG. 2A, and elements304,306, and310are described with reference toFIG. 3.

FIG. 3includesFIG. 3A,FIG. 3B, andFIG. 3C.FIG. 3A, as shown in element300, illustrates the novel operation of the Related Data Identifier Module120. The Related Data Identifier Module120obtains selected data304from a subject data cube126. The subject data cube126is created from multidimensional data110that is typically stored on a disk122. Element212is described with reference toFIG. 2.

The Related Data Identifier Module120novelly operates according to the present invention by performing multidimensional queries on the index data cube124in order to identify related index data310. Since the index data cube124is typically sparse, identifying related index data310by the use of multidimensional queries is an efficient way of identifying, in a single operation, all selected data304in a given view212of the subject data cube126that is referenced by the index data124. Therefore, and in the preferred embodiment of the present invention, the index data cube124is represented as a multidimensional cube106so that the multidimensional queries directed to the index data cube124may be performed efficiently. Further, the members of identified selected data304determine the predicate or predicates of the multidimensional queries. Therefore, the Related Data Identifier Module120may novelly identify related data306that is associated with the selected data304by use of multidimensional queries directed to the related index data310. Further the selected data304and the related data306may be presented to the user in a view212, such as a selected data view314and a related data view316.

FIG. 3B, as shown in element124, illustrates the conceptual structure of the index data cube124. In one embodiment of the present invention, a system is based on the product marketed under the trademark Hyperion® Essbase® OLAP software. The present invention utilizes a number of components from the Essbase® OLAP system, including the outline illustrated herein. However, since the present invention comprises new elements that enable identification of related data306that is associated with selected data304that has been selected from a multidimensional database110, those skilled in the art will recognize that the present invention may be practiced without components based on Hyperion® Essbase® OLAP software. For example, there are a number of multidimensional systems that use Multidimensional Expressions (MDX) as their query language. Element110is described with reference toFIG. 1, and elements304and306are described with reference toFIG. 3A.

In the present example, the multidimensional structure of the index data cube124may be represented as an outline, as shown in element320. The Measures Dimension290has only a single member, Deviation Magnitude332. The index data cube124identifies cells that fall within particular thresholds that are applied to the data in the subject database126. Further, as shown in Table 1, the present example of the index data cube124includes information about the values of Sales292having Deviation Magnitudes332at or above eight. Therefore in the present example the Products202shown are: Soda as shown in element250, and Beer as shown in element255. The Deviation Magnitudes332shown in Table 1 represent the deviations from the mean for the identified Sales292figures and serve to standardize the information that is presented. Element126is described with reference toFIG. 1, and element272is described with reference toFIG. 2.

It will be appreciated by those skilled in the art that there are many methods of calculating a deviation magnitude332associated with data108in the multidimensional data cube110. Such a method is described in U.S. Pat. No. 6,094,651, “Discovery-Driven Exploration of OLAP Data Cubes.” Other index data cubes124in the present example that are associated with the Market data214are East as shown in element256and Central as shown in element260. Element108is described with reference toFIG. 1.

Given the index data cube124illustrated inFIG. 3B, further information about a particular row may be obtained by operation of the present invention. For example, the feature shown in Table 2, is an example of related index data310that is discussed with reference toFIG. 3A. Therefore in this example, the related index data310of Table 2 may drive the operation of the present invention.

As shown in Table 3, subject data304is used to present related data306that is located by use of information in the related index310. Therefore, as shown in Table 3, Sales292data about Soda250in all Markets216and for all quarters in the Year240is described.

Continuing with the current example, the information associated with three entries in Table 3 falls within a threshold, such as highlight criteria130, that may be set by the user. It may be understood that the highlight criteria130enables the user or an administrator to constrain the points that are highlighted for any purpose, such as security or specialized analysis. Therefore, since the data shown in Table 4 falls within a threshold it represents related data306with Sales292figures of interest. The fact that the data for East256in Qtr1242falls within the identified threshold is not a surprise because the view of data in Table 3 was driven by the selection of the index entry shown in Table 3. However, with very large subject data cubes126and complex methods for determining which features to include in the index data cube124, it may not be apparent that the Sales292figures in the last two rows of Table 4 also fall within the identified threshold specified in the creation of the index data cube124. Element130is described with reference toFIG. 1.

Now, as shown inFIG. 3Cand element340, a query of the index data cube124is performed according to the preferred embodiment of the present invention. The Essbase® report script language query is shown inFIG. 3C. However, it will be appreciated by those skilled in the art that the present invention may be practiced with components based on multidimensional software other than Hyperion® Essbase® OLAP software. The example query fixes on non-expanded members of the related data306, namely Soda250and Sales392, as shown in lines2and3. The other two dimensions213, Year240and Market216are chosen to generate the view212and as shown in element344the report script declares these two dimensions213as row labels in line4.

The present example shows that a dimension213is novelly used by the present invention to create a representation of selected data304and related data306. For each selected dimension213, a representative set of siblings (members of the same level) of the member that identifies the selected data304within that dimension213, are used to retrieve related data306. By means of further explanation, the related data306are retrieved by accessing combinations of members that are at the same level of aggregation, in the hierarchies of the Subject Data Cube126as the Selected Data304. For example sibling members include Qtr4as shown in element248, and Central as shown in element260, respectively. In the present example, the sibling members are obtained by use of the directives shown in elements346and348, and are further shown with reference to Table 6. Since the index data cube124contains only indexed features of the subject database126the result is highly sparse and the query operation is highly efficient. The query340result of the search of the index data cube124, the related index data310, is shown in Table 5. Notice that the data points that are not interesting, such as empty data points, have been suppressed in the reporting of the indexed features of the subject database126. Elements110,124, and126are described with reference toFIG. 1; elements202,204,212,213,216,234,240,248and250are described with reference toFIG. 2; elements304,306, and310are described with reference toFIG. 3A; and element340is described with reference toFIG. 3C.

The view212of the subject database126is now augmented by the preferred embodiment of the present invention by highlighting the features identified by the query. The resulting view212of the subject database126shows the selected data304in context with other multidimensional data110at the same level of aggregation. Further, related data306are identified by use of the related index data310and in the present example the related data306in the related data view316are shown in bold format. The selected data point304is shown within a selected data view314in both bold and underlined format. Therefore, the selected data304and the related data306may be formatted and presented to the user in one highlighted view212, as shown in Table 6. While the present example has included two-dimensional views212, the operation of the present invention is not limited to two-dimensional views212and may operate with multi-dimensional views212. Elements314and316are described with reference toFIG. 3A

FIG. 4and element400illustrate the preferred method of the present invention that identifies data that are related to selected data304in a multidimensional database110on a computer system500. The method of the preferred embodiment of the present invention comprises obtaining selected data304, typically from a subject multidimensional database126, as shown in element402. The selected data304is obtained via the use of the index data cube124. As shown in element404, a view of the selected data314is obtained. A query of the index data cube124is created, as shown in element406, and as shown in element408, the query accesses the index data cube124to obtain related index data310. As shown in element410, a view of the identified related data316is obtained via the use of the related index data310. Elements124and126are described with reference toFIG. 1, elements304,306,310,314, and316are described with reference toFIG. 3, and element500is are described with reference toFIG. 5.

Further, the view of the related data316and the selected data314may be formatted for presentation to a user, as shown in element412. The attributes of the related data306and the selected data304that meet the threshold set by highlight criteria130may be highlighted, as shown in element414. As shown in element416, the preferred embodiment of the present invention may also suppress irrelevant data108that exists in the subject database126that might otherwise be included as part of a view212showing selected data304in context. Elements108and130are described with reference toFIG. 1, and element212is described with reference toFIG. 2.

FIG. 5is a block diagram of a computer system500, suitable for employment of the present invention. System500may be implemented on a general-purpose microcomputer, such as one of the members of the IBM Personal Computer family, or other conventional workstation or graphics computer devices, or mainframe computers. In its preferred embodiment, system500includes a user input device510, a display515, a printer520, a processor555, a read only memory (ROM)550, a data storage device122, such as a hard drive, a random access memory (RAM)540, and a storage media interface535, all of which are coupled to a bus525or other communication means for communicating information. Although system500is represented herein as a standalone system, it is not limited to such, but instead can be part of a networked system. For example, the computer system500may be connected locally or remotely to fixed or removable data storage devices122and data transmission devices545. Further, the computer system500, such as the server computer system102and the client computer system104, also could be connected to other computer systems500via the data transmission devices545. Elements102and104are described with reference toFIG. 1.

The RAM540, the data storage device122, and the ROM550, are memory components558that store data108and instructions for controlling the operation of processor555, which may be configured as a single processor or as a plurality of processors. The processor555executes a program542to perform the methods of the present invention, as described herein. Element108is described with reference toFIG. 1.

While the program542is indicated as loaded into the RAM540, it may be configured on a storage media530for subsequent loading into the data storage device122, the ROM550, or the RAM540via an appropriate storage media interface535. Storage media530can be any conventional storage media such as a magnetic tape, an optical storage media, a compact disk, or a floppy disk. Alternatively, storage media530can be a random access memory540, or other type of electronic storage, located on a remote storage system.

Generally, the computer programs and operating systems are all tangibly embodied in a computer-readable device or media, such as the memory558, the data storage device122, or the data transmission device545, thereby making an article of manufacture, such as a computer program product, according to the invention. As such, the terms “computer program product” as used herein are intended to encompass a computer program542accessible from any computer readable device or media.

Moreover, the computer programs542and operating systems are comprised of instructions which, when read and executed by the computer system500, such as the server computer system102and the client computer system104, cause the computer system500, such as the server computer system102and the client computer system104, to perform the steps necessary to implement and use the present invention. Under control of the operating system, the computer programs542may be loaded from the memory558, the data storage device122, or the data transmission devices545into the memories558of the computer system500, such as the server computer system102and the client computer system104, for use during actual operations. Those skilled in the art will recognize many modifications may be made to this configuration without departing from the scope of the present invention. Elements102and104are described with reference toFIG. 1.

The user input device510, such as a keyboard or speech recognition subsystem, enables a user to communicate information and command selections to the processor555. The user can observe information generated by the system500via the display515or the printer520. The user input device510may also be a device such as a mouse, track-ball, or joy-stick, which allows the user to manipulate a cursor on the display515for communicating additional information and command selections to the processor555.

When operating in accordance with one embodiment of the present invention, the system500identifies data108that is related to and associated with data108that has been selected from a multidimensional database110, by the use of an index that includes location and magnitude information about features in a subject multidimensional database. The processor555and the program542collectively operate as a module for identification of data108that is related to and associated with data108that has been selected from a multidimensional data110by the use of an index. The overwhelming amount of data108in a multidimensional database110is efficiently and advantageously reduced by the preferred embodiment of the present invention to the selected and related data. It will be appreciated that the present invention offers many advantages over prior art techniques. Elements108and110are described with reference toFIG. 1.

The present invention is typically implemented using one or more computer programs542, each of which executes under the control of an operating system and causes the computer system500, such as the server computer system102and the client computer system104, to perform the desired functions as described herein. Thus, using the present specification, the invention may be implemented as a machine, process, method, system, or article of manufacture by using standard programming and engineering techniques to produce software, firmware, hardware or any combination thereof.

It should be understood that various alternatives and modifications can be devised by those skilled in the art. However, these should not be viewed as limitations upon the practice of these teachings, as those skilled in the art, when guided by the foregoing teachings, may derive other suitable characteristics of a similar or different nature. The present invention is intended to embrace all such alternatives, modifications and variances that fall within the scope of the appended claims

TRADEMARKS

IBM is a trademark or registered trademark of International Business machines, Corporation in the United States and other countries.

Essbase and Hyperion are trademarks or registered trademarks of Hyperion Solutions Corporation.