David Beavonn


I have a general comment and then a question related to Analysis Services 2005. It seems to me that AS2K (the previous version) scaled out better - because a significant amount of CPU-intensive calculation was done on the client machine. This was especially nice when user queries were:

  • Poorly formed / unreasonable / just-plain-stupid
  • Very intensive but we-must-have-the-data

In the case of these types of user queries, the client machine's CPU would become swamped but the Analysis Services (AS2K) server could remain responsive in serving data to other users.

In Analysis Services 2005, however, a couple of bad user queries can quickly lock up the OLAP cubes tighter than Alcatraz. (Even when SSAS is running on a high performance 64 bit server.) It is especially bad when some partition update processing is trying to commit at the same time.

I was hoping the performance guide would discuss some ideas on how to scale out Analysis Services 2005 to a large number of users (ie. 10s or 100s). Lets face it, if you get a lot of users hitting these OLAP cubes then you can bet a good percentage of them won't know the difference between a reasonable and an unreasonable query.

Any thoughts or references to documention about this Are there ways of limiting the negative impact that novice users have on the queries that other people are trying to run


Thanks in advance, David





Re: Analysis Services Lock-ups

Darren Gosbell


You are right, AS 2005 has centralized all of the calculations and as a result may not scale out as well as AS2K.

AS 2005 is pretty much version 1 of a new architecture and there is not too much that can be done about this.

There is a Timeout option which you can add to the connection string to force queries to timeout after a given amount of time, but this could be hard to administer.

The only other option I can think of is that you can synchronize a database across more that one server and set them up in an NLB cluster, but this would be an expensive option if you were only adding the second server in order to be able to cater for runaway queries. Even then I am not sure if the NLB software is smart enough to not still have some clients being "locked out" by a runaway query.







Re: Analysis Services Lock-ups

David Beavonn

Thanks for the reply. What is an NLB cluster

We have a growing set of mission-critical, canned SSRS reports that read data from our new SSAS cubes. These reports are developed within the I.S. department and have well-defined (pre-defined) data requirements. In the very least I'd like these reports to remain available and responsive. Does anybody have ideas on preventing general OLAP users from being able to negatively imact our canned reports

Could I back up the entire SSAS database after nightly processing and re-deploy it to a different SSAS server which only canned reports have access to Does anybody have experience scaling SSAS in this way

Thanks, David







Re: Analysis Services Lock-ups

Darren Gosbell

NLB - Network Load Balanced

You can set up SSAS in a configuration similar to a web farm where you can have mulitple SSAS servers all with an identical copy of the same database. I think the default is that requests are just handled in a round robin fashion, with each node taking turns at servicing an incoming session. There may be 3rd party solutions that are more sophisticated and allocate connections based on the load that each of the servers is under, but I'm not familiar with anything like this myself.

You could do a back up and re-deploy. SSAS actually has a built-in feature called synchronization which does this, see details here http://msdn2.microsoft.com/en-us/library/ms174928.aspx.






Re: Analysis Services Lock-ups

David Beavonn

Thanks for the help. Do you know how licensing would work in this kind of environment with sychronized SSAS databases

I'm thinking that the extra SSAS services should be free. Users are telling me that these new SSAS cubes max out at 2 or 3 concurrent users. Depending on the types of data they are pulling, they are constantly locking each other up.

(No, don't blame my cube design. I was already following those best practices in Oct '05. )






Re: Analysis Services Lock-ups

Darren Gosbell

No, I'm by no means an expert on licensing, but in this case the extra instances of SSAS would require full SQL Server licenses. That said you should be getting way more than 2-3 concurrent users. I though we were talking about scaling out to 100's of users

(No, don't blame my cube design. I was already following those best practices in Oct '05. )

Are you saying that your design matches the best practices that were being promoted in Oct '05 or the ones that are being promoted now Back in '05 Microsoft was saying to put everything into one big cube and let SSAS sort it out, now they are saying that we are better off breaking off logically related groups of measure groups into separate cubes.

Unfortunately sometimes the perfect logical models do not perform well and you may need to compromise on some aspects of the design in order to achieve your performance requirements.

  • Do you have any idea where the bottlenecks are
  • Is the server disk, cpu or memory bound
  • Do you know if it is specific calculations that are slow (some of the calcs added by the time intelligence wizard are not optimal - searching on this forum should turn up some helpful hints if this is a potential issue)
  • Have you tried applying usage based aggregtions
  • Do you have many parent child dimensions





Re: Analysis Services Lock-ups

David Beavonn

I was following the current best practices back then. (Aren't I modest. .) I never bought into the silly "supercube" approach which attempts to gather together the entire warehouse into a singularity (single .cube file & related version control). My concerns were primarily from a software development perspective. Designing, testing, and maintaining something this monolithic is impractical. Every design change would involve a tremendous amount of regression testing and tremendous risk. In general, most software development involves creating small things and re-using them to make bigger things. In addition to these abstact software development concerns, I had some practical concerns that are related directly to OLAP. For example, there are certain design issues involving cube-dimensions (visibility, agg design, etc.) which should definitely NOT apply to all measure groups in an entire data warehouse. I remember having this discussion on the Yukon Ascend forums with Mosha when he was still recommending it. It would be nice if those threads were available here...

We want to be able to scale out but SSAS is very bound up on both CPU and data processing concurrency problems. Normally 2 or 3 concurrent users is all we can get (ie. users running queries at exactly the same time) .

In general our CPU problems occur in queries when dicing calculated members on large dimensions and also when using time intelligence. SP2 helped a little bit with time intelligence - but query execution still reverts to cell-by-cell evaluation when the MDX query optimization engine gets a little confused. I expect these types of execution plans are a fall-back and are unavoidable at times. The main problem that I'd like to solve is this architecture which forces everyone else to suffer along with the user who is running the demanding query.






Re: Analysis Services Lock-ups

JesseO

Interesting thread.

We're experiencing the exact same issues over here - it's nice to know we're not alone in the behavior we're seeing.

We process data every hour (both fact and dimensional) as it's a mandatory business requirement (no if's, and's, or but's). Two of these dimensions are in excess of two million members so they take a couple minutes to process. To make matters worse, our aggregations are mainly flexible rather than rigid - data floats around a lot. Our total size is appx 500GB.

At any given time, within a minute we have about 10-15 users querying the cube. We've found that a single user can throw everything out of wack, especially when processing is going on.

We looked at synchronization but were not impressed by the performance. Since our aggs are mainly flexible a big chunk of files have to be moved.

I'm curious as to how people are scaling out to handle complex queries and rapidly changing data.





Re: Analysis Services Lock-ups

Darren Gosbell

Have you had a look at the ForceCommitLock setting. I did a post about this recently here http://geekswithblogs.net/darrengosbell/archive/2007/04/24/SSAS-Processing-ForceCommitTimeout-and-quotthe-operation-has-been-cancelledquot.aspx

With the default setting though you should not see any more than 30 seconds added to the front of the query where it is waiting for the pending commit lock to be released before it even starts. But maybe if you get enough queries queued up that it is thrashing a bit after the lock is released. It would be interesting to run some profiler traces and see if there is a definite correlation here.






Re: Analysis Services Lock-ups

BIGuy

We are having some success with distributed cubes. It ultimately doesn't help with the final version of this problem - tons of people with bad queries will still bring the system down - but I think is better than round-robin nlb servers because 1) cube processing is cut down significantly, and 2) for few users they see a benefit to the scaling vs no beneift with nlb.

For #2, what I mean is that if you have a 60-second MDX query, and have 6 servers it will take maybe 10 seconds (assuming perfect spread, we are seeing 'almost perfect' spread in practice, so its close enough for now) for a single user - and if you had 6 servers with identical copies of the cubes when there aren't tens or hundres of users that single query still takes 60 seconds. Of course this is also mitigated by how you spread the data - if it is by week or month and most queries are last 5 days only, then the other method would be better as in a distributed environment these would all go to a single server.

Not a final answer, just one more piece of the puzzle.

Oh - and we had so many problems with data transmission across bad corporate networks and slow laptops with low memory that even in MSAS 2000 we had to basically disable client side processing anyhow - it wasn't practical at *any* of the sites we've developed for! We used provider string parameters to force processing to the server.





Re: Analysis Services Lock-ups

Stefoon

Hi,

I'm a BI consultant. In recent days I did a migration from SQL Server Analysis Services 2000 to 2005 and did completely redesign the cubes by following the guidelines promoted in Oct'05. I was not really amused reading the exact opposite in the olap best practices in March 2007. This sounds really like a kind of joke.

I am very angry about the situation and NOT satisfied by the product SQL Server 2005 at all. And none of the "cool guys" from MS really seems to have an opinion about the paradigm change and about all the still existing bugs, especially in SSAS. Maybe they discovered those bugs in their software and were to lazy or simply not able to solve them. Then they decided to recommend a completely other architecture. Maybe the bugs would not occur any more and all the problems are solved

I'm not sure if they have only Micky Mouse cubes there in Redmond containing 2 rows of data to test their software, but maybe this is the case. But this is a very very bad style of software development and delivering.

This is just my opinion after about 1 year experience with SQL Server 2005. (having about 6 years BI experience with SQL Server 2000)

Stefoon