Oracle OLAP and Express FAQ

$Date: 03-Oct-1999 $
$Revision: 1.0 $
$Author: Frank Naudé $

Topics

Note: Oracle likes using the term "Business Inteligence" instead of OLAP.

• Express Server - multi-dimentional 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-Excell interface to the Express Server

OLAP Applications:

• Sales Analyzer (OSA) - analyze 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
  

Other products:

• Oracle Data Mart Suite for NT - the Oracle Data Warehouse Builder included from V3

• Oracle Discoverer - End-user ROLAP query tool
  

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.

For Unix systems, do the following from root:

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

     -> 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.

• Use Express Analyser to create a Briefing
• Use Express Web Puiblisher 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)

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.

# 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 easy-to-use GUI interfaces.

RPR	REPORT
DTD	DATABASE
DSC	DESCRIBE
etc.

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 porgram names cannot be abbreviated. Eg. DBREPORT, LISTNAMES, etc.

        " 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
        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.

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

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

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	Orace Financial Analyzer
OSA	Oracle Sales Analyzer
RAA	Oracle express Relational Access Administrator
RAM	Oracle express Relational Access Manager
SNAPI	Structured N-dimentional Application Programing Interface
SPL	The Express Stored Procedure Language
XCA	Express Communications Architecture

• Oracle's Express Home Page

• Oracle Express Web Demo

• Oracle OLAP Product News

• Express SIG - Oracle Express Special Interest Group

• OLAP Council - download info about the APB-1 benchmark