MS SQL Server OLAP - determin time range of a cube....

Author

determin time range of a cube....

Eric S

2005-09-20, 1:24 pm

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

SQL McOLAP

2005-09-20, 1:24 pm

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 & #91;FirstMonthWithDa
ta] as 'Head(& #91;MonthsWithData],
1)'
set & #91;LastMonthWithDat
a] as 'Tail(& #91;MonthsWithData],
1)'

member [measures].& #91;FirstMonthWithDa
ta] as
'TupleToStr(& #91;FirstMonthWithDa
ta].item(0))'
member [measures].& #91;LastMonthWithDat
a] as
'TupleToStr(& #91;LastMonthWithdat
a].item(0))'
select {[measures].& #91;FirstMonthWithDa
ta], [measures].& #91;LastMonthWithDat
a]} 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:

> 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
>
>

Darren Gosbell

2005-09-21, 7:23 am

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_do
t_com>
Blog: http://www.geekswithblogs.net/darrengosbell

In article <49836218-4092-4DA6-8EB4- C026DDCE488B@microso
ft.com>,
SQLMcOLAP@discussion
s.microsoft.com says...[color=darkred]
> 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 & #91;FirstMonthWithDa
ta] as 'Head(& #91;MonthsWithData],
1)'
> set & #91;LastMonthWithDat
a] as 'Tail(& #91;MonthsWithData],
1)'
>
> member [measures].& #91;FirstMonthWithDa
ta] as
> 'TupleToStr(& #91;FirstMonthWithDa
ta].item(0))'
> member [measures].& #91;LastMonthWithDat
a] as
> 'TupleToStr(& #91;LastMonthWithdat
a].item(0))'
> select {[measures].& #91;FirstMonthWithDa
ta], [measures].& #91;LastMonthWithDat
a]} 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:
>

Sponsored Links