0

I always doubted if I should include ID or the actual text in the dimensions. For example, see DimSalesPerson in the screenchot below - https%3A%2F%2Fwww.codeproject.com%2FArticles%2F652108%2FCreate-First-Data-WareHouse&psig=AOvVaw2oGn_eyUgi8KV9m_u6PSIF&ust=1666197880975000&source=images&cd=vfe&ved=0CA0QjhxqFwoTCOjG9_6c6voCFQAAAAAdAAAAABBJ. Instead of using StoreName as text in DimSalesPerson, they used StoreID. What do you usually do in the cases like that when there is an attribute in a dimensions table A that has a stand-alone dimensions table B?

enter image description here

eponkratova
  • 467
  • 7
  • 20
  • 2
    Given that you want to denormalise the store information into the SalesPerson Dim (which based on the single star shown I wouldn’t), you should denormalise all the store attributes, not just one – NickW Oct 18 '22 at 21:18
  • @NickW, thank you for replying! Do you mean, instead of having a StoreID, you would have storeName, StoreLocation, etc. in DimSalesPerson? – eponkratova Oct 18 '22 at 23:29
  • [Why should I not upload images of code/data/errors when asking a question?](https://meta.stackoverflow.com/q/285551/3404097) An ERD is an image of DDL. Please: Give explict credit for what you quote/copy. Give just what you need & relate it to your problem. Don't copy what you don't have a right to. [ask] [Help] – philipxy Nov 01 '22 at 00:26

1 Answers1

-1

Dimensions should never link to other dimensions. The only exception that I normally make to this rule would be for dimensions with dates that would join to a date dimension. I'm sure there are other exceptions, but they're rare and I don't think they apply here.

So yes, the correct answer, based on what you provided, would be to recreate some of the DimStores dimension attributes inside of the DimSalesPersons dimension, based on the store that the salesperson is associated with. Depending on the type of dimension that DimStores is, your ETL processes will need to handle changes to an existing store record in both DimStores and DimSalesPersons. That's a pretty common design paradigm.

BayouKid
  • 69
  • 4