SQLBI - Marco Russo : SQL Server 2005

SQLBits 2009

Marco Russo (SQLBI) — Mon, 23 Feb 2009 14:51:00 GMT

This the second time in a row I will not able to attend to SQLBits - but don't lose this event if you can go to Manchester on March 28th, 2009. The conference is free, but registration is required. A training day (with a reasonable fee) is also scheduled the day before. I hope to attend the next one...

The reason I cannot go is mainly because a few days later there is the Microsoft SQL Server & Business Intelligence Conference 2009 in Milan and I'm involved in the organization of that conference - but be careful, all the sessions are in Italian, so it is good only if you already know that language! Otherwise, Manchester would be a better option for you!

The "by design" abuse

Marco Russo (SQLBI) — Thu, 19 Jul 2007 20:34:00 GMT

In the last months, I got the infamous "it's by design" answer for many bugs/issues/irrational behaviors I posted to Microsoft. For most of them I used the Connect web site, in a couple of cases I opened a formal incident to Microsoft support.

There are a lot of story (in and outside Microsoft) about the "it's by design" excuse and most of them are myths and urban legends. Unfortunately, today I received a notification about a bug closure with the "by design" comment that has the consequence to invalidate Microsoft documentation (ok, a small part of...).

The bug is related to SQL Server. One customer wrote me about a strange issue of the ODBC international date constant syntax {d 'yyyy-mm-dd'}. I never used that, since I prefer the yyyymmdd syntax, but Microsoft effectively suggests using this in the "Writing International Transact-SQL Statements" of SQL Server 2005 Books Online.

As a consultant, I was a little bit shamed because I never used it and I wasn't aware of the issues raised by changing the language settings (you know, this syntax should be used just because it's language neutral...). I double checked the whole thing (I always assume it's my fault before suspecting it's a product bug...) and then I posted this bug on the Connect web site. For the lazy of you, this is the repro code.


1:  USE tempdb
2:  GO
3:  IF NOT EXISTS (SELECT * FROM sysobjects where name = 'ProblemDate') BEGIN CREATE TABLE [dbo].[ProblemDate](
4:  [DateA] [datetime] NOT NULL ,
5:  [DateB] [datetime] NOT NULL
6:  )
7:  END
8:  GO
9:  IF NOT EXISTS (SELECT * FROM ProblemDate) BEGIN INSERT INTO [ProblemDate] ([DateA],[DateB])
10:  VALUES ({ d '2004-09-15'} ,{ d '2004-03-30'}) END 
11:  GO
12:  SET language english
13:  go
14:  -- The select run correctly
15:  SELECT CASE WHEN ProblemDate.DateA > {d '2006-11-21'} THEN 'y' ELSE 'x' END
16:  FROM ProblemDate
17:  WHERE ProblemDate.DateB={d '2004-03-30'} 
18:  GO
19:  SET language italian
20:  GO

21:  -- The same select, got an error
22:  SELECT CASE WHEN ProblemDate.DateA > {d '2006-11-21'} THEN 'y' ELSE 'x' END
23:  FROM ProblemDate
24:  WHERE ProblemDate.DateB={d '2004-03-30'} 
25:  GO

If you were able to say that line 22 will generate a date conversion error before running the code, I'd be ready to offer you a pizza everywhere you are in the world.

Now, it seems that I need to prepare a delivery of pizzas to Redmond, since the bug has been closed as "By Design". Unfortunately, there are no comments to explain such a decision.

I can make several hypotheses:

The bug has been fixed in a future release and someone pressed the wrong button closing the bug. That's the optimistic one.
The bug has been considered not a bug because I need to pay some pizzas to someone.

In that case, I will claim the pizzas from the documentation team, which has misguided those few people that RTFM :-)

The bug has been closed by someone that read one comment to the bug that pointed out a similar issue on the 'yyyy-mm-dd' syntax - but it is not a good explanation of the behavior that differs from documentation of the product.

I don't know. However, the issue I described is not the real problem; it's just an example to illustrate what happens on the Connect web site. In this case, the answer "by design" has been posted 3 month after the bug submission. Sometime I haven't seen an answer for many more months. However, it really depends on the team, there are other guys who typically answer in a few days (or some week in the worse case).

I really don't like writing this kind of posts, but I know that many people in Microsoft monitor blogs like mine and this is a message in a bottle for them. Guys, the whole Connect site isn't very useful if you will continue to answer with "by design" / "we don't have resource now" / "it's not on our plan for the next release (!)". The last one is my nightmare, because it means that this necessary feature/bug/whatever will be implemented/corrected in a production code that won't be released until 2011 (assuming that the 3 years gap between SQL Server releases will be maintained).

Posting on the Connect site has a time cost. To me (writing) and to Microsoft (reading and -sometime- answering). I typically post on Connect only after a search in newsgroups and web sites, only when I'm able to describe a repro of the issue. Until today, I submitted 44 comments (bugs and suggestions) for SQL Server 2005 - I'm not the first, but considering an average of 30 minute per post (and I'm not calculating the time to discover the bug of to recognize the need for a feature/change) it means 22 hours of work. Most of my posts are still active. Many don't have an answer, but I know that Katmai should have improvements covering some of my suggestions. A better update of these submissions would be appreciated...

I think that the Connect web site is a great resource, but it's not comparable to the kind of support that is offered by some ISV (I use other third-party tools related to SQL Server and .NET programming and they are years-light more responsive). I know, the small size of a company might help in being very efficient in customer support, but I can't believe that a company like Microsoft is not able to improve the Connect efficiency. In the last 7 days there were 48 new bugs and 31 new suggestions for the whole SQL Server platform (which includes SSIS, SSAS, SSRS, SSNS, SSBS). Are these numbers big? I don't know. I would say "no, they are not so big for a company like Microsoft".

Finally... I know, probably there are other priorities. But, in this case, please don't abuse the "by design" way to fix your bugs.

UPDATE - This bug has been fixed in SQL Server 2008!

Drop views from a schema

Marco Russo (SQLBI) — Wed, 30 May 2007 13:33:34 GMT

Today I wrote a procedure to drop all views from a schema (and the schema itself) in SQL Server 2005. I use views to expose objects to SSAS DSV and it's useful having a cleanup code. Here's the script: DropSchema drop all the views and the schema itself, DropSchemaViews is called by DropSchema and drop all the views within the specified schema. No error checking code, use it at your own risk!

IF EXISTS(SELECT * FROM sys.procedures WHERE NAME = 'DropSchema' AND SCHEMA_ID = 1) DROP PROCEDURE dbo.DropSchema
GO
IF EXISTS(SELECT * FROM sys.procedures WHERE NAME = 'DropSchemaViews' AND SCHEMA_ID = 1) DROP PROCEDURE dbo.DropSchemaViews
GO

CREATE PROCEDURE dbo.DropSchemaViews( @schema NVARCHAR(128) )
AS BEGIN

    DECLARE @view NVARCHAR(256)
    DECLARE @cmd NVARCHAR(500) 

    DECLARE v CURSOR FOR
        SELECT name 
        FROM sys.VIEWS
        WHERE SCHEMA_ID = 
                  (SELECT schema_id 
                   FROM sys.schemas 
                   WHERE NAME = @schema)
    OPEN v
    FETCH NEXT FROM v INTO @view

    WHILE @@fetch_status = 0 BEGIN
        SET @cmd = N'DROP VIEW [' + @schema + N'].[' + @view + N']'
        EXEC ( @cmd )
        FETCH NEXT FROM v into @view
    END
    CLOSE v
    DEALLOCATE v

END
GO

CREATE PROCEDURE dbo.DropSchema( @schema NVARCHAR(128) )
AS BEGIN

    DECLARE @view NVARCHAR(256)
    DECLARE @cmd NVARCHAR(500) 

    EXEC DropSchemaViews @schema 

    IF EXISTS(SELECT schema_id 
              FROM sys.schemas 
              WHERE NAME = @schema)
    BEGIN
        SET @cmd = N'DROP SCHEMA [' + @schema + N']'
        EXEC ( @cmd )
    END
    
END
GO

Is Microsoft serious about BI?

Marco Russo (SQLBI) — Sat, 26 May 2007 11:17:17 GMT

The short answer is yes. The long answer is that Microsoft should still improve in many ways. I thought about this many times before writing this post, because I want to be constructive and I hope that my words will not be misunderstood.

My consideration start from the lack of a BI client. If Microsoft want BI for the masses, a low-cost end-user front-end should be available to navigate data contained in Olap cubes. Excel 2007 is a great product to achieve this goal, but its limit is that it is not "only" a BI client, it is a very big product with a lot of dependencies. This is the only explanation for the situation created by the Service Pack 2 of Analysis Services 2005. This SP breaks a function that worked well with Service Pack 1 and Excel 2007 (see the issue here, that has many votes and comments of other people in the same situation). Well, bugs happen and it is not too strange to me that a SP can break something. It would be better to catch these kind of issues during the beta process... however, it happens.

Real issues started when I tried to fix the bug.

SQL Server 2005 SP2 cannot be uninstalled. A complete reinstallation of SQL Server 2005 is necessary to restore a working state (or at least Analysis Services 2005 - but do you trust an AS2005 SP1 with SQL2005 SP2? Some of my customers didn't like to be a beta tester).
I opened an incident to Microsoft Product Support Services. I opened the incident as a Microsoft Partner to simplify the life to my customer, who wouldn't be able to handle a technical conversation with the PSS. Unfortunately, the PSS closed the case answering that the observed behavior is "by design". David Gainer (partially) explained the change in this post.
I wasn't so happy of this answer and I asked to the PSS to give a solution to the customer - even as a workaround just to restore the previous behavior. The answer was that asking for an Excel hotfix (because they said that it depends from Excel) required a Premiere contract and I hadn't this level of contract. But my customer HAS a Premiere contract and I opened the same incident through him.
After more than 2 months from the start of this story, I still haven't received a definitive answer from Microsoft.

This apparent little feature is blocking the upgrade to SP2 on many sites. Any cube that uses Time Intelligence Wizard does not expose a fully navigable cube to Excel 2007. Not to mention all other models built with techniques similar to those used by Time Intelligence Wizard (making use of calculated members on non-measure dimensions).

I completely understand the (technical) impact of changes on a product big and widely used like Excel 2007. At the same time, I completely understand the disappoint of one of my customers that don't have a premiere contract and receive a "it's by design" answer to their complaints (it has to be seen if the premiere customers will receive a different answer...).

I see an issue that is more about customer relationship than technical. If you publicize the "BI for the masses" and "democratize BI" mantras but then you split your customers between first and second class, are you still reliable?

A BI solution, today, must be supported by someone. It is not a "install, click, click, click and go" product, like Word or Excel itself. You need the design and implementation of ETL packages and cubes. These solution are built by Microsoft Partners or other System Integrators. They are the most important contributors to Microsoft success in BI, because without them you would have empty boxes without data. Imagine their feelings when they are unable to solve these issues. At this point there are more chances that partners will adopt products like Dundas Olap Services or Report Portal to deliver data to their customer (especially in the mid-market), for a lot of reasons (customer service, control of user actions and so on). However, I like the improvements made by Excel 2007 in the PivotTable feature - but they are a "little" feature of a big product, thus we are at risk of further design changes in the future just for a Service Pack (of another product)?

The other part of the story is (should be) well known. There are too many different ways to create a KPIs. There is ProClarity that will be included in Performance Point Server, removing the option to get a desktop product alternative to Excel 2007 (maybe I'm in error for this - correct me if you have better information). There is no more PivotTable Web Component included in Office 2007.

A customer who want to start a simple BI project with a tight budget will need to rely only on Excel 2007 and Reporting Services (or he will look at third-party tools). I hope a Small Business Edition of Performance Point Server will be available at the time the product will be released: this could be a good move to introduce the complete Microsoft BI pipeline even in companies who are new to BI and are probably small-medium sized: they will invest more only if they can see a ROI. If the entry level is too high, they see an higher risk to play the game and prefer to wait for other solutions. Today these companies still produces tons of Reporting Services (or Crystal Report) static reports, obtained querying the relational database (often the OLTP one).

My conclusion: Microsoft is technologically serious about BI. But Microsoft could have a better BI technology adoption today just with simple support, customer-care and licensing decisions.