Re: Performance Benchmarks?

Hi,

first... 20 seconds its on warm cache or cold cache?
what is your test query?
have you created aggregations?
do you use MOLAP cube or ROLAP?
do you have calculated measures?

second... 40 000 rows is big and unsuable for most of the users.
also most of the applications display the records page by page
returning the data is not so long, the delay come from the rendering
process.
you have to train your users to focus on the question and not focus on the
raw data.

there is no formula to anticipate the response time.
there is too many things to consider like the CPU, memory, aggregations,
security, etc...

good luck to convince your users to change their mind :-)


"Greg Hess" <keadrix@xxxxxxxxxxx> wrote in message
news:O4xh9cRsGHA.4380@xxxxxxxxxxxxxxxxxxxxxxx
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

  • Re: MDX very slow
    ... from what I understand, you query your cube to extract all the content of the cube, including leaf level and aggregated content into a CSV file... ... there is a lot of text to extract + a lot a calculations to do. ... I think your users will load this file into another database, so send them only the detailed content and they aggregate as they need, and sending the detail is easy to do with a simple SQL query into your source database. ... > Would it help if I add more aggregations to the cube? ...
    (microsoft.public.sqlserver.olap)
  • Re: Better approach for common business problem ? (long post)
    ... have turned off query logging on the server - with this many subqueries, ... especially if you are partitioning by month. ... When you reprocessed the cube to include the new partitions, ... However, as we already established when looking at your aggregations, it's ...
    (microsoft.public.sqlserver.olap)
  • Re: calculated measure
    ... Optimize Query Performance with a Derived Measure ... are stored in the cube file, as we have mentioned, they typically mean ... more efficient query processing. ... calculated prior to the creation of aggregations. ...
    (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)