Manage Learn to apply best practices and optimize your operations.

How to Tap into SharePoint’s BI

Use Microsoft’s integrated tools to pull together data that will help SharePoint users make better business decisions.

Having the relevant information that is easily accessible is absolutely imperative to business success. The need to grow and innovate in today’s business landscape requires a new approach to gather business information and use it in ways that offer a competitive advantage.

Traditionally, a number of technical people in a single company would have to work together to compile pertinent business information. Microsoft Office SharePoint Server (MOSS) 2007 has business intelligence functionality that can help streamline the data-gathering process to create a single, integrated reporting platform for displaying information from multiple lines of business applications including accounting, project management, HR, payroll data and more.

This approach can enhance business performance by driving better decision making across the entire organization. The problem is that not many people know how to unleash SharePoint’s BI functionality.

In Microsoft’s integrated BI platform, MOSS 2007 is at the top of the stack and acts as the hub through which information workers can collaboratively access and analyze data The SharePoint BI architecture features help users take tools that are already familiar to them and develop scorecards, reports and other information needed for business decision-making without IT intervention. The BI tool is cost-effective and easy to use so that just about anyone in the organization can drill down for useful business information.

How to get started

And how do you put your plan in motion? Just remember these steps: Assess infrastructure, prepare data, design/plan and develop.

1. Assess infrastructure: Business intelligence is made up of SQL Server 2008 and Microsoft Office. System requirements for BI depend on which programs within the solution you choose to deploy.

2. Prepare data: The best way to learn about Microsoft’s BI stack is to build a simple tool. As you put together your BI tool, plan to spend most of your time performing data preparation tasks, such as restructuring and cleansing data. In BI terms, this set of tasks is called the extract, transform and load (ETL) process.

3. Design/plan: Before you start ETL development, carefully plan the design of your BI tool. The development of the tool goes more smoothly when you have a specific business problem to solve. Approach the design by first considering how people need to interact with information. By taking a user-centric approach, you can work your way backward through the applicable business processes to design a tool that properly retrieves and structures the data to support your business need.

4. Develop: After you come up with the initial design, you're ready to start developing. If your BI tool uses SSIS, SSAS and SSRS, begin by creating and populating the tool's data structures using SSIS. Once the data is in place, continue to the next step by building a cube. After your development is complete, process the cube to load it with data. You then use SSRS to develop a report that queries the cube and displays the query results in a report. You should approach this process incrementally to make sure the results of one step will work well in subsequent steps.

To complete the entire BI process, you need to install SQL Server 2008, including SSIS, SSAS and SSRS. If you have access to SQL Server, an SSAS server and a report server on your network, you need only install the development tools on your computer.

SharePoint’s BI tool supports a wide range of needs. Strategic planning and information management are easier in a centralized, fully integrated BI environment. Excel, Excel Services, SharePoint Report Center and the Business Data Catalog are the default BI features within the Office suite of products. SQL Server 2005 Reporting Services, Analysis Services, and PerformancePoint Server 2007 extend the core BI functionality by offering enhanced reporting capabilities, data warehousing, in-depth data analysis and real-time monitoring. Lists within SharePoint sites can be exported to Excel. Updates to the SharePoint list will be synced to the external Excel spreadsheet. This is a one-way sync—updates to the exported Excel spreadsheet will not replicate to the original SharePoint list.

Users can choose to maintain a link between the exported data and the original SharePoint list. By using the platform backed by Microsoft SQL Server 2008, SharePoint’s BI tool delivers ETL functionality, online analytical processing, data mining, predictive analysis and reporting all in one. The tool is fully scalable and includes enhanced security.

Scorecard functionality—supported by reports, charts, graphs and analysis—means that employees can track key performance indicators (KPIs) against key business goals. Understanding and analyzing the relationships between KPIs and your corporate objectives means you can better understand how your business is performing today—not at the end of the month, quarter or year when it’s too late to have an impact.

SharePoint’s BI tool offers a way to access, analyze and share crucial information for better decision-making. It can help an organization’s employees make better decisions by giving them the ability to collect data and transform it in a meaningful way.

Training is key to success

Using MOSS 2007 as the platform, Microsoft is trying to address what most people are using in their day to-day business processes. The more employees there are who have access to business data, the greater the company’s ability to anticipate changes and make adjustments that will benefit the business.

To increase adoption of SharePoint’s BI tool, be sure to train employees—and not only on how to use the tool and how to create dashboards but also on what they can gain by using it. BI presents easy-to-use information directly to people where they work, collaborate and make decisions. It can shift the way people work for the better but not unless employees are properly trained.

Another key to getting started is to have a visionary in your organization with a big-picture roadmap that includes realistic phased releases. Also make sure you have a small pilot project that you can quickly roll out within 60 to 90 days. The involvement of users is vital to the success of your pilot project.

BI benefits can be difficult to quantify, so it’s critical to demonstrate how they can affect long-term business survival and strategy. What is the value of a report or metric that gives a company important insight to initiate a strategic decision? It depends. More often than not, the value of BI can be best understood by imagining the risks of doing business without it.

About the Author

Paul West is principal and co-founder of SharePoint360 LLC, a SharePoint consulting and hosting provider. West has extensive experience with SharePoint architecture and implementations. He has been working with SharePoint technologies since the Microsoft SharePoint release in 2001.

Dig Deeper on Enterprise SharePoint strategy