Computer network

[0002] The present invention relates to a computer database with copies stated on different computers in the network which can be assessed and updated from individual computers an the network.

[0003] It is knows to replicate a keyed indexed 2-dimensional database over a computer network and i particular to, replicate changes in that database which have been made to different copies.

[0004] Replication describes the ability of databases to maintain multiple copies, or replicas, so that if a change is made to one copy of the database it will be replicated to the other copies. Replication can be one-way, meaning that only changes to a master copy will be replicated to the other copies, or two-way, meaning that one or a number of copies may be changed and that changes made will be reflected in all replica copies.

[0005] It is known to store data in multidimensional form in order to permit easy retrieval of particular slices of data from a multidimensional data set. Lotus Notes ® is one database programme having the replication feature. However, it is essentially a 2-dimensional database program.

[0006] The invention provides a network of computers having:

[0007] i) individual data entry means;

[0008] ii) at least one communications link for transferring data entered from a said data entry means to other computers in the network;

[0009] iii) a distributed N-dimensional database accessible and updateable by mid computers, N being an integer greater than two, said N-dimensional database comprising:

[0010] a) a Plurality of M-dimensional arrays where in is an integer greater than one and less than N, and

[0011] b) an ordered list of keys, each key being associated with a particular M-dimensional array and having a sting of N-M components which identify the associated M-dimensional army, said sting defining the order of the key in said list;

[0012] iv) searching means for finding a desired array in response to entry of said key from a said data entry means;

[0013] v) updating means for modifying a found M-dimensional array, and

[0014] vi) replicating means for replicating modifications to said modified M-dimensional array am the network.

[0015] Preferably M=2.

[0016] In one embodiment at least one of said M-dimensional arrays has a hierarchical structure, rows and/or columns at one level in the hierarchy being divided into groups at an adjacent lower level in the hierarchy and the levels in the hierarchy being selectable by individual users.

[0017] Preferably, levels in said hierarchical structure below the top level are associated with extra components in the string of the key corresponding to the top level of the hierarchy.

[0018] Preferably said replicating means comprises programmes for transmitting modifications to the distributed database over the network at predetermined intervals.

[0019] Preferably Sam program means further comprises override means for forcing a replication of modifications to the distributed database in response to entry of a command from a data entry means.

[0020] Preferably the network comprises spreadsheet means for performing calculation on the data in said distributed database.

[0021] The invention also provides a corresponding method of updating and accessing such a database.

[0022] A preferred embodiment of the invention is described below by way of example only with reference to FIGS. 1 to 8C of the accompanying drawings wherein:

[0023] FIG. 1 is a diagram of a computer network in accordance with the invention;

[0024] FIG. 2 is a diagrammatic representation of a multi-dimensional (N-dimensional) database utilised in the present invention;

[0025] FIG. 3 is a diagrammatic representation of the M-dimensional (in this case 2-dimensional) slices and their associated keys utilised in the multi-dimensional database of FIG. 2;

[0026] FIG. 4A is a representation of the daft format of slice 3 in FIG. 3;

[0027] FIG. 4B shows the corresponding two-dimensional data structure of slice 3;

[0028] FIG. 5 is a flow diagram illustrating writing of a data slice in the multi-dimensional database.

[0029] FIG. 6 is a flow diagram illustrating the reading of a data slice in the multi-dimensional database.

[0030] FIG. 7 is a diagrammatic representation of a two-dimensional data slice with a key for “drill down” to a deeper layer of the slice; and

[0031] FIG. 8A ohms two copies of a database respective computers before changes an entered and replicated;

[0032] FIG. 8B shows changes made independently on the two computers; and

[0033] FIG. 8C shows the replication of such changes.

[0034] The present invention enables two-way replication of a multi-dimensional (i.e. 3-dimensional or greater) database.

[0035] Replication can be achieved at record or field level. The current invention permits both record level and field level replication of multidimensional data.

[0036] The invention stores multidimensional data as 2-dimensional data slices, each associated with an (N−2) dimensional key, where N is the number of dimensions in the database.

[0037] When the database has more than 3 dimensions, it is harder to visualise, but the principle is the same. The multidimensional database consists of many such slices, each with a unique key. Fast access to the data is provided by indexing the keys associated with each slice to allow rapid retrieval of any slice referred to by its key.

[0038] In one implementation of the current invention, each slice of data is stored in a single field as a table of numbers with columns separated by TAB characters and rows separated by Carriage Return characters. In another implementation each of the cells is stored as a separate field.

[0039] The current invention uses the search engine of Lotus Notes to retrieve slices of data by key value, but a similar keyed indexed search could be used in any other system. Similarly the current invention uses Lotus Notes replication, but replication in any database could be used. Implementation of the current invention is demonstrated by two software functions, one to retrieve a slice of multidimensional data from a keyed indexed data base stored in Lotus Notes and one to place data into the database.

[0040] The function NotesKeyedSelect (“KEY1”) retrieves the two dimensional slice of data associated with the key “KEY1”. If the value of KEY1 is North;Manufacturing, then the data returned is the two dimensional slice of data relating to North and Manufacturing. This function can be used to retrieve data slices or comments. When then is more and one comment relating to a key, each of the comments is returned by a carriage return.

[0041] The source code to implement this function using the Lotus Notes 3.0 API for Microsoft Windows is provided in Appendix 1.

[0042] The function NotesKeyedInsert has “KEY1”) places a two dimensional slice of data “Slice” in a document with the key “KEY1”. If the slice already exists and its value has changed, it will be replaced. This function is used to store data where there is only one table of data associated with a key value.

[0043] The function NotesKeyedUniqueInsert (“Slice” “KEY1”) places a two dimensional slice of “Slice” in a document with the key “KEY1”. Even if the slice already exists a new document is added with the same key. This function is used to store comments where there may be more than one comment associated with a key value.

[0044] The source code to implement this function using the Lotus Notes 3.0 API for Microsoft Windows is provided in Appendix 1.

[0045] It is a further enhancement that if a document with a particular key already exists, data to be replaced is checked to see if it has changed and updated if it has changed. This smart update ensures that only slices which have actually changed will be modified and replicated. Without this enhancement, slices may be replicated unnecessarily causing additional replication time and communication cost.

[0046] It is a further purpose of this invention to allow textual comments relating to multidimensional data to be stored, replicated and shared. The retrieval of stored comments is demonstrated by the function NotesKeyedSelect where, if multiple documents am stored against an individual key, then the text in the data field of then shows is concatenated to give a list of comments.

[0047] It is a further purpose of The current invention to allow multiple textual comments to be stored in the database and associated with a particular slice of data.

[0048] Comments are added in the same way as data slices themselves are added to the database except that when a comment is added, it does not replace any existing document with the specified key, but is added is addition. To avoid confusion, in using the current invention to store comments associated with data, the key used for comments is prefixed or suffixed by the text “COMMENT”. For example, comments on the slice with key “North;Manufacturing” would be sorted with the key “COMMENT;North;Manufacturing.”

[0049] It is a further purpose of the current invention to provide drill-down from summary information to detail level information as wet as multidimensional access to information stored in a multidimensional database. To accomplish this, the key associated with each slice of specifies its position in a in drill-down hierarchy, for example the following keys are from documents used to store 3 levels of drill-down hierarchy.

[0050] World.

[0051] World;USA

[0052] World;USA;California

[0053] Drill-down functionality can be achieved by storing in each slice as, for example the row or column headings, the text which must be appended to the key value of the current slice to create the key value of the drill-down, or detail slice. This is demonstrated by the example documented by Appendix 2 (GLOBAL.SBA). The drill-down functionality is accomplished by taking the value of the row heading clicked on by the user, appending it to the key value of the current slice and retrieving the slice associated with the resulting key. As explained in Appendix 2, checks we made to ensure did drill-down only occurs when there is detail data for the row clicked on.

[0054] In a further extension of the current invention, drill-down can occur in two dimensions where both the value of the row and column heading of the current slice can be drilled-down upon.

[0055] In a further extension of the current invention, ad hoc query to choose a slice of a multidimensional database can be combined with drill-down by defining a key for slices which contains both drill-down information and other dimensions values. For example, the keys

[0056] Manufacturing;Worldwide:USA

[0057] and

[0058] Finance;Worldwide;USA;California

[0059] show that ad hoc query (to change Manufacturing to Finance) can be combined with drill-down to append the value California to the key Worldwide; USA. This extension is demonstrated the documentation in Appendix 2.

[0060] In a further extension of this invention, in addition to storing the table of numbers making up a 2 dimensional data slice in a single field, each of the numbers in the slice can be stored in separate fields.

[0061] In a further extension of this invention, the current invention can be used to store multidimensional data in a series of text files (one containing the key names of all slices and the names of files containing slice data) or with a text file for each slice and the files names replacing the key value. This extension is useful in that it allows the current invention to be used to store and retrieve data as HTML (text files) on the World-Wide-Web.

[0062] It is a further purpose of this invention to allow the replication of multidimensional databases which do not themselves support replication. That is to take modifications to daft in one database at one location and update another database which may be at a different location to reflect those changes.

[0063] This is using the current invention by methods including the following:

[0064] 1. By extracting a table of source data from Multidimensional database A at one location using appropriate function.

[0065] 2. Placing this data into Lotus Notes in the form described above as slices of data with keys for each slice using the NotesKeyedUniqueInsert function.

[0066] 3. Using Lotus Notes replication to transfer the data to another database.

[0067] 4. Retrieving the data from Lotus Notes using The NotesKeyedSelect function and placing this data into the replica copy of the database at location B.

[0068] Similarly a table of modifications can be extracted from database B and transferred to database A. This accomplishes two way replication.

[0069] 1. A log storing all changes to each database is stored in Lotus Notes in textual format. (As it slice of data with key specifying the databases to which it belongs. The slice specifies all changes made as a list of individual changes).

[0070] 2. Lotus Notes is used to replicate the log file of database A to database B and vice versa.

[0071] 3. A program is executed on database A which reads the log file for database B and makes all of the updates to database A which have been made to database B. Similarly, the log file for database A is replicated to database B and the updates specified in the log file for database B are made to database A.

[0072] 4. Comparison of the log files of database A and B is used to identify replication conflicts where the same number has been changed in both database A and database B, but to two different values. This exception is stored in a replication log to allow a program or operator to deal with the exception.

[0073] This scheme can be extended to allow replication between multiple databases by updating each database to reflect the log file changes of all the others.

[0074] One means of storing the log file is as follows (the line numbers are for clarity of explanation and are not to be a part of the log file:

[0075] 1. Database=185710-47375-067393

[0076] 2. NC=‘Sales’,‘Budget’,‘1996’,‘USA’=‘3200’=067393

[0077] 3. NC=‘Sales’,‘Actual’,‘1995’,‘USA’=‘1200’,‘2300’

[0078] 4. DCA=‘Year’=‘1998’

[0079] 5. DCD=‘Year’=‘1993’

[0080] 6. DA=‘Customer’

[0081] 7. End

[0082] This change log file specifies changes as follows:

[0083] 1. The log file is for database with unique identity: 185710-47375-067393

[0084] 2. The number for Budget Sales in 1996 in the USA has changed from 3200 to 3800

[0085] 3. The number for Actual Sales in 1995 in the USA has changed from 2100 to 2300

[0086] 4. An element by the name of 1998 has been added to the Year dimension

[0087] 5. The element by the name of 1993 has been deleted from the Year dimension.

[0088] 6. A new dimension has been added called Customer.

[0089] The log file can also contain of insertions, deletions and changes to calculation formulae.

[0090] Referring to FIG. 1, the computer network of the invention comprises computers A,B,C each having a keyboard it or other data entry means and linked by communications links L1,L2,L3, at least some of which (e.g. L3 in this illustration) can be long distance links, e.g. via modem over telephone lines, or even satellite links for example. The computers can be linked to the World-Wide-Web.

[0091] Each computer comprises a microprocessor is arranged to run a replication multidimensional database program P (in this case an augmented version of Lotus Notes®, as described below) and having RAM, ROM and a hard drive HD on which the database D is stored.

[0092] The network supports a Lotus Notes ® database, for which the required software is loaded in each computer. The user of each computer (preferably an 80486 machine or better, running Windows ®) can access and update the entire database. This involves changing a slice of a particular copy of the database. After a slice is saved and when the database is next replicated with a copy, changes an transferred to the replica copy, so that both copies now contain the same information. The database software is an augmented version of Lotus Notes ®. Before describing this process in more detail, the structure of the database will be described with reference to FIGS. 2 and 3.

[0093] Referring to FIG. 2. a four-dimensional database is illustrated as two three-dimension databases 1 and 2, relating to Europe and USA respectively, and each having month variables along one axis, sales, overhead and profit variables along another axis, and budget, actual and variance (=actual−budget) variables along the third axis. In this example than are 2×3³=54 cells in total, although in practice the number of cells will be much greater. The small number of cells shown is purely for illustration, and does not affect the principle of operation. A copy of the above N-dimensional database (N=4) is stored on the hard disc of each computer it the network.

[0094] In Lotus Notes ® an ordered list of keys is stored, each key having a single component or relating to a dimension of The database and each key being associated with a single one-dimensional record. The resulting two dimensional data structure is essentially a 2-dimensional keyed indexed database. In accordance with the present invention, as illustrated in FIG. 2, each key has N-M components (i.e. 4−2=2 components in an case) and is associated with a particular M dimensional array or slice (preferably M=2), so that each key is associated with a two-dimensional data slice 3,4 or 5 of the “EUROPE” part of 6,7 or 8 of the in part of the database. Far example the key “USA;BUDGET” relates to the data slice 6 containing the sales, overhead and profit figures for January, February and March.

[0095] The keys are listed in e.g. alphabetical order so that any slice of the 4-dimensional database can be retrieved by finding its associated key.

[0096] When such a slice (e.g. slice 4) has been retrieved by a particular user and amended, this amended version of the slice is replicated over the network from the database to which the change was made to replica copies when replication takes place.

[0097] Although FIG. 3 shows data slices 3 to 8 as being inherently two-dimensional it is important to note that in data slices are actually stored as strings, with the two-dimensional structure stored as control characters, eg. tab to separate adjacent data items in the same row has carriage return to separate adjacent rows. This is illustrated for slice 3 in FIG. 4A, which corresponds to the two-dimensional structure shown in FIG. 4B.

[0098] In principle a data structure of dimensionality greater than two could be defined by using more than two control characters.

[0099] The process of writing a data slice is illustrated in FIG. 5. “KEY1”, the first key in the list, is entered (step 10) to see if a corresponding document (i.e. data slice) exists (step 20) and if such a document does exist commands 40 and 50 are entered has shown. If the old datastring (i.e. the data in the slice) does not equal the new datastring being entered as the new datastring (data slice) then the appropriate datafield command is entered (step 60).

[0100] If no document with a matching key is found in step 20, the key field is set to “Key 1” and the data field is set to “datrastring” (step 30) before updating the database (step 70).

[0101] The reading process is shown in FIG. 6. The key for a given data slice is entered (step 80) and depending on whether or not a matching document (data slice) is found (step 90) an appropriate text string (eg. that shown above for slice) is returned (step 110) or an empty string is returned the (step 100).

[0102] Referring to FIG. 7, a data slice 6 of database 2 (FIG. 2) is shown, comprising two layers. The upper layer is provided with row or column headings H1,H2 and H3 of which H1 contains text (in this case: CALIFORNIA) which must be appended to the key value USA;BUDGET to create the key value of the lower “drill-down” layer for California.

[0103] Two replica databases as shown in FIG. 8A (and stored e.g. on computers A and B respectively) are assumed to contain the same data. As shown in FIG. 8B, changes are made to each of the two replicas independently where * indicates changed document from DBA and indicates changed document from DBB.

[0104] As shown in FIG. 8C, when the databases ‘replicate’ changes to ‘DBA’ are transferred to ‘DBB’ and vice versa. Where a document has been changed in ‘DBA’ and ‘DBB’, an exception condition (error) is logged for operator attention.

[0105] The result is that changes made to both database are replicated—transferred—to the other database. When each database contains documents which contain data slices and keys, the replication process can be used to facilitate replication of a multidimensional database.