Patent Application
20140143270
Embodiments generally relate to computer systems, and more particularly to methods and systems for generating dynamic drilldown reports.
Drilldown reports are commonly used in the industry for allowing different users to drilldown into reports according to the information required by the user. At present, the different drilldown reports are pre-built and connected to each other. A user may jump between these pre-build reports based on the connection defined between the reports. These drilldown reports include static data values that are collected at the time of building these reports. Therefore, these drilldown reports do not show the current data values, which is undesirable.
The claims set forth the embodiments with particularity. The embodiments are illustrated by way of example and not by way of limitation in the figures of the accompanying drawings in which like references indicate similar elements. The embodiments, together with its advantages, may be best understood from the following detailed description taken in conjunction with the accompanying drawings.
Embodiments of techniques for generating dynamic drilldown reports are described herein. In the following description, numerous specific details are set forth to provide a thorough understanding of the embodiments. One skilled in the relevant art will recognize, however, that the embodiments can be practiced without one or more of the specific details, or with other methods, components, materials, etc. In other instances, well-known structures, materials, or operations are not shown or described in detail.
Reference throughout this specification to “one embodiment”, “this embodiment” and similar phrases, means that a particular feature, structure, or characteristic described in connection with the embodiment is included in at least one of the one or more embodiments. Thus, the appearances of these phrases in various places throughout this specification are not necessarily all referring to the same embodiment. Furthermore, the particular features, structures, or characteristics may be combined in any suitable manner in one or more embodiments.
A report refers to information automatically retrieved from a data source (e.g., a database, a data warehouse, and the like). In one embodiment, the report may include attributes and measures. Attributes may refer to descriptive data like customer ID, city, country, etc. Measures may refer to quantifiable data such as revenue, quantity sold, counters, etc. The report may also include data values corresponding to the attributes and measures included in the report. For example, the parent drilldown report 104 may include an attribute X 106 having three data values, data value 1 108, data value 2 110, and data value 3 112. Next, a request may be received for obtaining additional information related to any of the data values, data value 1 108, data value 2 110, and data value 3 112, in the parent drilldown report 104. For example, in the parent drilldown report 104 a drilldown request 114 may be received for obtaining additional information related to the data value 2 110 in the parent drilldown report 104.
An attribute of the data value, for which the drilldown request has been received, may next be identified 116. In the parent drilldown report 104, the attribute of the data value 2 110, for which the drilldown request 114 has been received, may be identified at 116 as attribute X 106. Next, associated attributes corresponding to the identified attribute may be determined at 118 from an in an in-memory database attribute relationship table 120. The in-memory database attribute relationship table 120 may include the relationship between the attributes included in database tables, stored in the in-memory database. The database tables may also include the data values corresponding to the attributes included in the database tables. In one embodiment, the data value of an associated attribute, corresponding to an attribute, may include the additional information related to the data value of the attribute. In the above example, an attribute X1 122 may be identified as associated attribute for the selected attribute 106 from the in memory database attribute relationship table 120. Next database tables may be searched 124, to retrieve data value of the associated attribute, corresponding to the data value, for which the drilldown request is received. The retrieved data value may be the additional information related to the data value, for which the drilldown request is received.
For example, consider that a drilldown request is received for a data value “CALIFORNIA”, in a US population drilldown report, corresponding to an attribute “US STATE”. An associated attribute corresponding to the attribute “US STATE” may be determined as “STATE POPULATION” from an in-memory database attribute relationship table. The database table may then be searched to retrieve the data value “38,000,000” of the associated attribute “STATE POPULATION”, in the database tables, corresponding to the data value “CALIFORNIA. The data value “38,000,000” may be the additional information corresponding to the data value “CALIFORNIA”. In the above example, a search at 124 may be performed on the database table 126 to retrieve the data value 21 at 128 of the associated attribute X1 at 124 corresponding to the data value 2 at 110, for which the drilldown request is received. The data value 21 at 128 may be the additional information related to the data value 2 at 110.
Finally, the data values, retrieved from the database tables, may be used to generate at 130 the child drilled report 102. The generated child drilled report 102 may include the data values, retrieved from the data base tables, and the data value, for which the drilldown request has been received. In the above example, the child drilled report 102 may include the data value 21 at 128, retrieved from the database table 126, and the data value 2 at 110, for which the drilldown request has been received. As the drilldown operation is being performed based on relationship between the attributes, in the database tables, the child drilled down report may be dynamically generated based on the latest data values in database tables. In the above example for US population drilldown report, consider that the population for “CALIFORNIA” is changed from “38,000,000” to “40,000,000” in the database table. In this case, the next time a user clicks on “CALIFORNIA” for a drilldown operation then the latest population data “40,000,000, for the associated attribute “STATE POPULATION”, may be retrieved from the database table. In this case, the dynamically generated child report may include “CALIFORNIA” and the latest population data “40,000,000”.
An in-memory database model may include attributes and measures included in the database tables stored in the in-memory database. The in-memory database model may allow a user to model the attributes and measures included in the database tables stored in the in-memory database. Data modeling is the analysis of data objects that are used in a business or any other context, and the identification of the relationships among these data objects. The in-memory database model may allow a user to perform different modeling operations, for example, the in-memory database model may allow a user to define relationship between the different attributes and measures included in the in-memory database mode, or to define additional properties for a particular attribute, etc. Data, from the data tables, may be retrieved and processed at run time, based on the attribute relations and attribute definitions defined in the in-memory database model. In one embodiment, the attributes and measures included in the in-memory database tables may correspond to a particular context. For example, an in-memory database model for a finance context may be used for modeling attributes and measures included in finance related database tables, an income table, an expense table, and a saving table, stored in the in-memory database. The in-memory database model may include the attributes and measures included in the income, expense, and saving tables. The attributes and measures in the finance in-memory database model may allow a user to define relationships between the attributes and measures included in the finance related database tables or to modify the properties of any of the attributes and measures. For example, the finance in-memory database model may be used to define that property of an “amount remaining” attribute in the expense table as hidden. In this case, the attribute “amount remaining” may be hidden from an end user at run time.
Initially at block 202, a source in-memory database model including attributes, included in database tables, may be displayed at a user interface. As discussed above, the source in-memory database model includes the attributes and measures included in the database tables, stored in the in-memory database. The attributes and measures included in the source in-memory database model may be displayed at the user interface. In one embodiment, the attributes and measures of database tables, corresponding to a particular context, may be included in the source in-memory database model. In this case, the in-memory database model includes attributes and measures, corresponding to a particular context. A user may perform a modeling operation on the displayed attributes and measures using the in-memory database model. For example, assume that the in-memory database stores two database tables, “customer sales” table and a “customer location” table, corresponding to a “customer” context. The “customer sales” table and the “customer location” table, may include four attributes “customer name”, “customer identification”, “customer sales”, and “customer country”. A customer in-memory database model may include the four attributes “customer name”, “customer identification”, “customer sales”, and “customer country”, corresponding to the “customer” context. The customer in-memory database model including the four attributes “customer name”, “customer identification”, “customer sales”, and “customer country” may be displayed to a user. In one embodiment, the user may select the attributes and measures, from the displayed list of attributes and measures, on which the user wants to perform the modeling operation. In the above example, the user may select three attributes, “customer name”, “customer sales”, and “customer country”, from the displayed four attributes, on which the user wants to perform the modeling operation. In one embodiment, the attributes and measures included in the source in-memory database model may be included in a parent drilldown report. The parent drilldown report may also include data values corresponding to the attributes and measures included in the source in-memory database model.
Next at block 204, the source in-memory database model may receive a selection of a drillable attribute from the attributes and measures displayed at block 202. The selected drillable attribute may be an attribute for which the user wants to provide the drilldown operation. The drilldown operation may be performed on any of the data values corresponding to the drillable attribute. A measure included in the displayed in-memory database model may also be selected as a drillable attribute. The user may select the drillable attribute from the attributes and measures selected, by the user, for performing the drilldown modeling operation at block 202. In one embodiment, the user may select any number of attributes or measures as drillable attributes. In the above example, the user may select the “customer sales” attribute as a drillable attribute from the attributes, “customer name”, “customer sales”, and “customer country”, selected for performing the modeling operation.
Next, at block 206 a drilldown property section, corresponding to the drillable attribute selected at block 204, may be displayed at the in-memory database model. The drilldown property section may be displayed to update the drilldown property of the selected drillable attribute. The drilldown properties may be used to define the drilldown relationship between the drillable attribute and associated attributes corresponding to the drillable attribute. Associated attributes may be any of the attributes or measures included in the database tables stored in the in-memory database. The data corresponding to the associated attribute may include the additional information for the data values corresponding to the drillable attribute.
In one embodiment, a list of in memory database models may be displayed at the drilldown property section for selecting associated attributes corresponding to the drillable attribute (block 208). In one embodiment, each of the in-memory database models may include a different combination of attributes and/or measures included in the database tables stored in the in-memory database. In the above example, two activated in-memory database models, “customer sales details” model and “customer address details” model, may be displayed at the drilldown property section. The “customer sales details” model may include a “customer sales for product A”, “customer sales for product B”, and “customer sales location” attributes. The “customer address details” model may include a “customer office address” attribute and “customer home address” attribute. The in-memory database model displayed at the drilldown property section may be activated in-memory database models. In one embodiment, an activated in-memory database model is a database view, such as an SQL view. A database view may include a database query and may return a virtual table obtained based on executing the database query on the database tables, stored in the in-memory database. The activated in-memory database table may return a virtual table that includes the combination of the attributes and measures included in the activated in-memory database model.
Next at block 210, a selection may be received for a target in-memory database model from the in-memory database models displayed at block 208. The combination of attributes and/or measures in the selected target in-memory database model may be the associated attributes corresponding to the drillable attribute. In the above example, the user may select the “customer sales details” model as the target in-memory database model. In this case, the “customer sales for product A” attribute, “customer sales for product B” attribute, and “customer sales location” attribute, included in the “customer sales details” model may be identified as the associated attribute corresponding to the drillable attribute. In one embodiment, the combination of the attributes and/or measures in the selected target in-memory database model may be displayed to a user. The user may then select any of the attributes and/or measures, from the displayed combination of attributes and/or measures, as associated attributes corresponding to the drillable attribute. In the above example, the attributes “customer sales for product A” attribute, “customer sales for product B” attribute, and “customer sales location” attribute may be displayed to a user. The user may then select “customer sales for product A” attribute, “customer sales for product B”, from the displayed attributes, as the associated attribute corresponding to the “customer sales” attribute.
In one embodiment, the type of drilldown operation, synchronous operation or asynchronous operation, for the drillable attribute may be defined based on the selection of the attributes in the target in-memory database module. A synchronous drilldown operation may provide the next level of details for the data value of the drillable attribute. In the above example, a synchronous drilldown operation may be defined for the drillable attribute “customer sales” when the user selects “customer sales for product A” and “customer sales for product B” as the associated attributes for the drillable attribute “customer sales”. The data values for the associated attributes “customer sales for product A” and “customer sales for product B” may provide the next level of detail for data values corresponding to the drillable attribute “customer sales”. An asynchronous drilldown operation may provide additional details, at the same level, for the data value of the drillable attribute along with the next level of details for the data value of the drillable attribute. In the above example, an asynchronous drilldown operation may be defined for the drillable attribute “customer sales” when all the attributes “customer sales for product A”, “customer sales for product B”, and “customer sales location”, in the “customer sales details” model, are selected as associated attributes for the drillable attribute “customer sales”. The data values for the associate attribute “customer sales location” may provide additional details at the same level for the data values corresponding to the drillable attribute “customer sales”, and the data values for the associated attributes “customer sales for product A” and “customer sales for product B” may provide the next level of detail for data values corresponding to the drillable attribute “customer sales.”
Next at block 212, the source in-memory database model may be stored in the in-memory database. The source in-memory database model may include the relationship between the drillable attribute, selected at block 204, and the target in-memory database model, selected at block 210, including the associated attributes corresponding to the drillable attribute. In the above example, the “customer” in memory database model may include the relationship between the selected attribute “customer sales” and the target “customer sales details” attribute. Next at block 214, the source in-memory database model stored in the in-memory database may be activated. In one embodiment, activation is the process of converting the source drilldown report to an activated in-memory database model, which may be queried by a client. The activated in-memory database model may allow a client, for example an SQL client, to query the database tables, based on the relationships between the drillable attribute and the target in-memory database model, stored in the source in-memory database model.
In one embodiment, the activated in-memory database model may be stored in an in-memory database attribute relationship table. The in-memory database attribute relationship table may also store the target in-memory database model, selected at block 212, and the drillable attribute, selected at block 204. The in-memory database attribute relationship table may also store the associated attributes, corresponding to the drillable attribute, included in the target in-memory database model.
In one embodiment, blocks 204-214 may be repeated for another attribute in the source in-memory database model to identify and store the associated attributes corresponding to the another attribute, in the attribute relationship table. For example, a user may select the “customer country” attribute, as the drillable attribute, from the customer source in-memory database model. In this case, the “customer location details” model may be selected as the target in-memory database model. The attributes, “customer office address” attribute and “customer home address” attribute, in the “customer location details” model may be selected and stored as the associated attribute corresponding to the “customer country” attribute.
In one embodiment, a multi-level drilldown reporting may be provided by repeating the steps in blocks 202-214 for the target in-memory database model selected at block 210. Specifically, a drillable attribute may be selected from the attributes in the target in-memory database model selected at block 210. Associated attributes corresponding to selected attribute, of the target model, may be selected by selecting a target in-memory database model for the drillable attribute. The drillable attribute, of the target in-memory database model, and the associated attribute may be stored in the in-memory database attribute relationship table. Based on this, any level of drilldown operations may be provided by defining relationship between attributes included in the in-memory database models. In the above example, the steps in blocks 202-214 may be repeated for the “customer location details” target in memory database model. In this case, a “customer sales for product A” attribute, in the “customer location details” model, may be selected as the drillable attribute. Next, a “quarterly sales of product A” model may be selected as the target in-memory database model for the “customer sales for product A” attribute. The attributes, “sales of product A in Q1”, “sales of product A in Q2”, “sales of product A in Q3”, and “sales of product A in Q4”, in the “quarterly sales of product A” model may be selected as associated measure for the drillable “customer sales for product A” attribute. A user may then perform two levels of drilldown, based on the associated attributes selected for a drillable attribute in the target in-memory database model. A user may perform a first level of drilldown on a data value of the “customer sales” attribute to obtain the values of associated attributes “customer sales for product A” and “customer sales for product B”. A second level of drilldown may then be performed on a data value of the “customer sales for product A” attribute to obtain the data values of the associated attributes “sales of product A in Q1”, “sales of product A in Q2”, “sales of product A in Q3”, and “sales of product A in Q4.”
Next blocks 216-224 may be executed, at run time, to generate the child drilled report corresponding to the parent drilldown report based on the drilldown relationship between the drillable attribute and the associated attribute corresponding to the drillable attribute. At block 216, a request may be received for drilling down on a data value included in the parent drilldown report. In one embodiment, the parent drilldown report may include the attributes and measures included in the source in-memory database model. The parent drilldown report may also include data values corresponding to the attributes and measures in the drilldown report. The drillable attributes and measures in the parent drilldown report may be highlighted to indicate that drilldown is enabled for the data values corresponding to the drillable attributes and measures. The drilldown request may be received for obtaining additional information related to the selected attribute. In the above example, a parent drilldown report may include the attributes “customer name”, “customer identification”, “customer country”, and “customer sales” included in the source in memory database model. The drillable attribute “customer country” and the drillable measure “customer sales” may be highlighted. A drilldown request may be received for a data value “US” in the parent drilldown report.
Next at block 218, an attribute or measure of the selected data value may be determined. In the above example, the attribute of the selected data value “US” may be identified as “customer country”. Next at block 220 an associated attribute corresponding to the identified attribute may be identified. The associated attribute corresponding to the identified attribute may be identified based on the drilldown relationship between the identified (drillable) attribute and the target in-memory database model stored in the source in-memory database model. The source in-memory database model storing the drilldown relationship may be obtained from the in-memory database attribute relationship table. The attributes and measures included in the target in-memory database model may be determined as the associated attribute corresponding to the identified attribute. In the above example, based on the drilldown relationship stored in the “customer” source in memory model, the “customer address details” model may be identified as the target in-memory database model for the identified “customer country” attribute. The attributes “customer office address” and “customer home address”, included in the “customer address details” model may be determined as associated attributes corresponding to the identified “customer country” attribute.
Next at block 222, the database tables, stored in the in-memory database, may be searched based on the associated attributes, determined at block 220, and the data value for which the drilldown request has been received at block 216. In one embodiment, the database tables may be searched to retrieve the data value of the associated attribute corresponding to the data value, for which the drilldown request has been received. In the above example, the database tables may be searched to retrieve the data values of the “customer office address” and the “customer home address” attributes corresponding to the data value “US”. In this case, assume that the data value, stored in the in-memory database, for “customer office address” and the “customer home address” attribute is “ABC STREET, PALO ALTO, US” and “XYZ STREET, PALO ALTO, US.” These data values, “ABC STREET, PALO ALTO, US” and “XYZ STREET, PALO ALTO, US”, of the associated attributes may be retrieved from the database tables.
Finally at block 224, the value retrieved from the database tables, at block 222, may be used to generate the child drilled report. The generated child drilled down report may include the retrieved data values of the associated attributes and the data value for which the drilldown request has been received. In the above example, the child drilldown report may include the retrieved data values “ABC STREET, PALO ALTO, US” and “XYZ STREET, PALO ALTO, US” and the data value “US” for which the drilldown request has been received.
As discussed above, multiple levels of drilldown may be provided by defining the drilldown relationships between the attributes included in the database tables. Each of these drilldown reports may be shown in a tabbed view on a single screen. A user can easily jump between these reports by selecting the desired report from the tabbed view. For example, consider that a user drilldowns from a first report to obtain a second report. Next the user drillsdown the second report to obtain a third report. In this case, the reports which are not being displayed may be shown as a tab on the screen. For example, if the user is viewing the first report then the user may be shown two tabs corresponding to the second report and the third report. A user can jump to any of the reports by selecting the corresponding tab. For example, a user can jump directly to the third report from the first report. This may allow the user to easily jump to any of the drilled reports, depending on the level of data granularity desired by the user.
Some embodiments may include the above-described methods being written as one or more software components. These components, and the functionality associated with each, may be used by a client, server, distributed, or peer computer systems. These components may be written in a computer language corresponding to one or more programming languages such as, functional, declarative, procedural, object-oriented, lower level languages and the like. They may be linked to other components via various application programming interfaces and then compiled into one complete application for a server or a client. Alternatively, the components maybe implemented in server and client applications. Further, these components may be linked together via various distributed programming protocols. Some example embodiments may include remote procedure calls or web services being used to implement one or more of these components across a distributed programming environment. For example, a logic level may reside on a first computer system that is remotely located from a second computer system containing an interface level (e.g., a graphical user interface). These first and second computer systems can be configured in a server-client, peer-to-peer, or some other configuration. The clients can vary in complexity from mobile and handheld devices, to thin clients and on to thick clients or even other servers.
The above-illustrated software components are tangibly stored on a computer readable storage medium as instructions. The term “computer readable storage medium” should be taken to include a single medium or multiple media that stores one or more sets of instructions. The term “computer readable storage medium” should be taken to include any physical article that is capable of undergoing a set of physical changes to physically store, encode, or otherwise carry a set of instructions for execution by a computer system which causes the computer system to perform any of the methods or process steps described, represented, or illustrated herein. Examples of computer readable storage media include, but are not limited to: magnetic media, such as hard disks, floppy disks, and magnetic tape; optical media such as CD-ROMs, DVDs and holographic devices; magneto-optical media; and hardware devices that are specially configured to store and execute, such as application-specific integrated circuits (“ASICs”), programmable logic devices (“PLDs”) and ROM and RAM devices. Examples of computer readable instructions include machine code, such as produced by a compiler, and files containing higher-level code that are executed by a computer using an interpreter. For example, an embodiment may be implemented using Java, C++, or other object-oriented programming language and development tools. Another embodiment may be implemented in hard-wired circuitry in place of, or in combination with machine readable software instructions.
A data source is an information resource. Data sources include sources of data that enable data storage and retrieval. Data sources may include databases, such as, relational, transactional, hierarchical, multi-dimensional (e.g., OLAP), object oriented databases, and the like. Further data sources include tabular data (e.g., spreadsheets, delimited text files), data tagged with a markup language (e.g., XML data), transactional data, unstructured data (e.g., text files, screen scrapings), hierarchical data (e.g., data in a file system, XML data), files, a plurality of reports, and any other data source accessible through an established protocol, such as, Open DataBase Connectivity (ODBC), produced by an underlying software system (e.g., ERP system), and the like. Data sources may also include a data source where the data is not tangibly stored or otherwise ephemeral such as data streams, broadcast data, and the like. These data sources can include associated data foundations, semantic layers, management systems, security systems and so on.
In the above description, numerous specific details are set forth to provide a thorough understanding of embodiments. One skilled in the relevant art will recognize, however that the embodiments can be practiced without the specific details or with other methods, components, techniques, etc. In other instances, well-known operations or structures are not shown or described in details.
Although the processes illustrated and described herein include series of steps, it will be appreciated that the different embodiments are not limited by the illustrated ordering of steps, as some steps may occur in different orders, some concurrently with other steps apart from that shown and described herein. In addition, not all illustrated steps may be required to implement a methodology in accordance with the one or more embodiments. Moreover, it will be appreciated that the processes may be implemented in association with the apparatus and systems illustrated and described herein as well as in association with other systems not illustrated.
The above descriptions and illustrations of embodiments, including what is described in the Abstract, is not intended to be exhaustive or to limit the one or more embodiments to the precise forms disclosed. While specific embodiments are described herein for illustrative purposes, various equivalent modifications are possible, as those skilled in the relevant art will recognize. These modifications can be made in light of the above detailed description. Rather, the scope is to be determined by the following claims, which are to be interpreted in accordance with established doctrines of claim construction.
