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.

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 (AT) hotmail (DOT) com> wrote

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 (AT) hotmail (DOT) com> wrote

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 (AT) hotmail (DOT) com> wrote

I would suggest to check the following:
- Are attribute relationships defined properly on the dimensions?
- If you run Profiler against the server, how long is spent in "Query
Subcube" events?
- Are the aggregations really being hit?
- Are caches being hit?
- Are there unrelated dimensions for some of these measures?
- Does each row typically have values for each of the measures or is it
often the case that some of the measures are NULL while others have data?
- If you replace .ALLMEMBERS to .MEMBERS does it help?
- If you split this into 3 separate queries (one for each measure group's
measures) do you see any difference in times?

It seems to me that 40,000 rows should really be returned much faster than
20 seconds especially after the cache has been warmed. But I also believe
that a fair amount of time would be spent by SSMS in rendering the
results... So the 16 seconds you see in Profiler is a number more reasonable
but still slower than I would expect.

Thanks,
Akshai
--
Try out the MSDN Forums for Analysis Services at:
http://forums.microsoft.com/MSDN/Sho...ID=83&SiteID=1

This posting is provided "AS IS" with no warranties, and confers no rights
Please do not send email directly to this alias. This alias is for newsgroup
purposes only.

"Jeje" <willgart (AT) hotmail (DOT) com> wrote