A design based on junk dimension
2 posters
Page 1 of 1
A design based on junk dimension
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:
DIM_ENUM_VALUES
-------------------
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
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:
DIM_ENUM_VALUES
-------------------
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
kajaldas007- Posts : 15
Join date : 2012-01-05
Re: A design based on junk dimension
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.
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.
hang- Posts : 528
Join date : 2010-05-07
Location : Brisbane, Australia
Similar topics
» Generating a Junk dimension - Cross join or based on actual values in fact?
» bridge table and junk dimension on customer dimension (bank/credit union)
» modelling Product dimension for Pizza outlet
» Dimensiional Model based on PTA Database Design
» Fact table design based on sales appointments
» bridge table and junk dimension on customer dimension (bank/credit union)
» modelling Product dimension for Pizza outlet
» Dimensiional Model based on PTA Database Design
» Fact table design based on sales appointments
Page 1 of 1
Permissions in this forum:
You cannot reply to topics in this forum