Enhancing Excel Business Intelligence Capabilities with Visual Studio 2005 Tools for Office

This content is outdated and is no longer being maintained. It is provided as a courtesy for individuals who are still using these technologies. This page may contain URLs that were valid when originally published, but now link to sites or pages that no longer exist.

Steve Hansen, www.dakotatechgroup.com

Lori Turner, Microsoft Corporation

February 2006

Applies to: Microsoft Visual Studio 2005, Microsoft Visual Studio 2005 Tools for the Microsoft Office System, Microsoft Office Excel 2003, Microsoft SQL Server 2000, Microsoft Analysis Services 2000

Summary: Develop and implement a business intelligence application quickly with Microsoft Visual Studio 2005 Tools for the Microsoft Office System, Microsoft Analysis Services, and Microsoft Office Excel 2003. In this FoodMart Analyzer Solution, you target the Document Actions task pane to provide users with a rich, context-aware user interface. (22 printed pages)

Download OfficeVSTOFoodmartAnalyzer.msi.

Contents

• Scenario Overview

• FoodMart Analyzer Solution Software

• System Requirements

• Running the FoodMart Analyzer Solution

• General Solution Design

• Development Notes

• Extending the Solution

• Code Security

• Conclusion

• Additional Resources

• About the Author

Scenario Overview

The FoodMart Analyzer Solution demonstrates how you can use Microsoft Visual Studio 2005 Tools for the Microsoft Office System (Visual Studio 2005 Tools for Office) to develop a business intelligence application with an enhanced user experience. The Solution uses Microsoft Office Excel PivotTable dynamic views as the front-end application for a Microsoft Analysis Services 2000 database.

The FoodMart Analyzer Solution is an application built primarily on the FoodMart 2000 Sales cube. FoodMart 2000 is a sample database that is included with Analysis Services, which is included in Microsoft SQL Server version 2000.

Note   A cube is a data processing unit composed of fact tables and dimensions from a data warehouse. A cube provides a multidimensional view of data.

The following table shows the controls in the Document Actions task pane (actions pane) for the FoodMart Analyzer Solution.

Table 1. Actions pane controls in the FoodMart Analyzer Solution

Control	Description
Member Locator	Searches a dimension or all dimensions for a particular member.
Customer Details	Displays customer details in the FoodMart 2000 Sales cube.
Product Details	Displays product details in the FoodMart 2000 Sales cube.
Store Details	Displays store details in the FoodMart 2000 Sales cube.
Member SmartTags	Recognizes members from specific dimensions and offers convenient actions based on the member. The store dimension includes an action that displays a map of the store's location supplied by the Microsoft MapPoint Web service.
Settings	Displays connection information and the multidimensional expressions (MDX) associated with a particular PivotTable. (Analysis Services uses the MDX language to query a database, just as SQL Server uses SQL to query a database.) As a user convenience, Settings also contains Excel settings for PivotTables.

Figure 1. FoodMart Analyzer Solution actions pane

FoodMart Analyzer Solution Software

The FoodMart Analyzer Solution combines several Microsoft products and technologies to build a business intelligence application.

Visual Studio 2005 Tools for Office

Visual Studio 2005 Tools for Office provides a professional development environment with full access to all of the Microsoft .NET controls, use of Windows Forms, and access to the actions pane. Using Visual Studio 2005 Tools for Office, you can develop in managed code using a .NET-based language, such as Microsoft Visual C# or Microsoft Visual Basic.

Access to the actions pane is a good reason in itself to use Visual Studio 2005 Tools for Office. We have never liked the user interface (UI) of typical Excel Solutions because the location of the UI is unclear unless you embed controls on a worksheet. When you do so, the result can seem unfinished and clumsy to users.

In contrast, the actions pane allows you to develop a UI that is conceptually clean to users. If you are a Microsoft Visual Basic for Applications (VBA) developer, and the actions pane is unavailable to you, you must either implement the ISmartDocument interface in your custom assembly or use Visual Studio 2005 Tools for Office. If you have tried both, you already know that the practical choice is to use Visual Studio 2005 Tools for Office.

Analysis Services

As noted earlier, Analysis Services is included in Microsoft SQL Server version 2000. It enables you to develop online analytical processing (OLAP) databases. In contrast to relational databases, OLAP databases report numeric data. Users benefit from OLAP databases because such databases are very responsive. In addition, users do not have to know specialized (and expensive) visual query tools or SQL Server. Developers benefit from OLAP databases because they handle aggregation and pivoting on multiple dimensions natively.

Excel PivotTables

Excel PivotTables are excellent for providing a visual display for Analysis Services data. However, there are a few features that PivotTables in Excel do not support. The FoodMart Analyzer Solution addresses these limitations in PivotTables:

• Search capability. In all but the simplest cubes, dimensions typically have multiple levels. If you want to display a specified dimension member, you must know where the member is located within the dimension. You typically drill down on all of the members in the dimension to find the member you want. Not only is this an inefficient way to find members, it can result in poor performance, especially if the dimension in which you search is nested within another dimension, or vice versa.

• Member properties lookup and display. At times you would like to look up properties associated with a specified member. Although you can display member properties within a PivotTable, you have little flexibility about how they are displayed.

• Dimension details and actions. Although it makes sense to define some properties in the Sales cube, useful member information also comes from other sources, such as a Web service or a data mart.

  In contrast, the FoodMart Analyzer Solution shows information about a specified member from other data sources. When you build a dimension with Analysis Services, you can associate properties with a dimension. For example, if a dimension consists of retail stores, you can associate properties with each store. Properties might include the manager's name, the total square footage, or the number of available parking spaces.

System Requirements

To use the FoodMart Analyzer Solution, you must install the following software:

• Microsoft Visual Studio 2005 (After installation, open the FoodMart Analyzer Solution)

• Microsoft Visual Studio 2005 Tools for the Microsoft Office System

• Microsoft Office Excel 2003 or Microsoft Office Professional Edition 2003 SP1 (complete installation)

  Only a complete installation includes the primary interop assemblies.

• Microsoft Analysis Services 2000 (with Microsoft SQL Server 2000 SP3 or higher)

  For more information, see Running Setup.

• The download package associated with this article

To modify your server connection string

Be default, the FoodMart Analyzer Solution uses the localhost server. To use a different server, you must modify the connection string.

1. In Solution Explorer, right-click the FoodMart Analyzer project, and then select Properties.

2. Select the Settings tab.

3. Change the data source in the ConnectionString setting from localhost to the name of your server.

To provide your user credentials for the MapPoint Web service (optional)

1. Change the MapPointAccountName setting value to your user ID.

2. Change the MapPointAccountPassword setting value to your password.

   Note

   If you do not already have a MapPoint Web service account, you can request a developer test account on the Microsoft MapPoint Web site. Access to the Web service is needed only to display maps of store locations; the Web service is optional, and the FoodMart Analyzer Solution runs properly without it. Performance for a developer test account may be slower than for a paid, commercial account because test accounts have a lower priority.

Running the FoodMart Analyzer Solution

From the actions pane in the FoodMart Analyzer Solution, you can query a cube directly and retrieve data without using an Excel PivotTable.

To compile and run the FoodMart Analyzer Solution

1. In the FoodMart Analyzer Solution application, press F5, and then click Products in the actions pane.

   The first time you select Products, the application retrieves all product names from the data source. Consequently, this step might take a moment.

2. Select a product from the box to view a sales summary for the product you chose.

3. Click Home.

4. In the actions pane, click Customers.

5. Select the type of customer sales data you want to see.

6. Click Home.

7. In the actions pane, click Stores.

8. Select a store in the box to view details about that store.

   If you provided user credentials for the MapPoint Web service when you set up your system, you see a map for the store location along with other store details. The store manager's name appears as a link label in the actions pane.

9. To open a new e-mail message addressed to the store manager, click the link label.

10. Click Home.

11. In the actions pane, click Search Dimensions.

12. In the Search for box, type Store 2.

13. Select Store in the dimension dropdown box, and then click SearchNow to start the search.

    The search results box contains a list of stores that match your search criteria.

In addition to providing direct access to a cube, the FoodMart Analyzer Solution uses smart tags to extend the functionality of Excel PivotTables. Smart tags provide the user with more detailed cell data than PivotTables alone do.

To use smart tags to view detailed data

1. Click Home.

2. Select a cell on the worksheet.

3. Click Create PivotTable in the actions pane, and then click Yes when prompted to create a cube at the current location.

4. Click PivotTable Settings in the actions pane.

5. Click the View MDX link label in the actions pane to see the MDX code behind the PivotTable.

6. Dismiss the View MDX dialog box.

7. Double-click USA in the PivotTable to show the stores in United States.

8. Double-click a state abbreviation, such as WA, CA, or OR, to show the stores in that state.

9. Double-click a city name to show the stores in that city.

10. Click the smart tag indicator for a store, and then select View Store Details.

    The store detail information appears in the actions pane.

General Solution Design

Aside from smart tags and a single Windows Form for displaying MDX code, the actions pane provides the UI for the FoodMart Analyzer Solution. Using the actions pane as the primary UI is simpler than the traditional UI used for Excel Solutions: custom spreadsheet controls, forms, and menu items mixed with native Excel menu items.

Member Locator

The Member Locator control in the actions pane allows a user to search for a particular member in the cube or a particular dimension. This is useful when dimensions have multiple levels. Without this feature, the user must know where a member resides in the hierarchy to use that member as a page field or to select it as a row or column in a PivotTable.

Member Locator is a drop-down list control that lists all of the dimensions in the cube and a box that accepts the search term. When the user initiates a search, the actions pane displays potential matches in a box.

Figure 2. Member Locator actions pane

Dimension Details Controls

Customer Details, Product Details, and Store Details are dimension-details controls in the actions pane that retrieve information. For example, the Store Details control retrieves information about a store, such as the number of employees, the size of the store, and the name of the store's manager. Because this information is available as member properties in the Sales cube, the FoodMart Analyzer Solution uses MDX code to query the cube and retrieve the information. In addition, the Store Details control uses the Microsoft MapPoint Web service to display a map of the store's location.

Figure 3. Product Details actions pane

Member Smart Tags

The FoodMart Analyzer Solution demonstrates how you can create smart tags based on members found in a dimension. In particular, you can implement smart tags based on the members in the store dimension. The only purpose of these smart tags is to display details about the store that the user selects in the actions pane.

Figure 4. Store Name smart tags

Figure 5. Store Details actions pane

Settings

Using the Settings control, you can view the name of the PivotTable, the server where the database resides, and the MDX code used by the PivotTable to retrieve the data. This control also implements a few simple, but useful, features for hiding and showing the field list, and for toggling the GETPIVOTTABLE function on and off.

Figure 6. Settings control

Development Notes

Each component in the FoodMart Analyzer Solution interacts with the Sales cube in some way. This section provides an overview of how to retrieve data from the cube.

Retrieving Data from the Sales Cube

The FoodMart Analyzer Solution has a FoodMartData class that manages the connections to the data source and retrieves required data. When the application instantiates the FoodMartData class, it opens a connection to the Sales cube; the application uses the ConnectionString project setting to make this connection.

FoodMartData exposes several methods that query the cube and return a specific type of data, for example, the StoreNames, ProductNames, and StoreProperties methods. Although each method uses different queries to return different sets of data, each method builds its own OleDbCommand object for required data, passes that OleDbCommand to a RetrieveData function to acquire an ArrayList of data, and then returns that ArrayList to the caller.

The StoreNames method, shown in the following example, is typical of the methods in the FoodmartData class:

Friend Function StoreNames() As ArrayList
    Using oleDBCommand As OleDb.OleDbCommand = _
        New OleDb.OleDbCommand( _
        "WITH MEMBER [Measures].[StoreName] AS " & _
        "'[Store].CurrentMember.Name' " & _
        "SELECT {[Measures].[StoreName]} ON Columns, " & _
        "{Descendants([store], 7, LEAVES)} ON Rows FROM Sales", _
        connection)
        Return RetrieveData(oleDBCommand, 1)
    End Using
End Function

The command used by the StoreNames method, as well as the other methods in the FoodmartData class, are MDX statements, not SQL statements. Several of the commands in the FoodmartData class use the Descendants function, which retrieves members in a dimension relative to a dimension member. For example, nodes that do not have any children (leaf nodes), such as the individual store names, are what matter in the StoreNames method. The following example shows this:

WITH 
   MEMBER [Measures].[StoreName] AS '[Store].CurrentMember.Name'
SELECT 
   {[Measures].[StoreName]} ON Columns, 
   {Descendants([store], 7, LEAVES)} ON Rows 
FROM Sales

In this function, the number 7 specifies how many levels in the dimension to return. Although you can pass any number that is greater than the number of levels in the cube, the function uses seven because the FoodMart 2000 Sales cube does not contain more levels than that in any dimension. In many cases, you do not know the appropriate number of levels in advance, especially if you did not design the cube. Furthermore, if you have a ragged dimension, leaf nodes can appear at any level.

When you use OLE DB and the OLAP provider to query a cube, the data that the database returns is a flattened cellset.

Note   OLE DB is a low-level, COM API that is used for accessing data. A cellset is the result of a query on multidimensional data.

For example, the MDX statement mentioned previously in the StoreNames method returns data in rows and columns, as shown in table 2.

Table 2. Data returned in rows and columns by the StoreNames method

[Store]. [Store Country]	[Store]. [Store State]	[Store]. [Store City]	[Store]. [Store Name]	[Measures]. [StoreName]
Canada	BC	Vancouver	Store 19	Store 19
Canada	BC	Victoria	Store 20	Store 20
USA	CA	Alameda	HQ	HQ
USA	CA	Beverly Hills	Store 6	Store 6
USA	CA	Los Angeles	Store 7	Store 7
. . .	. . .	. . .	. . .	. . .
Mexico	Zacatecas	Hidalgo	Store 18	Store 18

From this cellset, the code returns only the required data to the caller. This is where the RetrieveData method comes in.

RetrieveData does not necessarily return all the columns in the dataset. Rather, it returns an ArrayList containing the n rightmost columns; n is the value you pass in through the measureCount parameter. For the StoreNames method, only the rightmost column (column 1) matters because that column contains the name of each store. Thus, when StoreNames calls the RetrieveData function, it passes a value of 1 for the measureCount parameter, as shown in the following example:

Private Function RetrieveData( _
    ByVal oleDBCommand As OleDb.OleDbCommand, _
    ByVal measureCount As Integer) As ArrayList
    Try
        Using dataReader As OleDb.OleDbDataReader = _
            oleDBCommand.ExecuteReader
            Dim dataArray As New ArrayList
            While (dataReader.Read())
                Dim values(measureCount - 1) As Object
                For i As Integer = 1 To measureCount
                    values(i - 1) = dataReader.GetValue( _
                        dataReader.FieldCount - measureCount _
                        + i - 1).ToString()
                Next
                dataArray.Add(values)
            End While
            dataReader.Close()
            Return dataArray
        End Using
    Catch oleDBException As OleDb.OleDbException
        ' Add exception handling that is
        ' appropriate for your circumstances here.
        Return Nothing
    End Try
End Function

In the FoodmartData class, OLE DB interacts with Analysis Services. Although this is convenient and portable for a sample solution, it is not the most flexible way to interact with Analysis Services. It does, however, demonstrate the flexibility of OLE DB to handle disparate data sources.

Note   Because MDX uses an OLAP database that is hierarchical, it is a more powerful way to interact with Analysis Services than Microsoft ADO.NET, which uses a relational database. For more information, see ADOMD.NET, a library that is designed specifically for work with Analysis Services data. Also, read MDX Solutions with Microsoft SQL Server Analysis Services by George Spoffond.

Managing Actions Pane Controls

FoodMartAnalyzer is the default actions pane control in the FoodMart Analyzer Solution. It provides a central point from which to access all of the features of the Solution. Because FoodMartAnalyzer only displays the other controls as needed, it is the easiest to implement.

FoodMartAnalyzer primarily consists of link label controls. FoodMartAnalyzer has a single LinkClicked procedure that handles the LinkClicked event for each link label on the FoodMartAnalyzer control, as shown in the following example:

Private Sub LinkClicked(ByVal sender As System.Object, _
ByVal e As System.Windows.Forms.LinkLabelLinkClickedEventArgs) _
Handles ShowCustomers.LinkClicked, ShowProducts.LinkClicked, _
ShowSearch.LinkClicked, ShowSettings.LinkClicked, _
ShowStores.LinkClicked, CreateCube.LinkClicked
    ' Execute the action associated with the link label
    Globals.ThisWorkbook.ExecuteAction(sender.tag)
End Sub

The ThisWorkbook class plays an important role in the FoodMart Analyzer Solution because it holds references to all of the actions pane controls that make up the Solution. In other words, ThisWorkbook determines and manages the workflow. Visual Studio 2005 Tools for Office creates the ThisWorkbook class automatically when you create an Excel Solution. How you use ThisWorkbook and any of the worksheet objects is a little different than how you may have used equivalent objects in VBA. ThisWorkbook and all of the worksheets are members of a Globals class that is accessible from any other code residing in the FoodMart Analyzer Solution. You can see an example of this in the LinkClicked event handler. The event handler calls the ThisWorkbook.ExecuteAction method.

One of the reasons ThisWorkbook plays such a prominent role in the FoodMart Analyzer Solution is that the ActionsPane object is a member of ThisWorkbook. Because the Solution implements the UI by means of actions pane controls primarily, you must use ThisWorkbook frequently to access the ActionsPane object. The ActionsPane object itself contains numerous useful properties and methods.

The process of displaying the actions pane and adding a control to it requires only a few of lines of code. For example, the following code creates a new instance of a control, adds it to the ActionsPane.Controls collection, and displays the actions pane:

myControl = New MyControl
ActionsPane.Controls.Add(myControl)
ActionsPane.Show()

Because the FoodMart Analyzer Solution has multiple actions pane controls, it declares a private variable for each actions pane control is uses. In addition, the Solution uses only one instance of each control. Consequently, it has an InitializeActionsPane procedure that creates an instance of each control. The ThisWorkbook_Startup event handler calls this procedure, as shown in the following example:

Private Sub InitializeActionsPane(ByVal data As FoodmartData)
    ' You must add the controls to the ActionsPane.Controls
    ' collection.
    actionsMenu = New ActionsPaneMenu
    ActionsPane.Controls.Add(actionsMenu)
    foodMartAnalyzer = New FoodMartAnalyzer
    cubeProperties = New CubeProperties(data)
    storeDetails = New StoreDetails   
    productDetails = New ProductDetails()
    customerDetails = New CustomerDetails
    memberLocator = New MemberLocatorControl
    ManageControls(foodMartAnalyzer)
    foodMartAnalyzer.ShowSettings.Enabled = False
    Me.ActionsPane.Show()
End Sub

The FoodMart Analyzer Solution has a ManageControls procedure to manage which controls are displayed at any time in the actions pane. ManageControls hides all controls except for the actions pane menu control and the control that is passed in as a parameter. The actions pane menu is always visible because it displays the FoodMart Analyzer Solution title. More importantly, the actions pane menu has a Home link label that returns the user to the starting point, as shown in the following example:

Private Sub ManageControls(ByVal control As UserControl)
    Application.ScreenUpdating = False
    Try
        ' Hide all controls except the menu control; then,
        ' display the control that is passed in as the parameter
        ' to this function
        For Each c As Control In ActionsPane.Controls
            If c IsNot actionsMenu Then c.Visible = False
        Next
        If control.Parent Is Nothing Then
            ActionsPane.Controls.Add(control)
        End If
        control.Visible = True
        control.Dock = DockStyle.Fill
        actionsMenu.ShowHome.Visible = _
            control IsNot foodMartAnalyzer
    Catch
        ' Add appropriate exception handling here
    Finally
        Application.ScreenUpdating = True
    End Try
End Sub

To add the controls to the ActionsPane.Controls collection, you can add all of the controls at startup or add them when they are needed. For the FoodAnalyzer Solution, the second approach works best because each control has its own initialization requirements, which delays loading the workbook.

The FoodMart Analyzer Solution creates a new instance of each actions pane control in InitializeActionsPane, but adds only the FoodMartAnalyzer control and the ActionsPaneMenu control to the ActionsPane.Controls collection initially. Consequently, until a control is added to the actions pane, its Parent is nothing. The ManageControls procedure checks for this condition, and then adds a control to the ActionsPane.Controls collection, if necessary, before making the control visible.

Working with Smart Tags

Because the FoodMart Analyzer Solution builds smart tags that recognize members from one or more dimensions in the cube, You can associate many useful actions with its smart tags. Specifically, the FoodMart Analyzer Solution implements smart tags that recognize store names and allow you to view the details associated with those stores.

Implementing smart tags with Visual Studio 2005 Tools for Office is straightforward. First, you create a class that inherits from Microsoft.Office.Tools.Excel.SmartTag. Then, in your smart tag class constructor or some other member, you take these actions:

• Define the expressions that the smart tag should recognize.

• Define user actions to associate with the smart tag.

• Add the user actions you defined to the Actions collection of the smart tag.

In the FoodMart Analyzer Solution, the smart tag class is named StoreSmartTag, as shown in the following example:

Public Class StoreSmartTag
    Inherits Microsoft.Office.Tools.Excel.SmartTag
    Dim WithEvents storeDetailsAction As Action
    Public Sub New(ByVal data As FoodmartData)
        ' Call smart tags base New procedure to ensure proper 
        ' creation.
        MyBase.New("http://www.example.com/SmartTags#Store", _
            "Store Smart Tag")
        ' Retrieve all of the store names from the cube
        ' and create the appropriate regular expressions.
        Dim storeNames As ArrayList = Data.StoreNames
        For Each store As Object In storeNames
            Expressions.Add( _
               New Regex(store(0).ToString().Replace(" ", ".")))
        Next
        ' Create a new action for the smart tag that displays the store 
        ' details.
        ' Additional smart Tag actions could be created here as well.
        storeDetailsAction = New Action("View Store Details")
        ' Add the action to the smart tag's Actions collection.
        Actions = New Action() {storeDetailsAction}
    End Sub
    Private Sub storeDetailsAction_Click(ByVal sender As Object, _
        ByVal e As Microsoft.Office.Tools.Excel.ActionEventArgs) _
            Handles storeDetailsAction.Click
        Globals.ThisWorkbook.ShowStoreDetails(e.Text)
    End Sub
End Class

Visual Studio 2005 Tools for Office uses the Microsoft Office tokenizer to parse strings, punctuation, and white space into the actual words that the smart tag recognizer uses. If a word includes white space or punctuation, for example, Store 1, you must use a regular expression. If a word has no white space or punctuation, you can use Expressions.Add("WordToRecognize"). Because store names in the FoodMart 2000 Sales cube are two words, the FoodMart Analyzer uses a regular expression.

To connect the StoreSmartTag class to the FoodMart Analyzer Solution at run time, you create a new instance of the StoreSmartTag class, and then add it to the VstoSmartTags collection. The FoodMart Analyzer Solution does this at startup, just before it initializes the actions pane controls, as shown in the following example:

Private Sub ThisWorkbook_Startup(ByVal sender As Object, _
    ByVal e As System.EventArgs) Handles Me.Startup
    Dim data As FoodmartData
    Try
        data = New FoodmartData
        ' Create the Store smart tags.
        Me.VstoSmartTags.Add(New StoreSmartTag(data))
        ' Initialize the Actions Pane with default controls.
        InitializeActionsPane(data)
    Catch ex As OleDb.OleDbException
        MessageBox.Show( _
          "An error occurred opening a connection to the database." & _
          vbCrLf & vbCrLf & ex.Message, "Foodmart Analyzer", _
          MessageBoxButtons.OK)
    Finally
        If data IsNot Nothing Then data.Close()
    End Try
End Sub

Integrating the Solution with the MapPoint Web Service

As noted earlier, the FoodMart Analyzer Solution implements the MapPoint Web service to retrieve a map of a store's location. The StoreDetails control in the actions pane consumes this image to display along with other store information that it retrieves from the FoodMart 2000 cube. The StoreDetails control demonstrates how you can bring together data from multiple sources into one control that presents an integrated experience for the user.

To add a reference to the MapPoint Web service

1. From the Project menu, select Add Web Reference.

2. In the Add Web Reference dialog box, type the following URL:

   http://staging.mappoint.net/standard-30/mappoint.wsdl

   and then click Go.

   The MapPoint Web Service provides four services: CommonService, FindService, RenderService and RouteService.

3. Click Add Web Reference.

   Visual Studio generates the necessary proxies to instantiate and call the Web services.

Figure 7. Add a reference to a Web Service

To work easily with the Web service from the StoreDetails control, the FoodMart Analyzer Solution adds Imports directives to the top of the class module, as shown in the following example:

Imports System.Web.Services.Protocols
Imports FoodMart_Analyzer.net.mappoint.staging
Imports System.Net

The FoodMart Analyzer Solution uses the MapPoint Web service to find and render a map using the FindService and RenderService services. It adds private, class-level variables to hold a reference to those services, as shown in the following example:

Private renderService As RenderServiceSoap
Private findService As FindServiceSoap

The Web service initialization code is in a single InitializeWebService procedure. Initializing a MapPoint Web service requires two steps for each service: providing user credentials and setting the PreAuthenticate property, as shown in the following example:

' Initialize the credentials used for the Web service
Dim ourCredentials As New _
    NetworkCredential(My.Settings.MapPointAccountName, _
    My.Settings.MapPointAccountPassword)
' Initialize the Render service
renderService = New RenderServiceSoap
renderService.Credentials = ourCredentials
renderService.PreAuthenticate = True
' Initialize the Find service
findService = New FindServiceSoap
findService.Credentials = ourCredentials
findService.PreAuthenticate = True

The FoodMart Analyzer Solution stores the credentials for the Web service in the MapPointAccountName and MapPointAccountPassword project settings. This approach to storing account information is fine for samples. However, in a real development project, you must protect your account information. Thus, generating a map becomes a four-step process:

1. Initialize the MapPoint Web services, if this is not already done.

2. Use FindService to create a MapPoint specification for a specified location.

3. Define how you want to view the map.

4. Use RenderService to render the map image.

The FoodMart Analyzer Solution encapsulates these tasks into the GetMapprocedure. GetMap requires a single string parameter that describes the store location. After GetMap has the image bits, it displays them in the PictureBox control on the StoresDetail control.

Private Function GetMap(ByVal storeLocation As String) As Boolean
    If Not InitializeWebService() Then Return False
    ' Create a find specification that specifies
    ' which MapPoint data source to use and
    ' what to look for.
    Dim findSpec As New FindSpecification()
    findSpec.DataSourceName = "MapPoint.NA"
    findSpec.InputPlace = storeLocation
    ' Get a list of places that match the
    ' find specification (perform the search).
    Dim foundResult As FindResults
    Try
        foundResult = findService.Find(findSpec)
        ' Define how to view the resulting map.
        Dim viewByHeightWidth(0) As ViewByHeightWidth
        viewByHeightWidth(0) = New ViewByHeightWidth()
        viewByHeightWidth(0).CenterPoint = New LatLong()
        viewByHeightWidth(0).CenterPoint.Latitude = _
            foundResult.Results(0).FoundLocation.LatLong.Latitude
        viewByHeightWidth(0).CenterPoint.Longitude = _
            foundResult.Results(0).FoundLocation.LatLong.Longitude
        viewByHeightWidth(0).Height = 121
        viewByHeightWidth(0).Width = 121
        ' Set the map specification.
        Dim mapSpec As New MapSpecification()
        mapSpec.DataSourceName = "MapPoint.NA"
        mapSpec.Views = viewByHeightWidth
        ' Render the map on the PictureBox control.
        Dim mapImages() As MapImage
        mapImages = renderService.GetMap(mapSpec)
        StoreMap.Image = New System.Drawing.Bitmap( _
            New System.IO.MemoryStream(mapImages(0).MimeData.Bits))
        Return True
    Catch ex As WebException
        Return False
    End Try
End Function

As we developed the FoodMart Analyzer Solution, defining the view was the most difficult part of working with the MapPoint Web service to render a map image. Although I used a basic view, MapPoint has many ways to define a view. I found it difficult to navigate through all of the options.

In contrast, I found it easy to define and execute a search. I chose the MapPoint.NA (North America) data source, and then passed a city and state for the InputPlace property used in the search. (The MapPoint Web service has other options.) To execute the search, I called the Find method associated with the MapPoint FindService, passing FindSpecification as a parameter.

The MapPoint Web service has many views to choose from. I chose the ViewByHeightWidth view. I first defined the center point of the map view, and then specified the desired height and width of the map. For the center point, I used the latitude and longitude associated with the first record in the list of results. Because I used the first record returned, I did not allow for the possibility that MapPoint might find more than one match.

Creating a PivotTable

The FoodMart Analyzer Solution uses the CreateDefaultPivotTable procedure in ThisWorkbook to create a default PivotTable. Because the Solution stores the connection string to the cube in the settings file, it is easy to provide this capability. The FoodMartAnalyzer control has a link label control that calls this procedure that you might find useful in other situations, also. For example, you can provide a smart tag action that calls CreateDefaultPivotTable to create a cube that focuses on a particular store or product. To do this, you must modify CreateDefaultPivotTable slightly, but the modification is fairly easy.

Creating a PivotTable is a two step process. First, you create a pivot cache and an empty PivotTable. This is the purpose of CreatePivotTable, as shown in the following example:

Private Function CreatePivotTable(ByVal location As Excel.Range) _
    As Excel.PivotTable
    Application.Cursor = Excel.XlMousePointer.xlWait
    Try
        Dim pc As Excel.PivotCache
        pc = Me.PivotCaches.Add( _
            Excel.XlPivotTableSourceType.xlExternal)
        With pc
            .Connection = "OLEDB;" & My.Settings.ConnectionString
            .CommandType = Excel.XlCmdType.xlCmdCube
            .CommandText = "Sales"
            .MaintainConnection = True
            CreatePivotTable = .CreatePivotTable( _
               location, , False, _
               Excel.XlPivotTableVersionList.xlPivotTableVersion10)
        End With
    Catch ex As SystemException
        ' Add exception handling here that is appropriate
        ' for your circumstances
        CreatePivotTable = Nothing
    Finally
        Application.Cursor = Excel.XlMousePointer.xlDefault
    End Try
End Function

A PivotCache connects to the data source and manages the cache of data on the user's computer. You can create a PivotTable by initializing the PivotCache with the appropriate connection information, and then using the CreatePivotTable method. You must prefix the connection string with the text OLEDB.

Note   If you ever want to infer the connection from a PivotCache to use for other purposes, you might have to strip off the OLEDB text first.

You now have an empty PivotTable that is connected to the cube. Next, you use CreateDefaultPivotTable to add fields to the table. You use the AddDataField method of the PivotTable object to add one cube measure to the PivotTable. Then, you use CubeFields to add the Store dimension to the PivotTable as a row item, as shown in the following example:

Public Sub CreateDefaultPivotTable()
    ' Create a PivotTable at the active cell; the PivotTable
    ' is initialized with the Store dimension on the row axis and 
    ' Store Sales on the data axis
    Dim response As DialogResult = _
        MessageBox.Show("Create a cube at current location?", _
        "FoodMart Analyzer", MessageBoxButtons.YesNo)
    If response = DialogResult.No Then Exit Sub
    pivot = CreatePivotTable(Application.ActiveCell)
    foodMartAnalyzer.CreateCube.Enabled = False
    foodMartAnalyzer.ShowSettings.Enabled = True
    Try
        pivot.AddDataField( _
            pivot.CubeFields("[Measures].[Store Sales]"), _
            "Store Sales")
        With pivot.CubeFields("[Store]")
            .Orientation = Excel.XlPivotFieldOrientation.xlRowField
            .Position = 1
        End With
        pivot.TableRange2.Select()
    Catch ex As SystemException
        ' Add exception handling here that is appropriate
        ' for your circumstances
    End Try
End Sub

Before you implement the FoodMart Analyzer Solution, you expand the exception handling. For example, you might want to modify the code to prevent a new PivotTable from overlapping an existing PivotTable.

Examining PivotTable Properties

The CubeProperties control provides a confirmation that the PivotTable "points" to the correct place. This is useful when development, testing, and production environments are separate or when migrating servers.

For the CubeProperties control, the essential connection information is initialized in the constructor. Because the constructor requires a FoodmartData object as a parameter, you can easily call the ServerName and DatabaseName properties that FoodmartData exposes. The following example shows this:

Public Sub New(ByVal data As FoodmartData) 
    MyBase.New() 
    InitializeComponent() 
    Me.ServerName.Text = data.ServerName 
    Me.CubeName.Text = data.DatabaseName 
End Sub 

In addition to providing key connection information, CubeProperties allows you to easily view the MDX code generated in Excel that retrieves the PivotTable's data from Analysis Services. The FoodMart Analyzer Solution displays the value of the PivotTable's MDX property in a basic, modal Windows Form, as shown in the following example:

Private Sub ViewMDX_LinkClicked( _
    ByVal sender As System.Object, _
    ByVal e As System.Windows.Forms.LinkLabelLinkClickedEventArgs) _
    Handles ViewMDX.LinkClicked
    'Display MDX code in larger Windows Form
    Using viewer As TextViewer = New TextViewer
        Try
            viewer.EditItem.Text = _
                Globals.ThisWorkbook.FoodMartPivot.MDX
            viewer.ShowDialog()
        Catch ex As Exception
            MessageBox.Show("Unable to display PivotTable MDX.", _
            "FoodMart Analyzer", MessageBoxButtons.OK)
        End Try
    End Using
End Sub

GETPIVOTDATA and ShowPivotTableFieldList aretwo other features of the CubeProperties control. GETPIVOTDATA is an Excel function that retrieves information from a PivotTable. Although GETPIVOTDATA is helpful in certain situations, it can frustrate users who want to refer to a range within a PivotTable by the address of the range.

Unfortunately, GETPIVOTDATA is on by default, and you must customize the PivotTable toolbar to turn it off. To turn GETPIVOTDATA on or off programmatically, you toggle the GenerateGetPivotData property, which is part of the Excel Application object. The following example shows this:

Globals.ThisWorkbook.Application.GenerateGetPivotData

Similarly, you toggle ShowPivotTableFieldList on and off, as shown in the following example:

Globals.ThisWorkbook.ShowPivotTableFieldList

ShowPivotTableFieldList differs from many properties. For example, no matter what the current setting is when a user adds a new PivotTable, the setting reverts to True (on). Also, the PivotTable field list only appears when the active cell is part of a PivotTable range. Thus, when ShowPivotTableFieldList is on, it can appear as though it is really off, and vice versa. As a result, this setting can confuse users.

Extending the Solution

To extend the FoodMart Analyzer Solution, you could make it more generic. For example, if your company has multiple cubes, you might not know in advance which cube the user wants to analyze. In this case, you cannot create a targeted solution, but you could enhance the user experience without knowing anything about the cube at development time. The ADOMD.NET library can help you with this. It allows you to determine dynamically the cubes, dimensions, and hierarchies that are present on a specified server, for example.

You could also provide more integration with the PivotTablesf, for example, by enhancing the Member Locator control. Although you have seen how to search for a particular member, the FoodMart Analyzer Solution does not provide a way to use the results. You could create a PivotTable, or alter an existing one, with the selected member in the page field area, and then add this to the FoodMart Analyzer Solution.

You could also integrate information from other data sources. Usually you have a data mart in the background populating your Analysis Services database. Although an OLAP database effectively analyzes numerical data, it does not contain nonnumeric data, other than member names and properties. In a budgeting application, for example, you could display comments about individual income statement lines that explain why variances occur. In this example, the numbers could be generated by Analysis Services while the variance explanations are looked up in a database.

Code Security

The sample code in this article is intended for instructional purposes, and it should not be used in deployed Solutions without modifications. In particular, you must consider code security.

To illustrate the simplicity of the FoodMart Analyzer Solution, we created a list of potential threats by using the threat modeling process and tools described in the Threat Modeling section of the Microsoft Security Developer Center.

The following are some examples of the identified threats that you should take into consideration before expanding or deploying the FoodMart Analyzer Solution.

• XML Data Files are altered. The sample application uses external XML files as data sources. If these files are compromised, invalid data could be used within the application or the application could stop functioning properly. You can mitigate this threat by marking these files as read-only or by moving the data into a more secure, protected database that limits user access.

• Malicious data is used for search**.** User input is used to search the data source. You can mitigate this threat by validating all user input. Note, however that the code that handles the search does not use the user input in any query so there is not a risk of a SQL injection attack. Still, you should perform some reasonable checks on any user input data that your code operates on.

• Visual Studio 2005 Tools for Office Assemblies are replaced**.** If the Visual Studio 2005 Tools for Office assemblies are replaced with other assemblies, the application behavior could be altered to compromise the system. You can mitigate this threat by signing the code, using strong name conventions, or providing hash evidence. This sample does not currently implement these precautions, but they are strongly recommended before deploying an application to a production environment.

For more information about code security, visit the Microsoft Security Developer Center.

Conclusion

Excel is a natural complement to Analysis Services. By default, Excel has the ability to provide Analysis Services data through PivotTables. As powerful and convenient as this is, you can improve the user experience with additional programming.

When it comes to Excel development, Visual Studio 2005 Tools for Office offers many advantages over VBA. One of the most useful UI tools for Microsoft Office Solutions is the Document Actions task pane. By targeting the actions pane as your primary UI, you can provide a rich, context-aware UI to your users without mixing the Excel UI elements with yours.

The FoodMart Analyzer Solution shows how you can apply these ideas to create a business intelligence application.

Additional Resources

Visual Studio 2005 Tools for Office

• Microsoft Office Developer Center: Visual Studio Tools for Office Developer Portal

• Microsoft Visual Studio Developer Center

• Visual Studio 2005 Tools for Office documentation

• Microsoft Visual Studio 2005 Tools for the Microsoft Office System blog

• Visual Studio Tools for Office Forum

• Visual Studio 2005 Tools for Office Hands-on Labs for Excel 2003

• Visual Studio 2005 Tools for Office Hands-on Labs for Word 2003

Code Security

• Writing Secure Code, Second Edition

• Microsoft Security Developer Center

Office Developer Center

• Microsoft Office Developer Center

Analysis Services

• Microsoft SQL Developer Center: Analysis Services

About the Author

Steve Hansen is the founder of Dakota Tech Group, a Microsoft Gold Certified Partner in Business Intelligence that specializes in developing business intelligence and information worker solutions. Trained as a financial analyst and frustrated with the inefficiency of corporate finance processes, Steve switched his career focus to building solutions that facilitate the analytical process and found his calling in the process. Steve has been developing solutions using Microsoft tools for nearly a decade. Steve is the co-author of Mastering Excel 2000 Premium Edition and the author of Excel 2003 Programming with VBA. Steve has an MBA from the University of Minnesota. For more information or to contact Steve, please visit Dakota Technology Group, Inc.

Steve would like to extend a special thank you to Ryan Bedell for his assistance on this article.

Lori Turner is a developer consultant for Microsoft Services with many years of expertise in Office development. Lori has been with Microsoft for more than 12 years and still loves it! When not assisting Microsoft customers or writing cool Office solutions with .NET, she enjoys spending time with her husband Robbie and twin daughters Haley and Jordan. She is most happy when outdoors: either at the beach or in her garden in Charlotte, North Carolina.