Range based dimension

I need to introduce a range based dimension for salary ranges. My dimension model addresses the recruiting domain, where I have come across a requirement to report on the number of job-posts, hires count, applications counts etc. based on the salary ranges.

We get the job posting records with free-text based salary ranges in different currencies and different pay periods(per annum, per hr, per month etc).
So, any suggestion for the appropriate update on the dim model to address this requirement??

Thanks,
Sujen

In general, the grouping of some fact data based on ranges (cohorts) is best implemented as a reporting function rather than tying the fact to a group dimension. So you have an ancillary table with low and high values and appropriate attributes. You would also include some form a range group ID to allow users to define different sets of ranges.

In your particular situation, the biggest challenge would be transforming the text-based salary ranges from the postings into useable numbers.

If you need the range or group in the cube it's pretty straight forward. Just create the group dimension and add it to your surrogate key pipeline lookup. Using the advance query option of the lookup transform is very slow so you will need to write a script component.

Check out...
http://blogs.msdn.com/b/mattm/archive/2008/11/25/lookup-pattern-range-lookups.aspx