WO2003088063A1 - A method of retrieving and viewing data from a database - Google Patents

A method of retrieving and viewing data from a database Download PDF

Info

Publication number
WO2003088063A1
WO2003088063A1 PCT/IB2003/001444 IB0301444W WO03088063A1 WO 2003088063 A1 WO2003088063 A1 WO 2003088063A1 IB 0301444 W IB0301444 W IB 0301444W WO 03088063 A1 WO03088063 A1 WO 03088063A1
Authority
WO
WIPO (PCT)
Prior art keywords
data
user
excel
database
file
Prior art date
Application number
PCT/IB2003/001444
Other languages
French (fr)
Inventor
Lodewyk Johannes Johnson
Anthony Neill Selsick
Original Assignee
Adcheck (Pty) Ltd
Priority date (The priority date is an assumption and is not a legal conclusion. Google has not performed a legal analysis and makes no representation as to the accuracy of the date listed.)
Filing date
Publication date
Application filed by Adcheck (Pty) Ltd filed Critical Adcheck (Pty) Ltd
Priority to AU2003219368A priority Critical patent/AU2003219368A1/en
Publication of WO2003088063A1 publication Critical patent/WO2003088063A1/en

Links

Classifications

    • G—PHYSICS
    • G06—COMPUTING; CALCULATING OR COUNTING
    • G06F—ELECTRIC DIGITAL DATA PROCESSING
    • G06F16/00—Information retrieval; Database structures therefor; File system structures therefor
    • G06F16/20—Information retrieval; Database structures therefor; File system structures therefor of structured data, e.g. relational data
    • G06F16/24—Querying
    • G06F16/248—Presentation of query results
    • G—PHYSICS
    • G06—COMPUTING; CALCULATING OR COUNTING
    • G06F—ELECTRIC DIGITAL DATA PROCESSING
    • G06F16/00—Information retrieval; Database structures therefor; File system structures therefor
    • G06F16/20—Information retrieval; Database structures therefor; File system structures therefor of structured data, e.g. relational data
    • G06F16/24—Querying
    • G06F16/242—Query formulation
    • G06F16/2423—Interactive query statement specification based on a database schema

Definitions

  • This invention relates to a method of retrieving and viewing data from a database.
  • Microsoft Excel has an external data function that connects the spreadsheet to most data sources, with the ability to refresh the data at any stage.
  • the user can then analyze the data offline. If the user wants to refresh the data, this can be refreshed from the central data source. If the data volumes exceed Excels limits, more programming from the system administrator is required.
  • the process determines the most suitable method of packaging data with Excel and then delivering the parcel to users for analysis.
  • a method of retrieving and viewing data from a database comprising the steps of:
  • the software is preferably Excel.
  • the external data file may be a cube file.
  • Figure 1 is a flowchart illustrating the steps carried out by the software of the present invention
  • Figure 2 shows the interface for a user to create a new dataset
  • Figure 3 shows the interface for a user to specify the data connection properties
  • Figure 4 shows the user interface for creating the tables
  • Figure 5 shows the user interface for selecting the fields within the tables
  • Figure 6 shows the user interface for creating a pivot table
  • Figure 7 shows the user interface for running the report.
  • FIG. 1 the figure is a flowchart illustrating the steps carried out by the software of the present invention.
  • a user using the software creates a new dataset in step 10 and gives the dataset a name. This is illustrated in Figure 2.
  • Figure 3 shows a user identifying the data connection properties.
  • step 12 the user then selects tables to be added through the dataset.
  • step 14 the user selects the fields within the tables which they wish to access, and this is illustrated in Figure 5.
  • step 16 the user creates a pivot table using the interface illustrated in Figure 6.
  • the interface mirrors Microsoft Excel's pivot table and the user creates the columns available on the top of the screen into an area in the bottom of the screen.
  • the user inputs a search strategy typically using an SQL select statement, shown the step 18.
  • step 20 the user runs the report action which allows the user to process their report.
  • the interface is illustrated in Figure 7.
  • Normally data is exported into Excel.
  • the data is placed on single or multiple sheets inside the Excel workbook. The user can then manipulate and analyze the data within Excel.
  • Excel is a very good analysis tool but can only hold a maximum of 65000 rows on a sheet, or approximately 100000 rows in its cache. This poses a problem when trying to fit more data on a sheet than Excel can handle.
  • One way around this is to connect Excel to an external data source (such as an SQL server), but the user must always have connectivity to this source. This is not practical or feasible.
  • the software of the present invention determines if the size of the data requested excess Microsoft Excel's resource parameter. If the data volume is lower than the resource parameter, the data is placed inside an Excel workbook in the step 24.
  • an external data file containing the data is created.
  • the external data file is created in the form of a local cube file using the structure input by the user in the pivot table described above. It will be appreciated that the external data file could be another data file such as extracting the data into an Access file.
  • Excel is referenced to the external data source so that when the Excel workbook is accessed, it automatically references the external data source without further input from the user required.
  • step 28 a data parcel is created with Excel and the external data.
  • the compression of the file to be shipped is optional in (step 30) and the file is then transmitted to the user either by mail, file transfer protocol (FTP) or using any other method.
  • FTP file transfer protocol
  • step 34 the user is able to author the Excel spreadsheet and format the report. This formatting is returned to the main server by mail, (FTP) or by being saved.
  • FTP mail,
  • step 36 the server stores the formatting of the report as a master report and the next time the user requests a report the formatting will look like the latest uploaded master report format.
  • the present invention allows the seamless creating of an external data file which expands the functionality of Excel from the user's point of view without requiring any user input. It will also be appreciated that although the present invention is described with reference to Excel, the invention would also find application with any other spreadsheet which has a relatively smaller data cache.
  • the present invention allows the user to format an edit their own mater reports to ensure that the data they receive is in their preferred format.

Landscapes

  • Engineering & Computer Science (AREA)
  • Theoretical Computer Science (AREA)
  • Databases & Information Systems (AREA)
  • Physics & Mathematics (AREA)
  • Computational Linguistics (AREA)
  • Data Mining & Analysis (AREA)
  • General Engineering & Computer Science (AREA)
  • General Physics & Mathematics (AREA)
  • Mathematical Physics (AREA)
  • Information Retrieval, Db Structures And Fs Structures Therefor (AREA)

Abstract

A method of retrieving and viewing data from a database includes the steps of receiving an input search strategy from a user and querying the database to determine the amount of data which will be returned by the search strategy. If the amount of data will exceeds the capacity of the software used for viewing said data. Then an external data file containing the viewable data is created. The external data file may be a cube file. The instant invention may comprise spreadsheet objects (36), as well as manipulation of datasets (10), tables (12), fields (14), pivot tables (16), and sql objects (18), resulting in a report (20).

Description

A METHOD OF RETRIEVING AND VIEWING DATA FROM A DATABASE
BACKGROUND OF THE INVENTION
This invention relates to a method of retrieving and viewing data from a database.
There are many software applications commercially available for exporting data from a data source such as SQL Server, Analysis Services, Oracle, Microsoft Access, Text files etc to Microsoft Excel. Once the data has been exported to Excel, users can then utilize ExcePs features to analyze the data. The limitation is the amount of data that Excel can physically store, in terms of the number of records and memory. As hardware improves this limitation will reduce. Due to Microsoft's Excel being so widely used, powerful data analysis features and minimal training required, it is currently the preferred method of receiving data.
Microsoft Excel has an external data function that connects the spreadsheet to most data sources, with the ability to refresh the data at any stage.
In existing systems, system administrators or programmers have to create individual spreadsheets, either populated with data or having a connection to an external data source.
If the spreadsheet is populated with data, the user can then analyze the data offline. If the user wants to refresh the data, this can be refreshed from the central data source. If the data volumes exceed Excels limits, more programming from the system administrator is required.
If Excel is connected to Analysis Server, every data request from the user requires CPU time, thus increasing the network traffic and connectivity (CPU time and network traffic being uncontrolled). Deploying large quantities of these reports into organizations would result in high cost scalability issues.
It is an object of the present invention to provide a simplified process of extraction, packaging and distribution of data for analysis using Microsoft Excel as a tool. This includes the seamless detection of size limitations within the Microsoft Excel Object and the creation of single member hierarchy multidimensional local cube files from relational databases.
The process determines the most suitable method of packaging data with Excel and then delivering the parcel to users for analysis. SUMMARY OF THE INVENTION
According to the present invention there is provided a method of retrieving and viewing data from a database, the method comprising the steps of:
receiving an input search strategy from a user;
querying the database to determine the amount of data which will be returned by the search strategy;
determining if the amount of data will exceed the capacity of the software which will be used to view the data; and
if the amount of data will exceed the capacity of the software then creating an external data file containing the data which can be viewed using the software.
The software is preferably Excel.
The external data file may be a cube file.
DESCRIPTION OF THE DRAWINGS
Figure 1 is a flowchart illustrating the steps carried out by the software of the present invention;
Figure 2 shows the interface for a user to create a new dataset;
Figure 3 shows the interface for a user to specify the data connection properties; Figure 4 shows the user interface for creating the tables;
Figure 5 shows the user interface for selecting the fields within the tables;
Figure 6 shows the user interface for creating a pivot table; and
Figure 7 shows the user interface for running the report.
DESCRIPTION OF AN EMBODIMENT
Referring to Figure 1 , the figure is a flowchart illustrating the steps carried out by the software of the present invention.
A user using the software creates a new dataset in step 10 and gives the dataset a name. This is illustrated in Figure 2.
Figure 3 shows a user identifying the data connection properties.
In step 12, and as illustrated in Figure 4, the user then selects tables to be added through the dataset.
In step 14, the user selects the fields within the tables which they wish to access, and this is illustrated in Figure 5.
In step 16, the user creates a pivot table using the interface illustrated in Figure 6. The interface mirrors Microsoft Excel's pivot table and the user creates the columns available on the top of the screen into an area in the bottom of the screen. Once the pivot table has been created, the user inputs a search strategy typically using an SQL select statement, shown the step 18.
In step 20, the user runs the report action which allows the user to process their report. The interface is illustrated in Figure 7.
The above steps are well known in the art and will not be described here in more detail.
Normally data is exported into Excel. The data is placed on single or multiple sheets inside the Excel workbook. The user can then manipulate and analyze the data within Excel.
However, Excel is a very good analysis tool but can only hold a maximum of 65000 rows on a sheet, or approximately 100000 rows in its cache. This poses a problem when trying to fit more data on a sheet than Excel can handle. One way around this is to connect Excel to an external data source (such as an SQL server), but the user must always have connectivity to this source. This is not practical or feasible.
Thus, in decision step 22, the software of the present invention determines if the size of the data requested excess Microsoft Excel's resource parameter. If the data volume is lower than the resource parameter, the data is placed inside an Excel workbook in the step 24.
If the size of the data exceeds the resource parameter then in step 26, an external data file containing the data is created. In the prototype of the present invention, the external data file is created in the form of a local cube file using the structure input by the user in the pivot table described above. It will be appreciated that the external data file could be another data file such as extracting the data into an Access file.
Excel is referenced to the external data source so that when the Excel workbook is accessed, it automatically references the external data source without further input from the user required.
It will be appreciated that the whole process from the user's point of view is seamless and non-technical users will not be aware that the external data file has been created.
In step 28, a data parcel is created with Excel and the external data.
In either of the above scenarios, the compression of the file to be shipped is optional in (step 30) and the file is then transmitted to the user either by mail, file transfer protocol (FTP) or using any other method.
In step 34, the user is able to author the Excel spreadsheet and format the report. This formatting is returned to the main server by mail, (FTP) or by being saved.
In step 36, the server stores the formatting of the report as a master report and the next time the user requests a report the formatting will look like the latest uploaded master report format.
Thus it will be appreciated that the present invention allows the seamless creating of an external data file which expands the functionality of Excel from the user's point of view without requiring any user input. It will also be appreciated that although the present invention is described with reference to Excel, the invention would also find application with any other spreadsheet which has a relatively smaller data cache.
Furthermore, the present invention allows the user to format an edit their own mater reports to ensure that the data they receive is in their preferred format.

Claims

1. A method of retrieving and viewing data from a database, the method comprising the steps of:
receiving an input search strategy from a user;
querying the database to determine the amount of data which will be returned by the search strategy;
determining if the amount of data will exceed the capacity of the software which will be used to view the data; and
if the amount of data will exceed the capacity of the software then creating an external data file containing the data which can be viewed using the software..
2. A method according to claim 1 wherein the software is Excel.
3. A method according to claim 1 wherein the external data file is a cube file.
PCT/IB2003/001444 2002-04-18 2003-04-17 A method of retrieving and viewing data from a database WO2003088063A1 (en)

Priority Applications (1)

Application Number Priority Date Filing Date Title
AU2003219368A AU2003219368A1 (en) 2002-04-18 2003-04-17 A method of retrieving and viewing data from a database

Applications Claiming Priority (2)

Application Number Priority Date Filing Date Title
ZA200203083 2002-04-18
ZA2002/3083 2002-04-18

Publications (1)

Publication Number Publication Date
WO2003088063A1 true WO2003088063A1 (en) 2003-10-23

Family

ID=29251516

Family Applications (1)

Application Number Title Priority Date Filing Date
PCT/IB2003/001444 WO2003088063A1 (en) 2002-04-18 2003-04-17 A method of retrieving and viewing data from a database

Country Status (2)

Country Link
AU (1) AU2003219368A1 (en)
WO (1) WO2003088063A1 (en)

Cited By (5)

* Cited by examiner, † Cited by third party
Publication number Priority date Publication date Assignee Title
WO2009052565A1 (en) * 2007-10-26 2009-04-30 Commonwealth Scientific And Industrial Research Organisation Method and system for information retrieval and processing
US7693860B2 (en) 2005-03-18 2010-04-06 Microsoft Corporation Method and system to associate cell and item metadata
US7792847B2 (en) 2005-09-09 2010-09-07 Microsoft Corporation Converting structured reports to formulas
US7805433B2 (en) 2005-10-14 2010-09-28 Microsoft Corporation Multidimensional cube functions
US8234293B2 (en) 2005-09-08 2012-07-31 Microsoft Corporation Autocompleting with queries to a database

Citations (2)

* Cited by examiner, † Cited by third party
Publication number Priority date Publication date Assignee Title
US5880742A (en) * 1993-09-17 1999-03-09 Xerox-Corporation Spreadsheet image showing data items as indirect graphical representations
US6366299B1 (en) * 2000-02-21 2002-04-02 Verizon Laboratories Inc. Multidimensional information visualization using attribute rods

Patent Citations (2)

* Cited by examiner, † Cited by third party
Publication number Priority date Publication date Assignee Title
US5880742A (en) * 1993-09-17 1999-03-09 Xerox-Corporation Spreadsheet image showing data items as indirect graphical representations
US6366299B1 (en) * 2000-02-21 2002-04-02 Verizon Laboratories Inc. Multidimensional information visualization using attribute rods

Cited By (5)

* Cited by examiner, † Cited by third party
Publication number Priority date Publication date Assignee Title
US7693860B2 (en) 2005-03-18 2010-04-06 Microsoft Corporation Method and system to associate cell and item metadata
US8234293B2 (en) 2005-09-08 2012-07-31 Microsoft Corporation Autocompleting with queries to a database
US7792847B2 (en) 2005-09-09 2010-09-07 Microsoft Corporation Converting structured reports to formulas
US7805433B2 (en) 2005-10-14 2010-09-28 Microsoft Corporation Multidimensional cube functions
WO2009052565A1 (en) * 2007-10-26 2009-04-30 Commonwealth Scientific And Industrial Research Organisation Method and system for information retrieval and processing

Also Published As

Publication number Publication date
AU2003219368A1 (en) 2003-10-27

Similar Documents

Publication Publication Date Title
TWI698108B (en) Blockchain-based data processing method and device
US20230334030A1 (en) System and method for slowly changing dimension and metadata versioning in a multidimensional database environment
US10545981B2 (en) Virtual repository management
US7251653B2 (en) Method and system for mapping between logical data and physical data
US8862540B2 (en) Replica placement strategy for distributed data persistence
US8239423B2 (en) System and method for semantic exposure of data stored in a dynamic schema
US7174345B2 (en) Methods and systems for auto-partitioning of schema objects
US7213208B2 (en) Data container for interaction between a client process and software applications
US8880463B2 (en) Standardized framework for reporting archived legacy system data
US20020093857A1 (en) System and method for managing information objects
CA2627270A1 (en) System and method for displaying data on a thin client
US20100235344A1 (en) Mechanism for utilizing partitioning pruning techniques for xml indexes
WO2018127747A1 (en) A method, apparatus and computer program product for user-directed database configuration, and automated mining and conversion of data
CN100498766C (en) Mass file management system and method based on database
CN106682879A (en) Integrated design flow standard system
US20140223100A1 (en) Range based collection cache
US7475088B2 (en) Systems and methods of providing data from a data source to a data sink
US7761461B2 (en) Method and system for relationship building from XML
US20040054640A1 (en) Interaction between a client process and software applications
WO2003088063A1 (en) A method of retrieving and viewing data from a database
CN102567432B (en) Intelligent information adaptation method and device for the same
Wrembel et al. Managing and querying versions of multiversion data warehouse
ZA200409209B (en) A method of retrieving and viewing data from a database.
KR20040077535A (en) System and method for generating a request for information about selected objects
Saxena et al. NoSQL Databases-Analysis, Techniques, and Classification

Legal Events

Date Code Title Description
AK Designated states

Kind code of ref document: A1

Designated state(s): AE AG AL AM AT AU AZ BA BB BG BR BY BZ CA CH CN CO CR CU CZ DE DK DM DZ EC EE ES FI GB GD GE GH GM HR HU ID IL IN IS JP KE KG KP KR KZ LC LK LR LS LT LU LV MA MD MG MK MN MW MX MZ NO NZ OM PH PL PT RO RU SC SD SE SG SI SK SL TJ TM TN TR TT TZ UA UG US UZ VC VN YU ZA ZM ZW

AL Designated countries for regional patents

Kind code of ref document: A1

Designated state(s): GH GM KE LS MW MZ SD SL SZ TZ UG ZM ZW AM AZ BY KG KZ MD RU TJ TM AT BE BG CH CY CZ DE DK EE ES FI FR GB GR HU IE IT LU MC NL PT RO SE SI SK TR BF BJ CF CG CI CM GA GN GQ GW ML MR NE SN TD TG

121 Ep: the epo has been informed by wipo that ep was designated in this application
WWE Wipo information: entry into national phase

Ref document number: 2004/09209

Country of ref document: ZA

Ref document number: 200409209

Country of ref document: ZA

122 Ep: pct application non-entry in european phase
122 Ep: pct application non-entry in european phase
NENP Non-entry into the national phase

Ref country code: JP

WWW Wipo information: withdrawn in national office

Country of ref document: JP