Microsoft SQL Server 2005 Goodies

In case you did not realise I am on Holiday, well from work anyway. It’s been great I have spent a whole five days away from my laptop.

Today I have managed to catch up on a few things, checked out what’s new for SQL 2005. I came across a couple of things that are worth a look.

• Get Ready for Microsoft SQL Server 2005
  
  Microsoft has put out a series of free training courses that can be downloaded and done in your own time. The great thing about the nine courses is they are free until November 2006. Plenty of time to get your head round Administration, Programming and Business Intelligence features checkout:
  
  Get Ready for Microsoft SQL Server 2005
  
  
• Feature Pack for Microsoft SQL Server 2005 - November 2005
  
  This is a major release of add ons for SQL server 2005 from Microsoft. It includes handy free database drivers for SAP (preview) and IBM DB2. The Microsoft SQL Server 2000 DTS Designer Components for SSIS, and Datamining Viewer Controls are also included, the list goes on. Checkout:
  
  Feature Pack for Microsoft SQL Server 2005
  
  
• SQL Server 2005 Business Intelligence Add ons
  
  This is an excellent start on a for SSIS metadata reporting framework. There are two downloads containing SSRS reports and report model that reports against the SSIS log table. There is a dependency analyser for SSAS and SSIS. Checkout:
  
  SQL Server Integration Services Log provider reports using SQL Server Reporting Services
  
  SQL Server 2005 Business Intelligence Metadata Samples Toolkit
  

If you have not heard there is an Updated Books Online

Microsoft has released updated Books Online and samples. There are a few cool things in this updated that makes it a must for downloading besides being the most up to date documentation. Rather than repeating Brian Welcker's Weblog on the new updates go and check out it at:
Antics (SQL 2005 Documentation Updates)

To download the latest copy of BOL:
SQL Server 2005 Books Online (December 2005)

Big Plug for Dave Duvarney

Well I just came across Dave's Blog. He took SQL 2005 ascend course last year in Sydney. He has also written a couple of books on Reporting Services. Well worth keeping an eye on what he is up too.

Check out:
Dave Duvarney Blog
http://bimvp.com/blogs/dave_duvarney/default.aspx

Report Builder and the UDM

This month is definitely Report Front end development month. After presenting at our cabana session at SQL Server 2005 launch I have been getting busy with Report Builder and Reporting Services 2005. The project I am working on is a small scale reporting solution.

The UDM

At first I had a good look at using Analysis Services as my main repository for storing all my business logic and handling querying against the source system. This has its advantages; it reduces the load against the source system and offers high performance.

The main disadvantage was the complexity of getting data out of an OLAP solution. Of course it is easy with RS 2005 now, but the navigation across the OLAP cube can be a bit daunting to a novice business user.

Report Builder

Then there is Report Builder. Now there is something, an easy to use interface to create reports against any SQL Server/Analysis Services data source. The report writing is done via a report model. The model removes the need to create queries that require knowledge of the data source. This is great for users that don’t want/know how to join tables.

There is a gap in Report builder when it comes to complex reports. Report builder is a limited report designer allowing users to create reports in a controlled interface. For complex reports I always go for Report Designer. There is a not so well document feature that allows you to create Visual Studio Report Designer reports against a report model.

Russell Christopher wrote a neat little piece on using a report model as a data source. Check out: Using a Report Builder Model as a Report Designer data source in SQL 2005

How about both Report Builder and Analysis services?

The best solution is using a combination of Analysis Service cubes and Report Builder to provide an easy access way for users to reports. Using this hybrid approach will enable high performance query and reporting response times with user interface that’s not a pivot table.

It is a challenge to create a Report Model based on Analysis Services. The quickest way is creating a shared data source in reporting services. Follow these steps:

• Select the shared data source in report manager


• Click on Generate model button


• Enter a Model Name, Description and Location Click ‘Ok’


• Open Report Builder and select your new model.

Mark Russo has also blogged on creating a report model against Analysis services in BI Studio. Check out: Report Builder Model with UDM

This Months Book Recomendation

Microsoft SQL Server 2000 Data Transformation Services DTS
by Timothy Peterson

With SQL Server 2005 launched and ready to go it is easy to forget that there will be a lot of DTS 2000 systems ticking over for a long time. This means you may need a good reference book with great descriptions of everything DTS wise.

Timothy did an amazing job describing every DTS Task, workflow and practical solution possible. It is the only DTS 2000 book I have in my bookshelf.

I hope Timothy writes a SSIS book I would definitely buy a copy.

Get down with the feed!

Ok so it’s taken an email from someone in Sydney to make me finally add a feed to my blog. I am using feed burner to provide a site feed. Now I need to select a viewer for all the other blogs I am reading. Got any suggestions?

For my feed:

Which SQL Server 2005 BI features are you going to use the most?

Well November has not been a good month for me when it comes to Blogging. I have been flat out doing the Nationwide SQL Server 2005 Launch in New Zealand. It was amazing to meet so many keen SQL Server Developers.

Microsoft was very impressive running three events in Wellington, Christchurch and Auckland. Great support for us Kiwis.

This month I ran a quick poll on ‘What BI features are Developers most interested in’. The results are well inline with what I am doing.

It is interesting to note that only 5% are looking at Data mining, and the shift from ETL(SSIS) to Reporting Services.