Note: Within nine months of the publication of the mention of the grant of the European patent in the European Patent Bulletin, any person may give notice to the European Patent Office of opposition to that patent, in accordance with the
1 6
10 235
B1
&
(11)
EP 1 610 235 B1
(12)
EUROPEAN PATENT SPECIFICATION
(45) Date of publication and mentionof the grant of the patent:
25.11.2009 Bulletin 2009/48
(21) Application number: 04014696.1 (22) Date of filing: 23.06.2004
(51) Int Cl.:
G06F 17/30(2006.01)
(54) A data processing system and method
System und Verfahren zur Datenverarbeitung Système et méthode de traitement de données (84) Designated Contracting States:
AT BE BG CH CY CZ DE DK EE ES FI FR GB GR HU IE IT LI LU MC NL PL PT RO SE SI SK TR
(43) Date of publication of application:
28.12.2005 Bulletin 2005/52
(73) Proprietor: SAP AG
69190 Walldorf (DE)
(72) Inventor: Schmitt, Winfried
69190 Walldorf (DE)
(74) Representative: Richardt, Markus Albert et al
Richardt Patents & Trademarks Leergasse 11
65343 Eltville am Rhein (DE)
(56) References cited:
US-A1- 2002 032 676
• WIDOM J: "Research problems in data
warehousing" PROCEEDINGS OF THE 1995 ACM CIKM INTERNATIONAL CONFERENCE ON INFORMATION AND KNOWLEDGE
MANAGEMENT ACM NEW YORK, NY, USA, December 1995 (1995-12), pages 25-30, XP002306922 ISBN: 0-89791-812-6
• MEGHINI C ET AL: "The complexity of operations on a fragmented relation" ACM TRANSACTIONS ON DATABASE SYSTEMS USA, vol. 16, no. 1, March 1991 (1991-03), pages 56-87, XP002306923 ISSN: 0362-5915
• IVES Z G ET AL: "An adaptive query execution system for data integration" SIGMOD RECORD ACM USA, vol. 28, no. 2, June 1999 (1999-06), pages 299-310, XP002306924 ISSN: 0163-5808 • ZAHARIOUDAKIS M. ET AL: ’Answering complex
SQL queries using automatic summary tables’ ACM SIGMOD RECORD vol. 29, no. 2, 16 May 2000, pages 105 - 116
5 10 15 20 25 30 35 40 45 50 55 Description
Field of the invention.
[0001] The present invention generally relates to the
field of data processing, and more particularly to online analysis processing (OLAP) database structures.
Background and prior art
[0002] Conventional relational databases are
well-known and data collected in support of large enterprises is often collected into relational databases. For example, an enterprise with a sales operation might store all of their data relating to sales transactions in a relational database. A relational database structure defines the ta-bles making up the relational database, along with defi-nitions for the rows and columns of the tables and the relations between tables.
[0003] For example, a relational sales database might
have an invoice table and a customer table. The invoice table might have columns for invoice number, customer number, salesperson, sales date, shipping date, etc., with one row per "instance" in the table. In this example, an instance is an invoice. The customer table might have one row per unique customer, and columns for customer number, customer name, address, credit limit, etc. As for the relations between tables, the relational sales data-base might relate customer number in the invoice table with customer number in the customer table.
[0004] Such relational structures are well-known and
several methods of navigating large relational databases are known. For example, a user at a relational database client might formulate a Structured Query Language ("SQL") statement and submit that SQL statement to a relational database server. The relational database serv-er would respond to the submission with a table of results that matched the SQL statement. For example, a user might request a list of invoices for a given day, listing the customer, the salesperson and the amount for each such invoice. The list might be informative if the enterprise only makes a few sales per day, but is less likely to be inform-ative if the enterprise makes thousands of sales per day.
[0005] To provide knowledge workers with informative
views of an enterprise’s data, analytical systems are of-ten employed. One example of an analytical system is a data warehouse. A data warehouse contains much the same data as the relational database, but in a much dif-ferent form. As should be apparent with the examples used above, adding one more invoices to the relational database could be as simple as adding a record with the invoice pertinent data to the invoice table.
[0006] For this reason, large relational databases used
in this way are often referred to as online transaction processing ("OLTP") systems. By contrast, the data warehouse usually stores data in aggregate, to allow for high-level analysis of the data. Often the data is aggre-gated according to multiple criteria, to provide access to
data and aggregations much faster than if the same in-formation were obtained from a relational database sys-tem.
[0007] Such systems of replicated and/or aggregated
data are often referred to as online analytical processing ("OLAP") systems. In a typical enterprise, the data ware-house is populated and updated periodically from the OLTP data. For example, US Patent Application 20030225798 shows a method of capturing data from on OLTP for data warehousing.
[0008] The updating process might, for example,
pro-vide invoice totals and other data extracted from the OLTP data to the OLAP data structures on a once-daily update. Using an OLAP system, a user might request a chart of the sales by geographic region broken down by month for a year’s worth of data.
[0009] If such a request were to be made of the OLTP
data structures, a server responding to that request would have to scan all the records in several tables to come up with totals for the chart. With one request, the scan might be easy, but when many, many requests are being made, it is more efficient to make those requests of an OLAP system, since the results for the chart can be obtained by taking the appropriate slice of data from the OLAP data structures. Sometimes, an OLAP data system is represented as a multi-dimensional data struc-ture and each OLAP query is simply a "slice" through this multi-dimensional data structure.
[0010] In one common analytical application, a user is
presented with a user interface at an OLAP client and uses that OLAP client to "navigate" a set of "cubes" (the multi-dimensional, or "MD" data structure) that were cre-ated from the OLTP data structures. Using that OLAP client, the user can navigate the OLAP data using top-down slicing and narrowing mechanisms, looking for points of interest within the information presented.
[0011] (OLTP) database structure and data stored in
at least one online analysis processing (OLAP) database structure. The system includes a dimension to domain server which interacts with a user interface client that presents, to a user, representations of elements of the OLTP database structure and representations of ele-ments of the OLAP database structure, wherein the user interface client also includes logic to accept a selection of representations of elements selected by the user and, if the selection comprises more than one element, an association among the elements in the selection. An el-ement relator is provided that relates one or more ele-ments of the OLTP database structure to one or more elements of the OLAP processing database structure when the selection of representations includes at least one element from the OLTP database structure and at least one element from the OLAP database structure. A query formulator, coupled to the user interface client, for-mulates the query based on the selection and any asso-ciations, wherein the query formulator is also coupled to the element relator when at least one association of the selection is an association between at least one element
5 10 15 20 25 30 35 40 45 50 55
from the OLTP database structure and at least one ele-ment the OLAP database structure. A query server re-ceives the query from the query formulator and provides responses to the query received from the query formu-lator.
[0012] As the execution of queries in the OLTP
data-base is expensive in terms of the required computational resources and due to the negative impact of such queries on the real-time capability of such an OLTP database the invention aims to provide an improved data processing system that reduces that amount of access operations to the OLTP database while providing a user with most up-to date data for analytical purposes.
[0013] WIDOM J: "Research problems in data
ware-housing" PROCEEDINGS OF THE 1995 ACM CIKM IN-TERNATIONAL CONFERENCE ON INFORMATION AND KNOWLEDGE MANAGEMENT ACM NEW YORK, NY, USA, December 1995 (1995-12), pages 25-30, XP002306922 ISBN: 0-89791-812-6 shows techniques for loading a warehouse from various information sourc-es.
[0014] ZAHARIOUDAKIS M; COCHRANE R; LAPIS
G; PIRAHESH H; URATA M: "Answering complex SQL queries using automatic summary tables" ACM SIGMOD RECORD, vol 29, no. 2, 16 May 2000 (2000-05-16), pag-es 105-116, disclospag-es a Query Graph Model that servpag-es as a basis for a matching algorithm.
Summary of the invention
[0015] The present invention provides a data
process-ing system that comprises a relational database for age of transaction data and an OLAP database for stor-age of a replication of the transaction data in one or more OLAP cubes. The replication of the transaction data can be a copy of the transaction data or an aggregation of the transaction data.
[0016] Data replication, i.e. the export of transaction
data from the relational database into the OLAP data-base, is performed at defined replication times. This can be done at predefined replication times, periodically or in accordance with a customised replication scheme. Typically data replication is performed daily during the night when no or little real time transaction data is entered into the relational database.
[0017] A user’s data request specifies at least a time
interval of interest to the user. In response to the data request the requested data is read from the respective OLAP cube and the OLAP cube data is stored in a random access memory. The OLAP cube data only contains data up to the last replication time. Transaction data that has been stored in the relational database after the last data replication is not present in the OLAP cube data.
[0018] In order to provide a complete response to the
user’s data request a query is generated that specifies a time interval spanning the time between the last replica-tion time and the upper limit of the time interval specified in the user’s data request. This query is executed by the
relational database which provides the transaction data missing in the OLAP cube data.
[0019] The data that is provided by the relational
da-tabase in response to the query is also stored in random access memory. An output is generated in response to the user’s data request on the basis of both the OLAP cube data and the data provided by the relational data-base in response to the query. Preferably the data is out-putted in a form that is familiar to the user, such as in the form of tabular data table, or pie chart or another con-venient output format.
[0020] The present invention is particularly
advanta-geous as it enables to include current transaction data into online analysis processing while only minimally load-ing the relational database that performs the online trans-action processing. This has the advantage that the real time capability of the relational database for the online transaction processing is not substantially affected while the user is provided with complete and most up to date data for online analytical processing. This is accom-plished by using the data already stored in the OLAP database and completing this data from the relational database by querying the relational database for data that is more recent than the last replication time. This minimises the number of time consuming mass storage access operations in the relational database structure.
[0021] Another advantage is that the process for
com-pletion of the data stored in the OLAP data structure with data received in response to the query from the OLTP database structure can be completely encapsulated such that no additional complexity needs to be added to the user interface.
[0022] In accordance with a preferred embodiment of
the invention the OLAP database has a plurality of OLAP cubes. Structural cube definition data that defines the structure of a cube but does not contain actual data val-ues is stored for each such OLAP cube. The user can specify one of the OLAP cube names in his or her data request. In response the OLAP cube data of the specified OLAP cube is read and stored temporarily in random access memory. The structural cube definition data of the respective OLAP cube is used for formulating the query that is sent to the OLTP database. For example, the query is formulated by means of an SQL statement corresponding to the structural cube definition data.
[0023] It is to be noted that the present invention has
applications in many fields, such as financial data processing systems, logistics data processing systems, supply chain management data processing systems and manufacturing control data processing systems.
Brief description of the drawings
[0024] In the following preferred embodiments of the
present invention are described in greater detail by way of example only, making reference to the drawings in which:
5 10 15 20 25 30 35 40 45 50 55
Figure 1 is a block diagram of an embodiment of the data processing system,
Figure 2 is a flowchart illustrating a preferred mode of operation of the data processing system of figure 1.
Detailed description
[0025] Figure 1 shows a data processing system that
has OLTP database 100 and OLAP database 102. OLTP database 100 is a relational database that is coupled to a transaction data source 104. Transaction data source 104 provides real time or near real time transaction data to OLTP database 100 such as financial postings, logistic postings or postings from a manufacturing control sys-tem.
[0026] The transaction data provided by the
transac-tion data source 104 in OLTP database 100 is stored in database tables 110 by program 106 that is executed by the processor 108 of the OLTP database 100. The data-base tables 110 are stored on a mass storage device 112, such as a disc drive or tape drive. In addition OLTP database 100 has random access memory (RAM) 114 that is used as a volatile main memory.
[0027] The OLTP database 100 is coupled to the OLAP
database 102 by a communication channel such as by a computer network, e.g. the Internet, an intranet or ex-tranet. The OLAP database 102 has a mass storage de-vice 116 for storing OLAP cubes A, B, C ... and for storing customising data 118.
[0028] The customising data 118 contains data 120
that indicates replication times when the OLAP database 102 is synchronised with the OLTP database 100. This data 120 can explicitly indicate the last replication time or it can define a replication scheme. For example, the data 120 can indicate a specific time of the day or night when the synchronisation of the OLAP database 102 and the OLTP database 100 is to be performed. Preferably, the data 120 specifies a time when the loading of the OLTP database 100 with incoming transaction data from transaction data source 104 is expected to be at a min-imum.
[0029] Customising data 118 further comprises cube
structural definition data 122. The cube structural defini-tion data 122 describes the data structure of each of the OLAP cubes A, B, C ... without including the respective OLAP cube data. Further, OLAP database 102 has a processor 124 for execution of a program 126 and a pro-gram 128 that provides a user interface, a random access memory 130 that is used as a volatile main memory, and a clock 132 that provides a time reference. For example clock 132 is implemented as a so called real time clock (RTC) that is battery powered.
[0030] In operation the transaction data source 104
provides a flow of transaction data to the OLTP database 100. The transaction data is processed and stored in the database tables 110 on mass storage device 112 by the
program 106.
[0031] When the next replication time as defined by
the data 120 is reached the OLAP database 102 sends a corresponding replication request 134 to the OLTP da-tabase 100 in order to request synchronisation of the OLAP database 102 with the OLTP database 100. In response program 106 reads at least the data that has been added or modified from the database tables 110 and sends this data as replication data 136 to the OLAP database 102 for updating of the OLAP cubes A, B, C ... The replication data 136 can be a copy of the transaction data stored in the database tables or it can be an gation of such transaction data; in the latter case aggre-gation is performed by the program 106.
[0032] A user can enter a query 138 into OLAP
data-base 102 by means of the user interface provided by the program 128. The user needs to select one of the OLAP cubes that are available in the OLAP database 102 and a time interval between a beginning time TB and an end time TE that is of interest for the user.
[0033] In response to the query 138 the program 126
reads the OLAP cube specified in the query 138 from mass storage device 116 into RAM 130. In addition the program 126 determines whether the time interval spec-ified in the query 138 is covered by the OLAP cube data that has been read from the mass storage device 116. The OLAP cube data read from the mass storage device 116 is complete if the upper limit of the time interval, i.e. the time TE, is not after the time of the last update TU, i.e. the last replication time, given by data 120. However, if the upper limit TE of the interval is past the time TU additional data is required from the OLTP database 102.
[0034] For example, the actual time and date provided
by clock 132 is 4 June 2004, 16 h. At this time the user enters a query 138 specifying a time interval between 4 May 2004 and 4 June 2004, 15 h. The data 120 specifies 2 h am as the replication scheme.
[0035] Using the data 120 and the current time
provid-ed by clock 132, the program 126 determines that the last replication has been performed on 4 June 2004 at 2 h am. Thus, transactional data that has been received by the OLTP database 100 between 4 June 2004, 2 h am and 4 June 2004, 15 h is not contained in the respec-tive OLAP cube stored by the mass storage device 116.
[0036] As a consequence the program 126 generates
a query that specifies this time interval, i.e. the time be-tween the last synchronisation bebe-tween the OLTP data-base 100 and the OLAP datadata-base 102 and the upper limit TE of the time interval specified in the query 138. In addition the query 140 can contain an SQL statement that corresponds to the cube structural definition data 122 of the cube that is specified in the query 138.
[0037] In response to the query 140 the program 106
reads data from the mass storage device 112 that match-es the query and providmatch-es this data 142 to OLAP data-base 102 where it is stored in the RAM 130. The data 142 complements the OLAP cube data that has been read from the mass storage device 116. On the basis of
5 10 15 20 25 30 35 40 45 50 55
the OLAP cube data and the data 142 that is temporarily stored in RAM 130 the program 126 generates an output that is shown to the user by means of the user interface. Preferably the output is tabular and has the same form irrespective of whether data 142 has been used for gen-erating the output or not.
[0038] Figure 2 shows a corresponding flowchart. In
step 200 a user enters a query that specifies one of the OLAP cubes and a time interval between TB and TE that is of interest for the user.
[0039] In response the respective OLAP cube data is
read from the mass storage of the OLAP database (step 202). The OLAP cube data is stored in the RAM of the data processing system in step 204. In parallel to steps 202 and 204 or sequentially the steps 206 to 210 are executed in order to complement the OLAP cube data with data from the OLTP database, if necessary.
[0040] If the upper limit of the interval specified in the
query is past the last replication time TU, i.e. the time of the last synchronisation of the OLAP and OLTP databas-es, a query is generated and sent to the OLTP database in order to obtain transaction data from the OLTP data-base that has been received from the transaction data source in the time interval spanning the last replication TU and the upper limit TE of the time interval of interest to the user. In step 208 the query response is received from the OLTP database and stored in RAM (step 210). This data received from the OLTP database comple-ments the OLAP cube data read from the mass storage of the OLAP database. In step 212, the OLAP cube data and the data received as a query response from the OLTP database is used to generate an output as a response to the query received in step 200.
List of Reference Numerals
[0041]
100 OLTP Database 102 OLAP Database
104 Transaction Data Source 106 Program
108 Processor 110 Database Tables 112 Mass Storage Advice
114 RAM
116 Mass Storage Device 118 Customising Data 120 Data
122 Cube Structural Definition Data 124 Processor 126 Program 128 Program 130 RAM 132 Clock 134 Replication Request 136 Replication Data 138 Query 140 Query 142 Data Claims
1. A data processing system comprising:
- a relational OLTP database (100, 110, 112) for storage of transaction data, said relational OLTP database coupled to a transactional source of real time or near real time data.
- an OLAP database (102) for storage of a rep-lication of the transaction data in an OLAP cube (A B, C,...),the OLAP database having a plurality of OLAP cubes and structural cube definition da-ta (122) for each such OLAP cube,
- means (116) for storing data (120) indicative of a replication time (TU), the replication time being the time when a synchronisation of the OLAP database and the relational OLTP data-base is performed,
- random access memory means (130), - means (124, 128) for receiving into the OLAP database a data request (138), the data request specifying at least a first time interval (TB-TE), and one of the OLAP cubes,
- means (124, 126) for reading OLAP cube data from the specified OLAP cube, said OLAP cube data corresponding to the portion of the data re-quested in the data request contained within the OLAP cube,
- means (124, 126) for storing the OLAP cube data in a random access memory,
- means (124, 126) for determining the tion time using the data indicative of the replica-tion time,
- means (124, 126) for using the structural cube definition data of the specified OLAP cube to generate a query (140) for the relational OLTP database specifying a second time interval, the second time interval having a lower limit (TU) given by the replication time and an upper limit (TE) given by the upper limit of the first time in-terval, said query concerning the data corre-sponding to the portion of the data requested in the data request not contained within the OLAP cube,
- means (124, 126) for receiving replication data from the relational OLTP database in response to the query,
- means (124, 126) for storing the replication da-ta in the random access memory,
- means (124, 126) for generating an output as a response to the data request using the OLAP cube data and the replication data stored in the random access memory.
5 10 15 20 25 30 35 40 45 50 55
2. The data processing system of claim 1, the query
comprising an SQL statement corresponding to the structural cube definition data of the specified OLAP cube.
3. The data processing system of any one of the
pre-ceding claims, further comprising a time reference (132), wherein the data indicative of the replication time specifies a replication time scheme, such that the replication time is determined by the replication time scheme and the actual time provided by the time reference when the data request is received.
4. The data processing system of any one of the
pre-ceding claims, wherein the output is a data table.
5. A data processing method comprising:
- receiving a data request (138) into an OLAP database (102), the OLAP database storing a plurality of OLAP cubes and structural cube def-inition data for each such OLAP cube, the data request specifying at least a first time interval (TB-TE) and one of the OLAP cubes, the OLAP database storing a replication of transaction da-ta from a relational OLTP dada-tabase (100, 110, 112),
- reading OLAP cube data from the specified OLAP cube of the OLAP database, the read OLAP data being the portion of the data of the data request contained within the specified OLAP cube,
- storing the OLAP cube data in a random access memory,
- determining a replication time, said replication time being the time when the OLAP database was synchronised with the relational OLTP da-tabase, coupled to a transactional source of real time or near real time data, storing up-to-date transaction data,
- generating a query using the structural cube definition data of the specified OLAP cube for the relational OLTP database, the query speci-fying a second time interval, the second time interval having a lower limit (TU) given by the replication time and an upper limit (TE) given by the upper limit of the first time interval, - receiving replication data from the relational OLTP database in response to the query, said replication data being the portion of the data re-quested in the request not contained within the OLAP cube,
- storing the replication data in the random ac-cess memory,
- generating an output as a response to the data request comprising the OLAP cube data and the replication data stored in the random access memory.
6. The data processing method of claim 5, further
com-prising reading an actual time from a time reference (132) and determining the replication time using the actual time and a replication time scheme.
7. The data processing method of claim 6, the
replica-tion scheme being stored as part of customizing da-ta.
8. A computer program product comprising computer
executable instructions for performing the steps of: - receiving a data request (138) into an OLAP database (102), the OLAP database storing a plurality of OLAP cubes and structural cube def-inition data for each such OLAP cube, the data request specifying at least a first time interval (TB-TE) and one of the OLAP cubes, the OLAP database storing a replication of transaction da-ta from a relational OLTP dada-tabase (100, 110, 112),,
- reading OLAP cube data from the specified OLAP cube of the OLAP database, the read OLAP data being the portion of the data request-ed in the data request containrequest-ed within the spec-ified OLAP cube,
- storing the OLAP cube data in a random access memory,
- determining a replication time, said replication time being the time when the OLAP database was synchronised with the relational OLTP da-tabase, coupled to a transactional source of real time or near real time data, storing up-to-date transaction data,
- instructions for generating a query using the structural cube definition data of the specified OLAP cube for the relational OLTP database, the query specifying a second time interval, the second time interval having a lower limit (TU) given by the replication time and an upper limit (TE) given by the upper limit of the first time in-terval,
- receiving replication data from the relational OLTP database in response to the query, said replication data being the portion of the data re-quested in the data request not contained within the OLAP cube,
- storing the replication data in the random ac-cess memory,
- generating an output as a response to the data request comprising the OLAP cube data and the replication data stored in the random access memory.
9. The computer program product of claim 8, further
comprising instructions for reading an actual time from a time reference (132) and determining the rep-lication time using the actual time and a reprep-lication
5 10 15 20 25 30 35 40 45 50 55 time scheme. Patentansprüche 1. Datenverarbeitungssystem, umfassend:
- eine relationale OLTP-Datenbank (100, 110, 112) zur Speicherung von Transaktionsdaten, wobei die relationale OLTP-Datenbank mit einer transaktionalen Quelle von Echtzeit- oder nahe-zu Echtzeit-Daten gekoppelt ist,
- eine OLAP-Datenbank (102) zur Speicherung einer Replikation der Transaktionsdaten in ei-nem Würfel (A B, C, ...) wobei die OLAP-Datenbank mehrere OLAP-Würfel und struktu-relle Würfeldefinitionsdaten (122) für jeden sol-chen OLAP-Würfel aufweist,
- ein Mittel (116) zum Speichern von Daten (120), die eine Replikationszeit (TU) angeben, wobei die Replikationszeit die Zeit ist, zu der ei-ne Synchronisation der OLAP-Datenbank und der relationalen OLTP-Datenbank durchgeführt wird,
- ein Direktzugriffspeichermittel (130),
- ein Mittel (124, 128) zum Empfangen einer Da-tenanforderung (138) in die OLAP-Datenbank, wobei die Datenanforderung mindestens ein er-stes Zeitintervall (TB-TE) und einen der OLAP-Würfel spezifiziert,
- ein Mittel (124, 126) zum Lesen von OLAP-Würfeldaten aus dem spezifizierten OLAP-Wür-fel, wobei die OLAP-Würfeldaten dem Teil der in der Datenanforderung angeforderten Daten entsprechen, der in dem OLAP-Würfel enthalten ist,
- ein Mittel (124, 126) zum Speichern der OLAP-Würfeldaten in einem Direktzugriffsspeicher, - ein mittel (124, 126) zum Bestimmen der Re-plikationszeit unter Verwendung der die Repli-kationszeit angebenden Daten,
- ein Mittel (124, 126) zum Verwenden der struk-turellen Würfeldefinitionsdaten des spezifizier-ten OLAP-Würfels, um eine Abfrage (140) für die relationale OLTP-Datenbank zu erzeugen, die ein zweites Zeitintervall spezifiziert, wobei das zweite Zeitintervall eine durch die Replika-tionszeit gegebene Untergrenze (TU) und eine durch die Obergrenze des ersten Zeitintervalls gegebenen Obergrenze (TE) aufweist, wobei die Abfrage bezüglich der Daten dem Teil der in der Datenanforderung angeforderten Daten entspricht, der nicht in dem OLAP-Würfel ent-halten ist,
- ein Mittel (124, 126) zum Empfangen von Re-plikationsdaten aus der relationalen OLTP-Da-tenbank als Reaktion auf die Abfrage,
- ein Mittel (124, 126) zum Speichern der
Repli-kationsdaten in dem Direktzugriffspeicher, - ein Mittel (124, 126) zum Erzeugen einer Aus-gabe als Reaktion auf die Datenanforderung un-ter Verwendung der OLAP-Würfeldaten und der Replikationsdaten, die in dem Direktzugriffspei-cher gespeiDirektzugriffspei-chert sind.
2. Datenverarbeitungssystem nach Anspruch 1, wobei
die Abfrage eine SQL-Anweisung umfasst, die den strukturellen Würfeldefinitionsdaten des spezifizier-ten OLAP-Würfels entspricht.
3. Datenverarbeitungssystem nach einem der
vorher-gehenden Ansprüche, ferner mit einer Zeitreferenz (132), wobei die die Replikationszeit angebenden Daten ein Replikationszeitschema spezifizieren, dergestalt, das die Replikationszeit durch das Repli-kationszeitschema bestimmt und die tatsächliche Zeit durch die Zeitreferenz bereitgestellt wird, wenn die Datenanforderung empfangen wird.
4. Datenverarbeitungssystem nach einem der
vorher-gehenden Ansprüche, wobei die Ausgabe eine Da-tentabelle ist.
5. Datenverarbeitungsverfahren mit den folgenden
Schritten:
- Empfangen einer Datenanforderung (138) in eine Datenbank (102), wobei die OLAP-Datenbank mehrere OLAP-Würfel und struktu-relle Würfeldefinitionsdaten für jeden solchen OLAP-Würfel speichert, wobei die Datenanfor-derung mindestens ein erstes Zeitintervall (TB-TE) und einen der OLAP-Würfel spezifiziert, wo-bei die OLAP-Datenbank eine Replikation von Transaktionsdaten aus einer relationalen OLTP-Datenbank (100, 110, 112) speichert, - Lesen von OLAP-Würfeldaten aus dem spezi-fizierten OLAP-Würfel der OLAP-Datenbank, wobei die gelesenen OLAP-Daten der Teil der Daten der Datenanforderung sind, der in dem spezifizierten OLAP-Würfel enthalten ist, - Speichern der OLAP-Würfeldaten in einem Di-rektzugriffsspeicher,
- Bestimmen einer Replikationszeit, wobei die Replikationszeit die Zeit ist, zu der die OLAP-Datenbank mit der relationalen OLTP-Daten-bank synchronisiert wurde, gekoppelt mit einer transaktionalen Quelle von Echtzeit- oder nahe-zu Echtzeit-Daten,
- Speichern von auf dem neuesten Stand be-findlichen Transaktionsdaten,
- Erzeugen einer Abfrage unter Verwendung der strukturellen Würfeldefinitionsdaten des spezi-fizierten OLAP-Würfels für die relationale OLTP-Datenbank, wobei die Abfrage ein zweites Zeit-intervall spezifiziert, wobei das zweite
Zeitinter-5 10 15 20 25 30 35 40 45 50 55
vall eine durch die Replikationszeit gegebene Untergrenze (TU) und eine durch die Obergren-ze des ersten Zeitintervalls gegebene Ober-grenze (TE) aufweist,
- Erhalten von Replikationsdaten aus der rela-tionalen OLTP-Datenbank als Reaktion auf die Abfrage, wobei die Replikationsdaten der Teil der in der Anforderung angeforderten Daten sind, der nicht in dem OLAP-Würfel enthalten ist, - Speichern der Replikationsdaten in dem Di-rektzugriffsspeicher,
- Erzeugen einer Ausgabe als Reaktion auf die Datenanforderung, die die OLAP-Würfeldaten und die Replikationsdaten umfasst, die in dem Direktzugriffspeicher gespeichert sind.
6. Datenverarbeitungsverfahren nach Anspruch 5,
fer-ner mit dem Schritt des Lesens eifer-ner tatsächlichen Zeit von einer Zeitreferenz (132) und des Bestim-mens der Replikationszeit unter Verwendung der tat-sächlichen Zeit und eines Replikationszeitschemas.
7. Datenverarbeitungsverfahren nach Anspruch 6,
wo-bei das Replikatinosschema als Teil von Anpas-sungsdaten gespeichert wird.
8. Computerprogrammprodukt mit
computerausführ-baren Anweisungen, die die folgenden Schritte aus-führen:
- Empfangen einer Datenanforderung (138) in eine Datenbank (102), wobei die OLAP-Datenbank mehrere OLAP-Würfel und struktu-relle Würfeldefinitionsdaten für jeden solchen OLAP-Würfel speichert, wobei die Datenanfor-derung mindestens ein erstes Zeitintervall (TB-TE) und einen der OLAP-Würfel spezifiziert, wo-bei die OLAP-Datenbank eine Replikation von Transaktionsdaten aus einer relationalen OLTP-Datenbank (100, 110, 112) speichert, - Lesen von OLAP-Würfeldaten aus dem spezi-fizierten OLAP-Würfel der OLAP-Datenbank, wobei die gelesenen OLAP-Daten der Teil der Daten der Datenanforderung sind, der in dem spezifizierten OLAP-Würfel enthalten ist, - Speichern der OLAP-Würfeldaten in einem Di-rektzugriffsspeicher,
- Bestimmen einer Replikationszeit, wobei die Replikationszeit die Zeit ist, zu der die OLAP-Datenbank mit der relationalen OLTP-Daten-bank synchronisiert wurde, gekoppelt mit einer transaktionalen Quelle von Echtzeit- oder nahe-zu Echtzeit-Daten,
- Speichern von auf dem neuesten Stand be-findlichen Transaktionsdaten,
- Instruktionen zum Erzeugen einer Abfrage un-ter Verwendung der strukturellen Würfeldefiniti-onsdaten des spezifizierten OLAP-Würfels für
die relationale OLTP-Datenbank, wobei die Ab-frage ein zweites Zeitintervall spezifiziert, wobei das zweite Zeitintervall eine durch die Replika-tionszeit gegebene Untergrenze (TU) und eine durch die Obergrenze des ersten Zeitintervalls gegebene Obergrenze (TE) aufweist,
- Erhalten von Replikationsdaten aus der rela-tionalen OLTP-Datenbank als Reaktion auf die Abfrage, wobei die Replikationsdaten der Teil der in der Anforderung angeforderten Daten sind, der nicht in dem OLAP-Würfel enthalten ist, - Speichern der Replikationsdaten in dem Di-rektzugriffsspeicher,
- Erzeugen einer Ausgabe als Reaktion auf die Datenanforderung, die die OLAP-Würfeldaten und die Replikationsdaten umfasst, die in dem Direktzugriffspeicher gespeichert sind.
9. Computerprogrammprodukt nach Anspruch 8,
fer-ner mit Instruktionen zum Lesen eifer-ner tatsächlichen Zeit von einer Zeitreferenz (132) und zum Bestim-men der Replikationszeit unter Verwendung der tat-sächlichen Zeit und eines Replikationszeitschemas.
Revendications
1. Système de traitement de données comprenant :
- une base de données relationnelles OLTP (100, 110, 112) destinée à la mémorisation de données de transactions, ladite base de don-nées relationnelles OLTP étant couplée à une source transactionnelle de données en temps réel ou pratiquement en temps réel,
- une base de données OLAP (102) destinée à la mémorisation d’une reproduction des don-nées des transactions dans un cube d’analyse OLAP (A, B, C, ...), la base de données OLAP possédant une pluralité de cubes OLAP et les données (122) structurelles de définition de cu-be pour chaque cucu-be OLAP de cette nature, - un moyen (116) permettant de mémoriser des données (120) indicatrices d’un temps de repro-duction (TU), le temps de reprorepro-duction étant le temps pendant lequel est effectuée une syn-chronisation de la base de données OLAP et de la base de données relationnelles OLTP, - un moyen de mémoire à accès direct (130), - un moyen (124, 128) destiné à recevoir dans la base de données OLAP une demande (138) de données, la demande de données spécifiant au moins un premier intervalle de temps (TB -TE) et l’un des cubes OLAP,
- un moyen (124, 126) permettant de lire les don-nées du cube OLAP à partir du cube OLAP spé-cifié, lesdites données du cube OLAP corres-pondant à la partie des données requises dans
5 10 15 20 25 30 35 40 45 50 55
la demande de données contenues à l’intérieur du cube OLAP,
- un moyen (124, 126) permettant de mémoriser les données du cube OLAP dans une mémoire à accès direct,
- un moyen (124, 126) permettant de déterminer le temps de reproduction en utilisant les don-nées indicatrices du temps de reproduction, - un moyen (124, 126) permettant d’utiliser les données structurelles de définition de cube pour le cube OLAP spécifié afin de générer une con-sultation (140) pour la base de données rela-tionnelles OLTP en spécifiant un second inter-valle de temps, le second interinter-valle de temps présentant une limite inférieure (TU) donnée par le temps de reproduction, ainsi qu’une limite su-périeure (TE) donnée par la limite susu-périeure du premier intervalle de temps, ladite consultation concernant les données correspondant à la par-tie des données requises dans la demande de données non contenues à l’intérieur du cube OLAP,
- un moyen (124, 126) permettant de recevoir des données de reproduction provenant de la base de données relationnelles OLTP en répon-se à la consultation,
- un moyen (124, 126) permettant de mémoriser les données de reproduction dans la mémoire à accès direct,
- un moyen (124, 126) permettant de générer une sortie en tant que réponse à la demande de données en utilisant les données du cube OLAP et les données de reproduction mémorisées dans la mémoire à accès direct.
2. Système de traitement de données selon la
reven-dication 1, dans lequel la consultation comprend une instruction en langage SQL correspondant aux don-nées structurelles de définition de cube pour le cube OLAP spécifié.
3. Système de traitement de données selon l’une
quel-conque des revendications précédentes, compre-nant en outre une référence de temps (132), dans laquelle les données indicatrices du temps de repro-duction spécifient un principe de temps de reproduc-tion de telle sorte que le temps de reproducreproduc-tion est déterminé par le principe de temps de reproduction et par le temps réel fourni par la référence de temps au moment où est reçue la demande de données.
4. Système de traitement de données selon l’une
quel-conque des revendications précédentes, dans le-quel la sortie est une table de données.
5. Procédé de traitement de données comprenant :
- la réception d’une demande de données (138)
dans une base de données OLAP (102), la base de données OLAP mémorisant une pluralité de cubes OLAP et des données structurelles de dé-finition de cube pour chaque cube OLAP de cet-te nature, la demande de données spécifiant au moins un premier intervalle de temps (TB - TE) et l’un des cubes OLAP, la base de données de traitements OLAP mémorisant une reproduction de données de transactions provenant d’une ba-se de données relationnelles OLTP (100, 110, 112),
- la lecture de données de cube OLAP provenant du cube OLAP spécifié de la base de données OLAP, les données lues du traitement OLAP re-présentant la partie des données de la demande de données contenues dans le cube OLAP spé-cifié,
- la mémorisation des données du cube OLAP dans une mémoire à accès direct,
- la détermination d’un temps de reproduction, ledit temps de reproduction étant le temps pen-dant lequel la base de données OLAP a été syn-chronisée avec la base de données relationnel-les OLTP, couplée à une source transactionnel-le de données en temps réel ou pratiquement en temps réel, en mémorisant les données de transactions à jour,
- la génération d’une consultation en utilisant les données structurelles de définition de cube pour le cube OLAP spécifié pour la base de données relationnelles OLTP, la consultation spécifiant un second intervalle de temps, le second inter-valle de temps présentant une limite inférieure (TU) donnée par le temps de reproduction ainsi qu’une limite supérieure (TE) donnée par la li-mite supérieure du premier intervalle de temps, - la réception des données de reproduction pro-venant de la base de données relationnelles OLTP en réponse à la consultation, lesdites don-nées de reproduction représentant la partie des données requises dans la demande non conte-nues à l’intérieur du cube OLAP,
- la mémorisation des données de reproduction dans la mémoire à accès direct,
- la génération d’une sortie en tant que réponse à la demande de données comprenant les don-nées du cube OLAP et les dondon-nées de repro-duction mémorisées dans la mémoire à accès direct.
6. Procédé de traitement de données selon la
reven-dication 5, comprenant en outre la lecture d’un temps réel provenant d’une référence de temps (132) et la détermination du temps de reproduction en utilisant le temps réel et un principe de temps de reproduc-tion.
reven-5 10 15 20 25 30 35 40 45 50 55
dication 6, le principe de reproduction étant mémo-risé comme partie des données personnalisées.
8. Produit de programme informatique comprenant des
instructions exécutables par ordinateur permettant d’effectuer les étapes définissant :
- la réception d’une demande de données (138) dans une base de données OLAP (102), la base de données OLAP mémorisant une pluralité de cubes OLAP et des données structurelles de dé-finition de cube pour chaque cube OLAP de cet-te nature, la demande de données spécifiant au moins un premier intervalle de temps (TB - TE) et l’un des cubes OLAP, la base de données OLAP mémorisant une reproduction de don-nées de transactions provenant d’une base de données relationnelles OLTP (100, 110, 112), - la lecture des données de cube OLAP prove-nant du cube OLAP spécifié de la base de don-nées OLAP, les dondon-nées lues du traitement OLAP étant la partie des données requises dans la demande de données contenues à l’intérieur du cube OLAP spécifié,
- la mémorisation des données du cube OLAP dans une mémoire à accès direct,
- la détermination d’un temps de reproduction, ledit temps de reproduction étant le temps pen-dant lequel la base de données OLAP a été syn-chronisée avec la base de données relationnel-les OLTP, couplée à une source transactionnel-le de données en temps réel ou pratiquement en temps réel, en mémorisant les données de transactions à jour,
- les instructions permettant de générer une con-sultation en utilisant les données structurelles de définition de cube pour le cube OLAP spécifié pour la base de données relationnelles OLTP, la consultation spécifiant un second intervalle de temps, le second intervalle de temps présen-tant une limite inférieure (TU) donnée par le temps de reproduction et une limite supérieure (TE) donnée par la limite supérieure du premier intervalle de temps,
- la réception des données de reproduction pro-venant de la base de données relationnelles OLTP en réponse à la consultation, lesdites don-nées de reproduction représentant la partie des données requises dans la demande de données non contenues à l’intérieur du cube OLAP, - la mémorisation des données de reproduction dans la mémoire à accès direct,
- la génération d’une sortie en réponse à la de-mande de données comprenant les données du cube OLAP et les données de reproduction mé-morisées dans la mémoire à accès direct.
9. Produit de programme informatique selon la
reven-dication 8, comprenant en outre des instructions de lecture en temps réel à partir d’une référence de temps (132) et de détermination du temps de repro-duction en utilisant le temps réel et un principe de temps de reproduction.
REFERENCES CITED IN THE DESCRIPTION
This list of references cited by the applicant is for the reader’s convenience only. It does not form part of the European patent document. Even though great care has been taken in compiling the references, errors or omissions cannot be excluded and the EPO disclaims all liability in this regard.
Patent documents cited in the description • US 20030225798 A [0007]
Non-patent literature cited in the description • Research problems in data warehousing. WIDOM J.
PROCEEDINGS OF THE 1995 ACM CIKM INTER-NATIONAL CONFERENCE ON INFORMATION AND KNOWLEDGE MANAGEMENT. ACM NEW YORK, 25-30 [0013]
• ZAHARIOUDAKIS M ; COCHRANE R ; LAPIS G ; PIRAHESH H ; URATA M. Answering complex SQL
queries using automatic summary tables. ACM SIG-MOD RECORD, 16 May 2000, vol. 29, 105-116
