Food For Thought?

[datamodeller]

Scenario: There is a huge dimension which is complex both length and breadth wise (~40 attributes and ~20 million records) which is referenced to a fact table (~15 million records). Both these dimension and fact tables are expected to be used extensively for reporting and they have to be modelled appropriately for the best performance.

Solution: The low cardinal attributes from the huge dimension table (that will be used for reporting mostly) are segregated into a sub dimension (so called 'Junk' dimension) and referenced to the master dimension (as 'Snow Flake') and also referenced directly to the fact table inorder to provide small and quick access paths for the sql query. X-dimensional reference is enabled iroder to perform dimensional analysis (i.e. without making use of the fact table) or 'What has not happened?' analysis. Access paths will be desinged appropriately in the presentation layer depending on the element that will be used for reporting.

The solution seems more appealing however does not appear to fit to the traditional approach on dimensional modelling plus it is the hybirid of snow flake + junk dimension + ??.

Have abstracted the scenario and solution details for simplicity and to give more ephasis on the crux of the scenario and the solution. Apologies if this is not clear.

There are a few scenarios like this and before adoptiong, I would like to expert views on this solution.

[hang]

Hopefully this post will help.
http://forum.kimballgroup.com/t1040-a-trio-of-interesting-snowflakes-article

[datamodeller]

Thanks Hang. Actually I have been searching for this topice earlier but found the link is broken ! Thanks very much for forwarding.

On the query perspective, I understand the scenarios where the snowflakes can be beneficial however the partial query remains "referencing the snow flaked dimension to both master dimension and also the fact table" inorder to enhance the performance. Any thoughts on this with respect to the previously mentioned scenario?

[hang]

In my understanding on Kimball’s mini-dimension concept and actual experience applying it, the mini-dimension referenced by FK in fact table will track the historical correlations between master dimension and the relevant set of attributes, which would otherwise be achieved through SCD2 attributes in the master dimension.

Since the master dimension is already too big, you may want to keep it reasonably static by tugging away any frequently changing attributes into one or more mini-dimensions. However the relationship between min dimension and master may be 1 to many, so you may need a ‘current profile’ reference in the master dimension to point to the latest set of attributes in mini-dimension, as such a relationship can only be achieved by a join and an self-join to an SCD2 dimension. Therefore the ‘referencing’ is two folds, functionality and performance.

The mini-dimension snowflake is also called outrigger of the master dimension. For any monster dimensions (>million rows), as the priority of the performance outranks the ease of use in this case, you should normalise it by following options:

1. Include low cardinality attributes in mini-dimensions.

2. For high cardinality and highly repeated SCD 2 attributes, create standalone dimensions and have FK in fact table to track the changes.

3. Snow-flake highly repeated SCD1 attributes that are not fit for mini-dimension into standalone lookup dimensions.

[datamodeller]

That makes sense !

Appreciate your response..

[Sponsored content]