BAAN Data Model Exposed

cmassdba · #1 26th February 2005, 00:37

Hello, I have not used Baan but am working on a project with a client to scope out a process of extracting product information from a Baan database and putting this info into a product marketing database. We are at a high level right now and client reps cannot tell me whether Baan database is obfuscated like SAP (table names and columns names are meaningless) or if the product information residing in Baan is easy to identify in table and column names.

Can I replicate data out of Baan and into another Oracle database (the client can tell me that their Baan DB is Oracle) or does Bann restrict this type of activity. If restricted, would I have to by a data pump tool or some other interface tool to get at the product data in the Baan DB?

Thanks IN Advance

PS, Any good sites that discuss the Baan DataModel?

patvdv · #2 26th February 2005, 15:50

First of all, it's Baan and not Bann. Secondly, the whole data model is pretty much company confidential information so unless you have an agreement with SSA then your chances to get this information would be pretty slim I'd say.

Markus Schmitz · #3 26th February 2005, 16:47

No double postings in several forums please!

vahdani · #4 28th February 2005, 12:46

Hi,

Baan Data Model is not obfuscated. But some parts of it can be pretty complicated as one would expect in any ERP software of this calss. One very important point about Baan Data model is the consitent use of four letter words

for table column names: example "orno" stands for "Order Number "cuqs" stands for "Code for Unit of Quantity in Sales line", you get the drift! Baan professionals usually know most of the table field names that interrest them. Table names are also standardised for example "tdsls040" is "sales order header" table. "td" stands for "Distribution" Package, "sls" for sales module (which is part of the Distribution Package) and 040 is a running number. The easiest way to find out the table names is to use the field level help of a Baan program. Any Baan user should be able to show you how to call help. The table name and field name seperated by a point (".") should appear on the forst row of the help. One pretty usefull tool is Baan "General Table Display" program ttaad4500 which can normally be started from the menu bar option "Start program". You can use "Genaral Table Disply" (GTD) to select tables from a table list with descriptions and then see all the fields in the table. You can switch to and from the standerd "four letter" column name to a more meaningfull name by entering a star character (*).

In Oracle is each table name supplemented with the Baan company number. For example the table for Sales order header in company 100 is called "tdsls040100". The column names are the same as in GTD but prefixed with a "t$". For example the order number in the above table would be "t$orno".

Armed with these basic infos, you should be ready to go with your work. Therfore.........

cmassdba · #5 28th February 2005, 17:38

Thank you for the responses.

I realized after reading my own post that I had spelled Baan incorrectly. I apologize. My Bad.

Being new here, I only noticed the Database category after posting in the General Discussion group and posted there thinking I'd get to the right group there. Sorry.

I really appreciate the detailed information given on the data model nomenclature. It gives me more to go on. I am trying to estimate the time involved in developing an interface to Baan product info for a client and not having work on Baan this is very helpful.

I need to replicate out of Baan the product specific info (Global Unique Id, description, size, weight etc) and populate an Oracle DB that will backend a Web site for consumers and other major resellers of these products.

Can I use Oracle's replication features with Baan or is that locked down?

Is there a built in data extraction tool?

It would be nice if I could ask the client all these questions but they do not have the IT staff or knowledge of this product to answer them.

I appreciate your time and guidance.
Regards

lbencic · #6 28th February 2005, 18:09

The data you listed can be found in Baan V in the table:

tcibd001xxx - Item Base Data, where xxx is your comany number in Baan

tcibd001.item - Unique Identifier
tcibd001.dsca - Item Description
tcibd001.dscc - Size
tcibd001.wght - Weight
tcibd001.cwun - Weight Unit Code

Check as Vahdani suggests in GTD if you need additional fields, mostly they are on that table, but there are some related tables with item information in them, post up if you cannot find what you need.

As for an extraction tool - probably Exchange is the closes without a 3rd party tool (Nazdaq's QPTool for Windows for example is nice), or maybe Baan's OpenWorld, which costs extra. Exchange will produce a fixed or delimited ASCII file. Here's a good link with Exchange Info for Baan V:

Exchange Post

I'm not big on the Oracle / DB side, maybe there is other methods there.

vahdani · #7 28th February 2005, 18:11

For some reason I dont think that replicating whole tables is the right way for you. You can read/extract Oracle Data using tools that use ODBC, JDBC,etc. BUT NEVER EVER WRTE BACK DATA INTO BAAN TABLES. This brings (at least) seven years of bad luck!!!

You might want to look at Crystal Reports as well. CR comes with a Baan Native Driver which can read from Baan regardless of the actual DB backend and allows generation of reports (Sorted/grouped lists and Charts) which can then be published on web using Crystal Enterprise Software.

foxguard · #8 1st March 2005, 11:21

Actually, if you have access to Baan Tools, there are some reports to print table definitions. The challenge would be getting the relationships with between the tables. In Baan ERP V, if you know DEM and have access to it, I think you can build the data model there. There is a utility in that particular package. This is not taught in DEM training but I experimented with it back in 2003.

dave_23 · #9 1st March 2005, 14:49

Quote:

Originally Posted by cmassdba

I need to replicate out of Baan the product specific info (Global Unique Id, description, size, weight etc) and populate an Oracle DB that will backend a Web site for consumers and other major resellers of these products.

Really, SSA has that tool already built. its one of their EE/CRM products (I'm a little fuzzy on which ones do which).

and if you're set on building your own - Baan's OpenWorld (SSA TA) would provide you with adapters for ODBC, JDBC, Java, XML, etc.

If you're worried about cost - compare the OpenWorld or EE/CRM products price to license and support fees for the additional Oracle DB server!! It's got to be cheaper!

Dave

squaresh · #10 4th March 2005, 23:53

Baan sells another great solution, which is BaanBIS (CPM is the new name).
It is initialized with Baan Data Model, so that you just check the field you need, then BaanBIS generates export scheme program or a whole session for export.
It is based on windows, sql server 2000.
There is also predefined models for specific domain: sales, purchase, finance, warehousing, project...
CPM will include Cognos predefined reports.

ronikb · #11 19th June 2007, 12:43

Do you have any info on templates for finanace in baan for cognos.Please share