In-Depth

Will Microsoft Reshape Business Intelligence?

Business intelligence has long been the domain of big companies with lots of resources. Microsoft wants to change that, but it faces a daunting task.

Data is out of control in many corporations. There's simply too much of it. It's common now for even small businesses to have 1TB or more of data to manage; larger firms have much more than that. Simply collecting the data, though, is the easy part. Managing and using it are far more difficult tasks.

That's where business intelligence (BI) software comes in. For more than a decade, BI tools have offered a wide range of analytic and reporting capabilities, allowing even relative computer novices -- such as non-IT executives -- to dive into corporate data and actually come out with something useful.

BI products are designed to deliver contextual, relevant and actionable information to executives so they can be better informed to make important decisions. Theoretically, after executives have sifted through oodles of information, correlated different actions, identified problems and potential benefits and changed business processes, their corporations will become more effective, efficient and profitable.

But while that has been the promise, the reality is that few companies have realized such benefits. "BI has been a tool that only very large companies have been able to leverage," says Rob Helm, an analyst at Directions on Microsoft in Kirkland, Wash. One reason is that these products are based on a complex data foundation that's often difficult to implement.

First, a corporation needs to consolidate its relevant information in a data warehouse. Next, an extract, transform and load (ETL) tool moves the information from its primary source to the warehouse and cleanses the data, so executives work with current, consistent information -- after all, there's no sense in using sales numbers that are no longer up-to-date.

Then, executives tinker with Web-based reporting tools to examine the information. Sometimes, a multidimensional cube database-reporting tool enhances employees' ability to see connections in the information. Because of the complexity of the process and the price of the different components, only large companies with dedicated IT staffs have been able to put all the pieces in place.

Most small and midsize businesses have found BI deployments to be out of their price ranges and beyond their levels of expertise. "About 1 percent of users now work with Microsoft BI tools," Helm says. The software giant would like to increase that percentage. The company now offers a wide range of interconnected products (SharePoint, Excel and PowerPivot) as the foundation of its most recent BI initiative, which has slowly been taking shape. While some view the moves as having significant potential, Microsoft must still put a lot of groundwork in place before BI becomes a mainstream technology.

History Has Not Been Kind
Microsoft has made a few attempts to open up the lower end of the BI market. "Microsoft has been pushing into the BI space since the late 1990s," says Joseph Bugajski, a research vice president at Gartner Inc. Microsoft Data Transformation Services (DTS) made its debut in SQL Server 2000. Redmond followed with SQL Server Integrated Services (SSIS), which arrived in 2005. One year later, the company purchased small BI vendor ProClarity and planned to offer that technology to users as a common BI interface to SQL Server, SharePoint and Office data. However, Microsoft quickly scrapped that plan and decided instead to focus on more familiar UIs: Excel and SharePoint.

That strategy seems sound to some. "Why not just let users work with Excel?" asks Gartner's Bugajski. "They're already familiar with it, and some have been using it for various department or even company data-analysis activities." In fact, companies have developed various data-analysis applications with Excel and SharePoint even though these applications are often not comprehensive or powerful enough to fall under the classic definition of BI applications.

For instance, construction-consulting firm Gafcon Inc. has about 120 employees working in San Diego, Los Angeles and Orange County, Calif. The company, which has been in business since 1997, has used Excel and SharePoint to streamline its business processes for about eight years. As a consulting firm, the company relies heavily on employee collaboration.

"Since we started using the Microsoft tools, we've been able to collect information, analyze it and respond to new business opportunities more quickly," says Joshua Sebert, CFO at Gafcon, which has built about 25 BI apps using Microsoft tools.

Where Is Your Timesheet?
The applications have helped the company monitor its operations in areas such as tracking billable hours and marking progress on various client projects. Gafcon began building the apps by extracting information from Deltek Vision, an integrated project-based enterprise resource planning (ERP) solution for architecture and engineering firms, and placing that data in SQL Server databases.

After developing various dashboards, Gafcon executives were able to track information, such as revenue and division productivity, more closely. For instance, they can quickly see whether or not employees are updating their timesheets on a daily basis and then take appropriate actions. If an employee fails to enter a timesheet into the system and ignores a series of reminders, the person will eventually be locked out of the company network.

This tracking system has had a positive impact on the business. The company eliminated time spent by a handful of employees who had to chase people down and get them to fill in their timesheets. "Because employees are entering timesheets on time and more accurately, we're able to send our invoices to clients more quickly," notes Sebert. On average, Gafcon is sending such items out up to two weeks earlier than it did prior to having the system in place.

In addition, managers can track individual employee productivity through a Web interface. Red, yellow and green highlights let managers see which employees are making their targets in particular areas, such as client hours billed, and which are not. Employees can view their own measurements, as well, and the change has increased their level of accountability within the firm, according to Sebert.

Another plus is that the company can identify projects that are veering off track. "Our reports will show that a project may be 25 percent behind schedule or 50 percent over budget, so we can take a closer look, find out what's causing the problems and then fix them," Sebert explains.

Microsoft Tries Another Approach
Microsoft's latest foray into the BI space features a handful of interconnected products. One of the key elements in the new approach is PowerPivot, which is designed to work with Excel and SharePoint. PowerPivot for Excel is a free add-on that lets users create applications that have pivot tables and interactive reports. With PowerPivot for Excel, users can conduct complex queries by downloading up to 100 million rows of data from various data sources to their desktops.

Users can then slice and dice that information into various reports that illustrate trends within the company. PowerPivot for SharePoint provides companies with the ability to publish BI applications, and can be integrated with Excel and PowerPivot for Excel. Non-Excel customers can create BI applications on their desktops with SharePoint, publish them and share information with colleagues. To harness the new PowerPivot features, businesses need to upgrade to Excel 2010 and SharePoint 2010.

Gafcon's Sebert, whose company is in the process of upgrading to Office 2010 and deploying PowerPivot, thinks the new tools have potential. "We've gained a lot more insight into our operations using the Microsoft BI tools and think more is coming once we upgrade to the latest software," he says.

Some companies have already made the switch. Vectorform LLC, a global digital experience studio, has approximately 100 employees stationed in half a dozen locations. In the spring of 2009, the company began to search for ways to leverage its corporate data. The business had been cobbling together reports by pulling data out of different applications, cutting and pasting information into a central place, creating filters and, finally, generating charts with the information needed.

Sometimes problems arose because users made changes to information after a chart was generated. To address those issues, the company decided to migrate to PowerPivot and installed a beta version of the software in February 2010. The process for generating reports is now much more streamlined. "It used to take us about a month to create a new report, but now the process takes a couple of weeks at most," says Kurt Steckling, president of Vectorform.

The company has created SharePoint 2010 dashboards that highlight new business opportunities and individual performance using data from SQL Server 2008 R2. It also built new apps to better manage proposal calculations by matching a new opportunity to historical data. The change has helped Vectorform streamline its project-acceptance and hiring processes. Because its employees know which resources are free at what time, who's double-booked and who can take on more work, the studio is able to manage its workload more effectively.

Another plus is that more data is available in hiring decisions. "When you have a good person on the line, nine times out of 10, multiple companies are interested in hiring that person, so we have to make that decision quickly. To do so, we need data that's quick, accurate and readily available, and that's now what we have," Steckling says.

Saving Lives in Mississippi
The Mississippi Department of Transportation (MDOT) is another organization using the latest Microsoft tools to improve its operations. The government agency is responsible for maintaining the state's highways and transportation infrastructure. The organization has 100 field offices, more than 3,000 employees and an annual budget of more than $1 billion. Like other organizations, the agency has been expanding the volume of data that it can analyze.

In late 2009, MDOT was managing its data using Microsoft SQL Server 2008 Enterprise data-management software, and although MDOT was pleased with it, the agency's needs were about to change. In conjunction with the development of a new portal system to be powered by SharePoint 2010, MDOT moved many large engineering documents into a central database for analysis.

"With SQL Server PowerPivot for Excel, our safety engineers can mine data in SQL Server 2008 R2 to help prevent road accidents," says John Simpson, CTO at MDOT. "They can see whether certain road features, lighting or incidents of impaired driving are causing accidents at specific locations and can design new safety strategies based on those insights." The agency found that the addition of rumble strips in two-lane roads helped lower the number of traffic fatalities dramatically.

"It becomes much easier to get funding for new projects when one can clearly show the connection between spending and traffic safety," Simpson says.

The Dark Side
While the Microsoft tools have some potential, they also come with some limitations. One is that Microsoft has taken a tunnel-vision approach to BI: Its solutions work best only with an all-Microsoft product stack. "Companies will need to invest money and upgrade to Office 2010 and SharePoint 2010 to take advantage of the Microsoft tools," says Directions on Microsoft's Helm. Making such an investment in the current economy may be difficult for many corporations.

Security may be another bugaboo. As companies move corporate data into a data mart or data warehouse, they need to put mechanisms in place so that individuals work with only relevant information. The Microsoft approach relies on Active Directory, which includes some security features. Companies can control access at the user level and provide it to some individuals in a department and deny it to others, depending on the sensitivity of the data.

The focus on SharePoint presents another potential hurdle. The product can be powerful, but it doesn't scale well. Typically, issues arise when a data mart reaches the 2GB mark. Consequently, companies may have trouble managing and controlling their BI applications as they become larger and more dispersed.

Another challenge is beyond Microsoft's purview. "Companies need to understand what data is available and what types of analyses are possible with BI," Helm says. While that sounds straightforward, that level of expertise is not present in many organizations.

To date, BI usage has been limited to large companies with complex systems, big budgets and comprehensive tools.

Microsoft has moved boldly in an attempt to open up the market, but there's no guarantee of success.

"Because the current number of companies using BI is so low now, Microsoft should experience some success with its new approach -- but it's still an open question about how well BI will fit into the lower end of the IT market," explains Helm.

comments powered by Disqus

Reader Comments:

Wed, Jun 15, 2011 David Littleton, Colorado

Is it just me? MS Access is an excellent BI tool for small businesses. Access 2007 and 2010 are considerably more advanced and much easier to use than previous versions of Access, and in combination with Excel 2007-2010 is an excellent BI platform for data extraction, reporting and dashboarding. So I don't understand why Access gets omitted from these articles. Experienced Access specialists can produce several reports for consumption by frontline users and executives in a few minutes or hours depending on the complexity of the reports.

Mon, Apr 4, 2011

BizTools Analytics provides BI for financial and sales analytics with QuickBooks using the Microsoft SQL Server 2008 R2 technology stack. Learn more at www.biztoolsanalytics.com

Add Your Comment Now:

Your Name:(optional)
Your Email:(optional)
Your Location:(optional)
Comment:
Please type the letters/numbers you see above

Redmond Tech Watch

Sign up for our newsletter.

I agree to this site's Privacy Policy.