VisiCube is no longer supported: Please visit Antaeus for a more advanced version of the same product!

VisiCube Knowledge Base Article #000010

How do I access database data?

Background

VisiCube provides direct access to text (ASCII) data files in either zoned or delimited form. A text data file is a file in which each record is comprised of the same data fields and these fields occur in the same relative position of each record. A zoned text file is one in which each field is in the same absolute position. A delimited text file is one in which each field is delimited by a special character (such as a tab, comma, or space). Most data, however, is stored in a proprietary data source, such as a databases, which is not directly supported by VisiCube.

Question

How do I access source data in a database (such as DB2, Oracle, SQL Server, or Access)?

Solution

You need to export your data to a text file. This is accomplished differently for each database, but generally can be accomplished with the database's export facility which allows you to save desired data into a text file. Most database export facilities allow you to generate either zoned or delimited data files, but this is dependent on the particular database program. For details, refer to the help system or documentation for your particular product.

In Microsoft Access 2002, data can be exported from a table to a zoned text file as follows:

  • Open your database and select the table containing the desired records.
  • In the "File" drop-down menu, choose the "Export..." option.
  • In the "Export Table as..." dialog:
    • In the "Save as type" field, choose the "Text Files (*.txt;*.csv;*.tab;*.asc)" option.
    • Specify the name and location of the new file in the standard manner.
    • Push the "Export" button.
  • In the "Export Text Wizard" dialog:
    • Choose the "Fixed Width" format. (This is Access' term for "zoned".)
    • Push the "Finish" button.

Note that the above procedure exports all records from the single selected table. If you wish to export a different set of records, use the database's query ability to generate a temporary table with the desired records and then export that temporary table.

The above is provided as an example. Each database program, as well as each version of such programs, has its own proprietary method of accomplishing this task. For details, refer to the help system or documentation for your particular product.

More Information

Many analysis products develop interfaces to obtain data from proprietary data sources to eliminate the extraction step described above. I have decided not to do so for a few reasons. The development effort to create and maintain those interfaces is always extensive and somewhat incomplete as there are innumerable sources. There are also compatibility issues as I attempt to keep the product (VisiCube in this case) up to date with changes made by other software manufactures (not all of whom are willing to expose their data structures, especially in advance of the release of their own product). Finally, such efforts are not directed at the desired core functionality. VisiCube is, after all, an analysis tool. That is why you use it. In the end, I have decided to keep my development efforts focused on the core functionality of VisiCube...giving you the best exploration tool I can.

References

  • PAR#100937; PAR#101296
  • Last Modified: June 12, 2004
  • Last Reviewed: June 12, 2004

 

THE DATAMOLOGY COMPANY Home of VisiCube, The Data Microscope