It's possible to use oracle discoverer with baan. A third party named Empiracle Advantage makes an Interface called Investigator Plus. It creates a gateway for Discoverer to use and renames baan field names into natural language names. Discoverer is an Ad-hoc query tool that looks and acts very much like excel. Users do not need to know sql in order to write thier own query. There are two peices to Discoverer, an administrator and user edition. The admin allows users to see only the data in the database that they need access to. Access is controlled by business areas, and data is arranged according to business use. Since Baan tables have a multitude of data fields that are not necessairily useful to the end user community, discoverer allows the admin to filter those fields away from the data views that the end user can see. Users can save query results into excel. It is not hard to administrate and can be quite useful to data analysis without having to customise Baan software! I have been able to save many hours of time using this tool to create data views and reports for my end-user community. If you want to know more check out www.ea-us.com for Investigator Plus
or www.oracle.com for Discoverer.

I regard Oracle Discoverer as the ultimate drilldown and information tool to use with Baan on Oracle DB.

However, since Baan does not utilize Oracle's repository for references between tables, Oracle Discoverer cannot use this to create joins between tables. Creating joins on matching fieldnames is not an option either, as it would create joins on dsca, and dscb (also refcntu and refcntf) found in nearly all tables.

Mapping 4 letter codes with describing names is a time consuming job to do in Oracle Discoverer, and result is as consistent as the person(s) doing it. And if the table is removed from it's folder, the job is waisted.

In order for the Oracle Discoverer to unleash all its power the following can be done:

Use Oracle views instead of baandb's tables directly.

The views can be given proper names that makes it easier both to select correct table and to make movement of OD solution to another customer running on different company no in Baan. (Because company no is omitted in view name).

Example: "tiitm001-Items", "tiitm002-Item_default_data"



View aliases for Baan 4 letter fields can be automatically created using Baan table datadictionary.

Using MS Access 2000 together with Oracle OLE DB object the following has Been accomplished:

Table field names and their descriptions is washed an treated in such a way that automatic join creation based on fieldnames works in a sensible way. In essence the most important thing to do, is that all "dsca-Description" is changed to something that makes it unique for the table like "dsca-Item Name" or "dsca-Customer Name". (Baan code in alias makes it easier to track data to actual Baan field).

Fieldnames for referenced tables must be the same across referenced tables in order for Discoverer to create a join automatically. Baan DD is not consistent enough, and washing of descriptive names is necessary. Baans DD for references tells you which fields are referenced, all you have to do is to make the names consistent.

The implementation of Baan enum values is acomplished using Oracle PL/SQL's decode() statement. The link between database stored value (numeric value) and literal can be found in Baan DD tables.

Here is a full example of sql for creating a view:

create or replace view "TIITM001-ITEMS" ("Item","Item Name","Item Search Key I","Item Search Key II","Item Type","Item Group","Product Type","Selection Code","Signal Code","Unit Set","Inventory Unit","Storage Unit","Warehouse","Safety Stock","Reorder Point","Order Interval","Order Lead Time","Bom Unit","Scrap Factor","Purchase Price","Supplier","Material Costs","Operation Costs")

as select T$ITEM,T$DSCA,T$SEAK,T$SEAB,
DECODE(T$KITM,1,'Purchased',2,'Manufactured',3,'Generic',4,
'Cost',5,'Service',6,'Subcontracting'),
T$CITG,T$CTYP,T$CSEL,T$CSIG,T$USET,T$CUNI,T$STGU,T$CWAR,T$SFST,T$REOP,T$OINT,T$OLTM,T$UNOM,T$SCPF,T$PRIP,T$SUNO,T$MATC,T$OPRC
from baandb.ttiitm001040



In the MS Access application I call "Baan Viewmaker" the generation of sql is automatic. The user selects which fields to be part of the view. The application either creates names and aliases in english or in home language prefered (and washed) by the user. Aliases can be with or without Baan 4 letter code, and view names can be with or without company no. (Company no can also be included as separate column in view, for Multi company solutions).

In the application, links to other tables can be verified, and this helps you to create views for the necessary tables for your solution. The SQL created, can be easily be reused for another customer.

The application "talks" with Oracle programatically through Oracle OLE objects.

The application is filled with tabledefs from standard update package VRC. Customizations on tables can be entered/modified manually in the application, or imported from Lindhards genious i2Baan/iBuilder product. (Interfacing product).
The Baan Viewmaker is as a product not ready for sale (yet). It is beeing used by Lindhard consultants giving them an advantage for doing Oracle Discoverer solutions towards Baan on Oracle.

Advantages are:

* The selection of tables to be used is straightforward (descriptive viewnames: tdsls041-Sales_Order_Lines)
* No more tedious mapping of fieldnames in Discoverer
* No more renaming of folders
* Joins are automatically created
* Enums are decoded
* Views can be created Read Only
* Solution is not specific to Oracle Discoverer, views can be used by any report generator, or DB tool. (MS Access is strongly recommended as the best and most flexible reportgenerator).

If you are interested in more information (f.ex on how to program with Oracle OLE objects using VBA) then contact:

btr@lindhard.no or trudvang@online.no (Bjorge Trudvang).

If you are interested in getting the Viewmaker, that might also be possible.