Re: Performance Benchmarks?

ok...

retrieving cell & dimension properties slow down the request.
try to remove these options and compare the response time.

adding memory on the server can help you; more data can be cached on the
server.
specially if AS2005 AND RS2005 runs on the same server.
if you have AS, RS and SQL on the same server, use a 4Gb of RAM.
start to focus on Windows x64 and SQL x64
if your license is by CPU, focus on Dual Core CPUs.


"Greg Hess" <keadrix@xxxxxxxxxxx> wrote in message
news:eNijbSTsGHA.4444@xxxxxxxxxxxxxxxxxxxxxxx
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: 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: 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: VLDM Slow performance on Client PC
    ... from the server to the client. ... you can force the execution of the query on the server side rather the ... it creates another system process to hold the dimension in memory. ...
    (microsoft.public.sqlserver.olap)
  • 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: Sudden drop in speed after adding some records. Why?
    ... maybe your biggest dimension is too large for your server memory. ... > play with the cube, and see the results with the usage analysis. ... > Try to play with the client connection string to insure you use server ...
    (microsoft.public.sqlserver.olap)