Replacing Aggregate dimension with Conformed base dimension at lowest level.

View previous topic View next topic Go down

Replacing Aggregate dimension with Conformed base dimension at lowest level.

Post  hang on Thu Feb 02, 2012 10:40 pm

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.

hang

Posts : 528
Join date : 2010-05-07
Location : Brisbane, Australia

View user profile

Back to top Go down

Re: Replacing Aggregate dimension with Conformed base dimension at lowest level.

Post  ngalemmo on Thu Feb 02, 2012 11:25 pm

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

Posts : 3000
Join date : 2009-05-15
Location : Los Angeles

View user profile http://aginity.com

Back to top Go down

Re: Replacing Aggregate dimension with Conformed base dimension at lowest level.

Post  hang on Fri Feb 03, 2012 12:17 am

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.

hang

Posts : 528
Join date : 2010-05-07
Location : Brisbane, Australia

View user profile

Back to top Go down

Re: Replacing Aggregate dimension with Conformed base dimension at lowest level.

Post  hang on Sat Feb 11, 2012 11:36 pm

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.

hang

Posts : 528
Join date : 2010-05-07
Location : Brisbane, Australia

View user profile

Back to top Go down

Re: Replacing Aggregate dimension with Conformed base dimension at lowest level.

Post  ngalemmo on Mon Feb 13, 2012 2:45 pm

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

Posts : 3000
Join date : 2009-05-15
Location : Los Angeles

View user profile http://aginity.com

Back to top Go down

Re: Replacing Aggregate dimension with Conformed base dimension at lowest level.

Post  Sponsored content


Sponsored content


Back to top Go down

View previous topic View next topic Back to top

- Similar topics

 
Permissions in this forum:
You cannot reply to topics in this forum