Friday, February 13, 2009

From DMR to "Real" OLAP - part 2

There are a couple more things that came to my mind about the differences between using a DMR model and using PowerCubes.

Roll-up along different dimensions

DMR allows you to specify different roll-up functions along different dimensions.

e.g. a metric called DailySales would use SUM() to roll up along the branches dimension to give the total amount collected in sales on a day but would use AVERAGE() along the time dimension to give the average amount collected in sales per day during a week, month or year.

This doesn't work in PowerCubes. It's not supported.

My first thought was that the lack of custom roll-up functions is a pretty big deal... then I figured I can live happily wihtout them. In my experience I could do everything using SUM().
For an average calculation I ended up having two metrics and doing a little calculation in the reports.

e.g. continueing the above example you could have sales_total and days_count metrics. For days_count you don't even need a metric, you can just calculate it in the report - unless you want to exclude holidays, store closures things like that.


Sorting of members in a dimension

A huge lacking feature in DMR is that there is no guarantee on the order of members in a dimension. Functions like previousMember(), nextMember() are not supported, they will return whatever they want. There is no way to specify sorting of members.
This is supported in PowerCubes which makes using dimensional expressions much safer.

Please also see part 1 if you haven't yet...