Server side vs Client Side (PTS) Processing
-
We have a situation where it seems like the query results are processed by the server for a period of time, then returned to the client machine and processed by Pivot Table Services, before rendering to the screen. It seems to spend as much time on the client as it is spending on the server, so it makes it difficult to determine where to place our best server.
In our situation, we are using Virtual Cubes to calculate the values needed for our reporting needs. In the Virtual Cubes, almost every column is a calculated member. One of our vendors suspects that there are certain calculations that require processing in PTS instead of AS, but, I have not found anything to support that hypothesis.
We have a lot of experience with Relational DB's, but not much with Analysis Server / OLAP cubes. Is this standard for everyone or unique to our situation?
Basic Hardware Stats:
Analysis Services - 4 x 2.3 Ghz Xeon CPU's w/ 8GB RAM, 1 GBit Nic
Client (Application Server) - 2 x 2.0 Ghz Xeon CPU's w/ 4GB RAM, 1 GBit Nic
-
This was removed by the editor as SPAM
-
Hi,
Not that it will make you feel any better, but we experience exactly the same type of issue - AS doing virtually no work, and PTS doing basically everything.
As yet, I have had no indication from Msft as to how to enforce processing on AS Vs PTS.
I can write MDX queries that perform this way on the physical Foodmart-Sales cube, but we too have clients with Virtual Cubes (1 virt. sitting over 4 phys), who run into this issue on a daily basis.
Steve.
-
Hi StevefromOz,
We have had some improvement by changing the AS connection string. I believe we changed the "ClientCache" setting to be "=0" instead of the previous value. We also had to rewrite a lot of our MDX queries.
It definitely seems like there are some functions that are processed by AS and some that are processed by PTS. At this point, it seems to be trial and error to use the functions that will be processed by the server.
Edited by - samiu2 on 02/27/2003 07:43:45 AM
-
Hi Samui,
Further to this post, I also posted on Msft OLAP newsgroup, where George S replied, gist of reply was that functions used in MDX may be causing execution at the client. Definitely not the case for us, but thought it may be of use to you.
Cheers,
Steve.
Steve
Steve.
-
If you look up a doc on MSDN about using Large Dimensions With OLAP Services (I know that this is from SQL 7, not 2K, but it's a pointer at least), near the bottom of the doc it lists ADDCALCULATEDMEMBERS as one of the functions that will nearly always cause the large dimension members (and therefore processing of the same) down to the client.
Steve
Steve.
-
Thanks for all of the help! It's much appreciated.
We are experiencing better response times after our query re-writes. We still have a ways to go with performance tuning as this is our first AS project.
Cheers,
Sami
-
Can you give me a hint on the basis of your re-writes? We have started doing our set definitions at the start of the query (ie WITH SET statements) which seems to help a little, but we currently have an open MSft support issue as (we believe) the Filtering andlarge dimensions that we use are causing performance issues.
steve.
Steve
Steve.
-
We have two people working on the cubes, including me. I handle more of the administration functions. The developer is the one who re-wrote the queries.
It is my understanding that he would take his queries and run them in the sample application (standard for most people, I suspect). Whenever he had a query that didn't return within a few seconds, he would search through MSDN and look for another function that would work. He found his answers through trial and error for the most part. As I said earlier, we don't have a lot of experience with AS.
He's not here right now, so I can't give you more specific details. I'll respond when I get more details from him on some specific changes that were made.
-
Our research into this 9 months ago showed us how to set it to server-side processing; however, the MS docs stated that the setting was a 'preference' setting which was overwritable! So we found that AS nearly always prefered client-side processing. ( Must be an internal Access engine lol ).
In any case, due to our thin, distributed nature, had to scrap it's use to only local corporate. 🙁
-
Hi to anyone still reading this thread. Received (very important) information from MSft late last week regarding the "Execution Location=3" setting. If you *do not* have SP3 on the server this setting within the conxn string IS IGNORED, yes ignored. Again, another great reason to SP3 your server(s). Make sure you've sp3'd your client also! in part. the PTS sp3.
Yours in hoping our queries run faster,
Steve.
Steve.
-
StevefromOz,
Sorry for the long delay, but I've been on holiday. Thanks for letting us know about the specific connection string setting. We upgraded to SP3 at the same time that we finally started making improvements in the location of processing, so that may have helped us.
The MDX changes that we made, in order to allow faster server side processing, were the following:
1. Always Use Non-Empty Cross Joins
2. Create Named Sets / Calculated Members in the Cube, NOT MDX. If done in MDX, this would cause client side processing, in our application.
3. Create summations prior to other arithmatic events (like divide) when calculating members like YTD percent.
I hope that helps.
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply