Role Playing Dimensions or Role Dimension?

Recently on the SSAS Forums, there was a question posted which was titled Dimension 1 or Dimension 2. The poster eventually answered their own question. I did not particularly like their proposed solution, decided to reply back with a response, but then realized that an alternate solution would be a better topic for a blog post than a forum response. So here goes that response. The question being asked is a more general dimensional modeling topic than the specific business problem described.

In a nutshell the question being asked was the following: Given that a Country can participate in a business process in two different roles, should the Country dimension be modeled as a single dimension (i.e. Country) or as two role-playing dimensions (i.e. Primary Country and Referer Country)? Additionally, their users needed to be able to query across both dimensions independently where a question could be answered that included contributions from both UK in the Primary Role OR France in the Referer Role. The OR is the important criteria to the solution; otherwise, use a role-playing dimension.

The solution the poster proposed in that post was heavily geared toward a technical approach to resolving the reporting requirement rather than guided by the composition of the business domain. I have to confess. I’m not even sure I could explain how their approach even solved the problem but it did involve creating a dimension directly from the fact table which is almost always leads to a very unscalable solution.

What the Or reporting criteria suggests is that the business domain is composed of a single Country dimension that participates in the Billing process in both Primary and Referer roles.Since a Billing process event can be related to more than one Country, the solution is going to involve modeling a many-to-many (M2M) relationship. Using a simple matrixification approach to address the M2M relationship to reduce the cardinality of the bridging dimension and the number of records in the bridging fact table, the separate primary and referer country references become concatenated into a single column in the fact table something like what’s shown below:

The dimensional model then would take that concatenated column and create the following structure,

• DimCountryRoleMatrix – a bridge dimension with one row for each unique value of CountryRoleMatrix having a regular relationship with both Billing and CountryRoleMatrix measure groups,
• FactCountryRoleMatrix – a  bridge fact table that contains two rows for each row in the DimCountryRoleMatrix table, one for country primary role and another for the country referer role,
• DimCountry – one row for each country. The corresponding SSAS dimension would be related to the Billing measure group as a M2M relationship through the CountryRoleMatrix measure group, and
• DimRole – contain only two rows, one for Primary and the other for Referer. Like Country, the corresponding SSAS dimension would also be related to the Billing measure group as a M2M relationship through the CountryRoleMatrix measure group.

The original question how to query for UK in the Primary role or France in the Referer role now in MDX is simply the following set:

{
( [Country].[UK], [Role].[Primary] ),
( [Country].[France], [Role].[Referer] )
}