Range based dimension

View previous topic View next topic Go down

Range based dimension

Post  sujen on Mon Jan 02, 2012 1:39 pm

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

sujen

Posts : 2
Join date : 2012-01-02

View user profile

Back to top Go down

Re: Range based dimension

Post  ngalemmo on Tue Jan 03, 2012 12:54 pm

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.
avatar
ngalemmo

Posts : 3000
Join date : 2009-05-15
Location : Los Angeles

View user profile http://aginity.com

Back to top Go down

Re: Range based dimension

Post  cjrinpdx on Tue Jan 03, 2012 9:33 pm

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





cjrinpdx

Posts : 51
Join date : 2011-07-14
Location : Portland, OR

View user profile

Back to top Go down

Re: Range based dimension

Post  Sponsored content


Sponsored content


Back to top Go down

View previous topic View next topic Back to top

- Similar topics

 
Permissions in this forum:
You cannot reply to topics in this forum