Search code examples
ssasolap

OLAP dimension for boolean, time, selective count

I have just started tinkering with MS SQL Analysis Services. For a start, I'm creating one cube from sales detail table. For the dimensions I have created ProductDim from product master table, LocationDim from location tables, and a CalendarDim.

However I'm stuck when trying to provide these data:

  • boolean: how do I let user filter active/inactive transactions? Should I create a dimension containing 2 values, TRUE and FALSE?
  • time: should I create a dimension containing 00:00:00 to 23:59:59 or should I merge time into my calendar dimension?
  • transaction count: one transaction can have many line items, there's line item id, and there's transaction id, how do I set the dimension so user can see transaction count? Because the count of the measure is line item count.
Solution

  • So, I've been reading about this quite a bit recently, and I will try to answer each one as much as theory suggests:

    1. For this, you should create something called 'junk' dimension: its basically a dimension with no attributes. http://en.wikipedia.org/wiki/Dimension_(data_warehouse)

    2. You probably don't want the time dimension merged with calendar. You'll end up storing way too many records. If your granularity is minute, then one day would be 24 * 60 = 1440 records. You have to decide how granular you want to go (per minute, per second??) And then store an entire days worth of time in a 'Time' dimension. So you fact tables will have two keys, one to your calendar dimension, and one to your 'time' dimension.

    3. Transaction count should be a 'measure', I think (no?). I assume you have transaction id repeated, because you have multiple line items per transaction. When you setup the measure, you can do 'distinct count' of transaction id.