Historically, because OLAP server technology has been tightly linked to proprietary client technology, customers have had little choice in their selection of mixed, best-of-breed products. This has led to high implementation costs and often inadequate choices for applications that require both client/server and Web-based OLAP information delivery. As was recognized years ago in the relational database industry, a common interface is necessary to promote openness in the selection of applications and databases. The industry standard became ODBC.
The issue of openness in OLAP tools was first taken up in 1996, when a vendor consortium called the OLAP Council announced an interoperability standard, multidimensional application programming interface (MDAPI), which was meant to open the marketplace to greater vendor participation. In spite of much customer anticipation, the vendor community, including the members of the OLAP Council, generally shunned MDAPI.
Recognizing the need for a unifying standard that leveraged existing customer investments, Microsoft extended the definition of the existing OLE DB data access API to include multidimensional capabilities. In one year, Microsoft published two drafts of the API, sought feedback from vendors and the public, and ultimately delivered a final version in February 1998 that was endorsed by 18 vendors at beta release.
Today, the OLE DB for OLAP API has the support of more than 30 vendors, many of which are listed on the Microsoft Web site at www.microsoft.com/data/oledb/olap. This list includes almost every member of the OLAP Council. Many of these vendors already are delivering beta products based on the specification to OLAP Services users. For an index of the vendors with products currently supporting SQL Server OLAP Services, see www.microsoft.com/industry/bi/solutions/olap/olap.stm.
Individuals often need to analyze data multidimensionally while they are disconnected from the corporate network, such as when traveling with a laptop computer. Disconnected users typically want to view and analyze small slices of their entire cube; for example, a sales manager may want to view a revenue summary for a particular region while visiting a regional office. The need is so common that desktop OLAP (DOLAP), which does not require a shared server for multidimensional data access, was created.
Most OLAP server technologies do not provide transparent creation of DOLAP cubes. As a result, this step has been left as yet another development-intensive effort or relegated to OLAP client tools that have added functionality to support desktop usage. Overall, this has increased the cost and complexity of delivering applications that require both connected and disconnected clients.
A popular viewing tool for any type of information, especially multidimensional information, is a Web browser. A key means of reducing the cost per user in large-scale OLAP applications, Web browsers open the world of multidimensional access to a wider audience. At present, there are some good products and tools for delivering OLAP data over an intranet, but there is no easy mechanism for an application developer to create custom OLAP viewing tools.
The OLAP Services server caches user queries and metadata as well as data. Cached query definitions and metadata make it possible for OLAP Services to answer new queries by calculating previously cached data rather than accessing the disk. For example, one user asks for sales data for January, February, and March. Another user asks for sales data for the first quarter. OLAP Services can summarize January through March from random access memory (RAM) faster than it can fetch the first-quarter data from disk.
OLAP Services is unique in that it provides much of the same functionality on the client. Every client connects to OLAP Services servers by using PivotTable Service. PivotTable Service acts as a driver to manage the connection between the client and server. PivotTable Service shares much of the same code as the OLAP Services server, bringing the server’s multidimensional calculation engine, caching features, and query management directly to the client. The result is an innovative client/server, data-management model that optimizes performance and minimizes network traffic. This comes at a very small computing cost: The disk space required for PivotTable Service is approximately 2 MB, and the memory requirements are only 500 KB in addition to the cached data.
The OLAP Services intelligent client/server architecture is capable of determining how to answer a user request as quickly as possible, while eliminating redundant network traffic. The key to this architecture is shared metadata between client and server. When a user requests information from the server, both data and metadata (definitions of the cube structure) are downloaded to the client. Having the cube metadata present on the client allows PivotTable Service to resolve which requests need to be returned to the server for resolution.
For example, recall the three-month sales data scenario. Assume that both the OLAP Services server and the client application have just been started. When the user asks for sales data for January, February, and March, the data will be cached on both the server and the client. If the user then asks for data for the first quarter, PivotTable Service will derive the results locally (at the client) without sending the query to the server. If the user subsequently asks to see data from the first quarter of this year compared to that of last year, PivotTable Service has the intelligence to access the server for last year’s data only.
PivotTable Service also provides the mechanism for disconnected usage. Portions of cubes defined and accessed from a server can be saved on the client for later access when disconnected from the network. In this way, business users can take portions of their database while travelling and still have complete analysis capabilities. In addition, PivotTable Service allows users to create simple OLAP models locally, accessing information in OLE DB compatible data sources, from flat files to desktop databases.
Finally, PivotTable Service provides the connectivity for Web-based applications. While OLE DB for OLAP is a low-level programming interface, a new extension to ActiveX Data Objects (ADO) provides multidimensional data access. This extension, called ADO/MD, can be used to create ActiveX controls in the Microsoft Visual Basic development system to browse, chart, or report on data in OLAP Services from a Web page. ADO/MD is the corporate application programmer’s tool for gaining access to the full functionality of OLAP Services.