Exploring Microsoft Data Analyzer Programmability

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.

Paul Cornell
Microsoft Corporation

October 2001

Applies to:

     Microsoft® Data Analyzer

Summary: This article briefly explores the programmability features of the Microsoft Data Analyzer. (6 printed pages)

Contents

Introduction
Setting Up Microsoft Data Analyzer
Programming the Microsoft Data Analyzer ActiveX Control
Additional Information

Introduction

The Microsoft® Data Analyzer is a software application, part of the Microsoft Office family of products, which enables you to graphically mine your organization's multidimensional data. Microsoft Data Analyzer is designed to work in conjunction with online analytical processing (OLAP) data based on Microsoft SQL Server™ 2000 with Analysis Services (currently, the only supported database is MSOLAP). For more information, visit the Microsoft Data Analyzer Web site.

Setting Up Microsoft Data Analyzer

To use Microsoft Data Analyzer, you need to connect to an OLAP database. For this column, I am using SQL Server 2000 with Analysis Services and the sample Food Mart 2000 database.

You can create a new connection to an existing database by clicking New on the File menu. In the Define View—Connections dialog box, use the Add or Edit buttons to create or edit a database connection.

If you click the Add button, you will need to provide a name for the connection, as well as the name of the OLAP database server, a local OLAP .cub file, or an HTTP URL containing the OLAP data. Once you have entered this information, click the Connect button. You will then need to pick the data catalog and the OLAP cube in the database.

For the Food Mart 2000 database, I will select the Sales OLAP cube.

Programming the Microsoft Data Analyzer ActiveX Control

In addition to the standalone user interface, Data Analyzer supplies as an ActiveX® control. This enables you to programmatically control Data Analyzer from COM-based development applications such as Microsoft Visual Basic®. For example, you could embed the Microsoft Data Analyzer ActiveX control on a UserForm and manipulate it with Visual Basic for Applications (VBA) code, embed the Microsoft Data Analyzer ActiveX control on a Visual Basic form and manipulate it with Visual Basic code, or embed the Microsoft Data Analyzer ActiveX control on a Web page and manipulate it with Microsoft Visual Basic Scripting Edition (VBScript) code.

Some of the features you can programmatically control with the Microsoft Data Analyzer ActiveX control are:

• Creating, loading, configuring, and saving database views.
• Changing properties such as colors, visualization methods, dialog boxes, analysis algorithms, and built-in functions.
• Running menu items.

Using the Microsoft Data Analyzer ActiveX Control in a UserForm

You can embed the Microsoft Data Analyzer ActiveX control in a UserForm and manipulate it with VBA code. To do so, on a computer with Data Analyzer installed, add a UserForm to a VBA project, then in the Toolbox (View menu), right-click and select Additional Controls. In the Available Controls dialog box, check the Max3Ax Class box, and then click OK. Finally, drag the Max3Ax Class control onto the UserForm. At design time, the Microsoft Data Analyzer ActiveX control's user interface will not be visible. At run time, the Microsoft Data Analyzer ActiveX control's user interface is visible and runs within the confines of the UserForm.

To program against the Microsoft Data Analyzer ActiveX control, you must also set a reference (References dialog box, Tools menu) to the Max3API DLL (in a default installation, this DLL can be found at C:\Program Files\Microsoft Data Analyzer\Data Analyzer 3.5\Max3API.dll).

Using the Microsoft Data Analyzer ActiveX Control in a Web Page

You can also include the Microsoft Data Analyzer user interface on a Web page. To do so in Microsoft FrontPage® 2002 with Data Analyzer installed:

• On the Insert menu, click Web Component.
• In the Component Type list, select Advanced Controls, and in the Choose an effect list, click ActiveX Control, and then click Next.
• In the Choose a control list, if Max3Ax Class is not available, click Customize and check the Max3Ax Class box, then click OK. After selecting the Max3Ax Class entry in the Choose a control list, click Finish.

Alternatively, if you are not using FrontPage to create your Web page, you can manually type the following HTML code in a Web page:

<object classid="clsid:E0ECA9C3-D669-4EF4-8231-00724ED9288F" 
      width="100%" height="100%" id="Max3Ax1"/>

The Microsoft Data Analyzer Object Model

The following tables includes a description of the objects and collections in the Microsoft Data Analyzer object model:

In the balance of this article, I will show you how to programmatically manipulate the Microsoft Data Analyzer ActiveX control through VBA code. You can easily adapt this code to Visual Basic or VBScript, depending on your solution environment.

Working with Views

The Application object's ActiveView property returns a View object representing the active database view. Two of the most common methods of the View object are OpenView and SaveView, as shown in the following example code:

Private Const VIEW_NAME As String = _
   "C:\Program Files\Microsoft Data Analyzer\" & _
   "Data Analyzer 3.5\FoodMart2000Connection.max"

Private Sub Max3Ax1_Initialized()
    
    With Max3Ax1.Application.ActiveView
        .OpenView VIEW_NAME, vlocFileSystem
        ' Do some processing on the view here.
        .SaveView VIEW_NAME, vlocFileSystem
    End With
    
End Sub

Working with Built-In Dialog Boxes

You can use the Application object's ShowDialog method to display built-in dialog boxes. The EMaxDialogs enumerated type contains constants for the various built-in dialog boxes; for example, to show the Change View dialog box, you would use the following code:

...
Max3Ax1.Application.ShowDialog Dialog:=mxDlgChangeView
...

Working with the Toolbar, Main Menu, and Status Bar

The Main Toolbar, the main menu, and the Status Bar can be made visible or hidden by setting the Band object's Visible property to True or False, as shown in the following example code:

...
' Hide the Main Toolbar, the main menu, and the Status Bar.
With Max3Ax1.Application.MainToolbar
    .Bands(Index:="MainToolBar").Visible = False
    .Bands(Index:="MainMenu").Visible = False
    .Bands(Index:="Main.StatusBar").Visible = False
End With
...

Note   If you hide the main menu, this setting will not persist if Microsoft Internet Explorer is refreshed.

This is a great technique if you want to disables many of the "bells and whistles" in the interface from the user at run time.

Working with Qualities and Traits

A quality is a unit of measurement in a database view. A trait is the type of measurement (for example, length or color). Traits, once defined, are the same for all aspects in a database view.

The following example code reports on the color trait:

...
' Reports the value of the Color trait
' (Change View dialog box (View menu), Measures tab,
' Color list).
MsgBox Prompt:=Max3Ax1.Application.ActiveView.TraitsManager _
    .Trait(TraitID:=trtColor).Qualities.QualityID(v:=0)
...

You can use the Add, Clear, and Remove methods of the Qualities object to add, clear, and remove qualities.

Working with Aspects

An aspect is a dimension in an OLAP cube, such as time, geographical location, product, and so on. The Aspects collection allows you to use the Add, Remove, and Clear methods to add, remove, and clear aspects, respectively. When you use the Add or Remove methods, you must specify the unique dimension name in the data cube, and you must enclose the dimension's name in brackets "[ ]". For example:

...
With Max3Ax1.Application.ActiveView
    ' Next line of code will cause an error if
    ' this dimension doesn't exist in the database or
    ' this dimesion is already visible in the view.
    .Aspects.Add "[Education Level]"
    ' Next line of code will cause an error if
    ' this dimension is not already visible in the view.
    .Aspects.Remove "[Gender]"
End With
...

This technique is useful if you want to conditionally add or remove visible dimensions at run time.

Additional Information

For more information about working with the Microsoft Data Analyzer object model, see the MSDA35om.chm file that is included with Microsoft Data Analyzer.