CODEWITHSUNDEEP
data-warehouse
Eternity
Eternity

Reputation: 3515

can any attribute with multiple values be a dimension?

This might be a weird question, but can we make a dimension table out of any attribute if it has multiple values? for instance "ethnicity", should ethnicity always be an attribute of a customer (for example), or can it ever, in any case or scenario be its own dimension table.

Thank you in advance for any help

Upvotes: 0

Views: 1831

Answers (1)

Lahiru Fernando
Lahiru Fernando

Reputation: 104

If the attribute is having multiple values, It is not a good idea to have all the values in one column. You will find it difficult in later stages if you get a requirement to search something based on that value. A good approach would be to create another sub dimension table with the main dimension surrogate key as a foreign key and place the multiple values row wise. So that you will have a result set which will have 1 record in Main dimension and many records for that main record in the sub dimension table. I think that would be a better approach to handle multiple value scenarios.

I got to handle a similar scenario where one customer has many phone numbers and all the phone numbers are stored in one column in the source database. I created the customer data without including the phone number, and created a separate Dimension table including the customer table key as a foreign key and included all the multiple values as rows in that Dimension table which help me a lot in other tasks that I had to address later. ETL processes can separate these values into rows.

I hope you get the idea.

Upvotes: 2

Related Questions