Modelling many to many relationships in a dimension

Hi,

I want to garner the best way to implement the above. Any advice is more than welcome! I have a dimension that needs modelling that has a number of attributes that are multi-valued. What is the best approach for this? Here are some of the example values that will make up the dimension:

Intelligence Types - up to ten values
Geography - up to 192

I see a number of ways I can model this:

Option 1

All single valued attributes in the main DimTable:

DimTable(DimKey, DimAlerternateKey, Current, EffectiveDate, Attribute1, Attribute2...)

Dim tables and bridge tables for all other multi-valued attributes (just intelligence type shown here):

DimIntelligenceType(IntelligenceTypeKey, IntelligenceTypeAlerternateKey, IntelligenceType)

BridgeIntelligenceType(BridgeIntelligenceType, IntelligenceTypeKey, DimKey)

Option 2

Create a column for every multivalued attribute in the main DimTable (will get very large).

So:

DimTable(DimKey, DimAlerternateKey, Current, EffectiveDate, Attribute1, Attribute2..., IntelligenceType1, IntelligenceType2, IntelligenceType3, IntelligenceType4...)

Option 3

Combination of option 1 and 2. So very large complex attributes (geography in this case) have their own bridge table. Other multivalued attributes stored in the main DimTable.

Option 4

Store comma separated values in a column for each multi-valued attribute.

So:

DimTable(DimKey, DimAlerternateKey, Current, EffectiveDate, Attribute1, Attribute2..., [IntelligenceType1, IntelligenceType2, IntelligenceType3, IntelligenceType4...],)


The main way that users will be accessing this data is through Excel using pivot tables. So I don't really like the idea of a really wide DimTable as it will mean all of the fields fall under one dimension in Excel, whereas I would prefer them group separately as Geography and Intelligence Types, enabling the user to easily filter, instead of having to select them one at a time. Just won't really work I don't think.

Is option one a decent way of doing this? Or is there a better way to implement?

Hi,
please can you give some more details about what your core Dims and Facts are? It's not obvious to me what sort of object, that you are modelling as a Dimension, could have 192 different values for the same geographical attribute or 10 different Intelligence Types. I would normally expect these to be in their own Dims and then joined to a fact table - via bridge tables if they were multi-valued in respect to the fact. The fact that you seem to be joining them to a Dim suggests I haven't clearly understood what you are trying to model
Bridge tables are normally used to join a single Fact record to many records in the same Dimension - using them to join Dimensions to each other looks like snowflaking which is normally a bad idea and should be avoided if at all possible

Hi,

Sorry, if I wasn't 100%, let me try again!

So my 'main dimension' stores a user's search profile (think of it as a google alert), which is made up of search criteria that has multiple choices for each set of criteria, such as the two mentioned above.

So DimUserSeach table:
(UserSearchKey, UserSearchBusinessKey, UserKey, Current, EffectiveDate, SearchCriteria1, SearchCriteria2....)

(UserKey is a reference to a dimension DimUser).

The Fact table would be an instance of the user receiving an alert for a match on their search criteria:

FactSearchMatch
(Id, SearchKey, MatchDate)

So I'm asking how to model two things:

One: how best to store values where there may be multiple choices for the particular search criteria, such as geography, sectors etc. Can I have a bridge table from the DimUserSearch to DimGeography, as such:

DimGeography:
(GeographyKey,GeographyBusinessKey,Country,Region,Area)

BridgeGeography:
(BridgeGeography, GeographyKey, UserSearchKey)

Secondly:

Can I store a dimension key from another dimension within a dimension?(!) In the above I am storing the UserKey (from the DimUser table) in the dimension DimUserSeach.

I also want to store a junk dimension key in the DimUserSeach table. Again, is this not the way to go? Should all of this just go in a fact table? My main hesitation with that is that I want to be able to record changes to the user search's criteria (using SCD type 2).

OK - I think I have a clearer idea of what you are modelling. This is reasonably complicated so the following is just a suggestion and may not meet all your needs but should at least give you a different way of looking at the problem - which may help your thinking.

I might model this differently by creating both a Search Profile Fact and an Alert Fact.
The Search Profile fact would be factless and have SKs linking it to:
- a Search Profile Dim that hold just information about the creation of the search profile but none of the search criteria e.g. profile name, created date, created by etc.
- a Dim for each type of criteria (e.g. a Geography Dim, an Intelligence Type Dim etc) and where these can be multivalued in respect to the Search Profile fact then use Bridge tables.
(NB this is the part of this design I am least comfortable with as if your types of criteria can change this would require a design change each time; if you had lots of Criteria types then you could end up with too many Dims referenced by your Fact table. If either of these are true then a single criteria table might be a better solution - again linked to the fact via a bridge table)

The Search Profile Dim would have a 1:1 relationship with the Search Profile fact and so, conceptually, can be treated as the Primary Key for the fact.

On your Alert Fact table you then also include an SK that references the relevant Search Profile record.

You can then query your Search Profiles and Alerts independently but when you need to query across search criteria and alerts you can join the two fact tables via the Search Profile Dim.

A similar design pattern is discussed by Kimball in Chapter 9 of The Data Warehouse Toolkit: the section headed "Skill Keyword Bridge". Here he talks about using bridge tables or delimited values held in a single field. I guess the pros and cons of each approach depend on what your reporting requirements are and the technology you are using. The delimited strings in a field approach is probably the simplest to implement in your DB but does present indexing, and therefore performance, issues. Also, it's not that easy to present to your end-users in an understandable way via tools such as OBIEE, Business Objects, SSRS, etc. Though if your end users are all skilled SQL query writers then requiring them to write something like:
UCase( skill_list) like '% | UNIX |% OR UCase( skill_list) like '% | LINUX |%'
is obviously not an issue

Thank you, Nick, that has cleared up a lot of questions for me. I think I'll go ahead and try the implementation you have suggested, it makes perfect sense. Thanks also for such a quick response (twice).