• Technologies

  • Instructors

  • Business Intelligence (BI) solution: SQL Server, Office, and SharePoint

    Do you really need to buy a business intelligence solution in order to get business intelligence?

    Maybe not. If you’ve got more or less all of your data in the latest version of SQL Server, and you’re using the latest versions of Office and SharePoint, you’d be surprised what you can cobble together on your own.

    Now, I should start by admitting that this isn’t for the faint of heart – you’ll need to decent database, SharePoint, and BI skills on-staff in order to pull together this do-it-yourself kind of system. But for organizations that currently have only very limited or small-scale BI needs, this might be 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, and SharePoint – backed up by SQL Server – can certainly deliver those 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 are letting many businesses dip their tows into the waters of BI, without having to invest in a full-scale BI system.

    You may also like:  Part 3 How to Import a CSV Text File into SharePoint 2013 – Creating the list in SharePoint

    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 pre-built 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, multi-core, gigabytes-of-memory computers. Once you’ve crunched those numbers, save the final data models into 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 CFO’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 4GB 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.

    You may also like:  How to Create a Site Content Type based on ObjectOfItem in SharePoint Designer 2013

    Let me conclude by saying that I’m not suggesting that a small business can build their 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 business intelligence 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
    PowerShell and SQL Instructor – Interface Technical Training
    Phoenix, AZ

    Share your thoughts...

    Please fill out the comment form below to post a reply.