Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!
  • Students Click Here

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Values from one dimension in another dimension

Values from one dimension in another dimension

Values from one dimension in another dimension

This one is similar to my previous post about stores.  

I am trying to figure out how to handle stores and retailers in an MDDB.  The issue is that a store with store number by itself if pretty meaningless without a retailer.

For example, you can have a store number that is the same as from one retailer to another.  For example, Sears can have a store 100 and so can Costco.

I have a retailer dimension and a store dimension.  The problem is that I can't take facts about a store and add it to my fact table without figuring out which store I am dealing with.  So if I sell a product in store 100 in OLTP and want to move that to my fact table, the problem is which of the 10 stores that have a store 100 do I use?   The store numbers are unique within stores.

Even though I have a Retailer dimension, would it be reasonable to also add the Retailer name to the Store dimension?  Would I just add the key from the Retailer dimension (which is how I would do it in a normalized database) or just put Sears or Costco in a string in the Store dimension.  



RE: Values from one dimension in another dimension

It seems your store table is meaningless without the retailer. Therefore I would combine the two into one table, whose natural keys would be retailer_id and store_id (as well as the source_id of which source-system is sending the data).

Your current structure of having two separate tables is known as "snowflaking". Its a technique that is not uncommon, but should be avoided whenever possible, because it can impair query performance.

Assuming you want to keep the snowflake model: If you add the retailer name to the store dimension, you would have to update it in both tables whenever a name changes. Even more to maintain if you borrow additional fields from the retailer table. I would be more inclined to add a foreign key to the store table which would point to the appropriate record in the retailer table (via its primary surrogate key).

RE: Values from one dimension in another dimension

We are doing a lot of reporting by retailer and then drill down to the store level, which is why we have the two dimensions.

But when I am adding records from my transaction systems, I need to know which store I am accessing and the store by itself isn't enough.  You would also need to know which retailer that store is with as mentioned.

I agree on the FK.  I wasn't sure if that is a good way to do it in a dimension structure (except for the Fact table).



RE: Values from one dimension in another dimension

I would combine Retailer and Store like dkyrtata suggests.  Retailer would just be another attribute/level in your hierarchy.  Since you are using SSAS (looking at your other post), it will also be easy for you to reuse this dimension when you have other facts which connect at the Retailer level, without going down to a particular store.

RE: Values from one dimension in another dimension

But that is the issue.

I do need to go down to the store level.

I need to run reports by retailer that will allow me to drill down to the store level.  Wouldn't this scenario prevent that?

Also, Dkyrtata mentioned snowflaking.  Is that what I would be doing if I added the Retailer FK from the DimRetailer table?



RE: Values from one dimension in another dimension

Retailer and Store are completely related based on what you have described.  They are part of a natural hierarchy.  You don't want two dimensions.  You want a single Store (Although I might call it Retailer) dimension.  From what I can gather, Retailer is just an attribute of a store, and a grouping of stores.  It's part of a hierarchy you will define in SSAS which will allow drilldowns.

So Retailer Name needs to be in your store dimension.

You will probably have Retailer ID in your store dimension as well.  When you bring a fact row in through your ETL, you should know the Ratailer ID and Store ID.  Perform a lookup on your dimension based on Reatailer ID and Store ID.  Save that surrogate key with your fact row.

In SSAS create relationships and a hierarchy which goes from Retailer to Store.  Your users will be able to drilldown with this hierarchy.

What I said before may have been confusing.  I was saying that in SSAS it is possible to still assign OTHER facts to the Retailer level only if needed.  For example, let's say each retailer is sending you their monthly sales forecasts.  They don't break them down by store, but are for the whole retailer.  You can still use this Retailer/Store dimension in SSAS even though it doesn't go down to the store level.  

RE: Values from one dimension in another dimension


Snowflaking occurs when you normalize your dimension tables into additional dimension tables - a natural tendency when you come from a TPS environment. Snowflaking is not always a bad idea. The trick is to figure out when it is appropriate and when its not.

If you prefer to keep both your store and retailer tables, use the foreign key in the store table to point to the retailer record (as I said in my previous post). Then add the same foreign key to the fact table as well. This way your fact table will have one foreign key pointing to the store table, and another one pointing to the retailer table. This will give you 2 paths to get to a given retailer record from the fact table: One directly from the fact table, the other from the fact table to the store table to the retailer table.


RE: Values from one dimension in another dimension

I assume that if I use the RetailerID from the DimRetailer in the DimStore that would be a snow flake?

If I were to just use the Retailer name, it wouldn't be.



RE: Values from one dimension in another dimension

Yes, if one dimension table references another (rather than using one combined dimension table), you are snowflaking.

In your case the foreign key (RetailerID) of your DimStore dimension references the Primary Key of your DimRetailer table (both being surrogate keys of course).

I would be more inclined to snowflake if both tables had many fields. But if each table has, say 3 fields, I would combine the two.

Sometimes you need to go with your gut feeling and determine whether it was the right choice by the problems you face (if any).

RE: Values from one dimension in another dimension

Sounds reasonable.



Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members! Already a Member? Login

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close