OLAP Clients for Microsoft OLAP Services

Karen Watterson

This is a summary of a presentation Karen Watterson made at COMPASS ’99, the inaugural conference of the PASS (Professional Association of SQL Server, www.sqlpass.org). Here, she provides a quick overview of OLAP terminology and her perception of the current OLAP client market for MSOLAP.

Most of you know that OLAP Services (MSOLAP) were introduced with SQL Server 7.0. OLAP Services, unlike SQL Server itself, only runs under NT, but OLAP clients can be run from Win9x systems (and sometimes other platforms) as well.

OLAP, which stands for online analytical processing, has its roots in executive information systems and high-end packages used by financial analysts. OLAP applications have tended to be read-only and have been characterized by interactive slicing and dicing, drilling up, drilling down, and using visualization software to chart aggregate values.

Frankly, OLAP hasn’t caught on as fast as everyone expected. I think that’s mainly due to the fact that DBAs have to learn new jargon and a new language (MDX) and have to learn how to design multidimensional cubes, and because Microsoft’s OLAP client, Excel, isn’t particularly user-friendly.

Jargon

Here’s a quick overview of the jargon associated with OLAP. Measures are the quantitative values in the database that you want to analyze–for example, sales, cost, and budget data. Measures, which are often aggregated, are analyzed against the different dimension categories of a cube. For example, you might want to analyze sales and budget data (your measures) for a particular product (a dimension) across various countries (specific levels of a geography dimension) during two particular years (levels of a time dimension).

OLAP Services ships with a sample called FoodMart, which has two cubes: Sales and Warehouse. The measures in the FoodMart Sales cube are: Unit Sales, Store Cost, Store Sales, Sales Count, Store Sales Net, and Sales Average (calculated measure).

Dimensions are another fundamental concept in OLAP. Dimensions represent the facts, factors, entities, or perspectives you want to analyze. The simplest way to create dimension information is to use the value from a single column. Dimensions will have names like [Time] or [Products]. Dimensions are typically organized into hierarchies of information that map to columns in a relational database. Dimension hierarchies are grouped into levels that consist of dimension members. Each level in a dimension can be rolled together to form the values for the next highest level. For example, in a time dimension, days roll into weeks or months, months roll into quarters, and quarters roll into fiscal years and calendar years.

Dimensions can be either shared or private. Shared means that the dimensions are visible to the entire database and may be used by one or multiple cubes that automatically join along shared dimensions in queries and virtual cubes. A private dimension is cube- (or virtual cube-) specific and is defined within the context of its cube. You can’t define virtual dimensions (which can’t have hierarchies until the next version of SQL Server) from the properties of private dimensions. Dimension tables such as location or product associated with star schema describe the grouping data by which data values such as sales from the central fact table can be aggregated. The most important characteristic of a dimension is the number of levels and members contained within it. The more members per dimension level, the more memory and disk space will be used, the longer the processing will take, and the larger the indexes.

The dimensions in FoodMart’s Sales cube are: Customers, Education level, Gender, Marital Status, Product, Promotion Media, Promotions, Store, Store Size in SQFT, Store type, Time, and Yearly income.

Hierarchies are attributes of dimensions and refer to the organization of members into a logical tree structure; they basically define the drill-up and drill-down paths. For example, a time dimension may consist of a hierarchy of hours, days, weeks, months, quarters, and so on. A geography dimension might consist of neighborhood, city, district, state, region, and country. Most members will have connections both up (to a parent) and down (to a child) in a hierarchy. Note that you aren’t limited to a single hierarchy for each dimension. You can define multiple hierarchies using a dot notation within the dimension’s name–for example, [Sales].[ByCategory] and [Sales].[ByDepartment].

A member is a single element or unit within a dimension that represents one or more data occurrences. A member can be either unique or non-unique. For example, 1997 and 1998 represent unique members in the year level of a time dimension, whereas January represents a non-unique member in the month level (there can be more than one January in the time dimension if it contains data for more than one year.) Members have names that can be used to identify them in queries, and OLAP Services uses a member key to identify members and access related data values in storage.

A cube is a subset of data from the data warehouse or data mart that’s organized and summarized into multidimensional structures. Each cube dimension can contain a hierarchy of levels that contain increasingly detailed information. Cubes can contain up to 64 dimensions (probably 128 in the next version of SQL Server). The OLAP Manager ships with 10 wizards, one of which is the Cube wizard, which helps you define your cubes.

Local cubes (.CUB files) can be saved from Excel 2000’s PTS or programmatically. In Excel 2000, first create PivotTable based on an OLAP Server cube (or other external relational data), then select "Create local data file" from the PivotTable—Client/Server Settings menu. Choose the File | Save as Web Page option. Then check the "Add Interactivity" check box and viewing option.

The market

Nigel Pendse (Mr. OLAP Report, www.olapreport.com) lists more than 20 features to look for in OLAP products, but his list includes a number of features that are OLAP server-specific. For OLAP clients, I add these: helpful wizards, industry-specific sample cubes (FoodMart gets old fast), a good tutorial and/or online training, the ability to link multiple cubes, a Web client and/or ActiveX controls and the ability to integrate with Microsoft’s "digital dashboard," full support for the MDX language, built-in VBA, the ability to create local .CUB files, and flexible methods for distributing and sharing data.

I categorize the OLAP client market as follows:

I consider Office 2000, with its Pivot Table Services (PTS), essentially free. PTS is the "invisible" client part of OLAP Services and is also bundled with Office 2000. For example, Excel 2000 offers a local cube creation option. In Excel 2000, you use PTS and MS Query (which must be installed) to create PivotTables, which can even be based on non-Microsoft data. Many Excel users are thrilled with PTS once they see it, so don’t discount it because you think it’s kludgy relative to some of the glitzier commercial products.

The MDX Sample Application is another "free" client application that’s sort of like Query Analyzer and lets you submit MDX queries. It ships with seven sample queries, the first of which is:

select  
  {[Measures].[Unit Sales]} on columns, 
  order(except([Promotion Media].[Media Type].members,
  {[Promotion Media].[Media Type].[No Media]}),
  [Measures].[Unit Sales],DESC) on rows
from Sales

Although the syntax of an MDX select isn’t exactly the same as a SQL SELECT, they’re similar enough that you can probably decipher that as asking how effective the promotions have been and asking to list sales in descending order by promotion medium, excluding "no media."

Far more common than MDX select statements, however, are MDX calculated measures, such as Query #4 that starts with a "with":

with member [Measures].[Total Store Sales] 
  as 'Sum(YTD(),[Measures].[Store Sales])'
select
  {[Measures].[Total Store Sales]} on columns,
  {TopCount([Product].[Product Department].members,5, 
  [Measures].[Total Store Sales])} on rows
from Sales
where ([Time].[1997].[Q2].[4])

Translation: Give me the top five best-selling 1997 products for Q2, the fourth month.

Seagate Software (www.seagatesoftware.com) has two free clients: Seagate Worksheet (based on Holos, probably won’t be updated) and Seagate Analysis 1.0. Unfortunately, neither supports calculated members, but both can be used to generate any number of reports using traditional data sources, not just MSOLAP cubes. More than 700,000 Seagate Analysis CDs (with the dog on the CD) have been mailed since June 15, 1999.

Cognos’ NovaView 2.0 ($395/client, $995 for administrator) was one of the first MSOLAP clients to ship. Cognos (www.cognos.com) has an exclusive license to distribute the original "Plato" client developed by Panorama Software. It adds writeback and support for multiple hierarchies, comes with a good tutorial, includes a view creation wizard, and has subwizards for group formulas and exceptions. It offers full support for MDX, but not VBA, and has good sample "how to’s" (DOC files with code) on the CD for "hard" MDX functions. Frankly, Cognos isn’t marketing this heavily and sees it as a point solution for Microsoft-only shops. It prefers to sell its PowerPlay and Impromptu products.

OLAP@Work 1.5 ($199) is an Excel (97 or 2000) add-in that’s the vision of a handful of Cognos expats. It’s a new product built for OLAP Services (1.0 was introduced in November 1998) that reportedly has about 50,000 licensed seats. OLAP@Work (www.olapatwork.com) licenses its code to Appsource/Hyperion (Wired for OLAP, www.appsource.com), which has more than 100,000 users. OLAP@Work includes VBA support, ships with an SDK with ActiveX controls, and has two wizards–a cube builder and a calculated members wizard. Its top features are support for writeback, distinct count, rank on rank, which lets you compare top performers to other top performers, and nested ranking.

Knosys ProClarity 2.0 ($395, including SDK) is another "ground up" product built for OLAP Services. Knosys (www.knosysinc.com), which shipped earlier products in the OLAP and analysis space, is trying to establish ProClarity as the "OLAP Central" hub. Result: an "open," modular model (ProClarity Analytical Platform) with 750 API functions, a dozen OCXs, and growing industry support. To date, five "plug-ins" have been announced (MapX, Max, DWSoft’s repository suite, ParkerSoft’s ezForcaster, and ForeCast X). ProClarity ships with the best set of vertical market sample cubes to play with, not to speak of a half-dozen wizards and its own MDX editor. Its strength is its hub vision, modularity, and partners with strengths in vertical markets.

Portola Systems’ Coronado 1.61 ($399, its first "real" release) is also modular and offers animation as a differentiator. Version 2.0, expected to ship in the first quarter of 2000, will support ActiveX and be Web-enabled, but will also cost more due to client/server architecture. Coronado targets the end user who wants a visual view on OLAP (www.portolasystems.com).

Maximal’s Max 1.0 ($495) is another excellent end-user OLAP client built ground-up as an MSOLAP client. Its unique strengths are visualization and ease of use for the end user, and it ships with a good tutorial and good selection of sample cubes (www.maxsw.com).

Most of the other current OLAP clients are familiar reporting and query tools that now also support OLAP. Examples are BusinessObjects 5.0 and WebIntelligence 2.5, a mature product with more than 1,317,000 licenses in 8,600 organizations. It’s multi-platform and multi-module, but it tends to be expensive at $1,195 per named user for Business Objects or WebIntelligence. It offers VBA support in the SDK (www.businessobjects.com).

Hummingbird’s BI/Analyze ($695) is part of Hummingbird’s (www.hummingbird.com) rich product line, much via acquisition (PaBLO, Genio). BI/Analyze is part of BI/Suite, and you can also buy BI/Broker for report distribution and repository engine ($20K)–add $10K for the query server option and $20K for the OLAP server option. BI builds its own proprietary HyperCubes but can load OLAP Services cubes. It offers good local (fat client) calculation.

InterNetivity’s dbProbe 4.11 is a thin browser-based client. Written in Java, it’s componentizable and costs $20,000 for 10 named users. Easy to deploy, with good PDF print support, it has about 50,000 licensed seats (www.internetivity.com).

That’s not all. As mentioned previously, Cognos has other clients (PowerPlay, Impromptu, Visualizer) that can be used with OLAP cubes. Seagate also offers Crystal Reports and Crystal Info. Brio Technology (www.brio.com), which competes with suites like Business Objects, has Brio Query/Brio ONE. Data Dynamics (www.datadynamics.com) ships an ActiveX OCX for programmers called DynamiCube. High-end financial analysis vendor Comshare (www.comshare.com) offers DecisionWeb. Mb (www.cizer.com) offers Cizer 2.0. I expect statistics vendors SAS (www.sas.com) and SPSS (www.spss.com) to ship products that support MSOLAP any time now, and Computer Associates (www.cai.com) is also reportedly updating Forest & Trees and InfoBeacon with support for MSOLAP.

Conclusion

Well, that’s all well and good, you might be thinking, but which one’s right for me? Well, as you might expect, there’s no simple answer. In most cases, if you already have Office 2000, I’d start by designing some cubes and letting users "have at" them with Excel 2000. Based on their response, savvy, and the nature of their needs, you might want to build a simple application for them or show them how to save local cubes, for example. If you already have a reporting/querying tool that’s been updated to support MSOLAP, consider that. If you want to build sophisticated OLAP apps, consider one of the packages that supports VBA and has an SDK. And, in any case, let me know your experiences! I’ll try to keep this OLAP client document up-to-date for all of us.