Express

Oracle Express was a multi-dimensional database and application development environment for building OLAP applications.

Normal relational databases store data in two-dimensional tables and analytical queries against them can be very slow. Express provides its own specialized database for storing muti-dimensional data. Data in a multi-dimensional database is stored as business people views it, allowing them to slice and dice the data to answer business questions. When designed correctly, an OLAP database will provide much faster response times for analytical queries.

Oracle Express is no longer actively promoted by Oracle and is officially end-of-life (tho still used by satisfied customers across the world). The successor is Oracle OLAP - the Oracle Database OLAP Option.

Overview

At the heart of the Express server are dimensions and variables. Dimensions are the elements that an Express database is broken down by. These are often the "keys" of a database. Examples of dimensions are PRODUCT, REGION or TIME.

Variables are the objects that hold data in an Express database. These are simply arrays of values (usually numeric) that are "dimensioned" by the dimensions in a database. For example, a SALES variable may be dimensioned by PRODUCT, REGION, and TIME. This three-dimensional variable or array is often visualized as a cube of data (though there can of course be more than three variables, which are not as easy to visualise).

Express databases can have multiple variables, with common or a unique set of dimensions. This multi-dimensional view of data is especially useful for OLAP applications.

History

Oracle Corporation purchased Express from IRI in 1995. Oracle later replaced Express with the Oracle OLAP, a Database option available from Oracle 9i. In Oracle 10g, and especially in Oracle 11g, the Oracle Database OLAP Option reached maturity and has become a powerful feature of the Database for Business Intelligence, Data Warehousing and operational systems that can benefit from its performance and multidimensional calculation power.

Express was originally written in AED, an MIT-created language based on ALGOL, and first made commercially available by a company called MDS. One of the original uses was as array-manager for Fortran, one of the main languages of the day. Its popularity grew as an end-user tool for data analysis, and was acquired by IRI as a technology to deliver that firms market research data and analysis. It was re-written in C during the 1980's at which point the portability of the code meant that IRI was able to make Express available of a range of operating systems. IRI then also developed a set of end-user tools and applications that made it easy to deploy Express technology to a wide range of business use-cases.

Today, the OLAP Option to the Oracle Database retains much of the Express heritage of rich multidimensional power, but does so within the context of the Oracle Database, thus inheriting other features of Oracle, and being accessible via simple SQL.

Express product set

Express Engine:

• Express Server - multi-dimensional data store
• Personal Express - single user express server

Express Tools:

• Express Analyzer - reporting and analysis tool
• Express Objects - object-oriented development environment for Express Server
• Express Web Publisher - Develop Briefings for Web Deployment
• Express Spreadsheet Add-in - MS-Excel interface to the Express Server

Express OLAP Applications:

• Sales Analyzer (OSA) - analyse sales, marketing and other corporate data
• Sales Analyzer Client
• Sales Brief Client

• Financial Analyzer (OFA) - financial reporting, analysis, budgeting, and planning
• Financial Analyzer Client

• Financial Controller
• Financial Controller Client
• Data Entry Client

Start and stop

For Windows NT servers, use the Express Service Manager utility or stop and start the NT Service from the NT Service manager.

For Unix systems, do the following from root:

cd $ORACLE_HOME/olap
./express.prm
bin/express.sh start

Create an express database

The Express Administrator provides an easy to use interface to create and maintain express databases. Express administrator will use commands like this to create a database:

-> DATABASE CREATE mydb ATTACH
-> DEFINE department DIMENSION TEXT
-> DEFINE employee   DIMENSION TEXT
-> DEFINE month      DIMENSION TEXT
-> DEFINE salary     VARIABLE  NUMBER <month employee department>
-> UPDATE

Note: Sales Analyser and Financial Analyser needs to create their own databases.

Connecting to Express

The Express Connection Editor (ECE) utility must be used create connection files (.XCF files) before you can establish a connection to an Express Server. You can test your .XCF files with the Express Connection Utility (ECU) (also known as the Remote Connection Utility).

For example, to connect to an Express 6.x server on a NT or Unix platform, you can use the following settings:

• Host Name: name or IP of your machine
• Transport: ora_ro_tcp (Oracle Remote Operations)
• UUID: Leave this field blank
• Authentication Type: Host (Server Login)

Query an express database

The following alternatives are available:

• Use Express Analyser to create a Briefing
• Use Express Web Publisher to create a WebBriefing
• Use Express Objects to develop a nice front-end to your database
• Write a SPL script to query the database.

Look at this scripting example:

-> ALLSTAT
-> LIMIT PRODUCT TO 'TOYOTA'
-> LIMIT GEOGRAPHY TO 'AFRICA'
-> LIMIT TIME TO LAST 3
->
-> REPORT SALES
-> REPORT DOWN GEOGRAPHY SALES
-> REPORT smallest(SALES), largest(SALES), average(SALES)

Backup and recover Express Databases

Do a file system backup of the Oracle Express database files (*.db). Always backup the system databases with user databases as they contain catalog information about them. These system databases are very small and can be found in subdirectories below $OLAP_HOME/oes630/.

Oracle Express databases cannot be backed-up with Oracle's backup and recovery tools, however Express applications (like Financial Analyzer and Sales Analyzer) normally provides their own administration functions.

Oracle Express and read consistency

Each Express user gets his own dedicated workspace that preserves the state of the data at the point in time that they attach to the database. This ensures read repeatability. Users will not see others' updates until they re-attach. In other words, if they run the same analysis more than once they will get the same results. Internally the system will maintain "before" and "after" images as long as someone is still reading them.

Writing SPL scripts

Oracle Express SPL (Stored Procedure Language) scripts can be written and executed by the oescmd command line interpreter. Look at these examples:

# Unix scripting example
. $ORACLE_HOME/olap/express.prm         # Set the environment
$ORACLE_HOME/olap/bin/oescmd            # Start command interpreter

-> database attach express         " Attach a database
-> database list
-> database detach express

-> outfile 'db_structure.lst'      " Describe database structure
-> listnames
-> dbdescribe
-> outfile eof

-> database create myexpress.db attach     " Create a new database
-> database password manager

-> DEFINE HELLO PROGRAM            " Write a program
      PROGRAM
          show 'Look Ma, I can execute express commands...'
      END
-> DESCRIBE HELLO
-> CALL HELLO                      " Execute program

Of course you can do this via an easy-to-use GUI interfaces.

Abbreviating Express commands

Yes, use the first letter of the command with the next two consonants. Look at these examples:

• RPR = REPORT
• DTB = DATABASE
• DSC = DESCRIBE

Some of the commands can be extremely cryptic. For example 'RPR W 10 D 0 SALES' is equivalent to 'REPORT WIDTH 10 DECIMAL 0 SALES'.

Note: Express program names cannot be abbreviated. Eg. DBREPORT, LISTNAMES, etc.

Accessing relational data sources

Relational Data can be IMPORTed into an express database from the Express Administrator. Choose this option if you need to perform OLAP analysis on data from Oracle and other databases. Use menu item File -> Import or write a SPL scrip to import the data. Look at this scripting example:

" Verify the available types of SQL Support...
SHOW SQL.DBMSLIST

" Connect to ORACLE Database...
SQL.DBMS='oracle'
SQLMESSAGES = yes
SQL CONNECT monitor IDENTIFIED BY oramon

" Check for errors
if SQLCODE eq 0
then do
  row w 60 'Connected to database ' w 8 tod today
doend
else do
  row w 60 'ERROR: Failed to connect to database' w 8 tod today
doend

" Prepare cursor
SQL DECLARE c1 CURSOR FOR select tname from tab
" etc...

The Express Relational Access Manager (RAM) is used to access Oracle and other ODBC data sources directly. This effectively turns the Express Server into a ROLAP analysis (Relational OLAP) engine. Configuration is done via the RAA (Relational Access Administrator) GUI utility.

SNAPI and XCA

SNAPI (Structured N-dimensional API) is an application programming interface that allows you to create Microsoft Windows applications that interact with Express. SNAPI is distributed with Personal Express and the Express Server.

XCA (Express Communications Architecture) provides peer-to-peer communications between express databases.

Accessing Express data from C/ VB/ Powerbuilder, etc.

Yes, the SNAPI API is a set of C-language interfaces to Express. See the Express SNAPI guide for example programs.

Note: Data can also be accessed via the Express Spreadsheet Add-in.

Express terminology

Some commonly used express terminology:

• BTM = Oracle express BaTch Manager
• EIF = Express Interchange File Format (output of an Express Export)
• OEA = Oracle Express Analyzer
• OEO = Oracle Express Objects
• OES = Oracle Express Server
• OFA = Oracle Financial Analyzer
• OSA = Oracle Sales Analyzer
• RAA = Oracle express Relational Access Administrator
• RAM = Oracle express Relational Access Manager
• SNAPI = Structured N-dimensional Application Programming Interface
• SPL = The Express Stored Procedure Language
• XCA = Express Communications Architecture

Also see

• Oracle OLAP - Express Server's successor product

There is a very direct relationship between Express Server and the OLAP Option to the Oracle Database.

In Oracle Database, an "Analytic Workspace" is the equivalent of an Express Server "database". It has all (and more) of the multidimensional data types, and calculation power of Express. The core functionality, including the Express Language (SPL) remains available inside Oracle Database (where it is called the OLAP DML and can be leveraged by OLAP developers). However, "Express" has been dramatically improved as it has been embedded into the Oracle Database, and made much more accessible.

From the OLAP Cube perspective : Performance and analytic functionality have been improved significantly, and the SQL Access feature makes it possible to embed advanced multidimensional calculation power and performance into 'any' tool or application that can connect to and query an Oracle Database. It also means that multidimensional data & calculations can be combined with any other data managed by, or accessible through, the Oracle Database via a simple SQL Join. This has dramatic usability and performance implications for contemporary Business Intelligence and Data Warehousing systems, and makes it possible for operational systems based on Oracle Database to seamlessly leverage the multidimensional calculation engine, including its sophisticated support for time series calculations, aggregations, allocations and forecasts.

Other advantages arising from embedding "Express Server" into the kernal of Oracle Database are as a result of being a part of the Database. For example, as a feature of Oracle Database, Oracle OLAP cubes share Database features for security, performance, high availability, manageability and avoids the drawbacks of older generations of OLAP products like Express Server which were standalone and required entirely separate management. Furthermore, multidimensional cubes inside the Oracle Database can be maintained via PL/SQL, refreshed in the same way as (relational) Materialised Views, and participate in the Query Re-write feature of the Database.

External links

• OLAP in practique - Oracle Express