mutilple One to Many relationships

I have a table called customer which has a one to many relationship with customer_boxes(hardware they own) and customer boxes has one to many relationship with customer_boxes_addressable_services(pay services) in the OLTP systems.

I am creating a Customer Dimension and will be needing data from Customer table for sure. Any idea, best paractices about handling customer_boxes and customer_boxes_addressable_services data? Should I create seperate dimension OR should I have fact table capturing the relationship between these three table. Your suggestions and comments are greatly appreciated.

Depends on your reporting requirements. Generally speaking though, I would put Customer Boxes and Services in a different dimension. I also try to avoid creating relationships between dimensions.

there are going to be many records in customer_boxes for one customer. I do not want to insert one record for each work box a customer owns in the fact table.

If you need to report what customers own what boxes and what services on a box (or if services are a function of customer, what services for a customer) there is no getting around having a fact table of customer services and boxes.

As for the boxes, what do you need to know about them? I assume there is a NIC code that identifies the box, but other than that, what else? Maybe a model and rev level and that sort of thing. If that is the case, I would have a dimension of box models and treat the NIC as a degenerate dimension. So a fact table of customer boxes would countain (at minimum):

customer key
box model key
box NIC (degenerate dimension)

As for services, I would treat it as a multivalue dimension. Create a service dimension that lists each specific service. Define a service group dimension that contains every unique combination of services any customer has (this is not a very large dimension in practice). There is then a fact table that associates the groups with the services:

service group key
service key

This may also have measures such as revenue contribution rates for the service as it relates to the group.

You then associate the service group with the customer and or the box. If the latter is the case, you just add service group key to the customer/box fact:

customer key
box model key
box NIC (degenerate dimension)
service group key

Again I'll refer you to your reporting requirements. These define how your dimensional model will look, not the normalized OLTP data structure. I think once you gather these you will find that there are not many set top box (STB) requirements other than who has it, how many do they have, did they return it when the canceled their service, and perhaps the MRC for the STB. This is one fact. The other fact you have is a Product or Service fact. Again your reporting requirements will dictate the grain of this fact table. A product would be any individual channel a customer is subscribed to. This table will get quite large. Most cable operators will provide product aggregations such as "Expanded Basic" and "HD Tier". This might fulfill the type of reporting you need. Customer will be a dimension on both of these fact tables.