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 whats so outstanding in the tool thats 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 - otn.oracle.com/oraclemagazine 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 http://www.erpjewels.com/Analysis.htm
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 dont 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, http://www.erpjewels.com/Easyanalysis.htm
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 http://www.erpjewels.com/Datawarehouse.htm
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 http://www.erpjewels.com/Jobmngmt.htm
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 its 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 http://www.erpjewels.com/BackIntegration.htm
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.
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 http://www.erpjewels.com/Drilldown.htm 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 http://www.erpjewels.com/LatestTrend.htm .
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.
Problem of Analysis - http://www.erpjewels.com/Probanalysis.htm
Functions & Features - http://www.erpjewels.com/Funt&Feat.htm
Complete Documentation - http://www.erpjewels.com/docs.zip
Slide presentation - http://www.erpjewels.com/erpjewels.pps
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. dict.zip - Containing export dump of tu package only based on sessions only for the sessions written for this tool only.
3. 3globj.zip - 2 3GL programs not exported by 'Export data dictionary' (because it is based on sessions only)
4.demotables.zip - Demo data table dump of tool tables plus XLS file illustrating each analysis.
5.2000.zip & 2002.zip - MS office client application zip containing setup file for installation
6.ejinstall.zip - 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.
We are pleased to say that we have made following enhancements to our ERPJewels tool.
1.We have added 2 more output types viz Pivot Table and Olap Interface. Pivot table is an excellent multi-dimensional output. Within the known limits , pivot table is an excellent load client a user can have. When the limits of Pivot table are exceeded or user wishes to have a different third party load client, then olap interface is the output to select. After the DTS work, control will pass on to a custom program which loads the third party client with the required information.
2.Earlier the ERPJewels DTS framework was limited by the limits of the load client. Now the changes have been made to ensure there are not limits for DTS work. When limits of load client are reached, DTS work is done completely and only load clients are not loaded with a message to that effect. This enables user to have a separate load client of his/her own with multi-dimensional data coming from ERPJewels.
3. Consolidation of analyses results from heterogeneous data sources and from different period analysis results the same analyses is possible now.
4. Data warehouse structure has been made more efficient and scaleable. The page in the hyperlink has an example of how users can estimate the size of the data warehouse. As per the example annual growth in the data warehouse structure is hardly 1GB. One can substitute one's own realistic figures to know the approximate size of the annual growth in ERPJewels data warehouse structure.
5.Creation and usage of hierarchies including period hierarchies based on date fields.
6. DTS framework has been more integrated with Baan infrastructure. Baan report complexities have been tackled easily..
7.Easy Analysis creators for ASCII and Baan report data sources have been created.
One may wish to know how this tool works differently from the many Baan2XL utilities and code posted in this forum. He/she can check this link.
Those who wish to see the benefits of ERPJewels in nutshell; he/she can see summary benefits of ERPJewels.
The tool is written in package tu module dll and export is based on sessions and of package tu only. So this does not hamper any standard , customized or localized functionality. For demo and documentation of the tool along with sample analyses data, please check our site
For any query / problem in installing and using the tool , u can send an email or send me a PM .
I was browsing from Baanboard.com .there i found your
we are using baanivc4 on nt environment with sql
we want some baan standard and customised report to be
downloaded in the excel format.
what are all the advantages and what tools is require
to install and how much time will take and how about
the training .
we are using baan
please give us some real example of the products.
what will be approximate price for the products.
With ERPJewels we can do many things . Here are some of the things it
can do .
1. Baan report has a good extract and transform capabilities. The tool
can do further extract from any report (one or more output fields) and
transform the same to 1 / 2 / multi-dimensional Excel format.It takes
care of Baan print conditions and non-printed sort fields in detail and
after field layout (considering the sort field order). In Baan report,
report field + aggregate function + aggregate condtion makes 1 data source
fields . This tool can easily understand these " .. " fields and use it in
2. There is easy generator for analysis on Baan report in order
to make it very easier for user to generate analysis on reports in jiffy.
In Baan , report fields have no direct association with descriptions. This tool
gives association to descriptions for report fields to a greater extent. All
changes to Baan reports are visible within this tool itself.
3.It uses Baan's native dynamic SQL to get the data from any Baan table. User
can specify selection criteria on Baan.
4. It uses excel's extensive charting capabilties.
5. User can have their own calculated items (data polishing at granular level like
date conversion,date aging,alphanumeric / numeric data categorization ) and calculated
fields (data manipulations at aggregation level like weighted avg / %). This provides
very good ad hoc analytical capabilities without much changing the infrastructure.
6. It is syncronized with Baan job management in such a way as to
understand the different number of days in a periods (like month,quarter).
Further it can understand numeric time indicators such as fiscal periods
in finance and weeks in manufacturing. These can help system push results
rather than user pulling the results (push intelligence). This can be done in night
when nobody is using Baan.
7.After data transformation, there is a possibility to save the analyses
results in multi-dimensional values. This can be helpful if u wish to do certain
processing back in ERP based on these results (backward integration) or if
you wish to use the same for loading in any scalable cube browser (forward integration).
There is a provision to run a user defined Baan 4 GL / 3GL program after the
data transformation is done.
8. It's native to Baan. Meaning it can take the advantage ERP structures like
domains specific zooms, domain range validation , table field descriptions,
table indices in query generation, use of IN & LIKE operators in SQL queries ,
enum descriptions , exchange , job management, multi-company selection using compnr etc
with extreme ease. Outside Baan it's very difficult to do.
9. User can generate and use hierachical structures within Baan upto 6 levels.
In summary, the tool provides good DTS capabilities for data extract and transformation
out of Baan. It has the excellent load client Excel pivot table. I would term Excel pivot
table as the best cube browser albeit with limited capabilities and limited security
and authorization. Further data transformation being very CPU intensive, there may be
a good case in putting data transformation to another server especially incase of 24X7 Baan
Regarding the benefits / real example /training / commercials etc we'll contact you .
Thanks for your reply. I would like to know demo details of BaaNIVC4.
if you can arrange to send some reports from baan and excel to analyse further how this will appear.
what is the cost for the software. please let me know.
We have mailed you sample outputs.
We shall be organizing a demo for you
We shall continue our communications in this context offline by email.
There have been lot of good news for those of you who wish to use
the analytical and presntation cabailities of MS Excel along with
Baan . Here are those.
1. MS Excel integration with an ERP is the latest strategy being followed world
wide . See SAP's Excel strategy.
2. Office 11 ie Office 2003 has 4 times larger pivoting capabilities than it's
earlier version . Pivot table can now contain maximum of 32000 disting rows as against
maximum of 8000 distinct rows in earlier . This means that MS excel can handle 8 million
cellsets of your summary data (NOT transaction data ) as against earlier 2 million cellsets.
3. User defined fields ie UDF's which can allow user to get any data in Baan using Baan
3GL coding were missing till now in ERPJewels . We have enhanced the tool so that users
can have any kind of calculation with this udf functionality .
4. Those using ODBC data access may find native data dictionary access
very useful in ad hoc analysis.
5. Even today in the latest versions users can not get job capable (capable of being scheduled
recognizing uneven periods and numeric non-date period indicators) multi-dimensional output
with all ad hoc calculated fields and items at single click . User needs to
play a lot in Baan and Excel to get such an output .
6. Even the most OLAP data warehousing vendors talks of it's compatibilty MS Excel as
the selling point (due to excel's superior presentation ,charting , printing and
emailing capabilities). So now MS Excel is now the mandatory strategy and not just
an alternative strategy. This article does an elaborate in-depth analysis of importance of Data
warehousing , Excel and it's integration. And this is what ERPJewels exactly
is doing. It makes the loop complete. It establishes direct connection
with Data warehousing tools for incremental export and Excel for Ad hoc analysis.
Those who wish to tap this strategy with the help of this tool , can send an
email or PM to us . We can help them further with our tool.
Can this be ported to Baan 5. If so, what would the major bottlenecks be?
I am not an expert on Baan V or ERP Ln . However from the knowledge available , I can say it will work due to following reasons .
1. Most tools functions have remained the same .
2. Reporting structure too is same.
All u may need is just recomplie the same in Baan V compiler .
We have kept the Baan dictionary export , client binaries , demo tables etc in our download . This binaries and dictionaries will work till 12 May 2007 . Just import this and c if it can be imported in Baan V and the demo data also . In case u need further help , please let me know by email.
Crystal Reports Problem
We have migrated Windows NT ,SQL 7.0 to Windows 2003,SQL 2000 for BaaN IV c4.
Also we have installed crystal reports views from NT server.
On WIN2003 ,sql 2000 I can view the BaaN Tables. views also seen in the views area.
I am creating new crystal8.5 reports. that time i noticed only baan tables are seen for selecting.Views(file) are not able to browse.
Please advice How to activate and to enable me to write new crystal reports with baan table + views file..
Please Advice your expert opinion.
question in wrong thread
U have posted in the wrong thread. This thread is for Baan 4 GL based data extraction (from Baan report and table) and transformation tool which can load multi-dimensional data in Excel pivot table with lot of data manipulation capability at data extraction level.
I have limited basic crystal knowledge which may not be sufficient to answer ur question.I would recommend u to post ur question as a new thread , u can get better answers for this .
|All times are GMT +2. The time now is 17:32.|
©2001-2017 - Baanboard.com - Baanforums.com