Help needed linking into Time Dimension

Hi All,

Need some help and I'm not sure how to proceed on this one.

We have a table (tblState) which has the following layout:

PersonID (PK), LifeID, State, StateDate, EndDate

1, 1, 'Gatewy', 2000-01-01 00:00, 2001-01-01 00:00

The PersonID will appear more than once as that person will have more than one state and or life during their time with our company e.g:

PersonID (PK), LifeID, State, StateDate, EndDate

1, 96, 'GATEWY', 2000-01-01 00:00, 2000-04-25 09:37

1, 1, 'Completed', 2000-04-25 09:37, ''

Where the EndDate is null, this is because the person is still in that current state.

I need to link both the StartDate and EndDate into our time table, which has been setup by Analysis Services with a 10yr range.

When I try and map the StartDate onto the PK_Date, all is OK and seems to be OK, but when I build the cube and select a date from my time heiarchary, the number of records doesent shrink like I would expect with it only looking for people who have had a StartDate >= the date selected.

If I run a query through QA:

select * from dbo.tblstate S

INNER JOIN WDUKCube.Time T on S.StartDate = T.PK_Date

Where PK_Date BETWEEN '2000-01-01' AND '2000-12-01'

then this returns a few records, but the cube doesent, even though it's the same data source.

Does anyone have any suggestions they could give me ?

Assuming that you're using AS 2005: under the Dimension Usage tab for the cube, ensure that the correct relationship has been established between your Time dimension and Measure Group for tblstate (including the Granularity and Dimension and Measure Group columns).|||

Thanks Deepak

Should have said I was using AS2005...Whoops.

In my measure group I only have a count of PersonID for tblState, as everything else is sliced and diced based on the PersonID.

In the Dimension Usage I've created a referenced link on PK_Date and StartDate, as I can only link to one column.

Confused...