Having worked with Microsoft BI for more than a decade now here are the top 30 things I wished I knew before starting development of a solution. These are not general BI project recommendations such as “listen to the business” or “build incrementally” but specific lessons I have learned (more often than not the hard way) designing and implementing Microsoft based Business Intelligence solutions. So here are the first five:
#1: Have at least one SharePoint expert on the team.
The vast majority of front-end BI tools from Microsoft are integrated with SharePoint. In fact, some of them only exist in SharePoint (for instance PerformancePoint). This means that if you want to deliver Business Intelligence with a Microsoft solution, you will probably deliver a lot of it through SharePoint. And make no mistake: SharePoint is very complex. You have farms, site collections, lists, services, applications, security… the list goes on and on. To make matters worse you may have to integrate your solution with an already existing SharePoint portal. There is a reason there are professional SharePoint consultants around, so use them.
#2: Do not get too excited about Visio integration with Analysis Services.
Yes, you can query and visualize Analysis Services data in Visio. You may have seen the supply chain demo from Microsoft which looks really flashy. You might think about a hundred cool visualizations you could do. Before you spend any time on this or start designing your solution to utilize it, try out the feature. While its a great feature, it requires a lot of work to implement (at least for anything more than trivial). Also, it (currently) only supports some quite specific reporting scenarios (think decomposition trees).
#3: Carefully consider when to use Reporting Services.
Reporting Services is a great report authoring environment. It allows you to design and publish pixel perfect reports with lots of interactivity. It also provides valuable services such as caching, subscriptions and alerts. This comes at a cost though. The effort needed to create SSRS reports is quite high and needs a specialized skill set. This is no end user tool. There are also issues with certain data providers (especially Analysis Services). But if you need any combination of multiple report formats , high scalability (caching, scale-out), subscriptions or alerts, you should seriously consider Reporting Services.
#4: Use Nvarchar / unicode strings throughout the solution.
Unless you live in the US (and are pretty damn sure you will never have “international data”) use unicode. Granted, varchars are more efficient but you do not want to deal with collations / codepages. Ever. Remember this is not only an issue with the database engine but also with other services such as Integration Services.
#5: Check if it exists on codeplex.
Do not build anything before you have checked codeplex. Chances are someone has already done the same or something similar that can be tweaked. If you are skeptical of including “foreign” code in your solution (like me) use the codeplex code as a cheat-sheet and build your own based on it. There is a lot stuff there including SSAS stored procedures, SSIS components and frameworks and much more.