|
|
|
|
A specific language is available in Oracle Express, allowing you to handle the whole database. With this 4GL language, it's possible to :
Products such as Express Administrator or Express Analyzer use 4GL to interact with the database. It's possible to write batch programs to be launched every night, etc.
I provide you here with some commented examples, based on the Best Foot Forward database. Please notice that these examples are very simple. My goal was to show some specific points of the language. Nevertheless, they are compiled and executed without problems in Oracle Express.
1. The Best Foot Forward creation
This first example creates a simple version of the Best Foot Forward database, with some dimensions, variables and formulas. This version does not include hierarchies in the Time dimension
" Create database
DATABASE CREATE 'BESTFOOTFORWARD.DB'
" Create dimensions
" We specify the name and the type of data
DEFINE CATEGORY DIMENSION ID
DEFINE COLOR DIMENSION ID
DEFINE OUTLET DIMENSION TEXT WIDTH 32
DEFINE SIZE DIMENSION ID
DEFINE REFERENCE DIMENSION INTEGER
DEFINE TIME DIMENSION ID
" Create variables
" We specify the name, the type of data and the associated dimensions
" We specify the dense and sparse dimensions
DEFINE QUANTITY VARIABLE DECIMAL <TIME SPARSE <OUTLET REFERENCE>>
DEFINE TOTALVTE VARIABLE DECIMAL <TIME SPARSE <OUTLET REFERENCE>>
DEFINE VAT VARIABLE DECIMAL <TIME>
" Create formulas
" We specify the name, the type of data and the associated dimensions
" We specify the text of the formula
DEFINE TOTALVTI FORMULA DECIMAL <TIME OUTLET REFERENCE>
EQ TotalVTE*VAT
2. Loading a flat file
This program will load data from the "exemple.txt" file described in the stage 8 of "OLAP by exemple". This script creates a new object, a program. It can be launched with "call <program_name>".
DEFINE LOAD_EXAMPLE PROGRAM
PROGRAM
variable _funit integer
variable _i integer
" Open the datafile
_funit = fileopen('exemple.txt' read)
" The first line is a description, skipping
fileread _funit stopafter 1
if not filenext(_funit)
" Nothing to do if there is only one line
then goto DONE
" For every line in the file
_i=0
while true
do
" Loadinf data in dimensions and variables
" For the files of ruled type, we have to specify the size and position of each column
fileview _funit ruled -
_i = _i + 1 -
COL 1 WIDTH 4 STRIP APPEND REFERENCE -
COL 9 WIDTH 8 STRIP APPEND TIME -
COL 25 WIDTH 10 STRIP APPEND OUTLET -
COL 41 WIDTH 5 STRIP QUANTITY -
COL 49 WIDTH 6 STRIP TOTALVTE
" Here we can add some specific code for each line of the file
" Next record
if not filenext(_funit)
then goto DONE
doend
DONE:
" Close the file
fileclose _funit
update
" Print the time of day and the number of records
show joinchars (tod ' - ' _i ' records created')
END
3. Loading data from a relationnal database
This example shows how to load all the references from the "model" relationnal table. Access to relationnal database can be dynamic, allowing multidimensionnal applications to access relationnal databases.
DEFINE LOAD_MODEL PROGRAM
PROGRAM
variable _i integer
" Connection to Oracle 8i
sql.dbms='oracle'
sqlmessages=yes
sql connect 'admin' identified by 'pass'
if sqlcode ne 0
then signal err_connect joinchars('Connection problem ' sqlerrm)
" Declare a cursor
sql declare C1 cursor for -
SELECT id_model, desc_model FROM model
if SQLCODE ne 0
then signal err_req1 joinchars('SQL error ' sqlerrm)
sql open C1
if SQLCODE ne 0
then signal err_req2 joinchars('SQL error ' sqlerrm)
" For each row in the MODEL table
_i = 0
while SQLCODE eq 0
do
_i = _i + 1
sql FETCH C1 INTO -
:APPEND modele, -
:mod.longlabel
if sqlcode ne 0 and sqlcode ne 100
then signal err_req3 joinchars('SQL error ' sqlerrm)
doend
update
" Close cursor and database
sql close C1
sql disconnect
END
4. Aggregating data
This last example shows how to rollup data along a hierarchy.
DEFINE ROLL PROGRAM
PROGRAM
arg vDay text " Parameter : Days to be aggregate
" Before doing a rollup, we have to select all the months in the year
" and all the days in the month, in the Time dimension.
" This is done with the 'limit' command on the time dimension and its relations
limit time to vJours " Select days in parameter
limit aute.hierdim to 'DMY' " Select the Day-Month-Year hierarchy
limit time to ancestors using aute.parent " Months and years of the days
limit time to children using aute.parent " Days of months and months of years
" For the other dimensions, we keep all positions
limit outlet to all
limit reference to all
show joinchars(tod, ' - Rollup along time')
" Aggregate
rollup quantity over time using aute.parent
rollup totalvte over time using aute.parent
END
That's all for the moment. Thank you for supporting my poor English (fortunately
corrected by Leonard !). Well done all of you who have
stayed with us - I am counting on you for constructive criticism. You can choose between my email
and my beautiful form.
|
|
|
|