Re: Performance - Best Practices

Jeje,

First, thanks for your response. I always see your activity on the
boards!

I should have mentioned most of what you asked...

I am using AS2005; x32; currently I am using Usage Based Optimizations,
but I am not getting much better results than I had with the standard
aggregation wizard.

And I have a ton of calculated members, and a lot of them build on each
other.

I have not heard about disabling the hyperthreading. What are the
issues here? Could it make a drastic difference?

Any other ideas?

Thanks again for your response.

Jeje wrote:
do you use AS2000 or 2005?
do you use x64 or x32 server?
have you used the usage based optimization wizard to add aggregations?
do you have calculated members? calculated sets?

We have a server with 16Gb, Windows x64, SQL 2005 x64, 2 * Dual Core Opteron
for the moment only 2 years of history, in all my partitions in all cubes I
have 250 millions of rows by year (biggest fact contain 50 millions / year,
and the cube in front of this fact provide standard SUMS and DCount!)

the response time is excellent, the first access is allways slow (cold
cache), but I can drill from years to days in seconds, and add filters and
the response time still good.
I'm playing with 2 dcount measures + a ratio between these 2 measures +
ratio between 2 sums.

The memory is shared between SQL, AS and reporting services on this server,
and AS don't use more then 2Gb today (but when we'll be in production with
more historical data the memory usage will be higher)

in your case, try to disable the hyperthreading, sometimes this feature
produce bad results.


"Todd" <toddkitta@xxxxxxxxx> wrote in message
news:1153342986.762069.317490@xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
Hello,

I am having some pretty major performance issues with a cube I've
built. My goal for this post is to give specifics related to the data
and Analysis Services hardware to try and get some feedback from the
community on whether or not I am having valid expectations
performance-wise.

Hardware:

-Two dual-core Xeon 2.8GHz processors
-3GB RAM

Data:

The data is retail transaction data which is at the line item level.
The most recent years have 50-60 million rows and I am partitioning by
year. In addition, each transaction has related discount and tender
(payment) data. The tender data has approximately 15-20 million rows
per year while the discount data has approximately 10-15 million rows
per year.

In my cube, each of these tables have their own separate fact group. I
am pulling data from all three fact groups to create quite a few
calculated members which build on each other to finally arrive at
calculations such as GAAP Sales, sales for a particular product type,
etc.

I believe I have my hierarchies and relationships set up correctly in
my dimensions. In addition I have aggregations designed on each
partition at about the 40% level.

Am I having reasonable expectations that data should be able to come
back fairly quickly? I have some reports which take 10-15 minutes that
bring back quite a lot of data for the whole company across the last
two years. However, even some simple slicing and dicing in the cube
browser can be slow. What is interesting to me is that even if I drop
my time dimension on the rows axis before adding any measures or other
dimensions, it can take 20 seconds or more to respond. All the while
when reports are running or when I am browsing the cube myself, the CPU
(all 8 in Task Manager [dual dual-core processors]) are spiking at
100%.

While I believe I can get some good responses from MS folks and MVPs
for my own benefit, I think any thoughtful posts will benefit the
community as a whole by setting performance expectations and pointing
out potential pitfalls. I have found that there are not a ton of good
resources out there on performance, so I appreciate any tips anyone can
offer.

Thanks!
Todd


.

Relevant Pages

  • Re: Cube fit in hypercube
    ... Greg Huber also responded, and was kind enough to edit his response for ... "Rupert problem" (so named because of a certain Prince Rupert who long ... -is- the optimal cube in a unit tesseract. ...
    (sci.math)
  • Re: Performance - Best Practices
    ... have you used the usage based optimization wizard to add aggregations? ... I am having some pretty major performance issues with a cube I've ... calculated members which build on each other to finally arrive at ... In addition I have aggregations designed on each ...
    (microsoft.public.sqlserver.olap)
  • Re: Cube fit in hypercube
    ... I emailed a copy of my previous response here to Terry J. Ligocki. ... shown to be a root of the last polynomial given in e-mail below. ... problem (fitting a maximal size m-dimensional cube inside an n-dimensional ... to guess at some patterns, the reasons for these patterns, and lower bounds ...
    (sci.math)
  • Re: Calculated member and Calculated Cells
    ... Nearly every cube will have some Calculated members. ... Calculated cells on the other hand you do not see all that often. ... usually think of them as conditional overrides. ...
    (microsoft.public.sqlserver.datawarehouse)
  • Calculate member disapper from cube from client m/c using OWC or Excel
    ... I have one measure in cube which are calculated members build using ... Excel Library function. ... It disappears when client m/c access the cube using OWC or Excel. ...
    (microsoft.public.sqlserver.olap)
Loading