Microsoft BI Review

My 30 tips for building a Microsoft BI solution, Part I: Tips 1-5

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.

Disclosure: The company I work for is a Microsoft Partner
[Syndicated from]

**Disclosure: I am a real user, and this review is based on my own experience and opinions.
More Microsoft BI reviews from users
...who work at a Energy/Utilities Company
...who compared it with Tableau
Learn what your peers think about Microsoft BI. Get advice and tips from experienced pros sharing their opinions. Updated: May 2021.
502,499 professionals have used our research since 2012.
Add a Comment
1 Comment

author avatarit_user4014 (Developer at a tech consulting company with 51-200 employees)

Hi Peter !

Let’s talk about the difficulties you have faced during your BI career;

#1: I do agree with you partially, that having a dedicated Share Point resource would be handy because you might be going to run into performance or security issues somewhere along the project, but my idea is to have a Single Share Point resource which can be share between multiple BI projects. Because from my experience what i have seen is, it’s not that much hard to configure the Performance Point Services, Power View & Reporting Services on Share Point. With some help BI consultant can do this on his own, and as a BI consultant one should take the ownership of the project and try to resolve issues on his own. This will give them more of a learning curve and hands on other front end tools. You can't always rely on someone else to fix the issues for you.

#2: I haven't yet to see any BI Consulting firm delivering their solutions through Microsoft Visio integration with SSAS. All I could say is Microsoft has done investment in lot of tools to see which tool become a real contender for replacing all other BI stack, or get most popular response from the market. This is more of a market strategy to see which product / tool gets more response.

#3: SSRS has been the greatest thing Microsoft has delivered for Reporting apart from PPS lately. I still feel there is still lot of areas where SSRS need to be improved, like SSRS don't have alters, or its very restricted when it comes to dynamic dashboard or interactive reporting. If you have seen PPS, as a BI Consultant i want to show my client how much interactive my BI Solution is. Still there are areas like you mentioned Subscription & caching are great from SSRS. Additionally SSRS is designed to keep in mind that developers will be using it for building reports. For End User Microsoft Excel is best they can have where they can slice & dice and with Power Pivot included there is a lot End User can do with SSAS Cube.

#4: Use navarchar / varchar will always be a debate between developers. It's more of a choice thing. But if you are developing a BI Solution which is going to be used across multiple regions, consider using nvarchar but keep in mind the overhead of extra storage that you will be paying as a developer.

#5: CodePlex is a great community, but most of the clients want things to be customized and be their own proprietary. This is what we are paid for as a BI Consultant to provide them solution which fulfills organization needs and you might agree every management has different needs. But still good idea to look on CodePlex and peer sites for reference.

When choosing between tools, there is no single tool which can meet all of your customer requirements, so keep in mind that you might be using some tool which you have rejected in your initial analysis, and believe me this will save you big time facing problem against clients, because one you communicate that we won't be using this tool, and then you go back and say now we are providing this particular report using the tool which you have discarded in your earlier review.

So my point is as a BI Consultant, one needs to be flexible, adaptive & responsive to be a successful BI Consultant.

Hasham Niaz