Abstract:
A method and system for automatically defining and applying an exception rule to reports created from at least one multi-dimensional database, comprising: defining global exception parameters, displaying at least one report created from a multi-dimensional database, receiving a user command to detect exceptional cells in the displayed report, locating the most relevant time dimension in the semantics of the source database of the report, detecting exceptional cells in the displayed report according to the exception parameters and the most relevant time dimension, calculating an exception quality for each detected exceptional cell, and indicating on the displayed report the exceptional cells, the indication comprising differentiating between different exception qualities, wherein the exception rule is independent of data granularity in the time dimension in the report.

Description:
FIELD OF THE INVENTION 
       [0001]    The present invention relates generally to electronic business technology and business processes, and more particularly, to an automatic exception detection method and system. 
       BACKGROUND 
       [0002]    The major purpose of OLAP is decision support. Users can gain insights into what is actually driving their business. They can measure effectiveness, maximize the ROI (return on investment) and thus maintain competitive advantage. OLAP is a key component to an organization&#39;s business intelligence infrastructure. 
         [0003]    OLAP Servers make it easy to get summarized information. OLAP Servers aggregate data at build time and store these aggregations. Aggregations can use a number of different built-in OLAP statistics to aggregate data. Statistics are stored with OLAP cubes and do not require additional computation when accessed by queries. The short response times of OLAP data sources enable workers to navigate through data following their own train of thought. OLAP clients support the standard OLAP navigations such as:
       Drilling up and down hierarchies.   Expanding and collapsing levels within hierarchies.   Slicing and dicing through data.   Drilling beyond the cube data into the underlying detailed data.       
 
         [0008]    It is possible to begin exploring the data by getting a high-level overview on corporate operations. Based on this view, OLAP technology enables fast and consistent drill down access into the details that describe the accumulated business metrics in order to pinpoint key elements and to view business activities from a number of different perspectives. OLAP allows users to be able to analyze data across any dimension, at different levels of aggregation, with equal functionality and ease. 
         [0009]    Within the business domain using OLAP technology, there are varying levels of sophistication. Some people use OLAP without actually knowing that it is OLAP, simply by opening up reports based on OLAP data and exploring one of the other dimensions by drilling up and down. At the other extreme, there are business analysts who use OLAP data sources as a launch pad to exploring multivariate problems, possibly applying statistical procedures or predictive models to specific slices of data taken from an OLAP data source. Reports using OLAP data are likely to be relevant for a large audience, while only a few people in an enterprise might be knowledgeable about statistical procedures or predictive analytics. 
         [0010]    These experts define analysis rules to calculate performance metrics from detailed business transaction data, and context rules to enable performance metrics to be tied to business goals and forecasts. Some basic automation can be achieved by applying exception rules to metrics and sending an alert to a business user when a metric exceeds a threshold defined in the exception rule. 
         [0011]    There is need for a tool that will enable the non-specialized user to define a powerful exception rule on his data, without having expert knowledge of the database structure and without the need to perform any programming. 
       SUMMARY 
       [0012]    According to a first aspect of the present invention there is provided a computerized method of automatically defining and applying an exception rule to reports created from at least one multi-dimensional database, comprising: defining global exception parameters including at least: an exception threshold, a comparison rule, a comparison lag indicating the number of periods to go back for comparison and a comparison scope indicating which level of entities to compare; displaying on a computer monitor at least one report created from a multi-dimensional database, said at least one report comprising data cells and filters; receiving a user command to detect exceptional cells in said at least one displayed report; locating the most relevant time dimension in the semantics of the source database of said at least one report; detecting exceptional cells in said at least one displayed report according to said exception parameters and said most relevant time dimension; calculating an exception quality for each said detected exceptional cells; and indicating on said displayed report said detected exceptional cells, said indication comprising differentiating between different exception qualities, wherein said exception rule is independent of data granularity in said time dimension in said at least one report. 
         [0013]    According to a second aspect of the present invention there is provided a computerized system for automatically defining and applying an exception rule to reports created from multi-dimensional databases, comprising: a system server comprising at least one multi-dimensional database and a server application; a plurality of client computers, each comprising a client application, a display monitor and GUI tools, said plurality of client computers communicating bi-directionally over the internet with said system server; said system application adapted to: locating the most relevant time dimension in the semantics of the source database of at least one report; detecting exceptional cells in said at least one report according to exception parameters and said most relevant time dimension; calculating an exception level for each said detected exceptional cells; and sending indications of said detected exceptional cells and said exception levels to said client application; and said client application adapted to displaying said at least one report and marking said exceptional cells and said exception levels thereon, wherein said exception rule is independent of data granularity in said time dimension in said at least one report. 
         [0014]    According to a third aspect of the present invention there is provided a computerized method of automatically defining and applying an exception rule to reports created from at least one multi-dimensional database, comprising: defining global exception parameters including at least: an exception threshold; a comparison rule; a comparison lag indicating the number of periods to go back for comparison; and a comparison scope indicating which level of entities to compare; receiving a user command for activating an exception detecting application upon fulfillment of at least one predefined criterion; receiving a user selection of at least one report page for applying said exception detection, each said at least one report pages comprising at least one report; and when said at least one predefined criterion is fulfilled: locating the most relevant time dimension in the semantics of the source database of the at least one report on said at least one report page; detecting exceptional cells in said at least one report according to said exception parameters and said most relevant time dimension; calculating an exception quality for each said detected exceptional cells; displaying said at least one report page; and indicating on said at least one displayed report said detected exceptional cells, said indication comprising differentiating between different exception qualities, wherein said exception rule is independent of data granularity in said time dimension in said at least one report. 
     
    
     
       BRIEF DESCRIPTION OF THE DRAWINGS 
         [0015]    For a better understanding of the invention and to show how the same may be carried into effect, reference will now be made, purely by way of example, to the accompanying drawings. 
           [0016]    With specific reference now to the drawings in detail, it is stressed that the particulars shown are by way of example and for purposes of illustrative discussion of the preferred embodiments of the present invention only, and are presented in the cause of providing what is believed to be the most useful and readily understood description of the principles and conceptual aspects of the invention. In this regard, no attempt is made to show structural details of the invention in more detail than is necessary for a fundamental understanding of the invention, the description taken with the drawings making apparent to those skilled in the art how the several forms of the invention may be embodied in practice. In the accompanying drawings: 
           [0017]      FIG. 1  is a schematic configuration of the system for carrying out the present invention; 
           [0018]      FIG. 2  shows an exemplary dashboard displaying two reports; 
           [0019]      FIG. 3  shows the dashboard of  FIG. 2 , where the user has selected the “Exceptions” tool; 
           [0020]      FIG. 4  is a flowchart showing the various steps taken by the server application when the user has requested to see the exceptions; 
           [0021]      FIG. 5  is a flowchart showing the various steps involved in determining for each cell in a report whether it is exceptional; 
           [0022]      FIG. 6  shows the example of  FIG. 2 , on which the automatic exception display has been performed using a default 5% threshold; 
           [0023]      FIG. 7  shows the updated reports; 
           [0024]      FIG. 8  shows a drop-down menu for selecting the exception levels to be displayed; 
           [0025]      FIG. 9  shows an additional GUI tool showing detailed growth data pertaining to the cell the user&#39;s cursor hovers over; 
           [0026]      FIG. 10  shows an exemplary GUI for defining automatic exception alerts; 
           [0027]      FIG. 11  shows an exemplary GUI for self-initiating the automatic exception alerts; 
           [0028]      FIG. 12  is a flowchart showing the various steps taken by the system when batch mode alerting is initiated; and 
           [0029]      FIG. 13  shows examples of applying the method of the present invention to other visualizations that are based on one or more data cells. 
       
    
    
     DETAILED DESCRIPTION OF PREFERRED EMBODIMENTS 
       [0030]    Before explaining at least one embodiment of the invention in detail, it is to be understood that the invention is not limited in its application to the details of construction and the arrangement of the components set forth in the following description or illustrated in the drawings. The invention is applicable to other embodiments or of being practiced or carried out in various ways. Also, it is to be understood that the phraseology and terminology employed herein is for the purpose of description and should not be regarded as limiting. 
         [0031]    One click exception allows a non-specialized user to define a powerful exception rule pertaining to the time dimension on his data. The exception rule is independent of data granularity, i.e. it will apply rightly to the time dimension on any level. 
         [0032]    One-click exception creates, for each cell in a report, a normalized percentage value that may be compared to the relevant entity. 
         [0033]    The one-click exception rule as applied to the time dimension defines two important criteria that combine to determine if a data item is an exception and how it should be displayed:
       1. Exception threshold (condition)
           a. The threshold value to cause an exception to happen.   b. Can be defined as positive or negative value, to show exceptional values that are “good” or “bad”.   c. The condition for each cell is based on the growth of the cell value from the previous period. The system auto detects the relevant previous period based on:
               i. Auto detection of the most relevant time dimension from all the time dimensions existing in the cube.   ii. Understanding the dimension semantics to do the right comparison for each cell, i.e. compare day against previous day, month against so previous month, year against previous year.   
               
           2. Exception quality
           a. How ‘strong’ is the exception. That is, two cells growth may be below 5% (the exception condition), but one is 20% below and the other is 60% below.   b. The exception quality is normalized to a discrete range or 1-10, where 10 is is the ‘stronger’ one.   
               
 
         [0043]    The method of automatically detecting exceptions according to the present invention may work simultaneously on several different multi-dimensional data sources (OLAP cubes), comprising e.g. different time dimensions. For example, a warehouse database, in which the time dimension indicates times of goods inflow and outflow, and a sales database, in which the time dimension indicates time of sale. 
         [0044]      FIG. 1  is a schematic configuration of the system for carrying out the present invention. The system  100  comprises an OLAP server  110  and a plurality of client computers  120  communicating bi-directionally with the server  110  over the Internet or Intranet  130 . 
         [0045]    The OLAP server  110  comprises at least one multi-dimensional database (OLAP cube)  140  and a server application  150 . 
         [0046]    Each client computer  120  comprises a client application  160  and GUI tools  170 . 
         [0047]    The method according to the present invention may work in one of two modes:
       Interactive mode   Alerting/batch mode       
 
       Interactive Mode: 
       [0050]    The interactive mode of defining, detecting and displaying exceptions operates on a set of N simultaneously displayed reports (crosstabs), derived from 1-N different OLAP cubes. 
         [0051]      FIG. 2  shows an exemplary dashboard  200 , displaying two reports, a Reseller Sales report  210 , showing different resellers&#39; $ sales for April through June of 2008 and Sales Total report  220 , showing total $ sales for different product categories in different territories. 
         [0052]    The reports are filtered by the “Sales” measure (pertaining to both reports) and by “Q3 FY 2009” (pertaining only to the Sales Total report). 
         [0053]    The dashboard additionally comprises three GUI tools for operating and defining the one-click exceptions method, namely:
       An on/off “Exceptions” toggle button  230  for enabling and/or disabling automatic exceptions highlighting. A change in the button state will apply to all reports currently displayed.   A “Threshold Selection” tool  240  for defining the exception threshold. A change in the threshold level will affect the exception rule to show more/less cells highlighted and will apply to all reports currently displayed.
           Threshold=−5 will show cells whose growth &lt;5%   Threshold=5 will show cells whose growth &gt;5%   The rule of comparison is defined as well, so user may select between   
           A “Filtering” tool  250  for defining the quality of exceptions to be displayed.       
 
         [0060]      FIG. 3  shows the dashboard  200  of  FIG. 2 , where the user has selected the “Exceptions” tool  230  to view exceptional cells in the two displayed reports. 
         [0061]      FIG. 4  is a flowchart showing the various steps taken by the server application when the user has requested to see the exceptions. 
         [0062]    In step  400  the system starts iterations on all the displayed reports by getting the first report. 
         [0063]    In step  410  locates the most relevant time dimension in the semantics of source cube of the report. Locating the most relevant time dimension is performed according to the following priority:
       1. The time dimension on the X-axis of the report.   2. The time dimension on the Y-axis of the report.   3. The first time dimension on the filters (if there is more than one) that is sliced on a member (e.g.  2009 ) and not on “All Time”.   Referring back to the example of  FIG. 3 , the time dimension selected for the “Reseller Sales” report will be “Month” (X-axis) and the time dimension selected for the “Sales Total” report will be “Quarters” (Filter).   In step  420  of  FIG. 4 , the system proceeds to automatically define the exception rule, according to several parameters, including but not limited to:
           a. Exception threshold. A system default may be set at −5% and may be changed by the user ( FIG. 6 ).   b. Comparison rule (&lt;, &lt;=, &gt;, &gt;=). A system default may be set as &lt;= and may be changed by the user ( FIG. 6 ).   c. Lag—How many periods to go back for comparisons. A system default may be set as 1 and may be changed by the user (not shown).   d. Scope—Which level of entities to compare. A system default may be set as 0 and may be changed by the user (not shown). Alternatively, a preferred scope may automatically be detected by the system (e.g. seasonal comparison), or be defined in the cube.   
           For example, if the time dimension has Year/Quarter/Month levels, Lag=1 and Scope=2, the system will determine the cell to compare to “January 2009” as follows:   1. UP two levels (Scope=2) to Year level→2009.   2. BACK one period (Lag=1)→2008.   3. BACK to the original cell level (Month)→January 2008.       
 
         [0077]    In step  430  of  FIG. 4 , the system proceeds to filter out of the displayed reports rows and columns that have no exceptional cells and in step  440  the exceptional cells in the N displayed reports are marked. 
         [0078]    Steps  410  through  440  are repeated for each displayed report. 
         [0079]      FIG. 5  is a flowchart showing the various steps involved in determining for each cell in a report whether it is exceptional. 
         [0080]    In step  500  the system starts iterations on all the cells of the report by getting the first cell C 1 . 
         [0081]    In step  510  the Lag and Scope parameters are used to determine which cell C 2  is to be compared with cell C 1 , as explained above. 
         [0082]    In step  520  the growth rate from C 2  to C 1  is calculated, namely ((C 1 −C 2 )/C 2 ) and in step  530  the calculated growth rate is compared with the Threshold and Comparison rules, to determine whether the growth rate complies with the exception rule. If it is determined that the growth rate complies with the exception rule, the system proceeds to calculate the quality of the exception (step  540 ), as will be explained in detail below. In step  550  cell C 1  is highlighted accordingly. 
         [0083]    Steps  510  through  550  are repeated for each cell in the report. 
         [0084]    An exception quality (strength) may be calculated using various known in the art statistical computations. A preferred but not limiting method of calculating the exception quality according to the present invention is:
       1. For all exceptional cells for each measure find the highest (Mx) and lowest (Mn) growth values:
           a. If the crosstab shows comparable values, e.g. only sales values, then we need to find two values, highest and lowest, of all the crosstab.   b. If the crosstab shows cell values for multiple measures, e.g. Sales, Cost, Profit, then we calculate the max\min value for each measure separately, since the distributions of the various measures may be different.   
           2. Define B buckets, where each bucket defines a strength level of exceptions.   3. Assign each exceptional cell to a bucket, thus defining its strength level.       
 
         [0090]    The buckets&#39; definition may be done in one of two ways:
       a. Equal sized buckets:
           According to this method, the buckets will define equal scopes of growth (e.g. 0-9%, 10-19%, etc.) but will have different number of members:
               1. Define cell(i) growth as G(i).   2. The bucket assigned to cell(i) will be: B(i)=B*(G(i)−Mn)/(Mx−Mn).   
               
           b. Weighted buckets:
           According to this method, the buckets will define different scopes of growth but will have an equal number of members:
               1. Define an array (Arr A) of size S(A) in which the exceptional cells are ordered from low to high growth rate.   2. Rank each cell in the array: X(i)=rank(Arr A, cell(i))   3. The bucket assigned to cell(i) will be: B(i)=int(B*X(i)/S(A))+1   
               
               
 
         [0100]    The resulting exception quality for each cell may be indicated on the displayed using different GUI methods. For example, each exceptional cell may be displayed with an up/down triangle denoting positive/negative exception respectively. The up/down triangle color shows the quality of the exception. Different shades of green (positive) or red (negative) show quality, the darker, the stronger the exception quality is. 
         [0101]      FIG. 6  shows the example of  FIG. 2 , on which the automatic exception display has been performed using a default 5% threshold. 
         [0102]    The user may now open a threshold changing slider drop-down  600  by clicking the “Threshold Selection” tool  240 . The threshold level may be changed using a slider  610  sliding over a numbers (percents) scale ranging from −100 to 100. A change in the slider drop-down will apply to all the currently displayed reports, provided the exceptions are activated. 
         [0103]    A Comparison Rule selection tool  630  may also be implemented as a slider drop-down  640 . 
         [0104]    In the example of  FIG. 6 , the user selects −10% to be the new exception threshold and a &lt;= comparison rule, resulting in the updated reports displayed in  FIG. 7 . As can be seen, some of the exceptional cells from  FIG. 6  are no longer indicated as exceptions (e.g. Convenient Sales for April 2008), while other cells have changed their exception level (e.g. Road Bikes sales for the Pacific territory). 
         [0105]      FIG. 8  shows a drop-down menu  800  for selecting the exception levels to be displayed, by clicking the Filtering tool  250 . The user may select one or more exception levels to be displayed, or he may chose to display All levels, as indicated by the current choice in the example. Selecting a different exception level will cause all triangles indicating other levels to disappear from display. 
         [0106]    In  FIG. 8 , the Reseller Sales report  810  and the Sales Total report  820  only show rows and columns having exceptional cells according to the current parameter selection. 
         [0107]      FIG. 9  shows an additional GUI tool  900 , in the shape of a balloon showing detailed growth data pertaining to the cell the user&#39;s cursor hovers over. 
       Alerting/Batch Mode: 
       [0108]    The Alerting mode of defining, detecting and displaying exceptions operates on a choice of the user&#39;s pages, each comprising at least one report, at times defined by the user. Default values may be configured in the system for the various exception rule parameters, or previously defined parameters may be used. 
         [0109]      FIG. 10  shows an exemplary GUI  1000  for defining automatic exception alerts. 
         [0110]    The user may select the pages which should be analyzed for automatic exception alert, e.g. “All my private pages”, “My favorite pages”, “Public pages I can view”. 
         [0111]    The user may also define when the automatic exception analysis and alert should take place, e.g. “Scheduled at . . . ” (e.g. 1 st  of each month, every Monday, etc.) or “Whenever data is changed”, i.e. whenever data pertaining to one of the selected pages is changed in the database. 
         [0112]    The user may additionally or alternatively self-initiate the defined batch of alerts, as shown in  FIG. 11 , by selecting a toggle button  1110  in his displayed home page  1100 . 
         [0113]      FIG. 12  is a flowchart showing the various steps taken by the system when batch mode alerting is initiated, either automatically or on-demand. 
         [0114]    In step  1200  the system gets the first relevant page, as defined by the user in conjunction with  FIG. 10 , and uses the exception parameters, either default or previously defined by the user, to determine which cells in the report(s) are exceptional (step  1210 ). 
         [0115]    In step  1220  the system calculates, for each report, for each exceptional cell, the exception quality, as described above and in step  1230  a Report Quality number is calculated for the report, to indicate the overall severity of the exceptions in the report. 
         [0116]    The Report Quality number may be calculated using various known in the art statistical computations. A preferred but not limiting formula of calculating the Report Quality number according to the present invention is:
       i. Cnum=number of cells in report   ii. Ci=an exceptional cell   iii. Qi=the quality of Ci exception   iv. M−report quality
           M=sum(Qi)Cnum   
               
 
         [0122]    M results in the average exception quality per report cell. 
         [0123]    The Page Quality number may be calculated using various known in the art statistical computations from the Report quality numbers 
         [0124]    A preferred but not limiting formula of calculating the Page Quality number according to the present invention is
       1. Summary of report quality of all reports.   2. Average of report quality (sum or report quality/number of reports in page).       
 
         [0127]    Steps  1200  through  1230  are performed for each page. 
         [0128]    In step  1250 , the pages are displayed, preferably as thumbnails, in a descending order of page quality. 
         [0129]    Referring back to  FIG. 11 , the user&#39;s homepage GUI comprises a window  1120  in which thumbnail pages  1130  are displayed according to descending order of pages quality. The pages Quality number  1140  for each page may be displayed with the thumbnail. Window  1120  may comprise a scroll-bar  1150 , to enable scrolling through the thumbnail page. Clicking one of the thumbnail pages will cause the full page to be displayed. 
         [0130]    It will be appreciated by persons skilled in the art that the present invention is not limited to what has been particularly shown and described hereinabove. Rather the scope of the present invention is defined by the appended claims and includes both combinations and sub-combinations of the various features described hereinabove as well as variations and modifications thereof which would occur to persons skilled in the art upon reading the foregoing description. 
         [0131]    For example, as shown schematically in  FIG. 13 , the method of the present invention may be similarly applied to any visualization that is based on one or more data cells, e.g. KPIs  1300 , pie-charts  1310 , etc.