Patent Publication Number: US-11048725-B2

Title: Methods and systems for unified data sources

Description:
BACKGROUND 
     Information technology systems might generally be divided into transactional (On-Line Transactional Processing, OLTP) systems and analytical (On-Line Analytical Processing, OLAP) systems. In general, OLTP systems use data sources for data warehouses that are analyzed by OLAP systems. The data sources of OLTP and OLAP systems are typically different and are further configured and optimized for each of the respective systems. Accordingly, systems and processes have been developed to transform data from an OLTP data structure to an OLPA data structure. 
     However, some OLTP to OLAP data transformations are complex and resource hungry. As such, some applications and other entities may have a desire and/or need for a technological tool to efficiently generate and implement a single data structure that may accommodate both OLTP and OLAP applications. 
    
    
     
       BRIEF DESCRIPTION OF THE DRAWINGS 
         FIG. 1  is an illustrative logical architecture for a database system; 
         FIG. 2  includes illustrative examples of database tables; 
         FIG. 3  is an illustrative example depicting two data sources and a transformation process therebetween; 
         FIG. 4  is an illustrative example depicting a unified data source and process herein; 
         FIG. 5  is a depiction of illustrative examples of database tables herein; 
         FIG. 6  is an illustrative flow diagram for some example embodiments herein; and 
         FIG. 7  is an example schematic diagram of a system in an embodiment herein. 
     
    
    
     DETAILED DESCRIPTION 
     The following description is provided to enable any person in the art to make and use the described embodiments. Various modifications, however, will remain readily apparent to those in the art. 
     In some example contexts, use-cases, and embodiments, one or more terms will be used in the present disclosure. As a matter of introduction and to ease the understanding of the present disclosure, a number of terms will be introduced, where the full meaning of the following terms will be further understood in context of the disclosure herein, on the whole. 
       FIG. 1  is an illustrative block diagram of an architecture or system  100 , in one example. Examples of some embodiments of the present disclosure are not limited to the particular architecture  100  shown in  FIG. 1 . System  100  includes an illustrative logical architecture for a database system including an in-memory database layer  105  and a repository, business, and application layer  110 . All of the data is stored in database layer  105 , where for system  100  all of the data is stored in-memory (e.g., in volatile (non-disk-based) memory such as, for example, Random Access Memory or RAM). The full database may be persisted in and/or backed up to fixed disks (not shown). 
     Accordingly, there is no need to transfer data between the database layer  105  and the repository, business, and application layer  110 . Additionally, as system  100  stores all of the data in RAM, calculations can be performed at the database layer  105  instead of, for example, at the application layer. 
     In some embodiments, system  100  may include the SAP HANA in-memory database infrastructure (developed by the assignee hereof, SAP SE). In some embodiments, system  100  may include a feature referred to as calculated columns. As used herein, the calculated columns feature (e.g., as implemented in SAP HANA) resides in logic in database layer  105 . As referred to herein, the calculated column feature can be invoked to add an additional column to a database table. The results for the calculated column can be calculated at runtime based on existing column(s) (e.g., from a database source table, another calculated column, etc.) and one or more functions, operators (e.g., logic operators, mathematical operators, etc.), input parameters, and constants. In some embodiments, a database instance including features and/or functions similar to the calculated column feature may be leveraged in accordance with other aspects of the present disclosure. 
     In some aspects, process executions by the database layer  105  (i.e., HANA), including the features thereof (e.g., calculated columns) are capable of being performed faster than traditional database systems that necessarily transfer data between the database layer and other layers (e.g., application layer). 
     In some embodiments herein, processes and systems might leverage technical aspects and features of an in-memory database infrastructure or framework to effectuate database authorization policies using column-based access controls. 
     Embodiments herein are not limited to an in-memory implementation of a database. For example, data may be stored in Random Access Memory (e.g., cache memory for storing recently-used data) and other forms of solid state memory and/or one or more fixed disks (e.g., persistent memory for storing their respective portions of the full database). 
       FIG. 2  is an illustrative example of database tables  201  used to store transactional data that forms the source data for On-Line Transactional Processing (OLTP) functions and systems. The data structure(s)  201  are configured for transaction-oriented applications. Data tables  201  relate to a purchase order and include PurchaseOrder OLTP table  205  having a primary key of PurchaseOrderNumber and the attributes PurchaseOrderDate and Supplier; Supplier OLTP table  210  including information for suppliers; PurchaseOrderltem OLTP table  215  that includes information for each item on a purchase order (e.g., product, quantity, price, and amount); and ScheduleLine OLTP table  220  that includes information that describes, for each purchase order item, a scheduled quantity, a delivered quantity, an issued quantity, and an item delivery date, where there may be more than one delivery date for a particular item and more than one schedule line item for a particular purchase order item.    
       FIG. 2  further illustrates an example of an On-Line Analysis Processing data structure  202  corresponding to the OLTP data structure  201 . OLAP data is configured for use and processing by OLAP applications. In some regards, some data and database systems traditionally stored data as OLTP data (e.g.,  201 ). However, OLTP data  201  might need to be transformed to an OLAP data  202  configuration for use and processing by an OLAP application. An ETL (Extract, Transform, and Load) process  225  may be used to extract data from a source (e.g., OLTP data source  201 ), transforming the data by one or more operations, functions, and processes, and loading the data to an application (e.g., an OLAP application), a system (e.g., a database system or service), and repository (e.g., in-memory database  105  in  FIG. 1 ). In the example of  FIG. 2 , ETL process  225  might operate to generate OLAP data  202  including data table  230 . ETL process  225  may aggregate the data of OLTP data table  215  (i.e., PurchaseOrderItem) to generate a new calculated attribute or property “Overdue” as shown at  235 , which can be used by an OLAP application to analyze and report desired attribute values. 
     While ETL process  225  provides a mechanism to transform an OLTP data source  201  into an OLAP data source  202 , the ETL process and the two data sources depicted in  FIG. 2  are not without their problems. For example, two data structures must be maintained (e.g., updated periodically to remain current), separate hardware is needed for each data source, and storing the two data sources consumes more stage resources than the OLTP data source alone. 
       FIG. 3  is an illustrative depiction of the classic solution for transforming an OLTP data source  305  to OLAP data source  330 , wherein the OLAP data structure may be used by an OLAP application for analysis and reporting purposes. In some aspects, in order to do some analysis on the OLTP data  305 , ETL step or process  327  performs a transformation to, for example, combine and/or aggregate the OLTP data to perform calculations on the attributes thereof. In  FIG. 3 , OLTP data  305  includes four entities or data tables. Supplier  310  and purchase order  315  are joined by a foreign key as illustrated. Attributes of the purchase order  315  are related to purchase order item  320  and attributes of purchase order item  320  are related to schedule line  325  of the purchase order item. ETL process or step  327  produces aggregated OLAP data  330  that includes some aspects of the OLTP data source, such as supplier  335 , purchase order  340 , and purchase order item  345 . Additionally, OLAP data source  330  includes the “Overdue” calculated attribute  350 . 
     The  FIG. 3  example demonstrates how some of the OLTP data  305  is replicated for purposes of the OLAP data source  330  by the ETL process  327 . Such data replication is costly from a memory storage perspective, particularly for an in-memory implementation (e.g., an in-memory database). 
       FIG. 4  is an illustrative example depicting some aspects and features of the present disclosure. Data structure  400 , in general, extends an OLTP data structure and further defines a calculated property to obtain a single data structure that can function as an OLTP data source and an OLAP data source.  FIG. 4  includes supplier  405  and purchase order  410  that are similar to the supplier  310  and purchase order  315  of  FIG. 3 . Data structure  400  further includes purchase order  415  and schedule line of purchase order item  420  that may be similar to the purchase order  320  and purchase order  325  of  FIG. 3 , respectively. Entities  405 ,  410 ,  415 , and  420  exist, as seen by a comparison to the traditional data structures of  FIG. 3 . However, data structure  400  is changed from data structure  305  in that data structure  400  adds new fields to the existing OLTP data structure, as illustrated by the new persisted fields  425 . The added fields  425  operate to extend the OLTP data source. The added field(s)  425  are added to ensure that a desired calculated property or attribute can be determined. 
     Data structure  400  further includes a new property or attribute at  430 . The new attribute may be based on the newly introduced filed(s)  425  that further extend the OLTP data structure. In some aspects, the calculated property is defined by the data structure  400  (i.e., at a design time) and the values for the calculated property  430  are determined or otherwise calculated during a runtime or other utilization of data structure  400  (e.g., by an OLAP application or service). 
       FIG. 5  is an illustrative depiction of data tables for a data structure corresponding to some example embodiments herein. For example, the tables illustrated in  FIG. 5  may correspond to the data structure introduced and described in  FIG. 4 . Tables  505  and  510  are the same or similar to the OLTP data tables shown in  FIG. 2 . However,  FIG. 5  differs from the OLTP data structure in a number of ways. For example, OLTP table  525  is extended to include, in the present example, two additional (new) attributes—“Overdue Date”  530  and “Overdue Quantity”  535 . These new attributes are added since additional information is needed to generated the desired calculated property  520  (e.g., “Item Overdue”). The value for calculated property  520  is determined based on the newly added attributes of “Overdue Date”  530  and “Overdue Quantity”  535 . The table  515  including the “PurchaseOrderItem” may be referred to as a Core Data Service since, for example, it includes more than data but further includes a function or formula for calculating a value for the defined calculated property or attribute. 
     The value for the calculated property may be determined “on-the-fly” by an in-memory database system or application. As used herein, the term “on-the-fly” refers to functions, operations, and calculations performed during a runtime execution of an application where the values generated are not persisted in a memory but the underlying formula(s) and/or function(s) are instead persisted. In this manner, updates to the formulas or other defining aspects of the calculated property may be automatically accounted for in value calculations. 
     In the example of  FIG. 5 , the calculated property aggregates data from the ScheduleLine table and its value is calculated on-the-fly. 
       FIG. 6  is an illustrative flow diagram of a process, in accordance with some embodiments herein. While some aspects of  FIG. 6  might be captured in some other portions herein,  FIG. 6  relates to some embodiments (e,g., system, method, and medium) for a single data structure that unifies OLTP and OLAP features and can be used by OLTP and OLAP applications. At operation  605 , OLTP data is accessed. The OLTP data may be accessed from a data store or memory or service or application. The OLTP data might be newly generated or previously generated for or by applications unrelated to an application or system performing process  600 . 
     At operation  610 , the OLTP data source is extended to include new attributes. The new attributes may be added to the data tables of the data source accessed at operation  605 . More than one attribute may be newly added and the one or more attributes may be added to one or more of the tables comprising the OLTP source data. 
     At operation  615 , at least one calculated property for at least one of the plurality of tables can be defined. The value for the at least one calculated property may actually be calculated during a runtime analysis of the extended OLTP data source (i.e., the OLTP data source having the at least one new attribute and the defined calculated property). 
     The result of the OLTP data source having the at least one new attribute and the defined calculated property may be saved in a memory in the form of a record or other data structure (e.g., database table) at operation  620 . In some embodiments, the memory may be a persistent memory. 
       FIG. 7  illustrates an exemplary system diagram for performing the processes described herein. Apparatus  700  includes processor  705  operatively coupled to communication device  720 , data storage device  730 , one or more input devices  715 , one or more output devices  725  and memory  710 . Communication device  720  may facilitate communication with external devices, such as a reporting client, or a data storage device. Input device(s)  715  may comprise, for example, a keyboard, a keypad, a mouse or other pointing device, a microphone, knob or a switch, an infra-red (IR) port, a docking station, and/or a touch screen. Input device(s)  715  may be used, for example, to enter information into apparatus  700 . Output device(s)  725  may comprise, for example, a display (e.g., a display screen) a speaker, and/or a printer. 
     Data storage device  730  may comprise any appropriate persistent storage device, including combinations of magnetic storage devices (e.g., magnetic tape, hard disk drives and flash memory), optical storage devices, Read Only Memory (ROM) devices, etc., while memory  710  may comprise Random Access Memory (RAM), Storage Class Memory (SCM) or any other fast-access memory. 
     Database engine  735  may comprise logic executed by processor  705  to cause apparatus  700  to perform any one or more of the processes described herein (e.g.,  400  and  600 ). Embodiments are not limited to execution of these processes by a single apparatus. 
     Data  740  (either cached or a full database) may be stored in volatile memory such as memory  725 . Data storage device  730  may also store data and other program code and instructions for providing additional functionality and/or which are necessary for operation of apparatus  700 , such as device drivers, operating system files, etc. 
     The foregoing diagrams represent logical architectures for describing processes according to some embodiments, and actual implementations may include more or different components arranged in other manners. Other platforms, frameworks, and architectures may be used in conjunction with other embodiments. Moreover, each component or device described herein may be implemented by any number of devices in communication via any number of other public and/or private networks. Two or more of such computing devices may be located remote from one another and may communicate with one another via any known manner of network(s) and/or a dedicated connection. Each component or device may comprise any number of hardware and/or software elements suitable to provide the functions described herein as well as any other functions. For example, any computing device used in an implementation of a system according to some embodiments may include a processor to execute program code such that the computing device operates as described herein. 
     All systems and processes discussed herein may be embodied in program code stored on one or more non-transitory computer-readable media. Such media may include, for example, a floppy disk, a CD-ROM, a DVD-ROM, a Flash drive, magnetic tape, and solid state Random Access Memory (RAM) or Read Only Memory (ROM) storage units. Embodiments are therefore not limited to any specific combination of hardware and software. 
     Embodiments described herein are solely for the purpose of illustration. Those in the art will recognize other embodiments may be practiced with modifications and alterations to that described above.