Dimension Look UP with in a range

[rveeja]

Hello,

I am building a fact table and performing look ups on a position dimension.
I am required to do a look up on a row for the given position ID and a date. The given date is compared against the effective date in the position table. The given date is not same as the effective date, but we need to return only one row which is closest to the given date.

In the position table the primary key is position_nbr, and effdt.
In the position table, there is also a column called DIM_ID that I created which is a concatenation of  position_nbr and effdt and therefore it is unique.

I have only done performing look ups for a given DIM_ID (Primary Key)  in the past.
How can I perform these type of look ups using data manager or where should I start?

Thank You
Veejar




Here is a sample Data
------------------------------
POSITION_NBR   EFFDT   EFF_STATUS   DESCR   MORE COLUMNS
-------------------------------------------------------------------------------------
10359   1997/06/30   A   Operator   ,...
10359   1999/10/01   A   Operator   ,...
10359   1999/12/01   A   Operator   ,...
10359   2001/07/03   A   Operator   ,...
10359   2002/02/01   A   Operator   ,...
10359   2003/04/06   A   Operator   ,...
10359   2007/12/03   A   Operator   ,...
10359   2010/03/01   A   Operator   ,...
16296   1997/08/01   A   Clerk   ,...
16296   1998/02/02   A   HUMAN RESOURCES   ,...
16296   1998/05/02   A   HUMAN RESOURCES    ,...
16296   1999/05/14   A   HUMAN RESOURCES   ,...
16296   1999/10/01   A   Generalist Assistant   ,...
16296   2000/02/21   A   Generalist Assistant   ,...
16296   2001/07/01   A   Generalist Assistant   ,...
16296   2002/01/01   A   Generalist Assistant   ,...
16296   2002/02/01   A   Generalist Assistant   ,...
16296   2002/04/02   A   Assistant General   ,...
16296   2003/02/24   A   Assistant General   ,...
16296   2003/04/06   A   Assistant General   ,...
16296   2004/07/01   A   Assistant General   ,...
16296   2006/02/06   A   Sr.Assistant   ,...
16296   2006/12/14   A   Sr.Assistant   ,...
16296   2007/02/15   A   Sr.Assistant   ,...
16296   2007/04/23   A   Sr.Assistant   ,...
16296   2007/08/06   A   Sr.Assistant   ,...
16296   2009/04/01   A   Sr.Assistant   ,...
42681   2007/02/01   A   Mgr, Development   ,...
42681   2009/02/20   I   Manager   ,...
43960   2007/10/26   A   Supervisor   ,...


-- This query return one row and from this row I can use the other columns as my look up values.
SELECT *
  FROM PS_POS POSITION
WHERE (POSITION.POSITION_NBR, POSITION.EFFDT) IN (
                          -- SELECT THE CLOSEST EFFECTIVE  DATE ROUNDED TO THE MINIMUM from the position table.
                          SELECT   POSITION_NBR, MIN (DATERANGE.EFFDT)
                              FROM PS_POS DATERANGE
                             WHERE POSITION_NBR = 16296
                               AND EFFDT >= '2005-11-20'
                          GROUP BY POSITION_NBR);

[starschema]

Have a read of the "Process Late Arriving Facts" section of the Data Manager documentation, specifically the "Use closest reference member" option, it should do what you want.