OK let me pose this question:

Say one had a cube with a date dimension. How would one go about
determining the min and max values in it?


we have a internally developed OLAP application serving reports that we
want to display the date range for available data. This is obviously
very simple with relational data, but not easy in OLAP...

can one interrrogate the dimension via XMLA, or is there an MDX
statement one could run?

-Eric

Hi Eric -

As long as your cube isn't too large, you can derive these values from the
cube data.

You can use filtered sets and members based upon their first and last
members to give you what you're asking for.

Something like:

with set [MonthsWithData] as
'Filter({[time].[month].members }, [Measures].[Unit Sales] <> NULL)'
set [FirstMonthWithData] as 'Head([MonthsWithData],1)'
set [LastMonthWithData] as 'Tail([MonthsWithData],1)'

member [measures].[FirstMonthWithData] as
'TupleToStr([FirstMonthWithData].item(0))'
member [measures].[LastMonthWithData] as
'TupleToStr([LastMonthWithdata].item(0))'
select {[measures].[FirstMonthWithData], [measures].[LastMonthWithData]} on
columns
from sales


The above code is for foodmart, which only goes down to month, it's just an
example. You would substitute the day level where appropriate. Also, it
will return a fully qualified hierarchical name, so you may want to do some
string parsing in those resulting calculated members.

If your cube is really large, this could potentially be slow, especially if
it's not partitioned by time. With a really large cube, you'd be better of
having member properties of the time dimension (at the day level) that denote
which are the first and last days with data. Your usual SQL processing could
update these columns accordingly when new fact data is processed. You could
then filter for these member properties.

Good luck.

- Phil


"Eric S" wrote:

Phils method is excellent for getting the first and last members that
have data. If you are just after the first and last members in the
dimension, regardless of whether they have data it is a lot simpler.

WITH

MEMBER [Time].First as 'Time.month.members.item(0).uniquename'
MEMBER [Time].Last as 'Time.Month.members.item(time.month.members.count-
1).uniquename'

SELECT
{[time].first,[time].last} ON COLUMNS
FROM sales

You can swap the .UniqueName for .Key or .Caption or even query a member
property if you like.

--
Regards
Darren Gosbell [MCSD]
<dgosbell_at_yahoo_dot_com>
Blog: http://www.geekswithblogs.net/darrengosbell

In article <49836218-4092-4DA6-8EB4-C026DDCE488B (AT) microsoft (DOT) com>,
SQLMcOLAP (AT) discussions (DOT) microsoft.com says...