Vidas Matelis Analysis Services Blog

Last week I had time to test if SP2 fixed SSAS database processing problem. On the databases that had a lot of partitions processing task sometimes would get “stuck” – there would be no CPU activity and processing would never get completed. Unfortunatelly I have to report that SP2 did not fix this problem – for me processing routine failed from the first attempt. But the good news is that Microsoft workaround for this problem on SP1 works on SP2. You have to change SSAS server parameter ThreadPool\Process\MaxThreads from default value of 64 to 150. I have tested my processing routines with value 150 and so far everything worked as expected.

Note: 

Found blog entry from PSS SQL Server Engineers that describes this problem and how to solve it:
http://blogs.msdn.com/psssql/archive/2007/01/16/processing-appears-to-stall-or-become-sluggish-on-multi-processor-machines-running-analysis-services-2005.aspx

Note 2008-Nov-07:

I am finding this issue on majority of servers I am dealing with. But the number MaxThreds could be different between machines depending on the configuration. I found from my experience that value 150 is OK for servers with up to 8 CPU cores. But when server has 16 CPU cores, I had to use value 200. In any way, I recommend that you follow advice from PSS SQL Engineers and sum values of Threads:Processing Pool Idle Threads, Threads:Processing Pool Busy Threads, and Threads:Processing Pool Job Queue Length performance counters and then add 10. You might need to do that a few times, but eventually you will come up with number good for your specific machine. In my experience, after setting new MaxThreds number I had to restart Analsysis Services server, it did not wake up by itself as described in their article.

Last week I answered question in MSDN Analysis Services forum thread that at first appeared to be very simple. Somebody asked how do you control access to SSAS cubes if information about users and groups is inside SQL Server table and not in the Active Directory. I answered that you cannot do that. This was my understanding from reading Books Online and various articles. I was sure that Microsoft SQL Server Analysis Services 2005 works just with integrated security.

Read the rest of this entry »

In my past blogs I showed how to use SSIS package to process dimensions, cubes or build aggregates. I am slowly rebuilding my existing packages thanks to points from Darren Gosbell and Jess Orosz. I found that using CaptureXML method is more convenient for me and it is definitely faster.
Read the rest of this entry »

Recently I posted a blog entry on how to process all dimensions in a single Analysis Services 2005 database using SQL Server Integration Services (SSIS) package. Here I’ll add code to this package that will allow you to process whole database, cubes, measure groups or partitions.

Read the rest of this entry »

Years ago, while I was using Analysis Services 2000, I had a strange problem. I migrated one of the cubes to be loaded from a SQL Server View instead of the actual table, and I noticed that the cube size almost doubled. It took me some time to find what was causing this. My original table had a few dozens fields with type decimal(19, 2). They were loaded into analysis services database with type double. After I migrated my fact table into this view, I also migrated some calculations. Read the rest of this entry »

After I published blog entry about a  SSIS package that processes dimensions, I received a suggestion from Darren Gosbell (his blog is here) that instead of building XMLA code by concatenating strings, I  should use the  CaptureXML option from an  SSAS server object and then process dimensions using dim.Process method and execute XMLA using ExecuteCaptureLog routine.

Read the rest of this entry »

Vidas: My next blog entry contains updated code !

It is quite often that I have to process all the dimensions in a database. I like to use scripts for that. Here I have step by step guide on how to create an SSIS package that processes all dimensions in one database.

Read the rest of this entry »

With SQL Server SP2, Microsoft added an Aggregation Manager utility to its samples. It is not installed by default, and has to be downloaded separately from http://www.microsoft.com/downloads/details.aspx?familyid=e719ecf7-9f46-4312-af89-6ad8702e4e6e&displaylang=en, file SqlServerSamples.msi

This utility allows you to see existing aggregates, and allows you to easily add aggregates manually or from a Query log. Do not miss this one!