Creating materialized view of snowflaked dimensions

Hi,

I have a scenario in my data warehouse project, where one of my facts is related to a SKU dimension. The SKU dimension itself contain a product, version, config, sale channel and so on. The fact can grow huge. So I am also creating aggregate facts at product level, or config level. For example in the case of a aggregated fact over product, the fact will contain a product key and there will be no SKU key.

In that case, if I create a product dimension or config dimension to include keys in aggregated facts, why not snow flake the SKU dimension, and store product key or config key in SKU dimension too. Now that comes with a multi-join hit, when reporting on the non aggregated fact. So I recommend go on with snowflaked design, but create a materialized view joining SKU to product dimension, config dimension, or any other. Now the big fact table single join to this materialized view.

Can you please suggest me if this is a good design?

I think this is one of the main situations where snowflake are suggested ( Aggregated facts)

If you have a situation where both product and SKU are valid dimensions (sales by SKU, marketing/planning by product), just include FKs to both in SKU level fact tables. There is no need to snowflake.

Also, what are you trying to gain with a materialized view?

Hi ngalemmo:

SKU dimension in my case is like a master source for all dimensions. I do not have separate sources for say product, config, and so on. Rather they need to be derived from the information contained in the SKU. Following are the attributes of SKU:

skuid, sku_desc, product_code, product_name, version_code, version_name, config_code, config_name, language_code, language_name,....

So while loading my SKU dimension, whenever I encounter a new language_code in a SKU record, I put language_code, laguage_name in DIM_LANGUAGE. Then I put the LANGUAGE_KEY in DIM_SKU replacing language_code and language_name. I do the same for all dimensions. Thus I finally get a considerable amount of snowflaking for SKU level facts. That is undesirable.

Now, if I create a materialized view say DIM_SKU_MVW, joining all child dimesions with DIM_SKU (it is equivalent to reversing the process what ETL did), I get away with single join of SKU level fact with the materialized DIM_SKU during reporting. At the same time I leave other dimesions DIM_CONFIG,DIM_PRODUCT,DIM_LANGUAGE, etc available for aggregate level facts.

I understand breaking them out, but if you are not keeping SKU intact (i.e. all fields), then the correct practice is to put the FK's to the other dimensions into the SKU level fact (no snowflake). This would allow you to create aggregates of the detail using any of the conformed dimensions.

If you don't want to do that, then just leave all the columns in the SKU dimension (i.e. what would have been the materialized view). You could still have the other dimensions, and create aggregates, provided you keep the other dimension keys in the SKU dimension as attributes to make the aggregation process simpler. This works ok for type 1 dimensions. I would do it the first way if some of these are type 2.

I'd start off by saying - don't snowflake your dimensions.

You are describing what Kimball labels "shrunken" dimensions. Shrunken dimension is a subset of rows and/or columns from a primary dimension.

Keep your primary dimension the SKU dimension. From that you can create any "shrunken" dimensions required for aggregate tables.

Within your ETL processes, you define a separate process for maintaining the shrunken dimensions. (i.e. keeping them conformed with SKU_DIM).

SKU_DIM - All attributes (SKU, Product, Config etc).
Product_DIM - All Product Attributes from SKU_DIM.
Config_DIM - All Config Attributes from SKU_DIM.

No snowflakes or MV's required.

Agreed with LAndrews. Don't snowflake your dimension when you need shrunken/aggregate dimension to support fact that is only available at aggregate level. The best dimensional modeling practice is to denormalise all the attributes, instead of normalising by FK, in the base dimension and use them to build/maintain the aggregate dimension.

The key point is to minimise the number joins to aggregate base facts to higher level. Unless the base dimension is too big (monster dimension) and growing fast because of the hierarchical changes, I would not let fact table to reflect the relationship that exists within dimensions. I would also not compromise the modeling guidelines because I can use MV supported by certain DB platform.

Thanks a lot ngalemmo ,LAndrews, hang and Vishy..
Your answers were really very helpful.