Manoj,
if you are pushing a client down this road you may be doing them a disservice. A custom ODBC driver ala ODA is not that simple a task. Witness JDE's attempts to try to get ODA right. Its also probably not needed (see below).
Is network latency an issue here (e.g. are the thin clients local to the database or remote)? If remote you've pretty much eliminated doing ad-hoc query and reporting from anything other than a terminal or web server.
Regarding the data issues you brought up (data security, decimal scaling, descriptive field names, and julian dates) these can all be dealt with by Oracle (or SQL Server) so that direct data access (ODBC) is both secure and user friendly. These issues have been discussed here before. Below is a partial copy of a response I have made to several people regarding these issues.
==============================================================
. . . we implemented Crystal Reports 8 as both an I.T. and an End User reporting/query tool. Our Enterprise Server is Unix/Oracle. The summary is that we are quite happy with Crystal Reports compared to the alternatives. One alternative we tried was ODA. After a lot of work with it, we found its limitations on table joins and performance to be unacceptable. Cognos was another alternative we discarded because of the cost factor (no free runtime unlike Crystal).
We (IT) did need to undertake some tasks in order for Crystal to meet our requirements for ease-of-use and data security. The specific concerns you need to deal with if you don't use ODA are:
1. Table and column naming. I.T. people after much use may become comfortable with the brief acronyms such as PRODDTA.F4101.IMITM when referring to the Short Item Number. We don't expect our end-users to have to deal with that though.
2. Date Fields. JDE uses a Julian Date format which stores dates as a number - not as a date. It's not obvious that 100129 represents April 8, 2000.
3. Decimal Places. All numeric fields are written without decimal places, so if you query the database a Line Item number will be returned as 1000 instead of 1.000.
4. Data Security. As delivered no database security is defined / setup. If you allow users direct access to the database instead of through JDE not only may they be able to see what should be restricted information - but some desktop tools would allow updates/deletes to take place (Access for one).
To deal with these issues we wrote PL/SQL scripts that:
1. Created views and synonyms for specific tables so that F4101 appears as "Item Master F4101" and IMITM appears as "Short_Item_Number_IMITM". The script pulls this information from the Object Librarian and Data Dictionary tables so the only thing we needed to do was to edit some of the names.
2. We dealt with dates in the same script that generates the views. It looks at the Data Dictionary Data Type and if the column is a Date data type then a custom Oracle Function is used to convert the JDE number into an Oracle Date. From Crystal (or Access or MSQuery ...) it looks like a normal date field now.
3. Numeric fields with decimal places were dealt with similiarly. If decimals exist then the database value was divided by 10 to the 'x' power.
4. To deal with the security issues a script was written to blow through the entire database and for all JDE tables remove public access and grant access only to the JDE Role. By default only the JDE account(s) now have access to any of the JDE tables. We also changed passwords on the default Oracle JDE accounts created when you install OW. The same SQL script generator used in 1 - 3 above also grants read (Select) privileges to different classes or roles of users. Tables that have not had a view defined are inaccessible to a user.
We're pretty happy with the approach and our user's are also. Performance is great - faster than RDA reports in almost all cases. And after having to route all report requests through I.T. users are ecstatic about being able to write their own when they need it. We do however require training in Crystal Reports before we will install the software on their desktop and turn them loose. Still though the I.T. role has changed from being the bottleneck to being the technical advisor - which is how it should be.
==================================================================
I'm not trying to toot my own horn here but just want to point out that your client already possesses the tools needed to safely and easily deliver OneWorld data to "Local" clients using MSQuery, Access, Crystal, etc.
Regards,
Larry Jones
[email protected]
OneWorld XE, SP 15.1
HPUX 11, Oracle SE 8.1.6
Mfg, Distribution, Financials
