Thread: ERPJewels
View Single Post
Old 8th August 2003, 18:02
Hitesh Shah's Avatar
Hitesh Shah Hitesh Shah is offline
Join Date: Nov 2001
Location: Mumbai,India
Posts: 1,853
Hitesh Shah is on a distinguished road
Baan: triton,Baan IVc4 , ERP Ln - DB: Oracle/Bisam/SQL 2000/SQL 2005 - OS: Sun Solaris/Windows 2003

We are a Baan Customer and we have developed in-house a tool ERPJewels written in Baan 4 GL and Visual Basic . This is very generic tool and any Baan customer on Baan IV (any service pack) and using Microsoft Office 1997/2000/2002 can benefit from the tool. The tool can help user automate and gear up Baan customer's MIS.

The Baan part is written in package tu and module dll. All tables and domains have created in tu package only . Further the tool is delivered as an export of package tu only based on session for the sessions created by us. So there is no danger of doing anything wrong with existing installation of customer.

We wish that all - other Baan customers , consultants and we ourselves should benefit from this tool . So we are making this tool generally available to all.

We are following Open source policy . So any one who buys this tool will be entitled to get the sources of the tool also. The tool implemetation takes about 7-15 days . It requires the knowledge of Baan packages (functional) , Baan tools , customizations at customer place and customer requirements.

We have lot of development / enhancement initiatives ourselves on which we wish to concentrate . Further we have developed in-house small support system which is simple and yet very effective in tracking issue with respect to the tool. We have good VRC structure and backup system.

This post is going to be long sheerly due to the size of the tool being described.

Outstanding Differentiators with other tools
Naturally one would wonder what’s so outstanding in the tool that’s not available elsewhere. Here are the key differentiators.

1. Baan Report as a data source - Baan reports is itself an output i.e. the ETL result arrived at after Extract (all select queries), Transform (calculate, sort and summarize) and load (ttstpdisplay, bwprint and print files for printer ) . Especially the information in after field & after report section of the report (total..0, min..1 etc) is really great. The only problem is with Load operation of Baan report (like no graphs , single dimension, exception reporting , short life wrt storage , too much detail , aggregate output field level manipulation, difficult report editor etc). So if one treats Baan report as data source without disturbing Baan reporting architecture and also removes these problems of Load, Baan report itself can be great Intelligence ETL tool. And ERPJewels does that only.

So the big picture with ERPJewels is like

Baan Job Management --> Baan program ---> Baan Report (ETL) --->
ERPJewels (ETL + Job synchronization + Data Marts) ---> Excellent analysis results

Ali El Kortobi, Oracle's Senior Director of Product Development for Data Warehousing in support of Oracle Warehouse Builder against so-called BoB (Best-of-Breed) BI applications.

"ETL is key to the data warehousing. Buying Best-of-breed means paying for a whole other transformation engine you already own - Oracle 9i Database" (Source - Jul/Aug 2003).

The same analogy we would extend for Baan users. Baan reporting is a great transformation engine with job scheduling capability which any Baan customers already owns legitimately. And extending the ETL capabilities of Baan report and job scheduling capabilities of Baan Job management with ERPJewels, customers will definitely maximize ROI on Baan. For more information, please see

2. Investment protection - So when Baan report is treated as data source, user is protecting his/her ETL investments in Baan reports (may be standard / customized) (lot of time, money & effort saved). No new skills need to be learnt. All Baan report output variables and input variables (if it makes sense In the analyses) can be treated as columns of data source (Baan report) and the information be presented in MS Excel sheet with graphs/charts.

3. Easy Analysis Creator for Baan report – Baan report is a different kind of data source with different Baan technology. In order to make simple for users (so that they don’t have to remember the codes) descriptions and zoom sessions have been given at almost all stages so that user can take a perfect decision at the time he/she is creating the analysis. This is significant considering the fact that there are no label associations with any report output fields in Baan.
For more info please see,

4. In-built Data Marts & ETL infrastructure- There is no need for separate periodic refresh /routines of ERP transaction data in separate data warehouse. If the refresh of data from server is not done intelligently (which is mostly the case in case of separate data warehouse / BoB), there is one extra Extract overhead associated in intelligence reporting. Lot of time and effort wasted. With ERPJewels data is directly populated in data marts which are resident in Baan itself. Lot of time and effort is saved by eliminating separate data warehouse. ERP database itself constitute data warehouse and ERPJewels analyses constitute separate Data marts. Data marts (called Analyses in ERPJewels) can be created by creating analysis on data source. Analysis results are saved in Baan system and along with selection criteria values which helps user relate the analyses results later on with the selection criteria.

For more information please see

5. Job Synchronization – Till now Baan job management has been used for batch process or day end rebuild process in most cases. With ERPJewels & Baan Job Management, Baan report can be scheduled in a job which makes it possible to get periodic analyses automatically. This is very important considering the fact ETL applications highly CPU intensive. Jobs which run in night or when the server resources are available can optimize the CPU usage (except in case of 24X7 multi location servers) by running the periodic repeating analyses at night. ERPJewels has very tight seamless integration with Baan Job Management. This facilitates sophisticated PUSH intelligence as against traditional PULL intelligence. For more information please see

5. Flexibility like custom grouping based on another table, Ad hoc classification , date classification , date / number categorization which requires little modification to the data source and facilitates quick ad hoc analyses.

6. Query generator on Baan tables to suit the backend (i.e. Baan) like compnr specification, dynamic zoom based on field domain, all Baan data type matching, in operator, like operator, use of Baan indices, formulae construction among fields of the table.

7. All this ensures tight integration with backend which is the prime requirement of any data warehousing project.

8. Easy forward and backward integration - It's quite possible that user may want to update the ERP data based on analysis results (like update budgets estimates/forecasts). Though ERPJewels has it’s own client applications for Load operation, user may decide to have another client application which can fetch the data from the data marts in Baan either through ODBC / JDBC connection and present the same to users in the way he/she desires. Data marts contain very small set of data which are summary in nature as compared to bulky ERP transaction data and such data marts can be accessed easily for further load action differently. User has to write a Baan session to start the load client and specify the same in after analysis action.
For more information

9. ERPJewels can also represent analysis results in Office Diagrams (only in Office 2002) besides normal routine graphs for each & every column / row. This is not available with any other Excel applications provider.

10. Developers of ERPJewels with excellent functional background have also created some sample ready analyses (data marts) which are ready to use in order to demonstrate the benefits of the tools immediately. This can give users immediate insight into the results which users can gain out of ERPJewels and help users relate the relevance of ERPJewels in his/her setup and also create new similar analyses.

11. Easy Change Management - One important yardstick is how flexible the query builder or data extractor is. ERPJewels being tightly integrated the backend, adapts very fast to the data source changes with little maintenance.

Sundry Features
1. Three data sources – The tool can recognize ASCII files, Baan Table and Baan report can be recognized as data sources with ERPJewels.
2. Three Excel output range – The tool can give analyses results in 3 different Excel formats viz. Excel Summary, Drill down and Save as XLS.
3. Exception Norms – The tool will give the Exception results in different colors.
4. Calculated Fields & items – Various range of calculated fields like Date conversion, Custom grouping, Data classification, Formulae construction are available with the tool which can help format the data source in the Extract process without requiring data source to be modified.
5. Wide range of MS Excel graphs as output – All excel graphs except Stock and bubble can be had by user for his/her analyses results.
6. Web compatibility – Optionally output can also be viewed in HTML format.
7. Automatic Email creation – An email of analyses results can be created automatically with ERPJewels for sending to designated recipients for each analysis separately. Further the text of the message can also be specified differently for each analysis.
8. Selection values in Excel also – Besides storing the selection criteria in Baan, the selection values are also available in Html file and XLS file which helps relate the results later on with the particular selection.
9. Document properties are updated in XLS file for better house keeping in network.
10. Online help in Baan – The tool is well documented and online help is available in Baan.
11. Natural Drill down - Baan reporting has some hierarchical structure in-build in Baan report. This tool uses this hierarchy knowledge by eliminating the need to extract and transform in the ETL process. Direct load in ETL process is possible. Please see for more info.

Benefits of ERPJewels to Customers
1. It maximizes ROI on Baan and reduces TCO for Baan by protecting existing investment in Baan, restricting new investment and giving more benefits in the same setup.
2. It removes the limitations of Baan reporting by treating the same as data source.
3. Brings automation in periodic MIS generations.
4. Bring flexibility in analyses generations.
5. It brings speed to the process of MIS generations.

Latest Trends in MIS & BI
This tool is in conformity with the latest trends with respect to ERP integration strategy and increasing use of MS Excel in MIS & BI. This will be evident reading this hyperlink .

Differences with other products available on MS Excel
ERPJewels is quite different from other off the shelf products available in the market in following way.
1. ETL tool – ERPJewels is an ETL tool working on 3 layers. Extract and Transform in ETL layer works in Baan 4GL. This is much superior, and faster data processing. MS Excel works only as Load engine in the ETL layer. And this is very superior in data presentation. Other tools merely extract data from Baan, and do not add any value to data processing.
2. MS Excel Graphs and Charts – Analyses results from Baan can be plotted against most MS Excel graphs and charts easily. Other tools available in the market do not do this.
3. Analyses results manipulation – ERPJewels is capable of manipulating the analyses results (like % of 1 column over another, differences between 2 columns etc) very easily. Mere data extractor MS Excel products do not have these capabilities.
4. Office Diagrams – Office diagrams (like Venn, Pyramid, Cycle etc.) are the latest contributions of Microsoft in MS Office 10. Interestingly ERPJewels is using this Office diagrams in addition to Excel graph/charts.
5. Only Summary handling – With ERPJewels, MS Excel does not have to deal with voluminous large ERP data. It works only at load with small summary data which is faster and effective.
6. Different Technology (No separate Output device required in Baan) – Other tools require users to create an output device in Baan and works as file converter. Whereas ERPJewels does not require such output device. It is intricately woven Baan report architecture. Original report remains same as it is and the tool works in the backend by extracting figures and values from Baan report without disturbing original Baan report.

Related Links

Problem of Analysis -
Functions & Features -
Complete Documentation -
Slide presentation -

ERPJewels Technicals
Number of Baan Session/Programs - 65
Number of Visual Basic Programs - 4

I am attaching a 2MB dump which contains following matters

1. EJ Justification.doc - Justification for ERPJewels containing mostly text of this post.
2. - Containing export dump of tu package only based on sessions only for the sessions written for this tool only.
3. - 2 3GL programs not exported by 'Export data dictionary' (because it is based on sessions only) - Demo data table dump of tool tables plus XLS file illustrating each analysis. & - MS office client application zip containing setup file for installation - containing the complete installation steps for server as well as client part.

In case anybody has any question / problem installing the same , please shoot me an email.
Attached Files
File Type: zip (144.9 KB, 439 views)
Reply With Quote