MS SQL Server OLAP - memory usage in SQL Server Analysis Services 2005 (SSAS)

Author

memory usage in SQL Server Analysis Services 2005 (SSAS)

techdummy

2006-03-17, 8:24 pm

Folks

I am running the enterprise edition of Analysis services 2005. I have a
cube that has 4 dimensions (including the date) and 2 years of test
data. (The data generated is pretty dense as it was generated by an
automated program. And we expect the rpoduction data to be this dense
as well.)

I was able to setup the cube and process around 43 million rows easily
(within about 20 minutes).

I am facing performance issues while querying this data. I am new to
this version of analysis services. When I say performance issues, I
mean, it is really slow. More than 15-20 seconds to retrieve data for a
single dimension and the corresponsing measures. I investigated
further and found that the CPU on the server spikes to 100% when I
issue a query. However, the memory used is only 55 MB. My server has 2
GB of RAM. Is there anyway to tune this or speed this up?. The cube
size on disk is less than 1 GB. So I am finding it very difficult to
accept that this is the best we can do with this.

Your help is highly appreciated.

Thanks
Techdummy

Darren Gosbell

2006-03-17, 8:24 pm

Sounds like there might be a problem with your aggregations. It sounds
like AS is having to aggregate on the fly, calculating everything from
the leaf level.

Have you designed aggregations?

If you have, are you sure that your attribute relationships are setup
correctly? There is a good article here:

http://blog.anothereon.net/ Categor...y,SQL%20Server%
202005,Analysis%20Se
rvices.aspx

on the importance of attribute relationships in AS 2005.

--
Regards
Darren Gosbell [MCSD]
Blog: http://www.geekswithblogs.net/darrengosbell

In article <1142626416.068277.71950@u72g2000cwu.googlegroups.com>,
subbu@askjeeves.com says...
> Folks
>
> I am running the enterprise edition of Analysis services 2005. I have a
> cube that has 4 dimensions (including the date) and 2 years of test
> data. (The data generated is pretty dense as it was generated by an
> automated program. And we expect the rpoduction data to be this dense
> as well.)
>
> I was able to setup the cube and process around 43 million rows easily
> (within about 20 minutes).
>
>
> I am facing performance issues while querying this data. I am new to
> this version of analysis services. When I say performance issues, I
> mean, it is really slow. More than 15-20 seconds to retrieve data for a
> single dimension and the corresponsing measures. I investigated
> further and found that the CPU on the server spikes to 100% when I
> issue a query. However, the memory used is only 55 MB. My server has 2
> GB of RAM. Is there anyway to tune this or speed this up?. The cube
> size on disk is less than 1 GB. So I am finding it very difficult to
> accept that this is the best we can do with this.
>
> Your help is highly appreciated.
>
> Thanks
> Techdummy
>
>

Darren Gosbell

2006-03-17, 8:24 pm

In article <MPG. 1e85de0e6dd5958a9898
c2@news.microsoft.com>,
jam@newsgroups.nospam says...
> Sounds like there might be a problem with your aggregations. It sounds
> like AS is having to aggregate on the fly, calculating everything from
> the leaf level.
>
> Have you designed aggregations?
>
> If you have, are you sure that your attribute relationships are setup
> correctly? There is a good article here:
>
> http://blog.anothereon.net/ Categor...y,SQL%20Server%
> 202005,Analysis%20Se
rvices.aspx
>
> on the importance of attribute relationships in AS 2005.
>
>

Sorry this is the article I was thinking about in relationship to
attributes and aggregations:

http://markiehillas.blogspot.com/20...05-aggregation-
confusion.html

--
Regards
Darren Gosbell [MCSD]
Blog: http://www.geekswithblogs.net/darrengosbell

techdummy

2006-03-17, 8:24 pm

Darren
Thanks fot your response.
Yes the aggregations are defined. Although they are the default of sum
in all cases. WE did this only to test how long it takes to build a
simple cube and see if it is viable. AS far as attribute relationships
are concerned we dont have any calculations that can be affected by the
alternate hierarchies in the time dimension. The article you mentioned
about talks about problems when you do period to date calculations,
which I have not done in this cube.

I know with Analysis Server 2000 we could control how much aggregation
we can perform, however in tis version I can only choose storage mode
and not the level of aggregations, am I right?

Jéjé

2006-03-17, 8:24 pm

good article.

I have a dimension which provides only attributes, there is no hierarchies.
So my counts are 0 except for the key attribute (50 000; on test data)

how this affect the performance?

does I have to create hierarchies on the most requested attributes? (1 level
hierarchies only)
does this will improve the performance?
does it possible to aggregates data on attributes without creating
hierarchies?

on my 3 biggest dimensions, only 1 has hierarchies. and I have a lot of
attributes! (20 to 50)

"Darren Gosbell" <jam@newsgroups.nospam> wrote in message
news:MPG. 1e85e5ad7ae317d79898
c3@news.microsoft.com...
> In article <MPG. 1e85de0e6dd5958a9898
c2@news.microsoft.com>,
> jam@newsgroups.nospam says...
>
> Sorry this is the article I was thinking about in relationship to
> attributes and aggregations:
>
> http://markiehillas.blogspot.com/20...05-aggregation-
> confusion.html
>
> --
> Regards
> Darren Gosbell [MCSD]
> Blog: http://www.geekswithblogs.net/darrengosbell

Deepak Puri

2006-03-18, 3:27 am

If you right-click on a cube measure group partition in Management
Studio and select "Design Aggregations...", the Aggregation Design
Wizard starts and allows you control the level of aggregation (of
course, attribute relationships should have been appropriately
established to generate effective aggregations):

http://msdn2.microsoft.com/en-us/library/ms180144(SQL.90).aspx[color=darkred]
Aggregation Design Wizard F1 Help (SSAS)

Aggregations provide performance improvements by allowing Microsoft SQL
Server 2005 Analysis Services (SSAS) to retrieve pre-calculated totals
directly from cube storage instead of having to recalculate data from an
underlying data source for each query.
...[color=darkred]

After designing aggregations and processing the cube, a useful new
feature in AS 2005 is that SQL Profiler can confirm whether aggregations
are used in evaluating the MDX queries being captured:

http://groups.google.com/group/micr....olap/msg/85a44
fe3915e908c[color=da
rkred]
Analyzing MDX query evaluation (SSAS 2005)

From: Edward Melomed [MSFT]
Date: Tues, Nov 8 2005 2:04 pm
Groups: microsoft.public.sqlserver.olap

In Profiler connect to your Analysis Server.
Go into Events selection pane and select "Show all events" checkbox.
You will see Query Processing category of events. You can select all
events
from it.
If you are specifically interested in hitting aggregations, select "Get
Data
From Aggregation" event.
...[color=darkred]

- Deepak

Deepak Puri
Microsoft MVP - SQL Server

*** Sent via Developersdex http://www.droptable.com ***

Darren Gosbell

2006-03-27, 3:29 am

In article <#52z3xiSGHA.1608@TK2MSFTNGP09.phx.gbl>,
willgart@BBBhotmailA
AA.com says...
> good article.
>
> I have a dimension which provides only attributes, there is no hierarchies.
> So my counts are 0 except for the key attribute (50 000; on test data)
>
> how this affect the performance?
>

Sorry for taking so long to reply, I am getting some similar behaviours
where some attributes always come up with a count of zero and I don't
know why.

I think this will have a negative impact on performance, but this is
just an educated guess, I cannot find any documentation on this.

I am assuming that if your attribute has a lower cardinality than your
dimension key, then you would want to let the aggregation designer know
about this.

For example if you have a customer dimension, facts would be read in and
stored at the CustomerId level. If you have a low cardinality attribute
like gender, with only 2 values, the aggregation designer should
consider this and possibly include aggregations on the gender attribute.
I imagine that storing aggregate for every attribute could be expensive
and lead to excessive data explosion. On the other hand if you have an
attribute for Customer Address I would expect this to have a relatively
high cardinality, possibly even a one-to-one relationship with the
CustomerId, in which case it is not an expensive operation to aggregate
all the facts at the CustomerId level for each address.

It's not obvious from the UI, but you can type in the attribute counts
if the aggregation designer does not count them properly.

> does I have to create hierarchies on the most requested attributes? (1 level
> hierarchies only)
> does this will improve the performance?
> does it possible to aggregates data on attributes without creating
> hierarchies?
>

No, you should not have to create hierarchies. Attributes are inherently
single level hierarchies. You can force an attribute to aggregate by
going into the Cube Structure, highlighting the attribute in question
and changing its AggregationUsage property to "Full"

If anyone from Microsoft or someone with some more background knowledge
is watching this thread I would be interested to know if we are on the
right track here. (Mosha, Dave, Deepak ?)

--
Regards
Darren Gosbell [MCSD]
Blog: http://www.geekswithblogs.net/darrengosbell

> on my 3 biggest dimensions, only 1 has hierarchies. and I have a lot of
> attributes! (20 to 50)
>
>
> "Darren Gosbell" <jam@newsgroups.nospam> wrote in message
> news:MPG. 1e85e5ad7ae317d79898
c3@news.microsoft.com...
>

techdummy

2006-03-30, 1:30 pm

Folks

Thanks for your responses. I was able to solve this based on all you
feedback. Now I have hit another issue. Not sure if this is a bug or I
am trying to do something that I dont know. I designed another cube -
a much larger one based on the data. When I design aggregations for
this cube and choose to aggregate to achieve 100% performance gain (or
choose the "aggregate until I click stop" option), the wizard stops
automaticaly after building aggregations for a 55% improvement in
performance. I does not go further even if I choose "Continue". Is this
a bug or a "feature"?

Sponsored Links