Replacing Aggregate dimension with Conformed base dimension at lowest level.

I have a dilemma about aggregate dimension. Say in a product dimension, I have denormalised attributes for category. Now I have a target fact set on category level. Should I create an aggregate dimension and have SK in the target fact, or I can just nominate a product in the corresponding category from product dimension and use the product SK in the target fact so that I don't need to create any aggregate dimension at all.

The same argument would apply to the aggregated fact table on the category level, or any other attribute level above the product. If I have to create an aggregate dimension for any high level fact, I would end up too many small aggregate dimensions, while I could just let high level fact tables share a single conformed dimension at lowest level. Performance may be a concern for a monster dimension. But with bitmap indexing, the performance alone may not justify separate aggregate dimensions. Is there any critical issue with single flattened dimension to cater for the fact tables at different levels, particularly in terms of the impact caused by different types of SCD.

You don't want to use an arbitrary product. It is too misleading.

If you want to aggregate to some other level, there really isn't much choice other than creating an appropriate dimension. The alternative is to just aggregate to product and leave it at that. Depending on your platform, performance and the nature of the aggregate, just bringing it to product will allow you to support queries on any combination of product attributes. And, if you are aggregating a very detailed fact, it may be sufficient enough to give reasonable performance for the anticipated queries.

ngalemmo wrote:You don't want to use an arbitrary product. It is too misleading.

Is that what we normally do with date dimension, using the first/last day of the month to represent the whole month in a monthly budget fact table. By the same rational, we could use the smallest product SK in the category of the product dimension to represent the category. You could create views to avoid the confusion to users.

The only difference is that the date dimension does not have SCD implication and product does in terms of hierarchical change. My biggest concern with single conformed dimension approach is type 1 change on any involving attributes. However if we enforce type 2 on any attribute that potentially could act as aggregate dimension in high level fact, I could not see why we can't just have a single conformed dimension.

I understand the aggregation should stop at certain level where performance is not critical for further aggregation. However in my case, it's mainly about the target or budget fact that can only be set at higher level.

Looks like both approaches will work. The aggregate dimension may have performance advantage, but given the new technology on bitmap indexing, the performance concern may have disappeared. If we can avoid extra maintenance work on those smallish aggregate dimensions by referring to just base dimensions, the concept of aggregate/shrunken dimensions may not be that important. I am not fully confident about single base dimension approach, but can't justify the otherwise.

It is not at all like a date dimension. In a date dimension the roles and usage are clear. If someone designates a 'month' role, it is clear which attributes are applicable to the role. In the case of product, this is not clear. If you have a dozen or more attributes, which ones are applicable to the role assigned? You could extend the concept and use the same product dimension for different types of aggregate using different combinations of attributes. It gets real confusing real fast. Besides, what do you do if the chosen product row is updated and no longer contains the values appropriate to the aggregate? This is not an issue with dates.