Performance Benchmarks?

I am looking for performance benchmarks for SSAS 2005. (I tried Google, but
couldn't find much there.) We have developed a new cube (the first use of
SSAS at my company) and performance on some queries leaves a lot to be
desired. There are conflicting views on this. I am starting to think that
the performance issues have more to do with the way the query is written
(too broad), while another person thinks that the cube is under-performing.

In the problem query we ask for a few measures from three measure groups:
sales dollars, shipped quantity, product cost, returned dollars, returned
quantity, returned product cost, budgeted sales, budgeted cases. This
information is requested for all customers (about 1200) for the past two
years by week (104 weeks). In SQL Management Studio this query takes about
20 seconds. The messages tab in the results says it is returning 40,000
rows and 8 columns.

If I change the above query to only return data for June 2006 (4 weeks) I
get about a 2 second response, and about 2,000 rows. (I left the exact
numbers at work.)

Now I'm no expert on SSAS by any means, but I'm guessing that query one will
take longer than query two because of the larger volume of data to be
returned to the client. My question is: Is the 20 seconds in line because
of the amount of data being returned, or does this smell of an issue in the
cube? Can one derive a sort of performance indicator using the rows,
columns, and time? Perhaps (rows*columns)/time?

Thanks,
Greg

PS. It is good to note that this cube only has about 3 months of data in it
(June 2005, June and July 2006), the data warehouse behind it was just
developed and hasn't been fully populated. The server it is running on
isn't lightning fast, 1.6Ghz and 1.2GB RAM. A faster server is in the
works, but from what we have been told this server should be able to handle
one developer throwing a few queries it's way.


.

Relevant Pages

  • Terminology used in Cubes
    ... I am trying to from existing normalised database structure to create a ... cube and I am looking for some basic descriptions of the terms used in ... SSAS 2005. ... detail than an developer overview. ...
    (microsoft.public.sqlserver.olap)
  • Re: MDX query mapped to a pivot table
    ... I am assuming that you are using SSAS 2000. ... profiler to see what query is submitted by PTS to SSAS. ... (part of cube browse in Management Studio) ...
    (microsoft.public.sqlserver.olap)
  • RE: Troubleshooting Execution Location
    ... It's much faster on the server. ... If I run the query once via HTTP, then I change the date range and run the query again, it's almost instantaneous the second time. ... have you made sure that each Distinct Count measure is in a cube ... the axis that I think would benefit the most from aggregation doesn't show up. ...
    (microsoft.public.sqlserver.olap)
  • Re: Timeout problems due to huge Fact Table
    ... I've also experienced very poor performing queries that the cube itself has ... of dimensions & with 'materialize' set on the relationships in the dimension ... to fill your cube you are using a very complex query instead of a table. ... level and adding required table in the database. ...
    (microsoft.public.sqlserver.olap)
  • Re: Risk Cube
    ... The 20 was produced by a query. ... This would have to change for each cell in your cube. ... I would like to create a cumulative Risk Cube. ... Exposure Value, Exposure Count ...
    (microsoft.public.access.forms)
Loading