title: Handling a Many-to-Many Dimension when all dimensional values have 100% importance
body: <p>I'll at least try to keep this succinct.</p>
  
  <p>Let's suppose we're tracking the balances of accounts over time.  So our fact table will have columns such as...</p>
  
  <p><strong>Account Balance Fact Table</strong>  </p>
  
  <ul>
  <li>(FK)AccountID  </li>
  <li>(FK)DateID</li>
  <li>...</li>
  <li>Balance</li>
  <li>...</li>
  </ul>
  
  <p>Obviously you have an <strong>Account Dimension Table</strong> and a <strong>Date Dimension Table</strong>.  So now we can easily filter on Accounts or Dates (or date ranges, etc.).</p>
  
  <p>But here's the kicker... Accounts can belong to Groups -- any number of Groups at a given Date.  Groups are simply logical abstractions, and they have no tangible meaning aside from reporting purposes.  An Account being in 0, 1, or 17 groups doesn't affect its Balance in any way.  For example, AccountID 1 may be in Groups 38, 76, 104, and 159.  Account 2 may be in Group 1 (which has a Group Description of "Ungrouped".  Account 3 may be in seventeen groups (real example).</p>
  
  <p>As an added bonus, our users are completely non-technical.  They don't know SQL, they have no experience with relational databases, and have historically done all of their work in a convoluted Excel solution.  Right now we're building a dimensional model they can slice and filter with PowerPivot, though these Account Groups are threatening to turn an otherwise ruthlessly simple Star Schema into something complex enough that the users will balk and return to their current spaghetti solution.</p>
  
  <p>So let's look at our options...</p>
  
  <p><strong>Boolean Method</strong>
  The Boolean method is not feasible.  We have about 570,000 different accounts, but more importantly, 26,000 different groups.  This would also be a devil for end-users to filter, since they're non-technical and are relying on very simple tools to get this done.</p>
  
  <p><strong>Multiple Column Method</strong>
  In theory this could work, however, we do have some accounts that belong to 17 groups.  Again, the groups are really just logical groups -- they have no meaning, but they are required by the business for reporting purposes.  Having end-users filter out groups from 17 different columns isn't going to go over well in user-acceptance, and would likely result in users refusing to use the solution (and I don't blame them).</p>
  
  <p><strong>Bridge Table</strong>
  This count work, but we do have 26,000 different groups.  I'm not finding this to be user-friendly.</p>
  
  <p>Since I'm not liking my options, I can only assume there's a better way other than snowflaking... unless snowflaking IS the only way.  If someone could lend a hand and explain their rationale it'd be appreciated.</p>
  
  <hr>
  
  <p><strong>UPDATE:</strong> For clarification, an example I think everyone here can relate to is imagine you can list keyword skills on a resume.  They all relate to the same person, but you can have any number of skills.  The skills don't effect any of individual measures on a resume -- i.e. 'C++' isn't more valuable than 'C#' -- you can't put all the resume/skill combinations in the fact table or you'd end up double counting (or well more than double ;) ).</p>
  
  <p>I think the best I'm going to be able to do here is to create an outrigger table for groups.  I'm not a fan of it, but I think it's the only real option I have.</p>
  
  <p>So now we have...</p>
  
  <p><strong>Account Balance Fact Table</strong>  </p>
  
  <ul>
  <li>(FK)AccountID  </li>
  <li>(FK)DateID</li>
  <li>...</li>
  <li>Balance</li>
  <li>...</li>
  </ul>
  
  <p><strong>Account Dimension</strong></p>
  
  <ul>
  <li>(PK)AccountID</li>
  <li>Account Name</li>
  <li>...</li>
  <li>(FK)Account Group Key</li>
  </ul>
  
  <p><strong>Account Group Outrigger</strong></p>
  
  <ul>
  <li>(PK)AccountGroupID</li>
  <li>(PK)AccountID)</li>
  <li>Account Group Name</li>
  </ul>