selecting date range

I have an MDX query that I'm using in Reporting Services:

SELECT NON EMPTY { [Measures].[NumberOfClaims] } ON COLUMNS FROM (
SELECT (
[Date].[DTM02_Date].&[2003-12-23T00:00:00]
:
[Date].[DTM02_Date].&[2005-01-01T00:00:00]
)
ON COLUMNS
FROM [ClaimStatus])
CELL PROPERTIES VALUE, BACK_COLOR, FORE_COLOR, FORMATTED_VALUE, FORMAT_STRING, FONT_NAME, FONT_SIZE, FONT_FLAGS

Now this works fine... until I change the dates... such us putting in 2000 instead of 2003 in the date first date.

This cube is from two tables:
1. A Claim table (health care claim)
2. A Date table

The data table has two columns:
1. a key
2. the actual date

The claim table has a key that links to the date table's key, and the corresponding date is the claim date.

So when building the cube I just built a dimension for the date (not time dimension though), set the date as attribute, and then linked the keys together in the cube.

The problem is that I have to use the actual dates for the date range to work.

For example, the list of claim dates could be:
01-01-2000
01-01-2001

Using

[Date].[DTM02_Date].&[1900-01-01T00:00:00]
:
[Date].[DTM02_Date].&[2050-01-01T00:00:00]

won't return any rows.

What I described is a simplified version of what I'm really doing. So far everything works perfectly except when I try to select a date range.

Thanks in advance!!!

Answer this question

selecting date range

LiamD
Deepak,

That exactly what I needed to know. I tried out the query and it works find. Thanks!

One follow up question: When selecting a range for the server date when creating the time dimension... I was wondering how that may be updated in the future.

For example, right now I set the end date to Dec. 31, 2007. But when 2008 rolls around, I'm going to need to reprocess that dimension. Is there an automated of doing this Or can I just select a later date like 2010 and not have performance dragged down

Thanks Again!
AntonioP

Could you clarify whether the selected dates may not exist in the date table because the table has "holes" (i.e there are only rows for dates with data), or because they're outside the range of the table and dimension In the latter case, one approach would be take the date parameter range and convert it to the largest contained range in the dimension. Here's a simplified example for Adventure Works - empty range is not checked:

>>

select {[Measures].[Sales Amount]} on 0

from (select {

iif(CDate("1900-01-01") <= [Date].[Date].[Date].Item(0).MemberValue,

[Date].[Date].[Date].Item(0), [Date].[Date].[Date].Item(

datediff("d", [Date].[Date].[Date].Item(0).MemberValue, CDate("1900-01-01"))))

:

iif(CDate("2050-01-01") >= Tail([Date].[Date].[Date]).Item(0).MemberValue,

Tail([Date].[Date].[Date]).Item(0), [Date].[Date].[Date].Item(

datediff("d", [Date].[Date].[Date].Item(0).MemberValue, CDate("2050-01-01"))))

} on 0

from [Adventure Works])

------------------------------------------------------------------------------------------------------------------

Sales Amount
$109,809,274.20

>>
Sean McLellan 360
FYI - holes in a time dimension table are usually not a good idea and could cause problems - for example, MDX time hierarchy-related functions like ParallelPeriod() may not work as expected...
billqu
To get started, you could set up an SSAS Server time dimension, though this has some limitations:

http://msdn2.microsoft.com/en-us/library/ms174832.aspx

>>

SQL Server 2005 Books Online

Defining a Server Time Dimension

A time dimension in Microsoft SQL Server 2005 Analysis Services (SSAS) can be based either on a table in a data source view or on a date range. A time dimension that is based on a table is really no different from any other standard dimension. Its attributes are bound to columns of a dimension table just like any other standard dimension.

In contrast, a range-based time dimension is typically used when there is no separate time table to define time periods. Attributes of a range-based time dimension have time-attribute bindings, which define the attributes according to specified time periods such as Years, Months, Weeks, or Days. Because the data for a range-based time dimension is created and stored on the server instead of coming from any table in the data source, a range-based time dimension is called a server time dimension.

...

>>

Here's an article by Tom Chester on time dimensions (from the days of AS 2000 - but much is still applicable):

http://www.sqljunkies.com/Article/D1E44392-592C-40DB-B80D-F20D60951395.scuk

>>

Getting Time Right in Analysis Services

...

The Pitfalls

The lack of a Time dimension table leads to several pitfalls, some of which may not be evident until after the database goes into production:

...

>>
GConst
I'm little bit confused about the time dimension table works. In regular SQL, I simply have a lot of claim dates, but they don't follow any kind of daily/weekly pattern. Selecting a date range in regular SQL isn't a problem even if there are holes. So how would I go about fixing this for Analsysis Services Again, thanks for the help.
Claudio V.
Because there are holes in the table. There could be days or even weeks between dates. So even if, I do stay within the highest and lowest date.... I won't be getting rows returned unless I select dates that are actually in the database.

You're reply was helpful though b/c I needed to know that too.

Thanks
selecting date range

Answer this question