SQL BI

Post hotfix post-SP1 problem with DTS Designer

After I installed build 2153, my SQL Server Managment Studio DTS 2000 Package Designer (which is part of SQL Server 2005 Feature Pack) was not working.

After I searched for an updated feature pack (which has not been released) I discovered that the problem has been described in KB917406. May be there are other factors, because I installed other programs yesterday after the build 2153, anyway I think this post is a good reminder.

SQL Prompt 2.0

I have being a user of PromptSQL since version 1.0. This utility was acquired by Red-Gate some months ago and today they shipped the version 2.0, renamed ad SQL Prompt 2.0.

SQL Prompt is fundamentally the ability to add Intellisense when you are editing T-SQL code (queries, stored procedures and so on) inside SQL Server Management Studio (but also Query Analyzer if you still rely on SQL 2000 tools). It works also inside other editors, like my favorite one (UltraEdit).

I really appreciate the improvements made between versions. It's still not a perfect product, but until September 1st it's free (I was a registered user of version 1.0, but I don't regret it). I expect a 3.0 version in the future that could further add features and then become a commercial product. Anyway, it deserves a try, especially if think how many time you asked why the Ctrl-Space doesn't work with SQL code.

MDX quality improvement in Excel 2007

I just had to profile MDX queries sent to SSAS from some client. Office Web Components (that are used in the cube browser of Management Studio) in many circumstances send a lot of MDX queries, increasing roundtrip between client and server, resulting in bad performance in tables with hundreds of rows (crossjoin between 3 attributes on rows and one measure on column).

I was very worried, until I saw the clean and beautiful MDX query that Excel 2007 send to Analysis Services 2005 to get the same results: great performance, a hand-written MDX query has the same performance (WARNING: this is not a benchmark but a small test on a very limited set of queries).

This week the Beta 2 of Excel 2007 could help me to make some customer happy...

Solved trouble with SP1 setup

If you have trouble installing SQL Server 2005 SP1 and you found in the log that views sys.dm_exec_query_resource_semaphores and sys.dm_exec_query_memory_grants are missing, then you have to move the Resource database into the same path of master database.

Details are explained in KB918695; I also suggest to read this thread in MSDN Forum.

SSIS service does not start with SP1: KB filed it as a bug

Some days ago I posted my troubles with SSIS after SP1 install.

Today Microsoft has published KB918644 where this behavior is officially described as a bug. It seems that product feedback is working well...

MapPoint 2006: first impressions

I tried MapPoint 2006 hoping to see an improved interface to UDM data. Unfortunately it seems there is not a direct support to OLAP cubes and a dynamic navigation with territory dimension.

I wrote a proof-of-concept of a .NET WinForm application that embed MapPoint 2004 as an ActiveX component and use levels of a Territory Dimension to choose what level of detail the user want to display on the map. I had to write data to a temporary file just to import them in MapPoint, but in generale it was very few lines of code. I hoped a better integration would have showed up in MapPoint 2006, but apparently MapPoint is still not considered a first-class citizen as a BI client application.