SQL BI

Drop views from a schema

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!

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

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

7:      DECLARE @view NVARCHAR(256)
8:      DECLARE @cmd NVARCHAR(500) 

9:      DECLARE v CURSOR FOR
10:          SELECT name 
11:          FROM sys.VIEWS
12:          WHERE SCHEMA_ID = 
13:                    (SELECT schema_id 
14:                     FROM sys.schemas 
15:                     WHERE NAME = @schema)
16:      OPEN v
17:      FETCH NEXT FROM v INTO @view

18:      WHILE @@fetch_status = 0 BEGIN
19:          SET @cmd = N'DROP VIEW [' + @schema + N'].[' + @view + N']'
20:          EXEC ( @cmd )
21:          FETCH NEXT FROM v into @view
22:      END
23:      CLOSE v
24:      DEALLOCATE v

25:  END
26:  GO

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

29:      DECLARE @view NVARCHAR(256)
30:      DECLARE @cmd NVARCHAR(500) 

31:      EXEC DropSchemaViews @schema 

32:      IF EXISTS(SELECT schema_id 
33:                FROM sys.schemas 
34:                WHERE NAME = @schema)
35:      BEGIN
36:          SET @cmd = N'DROP SCHEMA [' + @schema + N']'
37:          EXEC ( @cmd )
38:      END
39:      
40:  END
41:  GO

 

Is Microsoft serious about BI?

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.

BIDS Helper beta 0.9

BIDS Helper is an addin for Visual Studio that really improves productivity developing solutions for Analysis Services.

Today I tested it and looking for its feature I encountered a page describing the "Show Extra Properties" feature... and it links a post of mines where I was asking exactly that feature! Wonderful... :-)

Attribute Aggregation Usage Priority

I and Alberto have posted a suggestion on Microsoft Connect to improve aggregation wizard behavior in a future version of SQL Server. The idea is to give an aggregation usage priority to dimension attributes for each cube or measure group. If you find the idea interesting, please vote it. The following is the description of the suggestion.

It would be useful to have the ability to define a “priority level” for the dimension attributes that have “unrestricted” aggregation usage. The priority level should inform the aggregation wizard about which attributes will most likely improve calculations but has a lower effect than “full” usage.
A typical scenario would be that of a balance sheet having multiple parent child hierarchies on the account dimension, several LastNonEmpty calculations and other important dimensions (say “Organization”). In this situation we would like to have a Time-Account aggregation, a Time-Organization aggregation but we find it useless to have Time-Account-Organization aggregations.
The wizard should try to create an aggregation on each of these attributes but should not try to create their Cartesian product as it currently does with “FULL” aggregation usage.
The user interface for attribute aggregation priority definition should not be tied to the attribute properties but it should have its own panel (per cube/measure group) displaying attributes grouped and sorted by aggregation priority. A drag-and-drop feature would be welcome.
Even if the cost of aggregations seems to be high (both on process time and disk usage), the final result is terrific for this kind of scenarios. Actually we can do that by hand, but it has a very high cost of development and maintenance.

Calculation in Time dimension

David Shroyer published an interesting paper about a Different Approach to Implementing Time Calculations in SSAS. It is something I would have found the time to write since Analysis Services 2000 (I have being using a similar approach since then) and is a very good guide on the whole topic.

I completely agree that the Time Intelligence Wizard could generate a better MDX implementation. There are other consideration that can be made about Time Dimension and I hope to be able to write something about this in the next future... now that I found this good paper, I feel less pressure for doing that!

The Many-to-Many Revolution available as a printed book

During the Microsoft Business Intelligence Conference 2007 we distributed a free printed copy of The Many-to-Many Revolution to attendees who visited our booth. Now the same book can be ordered on Lulu.com paying only the print and shipping costs (we don't charge royalties).

If you often use the paper, you could appreciate the little format of the book. Lulu.com provides a very cheap service with a very good  ratio of quality for price. The content is the same of the PDF I published in September 2006. The PDF is freely downloadable here since then.

We got good feedback about the book format, thus we made it available as a book to everyone.

Microsoft BI Conference 2007 comments

The Microsoft Business Intelligence Conference 2007 has been a good conference. Not very good, probably as a result of its success.

Microsoft wanted to send the message "we are serious in the BI market" with this event. From my perception, half of attendees were "technical" and the other half were "manager" (or something else not so technical). If the goal was to reach only this second half, it has been partially achieved. Or fully - in fact the conference had more attendees than expected.

I think with a few corrections, the next conference could be better:

• More technical sessions: no level 400 in this conference, even if chalk talks partially replaced this lack of high level content.
• More comfortable rooms for chalk talks.
• More design sessions: not only from an implementation point of view, but also from a business-case perspective.
• More communities involved (there was very few in the exhibitor area) - not SQL but BI oriented (BI includes a lot of products, these virtual communities today are scattered across many different products-oriented communities
• Put a books shop: any conference has one!

There is also something that was very good and I wouldn't change:

• Exhibitors hours: having an exhibitors pavillon opening time not overlapped with breakout sessions is very good. This is not always true for TechEd / PDC and I liked it (even because I was in the exhibitor area, yes...).

Something in the middle:

• Keynotes of Michael Treacy and Robert Kaplan was very good. However, not every attendee was really interested (I think the more technical people - it was a rumor I heard). May be that some overlapping activity (like hands on lab) could be useful, but a study of the feedback forms could give better indications.
• Some more info about future directions: I understand that in this conference Microsoft was more interested in products that will be released within 2007, but considering that in less than a month at TechEd some more info will be released, I think that giving more detailed announcements for BI-related features of Katmai would have been good. But I agree this could confuse a novice...

Other general comments by Chris Webb and Patrice Truong.

SQLBI at MS BI Conference 2007 - Day 2

Now that our presence as exhibitor is finished (Day 3 is only for keynote and breakout sessions), we can say that we had a great success! We distributed all the 300 copies of The Many-to-Many Revolution paper we printed just to show the kind of content that is available on our web site. The conference had 2.800 attendees, that is a great number, and one person each ten visited our booth!

We want to thank you all the people who just visited us to say "Hello!". We put a face on many names we've only seen on some e-mails or forum posts.

Many things are moving on. We are working on many new things and in the next monhts we will provide even more content on SQLBI web site. Personally, I will be at TechEd too (June 2007, Orlando) and probably we will see some more technical news about Katmai than in this conference. If someone missed the BI Conference, get in touch with me and say hello during TechEd!

SQLBI at Microsoft BI Conference 2007 - Day 1

The first Microsoft BI Conference started today and we received a lot of people at our booth.

Thank you to all the people who came to visit us!

Introducing Microsoft LINQ book

Usually I blog about Business Intelligence, but BI is not the only field I work on. I born as developer and I recently co-authored a book about LINQ with Paolo: the title is Introducing Microsoft LINQ, we created a dedicated site where you can find blog, sample chapter, code and corrections (while a book is written during development before the beta, there is always some last minut change). It will be available on May 16, 2007 and it can already be ordered.

Even if LINQ is not directly related to BI, a possible LINQ to MDX implementation would be very interesting. Is there someone already working on it?

SSAS: Model on the server vs. client-side MDX

In the last two years I worked on advanced modeling with Analysis Services. One result of this was the paper "The many-to-many revolution" I released in September 2006. Since then, I got a lot of feedback and I have been working on other evolutions that I will publish in the future (I need to find the time to write!). However, there is a background question that I would like to discuss with some other Analysis Services developers. I hope that some of my blog readers will give their contribution to the discussion.

The general question is: is it better to give more analysis feature to the end user adding attributes and measures in the UDM model on the server or defining client-side MDX formulas?

Let's make some examples just to explain the needs. Imagine a user that want to see the Year-to-Date value of a measure or the Year-over-Year percentage difference. This seems a basic requirement of any analysis, but it's not for free. Assuming that the user doesn't know MDX, you need either an Olap client able to define the necessary MDX query or some calculated members on the server offering the required feature. The Time Intelligence Wizard provides a way (with many limitations, in my opinion) to obtain the desired feature. I use a more sophisticated and flexible approach (requiring less calculated members on the server) that could be an argument for a future post, but it is not relevant to this discussion.

We can compare pros and cons of each approach.

• Server-side calculated members / MDX Scripts
• Pros
• Full availability on any client
• One single source of the "truth"
• Query execution engine cache can be shared by different client sessions
• Cons
• Need to be prepared in advance
• Might rise complexity of the model
• Client-side MDX formulas / calculated members
• Pros
• Higher flexibility (the user choose what she want whenever she want)
• Simpler cube model (simplify cube maintenance)
• Cons
• Query execution engine cache cannot be shared by different queries
• Users might defines similar calculations with the same name but different formulas, losing the single "truth"
• Requires an Olap client able to define calculated members (Excel has many limitations for that)

It is clear that I'm a strong supporter of "put it on the model" approach. I better control optimization and performance and I limit the need to train end users. That said, I feel that sometime this approach is very expensive. For example, when you have several Date/Time dimensions (normally using role-based dimensions) it could be hard to provide a consistent set of calculations on the server (each role dimension is potentially a multiplier of the resulting members).

Please note that raising model complexity is often the result of improvements that provides a better and simpler end-user experience. What I found difficult is to find the right balance. Having a too much complex model can have an undersired side-effect: nobody tries to change anything on the model, resulting in new (and redundant) cubes when new requirements arise and the original developer has moved to another project.

What do you think about it? What is your experience? Do you have a different pro and cons balance?

Let me know... your comments will be welcome.

NOTE: Meet us at SQLBI booth #532 at the Microsoft BI Conference this week!

A better solution to control multi-phase data pump in DTS editor

Almost two months ago I wrote a post about the lack of multi-phase data pump control in Management Studio "legacy" user interface for DTS.

In the meantime, I received a sample VB.NET simple form that offer some of the missing features that were present in SQL Server 2000. This sampe is DTSDesignerProperties and apparently it is available only through this link. Thank you to Doglas Laudenschlager for sharing his code.