Getting Data Out Of Personal Express

The following question came up on comp.databases.olap:

"I realise that Oracle Personal Express v5 is hopelessly old, but we are trying to replace the app that it runs off. Unfortunately we need to get the data out of the existing database and I can find zero documentation on how to do this. When I had a look at the app I noticed there was a command line ... obviously though since at the mo this is a production system with no backup, I really don't want to go in and just mess with this. Does anyone have any idea

1) Where I can find any documentation to help me?

2) How the hell I use the CLI (or any other tool) to get at the data and
   extract it into XL or similar?"

As the poster thought, Personal Express is quite an old product now, and it's been upgraded and enhanced over the years and its latest incarnation was Oracle Express Server 6.3.4. OES had many similarities to Personal Express (programming language, datatypes and so on) and now Oracle have included the technology behind Express in the Oracle 9i Database server.

Getting data in and out of Express is a fairly common task, and as you suspected, it involves the command line interface that you've discovered. Basically, you need to look into the Express Programming Language, a procedural language that comes with all versions of Express that has commands for reading and writing to files, accessing databases (via ODBC), and interacting with users via a graphical user interface. To get data out of your Personal Express database, you'd need to put a program together to read the data in the database and copy it to a file - most probably, a comma separated (CSV) file. Of course you can do direct exports out of Personal Express and into another Express / Oracle 9i OLAP database, using the EIF file format, but this is specific to Express (like an Oracle export file) and it's usable outside of this environment.

You're going to need to get hold of some documentation to at least go through the basics of the Express architecture (which, being multidimensional, is quite different to the tables and columns approach of relational databases), and the best source of information you could get hold of would either be through the Oracle Express Server 6.3.4 documentation available on OTN (the sections on file reading and writing haven't changed much since Personal Express), or get hold of a book, the most useful of which is Oracle Express OLAP by Arkhipentov.

One of the key differences between relational databases and Express data objects is that, instead of tables with columns and rows, you've got variables, that are 'dimensioned' by one or more dimensions. Each variable (for example, AMOUNT_SOLD), stores a value for each combination of dimension entries (STORE, TIME, PRODUCT), and to get data out of the variables, you have to retrieve a value for each of these dimension combinations. There's a lot more to it than this in practice, but that's the basic concept, and it's worth bearing in mind the fundamental differences between how Express stores data and how relational databases store data. Having said that - they're both databases, and with a working knowledge of the two approaches, it's fairly easy to move data between the two systems,

The approach to reading data out of Express and into a CSV file would be along the lines of;

1. Load up Personal Express and 'attach' the database you wished to export from
2. Choose the objects (variables, dimensions etc) in the database you wish to export by using commands such as LISTNAMES and DSC to query the data dictionary
3. Use the OUTFILE command to send output to your CSV file
4. (and this is the tricky bit) Write a procedure to take each variable, loop through all the combination of dimension values attached to it, and use the ROW command to output the values of the variable.

If your Express database is big, or it contains lots of sparse data (where only a small subset of the possible dimension combinations have a variable entry assigned), you might need to convert these blanks to zeros, and/or you'll need to make sure you deal with this efficiently otherwise your output file size could balloon.

Having said that, is it a given that you're moving the data out of Personal Express and into a relational database? It might be worth looking at more recent versions of Oracle Express Server, as these have far better administration tools (Express Administrator, Instance Manager and so on) and there's a whole range of ways you can present data to users, including Express Web Agent (a thin-client HTML interface), Web Publisher, and client-server object-orientated tools such as Oracle Express Objects. In addition, you can directly export your Personal Express database into an Oracle 9i OLAP analytic workspace, which has tools to enable your Express database to be queried via SQL (opening it up to tools such as Discoverer, Oracle Reports, Business Objects and so on), and you can then build user interfaces using products like JDeveloper, Visual Studio and the like.