WO1993000642A1

WO1993000642A1 - Data transfer and storage means

Info

Publication number: WO1993000642A1
Application number: PCT/GB1992/001121
Authority: WO
Inventors: William Frederick Cawley
Original assignee: Ambit Research Limited
Priority date: 1991-06-21
Filing date: 1992-06-19
Publication date: 1993-01-07
Also published as: GB9113390D0; AU2152592A; EP0591319A1

Abstract

A data transfer and storage means comprises an interface to be connected to the programme controlling the spreadsheet of a source database, the interface including means for defining the data to be extracted and identifying the context thereof and to transfer that to a target database.

Description

DATA TRANSFER AND STORAGE MEANS

The invention relates to the transfer and storage of data, and more particularly to such steps using computer and like equipment. More especially, the invention relates to the interchange of data between electronic spreadsheets (source database) and a new form of database (target database). An electronic spreadsheet, is a layout analogous to an accountant's ledger sheet having many columns and rows in which data are entered. The data may then be subjected to a variety of management tasks such as audit, forecast, variable analysis, market planning, corporate strategy planning and the like.

The advent of the personal computer has highlighted the need for suitable programs whereby managers can store and manipulate their own information without calling on the skills of outside technicians to construct programs for them. The spreadsheet presents the user with a cell structure on a two dimensional plane, the cells of which may be defined by the user to be constant or to be based on formulae, which are enacted as the source values of the formulae are entered or changed on demand.

The conventional spreadsheet stores its information within the same structure as the cell headings, and each data value has no significance outside its position in the spreadsheet, thus limiting the amount of data that may be handled with confidence to a very small proportion of that which may be required.

In our patent application no. PCT/GB90/01554 published as WO 91/06059 (agent's ref: P00063PCT) , there is described and claimed a particularly efficient and advantageous method of setting up a spreadsheet. It is not intended that all of the disclosure of that document is incorporated herein merely by this reference save that to remind the reader that a "Context" is the term used to identify the additional information required to define uniquely the contents of the cells containing data in a worksheet. The present invention relates to a modification or extension of that invention and provides a means for the transfer of data in a similar form to existing for use in a spreadsheets, (LOTUS EXCEL etc.) set up on any computer system and not just one set up according to the technique of the earlier case.

According to the invention in one aspect there is provided a data transfer and storage system, comprising an interface to be connected to the program controlling the spreadsheet source database containing the data, the interface including means for defining the data to be extracted from the source database and identifying the Context thereof and means for storage of the extracted data.

In the system of this invention, each cell required by the user of the spreadsheet is incorporated as a separate item into the database with the 'Context' names specified by the user attached to the item. A separate file is set up, the file being associated, e.g. paired with the database called a 'Context' file which holds all the names relevant to the data stored in the database. If any names attached to new data are not included in this 'Context' file, then such names will be incorporated into the 'Context' file.

The 'Context' file is also known as a 'data dictionary', but in the simple definition, no information other than the name of the Concepts will be included in the file.

The system is arranged to extract the data as follows:

1) The user must specify the 'ARK region' in the spreadsheet that contains the data he/she wishes to extract. (ARK is a trade mark of Ambit Research Ltd).

2) The user then defines one or more 'Context' regions that apply to that data. Insofar as the context regions consist of a single cell it is accepted that that cell applies to all the data in the 'ARK region'. If the Context region is a horizontal range of cells then it is expected that the horizontal length of that range will be the same as the horizontal length of the 'ARK region' and each element of the 'ARK region' will be tagged with the equivalent horizontal element in the 'Context' region. In a similar way, vertical regions or full two dimensional 'Context' regions are permitted, assuming that the dimensions match those of the 'ARK region'. The elements in the Context are described as "Concepts".

3) Many 'ARK regions' may be active simultaneously on a single spreadsheet.

The interface is arranged to display to the user a submenu containing at least the functions 'connect', 'define', 'read', 'write'. The interface may use functions from the source database, e.g. LOTUS EXCEL to help the process of definition.

The 'connect' function enables the user to connect an 'ARK database' of his choice to the current spreadsheet.

The 'define' function will ask the user to define the 'ARK regions' and their related 'Context' regions.

The 'read' and 'write' functions enable the user to transfer data from spreadsheet to database and back again.

The important feature of the system is that in the database each individual item of data in the 'ARK regions' is separately 'tagged' with a label containing at least the names of the relevant elements of the Context as defined by the user. In the particular case to which this application refers, the concept names are saved separately in the 'Context' file, so that thereafter, only the record number from the 'Context' file need be stored against the data items. The record for each item of data must thus contain, at minimum:

1) The record length of the item (or of the data itself)

2) The type of the data (number, alphanumeric, etc)

3) The count of the 'Contexts'

4) A list of the 'Context' items (which will typically be a list of numbers, those numbers being the record numbers of the Context items in the Context file. The numbers will typically be sorted into numerical order).

5) The data itself stored in a format recognisable from 2) above.

In addition, in the form to which this invention applies, each data item record will preferably contain the information:

6) The date and time when the item was entered or last changed

7) The initials of the person who changed it

8) The spreadsheet name, 'ARK region' and cell reference at which the data was entered or last changed.

The invention is illustrated by the following example.

The worksheet below is set up on the screen of a visual display unit. The Connect option is used to connect the worksheet to an existing database or one formatted according to our earlier patent application.

SPREADSHEET OF SALES

The Define function is now used. This asks the user to define the 'ARK Region' (in this case D7..F9) then a list of Context items or ranges. (The word "ARK" is a trade mark of Ambit

Research Ltd). The first Context item in this case is say

'Sales'. When the user enters Sales, if it is not already in the

ARK data dictionary, he will be asked to confirm that it is a new name.

The second Context range would be B7-B9 (Product X - Product Z). Each item is associated with the cells on the relevant lines. The names Product X, Product Y and Product Z would be checked for inclusion in the data dictionary. The same procedure would apply to D5-F5 (US..France) , E3 (1991) and F3 (Forecast).

The definition concluded, the Write option writes away the data to be stored in the ARK Region, e.g. 1191 is saved as Sales, 1991, Forecast, Product X, US.

Now-for forecasting purposes, the figure 1991 is altered to 1992. The Read option is selected. The system asks whether 1992 is a new name. When this is confirmed, all the data in the ARK region vanishes from the screen because there is no 1992 data yet in the database. Data may be entered or calculated to fill the ARK region. When Write is selected, this data is added to the existing data in the ARK database. Changing the cell E3 back to 1991 and selecting Read recovers all the 1991 information. The same process may be continued for 1990, 1993, 1994 etc. changing 'Forecast' to 'Actual' or 'Revised Forecast', and adding new countries or Products.

Other spreadsheets

Another spreadsheet can now be connected to the same database. Provided that the Context is correctly defined, any of the data that has been stored away can be retrieved.

For instance, the matrix

Sales Product X Product Y

1991

1992

can be filled with one Define and one Read instruction.

The method of this invention is advantageous in that it may be used to extract data stored on a variety of systems, e.g. LOTUS, 1-2-3 (LOTUS is a trade mark) and then store the extracted data and any modifications made thereto from time to time.

The invention is not limited to the embodiment shown. Other information pertinent to Concepts may be added to the file, and variations disclosed in the earlier patent application may also be included in this invention.

Claims

1. A data transfer and storage system, comprising an interface to be connected to the program controlling the spreadsheet of a source database containing the data, the interface including means for defining the data to be extracted from the source database and identifying the Context thereof and means for storage of the extracted data.

2. A system according to Claim 1, wherein each cell required by the user the intended spreadsheet is incorporated as a separate item into the target database with the 'Context' names specified by the user attached to the item.

3. A system according to Claim 2, including a separate file containing the names relevant to the data stored in the target database, whereby the items^" in the target database refer to record numbers in the Concept file.

4. A system according to any preceding Claim, wherein the system is arranged to carry out the steps of

1) system specifying the 'region' in the spreadsheet that contains the data to be extracted 2) defining one or more 'Context' regions that apply t that data; and

3) saving the selected Concept names in a separate sectio of the Concept file

5. A system according to Claim 4, wherein each data item i stored in a record which comprises

1) the record length of the item (or of the data itself)

2) the type of the data (number, alphanumeric, etc);

3) the count of the 'Contexts'

4) a list of the 'Context' items, using record number from the Concept file

5) the data presented in a format recognisable from 2) above.

6. A system according to Claim 5, in which the record includes:

1) the date and time when the item was entered or last changed; 2) the initials of the person who entered or changed it;

3) the spreadsheet name, 'selected region' and cell reference at which the data was entered or last changed.