Thread: BI tools and postgresql

I am seeking suggestions for business intelligence and data mining tools compatible with postgresql. A new manager at a
client'sshop is leaning toward the MS offerings. I would like to be able to speak to the issue. 

TIA,

 - Mark

The combination of pandas ipython and psycopg2 work wonders for pulling data from db and manipulating/plotting,
although I don't know in more detail of what the client's use cases are.

Thank you. I will look into those projects. 

Hi Mark,
For me, Pentaho has delivered quite good results  in the past, and being an open source offering it would probably work OK with postgresql. Jasper reports also seems to have made it into BI space, and I would assume it'd work with postgresql too or so I recall. Both are open source projects/products with strong industry adoption, so if you're going to advice against MS tools, they'd be more likely to be considered as alternatives.

Regards
Seref

On Wed, Jul 25, 2012 at 1:41 PM, Mark Phillips
<mark.phillips@mophilly.com> wrote:
> I am seeking suggestions for business intelligence and data mining tools compatible with postgresql. A new manager at
aclient's shop is leaning toward the MS offerings. I would like to be able to speak to the issue. 
>

I'm in the same boat, and while it's not really my job, I'm looking
for two things:

1) a tool for power users to be able to get their own data.  There are
a few folks here who are smart enough to be trusted with direct access
to the database, but I don't want to ask them to learn SQL.  Something
like an open source version of the old Business Objects drag-n-drop
interface would be great.

2) I could also use a more traditional reporting tool to replace (or
augment) Crystal Reports.  Heck, I'd take an open-source version of
SQR if I could get it.

I've looked at Pentaho and Jasper, but I have not had much success.
(Can't remember exactly the issue right now, but I recall that they
seemed to be huge products and I could never quite figure out how to
do something simple with them.)

I'd be interested in other suggestions from the community, or if
people have some pointers to good newbie guides for Pentaho/Jasper,

-Chris

P.S.  If the open source tools are written in Python, so much the better :)


--
e-Mail is the equivalent of a postcard written in pencil.  This
message may not have been sent by me, or intended for you.  It may
have been read or even modified while in transit.  e-Mail disclaimers
have the same force in law as a note passed in study hall.  If your
corporate attorney says that you need an disclaimer in your signature,
you need a new corporate attorney.

I think simple requirements would fit reporting tools space better.
Another alternative worth looking into may be Eclipse BIRT





On 26 Jul 2012, at 13:18, Chris Curvey <chris@chriscurvey.com> wrote:

> On Wed, Jul 25, 2012 at 1:41 PM, Mark Phillips
> <mark.phillips@mophilly.com> wrote:
>> I am seeking suggestions for business intelligence and data mining tools compatible with postgresql. A new manager
ata client's shop is leaning toward the MS offerings. I would like to be able to speak to the issue. 
>>
>
> I'm in the same boat, and while it's not really my job, I'm looking
> for two things:
>
> 1) a tool for power users to be able to get their own data.  There are
> a few folks here who are smart enough to be trusted with direct access
> to the database, but I don't want to ask them to learn SQL.  Something
> like an open source version of the old Business Objects drag-n-drop
> interface would be great.
>
> 2) I could also use a more traditional reporting tool to replace (or
> augment) Crystal Reports.  Heck, I'd take an open-source version of
> SQR if I could get it.
>
> I've looked at Pentaho and Jasper, but I have not had much success.
> (Can't remember exactly the issue right now, but I recall that they
> seemed to be huge products and I could never quite figure out how to
> do something simple with them.)
>
> I'd be interested in other suggestions from the community, or if
> people have some pointers to good newbie guides for Pentaho/Jasper,
>
> -Chris
>
> P.S.  If the open source tools are written in Python, so much the better :)
>
>
> --
> e-Mail is the equivalent of a postcard written in pencil.  This
> message may not have been sent by me, or intended for you.  It may
> have been read or even modified while in transit.  e-Mail disclaimers
> have the same force in law as a note passed in study hall.  If your
> corporate attorney says that you need an disclaimer in your signature,
> you need a new corporate attorney.
>
> --
> Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general

On Thu, Jul 26, 2012 at 08:17:19AM -0400, Chris Curvey wrote:
> 1) a tool for power users to be able to get their own data.  There are
> a few folks here who are smart enough to be trusted with direct access
> to the database, but I don't want to ask them to learn SQL.  Something
> like an open source version of the old Business Objects drag-n-drop
> interface would be great.
>
> 2) I could also use a more traditional reporting tool to replace (or
> augment) Crystal Reports.  Heck, I'd take an open-source version of
> SQR if I could get it.

I don't know since I haven't used it yet, but Sofa looks rather promising:
http://www.sofastatistics.com/

Cheers,
Peter
--
http://sjamaan.ath.cx
--
"The process of preparing programs for a digital computer
 is especially attractive, not only because it can be economically
 and scientifically rewarding, but also because it can be an aesthetic
 experience much like composing poetry or music."
                            -- Donald Knuth

 Hi there,

> I've looked at Pentaho and Jasper, but I have not had much success.
> (Can't remember exactly the issue right now, but I recall that they
> seemed to be huge products and I could never quite figure out how to
> do something simple with them.)

 My advice here is to analyse the requirements and keep separated the BI
 components.

 Open source complete BI suites offered by BIRT, Pentaho and Jasper - to
 name a few - are really good products. I would suggest that - if you
 need to use the full product - you contact their sales representatives.
 Get more information, evaluate them, compare the products and pick one.

 Otherwise, I would simply look for specific products for each of the BI
 phases:

 * good ETL products are Kettle or Talend, both in Java
 * for reporting, I suggest Jasper report (with iReport), in Java again
 * for data mining you can look at Weka or, for in-database data mining
 algorithms in PostgreSQL, MADLib

 I am not sure if you need some tools for OLAP as well (I prefer to
 manage cubes and data marts within Postgres), you might want to look at
 Mondrian for Pentaho (Java).

 In my experience so far, in general the most common tools are for ETL
 and reporting. Data mining is less common (unfortunately), but in
 general it comes after the first two.

 All of the above technologies, require you to invest human resources
 and time in training.

 I prefer to see the BI framework as a set of components, rather than
 one single product. That gives you more flexibility and allows you to
 change a tool for a specific component in the future without changing
 the whole architecture.

 Of course, I take it for granted that you have already thought about
 the data warehouse layer (in case you have one).

 Cheers,
 Gabriele

--
  Gabriele Bartolini - 2ndQuadrant Italia
  PostgreSQL Training, Services and Support
  Gabriele.Bartolini@2ndQuadrant.it - www.2ndQuadrant.it

Le mercredi 25 juillet 2012 à 10:41 -0700, Mark Phillips a écrit :
> I am seeking suggestions for business intelligence and data mining tools compatible with postgresql.
> A new manager at a client's shop is leaning toward the MS offerings. I would like to be able to speak to the issue.

mmmmh..., I realize this will read like a wild exageration, but I
sincerely doubt anybody pushing this type of solution can be reasoned.

The money spent in licences alone would pay for scores of developpers to
produce any kind of reporting you will need many times over (the data
and its structure is what counts, reporting is easy if you have that)

And that is probably small potatoes compared to the hours that the users
will spend trying to have the mal-engineered thing output something.

My guess is that your battle is political : a strong ally would be
helpful.


--
Vincent Veyron
http://vincentveyron.com/
Logiciel de gestion des sinistres assurances et des dossiers contentieux pour le service juridique

Le jeudi 26 juillet 2012 à 08:17 -0400, Chris Curvey a écrit :

> 1) a tool for power users to be able to get their own data.  There are
> a few folks here who are smart enough to be trusted with direct access
> to the database, but I don't want to ask them to learn SQL.  Something
> like an open source version of the old Business Objects drag-n-drop
> interface would be great.

It's quite easy to build an exporting function with a web server, if you
can use that.

You can see one in action here :

https://as-pro.biz/clients/login?nom_utilisateur=demo-mfp&mot_de_passe=demo-mfp

Go into 'Statistiques' from the left menu.
Check any checkbox next to 'Tableaux' (Menu 1, Menu 2...)
Click on 'Envoyer'

You can download the data in csv format for each table displayed by
clicking into its header.

This is an Apache web server using mod_perl to connect to a postgresql
database. The module can be written in a day if you know the language
(you can use others), the apache web server has a learning curve at
first.

--
Vincent Veyron
http://marica.fr/
Logiciel de gestion des sinistres assurances et des dossiers contentieux pour le service juridique

On Thu, Jul 26, 2012 at 9:04 AM, Vincent Veyron <vv.lists@wanadoo.fr> wrote:
> Le mercredi 25 juillet 2012 à 10:41 -0700, Mark Phillips a écrit :
>> I am seeking suggestions for business intelligence and data mining tools compatible with postgresql.
>> A new manager at a client's shop is leaning toward the MS offerings. I would like to be able to speak to the issue.
>
> mmmmh..., I realize this will read like a wild exageration, but I
> sincerely doubt anybody pushing this type of solution can be reasoned.
>
> The money spent in licences alone would pay for scores of developpers to
> produce any kind of reporting you will need many times over (the data
> and its structure is what counts, reporting is easy if you have that)
>
> And that is probably small potatoes compared to the hours that the users
> will spend trying to have the mal-engineered thing output something.
>
> My guess is that your battle is political : a strong ally would be
> helpful.

Well, TBH, I wouldn't go that far.  SQL server has some very nice OLAP
extensions to SQL (PIVOT, UNPIVOT, ROLLUP, etc)
that postgres doesn't have. SQL Server is expensive, but the costs are
typically reasonable compared to paying for developers: in software
development labor costs are very substantial (that's why I love this
job, heh).   For an end to end BI stack microsoft is a decent choice
if (and only if) you're already heavily invested in the microsoft
platform; familiarity being the most important criteria.

In the same vein, Pentaho, BIRT, Jasper, etc are good choices if
you're a java shop.

(in my case, I'm doing almost 100% BI development now -- it's very
'hot' and I use 100% postgres, database driven, and it works great).

merlin

Le jeudi 26 juillet 2012 à 10:02 -0500, Merlin Moncure a écrit :

> job, heh).   For an end to end BI stack microsoft is a decent choice
> if (and only if) you're already heavily invested in the microsoft
> platform;

You're right. Because he posted on the list, I had assumed the OP _was_
using Postgres already, and that this was being challenged by the new
manager. Maybe they are not using Postgres yet.

> (in my case, I'm doing almost 100% BI development now -- it's very
> 'hot' and I use 100% postgres, database driven, and it works great).

Hence my rant about the impossibility to reason people who won't take
that kind of evidence :-)


--
Vincent Veyron

I think simple requirements would fit reporting tools space better.
Another alternative worth looking into may be Eclipse BIRT





On 26 Jul 2012, at 13:18, Chris Curvey <chris@chriscurvey.com> wrote:

> On Wed, Jul 25, 2012 at 1:41 PM, Mark Phillips
> <mark.phillips@mophilly.com> wrote:
>> I am seeking suggestions for business intelligence and data mining tools compatible with postgresql. A new manager
ata client's shop is leaning toward the MS offerings. I would like to be able to speak to the issue. 
>>
>
> I'm in the same boat, and while it's not really my job, I'm looking
> for two things:
>
> 1) a tool for power users to be able to get their own data.  There are
> a few folks here who are smart enough to be trusted with direct access
> to the database, but I don't want to ask them to learn SQL.  Something
> like an open source version of the old Business Objects drag-n-drop
> interface would be great.
>
> 2) I could also use a more traditional reporting tool to replace (or
> augment) Crystal Reports.  Heck, I'd take an open-source version of
> SQR if I could get it.
>
> I've looked at Pentaho and Jasper, but I have not had much success.
> (Can't remember exactly the issue right now, but I recall that they
> seemed to be huge products and I could never quite figure out how to
> do something simple with them.)
>
> I'd be interested in other suggestions from the community, or if
> people have some pointers to good newbie guides for Pentaho/Jasper,
>
> -Chris
>
> P.S.  If the open source tools are written in Python, so much the better :)
>
>
> --
> e-Mail is the equivalent of a postcard written in pencil.  This
> message may not have been sent by me, or intended for you.  It may
> have been read or even modified while in transit.  e-Mail disclaimers
> have the same force in law as a note passed in study hall.  If your
> corporate attorney says that you need an disclaimer in your signature,
> you need a new corporate attorney.
>
> --
> Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general

On 26/07/2012 15:04, Vincent Veyron wrote:
> The money spent in licences alone would pay for scores of developpers to
> produce any kind of reporting you will need many times over (the data
> and its structure is what counts, reporting is easy if you have that)

I disagree that licences will cover reporting costs...

I come from the other end of the spectrum. We provide reporting systems
for telco's banks etc. These are relatively stable, carefully designed
reports going to either a small number of high profile clients or
to a large number of end users.

When we installed a system for a UK telco some years ago it ended up
being the biggest outsourced print job in europe. It took up two
exchange trunks for data and ran a months reports iin a few days.

Initially the reporting hardware was three(ish) mid range sun desktops :-)

We dont "do" generic reporting systems - our target audience are
established complex and configurable reports that are run
periodically and take up lots of manula or system resources.

If the Op wants more detail I can pass provide $boss's email address
but as I say it is more of a niche reporting product.

Jacqui

p.s. I am interested in this thread as I have a clinet who has a larg(ish)
PG db and creates ad-hoc crosstab style reports. If I can find a tool he could
use  - he is a salesman and very non technical :-)

At the mo I am building reporting tables and using thsi to populate
crosstabs in openoffice using datapilot - crude but alot faster than
the existing solution which involves manually calcing each cell in
the crosstab :-/ openoffice replaces weeks of work with no more
that an hours report design/config.

SpagoBI provides extensive and integrated tools for BI/Reporting/Data
mining/Realtime BI/ Mobile BI/BAM all under Mozilla Public Licence.
The downside is that it's too big for simple reporting/dashboard needs
and a quite long learning curve.
http://www.spagoworld.org/xwiki/bin/view/SpagoBI/

HTH,
--Daoud.

On Fri, Jul 27, 2012 at 11:13 AM, Jacqui Caren
<jacqui.caren@ntlworld.com> wrote:
> On 26/07/2012 15:04, Vincent Veyron wrote:
>>
>> The money spent in licences alone would pay for scores of developpers to
>> produce any kind of reporting you will need many times over (the data
>> and its structure is what counts, reporting is easy if you have that)
>
>
> I disagree that licences will cover reporting costs...
>
> I come from the other end of the spectrum. We provide reporting systems
> for telco's banks etc. These are relatively stable, carefully designed
> reports going to either a small number of high profile clients or
> to a large number of end users.
>
> When we installed a system for a UK telco some years ago it ended up
> being the biggest outsourced print job in europe. It took up two
> exchange trunks for data and ran a months reports iin a few days.
>
> Initially the reporting hardware was three(ish) mid range sun desktops :-)
>
> We dont "do" generic reporting systems - our target audience are
> established complex and configurable reports that are run
> periodically and take up lots of manula or system resources.
>
> If the Op wants more detail I can pass provide $boss's email address
> but as I say it is more of a niche reporting product.
>
> Jacqui
>
> p.s. I am interested in this thread as I have a clinet who has a larg(ish)
> PG db and creates ad-hoc crosstab style reports. If I can find a tool he
> could
> use  - he is a salesman and very non technical :-)
>
> At the mo I am building reporting tables and using thsi to populate
> crosstabs in openoffice using datapilot - crude but alot faster than
> the existing solution which involves manually calcing each cell in
> the crosstab :-/ openoffice replaces weeks of work with no more
> that an hours report design/config.
>
>
> --
> Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general

Thanks to all who have posted. Your comments are very helpful.

Le samedi 28 juillet 2012 à 09:39 -0700, Mark Phillips a écrit :
> Thanks to all who have posted. Your comments are very helpful.
>
> On Jul 26, 2012, at 11:04 AM, Vincent Veyron wrote:
>
> > I had assumed the OP _was_
> > using Postgres already, and that this was being challenged by the
> > new
> > manager.
>
> Yes, that is accurate. The new manager is not familiar with OSS and
> PostgreSQL, having come from a large corporate environment into a
> smaller more lean organization.
>

I am in a similar situation with the application that you see in my sig
(it's a legal cases/insurance claims management system for the legal
department).

See below for some details.

>
> Thus my request here. I would like to offer the new manager an
> opportunity to extend the range of options.
>

Let me tell you briefly how it went for me :

The app has been in use for several years by a 3 person team, their
input being a great help in finalizing it. The manager in place was
sidelined in a takeover last year.

New manager comes in and starts talking about a bidding procedure for an
application for the combined team.

The guy is stopped by my 3 person team, who threatens to _strike_ if
'their' application is removed : they simply refused to work without it.
Bidding is halted, users keep the app, my bill is paid. I am still
hoping that new manager will see the light.

One year later, new manager has put restrictions all around them, and
they told me last week that they simply can't take the pressure anymore
and must protect themselves. I was not hoping anymore because I had
talked to the guy on the phone in the meantime, so I knew what to
expect.

It seems to me this is the kind of person you are dealing with : they
don't look at nor care about results. Because when you do care, you
don't come into a place and start replacing a functionning product,
specially not by what you mentionned.


--
Vincent Veyron
http://marica.fr/
Logiciel de gestion des sinistres assurances et des dossiers contentieux pour le service juridique

On 29 Jul 2012, at 11:56, Vincent Veyron wrote:

> New manager comes in and starts talking about a bidding procedure for an
> application for the combined team.
>
> The guy is stopped by my 3 person team, who threatens to _strike_ if
> 'their' application is removed : they simply refused to work without it.
> Bidding is halted, users keep the app, my bill is paid. I am still
> hoping that new manager will see the light.
>
> One year later, new manager has put restrictions all around them, and
> they told me last week that they simply can't take the pressure anymore
> and must protect themselves. I was not hoping anymore because I had
> talked to the guy on the phone in the meantime, so I knew what to
> expect.
>
> It seems to me this is the kind of person you are dealing with : they
> don't look at nor care about results. Because when you do care, you
> don't come into a place and start replacing a functionning product,
> specially not by what you mentionned.


I know the type. That's not a manager, that's a new alpha male marking his territory.

He's probably gone in a few years time to "advance his career", but by that time he'll have destroyed as much of what
hispredecessor accomplished as possible and replaced it with stuff with _his_ "mark", which is usually inferior because
he'snot an expert on those matters (after all, that's you). It's change for the sake of change, that's never a good
reason.

Guys like that are not good for the health of a company, but they tend to be quite good at sweetening up their
superiors.Worse, usually all the communication to those people tends to go through him. Unless your people find a way
tobypass him and convince upper management that he's no good (which will probably be difficult, as no doubt he's been
busycolouring their opinion of your people), you're screwed. The sooner they do that the better. 

If upper management is any good, they'll be happy to have a chat about the situation and keep an eye out and a direct
lineto your people open, so that they can hear both sides. He's probably not on their radar right now. 

Good luck.

Whether the same goes for the OP's situation I don't know. It's a lot of guessing either way, I am a techy as well, not
apsychologist or anything ;) 

Alban Hertroys

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

On 29/07/2012 10:56, Vincent Veyron wrote:
> New manager comes in and starts talking about a bidding procedure for an
> application for the combined team.

Hmm - I wonder...
All sorts of perks/backhanders for some types of management when this is
undertaken. I suspect this is an attempt to either get rid of
anyone he feels he cannot control and/or get a large wadge of goodies
from bidders.

Jacqui

Le dimanche 29 juillet 2012 à 12:47 +0200, Alban Hertroys a écrit :
> On 29 Jul 2012, at 11:56, Vincent Veyron wrote:

> If upper management is any good, they'll be happy to have a chat about the situation and keep an eye out and a direct
lineto your people open, so that they can hear both sides. He's probably not on their radar right now. 
>

Well, in that case upper management is made of elected officials (this
is an administration for a medium-sized city in France).

The way it works is :

-upper management somehow decides to budget a _large_(*) amount of money
for an application for the legal department. Budgeting always takes
places in december of year N-1, nothing can be put in after that.

-every year, a bidding procedure for some city takes place in may/june
of year N, and is won after a few meetings (always by the same company),
coincidentally a little less than the amount allotted six months before
that...

No bidding is needed for my application, which is priced at a tenth of
the average budget, and below the legal limit that triggers the
procedure. Strangely though, it appears strictly impossible to get a
couple thousand euros to test it in the service for a year.

So the alpha male is probably obeying his masters (the manager who took
my product and was sidelined is a union representative). It looks like I
am going to need luck, indeed ;-)

(* : average is 50 000 euros, with ~20 000 euros maintenance per year
for the foreseeable future + licences ; and for a crappy application I'm
told; larger cities will commend muuuch larger bills )


--
Vincent Veyron
http://vincentveyron.com/
Logiciel de gestion des sinistres assurances et des dossiers contentieux pour le service juridique