Microsoft BI Conference

Ok so I have not blogged in a long while. So time for an update!

I have been very busy working on a few data warehouse projects and have not had the time to blog. So I am glad to get sometime out to go to Microsoft's first BI conference in Seattle next week.

It's a Conference of many firsts, first time Microsoft has created a forum for BI using Microsoft products. Sure there has been many a 'TechEd Session' on SSRS, SSAS and SSIS. But this is the real deal best practices, case studies and the like. And I am not talking about project real either. The big difference with this conference is there is not mix bag of sessions.

As a part of the trip I am going to blog every day of the conference to let you know what went to.

It's very exciting.

Microsoft and Teradata Collaborate

Microsoft and Teradata have formally announced a partnership. It looks like SSIS, AS2005 and RS2005 will be able to access Teradata enterprise warehouse. This will help Tetra data access Performance Point.
check out:

Microsoft and Teradata Collaborate to Offer Business Intelligence Solutions
http://www.teradata.com/t/

Business Intelligence with SQL Server 2005: Transform Data into Decisions

Just came across a set of PerformancePoint webcasts on http://thesource.ofallevil.com/events/series/sqlserverbi.mspx. Check out:

TechNet Webcast: Performance Management 101 with PerformancePoint Server 2007 (Level 200)
Thursday, January 18, 2007
1:00 P.M.–2:00 P.M. Pacific Time
Join us and learn how Office PerformancePoint Server 2007 enables businesses to deliver performance management capabilities broadly by providing an application that is easy to use and less costly to deploy than traditional solutions.

TechNet Webcast: Microsoft Business Intelligence: Introduction to PerformancePoint Server 2007 (Level 200)
Friday, January 19, 2007
8:00 A.M.–9:00 A.M. Pacific Time
Discover how Office PerformancePoint Server 2007 provides you with critical visibility into the factors that affect your business, allowing you to make decisions and take actions that drive better business outcomes.

Round up for 2006

Happy New Year! I am sorry I have not blogged in awhile. It’s largely due to a house we are building in Auckland. Building a house is very similar to building a data warehouse, best left to the experts.

What a year 2006 it has been. I have to say Business Intelligence on SQL server has definitely seen some big changes this year. After the release of SQL Server 2005, big things had to happen to top the release and what a year it has been.

Service Pack 1 Was not long in coming after the November release of SQL, helped a lot of companies to get of over the ‘not until SP1’ issue. The release was not without controversy. The removal of the ‘Select All Parameter’ caused some developers concern. Microsoft removed it due to performance issues. At the same time MS released the feature pack with a series of great free add-ons. Such as a SAP .Net OLE DB driver and data mining viewer controls. Check out:

Rather Ripped (Select All in Service Pack 1)

Feature Pack for Microsoft SQL Server 2005


The acquisition of Proclarity has shown Microsoft continued commitment to build its BI platform. Seeing the direction and integration of Proclarity, Balance Scorecard Manager, etc into Performance Point is very exciting and will start to give some of the Analytical BI vendors a run for their money. Check Out:

Performance-Point-Screenshots

Performance Point Home

Office 2007 was shown off at TechEd and is very exciting from a BI point of view. The new excel features are going to continue Excels front-end dominance. Although the new Excel front-end may take some getting use to for some users the new BI features are well worth the upgrade. Check out:
Business Intelligence in Excel 2007

Service pack 2 CTP is going to continue to enhance the BI features of SQL 2005. Report builder will now support Oracle as a data source. The ‘Select All’ parameter is now back in en-vogue. Of course there will be the standard bug fixes. So best get to testing and seeing the new fixes/enhancements. Check out:

A list of the bugs that are fixed in SQL Server 2005 Service Pack 2 Community Technology Preview (CTP)

How to obtain the latest service pack for SQL Server 2005

Microsoft have just release SQL Server 2005 Service Pack 2 CTP. Check out: How to obtain the latest service pack for SQL Server 2005

MCITP: Business Intelligence Developer

Microsoft has just released an update to the Microsoft Certified IT Professional (MCITP) with the creation of Business Intelligence Developer credential. It looks like you only need to sit two exams to become a Business Intelligence Developer.

This qualification is a step up from the Microsoft Certified Technology Specialists in Microsoft SQL Server 2005 Business Intelligence (MCTS: SQL Server 2005 BI).

The training material is available but the exams are not available until early 2007. It might be a good time to start looking into getting this certification.

Check out:
MCITP: Business Intelligence Developer
MCTS: SQL Server 2005 Business Intelligence

Tips, Tricks, and Advice from the SQL Server Query Optimization Team

A colleague of mine has just introduced me to one of the best blogs I have seen in along while. The Tips, Tricks, and Advice is brought to you by the SQL Server Query Optimization Team.

Here are some of my favourite posts on the performance features of SQL 2005 Query Optimizer. Check out the following:

• Why should I create an index?
  
• I Smell a Parameter!
  
• Fun for the day - Automated Auto-Indexing!

SQL Server 2005 Skill Assessments

Microsoft has released a series of free skill tests that are well worth trying. There are tests covering most Microsoft products. There are two categories that I have given a go.

• Introduction to SQL Server 2005 (DBA, Developer, BI): SQL 2005
• Introduction to SQL Server 2000 Reporting Services: RS 2000

This Months Book Recommendation: Microsoft Data Warehouse Toolkit

After TechEd I realised that in my slide deck I did not recommend The Microsoft Data Warehouse ToolKit by Joy Mundy and Warren Thornthwaite as required reading before starting a BI project.

If you are new to BI or new to the SQL Server 2005 BI platform this book is a great starting point. This book covers an end to end BI solution on the SQL server 2005 platform. The book covers a Reference Architecture for implementing a Microsoft BI Solution.

The book also includes a case study using the Adventure works example database as the source for an end to end BI solution. All the sample code can be downloaded from www.msftdwtoolkit.com

My TechEd session Ready for download

My TechEd session on Delivering an End to End Business Intelligence is now up on Microsoft for download.

Check out: BIN213.ppt

Project REAL Reference Implementation

I have just come across a new download from project real. It’s a complete business intelligence reference implementation. I am currently downloading the 238.2 mb sample. It looks like it a good example of an end to end solution.

Check out: Reference Implementation

New Zealand TechEd Is Finally Here!

TechEd 06 has been a sold out event for a number of weeks now. It sold out in record time. For those you that are not aware, Microsoft NZ TechEd is the largest annual IT conference in New Zealand with over 2000 delegates attending.

This year I am presenting again. I am lucky enough to have one very comprehensive session on Business Intelligence.

BIN213 - Delivering an End to End Business Intelligence Solution on Monday 21st Aug 2:00:00 p.m. to 3:15:00 p.m.

My session is going to cover a detailed overview of implementing Microsoft Business Intelligence solutions. I am covering topics such as SQL Server 2005, SSIS, Analysis Services 2005, Reporting Services 2005, Office 2007 and ProClarity.

I am also looking at the business reasons behind BI projects. In the demonstrations I will show an integrated architecture for a Microsoft BI platform.

Ok it’s a lot to cover. So I will also be about on the HP stand this year to go through any questions or demos you would like to discuss in detail.

If you have registered for TechEd you need to login to CommNet and book your session schedule.

For complete schedule information see Nigel Parkers Blog

SQL Server Versions

I have lost count the number of times I go searching for the patch level or version number in SQL.

This was something I did all the time with SQL 2000. It all started when SP 2 came out. We started to get issues with dts packages that were developed on SP1 and then run SP2. The problem was the package format changed between versions. Hence the question what version I am really running?

Why have I brought this up you may ask? Because now that we have SP1 for SQL Server 2005 and the rollup hotfix. We now have three versions of SQL Server 2005 that are in support by Microsoft.

How Do I get the Version of SQL Server?
The easiest and most reliable is the T-SQL Statement below:

SELECT @@Version

There are of course lots of other methods but I prefer T-SQL. This works for SQL 7 and above. The statement returns the SQL Server edition, version, OS and OS Patch number, and the most important the SQL Build Number for Sqlservr.exe.

The build number will tell you the patch level of your SQL Server.

Build Numbers for SQL Server 2005

• 2005.90.1399 - RTM first commercial release of SQL Server 2005


• 2005.90.2047 - Service Pack 1 for SQL Server 2005 see: How to obtain the latest service pack for SQL Server 2005
  
• 2005.9.0.2153 - Rollup hotfix for SQL Server 2005 see: Cumulative hotfix package (build 2153) for SQL Server 2005

Other Build versions
There are heaps of sources for this information. Listed below are some helpful links for earlier versions of SQL Server and software update naming conventions.

How to identify your SQL Server version and edition

New naming schema for Microsoft SQL Server software update packages

New Microsoft SQL Server 2005 Business Intelligence Example

I have just come across a new set of sample databases for SQL Server 2005. Some of the examples are based on Project Real Business Intelligence best practices project. Based on a real world implementation of SQL Server 2005 by Microsoft.

Check out: SQL Server 2005 Samples and Sample Databases (July 2006)

Installation of SQL Server Service Pack 1 by Windows Update???

The other day something happened to my laptop that took me completely by surprise. Windows Update decided SQL2005 SP1 was a critical patch. And proceeded to download and install SP1.

Although this isn’t an issue for me at then moment as I was going to install the Service Pack. I was taken a back with Windows Update installing it in the first place.

Like most DB pros I prefer to manage the database patch level on my own. I can see this kind of service pack roll out causing problems for IT Administrators and DBAs a like. Particularly with SQL express instances that are managed by business users.

Kimball Group Webcasts

I have just come across a series of must see webcasts by the Kimball Group consultants that wrote The Microsoft Data Warehouse Toolkit. I have not viewed them all yet. I am currently viewing the MSDN Architecture Webcast: Using SQL Server 2005 Integration Services to Populate a Kimball Method Data Warehouse (Level 200). Which I highly recomend.

Check out the other Kimball Group Webcasts at:http://www.microsoft.com/sql/solutions/bi/kimballwebcasts.mspx

Free Tools that have made my SQL life a bit better

I normally don’t recommend tools on this blog. But over last month I have been working with couple of tools that are worth a mention.

The first tool on the list is SQL Prompt by Red-Gate. I have been a fan of red gate bloggers for a while now at http://www.simple-talk.com/. SQL Prompt is without a doubt one of the most useful ad-ins for SQL Management Studio and Query Analyzer. This little tool is Intellisense for SQL Server. This tool dynamically looks up common functions and syntax as you type. It’s a free download until September 2006.

Check out: SQL Prompt

The second tool was recommended to me by Chris Webb. It’s a replacement for SQL Manager for stoping and starting all of SQL Server 2000/2005 services created by Jasper Smith at SQLDBA.com

Check out: SQL Manager

The last tool on my list is a new migration script by Marco Russo. If you were like me and followed the DTS best practices guide that MS put out and used UDL data sources. You will find that are unable to migrate your DTS packages. Marco has put together a great tool for converting UDL and other DTS objects that do not migrate.

Check out: A tool to improve DTS to SSIS migration

SQL Management Studio Short Cuts

I have been working with SQL Management Studio (SMS) a fair bit lately. Like most developers I always look for the standard short cuts that I used on Query Analyser. Below are a few of my favourites.

Commenting

I use these short cuts all the time to comment large sections of code. The comment style I prefer the most is dash form which I prefer too the slash style. -- Dash Comment /* Slash Comment */

• To comment in SQL management studio you must now do Ctrl+K, Ctrl+C instead of Ctrl+Shift+C in Query Analyzer.
  
• To uncomment in SQL management studio you must now do Ctrl+K, Ctrl+U instead of Ctrl+Shift+R in Query Analyzer.

Formatting

• To indent a block of code Select the code you would like to indent and press TAB to indent
  
• Shift+TAB to un-indent your code
  
• Word Warp long strings with Ctrl+E, Ctrl+W
  
• Make Uppercase Ctrl+Shift+U
  
• Make Lowercase Ctrl+Shift+L

Bookmarks

Ok so you are working on a massive script that travels for miles. Well then you better be using book marks to navigate that code. Bookmarks are great for moving through scripts and trouble shooting. Try out the following the next time you are working on a big script.

• Toggle Book Mark on/off and Crtl+K, Ctrl+K
  
• Enable all book marks Crtl+K, Ctrl+A
  
• Move to next bookmark Ctrl+K, Ctrl+N
  
• Move to previous bookmark Crtl+K, Ctrl+P
  
• Move to next bookmark folder Ctrl+Shift+K, Ctrl+Shift+N
  
• Move to previous bookmark folder Ctrl+Shift+K, Ctrl+Shift+P
  
• Clear book marks Ctrl+K, Ctrl+L

This Months Book Recommendation: SQL in a Nutshell

Ok I haven’t recommended a book in a while, and it’s not from the lack of reading them. I thought its time I recommend one of the books I always use on most of the projects I work on. SQL in a Nutshell from O'Reilly Press is my universal translator between database engines.

It’s the one book I take onsite with me, when I am working in multiple database environments. SQL In a Nutshell allows me to look at functions and syntax across database platforms. For instance ever wondered what CASE is in Oracle? Or what date functions are supported in MySQL? SQL In a Nutshell will give an overview of each SQL function and what is supported across database platforms.

This book also gives a great overview of the ANSI standard for SQL. The Second edition is well worth getting as it is updated with the SQL language definitions for Microsoft SQL Server, Oracle, MySQL, and PostgreSQL.

BTW: CASE in Oracle is DECODE go figure.

Blog Neglect and SQL Server 2005 Service Pack 1

Ok, it’s been a really long time since I posted, well over a month. Work commitments have taken a bit more of my time this month. If you missed it this may have been the biggest month for SQL Server since RTM release.

April saw the release of Service Pack 1 for SQL Server 2005 and Microsoft making another tool acquisition in the BI space.

SP1 was the most interactive service pack I have seen. The CTP access allowed SQL users to work with the beta product and provide feed back to MS on any possible issues. Creating what should be a solid service pack.

If you have not downloaded SP1 yet check out: Download SQL Server 2005 SP1