.NET with BI development tips, tricks and experiences.

Before I go on a rant I would like to point out what a joy PowerPivot is. I found it a marvellous feature of upcoming Microsoft products and the implications for the Business Intelligence community are huge. I had been using it consistently for a couple of months in my spare time, exploring its potential for my present employer after seeing it at the SQLBits V conference back in November. I had nothing but praise.

This morning I got back from my Easter break ready to do a demo to the company only to be greeted with an expiry message. This would not have annoyed me so much if:

1. I had been informed PowerPivot will expire during the original installation.
2. A warning was given that an expiry was looming.
3. PowerPivot was not free.
4. A replacement download or installation was available to keep it rolling.

On the MSDN forums the only solution appears to be disable the Windows Time service and then set your clock back before April 1^st. This can cause issues in other applications though, so not viable if you are trying to perform a smooth demonstration.

Microsoft please remember to take your crazy pills. Seems a little insane to expire a FREE product, particuarly before the official release is available.

After installing the Office 2010 Beta I continued to develop my SSIS and SSAS projects without interruption. In my spare time (a rare thing) I wanted to keep my web app skills up to date and continue to explore Silverlight and its all-singing, all dancing .NET RIA Web Services. All was well at first. I created a new web application project and started to create my master page and default web form when without cause I was not able to click on the Visual Studio 2008 window. The CPU was idle and I had no other apps open at the time other than Spotify. Tried the ALT+TAB to see if some modal dialog had gone astray but nothing…I was completely locked out.

I scratched my head for a while and tried restarting a few times. The only way I could kill VS2008 was using the Task Manager. The same occured each and every time with a web project. BI projects remained functional. I started to trawl the internet and did not find much but then I stumbled across Martin Hinshelwood’s blog and this article. He hit the nail right on the head.

If you have VS2008 locking you out on web projects after installing Office 2010 your solution awaits you here. Thanks Martin.

When you first install PowerPivot for Sharepoint 2010 and start deploying your workbooks it does not take long to notice that the Sharepoint library preview displays ALL the sheets in your workbooks. This looks pretty awful in what should be a slick gallery, plus the thumbnails are too small to examine data in any meaningful way. As my experience with Excel was pretty limited I naturally thought you would hide the sheets in Sharepoint – WRONG.

Open up your workbook in Excel and right click on the sheet tab and it will have a “Hide” option. Hide all the sheets you don’t want to see and save it back to Sharepoint et voila! No more pesky ugly data sheets. Only nice clean, sexy charts that will make your clients drool.

Immediately the next natural question is how do I get them back in case I have an itchy mouse finger? Right click any remaining sheet and select “Unhide”. You are presented with a list of hidden sheets to re-display.

Pretty simple stuff when you are looking in the right place.

Once again all praise goes to PowerPivot-info.com. This time it is down to their comprehensive installation guide of PowerPivot with SharePoint 2010. I followed their guide meticulously and had no issues whatsoever on my virtual Windows Server 2008 R2 instance. The only problem I had was with the PowerPivot gallery previews being generated once I was installed and deploying PowerPivot workbooks. This was a known issue and covered in their installation guide. I tip my hat gentlemen. Great work guys, you have my gratitude and thanks.

Just completed installing PowerPivot for Microsoft Excel 2010. As I am not a huge Office user I chose to go the whole nine yards and completely uninstall 2007. If it did not work or had loads of bugs it was not going to slow my day down. I had also been told by our resident I.T. gurus that the x64 version cannot run alongside Office 2007.

This guide from PowerPivot-info.com has to be the one stop shop for a painless install. Looking further down the line it has some great examples for getting started as well as a detailed installation guide for Sharepoint 2010.

So far it is going great and have only come across one major issue. When having launched the PowerPivot window from inside Excel, I promptly dragged it over to my second monitor so I could work with both at the same time. When I clicked off the PowerPivot window it disappeared and could not get it back. Alt+Tab etc. had no effect. A restart of Excel resolved it.