Hitachi Consulting BI Blog

Dimensions and sorcerer’s brooms

When building a cube in AMO (or the XML equivalent, ASSL), sometimes it feels like you’re copying a dimension every time you turn around. You start with a dimension (that is, a database dimension). Then you don’t just add it to the cube, you create a cube dimension that references it. And then you don’t just use that in a measure group, instead, you create a measure group dimension. It’s like poor Mickey Mouse and his sorcerer’s broom: they keep multiplying.

Well, I finally worked through why there’s a new object at each layer, and it really does help to see what each one adds that’s not there in the more general version.

• Database dimension: this is where you really define the dimension and create all the attributes.
• Cube dimension: this adds the fact that a cube can have multiple instances of the same dimension—the classic example is Ship Date, Due Date, Order Date. The point is that you don’t have to create three different dimensions. You define the dimension only once and use it three times, and the cube dimension is where you give them unique names.
•  Measure Group dimension: this adds the fact that the same dimension can have a different granularity attribute with different fact tables—the classic example is Orders at the Daily grain but Budget at the Monthly grain. You need a place to identify what the “leaf” level of the dimension should be for this particular fact table, and also tell which fact table column it should join to (which further supports the multiple instances of the dimension).
• Aggregation Design dimension: this allows you to override the global row counts for attributes in the context of one aggregation. Basically, it lets you speak out of both sides of your mouth (not to say “lie”) with different aggregation designs—in one, you might say an attribute has 10 members, and in another you might say the attribute has 10000 members.

So I feel better now. Just thinking through the different versions made it clear that there really weren’t 400 different variations of one dimension. (Although there are more than 4 :-).

Reed