Customer - Account - Service modelling

I am new to data modelling. I have a requirement like below

There are 3 dimensions by name Customer, Account and Service. Here One Customer can have multiple accounts, each account can have multiple services. This is for telecom domain.

I have designed a bridge table with Custmer key,Account Key and Service Key, start date and end date.

Can someone suggest on the best approach to model the above case.

Also ETL approach to populate the bridge table.

Awaiting for response.

Thanks in advance - Siddesh

Hi,
a dimensional model consists of facts and dimensions, bridge tables are used to solve very specific m:m relationship challenges and would not normally be considered at the start of your design process - as fact tables are the core objects for linking dimensions.

What facts are you trying to model?

Regards,

Hi,

Thanks for the reply.

I am modeling a case as below for a telecom service provider.

Service number is basically a mobile number which belongs to one account. One account belongs to one customer.
In reverse : One customer can have multiple accounts. One account can have multiple services.

From MSC, we receive call detail records [CDRs] which contains only service number.

Business requirement is to make analysis on the revenue on services, accounts, customers, customer age band etc...

Source for customer, account and service is 3 different tables linked with foreign keys.

Since it is many to one relation, I preferred to make bridge table link customer, account and service to link these dimensions.

Please suggest


Thanks
Siddesh

For starters, you need to understand the terminology. As Nick pointed out, a 'bridge table' is a relationship between two dimensions and is used to resolve M:M relationships. In a dimensional model you have fact tables, dimension tables and bridge tables… that is it except in unusual circumstances, of which this is not one of them.

A fact table is zero or more measures surrounded by context (dimensions). Since customer, account and service number are contexts for whatever fact you are trying to record, they should be dimensions of the fact… not a bridge table.

As I am also modeling for a similar situation (Utility Industry) and was about to ask about the proper way to model the same dimensions, I thought it might be good to tag into this discussion. I hope nobody minds. We are rebuilding our Data warehouse. The original was built about 7 years ago and only got as far as the Customer Data. In the prior model, the Customer, Account and Service Dimensions were created with overlapping information (Service DIm contains info from Customer and Account dims, Account dim contains info from Customer). They are all type 2 SCD's with similar trigger columns so, changes in the Customer info cause new records to be created in all 3 dimensions. In most cases a fact table will only join to 1 of these dimensions. In the new design, I plan to change these dimensions as follows:
1. They do not have overlapping information
2. Do not generate a new record based on changes in one of the other dimensions
3. Whenever possible, have the fact table join to all dimensions at the same level or higher (if a fact is at the service level, it will join to all 3 dims, if it is at the Customer Level, it will only join to the Customer Dim).
I feel pretty confident that these changes are more in line with best practices. Please let me know if I'm missing something. The architect that designed our first DW did a good job for the most part so, if there's justification for keeping it the old way, I will. It just doesn't make sense to me. There's 1 other thing I'm not sure how to handle. The original design also included the surrogate keys for the other dimensions for hierarchies (Service dim had SA Key, Account Key and Customer Key, Account DIm has Account Key and Customer Key). I believe this was done because they are type 2 dimensions and it was the only way to get a primary key for the dimension levels. As I have not seen this in any other Dimensional Model example, I'm wondering if I still need to keep those in the new design. If it helps, we're using OBIEE. Thanks

Yes, you are better off keeping customer, account and service distinct and using the appropriate number of FK's depending on the grain of the fact. This allows you to easily aggregate and integrate service level facts with account or customer level facts…. it also cuts down the number of addresses you would have in the service dimension, which can get pretty wide if you include attributes of the dimensions above it.

Thanks for the quick response. Do you have any thoughts on the practice of adding the surrogate keys of the other dimensions in the more detailed dimension tables? As I mentioned, I believe this was done in order to have a primary key for the hierarchies - not for snowflaking. I don't know that if it is still necessary and I've never seen it done in any other example

There are times when having FK's to other dimensions is handy to support functional aspects of the load processes. Hierarchies are a good example.

They can be used to generate bridge tables or to publish a 'flattened' version of the hierarchy (table or view). It is also useful loading facts when the source does not have all the natural keys. For example a meter read that just has the service number. You want the fact to have the customer and account keys as well, so storing those keys in the service dimension makes the ETL process much simpler as it has an easy way to get them when loading the fact.

You just don't expose those columns thru the end-user BI tool. They are there to support back-end processes.

Great! Thanks again for the speedy response. I think I can finally complete my design:)

OK, I've spent the week finalizing my design. The Service Agreement Dimension is what gave me the most heartburn. It has close to 7,000,000 rows and about 100 attributes. After spending many hours evaluating the attributes for expulsion, the light went on. If I remove the Natural Key (SA ID) from the Dimension and make it an attribute dimension, the table only has about 8,000 rows (distinct set of attributes currently used). I figure I can treat the SA ID as a degenerate dimension and move it to the fact table. For reporting purposes, people just care about the attributes, not the ID. So, am I thinking crazy or am I thinking dimensionally? Are there any pitfalls I'm missing with this approach?

Your approach is correct. If the rows in a source system table are effectively just instances of a set of master records you should just be bringing that set of master records into your Dim - and make the BK a hash of the values in the fields in that Dim, so that you can easily match source records with DIm records without having to compare every field value

Thanks for the confirmation Nick. Sorry for the slow reply. I hope this has helped the original poster