CODEWITHSUNDEEP
data-warehouse
Dan
Dan

Reputation: 11

Data warehousing dimension design

I'm hoping someone can help me with this.

Suppose we have 2 dimensions in our vehicle data warehouse: TRUCKS and PACKAGES. Both are Type 2 SCD.

dim_TRUCKS contains the following data:

TRUCK_KEY   NAME   PRICE
    1       Ram    45000
    2       F150   48000
    3       Tundra 43000

dim_PACKAGES contains the following data:

PACKAGE_KEY   NAME     PRICE 
    4       Offroad    4000
    5       Luxury     7000
    6       Sport      2000

The biz rules and requirements state that each TRUCK offers only one PACKAGE. (I know that's not realistic, but it best conveys the particular business dilemma I'm faced with).

The PACKAGE that each TRUCK offers can change over time.

So the question is what's the best way to design and implement this?

My initial thought is to simply add the PACKAGE_KEY to dim_TRUCKS, such as this:

TRUCK_KEY   NAME   PRICE   PACKAGE_KEY   
    1       Ram    45000        4
    2       F150   48000        4
    3       Tundra 43000        6

Obviously what I'd end up with is an attribute of a SCD being based on another SCD. Is that bad design? Is there a better way to go?

Thanks much.

Upvotes: 0

Views: 45

Answers (1)

tobi6
tobi6

Reputation: 8249

I would not model the business rule one package only on one truck. I would rather set PACKAGE and TRUCK as dimensions which are being referred to out of the fact table.

Reason

If the business rule changes in the future (the probability for this is usually high) you might have to remodel more than when keeping it simple now.

Also you are right, always try to hold complexity down and since you can choose, I'd go with the less complex way in directly referring to the dimension.

Upvotes: 0

Related Questions