Express
From Oracle FAQ
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 are normally 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 must faster response times for analytical queries.
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.
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.
Express was originally written in AED, an MIT-created language based on ALGOL. One of the original uses was as array-manager for Fortran, one of the main languages of the day.
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
- DTD = 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
- EIS = 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
