A design based on junk dimension

View previous topic View next topic Go down

A design based on junk dimension

Post  kajaldas007 on Tue May 15, 2012 2:43 pm

Following is the scenario:

In the datamart there are several fact tables, each serving different puposes and relevant to specific processes. There are several unrelated low-cardinality value types (e.g. catgories, status, flags, etc.) that are associated with different fact tables.
For example, there is a Reject fact table (factory process); for each reject fact record, there is a Reject category associated with it. Similarly, for another fact table, there is another low-cardinality field called Collection point.

These low cardinality fields (e.g. Reject category, Collection point, etc.) are un-related to each other and make sense in specific contexts only. They are more like Enumerated values. There are at least 9-10 such enumerated value types.

Now, one way to deal with this is to have them as textual fact in corresponding facts tables. But, I was thinking of creating a junk dimension with the following structure:

EnumValueSK int,
EnumValueType nvarchar(20), -- this will hold "Reject Category", "Collection Point", etc.
EnumValue nvarchar(50) -- this will hold the individual enum values.

Since, this dimension contains different "types" of values, it needs to be properly aliased based on the context and usage. For example, when this dimension will be joined with Reject fact table, the filter condition will be EnumValueType = "Reject Category" and the dimension will be aliased as "Reject Category".

What are the down-sides of this design?

BR - Kajal

Posts : 15
Join date : 2012-01-05

View user profile

Back to top Go down

Re: A design based on junk dimension

Post  hang on Sat May 19, 2012 9:16 pm

Normally junk dimension is fact table specific, unless it is also a mini dimension based on another conformed master dimension. So if the attributes are of unrelated low cardinality, and not all the attributes in a single junk dimension are used by the relvant fact tables, I would create some conformed to minimise the overlaps, and separate junk dimensions for some fact tableS, even though there could be overlapping attributes among them.

Think of junk dimension as a form of dimension denormalisation. The repeating groups (attributes) in a junk dimension may also be ones in other denormalised dimensions, including junk dimensions.


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

View user profile

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