Modeling Supporting Dimension Data in a Enterprise DW

My goal is to create the beginnings of what will become an enterprise data warehouse. My intensions are to build this by using a dimensional model. While standard star schema dimensional models can be straightforward, I'm trying to understand how to include information that supports my dimensions with a lot more detail.

For example, I have a Customer dimension that will join to multiple fact tables on DimCustomerID. This table has one record per customer (forget Type 2's for now). This allows me to slice the fact tables easily and supports the analytical requirements.

Now, I also need to include information in the data warehouse that is used for reporting, but not really analytical reporting (meaning it doesn't connect to a fact table). For example, Customers have multiple Phone Numbers, Addresses, Email Addresses, Notes among other things. Each of these has a many relationship to my single Customer Dimension. I'm curious to konw the best way to include this data in the dimensional model since it will not get linked directly to fact tables, but does support further required Customer reporting. From a Customer perspective, the Fact table grain would never be at one of these lower levels, such as Phone Number.

I can't combine or de-normalize any one of these in my core Customer dimension because it would leave more than one record per unique Customer. This wouldn't allow me to join to a fact table appropriately. It would produce double counting of my measures when joined to the fact table.

My primary thought would to create these as separate tables that have the DimCustomerID in them leaving a one-to-many (Cusomter-to-Phone Numbers) type of relationship between the core dimension and the supporting tables. These don't seem like real dimensions since they won't tie to a fact table, but I would denormalize any many-to-one's like a typical dimension where possible to make ad-hoc reporting easier. Should I call them Dimensions with a "Dim" Prefix? This also doesn't appear to be the standard snowflake model since they are one-to-many and not many-to-one's. I understand a standard snowflake to be able to be joined together and retain a single record, such as Product to SubCategory to Category (AdventureWorks). Joining these still leaves a single unique Product record. My tables would not leave a single unique record like Product would.

Is this the best way to do this? I'd appreciate any thoughts or other suggestions on this.

Cory

These other things would be dimensions unto themselves. You associate them to the customer using bridge tables, customer key on one side, key to the dimension (such as address) on the other.

ngalemmo, thanks for the reply!

I had thought about using bridge tables and after considering it I didn't know if it was the right way to go. I think it definately can work to add a bridge table between Customer and each support table such as Addresses and Notes, however, I didn't know if that was the right approach since there is not a many-to-many relationship between Customer and Addresses or Customers and Notes. There is a one-to-many between these since an Address or Note will always be tied to exactly one customer.

I thought the bridge table was to help resolve many-to-many relationships such as customer and account where a customer has multiple accounts and an account can be owned by multiple customers.

I just want to confirm that bridge tables would be the right solution for my one-to-many relationships as well. I do see another advantage of using a bridge table being that it would be keeping the DimCustomerID out of the other dimensions like Address and Notes. This would keep these dimensions more isolated and independent. At the same time, keeping the tables independent is fine, but they will only be used in the Context of the relationship to Customer.

Please explain further why bridge tables would be beneficial to just including the DimCustomerID in the other tables. Bridge tables obviously add complexity to the ETL, so I'm just looking for Pros and Cons.

Cory

The bridge table allows you to just have customer key in fact tables. If you need an address for the customer you would use the bridge (which can contain an address type code) to select the appropriate address if needed.