Can you have multiple Time type dimensions in AS2005? You can certainly set
2 dimensions to type Time but in the cube I'm playing with, it doesn't seem
to be working correctly. I have dimensions of WebService,Date and Time
(where Date is Year-Month-Day and Time is Hour-Minute and both are of type
Time). I have a measure called AverageResponseTime and have set the
aggregation to AverageOfChildren but it doesn't seem to be working (i.e.
it's just summing the values). I can create a standard calculated member and
that works but I was hoping to use the new aggregation type.

The vast majority of my cubes have both date and time dimensions as they are
mainly for performance analysis and I have always modeled this using
separate date and time keys in the fact table linked to separate date and
time dimensions. I guess I want to check if that is "best practice" and also
whether MDX time aware functions will still work when there are 2 Time type
dimensions in a cube?

--
Cheers,
Jasper Smith

Hi Jasper,

I've also typically built AS 2000 cubes with separate Date and Time Of
Day dimensions, but am still in the process of migration to AS 2005 (new
AS 2005 cubes take precedence!). Anyway, there are some Time
dimension-related best practices called out in the Project REAL
technical article on Analysis Services. It seems to be endorsing the
concept of a separate Time Of Day, but maybe only one (the Date
dimension?) should be marked as being of the Time type. Will that work
for you?

http://www.microsoft.com/technet/pro.../realastd.mspx
By Dave Wickert, Microsoft Corporation
SQL Server Technical Article
Published: September 2005
...
If your application needs a drilldown that is deeper than day, consider
separating time into two dimensions. One dimension records time down to
the day level and a second dimension records the time of day.
...
Best Practice: If you use semiadditive measures, be sure that cubes have
no more than one time dimension.

There are some limitations on the use of semiadditive measures. First,
you must have a dimension marked as type Time to use semiadditive
measures. See Figure 25 for an example. The Time Intelligence Wizard
sets the attribute and dimension type properties appropriately when it
runs. If you create your time dimensions using the standard Dimension
Wizard, then you must manually set the appropriate type properties.



- Deepak

Deepak Puri
Microsoft MVP - SQL Server

*** Sent via Developersdex http://www.developersdex.com ***

Thanks for the link, very useful article. It looks like I'll need to stick
to the standard Sum aggregation and just use a calculated member to get the
average response time in order to allow hourly averages as well which is
fine. When set to average of children, it only kicks in at the month level
which makes sense in terms of the Date hierachy but doesn't give me what I
want at the day level as it just sums the values.

--
Cheers,
Jasper Smith


"Deepak Puri" <deepak_puri (AT) progressive (DOT) com> wrote