Future Directions: Room for Improvement

There are organizations dedicated to trying to figure out what Microsoft is going to do next . We have no interest in playing that game, so rather than trying to predict the future, well highlight a few of the areas wed like to see Microsoft improve on.

Query Tools

Excel, despite being the most popular data tool on the market, is not the ideal query and reporting tool. There are several problems, the most troubling of which is that Excel is fundamentally a two-dimensional grid. Its hard for us to imagine how the Excel team will address this issue without creating a whole new product, or breaking the existing (hugely valuable ) product. Lucky for us, this isnt our problem.

The existing query interfaces for Excel are imperfect, too. Queries from Excel into the Analysis Services database are limited. The mechanism for specifying a relational query is archaic. We detested it 12 years ago when we first saw it, and it hasnt improved since then.

CIOs hate Excel for the same reason business users love it: Wheres the control? Because Excel pulls data to the local PC, its really hard for an organization to control what happens to it after that. Users can create all sorts of crazy calculations. They can email data to their friends . They can attempt to download a billion-row result set.

Weve seen pre-release demos of the next version of Office that address some of these issues. In addition, the Report Builder functionality in Reporting Services is a promising start for an ad hoc query tool. Its a bit raw as SQL Server 2005 releases, but we expect it to evolve and, we hope, integrate seamlessly with Office.

Metadata

The Microsoft toolset is full of metadata, as we discussed in Chapter 13. But the metadata doesnt talk to each other: Its a bunch of metadata islands with a few tenuous bridges thrown across between them. At the time of this writing, its your job to build or buy a coherent metadata bridge.

The lack of integrated metadata hasnt prevented Microsofts past customers from successfully implementing a DW/BI systemor else theyd have demanded a solution in this version of the toolset. Indeed, through the years weve seen very few good metadata implementations on any platform. But because Microsoft owns the entire toolset, they should find it easier to provide an innovative, interesting, and valuable metadata solution than is possible with a heterogeneous architecture. We hope they decide to leverage this opportunity soon.

Relational Database Engine

The relational database engine is primarily designed to support a transaction load. We cant comment on its advantages and disadvantages in that role. From a DW/BI point of view, we find several things puzzling or frustrating.

Ad hoc query optimization is inconsistent. Mostly, the query optimizer does a good job with ad hoc queries against a dimensional model, and the relational database engine performs extremely well. But its performance is variable. For some queries, the optimizer takes a path that is clearly suboptimal. And the nature of ad hoc queries makes adding optimizer hints a nonviable solution. The Analysis Services query optimizer does a much more consistent job of resolving dimensional queries, even if you strip away its advantage of precomputed aggregations. Why cant the relational database engine perform at the same level?

Were thrilled to finally have true partitioning in the relational engine, so we dont want to seem like complainers, but managing relational partitions is a headache , especially when you have a rolling set of partitions to maintain over time. In Chapter 4 we walked through the periodic process of managing partitions. All the tools are there, but it should be an order of magnitude easier to manage partitions than it is.

Other relational database engines support an upsert syntax that in a single statement will update and insert data into a table. Granted, Integration Services greatly reduces the need for an upsert statement, but an upsert that allows fast loading would be extremely valuable.

Analysis Services

Analysis Services is a bit overwhelming in the SQL Server 2005 release. The wizards are helpful, but they still leave you with a lot of hand work to do in an environment thats necessarily complex. We certainly wouldnt expect any but the most intrepid power users to succeed at developing their own cubes from scratch. Granted, Analysis Services is targeted not at this market but at the enterprise DW/BI system. Still, we know many analysts whod like to throw together a cube in order to explore a specific analytic problem.

The other big flaw we see in Analysis Services is its inability to respond to SQL queries. It can handle only the very simplest SQLsyntax too simple to be useful. Although MDX is superior to SQL for analytics, people with expertise and investment in SQL and SQL-based tools are reluctant to move to Analysis Services. It seems unrealistic to expect the world to move to MDX-based tools in order to take advantage of even the most basic Analysis Services functionality.

Analytic Applications

Microsoft develops transaction systems, including the Great Plains tools and Commerce Server, among others. We certainly hope to see the next versions of these packaged applications include embedded analytics in a graceful , well-designed way. As we discussed in Chapter 17, the delivery of real-time business information is (or should be) primarily a responsibility of the transaction systems. These packaged applications have a great toolset and a single, integrated platform. Their analytics ought to be great as well.

Integration

The greatest problem with the Microsoft BI toolset, underlying the criticisms weve already discussed, is integration or, more accurately, the lack of integration. The various components of even the SQL Server BI toolsthe relational database, Analysis Services, Integration Services, and Reporting Servicessometimes appear to be built by different groups. And other Microsoft technologies outside that core set, like Office and BizTalk, appear to be developed by different companies.

While we understand why this situation occurs, its time for a more holistic view of business intelligence at Microsoft.



Microsoft Data Warehouse Toolkit. With SQL Server 2005 and the Microsoft Business Intelligence Toolset
The MicrosoftВ Data Warehouse Toolkit: With SQL ServerВ 2005 and the MicrosoftВ Business Intelligence Toolset
ISBN: B000YIVXC2
EAN: N/A
Year: 2006
Pages: 125

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net