Python / Django : emulating a multidimensional layer on a MySQL database

Question

Hi,

I'm working on a Django project where I need to provide a lot of different visualizations on the same data (for example average of a value for each month, for each year / for a location, etc...).

I have been using an OLAP database once in college, and I thought that it would fit my needs, but it appears that it is much too heavy for what I need. Actually the volume of data is not very big, so I don't need any optimization, just a way to present different visualizations of the same data without having to write 1000 times the same code.

So, to recap, I need a python library:

• to emulate a multidimensional database (OLAP style would be nice because I think it is quite convenient : star structure, and everything)
• non-intrusive, because I can't modify anything on the existing MySQL database
• easy-to-use, because otherwise there's no point in replacing some overhead by another.

Answer 1

Why not just use the standard ORM aggregation functions: http://docs.djangoproject.com/en/dev/topics/db/aggregation/

Wherever you think performance is going to be a hit, you can denormalize that field.

Answer 2

You have Python defaultdict dictionaries.

If your data is small, simply query all of it and load up a bunch of dictionaries with counts and sums and what-not.

A "star schema" is just a poor-person's inverted database where dimensions (i.e., dictionaries) reference lists of rows. You can summarize those lists of rows to create summary dictionaries.

d1_sum= defaultdict( int )
d1_count= defaultdict( count )
d2_sum = defaultdict( int )
for row in MyFactTable.objects.all():
    d1_sum[row.attr1] += row.fact1
    d1_count[row.attr1] += 1
    d2_sum[row.attr2] += some_function( row.fact2 )
    etc.

Answer 3

Ok ... I finally came up with my own solution ( https://code.google.com/p/django-cube/ ), because I couldn't find what I wanted.

With a model like this:

class Instrument(models.Model):
    name = models.CharField(max_length=100)

class Musician(models.Model):
    firstname = models.CharField(max_length=100)
    instrument = models.ForeignKey(Instrument)

Create a cube:

>>> c = Cube(['instrument__name', 'firstname'], Musician.objects.all(), len)
... #Cube(dimensions, queryset, aggregation_function)
... #You can use the Django field-lookup syntax for dates and foreign keys !!!

Query the cube along one (or several) dimension(s):

>>> c.measure_dict('firstname', 'instrument__name', full=False) == {
...     'Miles': {
...         'trumpet': {'measure': 1},
...         'sax': {'measure': 0},
...         'piano': {'measure': 0},
...     },
...     'John': {
...         'trumpet': {'measure': 0},
...         'sax': {'measure': 1},
...         'piano': {'measure': 4},
...     },
... }

Use the custom template tags, etc ...