Re: Performance Benchmarks?

Thanks for the response Jeje. For warm vs cold cache it doesn't seem to
matter, I can run the query, then run the same one again and get the same
performance.

Here is my test query. It was originally generated by the Query Builder
when creating a Reporting Services report.
SELECT NON EMPTY { [Measures].[SHIPPED QUANTITY], [Measures].[PRODUCT COST],
[Measures].[SALES DOLLARS],
[Measures].[CREDIT DOLLARS], [Measures].[RETURNED PRODUCT COST],
[Measures].[RETURNED QUANTITY],
[Measures].[Budget Cases], [Measures].[Budget Sales Dollars]
} ON COLUMNS, NON EMPTY { (
[Dim_Customer].[Category_Hiearchy].[NAME].ALLMEMBERS *
[Delivery_Date].[Fiscal_Calendar].[FISCAL WEEK].ALLMEMBERS
) } DIMENSION PROPERTIES MEMBER_CAPTION, MEMBER_UNIQUE_NAME ON ROWS FROM
[Sales_Cube] CELL PROPERTIES VALUE, BACK_COLOR, FORE_COLOR, FORMATTED_VALUE,
FORMAT_STRING, FONT_NAME, FONT_SIZE, FONT_FLAGS

I have created aggregations (Partitions tab in cube design). Using MOLAP.
We do have calculated measures, but for the purpose of this testing I have
not been using them. The consultant who is helping me on this project
suggested leaving them out to help us focus in on the problem.

I agree that 40,000 rows of results is too large. This query is supposed to
feed a report (SSRS) that uses drill-down. The initial view is to show the
customer categories (about 20) with the ability to drill down to the
customers. And the time dimension on the top with the ability to drill down
to the level needed (year, quarter, month, week). Of course this report
currently takes several minutes to display when using BI Visual Studio,
first is the query that hits the server, then there is the report rendering
which hits my workstation. On top of that, half the time it ends up in an
"out of memory" error (workstation is 2.8Ghz with 1GB). I think part of the
answer is using drill-through reports instead of a drill-down one.

The person who thinks the cube is under-performing is the consultant who
helped build the data warehouse and cube. My boss agrees with him to a
certain extent. The consultant is experienced with BI and the use of
SSAS/SSRS. He is currently off-site, supporting me via email. For the last
couple of days I have been doing extensive testing on the dimensions,
rebuilding the time dimension (where he thinks the problem is), and building
a test cube with a subset of the available dimensions. My limited
interpretation of the results is "the more data to return, the longer the
query takes." If I run the same query as above, but filter it for June 2006
it runs in 5 seconds in SS Management Studio. A Profiler trace of that
query shows that the server was only working on the query for 2 seconds.
The query above (with no filters) shows 16 seconds of activity in Profiler.

Do you have any suggestions for further tuning? For the most part the
performance on focused queries seems acceptable to me. I need to find a way
to validate that and then convince the others.

Thanks,
Greg



"Jeje" <willgart@xxxxxxxxxxx> wrote in message
news:%23VLiwjSsGHA.148@xxxxxxxxxxxxxxxxxxxxxxx
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: Tips on domain aggregate replacements
    ... This already is a split db, but the users are 400 miles away from the server hosting the data:) They are all running the same copy of the FE locally on a single terminal server via TS/RDP sessions. ... It doesn't seem to have much impact on performance; the report takes just about as long to run if they are all logged in as it does when I am logged on testing it at night. ... I would do DSums from the controls on the report on the data returned by the query. ... The biggest offender is a certain report that needs to Sum a particular complex total for each of the next twelve months (the DSum in VBA was a sideline to this issue) and present these as items on each line of output. ...
    (microsoft.public.access.modulesdaovba)
  • Re: Performance Benchmarks?
    ... Are attribute relationships defined properly on the dimensions? ... If you run Profiler against the server, how long is spent in "Query ... I have created aggregations (Partitions tab in cube design). ...
    (microsoft.public.sqlserver.olap)
  • Re: How to make your report run faster
    ... "WHERE SITE_ID IN depend on each query. ... after main report or same time? ... >> - The DB server is Oracle. ... > When you use Reports embedded in a subreport control and the ...
    (microsoft.public.access.reports)
  • Re: Sudden drop in speed after adding some records. Why?
    ... setup the log query interval to 1 in your servre properies to log every ... play with the cube, and see the results with the usage analysis. ... server process time takes only 5 seconds, ... Try to play with the client connection string to insure you use server side ...
    (microsoft.public.sqlserver.olap)
  • Re: Dates in Report
    ... How about creating a new query that joins the MonthNums ... >started, ended, and the server name. ... >span they would like for the report. ... The query behind the report has a field that sums ...
    (microsoft.public.access.reports)