Open Source Pivot table (OLAP cube?)

[Laszlo Nagy]

Hi All,

I'm planning to create a new visual component for wxPython, for
designing, creating and printing pivot tables. Also known as: decision
cube, OLAP cube. I was searching on the internet for something similar,
but I could not find any open source version (or free to use, at least).
The only available free program was jmagallanes (
http://jmagallanes.sourceforge.net/en/ ) but it is a stand alone
application. I would like to create something that has Python bindings
(more preferably, written in Python and is extensible).

Before I start on this project, can you please confirm that there is no
such thing available? I do not want to work on something that is already
there.

Thanks,

Laszlo

 

[Jon Clements]

  Hi All,

I'm planning to create a new visual component for wxPython, for
designing, creating and printing pivot tables. Also known as: decision
cube, OLAP cube. I was searching on the internet for something similar,
but I could not find any open source version (or free to use, at least).
The only available free program was jmagallanes (http://jmagallanes.sourceforge.net/en/) but it is a stand alone
application. I would like to create something that has Python bindings
(more preferably, written in Python and is extensible).

Before I start on this project, can you please confirm that there is no
such thing available? I do not want to work on something that is already
there.

Thanks,

   Laszlo

Please note: this is not a direct answer to your question.

I would personally go for a client-server model; not worrying what the
server is written in or how it works. For some reason I have a scary
thought of your widget pulling in 100million records and cross-tabbing
two dimensions Don't worry about python bindings or such, why not
try and find something that can take an OLAP query, maintain
aggregates/computations on a DB that can possibly return in XML/JSON/
WHATEVER then just have your widget render visually the response and
send out XML/JSON/WHATEVER on click events.

Basically your widget is a renderer and UI to update the MDX/whatever
instructions in the OLAP server.

Of course, for small datasets, just nested dicts-of-dicts rendered
would probably do.

hth,

Jon.

 

[Laszlo Nagy]

Please note: this is not a direct answer to your question.

I would personally go for a client-server model; not worrying what the
server is written in or how it works. For some reason I have a scary
thought of your widget pulling in 100million records and cross-tabbing
two dimensions Don't worry about python bindings or such, why not
try and find something that can take an OLAP query, maintain
aggregates/computations on a DB that can possibly return in XML/JSON/
WHATEVER then just have your widget render visually the response and
send out XML/JSON/WHATEVER on click events.

Regarding the implementation, I was already thinking about separation of
the data query and the rendering part. Your answer was particularly
useful, because I was not thinking about MDX and a real client server
model. Until now.

Can you tell something about the licence of the MDX language? Can I just
make a new engine/library that uses MDX? Or do I need to develop a new
query language in order to make it open source?

It seems strange that several open source RDBMS exist (sqlite,
postgresql, mysql) but none of them support multi dimensional queries.
Is this a very difficult thing to implement? Even if we could only have
a not-so-efficient implementation that works with smaller datasets (100
000 records or so), it would be nice to have it.

Thanks

Laszlo

 

[Jon Clements]

Regarding the implementation, I was already thinking about separation of
the data query and the rendering part. Your answer was particularly
useful, because I was not thinking about MDX and a real client server
model. Until now.

Can you tell something about the licence of the MDX language? Can I just
make a new engine/library that uses MDX? Or do I need to develop a new
query language in order to make it open source?

IIRC it was MS' "analysis services", but it seems so wide spread --
I'm not sure if there's an ISO standard on it or anything...
I'm not a solicitor/lawyer-- GIYF.

It seems strange that several open source RDBMS exist (sqlite,
postgresql, mysql) but none of them support multi dimensional queries.

Not strange at all! When was MD part of the SQL standard?

One method would be to create a load of triggers that keep summary
info.
Depends on the DB and other stuff... Although then you're just
duplicating
work, and making the DB store meta-data, when something else should
be
doing that for it (in another DB maybe )

Is this a very difficult thing to implement? Even if we could only have
a not-so-efficient implementation that works with smaller datasets (100
000 records or so), it would be nice to have it.

Implement properly for small things (or at least acceptability),
probably not. To do it properly
waste your time and everyone else's -- oh yes.

Let's put it this way -- would you write your own RDBMS from scratch?

It also depends if you want "immediate" information, or if you're
happy for the queried DB to be slightly out of sync with the main DB.
So many options -- but I think this is getting OT for the python list.

Thanks

   Laszlo

Again, my humble opinion,

Jon.