DBAs Leery About Letting the Masses Build Reports
"Were very excited about the integration of analytics and reporting functionality in SQL Server 2005, and weve already experienced some benefits from having Reporting Services in its junior version available to us today," said Morris Koeneke, manager of database services for Mary Kay Inc., in Dallas. "But as DBAs [database administrators], were very concerned" about the very real potential for users to clog networks as they generate inelegantly written reports that force databases to squeeze out data in inefficient ways, Koeneke said.
Koeneke was referring to Microsofts plans to integrate BI reporting into the upcoming SQL Server 2005 database release, which is slated for the first half of 2005. Those plans were set to be announced by Bill Baker, Microsofts general manager of business intelligence, during his opening keynote at the PASS (Professional Association for SQL Server) users conference here on Wednesday.
The aggressive move into BI is just par for the course for Microsoft, which has kept up a steady litany of BI releases, including earlier this year when the Redmond, Wash., company unleashed Reporting Services for free download. Since it was rolled out in February, Reporting Services downloads have exceeded 100,000, according to Alex Payne, senior product manager for SQL Server.
In SQL Server 2005, Microsoft is delivering Reporting Services Report Builder, the reincarnation of the ActiveViews BI technology it bought earlier this year. Report Builder is designed to allow end users to build reports in an ad hoc environment. According to Microsofts Payne, Report Builder will open report building to users who neednt have the knowledge necessary to use more complicated BI tools like Reporting Services, such as familiarity with database schema, database connection strings or how to write a SQL query.
As it now stands, report designers can make reports available to other users, but those users then have to live with what designer-built reports look like. With the new functionality, any user can browse a business layer presented in a familiar, drag-and-drop Windows environment, writing reports off of the SQL Server relational database or off of Analysis Services, which is Microsofts OLAP (online analytical processing) engine.
It sounds good to Lou Ann Leary, director of merchandise systems for Barnes & Noble Inc., in New York. "Id love to enable my users to get at the data themselves in an ad hoc kind of way," she said. As it is, Leary said, users are constantly coming back to Learys report programmers to ask that they enable more columns or tweak reports in some other way. "Everyone wants to cut [a given report] a different way, and if they could do it themselves, that would be fantastic," she said.
Perhaps a bit too fantastic. Mary Kays Koeneke said that of the dozens of customer-centric reporting tools on the market nowadays, most dont have the built-in functionality necessary to restrict offensive queries from overwhelming an online transaction processing back end. "Offensive" refers to queries that neglect to limit result sets through the WHERE clause, letting users extract potentially millions of rows of data, thereby clogging the network, misusing cache and generally making SQL work harder than is necessary to answer a given question.
"It takes four to five iterations of running offensive queries before you get the report right," Koeneke said. "By then, the DBA is pulling his hair out, wondering why his database doesnt work worth a darn, and getting all these complaints" about poor database performance.
The second negative part of easy-to-use reporting tools such as Microsofts Reporting Services has to do with the voluminous amounts of data they allow users to cache on the back-end database. "All of a sudden you have a customer who develops a report that consumes, say, 200MB of database space," Koeneke said. "They run it once a shift, and they want to retain it all week. You have all that core data, and youve rendered the flattened-out version of the data for reporting services." Between the original report in the OLTP system, the flattened Reporting Services version and the rendered report in database cache, a 200MB file can become a 500MB monster.
Koeneke fears an "explosion" of the number of database instances that will be needed to cache all this report data, thus making Microsofts free BI technology ironically responsible for substantial infrastructure costs. "Looking from the technology out, our concern as DBAs is there wont be a recognition of the cost of doing things this way," he said. "There will be an appreciation of the ease of doing things this way. I dont know if we on the infrastructure side can afford to embrace it as rapidly as executive leadership would like."