data/olap cubes

[Adam Heath-2]

Has anyone had any dealings with using data/olap cubes with ofbiz?
I've recently had some thoughts on how to maintain such data
structures automatically.

This is somewhat of a brainstorm, but this is what I'm thinking.

Define the cube with a dynamic view entity(possibly using my sql
representation).  Have this dve serialized to a DataCubeDescriptor
table.  Then, during ofbiz startup, it would read from
DataCubeDescriptor, auto-create any missing summary tables, then add
dynamic reverse ecas, so that any time the underlying data is
modified, the summary table would be auto-updated with new data.

This would allow data cube querying to be instantaneous, because it
would just be dumping the summary table's contents.

Does anyone else understand what I am describing?  Is anyone else
interested?

[Vince Clark]

Adam - great topic. Thanks for raising it.

I would like to suggest that you look at how Mondrian defines cubes. Not sure how we could incorporate that into the Entity Engine but two of the major open source BI products, Jasper and Pentaho, use Mondrian. So it would be nice if we could give users the option of using whatever is built in to OFBiz, and also use existing solutions.

Vince Clark
[hidden email]
[phone]

----- Original Message -----
From: "Adam Heath" <[hidden email]>
To: [hidden email]
Sent: Wednesday, July 29, 2009 10:14:02 AM GMT -07:00 US/Canada Mountain
Subject: data/olap cubes

Has anyone had any dealings with using data/olap cubes with ofbiz?
I've recently had some thoughts on how to maintain such data
structures automatically.

This is somewhat of a brainstorm, but this is what I'm thinking.

Define the cube with a dynamic view entity(possibly using my sql
representation).  Have this dve serialized to a DataCubeDescriptor
table.  Then, during ofbiz startup, it would read from
DataCubeDescriptor, auto-create any missing summary tables, then add
dynamic reverse ecas, so that any time the underlying data is
modified, the summary table would be auto-updated with new data.

This would allow data cube querying to be instantaneous, because it
would just be dumping the summary table's contents.

Does anyone else understand what I am describing?  Is anyone else
interested?

[Brett]

Adam,

I am interested in your OLAP ideas.  I've haven't used the existing ofbiz
olap tables directly, but I have used them to model new Star schemas.  I
capture a lot of performance testing data and needed a way to query and
report the results.

A lot of the work in data mining is breaking down the data to work
appropriately in a start schema topology.  Creating a standard way to do
this would be very helpful.  I like the idea of a dynamic view.

My approach to date has been the following:

- Define Fact Table and reuse DimensionTables as much as possible
- Create common Java utils to handle dimension table look up from a flat
file of imported data
- Create Groovy scripts to tie the Java utils and file parsing together

I have also considered just using a common Fact Table entity with just
generic column names and types to capture most common data imports.  Then
use a meta table dimension table to define the columns used for the
particular fact.

If anyone has thoughts on a generic approach to data mining I would
appreciate the feedback.

Thanks Adam for bringing up the topic.


Brett



On Wed, Jul 29, 2009 at 10:14 AM, Adam Heath <[hidden email]> wrote:

> Has anyone had any dealings with using data/olap cubes with ofbiz?
> I've recently had some thoughts on how to maintain such data
> structures automatically.
>
> This is somewhat of a brainstorm, but this is what I'm thinking.
>
> Define the cube with a dynamic view entity(possibly using my sql
> representation).  Have this dve serialized to a DataCubeDescriptor
> table.  Then, during ofbiz startup, it would read from
> DataCubeDescriptor, auto-create any missing summary tables, then add
> dynamic reverse ecas, so that any time the underlying data is
> modified, the summary table would be auto-updated with new data.
>
> This would allow data cube querying to be instantaneous, because it
> would just be dumping the summary table's contents.
>
> Does anyone else understand what I am describing?  Is anyone else
> interested?
>

[Bob Morley]

We are trying to fill our reporting needs by leveraging OLAP in Ofbiz.  Here is our original approach:

- Expand upon the existing fact and dimension definitions
- Enhance the view-entity to support a "materialize" attribute -- this attribute would cause a table to be created to represent the view-entity and dynamically create EECAs on the underlying entities.
- Integrate Mondrian (OLAP4J?) - MDX along with performance and caching
- Integrate presentation tools - something similar to OpenI and/or Jasper,JFreeChart,JPivot,etc

Not sure how well this would line-up with what the community would want to do in general, but I suspect the first and perhaps second would be a good fit.

We also talked about properly supporting hierarchical dimensions.

Thoughts?

Brett Palmer wrote

Adam,

I am interested in your OLAP ideas.  I've haven't used the existing ofbiz
olap tables directly, but I have used them to model new Star schemas.  I
capture a lot of performance testing data and needed a way to query and
report the results.

A lot of the work in data mining is breaking down the data to work
appropriately in a start schema topology.  Creating a standard way to do
this would be very helpful.  I like the idea of a dynamic view.

My approach to date has been the following:

- Define Fact Table and reuse DimensionTables as much as possible
- Create common Java utils to handle dimension table look up from a flat
file of imported data
- Create Groovy scripts to tie the Java utils and file parsing together

I have also considered just using a common Fact Table entity with just
generic column names and types to capture most common data imports.  Then
use a meta table dimension table to define the columns used for the
particular fact.

If anyone has thoughts on a generic approach to data mining I would
appreciate the feedback.

Thanks Adam for bringing up the topic.


Brett



On Wed, Jul 29, 2009 at 10:14 AM, Adam Heath <[e-mail]> wrote:

> Has anyone had any dealings with using data/olap cubes with ofbiz?
> I've recently had some thoughts on how to maintain such data
> structures automatically.
>
> This is somewhat of a brainstorm, but this is what I'm thinking.
>
> Define the cube with a dynamic view entity(possibly using my sql
> representation).  Have this dve serialized to a DataCubeDescriptor
> table.  Then, during ofbiz startup, it would read from
> DataCubeDescriptor, auto-create any missing summary tables, then add
> dynamic reverse ecas, so that any time the underlying data is
> modified, the summary table would be auto-updated with new data.
>
> This would allow data cube querying to be instantaneous, because it
> would just be dumping the summary table's contents.
>
> Does anyone else understand what I am describing?  Is anyone else
> interested?
>