Report Builder 2.0 ClickOnce and SQL Server 2008 CTP SP1 Experience

Finally, the ClickOnce version of Report Builder 2.0 is available with the CTP release of SQL Server 2008 SP1 (Community Technology Preview (CTP) for Service Pack 1 of SQL Server 2008).  The big changes that have been made now with this service pack are the following:

Slipstream – You are now able to integrate the base installation with service packs (or Hotfixes) and install in a single step.

Service Pack Uninstall – You are now able to uninstall only the service pack (without removing the whole instance)

Report Builder 2.0 ClickOnce capability

Now the big question is will the support for infinite clickthrough exist?  First thing that we have to do is install SP1.  So I fired up my VPC and went through the process of updating my SQL Server 2008 CU2 release.  Here are some screenshots of the process:

So after that process was done (took about 20 to 25 minutes on my VPC) I was now on build number 10.0.2520 with CTP SP1 for SQL Server 2008.  The main thing I wanted to check out now was the replacement of Report Builder 1.0 with 2.0 with the ClickOnce deployment.  It turns out that the Report Builder option in Report Manager still points to the 1.0 release, but you can access the 2.0 release through the following URL – http://<servername>/ReportServer/ReportBuilder/ReportBuilder_2_0_0_0.application.  If you want to change the settings for the Report Builder option in the toolbar you can go into the Site Settings and set Custom Report Builder launch URL to this path or you can also access this in the Advanced settings in the Report Server properties if you connect by using SQL Server Management Studio (SSMS) – the property is called ReportBuilderLaunchURL under the Reports section.  Then the fun begins and here are some screenshots of the process:

Accessing the ClickOnce application with the URL address	Almost a 50 MB install (1.0 was like a 8 MB download)
The splash screen	And finally the application

I went ahead and created a report against the Adventure Works DW SSAS database report model and deployed the report, but unfortunately as of now there is no infinite clickthrough (Enterprise Edition feature) support like there is with Report Builder 1.0.  Plus there wasn’t any interactive sorting capability like with the Report Builder 1.0 reports out-of-the-box.  You can go into the properties though and setup the Interactive Sorting functionality.  Will clickthrough support be available at all with Report Builder 2.0?  Am I just missing something? That was a really nice feature, but maybe Microsoft has decided to stop support for this option.  I guess if you want to stay with that then you have to go with Report Builder 1.0 and stick to a single data region

For more information about Report Builder 2.0 configuration take a look at Teo’s blog posting here Report Builder 2.0 ClickOnce.  For more information about Report Builder 2.0 take a look at my posting Report Builder 2.0 RTM install and overview.

UPDATE (3/8/2009):

Just read this posting, How to build a Clickthrough link in Report Builder 2.0, from Bob Meyers on the MSDN blogs in regards to a couple of different options for setting up clickthrough in Report Builder 2.0.  One option is to create the report in Report Builder 1.0 initially (and then you can modify it with Report Builder 2.0) and the other is to get some information from the Report Model and setup a ‘Go to report’ action.

ReportSurfer.com – run and share Reporting Service (SSRS) reports

This past week I read Chris Webb’s recent posting Implementing Real Analysis Services DrillDown in a Reporting Services Report which I thought was very cool.  I had left a comment and was reading his reply yesterday when I saw a comment left by Darren Gosbell “Hi Chris, have seen http://www.reportsurfer.com? – it allows you to post reporting services reports and people can try them out live on the site before downloading them. They have Adventure Works as one of their databases so if you posted this up there people could try it out live and see how it works.”

I was like, wow, how cool is that.  Now when I want to share a report or design with someone I do not have to simply send them the RDL file with comments, I can upload the report and have them actually view the output.  If you are a member you can download the reports, setup your favorites, upload reports, and participate in the forums.  So now when when people have some really cool examples to share like Chris, Tim Kent, Robert Bruckner, etc. they can upload them for everyone to view.

It appears that right now you can use the Adventure Works, Northwind, Pubs, Project REAL, and a couple of other sample databases for your report demos.  This is a great option if you want to share your reports with colleagues, clients, customers, and show off your ‘portfolio’.  I really like the concept and would recommend checking out some of the reports that have been made available from the Robert’s Get More Out of SQL Server Reporting Services Charts that are available to view in the Reports Library.

Here are a few examples of what reports you can checkout on the site:

Pareto Chart	Moving Average	Microsoft Halo 3 Statistics
Schema Table Extended Properties	Sales Trend 2008	Recursive Hierarchy

So how cool is this?  I think this is a great idea and a great way to show off the power of Reporting Services.  I want to thank Angry Koala for making Report Surfer available.

File too large for the destination file system

I experienced this last year working with external USB drives and Virtual Machine files (vhd).  I have seen this come up quite a bit and if you are trying to setup the MS BI VPC then this trick and advice will definitely be helpful.

The reason for this is going to be the file system of the USB or destination that you are copying the file to.  The file that you are trying to transfer or extract is greater than 4GB in size and the destination file system is most likely FAT32.  FAT32 file system does not support files larger than 4GB in size.

Now in my scenario I had a Western Digital (WD) passport USB drive (default file system setup is FAT32) and I had about 100GB of existing files already on the drive.  I really did not want to transfer these files in order to change the file system (and I couldn’t because I am a pack rat and did not have any room to offload the files to).  So what can you do?  Well luckily I came across a blog posting by Scott Hanselman ‘The Duh Files – The file is too large for the destination file system’.  In this posting he provides a very convenient way to convert a file system without having to reformat the drive or move existing files.

If you run into this then you definitely need to check out Scott’s posting above and follow his instructions.  The last drive I got, SimpleTech 320GB, came formatted as NTFS, so no issues

Week of Data Warehousing Webinar Series – 2/23 to 2/27/2009

In case you haven’t heard about this yet and are interested in finding out more information about designing, loading, performance tuning, and reporting against data warehouses, you might want to check out these webinars.

Join SQL Server authors and MVPs for a week of warehousing and get the latest on Microsoft Business Intelligence inside your environment. If you are in any stage of a data warehouse design, administration or planning you won’t want to miss out on this free "week of warehousing" webinar series, free from SQLServerCentral.com and in partership with Wrox and Pragmatic Works. You may attend all five sessions if you wish or register for the sessions independently. Audio is provided either via a phone or over your computer speakers (voice over IP).

In this week of warehousing, you’ll learn how to design a data warehouse, load it using SSIS, place a cube on top of it using SSAS and report against it using SSRS. Finally, you’ll see how to performance tune and scale the data warehouse using common techniques. All speakers have authored many of your favorite books from Wrox (some speakers will be announced next week). SQLServerCentral.com’s last webinar series had nearly 15,000 registered so register quick for one the morning or afternoon session to claim your spot!

Registration is open to the first 1,000 people in each session and seats are going fast.

Week of Warehousing Webinar Series Schedule

Speakers include Joy Mundy, Brian Knight, Siva Harinath, Paul Turley, and JC Armand.

SSWUG.org Ultimate Virtual Conference April 22-24

Check out the upcoming SSWUG Virtual Conference.  The current early bird registration is $80 and you can use the code SPVTLASP09 to get an additional $10 off, so 3 full days for only $70.  Otherwise the registration is $125 and you can use the code to get that down to $115.  If you have attended a previous virtual conference with them then you are also eligible for $10 savings.

Check out the session schedules as they update them – amazing content. Click here.

Top industry speakers, outstanding content, four all-new (4!) tracks of information – 36 sessions – over three days.

Everything from the basics to more advanced techniques and technologies will all be featured.

Collecting Performance Data for SSAS 2008 – available on CodePlex

I saw some of this demoed this past year at the MS BI Conference and it appears that it has now been released on CodePlex

Check out the posting on the SQLCat team A Solution for Collecting Analysis Services Performance Data From Many Sources For Performance Analysis.

The solution is available to download on CodePlex with the SQL Server Analysis Services samples.

SharePoint Server 2007 Training

As we have all heard now PerformancePoint Server Monitoring & Analytics is completely moving into the Office 14 SharePoint Server Enterprise product as PerformancePoint Services.  As it sits now the dashboards end up being a part of SharePoint at some point anyway with either WSS 3.0 or MOSS 2007.  I just thought that I would provide some links to some resources that are available to build up your SharePoint knowledge and start diving into the product more.  This is definitely the direction that Microsoft is going with their products, so it is going to be important to really understand how everything integrates and works within SharePoint.

If you don’t have a copy of SharePoint to work with then the first thing you should do is download a trial version Microsoft Office SharePoint Server 2007 Trial Version.  You can also order the technical resource DVD to get additional resource information, presentations, SDKs, etc.  You can download the SDKs for WSS 3.0 and SharePoint Server 2007 if you want too.  If you want to get a VHD to use you could also download the 30-day trial Microsoft Office SharePoint Server 2007 VHD or just download the MS BI VPC 7.1 which has this already loaded up and is good through the end of the year (version 7 had a time bomb expiration of 12/4/2009).

So now that you have the software on to the training references (some of the information is contained in multiple spots, seems to be scattered all over the place, so sorry for any duplicates):

Ramp Up	SharePoint for Developers – Part 1 SharePoint for Developers – Part 2
10 Hands on Labs for Developers	http://MSSharePointDeveloper.com
SharePoint Virtual Labs on TechNet	http://www.microsoft.com/sharepoint/learning/virtual.mspx
BI Virtual Labs on TechNet (PPS related, but will be good to know for PerformancePoint Services)	TechNet Virtual Lab: Building Integrated Business Intelligence Solutions Leveraging SQL Server 2005 and SharePoint Server 2007 TechNet Virtual Lab: Microsoft Office Performance Point Server 2007 : SharePoint List Dashboards TechNet Virtual Lab: Microsoft Office Performance Point Server 2007 : Excel Dashboards TechNet Virtual Lab: Microsoft Office Performance Point Server 2007 : Dashboard End to End
SharePoint Server Virtual Labs	http://msdn.microsoft.com/en-us/virtuallabs/cc707678.aspx
TechNet Virtual Labs: SharePoint Products and Technologies	http://technet.microsoft.com/en-us/bb512933.aspx
Hands on Labs for Developing Applications on Windows SharePoint Services 3.0	http://www.microsoft.com/downloads/details.aspx?FamilyID=B885DCE8-C93F-48AA-B798-4422FEBCC200&displaylang=en
AppDev Training Download – Learn Microsoft SharePoint 2007	http://www.appdev.com/promo/freetitle.asp?PC=SN00037&T=d%5FSPS
Office SharePoint Designer 2007 Training	Standalone Edition Portal Edition
SharePoint – Events and Webcasts	http://www.microsoft.com/sharepoint/learning/events.mspx
Web Content Management Training Modules	http://www.microsoft.com/downloads/details.aspx?familyid=8b2c7980-b604-493f-b5ee-52c562458e34&displaylang=en&tm

Some other links of interest that you can use to get additional resource information are the following:

• Microsoft SharePoint Products and Technologies Community Portal
• SharePoint Team Blogs
• SharePointPedia
• CodePlex – SharePoint 2007 or CodePlex – MOSS
• SharePoint GEAR Up
• Magenic Blogs – including Michael Cummings and Michael Blumenthal

That should be a good start.  Enjoy the links and leave a comment if you have any additional items that are good resources for learning SharePoint…I know there are tons of them.

PASSMN February 17, 2009 Monthly Meeting

The next PASSMN Minnesota SQL Server User Group meeting is coming up soon.  The meeting is on February 17 from 3 PM to 5:15 PM.  You can visit the local site to get registered at http://www.mnssug.org or click on the link to register here.

This month the topics are:

SQL Server 2008 Data Collection and the Management Data Warehouse (MDW) (speaker Lara Rubbelke, Microsoft) – One of the more powerful new features for database administration, diagnostics, and trending is the SQL Server 2008 Management Data Warehouse.  The new Management Data Warehouse leverages a data collector framework to centralize and store SQL Server system and database performance data.  This session will:

1.  Introduce the data collector architecture.  Walk through how SQL Server 2008 collects performance and diagnostic data and presents the information in Microsoft SQL Server Management Studio.

2.  Show how to set up data collection, and configure the Management Data Warehouse.

3.  Demonstrate how to extend data collection to support your own data centralization requirements.

Performance Monitoring (speaker Amanda Bates, Robert Half International) – In this session, we’ll review some SQL statements that I’ve put together to take a look at the status of my databases and to monitor them over time.

Just a reminder that the group meets on the 3rd Tuesday of each month.  If you are in the area and available to attend please join us and stop by and say ‘Hi’.  There will be no meeting in March with the TechFuse 2009 event going on and the NCD BISIG group is also meeting.

Integration Services (SSIS) Package Designing Tips

I recently co-presented at a local event in Bloomington, MN talking about SQL Server Integration Services – Enterprise Manageability.  There was a lot of content to discuss and along the way I mentioned a few items in regards to designing SSIS packages, but I forgot to mention one item in particular – layout.  By package layout I am talking about how the items are placed on the control and data flow along with how they are joined together.

Lets take a look at an example from the Microsoft Data Warehouse Toolkit Book website.  The package that I will use is the DimProduct.dtsx.  Here are a couple of screenshots of the initial control and data flows:

Control Flow	Data Flow
Look at the item and precedence constraint alignment	Once again, look at the item and data flow alignment

What we see here is that the items that have been placed in the package are not neatly aligned and the precedence  constraints and data flow paths are not nice and straight (when they can be).  If I was creating some documentation or handing this off to a customer I would probably want to take a little time to polish it up and make it look a little cleaner.  Doing this by clicking and dragging the items can be extremely difficult (just like it is in creating a Database Diagram in SQL Server Management Studio).  So how can you do this more effectively?  Easy, enable the ‘Layout’ toolbar within the Business Intelligence Development Studio (BIDS).  You can do this by right-clicking on the tool bar and selecting ‘Layout’ or you can do this through the file menu by selecting View—>Toolbars—>Layout.

Now by selecting items within either of the flows I can utilize the different types of options in the ‘Layout’ toolbar to nicely center, top, and middle align the items and make the package layout look like the following:

Control Flow	Data Flow
Here the items have been center aligned, the annotation is top aligned with the top item, the precedence constraints are nice and straight, and I have enabled the precedence constraint labels.	Once again you can see that the certain items have been center aligned, middle aligned, top aligned, the data flow lines are nice and straight (when they can be), and items have been properly sized to display all of the text (source and annotation items).

This is kind of a nitpicky thing, but now the package looks really clean that the items have been nicely aligned.  Make sure that you properly size items so that you can read the names and annotations properly too.  This makes it much easier to read without having to read properties.  You can also see that in the control flow I have enabled the option to display the precedence constraint labels.  This makes it very easy to see what is going on and helpful for people that have color blindness and have difficulty distinguishing between the green and red lines.  To enable the labels you need to go into the file menu in BIDS (Tools—>Options—>Business Intelligence Designers—>Integration Services Designers) and check the box ‘Show precedence constraint labels’ in the Accessibility section.

 

In review, here are the items that I would suggest when designing packages:

1. Enable ‘Layout’ Toolbar
2. Align items in the control and data flows
3. Enable ‘Show precedence constraint labels’ (check out the update below in the posting for additional property setting)
4. Properly size items so that all text is readable

On a side note, effectively naming the items and including annotations are other best practices that should always be included when creating SSIS packages.  I would have included some additional annotations in the package above to add more explanation to what the package is actually doing.  To add annotations you right-click within the control or data flow and select ‘Add Annotation’.  To add additional lines in the annotation perform a CTRL+Enter.  If you want to watch a quick video on documenting SSIS packages take a look at this one by the Midnight DBA – Commenting Tribal Knowledge in SSIS Packages.

Additional design best practices to add to the list above would be:

1. Effectively name control and data flow items
2. Include annotations

One last thing…hopefully in the future snap lines will be implemented in the SSIS designer.  It has been part of Visual Studio development environment since Visual Studio 2005 and was implemented in SSRS 2008, so why not across the entire toolset?  Hmmm…

UPDATE (10/17/2009): Jamie Thomson did a recent posting about setting the ShowAnnotation property of the precendence constraints to ‘ConstraintOptions’.  This will show the label and also any expressions that are being used which is very valuable information and improves the self docummenting capabilites of SSIS so that you don’t have to manual enter this information into a annotation.  Check out the posting here Things you might not know about Precedence Constraints : SSIS Nugget.