Manage Learn to apply best practices and optimize your operations.

Blend Microsoft SQL Server, SharePoint, Office for ‘BI lite’

A business intelligence (BI) suite isn’t the only delivery system for BI insight. Get some expert advice and blend Microsoft SQL Server, SharePoint, Office for your own DIY system.

If you’re at a small or medium-sized business, you’ve probably asked yourself this question: Do I need to buy a business intelligence (BI) suite to glean business insight?

Maybe not. If you’ve got more or less all of your data in the latest version of SQL Server, you can cobble together an impromptu but effective BI system: Just blend Microsoft SQL Server, SharePoint and Office.

Now, I should start by admitting that this isn’t for the faint of heart -- you’ll need a decent database, the newest versions of SharePoint and Office, plus SharePoint and BI skills on staff in order to pull together this do-it-yourself system. But for organizations that have limited or small-scale BI needs, this is a great stopgap solution until you’re ready to move to a fully fledged BI system.

Most of what people consider to be a BI system consists of things like scorecards and dashboards; SharePoint -- backed up by SQL Server -- can certainly deliver those tools if you’re willing to develop them yourself. Microsoft Excel’s new PowerPivot add-in also provides pretty amazing analytic capabilities, all in a self-service package. Backed by SharePoint, PowerPivot can even publish shared analysis reports. In fact, it’s the combination of Excel, PowerPivot, SQL Server and SharePoint that’s letting many businesses dip their toes into BI waters, without having to invest in a full-scale BI system.

Excel has always had its PivotTable feature, but it had some definite limitations in terms of how much data it could deal with at once. PowerPivot blows that limit out of the water by relying on SQL Server to provide some of the back-end power, including the ability to integrate data from multiple data sources. Creating “mashups” of data from many sources is a key benefit of BI systems.

The in-memory analysis engine used by PowerPivot removes the need for a prebuilt data warehouse that has to be loaded with data in advance; instead, you rely on live data from live sources and crunch the numbers on today’s seriously powerful, multicore, gigabytes-of-memory computers. Once you’ve crunched those numbers, save the final data models in SharePoint, where other users can easily access them and even collaborate on them.

There’s a downside: That old Pentium 4 laptop sitting on your chief financial officer’s desk isn’t going to be up to the task. You’ll need a late-model processor with multiple cores, and the more memory you can stuff into the machine, the better; think about 4 GB as a reasonable minimum. That’s peanuts compared to the cost of a full BI system, and it’s an inexpensive way to get started on analytics.  

Now, I’m not suggesting that a small business can build its own BI system that’ll be just as rich and powerful as a commercial system. Far from it. But a small business can start getting some of the benefits of BI with a little knowledge, a lot of elbow grease and the right self-service technologies. Doing so will almost inevitably whet users’ appetites for more, and that’ll put you on the path toward a full-scale BI system that’s right for your organization.

Don Jones
is a co-founder of Concentrated Technology LLC, the author of more than 30 books on IT and a speaker at technical conferences worldwide. He can be reached through his website at

Dig Deeper on SQL Server Business Intelligence Strategies

Start the conversation

Send me notifications when other members comment.

Please create a username to comment.