Values from one dimension in another dimension

[tshad]

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.

Thanks,

Tom

 

[dkyrtata]

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).

 

[tshad]

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).

Thanks,

Tom

 

[RiverGuy]

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.

 

[tshad]

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?

Thanks,

Tom

 

[RiverGuy]

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.

 

[dkyrtata]

Tshad

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.

 

[tshad]

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.

Thanks,

Tom

 

[dkyrtata]

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).

 

[tshad]

Sounds reasonable.

Thanks,

Tom