Representing Repeating Groups within A Dimension

Hi,

What is the best way to represent repeating groups within a Dimension.

I have, for example, a simple start schema that has DIM_CUSTOMER and FACT_SALES.

For my customer, I want to record their nationality; however, for some customers, they may have multiple nationalities. How is this best represented?

There could be other data items that could have 1-n occurrences.

If it was a time related attribute, for example, Occupation, then I could have a slowly changing dimension, although some people have multiple concurrently running occupations that we may want to record.

Thanks,
Al.

If you are in retail, good luck. It's hard enough to get a name let alone a nationality. It would seem to me that trying to collect more than one is a bit overkill. But, if you must, include a second or third column.

Thanks for the reply.

It's actually a system for Criminal Justice. I was picking a common example, when using Sales.

Maybe this problem is not as common as I thought it may be. For some attributes, adding column1...column2 will certainly work. For some, not so easy when there are an unknown number of members in the group.

Let's say I was writing the Facebook 'advertising' Data Warehouse. I have DIM_MEMBERS, DIM_DISPLAYS_ADS and FACT_CLICKS_ON_DISPLAY_ADS.

Facebook allows its Members to record their Interests (to allow for targeted advertising), so I could also have DIM_INTERESTS.

A Member has many concurrent interests.

I wonder how the relationship between a Member, their Interests and the Facts would be represented?

I may want to run a report that shows the number of Ads clicked on my people under 21 and who are interested in Football and Rugby.

Al.