There appears to be some concurrence emerging on BI front in the BI community. Such concurrence can be like

1. MS office (and MS excel in particular) integration with application is identified as important first & foremost component of BI for large and small enterprise equally.

2. As the company BI requirements grow, people can explore and deep dive into collaborative software Sharepoint , data warehouses , reporting tools and services etc .

Here are the links from where above conclusions seems to be emerging.

Office 2010 heeds BI propagation

Don’t dismiss Excel in your BI strategy

Microsoft Promises BI For The Masses With PowerPivot For Excel

Even Infor is buddying up with MS . So users may not have to worry for office integration at all and depend on any 3rd party products !!

• Read more

Online dashboards are frequent user queries / reports with set of all inter-related questions capable of being answered by the dashboard under user control (and not the user under control of dashboard).

Online Multi-dimensional modeling, online slicing & dicing ,charting & graphical analysis (thermostat, speedometer ,charts etc ) , drill-down to details for exceptional item etc are some of the essential features of dashboards.

Here is a sample of an excel based dashboard which gives online trial balance in multi-dimensional format with possibility to drill down to details from any database through ADODB.

Here are the links one can use to study , understand ,develop and use XL based dashboards.

http://www.erpjewels.com/Excel based Trial balance dashboard report.docx

http://www.erpjewels.com/trialbalance.xlsm

Getting graphs /charts with symbols like thermostat / speedometer etc are very easy. One can see umpteen online resource like following in the internet.

• Read more

Online dashboards coupled with in-memory analysis and predictive / statistical analytics form the major drivers of current BI solutions besides the obvious major driver routine operational reporting.

Operational baan reporting supplemented by in-memory data transformation in Excel/Baan and statistical functions in Excel substantially fulfils this need . However dashboards are an area so far unaddressed.

For online dashboards also Excel can play a good role. There is a myth that Excel can only be used with MS SQL. But that’s not correct . It can also be used with other databases like Oracle / DB2 which  may be installed on non-windows system through ADO-DB connections .

Here are certain resources which can help user build Excel based dashboards. 

Excellent dashboard reports in http://www.exceluser.com/dash/index.htm

Importance of using named ranges (which can be used like variables in VBA by statements like application.range(“named_range”) and functions like lookup/match etc: http://charts.jorgecamoes.com/how-to-create-an-excel-dashboard/. Very good tips to improve dashboards and having good interactivity with user with dashboards: http://charts.jorgecamoes.com/tips-improve-better-excel-dashboard/

• Read more

Normally individual and collective wisdom / insights which takes care off latest trends outside system , historical data , seasonal fluctuations , other correlations in predicting events and data. At times such predictions need to be supplemented by statistical analytical tools especially in case of scientific researches and vast tera bytes of data.

One can understand the importance of such predictive analysis from free Oracle Spreadsheet Add-In for Predictive Analytics

• Read more

Scheduling emails of Analyses and Reports in Baan

It’s very important to be able to schedule the reports / analyses in Baan and email the same to the user in a meaningfully crafted message. This enables system to PUSH the intelligence and rather than requiring users to PULL the same.

Scheduling for Unequal periods and numeric periods

In Baan, Job management is the instrument of task / process scheduling. The same can be useful for scheduling report also. However there are following problems.

• Read more

Tips for Multi-company / Calculated items in MS SQL query

If one is using SQL 2000/2005 and one wants to access the same from outside (say MS Excel or .net application or any other application) , one can use the SQL client in conjunction with  following SQL tips.

Multi-company query

Though baan driver let’s user access data from same table from multiple companies using compnr specification, in the MS SQL backend the tables are separate with company suffix . If one is to combine the data from multiple companies for same table, Union operator is SQL query is the key. Using this operator also one may need to know from which company the data has originated . In Baan 4GL one can get this with ._compnr field selected . In MS SQL one needs to  hard code the company constant expression in each SQL  query  combined with UNION operator . An example will illustrate this clearly.

Select t_leac as Ledger ,t_dbcr ‘Debit/Credit’,t_year as Year ,t_fprd as Period, t_amth as Amount , ‘300’ as company – Constant for company

From ttfgld106300

UNION

Select t_leac as Ledger ,t_dbcr ‘Debit/Credit’,t_year as Year ,t_fprd as Period, t_amth as Amount, ‘301’ as company – Constant for company

From ttfgld106301

This example will select data from tfgld106 of 300 and 301 company.

• Read more

While working on analysis services found certain good Excel add-ins available freely. Though some of the add-ins are not useful to us as we already have long established smart programs to accomplish much more than these add-ins do , I indicate the same here for those who may wish to use such approach for their excel integration with their database ( whatever it be ).

• Read more

Here is a blog on a reporting strategy for baan complemented with Excel load clients. This blog discusses various approaches to reporting in Excel and ingredients of successful Excel assisted baan reporting.

WHO SHOULD READ THIS  

One should read this blog if:

1. one is very much comfortable with existing authorizations and semantic layer in Baan (Read baan data dictionary ) technically ( table/domain /field codes ) and functionally (descriptions )  and
2. one wishes to capitalize on  existing learning and knowledge of existing semantic layer in Baan (technically as well as functionally )  and do not wish to invest (or waste??) in learning / migrating /adopting / translating / maintaining new semantic layer for reporting
3. one is very much adept at using Baan reporting and programming for routine documents  and
4. one’s users enjoy the freedom, flexibility and functionality of spreadsheets (which may be freeware also) for analytical purposes.
5. there are sufficient Office licenses already with company on PCs with high CPU and memory ( far exceeding basic Excel requirements and which have become norm rather than exceptions ) in one’s place and there is high need to optimally utilize  these resources  and to reduce the load on servers .

WHO SHOULD NOT READ THIS  

One whose objectives are different OR who don’t have any objectives only OR who is extremely satisfied with their current reporting already.

• Read more
• 1 attachment

Advanced Excel Training blog 

Spreadsheets are great number-crunching analytical tools. Knowing it’s analytical capabilities and generating MIS speedily with such spreadsheets, is very critical to success of any manager. And it’s successful usage ensures insights unlimited, ingenuity guaranteed and intelligence free from any shackles. And this in turn leads to lean and mean system architecture least recurring costs while maximizing gains.

Here is a blog to sharpen one's MS Excel analytical skills of Baan and ERPJewels users with lots of training material for MS Excel. Though most  training material is specific to Office 2007, it can be useful for earlier (or even later) versions for most compatible features and functions. One can download this MS Excel training resources from this link. The zip file contains following documents. 

• Read more

Now it is becoming increasingly clear that users need liveliness and interactivity on static data from OLTP. This is possible through multi-dimensional modeling available in free-form pivot table or cube structures. As there are not many cube applications and not many established routines to populate cubes incrementally periodically automatically, report-directly-in-excel is becoming vogue.  Though most summarized multi-dimensional modeling cab be done easily with old MS Excel 2003, with excellent enhancement in MS Excel 2007, scalability and liveliness has increased manifold in pivoting (multi-dimensional modeling using pivot table).

All that one need is accurate, reliable and relevant figures side-by-side in CSV format for all dimensions / measures to follow report-directly-in-excel-with-data-dict-advantages strategy. For example, here is a baan report which gives all important dimensions/measures related to customer financial behavior.

Here is the list of dimensions:

• Read more