Vidas Matelis Analysis Services Blog

This weekend I had some time to play with SQL Server 2008 (Katmai) June CTP, and I am posting here my first impressions (focus on Analysis Services).

Installation

As a first attempt I tried to setup SQL Server 2008 on the computer that already had SQL Server 2005 default instance installed. I wanted to install SQL 2008 as a named instance and compare SQL 2005 and 2008 side by side. After answering few standard questions I got message that because SQL Server 2005 was found on the machine, SQL Server 2008 cannot install client tools. I proceed with installation without client tools. After installation I tried to use SQL Server 2005 BIDS environment and connect to Katmai instance, but somehow that did not work. Then finally I decided that maybe I should read readmesqlkatmai.htm file. Found that side by side installation is not supported. From readme file: Read the rest of this entry »

Found this interesting presentation online “SSAS Implementation Best Practices“. Presenter was: Nicholas Dritsas, SQL Server Product Group Customer Adivsory Team. It is PDF document that contains power point sildes.

My notes from 2007 Microsoft Business Intelligence Conference:

• SP2 has a lot of performance improvements for processing and querying. Upgrade ASAP.
• Attribute relationship is important. I have heard that at least 10 times in different sessions.

Read the rest of this entry »

Just came back from Microsoft BI conference. I would say it was better than I expected and most likely I’ll come back next year. My experience: 

• There were not enough technical sessions. That is the biggest complaint from my side to conference organisers.
• I wish they handed out session slide print-outs before each session.
• There were very good chalk talk and break out sessions that made it all worth while. But I do not believe they were recorded on DVD. I hope they will do this next year.
• I met a lot very interesting people. Many of them were from my blog roll list.
• I have heard many interesting stories on how others are implementing Analysis Services projects and their solutions to different problems.
• Microsoft employees were available to answer questions in partner pavilion. I liked that very much. I had a few consultations with them that were very very helpful for me.

There is new (I believe) paper published by Microsoft today: 2007 Microsoft Office System Business Intelligence Integration with SQL Server 2005 here:

http://www.microsoft.com/sql/techinfo/whitepapers/sqlplusofficebi.mspx

Published date is today, but internally document says Jan 31th, 2007. Anyway, I have not seen this paper before.

Welcome new Analysis Services blogger – Ramunas Balukonis.

http://www.ssas-info.com/RamunasBalukonisBlog/

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 »