Hi

I have a query that is based on a time dimension as follows.
[Day of week]
- [Hour]
- [Minute]

as the design of the levels.

I have to run a query based on different sets from this dimension (eg: Mon
8:30 to 12:00 + Tues 9:00 to 13:00 + Thurs to Sat 8:45 to 13:15) which will
be run for a certain date range.

Firstly, For ease of MDX we created the dimension as one dimension rather
than day and time. (With keys we can pinpoint a minute on a day rather than
having to do crossjoin sets). WOuld you agree with this architecture.

Secondly, when it comes to actually querying the data, does the standard MDX
keywords have any advantage over specifying a time range: eg. Would there be
any difference in the following two queries and which would be quicker (if
anyone has had a similar problem before)

Sum([Time].[Minute].[8:30]:[Time].[Minute].[8:59])
or
Sum([Time].[Minute].[8:30]:[Time].[Minute].[8:30].Parent.Lastchild)

Thanks

Hi Mike,

Regarding your first question, I would prefer to split Days, Hours and
Minutes into separate dimensions. Assuming your Day of Week level has 7
members, that makes 7 * 24 * 60 = 10080 members on the dimension, which is
not massive but certainly much larger than three dimensions which have 7 + 24
+ 60 = 91 members in total. There are several reasons I can think of why
performance might be better in the latter case:
- Since all dimensions are held in memory on the server, the smaller the
dimensions in your database are, the less memory is used. This means more
memory is available for other purposes, eg the cache. If you intend to use
dimension security on this dimension then this becomes even more important.
- The way AS2K downloads all or part of dimensions onto the client means
that larger dimensions usually perform worse, simply because more data needs
to be transferred to the client.
- You have more flexibility with aggregation design. For example, if you
wanted to run queries like "What percentage of sales were made between 9:00am
and 11:00am" which don't involve day or minute, you are much more likely to
be able to build an aggregation to help. Taking a look at the query you
mention below, creating two levels on your minutes dimension (minutes and
quarter-hour intervals, the first being the leaf and the second having
members [00 to 14], [15 to 29], [30 to 44] and [45 to 59]) and only enabling
aggregations at the higher level would help even more.

On the other hand, as you've noted, it makes the MDX a bit more complex and
the cube somewhat more user-unfriendly.

Regarding your second question, there shouldn't be a noticeable overhead
associated with using MDX functions instead of specifying members explicity.

Overall, if you haven't read it then you should definitely read the Analysis
Services Performance Guide, which you can find at:
http://www.microsoft.com/technet/pro.../ansvcspg.mspx
....which will explain about aggregations, memory usage etc in much more
detail. Partitioning and aggregation strategy are always important, and in
your case my feeling is that the correct aggregation design could make a
dramatic difference to query performance.

Regards,

Chris


"Mike" wrote: