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...

5 comments:

  1. If you use Oracle as your datasource you might consider using Oracle Olap as your Olap source. It combines the benefits of pre-aggregated data and dimensional functions with SQL query access.

    ReplyDelete
  2. Hi Snoekie

    unfortunatelly Oracle OLAP is not a supported datasource for Cognos. I wish it was...

    ReplyDelete
  3. Sorting of members in a dimension is supported in Cognos 8.4.

    ReplyDelete
  4. Thanks Agnes for the update!

    ReplyDelete
  5. The way a measure rolls up is a function of the measure, not the dimension. It would be chaos if the same measure aggregated differently when viewed under different dimensions - I know of no application this would make sense it. In your example of DailySales, the need for "average sales" during a week/month/year, is a DIFFERENT measure! Just create one called "Avg. Sales" and set it to aggregate as an average. That way people can see the total and the average, as needed.

    ReplyDelete