Case Study: Microsoft reports for BI duty

When residential real estate agency Long & Foster Cos. decided to update the way it prepares financial reports and replace a system it had been using for 20 years, the company chose Microsoft's first reporting tool to do the job.

Long & Foster opted to use a beta version of Microsoft's Reporting Services (RS) to anchor its entire process for generating financial reports for company management, even though other tools had been on the market for years -- and some even ship with Microsoft's own SQL Server 2000.

"That was a bold and to some degree risky move," says Mayur Raichura, the company's director of information services.

But the gamble seems to have paid off for Fairfax, Va.-based Long & Foster. In four months, it developed 40 different reports for its 212 offices using Microsoft's SQL Server 2000 RS -- eight months ahead of the schedule projected by developers.

Users now can access reports instantaneously over the Web instead of receiving them monthly. Raichura estimates that the company is saving approximately US$530,000 per year by using RS in lieu of paper copies delivered by couriers.

Long & Foster is one of a growing number of companies using RS to create, manage and publish reports from data pulled from a data warehouse. Microsoft says more than 100,000 users have downloaded the tool since its introduction in January. Users say the price is right for the server-based reporting platform: It's free for SQL Server users who need to run only one instance of the business intelligence database. (If they need more instances, they must buy more server licenses.) Users are also attracted to RS's tight integration with the Visual Studio .Net development environment.

However, while RS can deliver production-style reports from OLTP database queries, it can't provide management-style reporting designed to allow users to author their own reports. Users and analysts alike say that while the product's tight integration with .Net is helpful for developers, it locks out users who don't have knowledge of the development platform. Competitors in the reporting arena, including Business Objects, Cognos, MicroStrategy and Hyperion Solutions, support both production and management reporting.

Nonetheless, Long & Foster and other companies are making RS a corporate standard for reporting.

For example, copper producer Phelps Dodge has replaced its Crystal Reports tools (marketed by Business Objects, which acquired Crystal Decisions late last year) with RS, which now serves up 400 to 500 daily financial reports to managers. James Faith, lead engineer at the Phoenix-based company, says RS had an advantage because it was was available for free with Phelps' existing SQL Server license and because it allowed the company to push Web-based reports to users.

"It has absolutely changed the way we do our reporting," Faith says. "We can have it so it automatically e-mails out to users . . . to do financial reporting on a daily basis. It is very file-based, and it allows you to move from various environments pretty seamlessly. It takes our developers no time at all to produce these, get them tested . . . and into production."

Cindi Howson, a faculty member at The Data Warehousing Institute in Seattle and author of the independent BIScorecard.com report, which evaluates business intelligence tools, said RS is most likely to be found in Microsoft shops, given its tight integration with other Microsoft products.

"If your standard for application servers is Microsoft-based, then definitely you should be looking at the Microsoft stack," Howson says. "If a company has decided to have a different application server, or if they're running all on Unix, Microsoft doesn't meet that need."

She adds that RS does, however, have capabilities that other popular reporting tools don't, such as a data-driven subscription model.

"(Microsoft) reuses some of the components, like scheduling, that are inherent in SQL Server," she says. "What Microsoft can do that Crystal can't do is have multiple data sets populate a report, which really creates a dashboardlike report."

Market Shake-up

Microsoft shook up the business intelligence market in 2003, when it announced its intentions to add a reporting tool to its BI stack, which already included online analytical processing and extract, transform and load tools. After Microsoft disclosed its plan, Business Objects acquired reporting-tool vendor Crystal Decisions, Cognos rolled out its own reporting tool, and Hyperion snapped up tool vendor Brio.

Now, Microsoft is maneuvering to expand the functionality of RS. The new features will debut in the version of RS that ships with SQL Server 2005 (see sidebar). Microsoft also has announced two new Report Packs report templates that allow reporting against its CRM and Exchange applications.

"You have this case where Microsoft used to be dependent on outside vendors like Crystal for reporting," says Rob Helm, an analyst at Directions on Microsoft, an independent research firm in Kirkland, Wash. "Now it has an in-house product that looks basically to be pretty much ubiquitous in the Microsoft product line. It's going to have a huge impact over the next two to three years simply because it will ship with so many Microsoft products."

Perhaps the biggest enhancement will come when Microsoft adds Report Builder to RS in SQL Server 2005. Report Builder is rebranded technology from Active Views, which Microsoft acquired earlier this year.

"Report Builder will go after that group that wants to come in and build their own report or modify an existing report and not have to live in the Visual Studio environment, but more importantly not have to understand the database," says Alex Payne, senior product manager on Microsoft's SQL Server team. "I can drag and drop report entities onto a palette, effectively allowing me to build a report, an ad hoc report . . . but I'm browsing a semantic layer -- I'm not building it off a database."

But despite the initial buzz around Microsoft's entrance into the reporting world and the new features planned for RS, some users haven't been lured away from their existing tools.

For example, the Federal Home Loan Bank of San Francisco, a Microsoft shop, took a look at RS but opted to stick with its Crystal reporting tools, says Rose Kim, the bank's vice president of application development and support.

"Microsoft is doing with Reporting Services what they do with everything else -- they are introducing it now, and in three to four years time, they may have all the industry-leading features," Kim says. "For now, we're looking to continue to build upon the tools we already have. Part of it is a human investment in terms of training and knowledge. The ability to become agile and proficient at a particular tool requires some time."

In addition, users should be aware that while RS is essentially free for SQL users, heavy demands for report generation may require enterprises to run more than a single instance of SQL, says Keith Gile, an analyst at Forrester Research Inc. "It is not recommended that any company with a large report library or a heavy demand for report generation use a single instance of SQL Server for all database and reporting activities," Gile says. "Companies need to understand how much of an additional load a production-reporting environment will place on the SQL Server engine."

Extending the reach

Microsoft is maneuvering to broaden the appeal of Reporting Services by integrating it into more products and adding new features.

The company has already shipped RS in the latest version of its Microsoft Operations Manager. And Visual Studio 2005 will include embeddable reporting controls that Microsoft says will allow developers to more easily build applications with RS. And RS will be integrated into the next version of Microsoft's CRM software.

For SQL Server 2005 -- now due out next summer -- Microsoft will add an MDX designer into Reporting Services to allow enterprises to create and run relational and OLAP queries against SQL Server.

The most highly anticipated improvement slated for RS is probably the new Report Builder, which will be available in SQL Server 2005. Report Builder will incorporate current technology with an end-user query and report-authoring environment that has drag-and-drop features that are more suitable for business users.

Most analysts and users agree that RS's biggest drawback is that it is mostly limited to developers who have experience in Microsoft's Visual Studio .Net.

Report Builder will provide the type of functionality that Hans Rasmussen, an IT project leader at TDC AS, says he has been waiting for since the Danish telecommunications company began using RS in January. Rasmussen says he was surprised to find that the designer interface of RS was in Visual Studio, meaning end users couldn't use it to build reports. Still, he says he has high hopes for Report Builder, which he has previewed.

"I believe that with this interface we can push Reporting Services all the way to the end users in the business lines," he says.

Rasmussen estimates that by using Report Builder, TDC can save up to $543,000 -- the amount the company is spending annually to license a Brio reporting tool.

While Report Builder will provide the native report-writing that was missing from RS, the onus now will be on Microsoft to present benchmarks and customer case studies that prove RS can stand up to the rigors of production enterprise and analytic reporting, says Forrester Research analyst Keith Gile.

"This will take time, and prospective users need to understand that even though SQL Server is a mature and proven relational database management system, Reporting Services is still brand-new and relatively unproven," he says.

Join the newsletter!

Or

Sign up to gain exclusive access to email subscriptions, event invitations, competitions, giveaways, and much more.

Membership is free, and your security and privacy remain protected. View our privacy policy before signing up.

Error: Please check your email address.

More about BRIOBusiness ObjectsCognosCognosCrystal DecisionsForrester ResearchHyperion solutionsMicrosoftMicrostrategy AustraliaPhelps DodgePhoenix

Show Comments
[]