a row value between two dates

hi,

i have a resultset as ( sample )

id startdate enddate value

1 01.01.2007 10.05.2007 20

2 04.01.2007 12.04.2007 40

3 07.01.2007 09.06.2007 30

.

.

i need an olap resulset of the query : value between startdate (xx.xx.xx) and enddata (xx.xx.xx)

how am i supposed the get it on SSAS

2 different dimensions? mdx ?

plz help i could not find a resolution..

thx in advance

Keep in mind I have worked with this for less than a year, so take it for what it's worth...

It looks like you are still in a relational world - the MDX world is a bit different. You would have to have a fact table that holds the value and the foreign keys for your dimension table(s), which contain your dates. So, your schema would look like:

Code Block

fact_values

-

id [business key]

start_calendar_key

end_calendar_key

value

dim_calendar

-

calendar_key

date

and the contents of the tables:

Code Block

fact_values

-

1 20070101 20071005 20

2 20070401 20071204 40

3 20070701 20070906 30

You would create a single dimension (Calendar), include it twice in your cube (once for start date, once for end date - see "Role Playing Dimensions" - http://technet.microsoft.com/en-us/library/ms174487.aspx) and a single measure group based on your fact table. Do a sum on the value.

And your MDX would do something like:

Code Block

Select [Measures].[Fact Value] on 0

From [Cube Name]

Where ( { [Calendar Start].[Date].&[YYYYMMDD] : [Calendar Start].[Date].&[YYYYMMDD] }

, { [Calendar End].[Date].&[YYYYMMDD] : [Calendar End].[Date].&[YYYYMMDD] }

)

I think this is what you were after... I'm sure there's a hundred ways to model this out -

Hope this helps,

John