Обсуждение: OLAP

Hi Alban,

I think Postgres works great for OLAP work, and Amazon's Red Shift is
even based on Postgres. 100 million sales should be not problem at
all. My understanding is Greenplum also builds on top of Postgres, so
if you ever do outgrow your Postgres installation, that would be an
easy migration path. One Postgres OLAP tool to consider is Pentaho.
That will save you lots of time around ETL, ad-hoc reporting, and
other standard OLAP functionality.

Good luck!
Paul



On Tue, Aug 27, 2013 at 8:12 AM, Alban Hertroys <haramrae@gmail.com> wrote:
> Hi all,
>
> At work we have a system that's built on top of a proprietary OLAP database
> system (Rocket Gentia). We're looking into replacing that system with
> something that's still supported and in such a way that we can also access
> the data from our reporting software (WebFOCUS by information builders).
>
> Because I'm always advocating PG, I was asked whether PG would be suitable
> for this, but I'm not really familiar enough with OLAP databases to be able
> to comment on that.
>
> I got three prerequisites for a solution, namely:
> 1. It must contain correct information,
> 2. It must be fast and
> 3. It must be easy to maintain the data and the models; that's a task for a
> 3rd party back-end application, but it would be helpful to be able to name
> something to the higher-ups.
>
> Next to that, because we're also going to access the system using our
> reporting software (which is read-only access), it would be best if the
> entire data model and all the business rules are stored inside the database
> so that we're looking at the data in the same way that the "back-end" sees
> it.
>
> For size, we're looking at about 20 years of sales and shipment data all
> over the world (although mostly in Europe) for about 5mln sold products per
> year.
>
> I suspect there might be some "middleware" that handles the models and
> dimensions and stuff and manages triggers on relational tables in PG or a
> setup like that.
> I've seen an old reference to "Cybertec OLAP", but they don't seem to carry
> a product like that if I watch their site.
>
> I'm looking for suggestions for something that would be able to do this.
>
> Cheers,
> Alban.
> --
> If you can't see the forest for the trees,
> Cut the trees and you'll see there is no forest.



--
_________________________________
Pulchritudo splendor veritatis.

On Aug 27, 2013, at 19:07, Paul Jungwirth <pj@illuminatedcomputing.com> wrote:

> Hi Alban,
>
> I think Postgres works great for OLAP work

What do you base that on?
I don't really doubt that the database layer is up to the task, I'm much more worried about maintaining the model and
thecube data and all that typical OLAP stuff that I've mostly just heard about. 

> , and Amazon's Red Shift is
> even based on Postgres. 100 million sales should be not problem at
> all. My understanding is Greenplum also builds on top of Postgres, so
> if you ever do outgrow your Postgres installation, that would be an
> easy migration path.

What's the benefit of GreenPlum for OLAP? Isn't it a columnar database? And a pretty old fork of Postgres at that?
GreenPlum has a pretty steep price-tag too.

I didn't really look into Red Shift, perhaps I should…

Anyway, I'm not at all sure I want to use some product that's heavily modified from Postgres. If it is, it has to be
reallyreally good. 

> One Postgres OLAP tool to consider is Pentaho.
> That will save you lots of time around ETL, ad-hoc reporting, and
> other standard OLAP functionality.

How is Pentaho an OLAP tool? Aren't you mixing up a few things?
We already use Pentaho for ETL, so I'm a bit familiar with it. Why do you consider it suitable for managing an OLAP
database?

How would Pentaho manage cube rollup triggers, business models, dimensions and stuff like that?
We don't want to hand code those, that's far too error-prone and far too much work to keep track of. That stuff needs
tobe automated, preferably similar to what we're used to from Gentia (well, not me - I can't make heads or tails of
Gentia,but the person who asked me about PG's suitability has been developing with it for years). That's what we're
comparingto. 

Unfortunately, I can't find any decent information about Gentia for reference. Apparently these days they're all about
NoSQLdatabases and such. That's not what we have - I guess the clunky GUI is a hint that it's something of the past... 


BTW, please don't top-post.


> On Tue, Aug 27, 2013 at 8:12 AM, Alban Hertroys <haramrae@gmail.com> wrote:
>> Hi all,
>>
>> At work we have a system that's built on top of a proprietary OLAP database
>> system (Rocket Gentia). We're looking into replacing that system with
>> something that's still supported and in such a way that we can also access
>> the data from our reporting software (WebFOCUS by information builders).
>>
>> Because I'm always advocating PG, I was asked whether PG would be suitable
>> for this, but I'm not really familiar enough with OLAP databases to be able
>> to comment on that.
>>
>> I got three prerequisites for a solution, namely:
>> 1. It must contain correct information,
>> 2. It must be fast and
>> 3. It must be easy to maintain the data and the models; that's a task for a
>> 3rd party back-end application, but it would be helpful to be able to name
>> something to the higher-ups.
>>
>> Next to that, because we're also going to access the system using our
>> reporting software (which is read-only access), it would be best if the
>> entire data model and all the business rules are stored inside the database
>> so that we're looking at the data in the same way that the "back-end" sees
>> it.
>>
>> For size, we're looking at about 20 years of sales and shipment data all
>> over the world (although mostly in Europe) for about 5mln sold products per
>> year.
>>
>> I suspect there might be some "middleware" that handles the models and
>> dimensions and stuff and manages triggers on relational tables in PG or a
>> setup like that.
>> I've seen an old reference to "Cybertec OLAP", but they don't seem to carry
>> a product like that if I watch their site.
>>
>> I'm looking for suggestions for something that would be able to do this.
>>
>> Cheers,
>> Alban.
>> --
>> If you can't see the forest for the trees,
>> Cut the trees and you'll see there is no forest.
>
>
>
> --
> _________________________________
> Pulchritudo splendor veritatis.

Alban Hertroys
--
If you can't see the forest for the trees,
cut the trees and you'll find there is no forest.

Alban Hertroys <haramrae@gmail.com> writes:

> On Aug 27, 2013, at 19:07, Paul Jungwirth <pj@illuminatedcomputing.com> wrote:
>
>> Hi Alban,
>>
>> I think Postgres works great for OLAP work
>
> What do you base that on?
> I don't really doubt that the database layer is up to the task, I'm much more worried about maintaining the model and
thecube data and all that typical OLAP stuff that I've mostly just heard about. 
>
>> , and Amazon's Red Shift is
>> even based on Postgres. 100 million sales should be not problem at
>> all. My understanding is Greenplum also builds on top of Postgres, so
>> if you ever do outgrow your Postgres installation, that would be an
>> easy migration path.
>
> What's the benefit of GreenPlum for OLAP? Isn't it a columnar database? And a pretty old fork of Postgres at that?
> GreenPlum has a pretty steep price-tag too.

Vertica is another case of an analytics focused platform that dirived
from Postgres, version 8.0 IIRC.

It was, by the time I first looked at it back about 4 years ago,  only
superficially  resembling Postgres.  Performance was absolutely
shocking in terms of how quickly it processed queries over certain
kinds of data... and for which the set of expected queries to be run
over same was identifiable in advance.

Sample queries are given to a moddeler which in turn creates a set of
"projections" which are physical manifestations of the backend storage
intended to optimize for this specialized workload.

Vertica and I presume Green Plumb are *not* well suited for an OLTP
role so it takes a fair amount of learning to make good use of them.

Just FWIW.

> I didn't really look into Red Shift, perhaps I should…
>
> Anyway, I'm not at all sure I want to use some product that's heavily modified from Postgres. If it is, it has to be
reallyreally good. 
>
>> One Postgres OLAP tool to consider is Pentaho.
>> That will save you lots of time around ETL, ad-hoc reporting, and
>> other standard OLAP functionality.
>
> How is Pentaho an OLAP tool? Aren't you mixing up a few things?
> We already use Pentaho for ETL, so I'm a bit familiar with it. Why do you consider it suitable for managing an OLAP
database?
>
> How would Pentaho manage cube rollup triggers, business models, dimensions and stuff like that?
> We don't want to hand code those, that's far too error-prone and far too much work to keep track of. That stuff needs
tobe automated, preferably similar to what we're used to from Gentia (well, not me - I can't make heads or tails of
Gentia,but the person who asked me about PG's suitability has been developing with it for years). That's what we're
comparingto. 
>
> Unfortunately, I can't find any decent information about Gentia for reference. Apparently these days they're all
aboutNoSQL databases and such. That's not what we have - I guess the clunky GUI is a hint that it's something of the
past...
>
>
> BTW, please don't top-post.
>
>
>> On Tue, Aug 27, 2013 at 8:12 AM, Alban Hertroys <haramrae@gmail.com> wrote:
>>> Hi all,
>>>
>>> At work we have a system that's built on top of a proprietary OLAP database
>>> system (Rocket Gentia). We're looking into replacing that system with
>>> something that's still supported and in such a way that we can also access
>>> the data from our reporting software (WebFOCUS by information builders).
>>>
>>> Because I'm always advocating PG, I was asked whether PG would be suitable
>>> for this, but I'm not really familiar enough with OLAP databases to be able
>>> to comment on that.
>>>
>>> I got three prerequisites for a solution, namely:
>>> 1. It must contain correct information,
>>> 2. It must be fast and
>>> 3. It must be easy to maintain the data and the models; that's a task for a
>>> 3rd party back-end application, but it would be helpful to be able to name
>>> something to the higher-ups.
>>>
>>> Next to that, because we're also going to access the system using our
>>> reporting software (which is read-only access), it would be best if the
>>> entire data model and all the business rules are stored inside the database
>>> so that we're looking at the data in the same way that the "back-end" sees
>>> it.
>>>
>>> For size, we're looking at about 20 years of sales and shipment data all
>>> over the world (although mostly in Europe) for about 5mln sold products per
>>> year.
>>>
>>> I suspect there might be some "middleware" that handles the models and
>>> dimensions and stuff and manages triggers on relational tables in PG or a
>>> setup like that.
>>> I've seen an old reference to "Cybertec OLAP", but they don't seem to carry
>>> a product like that if I watch their site.
>>>
>>> I'm looking for suggestions for something that would be able to do this.
>>>
>>> Cheers,
>>> Alban.
>>> --
>>> If you can't see the forest for the trees,
>>> Cut the trees and you'll see there is no forest.
>>
>>
>>
>> --
>> _________________________________
>> Pulchritudo splendor veritatis.
>
> Alban Hertroys
> --
> If you can't see the forest for the trees,
> cut the trees and you'll find there is no forest.
>
>
>
> --
> Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
>

--
Jerry Sievers
Postgres DBA/Development Consulting
e: postgres.consulting@comcast.net
p: 312.241.7800

Dne 28. 8. 2013 0:05 "Jerry Sievers" <gsievers19@comcast.net> napsal(a):
>
> Alban Hertroys <haramrae@gmail.com> writes:
>
> > On Aug 27, 2013, at 19:07, Paul Jungwirth <pj@illuminatedcomputing.com> wrote:
> >
> >> Hi Alban,
> >>
> >> I think Postgres works great for OLAP work
> >
> > What do you base that on?
> > I don't really doubt that the database layer is up to the task, I'm much more worried about maintaining the model and the cube data and all that typical OLAP stuff that I've mostly just heard about.
> >
> >> , and Amazon's Red Shift is
> >> even based on Postgres. 100 million sales should be not problem at
> >> all. My understanding is Greenplum also builds on top of Postgres, so
> >> if you ever do outgrow your Postgres installation, that would be an
> >> easy migration path.
> >
> > What's the benefit of GreenPlum for OLAP? Isn't it a columnar database? And a pretty old fork of Postgres at that?
> > GreenPlum has a pretty steep price-tag too.
>
> Vertica is another case of an analytics focused platform that dirived
> from Postgres, version 8.0 IIR

vertica use a similar interface, but internally use nothing from pg. it was written from zero.

> It was, by the time I first looked at it back about 4 years ago,  only
> superficially  resembling Postgres.  Performance was absolutely
> shocking in terms of how quickly it processed queries over certain
> kinds of data... and for which the set of expected queries to be run
> over same was identifiable in advance.
>
> Sample queries are given to a moddeler which in turn creates a set of
> "projections" which are physical manifestations of the backend storage
> intended to optimize for this specialized workload.
>
> Vertica and I presume Green Plumb are *not* well suited for an OLTP
> role so it takes a fair amount of learning to make good use of them.
>
> Just FWIW.
>
> > I didn't really look into Red Shift, perhaps I should…
> >
> > Anyway, I'm not at all sure I want to use some product that's heavily modified from Postgres. If it is, it has to be really really good.
> >
> >> One Postgres OLAP tool to consider is Pentaho.
> >> That will save you lots of time around ETL, ad-hoc reporting, and
> >> other standard OLAP functionality.
> >
> > How is Pentaho an OLAP tool? Aren't you mixing up a few things?
> > We already use Pentaho for ETL, so I'm a bit familiar with it. Why do you consider it suitable for managing an OLAP database?
> >
> > How would Pentaho manage cube rollup triggers, business models, dimensions and stuff like that?
> > We don't want to hand code those, that's far too error-prone and far too much work to keep track of. That stuff needs to be automated, preferably similar to what we're used to from Gentia (well, not me - I can't make heads or tails of Gentia, but the person who asked me about PG's suitability has been developing with it for years). That's what we're comparing to.
> >
> > Unfortunately, I can't find any decent information about Gentia for reference. Apparently these days they're all about NoSQL databases and such. That's not what we have - I guess the clunky GUI is a hint that it's something of the past...
> >
> >
> > BTW, please don't top-post.
> >
> >
> >> On Tue, Aug 27, 2013 at 8:12 AM, Alban Hertroys <haramrae@gmail.com> wrote:
> >>> Hi all,
> >>>
> >>> At work we have a system that's built on top of a proprietary OLAP database
> >>> system (Rocket Gentia). We're looking into replacing that system with
> >>> something that's still supported and in such a way that we can also access
> >>> the data from our reporting software (WebFOCUS by information builders).
> >>>
> >>> Because I'm always advocating PG, I was asked whether PG would be suitable
> >>> for this, but I'm not really familiar enough with OLAP databases to be able
> >>> to comment on that.
> >>>
> >>> I got three prerequisites for a solution, namely:
> >>> 1. It must contain correct information,
> >>> 2. It must be fast and
> >>> 3. It must be easy to maintain the data and the models; that's a task for a
> >>> 3rd party back-end application, but it would be helpful to be able to name
> >>> something to the higher-ups.
> >>>
> >>> Next to that, because we're also going to access the system using our
> >>> reporting software (which is read-only access), it would be best if the
> >>> entire data model and all the business rules are stored inside the database
> >>> so that we're looking at the data in the same way that the "back-end" sees
> >>> it.
> >>>
> >>> For size, we're looking at about 20 years of sales and shipment data all
> >>> over the world (although mostly in Europe) for about 5mln sold products per
> >>> year.
> >>>
> >>> I suspect there might be some "middleware" that handles the models and
> >>> dimensions and stuff and manages triggers on relational tables in PG or a
> >>> setup like that.
> >>> I've seen an old reference to "Cybertec OLAP", but they don't seem to carry
> >>> a product like that if I watch their site.
> >>>
> >>> I'm looking for suggestions for something that would be able to do this.
> >>>
> >>> Cheers,
> >>> Alban.
> >>> --
> >>> If you can't see the forest for the trees,
> >>> Cut the trees and you'll see there is no forest.
> >>
> >>
> >>
> >> --
> >> _________________________________
> >> Pulchritudo splendor veritatis.
> >
> > Alban Hertroys
> > --
> > If you can't see the forest for the trees,
> > cut the trees and you'll find there is no forest.
> >
> >
> >
> > --
> > Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
> > To make changes to your subscription:
> > http://www.postgresql.org/mailpref/pgsql-general
> >
>
> --
> Jerry Sievers
> Postgres DBA/Development Consulting
> e: postgres.consulting@comcast.net
> p: 312.241.7800
>
>
> --
> Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general

Alban Hertroys-4 wrote
> How is Pentaho an OLAP tool? Aren't you mixing up a few things?
> We already use Pentaho for ETL, so I'm a bit familiar with it. Why do you
> consider it suitable for managing an OLAP database?
>
> How would Pentaho manage cube rollup triggers, business models, dimensions
> and stuff like that?
> We don't want to hand code those, that's far too error-prone and far too
> much work to keep track of. That stuff needs to be automated, preferably
> similar to what we're used to from Gentia (well, not me - I can't make
> heads or tails of Gentia, but the person who asked me about PG's
> suitability has been developing with it for years). That's what we're
> comparing to.

Pentaho, Jasper and Actuate are the common OLAP tools. Pentahos' ETL tool
(Kettle) is more popular than their Reporting solution. Similarly, Jasper's
reporting tool is more popular than their ETL (they integrate talend). The
OLAP functionality in both come from Mondrian, as far as I know.
The cubes, dimensions etc can be defined using a UI tool and uploaded to the
reporting "server" - either Jasper or Pentaho. These typically support MDX,
in addition to standard SQL.
http://en.wikipedia.org/wiki/MultiDimensional_eXpressions



--
View this message in context: http://postgresql.1045698.n5.nabble.com/OLAP-tp5768698p5768782.html
Sent from the PostgreSQL - general mailing list archive at Nabble.com.