Business Intelligence

[snowch]

Is there any work going on at the moment to build an ofbiz data  
warehouse and reporting infrastructure, for example the integration  
of the Pentaho toolset?

Many thanks ...

--
This message has been scanned for viruses and
dangerous content by MailScanner, and is
believed to be clean.

[Jacopo Cappellato]

Christopher,

still nothing official or concrete, as far as I know.
I know that Chris Howe did some integration tests with OpenI:
http://docs.ofbiz.org/x/0AI

I am seriously considering a different approach; while I'm studying the
book "The Datawarehouse Toolkit"
(http://ofbiz.apache.org/documents.html), I'm trying to draft out a
proposal for the implementation of base datawarehousing features in
OFBiz (a separate set of entities for dimensions, facts and start
schemas; ETL services based on minilang, tools to manage the dimensions
tables and synchronization).
You'll find some of my notes here:
http://docs.ofbiz.org/x/2QI
My goal is this: once we have a set of star schemas (facts and
dimensions) derived from OFBiz entities and based on best practices, and
the tools to manage the data in them, we could integrate a visual
reporting tool to run reports against them (or just use, with some
improvements the form widgets).
It may seem an ambitious plan, but I think that many of the building
blocks to complete it are already in the framework, we'll just have to
improve and fine tune them.
If you are interested in helping with this we could try to create a work
group for this...

Jacopo

Christopher Snow wrote:
> Is there any work going on at the moment to build an ofbiz data
> warehouse and reporting infrastructure, for example the integration of
> the Pentaho toolset?
>
> Many thanks ...
>
> --This message has been scanned for viruses and
> dangerous content by MailScanner, and is
> believed to be clean.

[snowch]

Jacopo,

I think there are three main user needs:

1) User driven Adhoc Query By Example 'live data' reports
2) Analytics
3) Predefined reports.

Query by Example reports are usually driven by the end application user.
Used adhoc, they allow the user to find out what is currently happening
in the live system.  After the initial creation, if they are regularly
run, this means they are becoming a management tool and usually get
converted to a predefined report.

Analytics are usually 'what if?' style reports.  They are effectively
the same as QBE, but they are usually run by senior management. They are
usually run against a data warehouse/OLAP cube and allow the comparison
of historical data.  

        E.g.
        how many sales did we have last year?
        how many sales last month?
        what are the sales currently at?
        what is the underlying cause of the sales trends?


Predefined reports are those that have precise layout/data requirements
(e.g. management board reports).  They can also have complex links/drill
downs to other reports/data.  They are also usually used as a management
tool as the report contents and data rules are usually well defined and
documented for end users.

My experience is in (some) ETL, Analytics (mostly OLAP) and (lots of)
Predefined reporting, but willing to investigate QBE.  There are a lot
of good tools out there I would recommend looking at:

 - SpagoBI for QBE
 - Pentaho Kettle for ETL (don't think we should couple ETL to minilang)
 - Pentaho Mondrian/JPivot Analytics (although my experience is Cognos)
 - Eclipse BIRT for predefined reports

There are other areas such as dashboards, data mining, etc, but then you
are looking at integrating a complete B.I. infrastructure such as
Pentaho.  I think the above list is enough to start with!

Cheers,

Chris



On Fri, 2007-02-16 at 08:15 +0100, Jacopo Cappellato wrote:

> Christopher,
>
> still nothing official or concrete, as far as I know.
> I know that Chris Howe did some integration tests with OpenI:
> http://docs.ofbiz.org/x/0AI
>
> I am seriously considering a different approach; while I'm studying the
> book "The Datawarehouse Toolkit"
> (http://ofbiz.apache.org/documents.html), I'm trying to draft out a
> proposal for the implementation of base datawarehousing features in
> OFBiz (a separate set of entities for dimensions, facts and start
> schemas; ETL services based on minilang, tools to manage the dimensions
> tables and synchronization).
> You'll find some of my notes here:
> http://docs.ofbiz.org/x/2QI
> My goal is this: once we have a set of star schemas (facts and
> dimensions) derived from OFBiz entities and based on best practices, and
> the tools to manage the data in them, we could integrate a visual
> reporting tool to run reports against them (or just use, with some
> improvements the form widgets).
> It may seem an ambitious plan, but I think that many of the building
> blocks to complete it are already in the framework, we'll just have to
> improve and fine tune them.
> If you are interested in helping with this we could try to create a work
> group for this...
>
> Jacopo
>
> Christopher Snow wrote:
> > Is there any work going on at the moment to build an ofbiz data
> > warehouse and reporting infrastructure, for example the integration of
> > the Pentaho toolset?
> >
> > Many thanks ...
> >
> > --This message has been scanned for viruses and
> > dangerous content by MailScanner, and is
> > believed to be clean.
>
>
>

[David E Jones]

Jacopo,

I like the idea of this approach, and it's something I've been  
thinking about for a while.

A good next step might be to do a PoC implementation with maybe just  
one star schema using a simple-method to do the ETL, and a form  
widget to report on the data. In that process we'll probably find  
some things that are ugly and could use some tool extensions.

BTW, there are some interesting generic star schemas in the Data  
Model Resource Book. There is a sales one with some requirements in  
the form of questions on page 370 of volume 1, and a model diagram on  
page 371.

The data warehouse entities should go into their own entity group, a  
new group, with it's own datasource in the OOTB entityengine.xml  
file. A good group name might be something like org.ofbiz.olap (as  
opposed to oltp which characterizes most of the current entities.

Anyway, yeah this would be cool, and we already have a lot of tools  
that would work really well here.

-David


On Feb 16, 2007, at 12:15 AM, Jacopo Cappellato wrote:

> Christopher,
>
> still nothing official or concrete, as far as I know.
> I know that Chris Howe did some integration tests with OpenI:
> http://docs.ofbiz.org/x/0AI
>
> I am seriously considering a different approach; while I'm studying  
> the book "The Datawarehouse Toolkit" (http://ofbiz.apache.org/ 
> documents.html), I'm trying to draft out a proposal for the  
> implementation of base datawarehousing features in OFBiz (a  
> separate set of entities for dimensions, facts and start schemas;  
> ETL services based on minilang, tools to manage the dimensions  
> tables and synchronization).
> You'll find some of my notes here:
> http://docs.ofbiz.org/x/2QI
> My goal is this: once we have a set of star schemas (facts and  
> dimensions) derived from OFBiz entities and based on best  
> practices, and the tools to manage the data in them, we could  
> integrate a visual reporting tool to run reports against them (or  
> just use, with some improvements the form widgets).
> It may seem an ambitious plan, but I think that many of the  
> building blocks to complete it are already in the framework, we'll  
> just have to improve and fine tune them.
> If you are interested in helping with this we could try to create a  
> work group for this...
>
> Jacopo
>
> Christopher Snow wrote:
>> Is there any work going on at the moment to build an ofbiz data  
>> warehouse and reporting infrastructure, for example the  
>> integration of the Pentaho toolset?
>> Many thanks ...
>> --This message has been scanned for viruses and
>> dangerous content by MailScanner, and is
>> believed to be clean.
>
>

[Jacopo Cappellato]

David,

first of all, thanks for the insight, much appreciated.

David E. Jones wrote:

>
> Jacopo,
>
> I like the idea of this approach, and it's something I've been thinking
> about for a while.
>
> A good next step might be to do a PoC implementation with maybe just one
> star schema using a simple-method to do the ETL, and a form widget to
> report on the data. In that process we'll probably find some things that
> are ugly and could use some tool extensions.
>

I agree with you that we should start with a pretty simple PoC
implementation. However, while reading the book "The Datawarehouse
Toolkit" in my (spare?) time I'm taking notes about tool extensions that
will help to build the datawarehouse; here are a few examples of these
very preliminary requirements:

1) add support in the entity engine for view-entities that are stored in
the db as sql VIEWs (it seems that we will need many different views
over the same shared dimensions; for example the "Date" dimension, could
play different roles in the datawarehouse)

2) implement util methods to populate the Date dimension (that will
store all the days of the years of interest for the analysis) and the
Time dimenstion (all the minutes of a day)

etc...

> BTW, there are some interesting generic star schemas in the Data Model
> Resource Book. There is a sales one with some requirements in the form
> of questions on page 370 of volume 1, and a model diagram on page 371.
>

I will definitely have a look at them.

> The data warehouse entities should go into their own entity group, a new
> group, with it's own datasource in the OOTB entityengine.xml file. A
> good group name might be something like org.ofbiz.olap (as opposed to
> oltp which characterizes most of the current entities.
>

I agree with everything you write here; by the way Iìd like to clarify
that the name "olap" here will be used in its original (generic) meaning
of "Online Analytic Processing" and not to designate the underlying
technology of the db (that in my initial plan will be a relational db
with star schemas and not an olap db with cubes).

> Anyway, yeah this would be cool, and we already have a lot of tools that
> would work really well here.
>

I will try to write down some notes in the Confluence doc site and see
if we can get this ball rolling... do you think that the upcoming
conference would be a good chance to speed up things?

Jacopo

> -David
>
>
> On Feb 16, 2007, at 12:15 AM, Jacopo Cappellato wrote:
>
>> Christopher,
>>
>> still nothing official or concrete, as far as I know.
>> I know that Chris Howe did some integration tests with OpenI:
>> http://docs.ofbiz.org/x/0AI
>>
>> I am seriously considering a different approach; while I'm studying
>> the book "The Datawarehouse Toolkit"
>> (http://ofbiz.apache.org/documents.html), I'm trying to draft out a
>> proposal for the implementation of base datawarehousing features in
>> OFBiz (a separate set of entities for dimensions, facts and start
>> schemas; ETL services based on minilang, tools to manage the
>> dimensions tables and synchronization).
>> You'll find some of my notes here:
>> http://docs.ofbiz.org/x/2QI
>> My goal is this: once we have a set of star schemas (facts and
>> dimensions) derived from OFBiz entities and based on best practices,
>> and the tools to manage the data in them, we could integrate a visual
>> reporting tool to run reports against them (or just use, with some
>> improvements the form widgets).
>> It may seem an ambitious plan, but I think that many of the building
>> blocks to complete it are already in the framework, we'll just have to
>> improve and fine tune them.
>> If you are interested in helping with this we could try to create a
>> work group for this...
>>
>> Jacopo
>>
>> Christopher Snow wrote:
>>> Is there any work going on at the moment to build an ofbiz data
>>> warehouse and reporting infrastructure, for example the integration
>>> of the Pentaho toolset?
>>> Many thanks ...
>>> --This message has been scanned for viruses and
>>> dangerous content by MailScanner, and is
>>> believed to be clean.
>>
>>
>

[cjhowe]

Jacopo,

I still don't think I'm understanding the benefit of this being native
in OFBiz as opposed to a cube style integration that
mondrian/pentaho/etc might provide?  

Obviously additional services will need to be created to denormalize
and pick out relevant data regardless of the approach, but that's not
really the meat and potatoes that the pentaho stuff is demonstrating.
Mondrian is essentially taking relational database views (that can be
based on star schemas) and creating the multi-dimensional cube that
you're going to be ending up with ultimately if you're planning on
using time as a dimension, only mondrian is prepared to take on many
more dimensions as well.

I understand wanting to benefit from the screen and form widgets, but
to utilize multiple dimensions, I imagine you would be creating forms
that could only be reused by multidimensional data anyway which is the
same place you get with a pentaho integration except with pentaho
you're also getting the on the fly query instead of being predefined.

I'm really excited about this topic and see my calendar clearing up to
be able to help but am afraid my toolbox may not be deep enough to
provide help in the direction you're talking about.  Which is fine. It
just seems like a lot of time spent on something that appears to
already exist and have minds that are in the industry of BI
contributing to it.

I'm still learning the penatho stuff and am by no means an expert, just
curious as to what you're seeing on the surface that makes you want to
stay inside OFBiz for the solution.

--- Jacopo Cappellato <[hidden email]> wrote:

> David,
>
> first of all, thanks for the insight, much appreciated.
>
> David E. Jones wrote:
> >
> > Jacopo,
> >
> > I like the idea of this approach, and it's something I've been
> thinking
> > about for a while.
> >
> > A good next step might be to do a PoC implementation with maybe
> just one
> > star schema using a simple-method to do the ETL, and a form widget
> to
> > report on the data. In that process we'll probably find some things
> that
> > are ugly and could use some tool extensions.
> >
>
> I agree with you that we should start with a pretty simple PoC
> implementation. However, while reading the book "The Datawarehouse
> Toolkit" in my (spare?) time I'm taking notes about tool extensions
> that
> will help to build the datawarehouse; here are a few examples of
> these
> very preliminary requirements:
>
> 1) add support in the entity engine for view-entities that are stored
> in
> the db as sql VIEWs (it seems that we will need many different views
> over the same shared dimensions; for example the "Date" dimension,
> could
> play different roles in the datawarehouse)
>
> 2) implement util methods to populate the Date dimension (that will
> store all the days of the years of interest for the analysis) and the
>
> Time dimenstion (all the minutes of a day)
>
> etc...
>
> > BTW, there are some interesting generic star schemas in the Data
> Model
> > Resource Book. There is a sales one with some requirements in the
> form
> > of questions on page 370 of volume 1, and a model diagram on page
> 371.
> >
>
> I will definitely have a look at them.
>
> > The data warehouse entities should go into their own entity group,
> a new
> > group, with it's own datasource in the OOTB entityengine.xml file.
> A
> > good group name might be something like org.ofbiz.olap (as opposed
> to
> > oltp which characterizes most of the current entities.
> >
>
> I agree with everything you write here; by the way Iìd like to
> clarify
> that the name "olap" here will be used in its original (generic)
> meaning
> of "Online Analytic Processing" and not to designate the underlying
> technology of the db (that in my initial plan will be a relational db
>
> with star schemas and not an olap db with cubes).
>
> > Anyway, yeah this would be cool, and we already have a lot of tools
> that
> > would work really well here.
> >
>
> I will try to write down some notes in the Confluence doc site and
> see
> if we can get this ball rolling... do you think that the upcoming
> conference would be a good chance to speed up things?
>
> Jacopo
>
> > -David
> >
> >
> > On Feb 16, 2007, at 12:15 AM, Jacopo Cappellato wrote:
> >
> >> Christopher,
> >>
> >> still nothing official or concrete, as far as I know.
> >> I know that Chris Howe did some integration tests with OpenI:
> >> http://docs.ofbiz.org/x/0AI
> >>
> >> I am seriously considering a different approach; while I'm
> studying
> >> the book "The Datawarehouse Toolkit"
> >> (http://ofbiz.apache.org/documents.html), I'm trying to draft out
> a
> >> proposal for the implementation of base datawarehousing features
> in
> >> OFBiz (a separate set of entities for dimensions, facts and start
> >> schemas; ETL services based on minilang, tools to manage the
> >> dimensions tables and synchronization).
> >> You'll find some of my notes here:
> >> http://docs.ofbiz.org/x/2QI
> >> My goal is this: once we have a set of star schemas (facts and
> >> dimensions) derived from OFBiz entities and based on best
> practices,
> >> and the tools to manage the data in them, we could integrate a
> visual
> >> reporting tool to run reports against them (or just use, with some
>
> >> improvements the form widgets).
> >> It may seem an ambitious plan, but I think that many of the
> building
> >> blocks to complete it are already in the framework, we'll just
> have to
> >> improve and fine tune them.
> >> If you are interested in helping with this we could try to create
> a
> >> work group for this...
> >>
> >> Jacopo
> >>
> >> Christopher Snow wrote:
> >>> Is there any work going on at the moment to build an ofbiz data
> >>> warehouse and reporting infrastructure, for example the
> integration
> >>> of the Pentaho toolset?
> >>> Many thanks ...
> >>> --This message has been scanned for viruses and
> >>> dangerous content by MailScanner, and is
> >>> believed to be clean.
> >>
> >>
> >
>
>
>

[Jacopo Cappellato]

Chris,

first of all, thanks for your interest on this subject.
Please, see my comments inline:

Chris Howe wrote:
> Jacopo,
>
> I still don't think I'm understanding the benefit of this being native
> in OFBiz as opposed to a cube style integration that
> mondrian/pentaho/etc might provide?  
>
> Obviously additional services will need to be created to denormalize
> and pick out relevant data regardless of the approach, but that's not
> really the meat and potatoes that the pentaho stuff is demonstrating.

Building ETL services and an initial star schema (that I guess is what
you refer to with denormalized entities) is my primary goal now.

> Mondrian is essentially taking relational database views (that can be
> based on star schemas) and creating the multi-dimensional cube that
> you're going to be ending up with ultimately if you're planning on
> using time as a dimension, only mondrian is prepared to take on many
> more dimensions as well.

The star schema will be built in a relational database but essentially
is composed of:
a fact table (e.g. "Sales Transactions")
a set of dimension tables (e.g. "Products", "Date", "Time", "Stores" etc..)
Usually the fact table is only useful inside one star schema, while
dimensions are shared among many star schemas.
I'd like to build a common, based on best practices set of dimensions
(and a few fact tables that use them) derived from the OFBiz data model.
You can then use the tool you want to run your reports/analysis etc...
I don't know how Mondrian works, but by what you say here it seems that
you can use Mondrian on top of these star schemas.

>
> I understand wanting to benefit from the screen and form widgets, but
> to utilize multiple dimensions, I imagine you would be creating forms
> that could only be reused by multidimensional data anyway which is the
> same place you get with a pentaho integration except with pentaho
> you're also getting the on the fly query instead of being predefined.
>

Yes, I'd like also to provide some simple tools, based on form widgets
to create some simple reports based on the star schemas above but mainly
as a PoC... you can definitely use more advanced reporting tools for this.

Jacopo

> I'm really excited about this topic and see my calendar clearing up to
> be able to help but am afraid my toolbox may not be deep enough to
> provide help in the direction you're talking about.  Which is fine. It
> just seems like a lot of time spent on something that appears to
> already exist and have minds that are in the industry of BI
> contributing to it.
>
> I'm still learning the penatho stuff and am by no means an expert, just
> curious as to what you're seeing on the surface that makes you want to
> stay inside OFBiz for the solution.
>
> --- Jacopo Cappellato <[hidden email]> wrote:
>
>> David,
>>
>> first of all, thanks for the insight, much appreciated.
>>
>> David E. Jones wrote:
>>> Jacopo,
>>>
>>> I like the idea of this approach, and it's something I've been
>> thinking
>>> about for a while.
>>>
>>> A good next step might be to do a PoC implementation with maybe
>> just one
>>> star schema using a simple-method to do the ETL, and a form widget
>> to
>>> report on the data. In that process we'll probably find some things
>> that
>>> are ugly and could use some tool extensions.
>>>
>> I agree with you that we should start with a pretty simple PoC
>> implementation. However, while reading the book "The Datawarehouse
>> Toolkit" in my (spare?) time I'm taking notes about tool extensions
>> that
>> will help to build the datawarehouse; here are a few examples of
>> these
>> very preliminary requirements:
>>
>> 1) add support in the entity engine for view-entities that are stored
>> in
>> the db as sql VIEWs (it seems that we will need many different views
>> over the same shared dimensions; for example the "Date" dimension,
>> could
>> play different roles in the datawarehouse)
>>
>> 2) implement util methods to populate the Date dimension (that will
>> store all the days of the years of interest for the analysis) and the
>>
>> Time dimenstion (all the minutes of a day)
>>
>> etc...
>>
>>> BTW, there are some interesting generic star schemas in the Data
>> Model
>>> Resource Book. There is a sales one with some requirements in the
>> form
>>> of questions on page 370 of volume 1, and a model diagram on page
>> 371.
>> I will definitely have a look at them.
>>
>>> The data warehouse entities should go into their own entity group,
>> a new
>>> group, with it's own datasource in the OOTB entityengine.xml file.
>> A
>>> good group name might be something like org.ofbiz.olap (as opposed
>> to
>>> oltp which characterizes most of the current entities.
>>>
>> I agree with everything you write here; by the way Iìd like to
>> clarify
>> that the name "olap" here will be used in its original (generic)
>> meaning
>> of "Online Analytic Processing" and not to designate the underlying
>> technology of the db (that in my initial plan will be a relational db
>>
>> with star schemas and not an olap db with cubes).
>>
>>> Anyway, yeah this would be cool, and we already have a lot of tools
>> that
>>> would work really well here.
>>>
>> I will try to write down some notes in the Confluence doc site and
>> see
>> if we can get this ball rolling... do you think that the upcoming
>> conference would be a good chance to speed up things?
>>
>> Jacopo
>>
>>> -David
>>>
>>>
>>> On Feb 16, 2007, at 12:15 AM, Jacopo Cappellato wrote:
>>>
>>>> Christopher,
>>>>
>>>> still nothing official or concrete, as far as I know.
>>>> I know that Chris Howe did some integration tests with OpenI:
>>>> http://docs.ofbiz.org/x/0AI
>>>>
>>>> I am seriously considering a different approach; while I'm
>> studying
>>>> the book "The Datawarehouse Toolkit"
>>>> (http://ofbiz.apache.org/documents.html), I'm trying to draft out
>> a
>>>> proposal for the implementation of base datawarehousing features
>> in
>>>> OFBiz (a separate set of entities for dimensions, facts and start
>>>> schemas; ETL services based on minilang, tools to manage the
>>>> dimensions tables and synchronization).
>>>> You'll find some of my notes here:
>>>> http://docs.ofbiz.org/x/2QI
>>>> My goal is this: once we have a set of star schemas (facts and
>>>> dimensions) derived from OFBiz entities and based on best
>> practices,
>>>> and the tools to manage the data in them, we could integrate a
>> visual
>>>> reporting tool to run reports against them (or just use, with some
>>>> improvements the form widgets).
>>>> It may seem an ambitious plan, but I think that many of the
>> building
>>>> blocks to complete it are already in the framework, we'll just
>> have to
>>>> improve and fine tune them.
>>>> If you are interested in helping with this we could try to create
>> a
>>>> work group for this...
>>>>
>>>> Jacopo
>>>>
>>>> Christopher Snow wrote:
>>>>> Is there any work going on at the moment to build an ofbiz data
>>>>> warehouse and reporting infrastructure, for example the
>> integration
>>>>> of the Pentaho toolset?
>>>>> Many thanks ...
>>>>> --This message has been scanned for viruses and
>>>>> dangerous content by MailScanner, and is
>>>>> believed to be clean.
>>>>
>>
>>

[cjhowe]

Inline..
--- Jacopo Cappellato <[hidden email]> wrote:

> Chris,
>
> first of all, thanks for your interest on this subject.
> Please, see my comments inline:
>

> Building ETL services and an initial star schema (that I guess is
> what
> you refer to with denormalized entities) is my primary goal now.

From the Pentaho site

    *  Mondrian - Open Source OLAP Server
    * JFreeReport - Open Source Reporting
>>    * Kettle - Open Source Data Integration (E.T.T.L.)  <<
    * Pentaho - Comprehensive Open Source BI Suite
    * Weka - Open Source Data Mining

ETTL means...

    * Extraction of data from one or more databases
    * Transport of data from one location to an other
    * Transformation of data
    * Loading of data in a data warehouse

So their's even has an extra "T" ;)

>
> The star schema will be built in a relational database but
> essentially
> is composed of:
> a fact table (e.g. "Sales Transactions")
> a set of dimension tables (e.g. "Products", "Date", "Time", "Stores"
> etc..)
> Usually the fact table is only useful inside one star schema, while
> dimensions are shared among many star schemas.
> I'd like to build a common, based on best practices set of dimensions
>
> (and a few fact tables that use them) derived from the OFBiz data
> model.
> You can then use the tool you want to run your reports/analysis
> etc...
> I don't know how Mondrian works, but by what you say here it seems
> that
> you can use Mondrian on top of these star schemas.
>

That is almost an exact description of what a cube is in Mondrian and
from what I understand Microsoft's OLAP as well
http://mondrian.pentaho.org/documentation/schema.php#Cube

[Jacopo Cappellato]

Chris,

it is obviously difficult for me to compare the features that I can
offer to the rich set of the ones offered by a suite of
open-source/commercial softwares: in fact I don't have anything at the
moment on my desk!
In my opinion, the drafted plan that I'm considering could lead to a
better/cleaner set of dimensions and facts (derived by the OFBiz data
model); if in the process we will find good ways of integrating (or just
using) external tools (and I hope this will happen) I would be more than
happy to invest some time in their integration.

Maybe, it would be really helpful at this stage to find out what are the
*cons* of integrating an external suite for datawarehouse; for example,
since you did some experiments integrating such tools in OFBiz, what are
the problems (if any) you found using them with OFBiz? What are the
challenges we will have to resolve if we try to integrate them?

Thanks,

Jacopo


Chris Howe wrote:

> Inline..
> --- Jacopo Cappellato <[hidden email]> wrote:
>
>> Chris,
>>
>> first of all, thanks for your interest on this subject.
>> Please, see my comments inline:
>>
>
>> Building ETL services and an initial star schema (that I guess is
>> what
>> you refer to with denormalized entities) is my primary goal now.
>
>>From the Pentaho site
>
>     *  Mondrian - Open Source OLAP Server
>     * JFreeReport - Open Source Reporting
>>>    * Kettle - Open Source Data Integration (E.T.T.L.)  <<
>     * Pentaho - Comprehensive Open Source BI Suite
>     * Weka - Open Source Data Mining
>
> ETTL means...
>
>     * Extraction of data from one or more databases
>     * Transport of data from one location to an other
>     * Transformation of data
>     * Loading of data in a data warehouse
>
> So their's even has an extra "T" ;)
>
>> The star schema will be built in a relational database but
>> essentially
>> is composed of:
>> a fact table (e.g. "Sales Transactions")
>> a set of dimension tables (e.g. "Products", "Date", "Time", "Stores"
>> etc..)
>> Usually the fact table is only useful inside one star schema, while
>> dimensions are shared among many star schemas.
>> I'd like to build a common, based on best practices set of dimensions
>>
>> (and a few fact tables that use them) derived from the OFBiz data
>> model.
>> You can then use the tool you want to run your reports/analysis
>> etc...
>> I don't know how Mondrian works, but by what you say here it seems
>> that
>> you can use Mondrian on top of these star schemas.
>>
>
> That is almost an exact description of what a cube is in Mondrian and
> from what I understand Microsoft's OLAP as well
> http://mondrian.pentaho.org/documentation/schema.php#Cube
>

[Jacopo Cappellato]

If (but not guaranteed) I will start writing down something concrete,
should I wrap it in a new specialpurpose component and commit (or put in
Jira) it asap to get early feedback from the community? If so, which name?

Jacopo

David E. Jones wrote:

>
> Jacopo,
>
> I like the idea of this approach, and it's something I've been thinking
> about for a while.
>
> A good next step might be to do a PoC implementation with maybe just one
> star schema using a simple-method to do the ETL, and a form widget to
> report on the data. In that process we'll probably find some things that
> are ugly and could use some tool extensions.
>
> BTW, there are some interesting generic star schemas in the Data Model
> Resource Book. There is a sales one with some requirements in the form
> of questions on page 370 of volume 1, and a model diagram on page 371.
>
> The data warehouse entities should go into their own entity group, a new
> group, with it's own datasource in the OOTB entityengine.xml file. A
> good group name might be something like org.ofbiz.olap (as opposed to
> oltp which characterizes most of the current entities.
>
> Anyway, yeah this would be cool, and we already have a lot of tools that
> would work really well here.
>
> -David
>
>
> On Feb 16, 2007, at 12:15 AM, Jacopo Cappellato wrote:
>
>> Christopher,
>>
>> still nothing official or concrete, as far as I know.
>> I know that Chris Howe did some integration tests with OpenI:
>> http://docs.ofbiz.org/x/0AI
>>
>> I am seriously considering a different approach; while I'm studying
>> the book "The Datawarehouse Toolkit"
>> (http://ofbiz.apache.org/documents.html), I'm trying to draft out a
>> proposal for the implementation of base datawarehousing features in
>> OFBiz (a separate set of entities for dimensions, facts and start
>> schemas; ETL services based on minilang, tools to manage the
>> dimensions tables and synchronization).
>> You'll find some of my notes here:
>> http://docs.ofbiz.org/x/2QI
>> My goal is this: once we have a set of star schemas (facts and
>> dimensions) derived from OFBiz entities and based on best practices,
>> and the tools to manage the data in them, we could integrate a visual
>> reporting tool to run reports against them (or just use, with some
>> improvements the form widgets).
>> It may seem an ambitious plan, but I think that many of the building
>> blocks to complete it are already in the framework, we'll just have to
>> improve and fine tune them.
>> If you are interested in helping with this we could try to create a
>> work group for this...
>>
>> Jacopo
>>
>> Christopher Snow wrote:
>>> Is there any work going on at the moment to build an ofbiz data
>>> warehouse and reporting infrastructure, for example the integration
>>> of the Pentaho toolset?
>>> Many thanks ...
>>> --This message has been scanned for viruses and
>>> dangerous content by MailScanner, and is
>>> believed to be clean.
>>
>>
>

[David E Jones]

Depending on what you do I'd say this should go straight into the  
product or order or whatever component, just like any reporting stuff  
would.

-David


On Feb 18, 2007, at 12:31 AM, Jacopo Cappellato wrote:

> If (but not guaranteed) I will start writing down something  
> concrete, should I wrap it in a new specialpurpose component and  
> commit (or put in Jira) it asap to get early feedback from the  
> community? If so, which name?
>
> Jacopo
>
> David E. Jones wrote:
>> Jacopo,
>> I like the idea of this approach, and it's something I've been  
>> thinking about for a while.
>> A good next step might be to do a PoC implementation with maybe  
>> just one star schema using a simple-method to do the ETL, and a  
>> form widget to report on the data. In that process we'll probably  
>> find some things that are ugly and could use some tool extensions.
>> BTW, there are some interesting generic star schemas in the Data  
>> Model Resource Book. There is a sales one with some requirements  
>> in the form of questions on page 370 of volume 1, and a model  
>> diagram on page 371.
>> The data warehouse entities should go into their own entity group,  
>> a new group, with it's own datasource in the OOTB entityengine.xml  
>> file. A good group name might be something like org.ofbiz.olap (as  
>> opposed to oltp which characterizes most of the current entities.
>> Anyway, yeah this would be cool, and we already have a lot of  
>> tools that would work really well here.
>> -David
>> On Feb 16, 2007, at 12:15 AM, Jacopo Cappellato wrote:
>>> Christopher,
>>>
>>> still nothing official or concrete, as far as I know.
>>> I know that Chris Howe did some integration tests with OpenI:
>>> http://docs.ofbiz.org/x/0AI
>>>
>>> I am seriously considering a different approach; while I'm  
>>> studying the book "The Datawarehouse Toolkit" (http://
>>> ofbiz.apache.org/documents.html), I'm trying to draft out a  
>>> proposal for the implementation of base datawarehousing features  
>>> in OFBiz (a separate set of entities for dimensions, facts and  
>>> start schemas; ETL services based on minilang, tools to manage  
>>> the dimensions tables and synchronization).
>>> You'll find some of my notes here:
>>> http://docs.ofbiz.org/x/2QI
>>> My goal is this: once we have a set of star schemas (facts and  
>>> dimensions) derived from OFBiz entities and based on best  
>>> practices, and the tools to manage the data in them, we could  
>>> integrate a visual reporting tool to run reports against them (or  
>>> just use, with some improvements the form widgets).
>>> It may seem an ambitious plan, but I think that many of the  
>>> building blocks to complete it are already in the framework,  
>>> we'll just have to improve and fine tune them.
>>> If you are interested in helping with this we could try to create  
>>> a work group for this...
>>>
>>> Jacopo
>>>
>>> Christopher Snow wrote:
>>>> Is there any work going on at the moment to build an ofbiz data  
>>>> warehouse and reporting infrastructure, for example the  
>>>> integration of the Pentaho toolset?
>>>> Many thanks ...
>>>> --This message has been scanned for viruses and
>>>> dangerous content by MailScanner, and is
>>>> believed to be clean.
>>>
>>>
>
>

[Jacopo Cappellato]

David,

I would like to set up some staging entities (e.g. the one to map the
oltp pk to the olap pk) and the services to manage them.
Also, "dimensions" will be shared by many star schemas and there are
some dimensions that are low level (the date and time dimensions).
Wouldn't be easier to maintain them in an indipendent component?

As a side note, here is the entity package layout I have in mind:

org.ofbiz.olap
org.ofbiz.olap.staging
org.ofbiz.olap.dimensions (e.g. "Products", "Stores", "Dates"...)
org.ofbiz.olap.facts (e.g. "Sales Transactions")
org.ofbiz.olap.starschemas (mostly views with one fact and many
dimensions each; used by reporting tools)
org.ofbiz.olap.starschemas.product
org.ofbiz.olap.starschemas.manufacturing
etc...

Another option could be this:

org.ofbiz.olap
org.ofbiz.olap.staging
org.ofbiz.olap.product.dimensions
org.ofbiz.olap.product.facts
org.ofbiz.olap.product.starschemas
org.ofbiz.olap.manufacturing.dimensions
org.ofbiz.olap.manufacturing.facts
org.ofbiz.olap.manufacturing.starschemas
etc...

What do you think?

Jacopo

David E. Jones wrote:

>
> Depending on what you do I'd say this should go straight into the
> product or order or whatever component, just like any reporting stuff
> would.
>
> -David
>
>
> On Feb 18, 2007, at 12:31 AM, Jacopo Cappellato wrote:
>
>> If (but not guaranteed) I will start writing down something concrete,
>> should I wrap it in a new specialpurpose component and commit (or put
>> in Jira) it asap to get early feedback from the community? If so,
>> which name?
>>
>> Jacopo
>>
>> David E. Jones wrote:
>>> Jacopo,
>>> I like the idea of this approach, and it's something I've been
>>> thinking about for a while.
>>> A good next step might be to do a PoC implementation with maybe just
>>> one star schema using a simple-method to do the ETL, and a form
>>> widget to report on the data. In that process we'll probably find
>>> some things that are ugly and could use some tool extensions.
>>> BTW, there are some interesting generic star schemas in the Data
>>> Model Resource Book. There is a sales one with some requirements in
>>> the form of questions on page 370 of volume 1, and a model diagram on
>>> page 371.
>>> The data warehouse entities should go into their own entity group, a
>>> new group, with it's own datasource in the OOTB entityengine.xml
>>> file. A good group name might be something like org.ofbiz.olap (as
>>> opposed to oltp which characterizes most of the current entities.
>>> Anyway, yeah this would be cool, and we already have a lot of tools
>>> that would work really well here.
>>> -David
>>> On Feb 16, 2007, at 12:15 AM, Jacopo Cappellato wrote:
>>>> Christopher,
>>>>
>>>> still nothing official or concrete, as far as I know.
>>>> I know that Chris Howe did some integration tests with OpenI:
>>>> http://docs.ofbiz.org/x/0AI
>>>>
>>>> I am seriously considering a different approach; while I'm studying
>>>> the book "The Datawarehouse Toolkit"
>>>> (http://ofbiz.apache.org/documents.html), I'm trying to draft out a
>>>> proposal for the implementation of base datawarehousing features in
>>>> OFBiz (a separate set of entities for dimensions, facts and start
>>>> schemas; ETL services based on minilang, tools to manage the
>>>> dimensions tables and synchronization).
>>>> You'll find some of my notes here:
>>>> http://docs.ofbiz.org/x/2QI
>>>> My goal is this: once we have a set of star schemas (facts and
>>>> dimensions) derived from OFBiz entities and based on best practices,
>>>> and the tools to manage the data in them, we could integrate a
>>>> visual reporting tool to run reports against them (or just use, with
>>>> some improvements the form widgets).
>>>> It may seem an ambitious plan, but I think that many of the building
>>>> blocks to complete it are already in the framework, we'll just have
>>>> to improve and fine tune them.
>>>> If you are interested in helping with this we could try to create a
>>>> work group for this...
>>>>
>>>> Jacopo
>>>>
>>>> Christopher Snow wrote:
>>>>> Is there any work going on at the moment to build an ofbiz data
>>>>> warehouse and reporting infrastructure, for example the integration
>>>>> of the Pentaho toolset?
>>>>> Many thanks ...
>>>>> --This message has been scanned for viruses and
>>>>> dangerous content by MailScanner, and is
>>>>> believed to be clean.
>>>>
>>>>
>>
>>
>

[cjhowe]

The only downside of the openI and Pentaho integrations is the manner
in which they store the datasource information, it's not centralized
very well, which should be rather trivial to overcome.  I spent a good
part of today getting Oleg and Kirill's datawarehouse component running
off a FoodMart database.  Still buggy, but I did get a result set to
display in a pivot table.

I wrote Oleg and Kirill off list to see if they'd be interested in
sticking their head back in it since their project has been dormant
since about July.

Their implementation utilizes the same controller.xml that we're used
to, the same service calls we're used to and runs off of the screen
widget.  All in all, a lot of promise.  

Because it uses the controller and screen widget, the schema definition
can benefit from a lot of reuse in separating the dimensions into
screens.

After that, create a simple way to generate the MDX query and we'd have
a pretty powerful tool. (after pulling the data from traditional ofbiz
that we're interested in, of course)

Oleg's and Kirill's component uses Octopus for the ETL, but I haven't
evaluated that, nor am I sure what I should be evaluating for that.

I'm still working out some bug's in Oleg and Kirill's component so let
me know if anyone is interested AND has time and I'll post a patch to
their work otherwise I'll post it when it seems pretty clean.



--- Jacopo Cappellato <[hidden email]> wrote:

> Chris,
>
> it is obviously difficult for me to compare the features that I can
> offer to the rich set of the ones offered by a suite of
> open-source/commercial softwares: in fact I don't have anything at
> the
> moment on my desk!
> In my opinion, the drafted plan that I'm considering could lead to a
> better/cleaner set of dimensions and facts (derived by the OFBiz data
>
> model); if in the process we will find good ways of integrating (or
> just
> using) external tools (and I hope this will happen) I would be more
> than
> happy to invest some time in their integration.
>
> Maybe, it would be really helpful at this stage to find out what are
> the
> *cons* of integrating an external suite for datawarehouse; for
> example,
> since you did some experiments integrating such tools in OFBiz, what
> are
> the problems (if any) you found using them with OFBiz? What are the
> challenges we will have to resolve if we try to integrate them?
>
> Thanks,
>
> Jacopo
>
>
> Chris Howe wrote:
> > Inline..
> > --- Jacopo Cappellato <[hidden email]> wrote:
> >
> >> Chris,
> >>
> >> first of all, thanks for your interest on this subject.
> >> Please, see my comments inline:
> >>
> >
> >> Building ETL services and an initial star schema (that I guess is
> >> what
> >> you refer to with denormalized entities) is my primary goal now.
> >
> >>From the Pentaho site
> >
> >     *  Mondrian - Open Source OLAP Server
> >     * JFreeReport - Open Source Reporting
> >>>    * Kettle - Open Source Data Integration (E.T.T.L.)  <<
> >     * Pentaho - Comprehensive Open Source BI Suite
> >     * Weka - Open Source Data Mining
> >
> > ETTL means...
> >
> >     * Extraction of data from one or more databases
> >     * Transport of data from one location to an other
> >     * Transformation of data
> >     * Loading of data in a data warehouse
> >
> > So their's even has an extra "T" ;)
> >
> >> The star schema will be built in a relational database but
> >> essentially
> >> is composed of:
> >> a fact table (e.g. "Sales Transactions")
> >> a set of dimension tables (e.g. "Products", "Date", "Time",
> "Stores"
> >> etc..)
> >> Usually the fact table is only useful inside one star schema,
> while
> >> dimensions are shared among many star schemas.
> >> I'd like to build a common, based on best practices set of
> dimensions
> >>
> >> (and a few fact tables that use them) derived from the OFBiz data
> >> model.
> >> You can then use the tool you want to run your reports/analysis
> >> etc...
> >> I don't know how Mondrian works, but by what you say here it seems
> >> that
> >> you can use Mondrian on top of these star schemas.
> >>
> >
> > That is almost an exact description of what a cube is in Mondrian
> and
> > from what I understand Microsoft's OLAP as well
> > http://mondrian.pentaho.org/documentation/schema.php#Cube
> >
>
>
>

[David E Jones]

I like the second group pattern better.

Along with that you could take all generic dimension entities and put  
them in the common component.

-David


On Feb 18, 2007, at 1:00 AM, Jacopo Cappellato wrote:

> David,
>
> I would like to set up some staging entities (e.g. the one to map  
> the oltp pk to the olap pk) and the services to manage them.
> Also, "dimensions" will be shared by many star schemas and there  
> are some dimensions that are low level (the date and time dimensions).
> Wouldn't be easier to maintain them in an indipendent component?
>
> As a side note, here is the entity package layout I have in mind:
>
> org.ofbiz.olap
> org.ofbiz.olap.staging
> org.ofbiz.olap.dimensions (e.g. "Products", "Stores", "Dates"...)
> org.ofbiz.olap.facts (e.g. "Sales Transactions")
> org.ofbiz.olap.starschemas (mostly views with one fact and many  
> dimensions each; used by reporting tools)
> org.ofbiz.olap.starschemas.product
> org.ofbiz.olap.starschemas.manufacturing
> etc...
>
> Another option could be this:
>
> org.ofbiz.olap
> org.ofbiz.olap.staging
> org.ofbiz.olap.product.dimensions
> org.ofbiz.olap.product.facts
> org.ofbiz.olap.product.starschemas
> org.ofbiz.olap.manufacturing.dimensions
> org.ofbiz.olap.manufacturing.facts
> org.ofbiz.olap.manufacturing.starschemas
> etc...
>
> What do you think?
>
> Jacopo
>
> David E. Jones wrote:
>> Depending on what you do I'd say this should go straight into the  
>> product or order or whatever component, just like any reporting  
>> stuff would.
>> -David
>> On Feb 18, 2007, at 12:31 AM, Jacopo Cappellato wrote:
>>> If (but not guaranteed) I will start writing down something  
>>> concrete, should I wrap it in a new specialpurpose component and  
>>> commit (or put in Jira) it asap to get early feedback from the  
>>> community? If so, which name?
>>>
>>> Jacopo
>>>
>>> David E. Jones wrote:
>>>> Jacopo,
>>>> I like the idea of this approach, and it's something I've been  
>>>> thinking about for a while.
>>>> A good next step might be to do a PoC implementation with maybe  
>>>> just one star schema using a simple-method to do the ETL, and a  
>>>> form widget to report on the data. In that process we'll  
>>>> probably find some things that are ugly and could use some tool  
>>>> extensions.
>>>> BTW, there are some interesting generic star schemas in the Data  
>>>> Model Resource Book. There is a sales one with some requirements  
>>>> in the form of questions on page 370 of volume 1, and a model  
>>>> diagram on page 371.
>>>> The data warehouse entities should go into their own entity  
>>>> group, a new group, with it's own datasource in the OOTB  
>>>> entityengine.xml file. A good group name might be something like  
>>>> org.ofbiz.olap (as opposed to oltp which characterizes most of  
>>>> the current entities.
>>>> Anyway, yeah this would be cool, and we already have a lot of  
>>>> tools that would work really well here.
>>>> -David
>>>> On Feb 16, 2007, at 12:15 AM, Jacopo Cappellato wrote:
>>>>> Christopher,
>>>>>
>>>>> still nothing official or concrete, as far as I know.
>>>>> I know that Chris Howe did some integration tests with OpenI:
>>>>> http://docs.ofbiz.org/x/0AI
>>>>>
>>>>> I am seriously considering a different approach; while I'm  
>>>>> studying the book "The Datawarehouse Toolkit" (http://
>>>>> ofbiz.apache.org/documents.html), I'm trying to draft out a  
>>>>> proposal for the implementation of base datawarehousing  
>>>>> features in OFBiz (a separate set of entities for dimensions,  
>>>>> facts and start schemas; ETL services based on minilang, tools  
>>>>> to manage the dimensions tables and synchronization).
>>>>> You'll find some of my notes here:
>>>>> http://docs.ofbiz.org/x/2QI
>>>>> My goal is this: once we have a set of star schemas (facts and  
>>>>> dimensions) derived from OFBiz entities and based on best  
>>>>> practices, and the tools to manage the data in them, we could  
>>>>> integrate a visual reporting tool to run reports against them  
>>>>> (or just use, with some improvements the form widgets).
>>>>> It may seem an ambitious plan, but I think that many of the  
>>>>> building blocks to complete it are already in the framework,  
>>>>> we'll just have to improve and fine tune them.
>>>>> If you are interested in helping with this we could try to  
>>>>> create a work group for this...
>>>>>
>>>>> Jacopo
>>>>>
>>>>> Christopher Snow wrote:
>>>>>> Is there any work going on at the moment to build an ofbiz  
>>>>>> data warehouse and reporting infrastructure, for example the  
>>>>>> integration of the Pentaho toolset?
>>>>>> Many thanks ...
>>>>>> --This message has been scanned for viruses and
>>>>>> dangerous content by MailScanner, and is
>>>>>> believed to be clean.
>>>>>
>>>>>
>>>
>>>
>
>

[carolinajulie]

Business only runs smoothly if you work smartly. I have just started my business, and many things are new for me, which is very difficult, so I found my answer on the Dataromas website. On this website, you can see the top-rated portfolios where you can read multiples of articles related to business.

[sandy layton]

All that you want to be familiar with how to get more fit

There are numerous legends encompassing weight reduction. You could trust every one of the legends about weight reduction, regardless of whether you know reality. You might ask yourself " The number of calories that do I really want to work out?" This relies on how you eat and your work-out daily practice.
To decide the number of calories you that need to lose to accomplish your ideal weight, you should work out the calories you consume every day.

Work out with Rope
In your experience growing up, you could have played with a jumprope. This rope can be utilized to shed pounds. Work out with rope can be utilized related to push-ups or sit-ups. This is on the grounds that you will consume more muscle versus fat as it is required.

Climbing Steps
It will decrease hip fat in the event that you can climb steps every morning and night. It probably won't be appropriate for you relying upon your age and how much fat in your body. To rapidly work on your wellbeing, you will require explicit weight reduction gadgets.

Squats
Individuals frequently get confounded when they are overweight and don't know which one to do. The squat can be an incredible activity to assist you with adjusting your weight. It is not difficult to dominate and you will see the advantages in practically no time. It has been a well known home solution for weight reduction.
By https://www.healthclubservices.com