Customer Dimension

I am trying to model a Customer Dimension with Educational and Employment details and I want some advice on how to model this scenario
A Customer can have several levels of Education like schooling, college, university, training (one to many realtionship) and
A Customer could have been employed at several places throughout his/her life (one to many realtionship), If I create a record for each level of Education and Employment I would be storing too many records

what is the best way to model this scenario should I create seperate Dimensions for Education and Employment and reference Education_Key and Employment_Key for that Customer_Key in the Fact table

Please advise

As education and employment are independent of each other, if you go the factless fact table route, you need to create two facts, one with customer/education and the other with customer/employment.

Alternately, treat education as a multivalued dimension with a single fact table of customer/employment/education group. There are not that many unique combinations of education that a grouping would become unwieldy... or you can break education down to education and training/skills as separate dimensional groups. Also, do you need to know lesser education? If someone has a Masters, is it necessary to also record a Bachelors and High School?

I would agree with ngalemmo, to have a factlessfact to have a single combination of Customer/Education/Employement.

But it is important how much does it value add to store Customers schooling and college details.Subjected to business requirements, if it is not that needed better to avoid such data in ETL process itself.

In such case if you dont prefer to have his schooling and college details, i would suggest to have only two Dimensions namely DimCustomer where one or two columns related to his education fall under his education profile., say Degree ( graduate/post graduate ...), University ( Stanford / Cambridge /...). The DimCustomerEmployement however remains the same as the current employement status of the customer is always is of high importance and value.

I'm trying to understand you situation a little better. What would the fields be in your DimCustomerEmployement table?

Thanks for responding to the post

jim - The fields in DimCustomerEmployement would be all Customer Info as in FirstName, LastName, Gender, etc and the Employment info will be Employment From, Employment To, Employment Type, Job Title, Reason for Leaving etc

My question to ngalemmo is if I create a FactlessFact table how can I link this Factless Fact table with the Product and Geography Dimensions, here is my requirement we have "unemployed people coming to our office, we capture their personal, educational, employment info and then refer them to Products offered at various Locations", the confusion I have is our Customers can have 3 addresses, 2 phone numbers, 3 email addresses 5 Education history and 6 Employment history should the address,phone,email also be stored in a Factlessfact table and how do I link this factless fact table to the actual metrics/facts of the Products offered to this Customer

Thanks
Kenny

Ok, it's getting more complicated...

You need to break the problem down into its fundimental parts. The core fact you are trying to track is product recommendations to customers. That is a single business event which would have its own fact table based on customer, product, location and other dimensions such as date, who made the recommendation, etc...

If you treat education as a multi valued dimension, you can add education group as a dimension as well.

For the customer itself, it is not practical to directly link the other attributes (multitude of addresses and job history) directly to the core fact. If the addresses are historical, rather than alternates, you may want to create a type 2 customer dimension, as well as a type 1 customer with current contact information and carry both keys in the fact. The customer dimension would include an address and a few phone numbers. If these are alternate addresses, then I would create a type 1 customer dimension with the primary address (and all phone numbers) and have another fact table with customer and address to track the alternates.

Job history is its own fact. You may want to implement position/title and company(employer) as dimensions in addition to customer, from and to dates, reason for leaving, etc... If education cannot be handled as a multi valued dimension, then treat it the same as job history (have school/university and diploma/certificate as dimensons).

You wind up with three or four fact tables. It is unavoidable due to the cardinality of the relationships. If there are key attributes relating to job history and education (such as highest job level or education level) that are commonly used in analysis, these can be maintained in an additional mini dimension and referenced from the main fact table to speed such queries. But if someone wants to see recommendations along side a customers job history, these are two distinct queries merged into a single report... there is no way around that. Fortunately, this is something most robust BI/reporting tools can handle.