Suggestions on Dimension/Fact design

View previous topic View next topic Go down

Suggestions on Dimension/Fact design

Post  TheDarkKnight on Thu Apr 11, 2013 6:44 pm

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!!!
avatar
TheDarkKnight

Posts : 10
Join date : 2013-03-22
Age : 34
Location : NJ

View user profile

Back to top Go down

Re: Suggestions on Dimension/Fact design

Post  ngalemmo on Fri Apr 12, 2013 3:43 am

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.
avatar
ngalemmo

Posts : 3000
Join date : 2009-05-15
Location : Los Angeles

View user profile http://aginity.com

Back to top Go down

Re: Suggestions on Dimension/Fact design

Post  TheDarkKnight on Fri Apr 12, 2013 8:45 am

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!!!
avatar
TheDarkKnight

Posts : 10
Join date : 2013-03-22
Age : 34
Location : NJ

View user profile

Back to top Go down

Re: Suggestions on Dimension/Fact design

Post  ngalemmo on Fri Apr 12, 2013 12:21 pm

Why do you have product name in both dimensions?
avatar
ngalemmo

Posts : 3000
Join date : 2009-05-15
Location : Los Angeles

View user profile http://aginity.com

Back to top Go down

Re: Suggestions on Dimension/Fact design

Post  TheDarkKnight on Sun Apr 14, 2013 1:34 pm

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


avatar
TheDarkKnight

Posts : 10
Join date : 2013-03-22
Age : 34
Location : NJ

View user profile

Back to top Go down

Re: Suggestions on Dimension/Fact design

Post  TheDarkKnight on Sun Apr 14, 2013 1:35 pm

Impact fact = Result fact
avatar
TheDarkKnight

Posts : 10
Join date : 2013-03-22
Age : 34
Location : NJ

View user profile

Back to top Go down

Re: Suggestions on Dimension/Fact design

Post  ngalemmo on Sun Apr 14, 2013 5:28 pm

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?
avatar
ngalemmo

Posts : 3000
Join date : 2009-05-15
Location : Los Angeles

View user profile http://aginity.com

Back to top Go down

Re: Suggestions on Dimension/Fact design

Post  Sponsored content


Sponsored content


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