Suggestions on Dimension/Fact design

what is the best/popular approach to design small dimensions(candidates for Junk Dimension) in the following scenario?

"Happening" is both Dimension and Fact [has measures]
"Result" is another Fact with "Happening" Dimension.

Happening has multiple Small Dimensions which are only present on the Happening Fact.

In the case of analyzing the "Result" Fact from "Happening" Dimension using one of those Small Dimensions,what will be the best way of designing them.

Is it Okay to have the Small Dimensions ,described within the "Happening" Dimension itself?Not sure if that would be the best designapproach.

Data view

Happening Dimension

Happening | Source Product Name |DW STANDARD NAME

ABC | AsH |ASHAP
BBB | tDVC |TADER


Source Product Dimension [ on Happening Fact ]

KEY|Product Name
1 | ASHAP
2 | TADER

Hoping for some excellent suggestions.

Cheers!!!

The design is fine. It is not unusual to have a handful of small dimensions like that. It is an appropriate way to deal with attributes specific to a fact. When multiple such attributes are involved, it is common to combine them into a single dimension.

thanks for your response!!!

but just that will be a problem,if for some reason we need to change the DW STANDARD NAME for the product[which i have seen happening in the past],we will need to update the "happening" dimension with new row(scd2) and also the product dimension with the new row.

2 surrogate keys

KEY|key2|key2 ef dt |Product Name |current indc
1 | 10 |1/1/2013 |ASHAP | N
2 | 10 |1/3/2013 |ASHAPs | Y

now ASHAPs would needed to be updated on the "happening" dimension as well.I dont think thats the right approach.Your thoughts?

cheers!!!

Why do you have product name in both dimensions?

We have the product source name in the Dimension table ,so that we dont need to have fact table involved in getting the product information.

The requirement is to preserve the source product name and if we dont put in the happening dimension then would need to join the happening fact as well to get the product source name information.

One case:

happening dim-> happening fact (product source/dw)
happening dim -> impact fact

so to get happening (product) ,we would need to go impact fact -> happening dim -> happening fact

second case:

happening dim (product source/dw) -> happening fact (product dw)
happening dim -> impact fact

so to get happening (product) ,we need to go impact fact -> happening dim.

Any thoughts?

cheers

Impact fact = Result fact

The whole point of dimensional design is "to have fact table involved in getting the product information".

A fact represents a business event or state. The dimensions provide context for that event or state. If a product is involved then product should be represented as a dimension FK from the fact. Besides, what do you do if the user wishes to use other attributes of product? How would they get them?