The term OLAP (On-Line Analytical Processing) was first used by E.F. Codd in
1993. It refers to a type of application that allows a user to interactively
analyze data. An OLAP system is often contrasted with an OLTP (On-Line
Transaction Processing) system that focuses on processing orders, invoices
or general ledger transactions. OLAP applications were previously called
Decision Support Systems.
Note: Oracle likes using the term "Business Inteligence" instead of OLAP.
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.
Express is a multi-dimensional database and application development environment
for building OLAP applications.
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.
The Express Adminsistrator provides an easy to use interface to create and maintain
express databases. Express administrator will use commands liks 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.
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 Opeations) UUID: Leave this field blank Authentication Type: Host (Server Login)
Do a filesystem backup of the Oracle Express database files (*.db).
Alwayse 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.
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.
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 (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.
