Business Intellegence

[cjhowe]

David, others,

A couple of weeks ago there was some talk about
business intelligence in OFBiz.  Like others, I
started looking at Pentaho.  Reading through their
documentation the only real difference between the
basics of Pentaho and OFBiz is the integration of an
OLAP Engine and a structured way (meaning specifically
for BI reporting) to put the components together.  Was
there any other ideas/technology that you see as
needed to put together to get that caliber of insight
out of OFBiz?

[Jacopo Cappellato]

We should really consider to add (or integrate) in OFBiz some facilities
for datawarehousing.
I don't know how Pentaho works, and I've still not researched for
similar tools but I think before looking outside we should set up
something in OFBiz for this: I'm thinking of a new entity group (e.g.
org.ofbiz.datawarehouse) to store denormalized/customized entities that
will be the basis of the new reports (these entities could be stored in
a different db); triggers or scheduled services could fill the entities;
we should design the whole thing so that the layout (and supporting
services) of the entities will be easy to customize, and we could
include in SVN the basic layout.

I guess that this would allow the creation of (simple) reports (using
the widgets?) based on complex analysis and data manipulation (done by
the scheduled services/triggers) but also more complex ones using an
external reporting tool.

And about simpler reports (but this is a bit off topic here) I strongly
believe that we should all use the screen/form widgets and just forget
about JasperReport etc... because with them it is already possible to
create very quickly simple/nice/easy-to-customize reports (in html/pdf/xml).

Jacopo

Chris Howe wrote:

> David, others,
>
> A couple of weeks ago there was some talk about
> business intelligence in OFBiz.  Like others, I
> started looking at Pentaho.  Reading through their
> documentation the only real difference between the
> basics of Pentaho and OFBiz is the integration of an
> OLAP Engine and a structured way (meaning specifically
> for BI reporting) to put the components together.  Was
> there any other ideas/technology that you see as
> needed to put together to get that caliber of insight
> out of OFBiz?

[David E Jones-2]

I agree on both of these points. Using the widgets for reporting  
works out pretty nicely, and is very consistent with the application  
components which is a big plus for most users to reduce the overall  
skill sets required for development and maintenance.

For the data warehousing it would be nice to get at least some  
examples of this. There are various reports that are just too slow to  
run with lots of historical or other data without a preliminary  
transformation. It would be great to pick a couple and create and  
warehousing tables for these.

It would be nice to keep all of these entities in a separate group,  
and then according to that a separate database. For now it would just  
be another Derby database, but the nice thing about doing it this way  
is that in production it would be easier to have the warehouse  
database on a separate box, which a lot of companies do with this  
sort of thing.

The main thing left that is needed is a tool to transform the data  
from the source tables to the target tables in the data warehouse.  
The entity engine and simple-methods together are actually quite good  
for this sort of data mapping and moving.

So yes, I think we already have a pretty good set of tools for this  
and the next step would just be to get a proof of concept done and  
then starting applying it more widely.

-David


On Nov 5, 2006, at 2:17 AM, Jacopo Cappellato wrote:

> We should really consider to add (or integrate) in OFBiz some  
> facilities for datawarehousing.
> I don't know how Pentaho works, and I've still not researched for  
> similar tools but I think before looking outside we should set up  
> something in OFBiz for this: I'm thinking of a new entity group  
> (e.g. org.ofbiz.datawarehouse) to store denormalized/customized  
> entities that will be the basis of the new reports (these entities  
> could be stored in a different db); triggers or scheduled services  
> could fill the entities; we should design the whole thing so that  
> the layout (and supporting services) of the entities will be easy  
> to customize, and we could include in SVN the basic layout.
>
> I guess that this would allow the creation of (simple) reports  
> (using the widgets?) based on complex analysis and data  
> manipulation (done by the scheduled services/triggers) but also  
> more complex ones using an external reporting tool.
>
> And about simpler reports (but this is a bit off topic here) I  
> strongly believe that we should all use the screen/form widgets and  
> just forget about JasperReport etc... because with them it is  
> already possible to create very quickly simple/nice/easy-to-
> customize reports (in html/pdf/xml).
>
> Jacopo
>
> Chris Howe wrote:
>> David, others,
>> A couple of weeks ago there was some talk about
>> business intelligence in OFBiz.  Like others, I
>> started looking at Pentaho.  Reading through their
>> documentation the only real difference between the
>> basics of Pentaho and OFBiz is the integration of an
>> OLAP Engine and a structured way (meaning specifically
>> for BI reporting) to put the components together.  Was
>> there any other ideas/technology that you see as
>> needed to put together to get that caliber of insight
>> out of OFBiz?
>

[cjhowe]

--- David E Jones <[hidden email]>
wrote:

> The main thing left that is needed is a tool to
> transform the data  
> from the source tables to the target tables in the
> data warehouse.  
> The entity engine and simple-methods together are
> actually quite good  
> for this sort of data mapping and moving.
>
> So yes, I think we already have a pretty good set of
> tools for this  
> and the next step would just be to get a proof of
> concept done and  
> then starting applying it more widely.
>
> -David
>

This weekend I had the opportunity to play with the
OLAP portion of Pentaho, Mondrian, through another
open source project openI
(http://openi.sourceforge.net/).  The creation of
analysis reports is done with JSPs but they have it
set up to create them programmatically through the web
application.  

I have to say, it had some moments of really shining
with the ofbiz data.  With speed and features. It
really pushes a lot of the query decision making down
to the user and takes it off of the developer (where
once it's decided what data is permissible to see is
where it should be).

I mention this because there's a critical size of data
that must be met first before the overhead of the CUD
on a denormalized data warehouse is beneficial and not
a hindrance.

Anyway, I think a lot of what openi does can be
replicated even smoother in OFBiz pretty easily by
someone who's familiar with JSPs (the implicit
references most JSP projects use seem too arbitrary
for me to follow).

Setting up OpenI was a bit smoother for me than the
Pentaho demo, so I wasn't able to evaluate Pentaho as
a package. Hopefully next weekend will fair better.

One side question.  Are the view-entities supposed to
be creating actual views in the database?  Because
they don't in mysql. I had only used ofbiz to
manipulate views before so I never looked at it.  But
now creating some OLAP schemas I'm noticing they're
not there.