Information
The topic you requested is included in another documentation set. For convenience, it's displayed below. Choose Switch to see the topic in its original location.

Using Microsoft Excel 2002 with SQL Server Analysis Services

This content is no longer actively maintained. It is provided as is, for anyone who may still be using these technologies, with no warranties or claims of accuracy with regard to the most recent product version or service release.
 

Don Kiely
Third Sector Technologies

April 2002

Applies to:
    Microsoft® Excel 2002
    Microsoft SQL Server™ 2000 Analysis Services

Summary: How to use OLAP features in Microsoft SQL Server Analysis Services from Microsoft Excel 2002. (16 printed pages)

Contents

Introduction
Introduction to OLAP
Fundamentals of OLAP
Microsoft SQL Server 7.0 and 2000 OLAP Services
Creating an OLAP Cube
Excel OLAP Features
What's New in Microsoft Excel 2002 PivotTable Reports
Programming OLAP and PivotTable Reports with VBA
PivotTable Web Component
Conclusion

Introduction

Information has become one of an organization's greatest assets. However, making use of all this valuable data can be difficult. Answering simple questions like, "What were the top ten products that our five largest customers bought over the last five years?" can mean a complex query pulling data from several databases that runs for hours or days. Doing a data drill down to reflect geographic regions and quarterly time periods can increase the computing power and time needed by orders of magnitude.

One solution to the problem (which has been around for more than a decade) is online analytical processing (OLAP). But until early 1999, OLAP solutions were very expensive, largely mainframe-based solutions targeted at the world's largest corporations. Smaller organizations finally had access to these powerful tools with the release of Microsoft® SQL Server™ 7.0 and Microsoft Office 2000, and the features only got better with Microsoft SQL Server 2000 Analysis Services and Microsoft Office XP.

This article will give you a background on OLAP and its features so that you can get started using your organization's data in powerful new ways. The intent is you, as a Microsoft Visual Basic® for Applications (VBA) programmer, understand the fundamentals of Microsoft OLAP tools so that you can use them effectively in your applications. SQL Server 2000 Analysis Services encompasses tools beyond OLAP, but the focus here is on the OLAP features you can use from Microsoft Excel 2002.

Introduction to OLAP

At its most basic level, OLAP is a preprocessor for data, providing a way to prepackage and preanalyze huge data warehouses, or even small, single-purpose databases. In order to use data to answer the need for business information, a typical query on a relational database has to perform hundreds or millions of calculations, depending on the number of tables, records, and kinds of aggregations needed. This is the reason that some queries take so long as to render them useless. By the time you have the answer you need, the data is antiquated and the opportunity to use the answer profitably has long since passed.

OLAP is an end-user tool, something that surprises many people. It helps end users understand and analyze vast amounts of data. Without OLAP, a user who wants to analyze data has to understand the underlying structure of relational databases to use and query effectively. There are some applications that include a user-requested query builder which allow users to answer questions about data in small databases, with mere megabytes of data. Some query builders actually expose the underlying raw names of tables and fields. Worse, the user has to understand the relationships between tables and fields. Have you ever tried to explain to an end-user relational concepts such as foreign keys and left inner joins? Such applications are just what give computers and software their reputation for being hard to use.

All said, why bother interjecting yet another layer of complexity, even if it has a goal of simplifying user access to data, between the user and raw data? First, the information is prepackaged and preprocessed, so access to it is very fast. Rather than building queries against terabytes of raw data structured in a way that users can't understand, OLAP preprocessing calculates many of the relationships between data, essentially summarizing data for the user. There is a problem with what is called data explosion in some OLAP implementations, but with some preplanning the database administrator can minimize this problem.

The most common end-user entry point to OLAP data is through the Microsoft PivotTable® report in Microsoft Excel 2000 and Microsoft Excel 2002. A big difference between a "traditional" PivotTable and those based on OLAP data is that the traditional tables return all of the underlying raw data on which the table is based to the client application, which could potentially be a network-stopping amount, while OLAP tables download only the data used in the table.

So, does this mean that IT and database administrators do not have a role to play anymore? No. The first step in making the data available to end users is to create an OLAP cube, a highly technical task that should only be performed by your best database talent. By creating OLAP cubes and enabling users to use them creatively, IT managers and database administrators continue to maintain control over precious data, but free themselves from the tedious work of constantly responding to users' requests for answers to frivolous questions.

Fundamentals of OLAP

An OLAP cube assimilates billions of pieces of data. An OLAP cube aggregates data, resulting in the data losing its distinctiveness. However, in the process, an OLAP cube becomes more effective as it harnesses all the power of the data's collective information.

There are four fundamental elements of OLAP: cubes, dimensions, levels (sometimes called hierarchies) and measures. Understanding the OLAP element concepts is vital for the database administrator but helpful for the end user and VBA programmer to know as well. To aid the explanation of these four elements, let's look at the Analysis Manager window as shown in Figure 1.

(If you have Microsoft SQL Server 2000 Analysis Services installed on your computer, to open the Analysis Manager window, click Start, point to Programs, point to Microsoft SQL Server, point to Analysis Services, and click Analysis Manager.)

Click here for larger image

Figure 1. Analysis Manager window (click picture to see larger image)

Cube

A cube is the universe of data, a theme of data, if you will. The best-built cubes are usually related to a functional part of the business, such as a human resources cube, warehouse cube or a marketing cube. Such a functional relationship is essentially coincidental, but you will probably want to follow the logic of creating such themed cubes. The data assimilated by each such cube may use overlapping data. This however should not be considered redundancy, as the users in each part of the organization will most likely find the information more useful.

Dimensions

Dimensions are descriptive categories with different levels of detail. For example, you might want to categorize the data in a marketing cube in into geography, time, products, and services. These would be the areas of interest for the users of the cube.

Dimensions are further broken down into levels or hierarchies. These are divisions of dimensions with increasing amounts of detail as users drill down further, and approaching with each step closer to the raw data categorized by dimensions and levels. For example, in a time dimension, levels could include fiscal years, quarters, months, weeks, and days.

Measures

Measures are the actual qualitative data values aggregated as specified by the creator of the cube. While dimensions and levels define the rows and columns of a typical Excel 2002 PivotTable, measures are the data points within the table itself, the ultimate information of interest to the user. Measures might include wages paid, units sold, income, and expenses, whatever data is of interest to the user, aggregated by dimensions and levels.

Cube Creation

As you would have probably guessed by now, cube creation is both a technical exercise and an art form. Succeed, and your users will find exactly the information they need. Fail, and both you and they will become frustrated by the long wait times and users could potentially make faulty decisions based on flawed data.

In OLAP science, there are four models of data, mainly related to how the resulting cube is stored. The original form was multidimensional OLAP (MOLAP). As this form precalculates every possible combination of data, an aggregation process, it tends to have the best performance. And because the resulting cube in the data is no longer in a relational form, OLAP products store MOLAP cubes in proprietary data formats to optimize access, giving the OLAP form the best performance if the cube is well designed. However, it usually results in data stores that can be in the orders of magnitude larger than the original data. As such it is not a very scalable solution.

As the relational database grabbed the imagination of computer scientists and became the data standard, relational OLAP (ROLAP) was created. This is a much more scalable form, since it stores the cube's data in a relational database. Performance tends to lag far behind that of MOLAP, giving administrators a fairly clear choice based on their priorities and user needs.

Fortunately, there is a middle ground between MOLAP and ROLAP: hybrid OLAP (HOLAP). This is the best of both worlds, storing much of the cube's data in a relational form for scalability, but storing key access data in a more efficient form. You will no doubt be relieved to know that this is the way Microsoft implemented its OLAP services, although you can still use the other forms.

One Microsoft innovation is desktop OLAP (DOLAP), one that you are likely to use often in VBA applications. DOLAP supports using the data in a cube when disconnected from the server. You can slice and dice a full cube into sections of interest to a user, letting them work with a single dimension, a subset of a full multidimensional cube.

Issues

One issue that you will need to deal with is data explosion. A full discussion of this concept is beyond the scope of this paper, but it deals with cube design and scarcity of data that result in a cube that is far larger than the original data store. At some point the size of the cube obviates any advantage of using OLAP, and requires an increase in your disk drive budget, to boot. Performance, as you would expect, decreases with an increased size of a cube.

Yet another issue to deal with is the aggressiveness of the preaggregations you build into the cube. The basic process of creating a cube is aggregating data in ways that support the dimensions and levels that will be useful for end users. The more preaggregations you create in the cube, the less processing is needed at run time of the user's query. However, by preaggregating beyond the reasonable needs of the users means a far larger cube and much longer time needed to create and update a cube.

Microsoft SQL Server 7.0 and 2000 OLAP Services

Microsoft Excel 2002 includes the PivotTable and the Microsoft PivotChart® objects enhanced for OLAP data sources as shown in the Microsoft OLAP Services architecture (Figure 2). The Offline Cube Wizard creates a slice of data from the existing cube for a DOLAP cube that can be used offline. The PivotTable and PivotChart wizards have been enhanced to allow the use of OLAP external data sources.

Aa140063.odc_xlsql02(en-us,office.10).gif

Figure 2. The Microsoft OLAP Services architecture

The middle tier shown in yellow in Figure 2, which will be referred to as the client tier services to avoid confusion with n-tier programming concepts, is the interface between the client and cube data. These are the services that will interest you the most as a VBA programmer as these services give your applications access to OLAP data. The PivotTable Service, which is actually a service shared by both Excel and SQL Server, provides client-side cache and calculation engine services making data retrieval more efficient.

OLE DB is the data access component for OLAP and has been enhanced into OLE DB for OLAP, which is an interface for all application access to OLAP data. Microsoft ActiveX® Data Objects (ADO) has also been enhanced to handle OLAP data. ADO MD (MD stands for multidimensional) is an object-based data interface for applications that understands that parts of a cube.

In SQL Server there are a number of new services as well. These are mostly the purview of the database administrator who creates the OLAP cube, but as a VBA programmer using OLAP services you will benefit from understanding those features.

The Analysis Server contains the core computational features of OLAP Services, implemented as a Microsoft Windows NT® service. Decision Support Objects provide programmatic access to administrative functions, sort of like SQL Server DMO services. These two sets of features operate mostly in the background, but the OLAP Manager puts a front end to everything, acting as the administrative user interface for OLAP Services, implemented as a Microsoft Management Console (MMC) snap-in.

Creating an OLAP Cube

Creating an OLAP cube is a complex process of creating precomputed values called aggregations. There are three ways to create an OLAP cube. The first is through Microsoft Query using ODBC data sources. Once you create a query based on OLAP, the file menu has a Create OLAP Cube option that launches the OLAP Cube Wizard. The end result lets you save an .oqy file that you can then use in Excel 2002.

The second way to create a cube is through the Create Local Cube option in Excel 2002, allowing you to create a slice of a cube from a cube on the server. (The entry point to start the process of creating a cube in Excel 2002 is by clicking Data, pointing to Import External Data, and clicking New Database Query.) Like the Microsoft Query option, you have to create a PivotTable report first, and then save the desktop cube with the resulting data.

The final way to create an OLAP cube is by using the SQL Server OLAP Services, letting you create a powerful server-side cube from the raw data within SQL Server 2000. Also using its Data Transformation Services you can potentially create a cube from almost any source of data.

One decision you would need to make early on in the cube design process is where the cube is best located, on the client or server. A local file-based "offline cube file" allows users to continue working with the data when they are not connected to the server. A server-based cube requires connection to the server, but allows full use of all the SQL Server OLAP services making queries more efficient.

Excel OLAP Features

This isn't a tutorial on how to create a PivotTable report in Excel but a look at the features that you will be working with in your VBA applications. Figure 3 shows the structure of an Excel PivotTable report as you will encounter it when designing the PivotTable report. (The entry point to start the process of creating a PivotTable report in Excel 2002 is by clicking Data and clicking PivotTable and PivotChart Report. This will launch the PivotTable and PivotChart Wizard.)

Aa140063.odc_xlsql03(en-us,office.10).gif

Figure 3. Design structure of an Excel PivotTable report to which OLAP data fields will be added

Figure 4 shows how those elements relate to the actual PivotTable report that you produce. When you run the PivotTable and PivotChart Wizard, you will need to drag and drop data fields into the various areas of the table. The Page field is optional, acting as a kind of filter that lets you display subsets of the data.

Click here for larger image

Figure 4. Elements of an Excel PivotTable report as they relate to the design structure (click picture to see larger image)

Refreshing the data in a cube is a topic that is beyond the scope of this article. But you need to know how Excel responds to changing data on the server, what is dynamic PivotTable refreshing. Excel doesn't determine if a server recompute occurs (the cube is recomputed to reflect changing data on the server). Instead Excel just keeps issuing multidimensional expression (MDX) queries (a multidimensional OLAP query) during a session. If the cube changes and there is new data in the cube, nothing equivalent to an event is raised. This means that new sibling items will "automatically" appear when the new MDX is issued. If the data changes, the PivotTable report will reflect the new state of data.

More interesting is when items disappear from a server cube (for example levels, dimensions, cubes, and so on). When that happens, Excel repaints the PivotTable reasonably well.

What's New in Microsoft Excel 2002 PivotTable Reports

In Excel 2002, the PivotTable feature is enhanced for both OLAP and non-OLAP data. Some of the PivotTable report enhancements in Excel 2002 are as follows.

PivotTable Report User Interface Improvements

Many of the user interface changes in the PivotTable feature created greater consistency between the Office XP Web Component PivotTable report and the Excel 2002 PivotTable report. The following are the major improvements:

  • The new field well in Excel 2002 is easier to manipulate than the field well that was attached to the bottom of the PivotTable toolbar in Excel 2000. For OLAP data sources it allows you to drill in and see the levels within each hierarchy.
  • In Excel 2002, when you drop a field in the PivotTable row or column area, you will see its items immediately, instead of having to wait to have a data field. This makes it easier to conceptualize how the resultant PivotTable report will look, step by step. There is a button on the PivotTable toolbar to turn off this behavior for users who do not want it. For OLAP data sources, power users might want to turn this behavior off, because each drop causes a query to the OLAP server.
  • The field tree view dropdown has been improved with the addition of a Show All/Hide All checkbox. This makes it much easier to make multiple filtering selections in the PivotTable fields, even though it can clutter up the workspace, obscuring large tables. The new field list can also be docked by dragging it to the left or right edge of the worksheet window, if you find it more convenient in that position.
  • There is new way to create a PivotTable report other than the PivotTable wizard. By clicking Data, pointing to Import External Data and clicking Import Data, users can make PivotTable reports based on new Office data source files without having to use Microsoft Query or the query wizard. For some users, this will be easier than using the PivotTable wizard.
  • The PivotTable report commands have been consolidated onto one menu to make it easier for users to discover all the features that PivotTable reports provide. As a result of this consolidation, there were some changes in commands available from the PivotTable context menu, or buttons displayed on the PivotTable toolbar. The context menu now represents the most useful and commonly needed commands for PivotTable report users.

Dynamic Total Filtering for OLAP-Based PivotTable Reports

Traditionally, in non-OLAP data source PivotTable reports, when you hide an item in a field, that item's total is excluded from the subtotals for that field and for the PivotTable report grand total. In Excel 2000, this was not the case for OLAP based PivotTable reports. The total for OLAP sources was always the total of all children item in the actual cube, unaffected by any filtering applied by the user to the view.

This has changed in Excel 2002 so that OLAP sources work the same as traditional non-OLAP sources. When the user filters an item in the view, its value is removed from the subtotals for that field and from the grand total for the PivotTable report.

The defaults for PivotTable reports are now:

  • For an OLAP PivotTable report created in Excel 2000, the default will continue to be that filtering does not affect the displayed subtotals and grand totals. However, if you want to have the new behavior, press the new toolbar button with the ToolTip Include hidden items in totals to get the new behavior.
  • For all newly created PivotTable reports in Excel 2002, the default for OLAP sources will be the same as for traditional sources: filtering items in the view will change the subtotals and grand totals. If you want to revert to the Excel 2000 behavior for OLAP sources, you may toggle the toolbar button to get the desired behavior.

Note that there is a visual indication on the PivotTable report as to whether the totals include hidden items or not. If hidden items are included in totals, all subtotals and grand totals have an asterisk displayed next to them in the PivotTable report. (This is the default behavior. The user can go to the PivotTable options dialog and uncheck the Mark totals with * option.) All tables where hidden items are not included in the totals will not contain the * marking.

Custom Grouping in OLAP-Based PivotTable Reports

Traditional, non-OLAP PivotTable reports allowed users to select items on the PivotTable report and group them. In Excel 2000 this was not available for OLAP-based PivotTable reports. In Excel 2002, this functionality has been extended to OLAP-based PivotTable reports. This command has been added to Excel 2002 for OLAP sources. It is located in the PivotTable context menu. To group a set of items, select the items you want to group, then right click, and point to Group and Show detail and then click Group.

For OLAP data sources, grouping can only be done within a single parent in an OLAP dimension. For instance, under 1999, you could group "Quarter1" and "Quarter 2". But, you could not group "1999 Quarter 4" with "2000 Quarter 1", since that would cross a boundary between two parent items in the OLAP hierarchy, 1999 and 2000.

You will encounter a few differences in behavior:

  • The field name for the grouped field is not formed in the same way as for non-OLAP. It will come from the left rather than from the right.
  • Parents under which there is no grouping are bubbled down to the group level, instead of the items from the field to the right being bubbled up.
  • If there is grouping under a parent item, the remainder of the items under that parent which are not grouped are automatically formed into an Other group. The caption for this group can be renamed.

Multi-Select Page Fields in OLAP-Based PivotTable Reports

Often, users want to create a custom aggregate of multiple items in the page area of a PivotTable report and then show a table based on that custom set of items. In Excel 2000, this was not possible for OLAP data sources. It was possible for non-OLAP data sources, but the user interface to do so was not easy to discover. In Excel 2002 for OLAP-based data sources, you can now define a custom aggregate of multiple items in the page field, and there is a new user interface for doing so. (This new interface is for OLAP sources only; for non-OLAP sources, the previous method of hiding items in the page dropdown and then selecting All continues to function just as it did.)

When you drop down the page field drop-down, and if the Select multiple items checkbox (just above the OK/Cancel buttons) is enabled, you will find each item in the page field now has a check box by it. You can select any group of items when the check box is enabled. All values shown in the PivotTable report will be the aggregate of the selected values.

Improved Reference PivotTable Cells with Formulas on the Excel Worksheet

The Excel function GETPIVOTDATA is the best way to reference PivotTable data in a cell elsewhere on the sheet. However, the arguments for the GETPIVOTDATA function have proved to be quite complex to figure out. Now, while composing a formula on the worksheet, if you click in a PivotTable report cell, Excel automatically generates a GETPIVOTDATA formula.

OLAP Member Property Fields are Supported

OLAP databases support a feature called Member Property Fields. Member property fields are additional fields of information about a particular member in the OLAP database, but information that is not part of the dimension of information. For instance, a dimension may consist of the levels: Customer State, Customer City, and Customer Name. And for the field Customer Name, there might be several different other related pieces of information about the customer that are not part of the levels of the dimension, such as age, gender, occupation, income, and so on. These additional related pieces of information are called Member properties in the OLAP database, and you can use the menu item on the PivotTable drop-down menu on the PivotTable toolbar called Property Fields to access them.

Extensive PivotTable Report Object Model Improvements

There are a lot of new programmability features to the Excel object model, useful for creating powerful OLAP and non-OLAP PivotTable report applications.

Programming OLAP and PivotTable Reports with VBA

Finally we arrive at the fun part: using VBA to program Excel 2002 and SQL Server new OLAP features. The main thing that you will have to learn is the new objects in Office XP that support OLAP PivotTables. Most of the time you will work with the fields included in a PivotTable, adding and removing them to modify the end result that the user sees. Here are the main collections that Excel exposes:

  • PivotFields—All fields available in the data source
  • ColumnFields—Returns a collection of fields in the column area
  • DataFields—Returns a collection of fields in the data area
  • HiddenFields—Returns a collection of fields that are not displayed in the PivotTable report
  • PageFields—Returns a collection of fields in the page area
  • RowFields—Returns a collection of fields in the row area
  • VisibleFields—Returns a collection of fields that are displayed in the PivotTable report

The best part of all this is that a PivotTable report either paints or repaints itself based on the fields you use. One caution, however. Based on the design of the cube, a particular field from an OLAP cube, that is a measure, can be used in only certain areas of the table.

The other major new area, one that will take some getting used to, is the extensions to ADO. ADO has had hierarchical features since its first version, but now has an entire, detached object model to handle OLAP data. The ADO MD object model is related to, but detached from, the ADO object model. You can use either model separately or together. The objects in the ADO MD model reflect the objects in an OLAP cube, which is why you have to have a pretty good understanding of OLAP concepts before you can program OLAP.

ADO MD is based on an underlying OLE DB for OLAP specification, so you need an OLE DB provider that supports these features. To use ADO MD in your applications, you need to set a reference to the Microsoft ActiveX Data Objects (Multi-dimensional) 1.0 Library or whatever later version you have available. In your applications, the programmatic ID is ADOMD, which you will use instead of ADODB.

Figure 5 shows the ADO MD object model. Remember that this is separate from the regular ADO object model. The Positions, Members, and Properties collections are part of several objects as indicated by the numbered indexes.

Aa140063.odc_xlsql05(en-us,office.10).gif

Figure 5. The ADO MD object model

Let's look at some code now. There are several common tasks that you will need to perform programmatically. The first is adding a PivotTable object to your workbook.

Here is a sample code on how to accomplish this:

...
With ActiveWorkbook.PivotCaches _ 
    .Add(SourceType:=xlExternal)
    .Connection = _
        "OLEDB;Provider=MSOLAP.1;Data Source=adatum;" & _
        "Connect Timeout=60;Initial Catalog=FoodMart;" & _
        "Client Cache Size=25;Auto Synch Period=10000"
    .CommandType = xlCmdCube
    .CommandText = Array("Sales")
    .MaintainConnection = True
    .CreatePivotTable _
        TableDestination:= Range("A3"), _
        TableName:= "PivotTable1"
End With
...

There are many wizards exposed as methods that you can use in your applications. One such wizard is the PivotTable Wizard, exposed in VBA as the PivotTableWizard method. As with most wizards, there are a few parameters to this method, but by using named parameters you can use only those that differ from the defaults.

...
'Return current data range.
Set wksData = ThisWorkbook.Worksheets _
    (ORDER_DETAILS_EXTENDED)
Set rngData = wksData.UsedRange

'Create PivotTable report. 
Set pvtTable = wksData _
    .PivotTableWizard( _
        SourceType:=xlDatabase, _
        SourceData:=rngData, _
        TableDestination:=wksPivot.Range("B5"), _
        TableName:=SALES_BY_CATEGORY, _  
        RowGrand:=True, ColumnGrand:=True)
...

As mentioned above, a lot of your programming work will involve adding and changing the fields used in various parts of the table. Here are several examples showing how to add the fields. First, add a page field:

...
With ActiveSheet _
    .PivotTables("PivotTable1") _
    .CubeFields("[Customers]")
    .Orientation = xlPageField
    .Position = 1
End With
...

Next, add row fields. Note that you need to specify the relative position of the fields within the table by using the Position property.

...
With ActiveSheet _
    .PivotTables("PivotTable1") _
    .CubeFields("[Store]")
    .Orientation = xlRowField
    .Position = 1
End With

With ActiveSheet _
    .PivotTables("PivotTable1") _
    .CubeFields("[Store Size in SQFT]")
    .Orientation = xlRowField
    .Position = 2
End With
...

Next, add column fields to the table as shown by the code below:

...
With ActiveSheet _
    .PivotTables("PivotTable1") _
    .CubeFields("[Time]")
    .Orientation = xlColumnField
    .Position = 1
End With

With ActiveSheet _
    .PivotTables("PivotTable1") _
    .CubeFields("[Yearly Income]")
    .Orientation = xlColumnField
    .Position = 1
End With
...

Finally, you need to add one or more fields to the data area of the table:

...
With ActiveSheet _
    .PivotTables("PivotTable1") _
    .CubeFields("[Measures].[Profit]")
    .Orientation = xlDataField
    .Position = 1
End With

With ActiveSheet _
    .PivotTables("PivotTable1") _
    .CubeFields("[Measures].[Store Cost]")
    .Orientation = xlDataField
    .Position = 2
End With
...

One of the strengths of Excel is its ability to create a PivotChart report from the PivotTable data. This give users the ability to examine data in interesting visual forms, and the PivotChart report is updated dynamically should the data in the PivotTable report change. You can add a PivotChart to a worksheet using VBA as shown by the code snippet below.

...
Charts.Add
ActiveChart.SetSourceData _
    Source:=Sheets("Sheet2").Range("A3")
ActiveChart.Location _
    Where:=xlLocationAsNewSheet
With ActiveChart.PivotLayout _
    .CubeFields("[Customers]")
    .Orientation = xlPageField
    .Position = 1
End With
...

After this code, you can add fields in the same way you add them to a PivotTable.

PivotTable Web Component

Excel spreadsheets, by their nature, are dynamic entities, and it would be nice to have such dynamism on your Web pages. Here is where the Microsoft Office Web Components (OWC) comes in. Office Web Components are ActiveX controls that you can embed in your Web pages to allow interactivity. There are two Office Web Components of interest to OLAP VBA programmers, the PivotTable List and Chart. The PivotTable List control object model is shown in Figure 6.

Aa140063.odc_xlsql06(en-us,office.10).gif

Figure 6. The PivotTable List Web Component object model

Detailed discussion about the Office XP Web Components and how to use it is beyond the scope of this article. To find out more about it, the Microsoft Office 2000 Web Components Basics and Introducing the Office Web Component articles are good places to start. There is also a Microsoft Office XP Web Component Toolpack that is available for download from MSDN. The toolpack has extensive code samples and is a great resource to have.

Conclusion

OLAP is an end-user tool that you can use with Excel 2002 in powerful ways. By using an OLAP cube as the data source for the PivotTable and PivotChart reports in Excel, you can quickly build PivotTable and PivotChart reports that are visually attractive, easy to analyze and create. You can also use VBA to program Excel with SQL Sever 2000 Analysis Services OLAP tool.

To find out more about using Excel 2002 with SQL Server 2000 Analysis Services, here is a list of references to get you started.

Don Kiely has authored and co-authored several programming books, including Visual Basic Programmer's Guide to the Windows Registry from Mabry Software, and writes for several industry journals including InformationWeek, EarthWeb, and VBPJ. He is a Visual Basic, SQL Server, XML, and ASP instructor for Application Developers Training Company. To round out his day, he is Software Technologist for Third Sector Technologies in Fairbanks, Alaska.

Show: