Kimball Forum
Would you like to react to this message? Create an account in a few clicks or log in to continue.

Suggestions on Dimension/Fact design

2 posters

Go down

Suggestions on Dimension/Fact design Empty Suggestions on Dimension/Fact design

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

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

Back to top Go down

Suggestions on Dimension/Fact design Empty Re: Suggestions on Dimension/Fact design

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

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

http://aginity.com

Back to top Go down

Suggestions on Dimension/Fact design Empty Re: Suggestions on Dimension/Fact design

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

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

Back to top Go down

Suggestions on Dimension/Fact design Empty Re: Suggestions on Dimension/Fact design

Post  ngalemmo Fri Apr 12, 2013 12:21 pm

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

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

http://aginity.com

Back to top Go down

Suggestions on Dimension/Fact design Empty Re: Suggestions on Dimension/Fact design

Post  TheDarkKnight 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


TheDarkKnight
TheDarkKnight

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

Back to top Go down

Suggestions on Dimension/Fact design Empty Re: Suggestions on Dimension/Fact design

Post  TheDarkKnight Sun Apr 14, 2013 1:35 pm

Impact fact = Result fact
TheDarkKnight
TheDarkKnight

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

Back to top Go down

Suggestions on Dimension/Fact design Empty Re: Suggestions on Dimension/Fact design

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

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

http://aginity.com

Back to top Go down

Suggestions on Dimension/Fact design Empty Re: Suggestions on Dimension/Fact design

Post  Sponsored content


Sponsored content


Back to top Go down

Back to top

- Similar topics

 
Permissions in this forum:
You cannot reply to topics in this forum