Export (0) Print
Expand All
1 out of 1 rated this helpful - Rate this topic

Code Walkthrough: The Business Intelligence Smart Tag Solution

 

Matthew Nunn, Dell | Plural Professional Services
Frank C. Rice, Microsoft Corporation

October 2002

Applies to:
   Business Intelligence Smart Tag Wizard
   Microsoft® Excel
   Microsoft Word

Summary: This article examines the development process and the code behind the Business Intelligence Smart Tag Add-in, which is used to add flexible data analysis functionality to your Microsoft Office applications. (52 printed pages)

Download bi_st.msi.


Contents

Introduction
Accessing Data Warehoused Data
A Brief Introduction to Cubes, Dimensions, Levels and Members
BI Smart Tag Solution
BI Smart Tag
Recognition
Loading the List of Recognizable Terms
Recognizing a Term
Handling Actions
Deciding What Actions to Display
Executing an Action
Launching Data Analyzer
Launching the Custom Actions
BI Smart Tag Wizard
The Main Wizard Form
Comparing OLAP Databases to Offline Cubes
Returning a List of OLAP Catalogs
Returning a List of Cubes for a Catalog
Returning Dimensions and Levels into a TreeView Control
Confirm the Selections and Save to XML
Custom Action Definitions
BI Smart Tag COM Add-In
Creating a Menu From a COM Add-In
Launching an Executable
Displaying and Inserting Terms
Summary

Introduction

There are usually many complex systems within an enterprise that streamline day-to-day basis business operations and enable the collection of the data that drives those operations. However, there is often one aspect of the creation of an enterprise system that is not addressed; once you have collected the large amounts of data that typically result from automating the core operations of a business, how do you use all this data to give the business an advantage?

Developers often overlook the importance of the data that is collected by their systems. They tend not to think about what that information could be used for in the same terms as management. A developer sees the data as a necessary part of ensuring that the operational effectiveness of the systems continues. Managers, on the other hand, will often want to use the data that is being gathered by these systems as the basis for business decisions that can ensure that the company remains competitive in the market place and continues to endear itself to customers by providing customized services.

Accessing Data Warehoused Data

A common way that companies analyze data is by creating a data warehouse of all the data in the various systems throughout the organization, and then running queries against the data warehouse to access the important data. This process and the tasks involved are often referred to as business intelligence (BI).

Microsoft® Office provides many powerful ways to analyze data stored in a database and Microsoft Excel is particularly good at analyzing the data organized in cubes from the data warehouse. In Excel, PivotTable® reports and PivotChart® reports are the staple tool for anyone looking to analyze warehoused data. While these tools provide powerful ways of analyzing information, it does require the users to have a certain level of knowledge of the data stored in the system. Often the people in a position to analyze and make decisions on the information are not always the same people who have the knowledge of the data required to extract that information! As Office developers, this is where we have the opportunity to step up and earn some "brownie" points from people higher up the food chain. Office provides the tools to automate a large portion of the processes required to access the important business information.

A Brief Introduction to Cubes, Dimensions, Levels and Members

Analyzing data using an Online Analytical Processing (OLAP) database is a discipline unto itself. It isn’t feasible to cover all of the techniques and terminology surrounding BI in this article. However, it is useful to understand some of the key terms that are used when talking about data in an OLAP database.

  • OLAP—An OLAP database is a special type of database that is structured to allow easy querying of data. It is normally maintained separately from the active operational database to avoid trying to analyze data that is constantly changing. It contains a snapshot of the data in the system at the time that it was built. An operational database is used to collect the data from the day-to-day business operations.
  • Fact Table—The fact table contains the fundamental data that is to be analyzed. It normally contains just data with no descriptive information because in a relational database this is the most efficient way of storing the data.
  • Dimension—A dimension is a table or group of tables that provide descriptive information for the data in the fact table. A dimension contains the information that a business analyst can look at and use to structure the data in order to get at the information in the fact table.
  • Levels—Levels represent the hierarchy of data within a dimension. For instance, the Time dimension has the levels Year, Quarter, Month and Day. Each lower level is a child of the preceding level. A year has four quarters, each quarter has three months, and each month has a certain number of days.
  • Member—A member is a piece of factual information contained within a dimension. The members of the Time dimension might be 2000, 2001, and 2002 but can also include Q1, Q2, Q3 and Q4. This is why it is useful to be able to view any specific dimension as a set of levels where the Year level has the members 2000, 2001 and 2002 and the Quarter dimension has the members Q1, Q2, Q3 and Q4.
  • Cube—A cube aggregates the facts in each level of each dimension in a given OLAP schema. The business intelligence industry uses the word cube because it best describes the resulting data structure.

There are a number of possibilities available to access and analyze data. In this article, I will present an option that I implemented to find a way of letting users easily access data from within a cube and have it displayed in a number of custom ways. It should not be important for the user to know how the data was organized but they should be able to access it by just entering a specific piece of member information that appears in the data. The solution takes the power of smart tags and integrates this with the ability to access data from the data warehouse, all from within an Office program. Collectively, this solution is referred to as the BI Smart Tag.

BI Smart Tag Solution

The BI Smart Tag solution allows users to select specific data from a data warehouse and display the information in various formats within Microsoft Excel or Microsoft Word. Users have the ability to define which cube to connect to and which dimensions and levels contain the data with which they wish to work.

There are three separate pieces to the BI Smart Tag solution that combine to provide all the functionality required to access and work with the data in the data warehouse. From the name, you can guess that there is, of course, a smart tag. This smart tag is responsible for recognizing terms and performing actions related to the data contained within the data warehouse. For the smart tag to work, it needs to understand which terms to recognize and where to get the data. To allow it to do this, there is a wizard that lets users define what areas of the data warehouse contain the recognizer terms and where the data warehouse is stored. To allow the smart tag and wizard to feel as cohesive as possible, there is a third piece of the solution, which is a Component Object Model (COM) add-in. The COM add-in links the wizard into the different Office applications and is also able to display a list of the recognizer terms that can be added into either an Excel worksheet or a Word document.

BI Smart Tag

The core of the solution is, of course, the smart tag. This is responsible for recognizing text in either Excel or Word and then allowing the user to perform tasks that relate to the recognized term. When developing a solution that has multiple applications that interact, it is important to understand how you want the core smart tag to work before building the other supporting systems.

There are two things that the smart tag has to do:

  • Recognize a set of terms
  • Provide actions to the user based on the term that was recognized

The actions may differ depending upon which of the Office applications is currently executing the smart tag. However, the recognizer will always do the same thing; it will recognize a piece of text. One of the things that the BI Smart Tag does is allow users to select any cube in any data warehouse to provide the recognizer terms. This means that the connection information for the cube needs to be stored somewhere where it can be easily updated. XML is used to provide a simple common file format to store this location information.

Let’s start by looking at the smart tag recognizer and action handlers.

Recognition

For the BI Smart Tag, there are a number of issues to address when creating the list of terms that are to be recognized. Because the list of terms should always be representative of the actual data contained within the data warehouse, it is important that rather than storing the terms locally, the list of terms is queried from the server each time the recognizer is initialized. This initialization happens whenever Excel or Word is first started. The list of terms is queried from the server based on the information stored in a local XML file that defines the database, cube, dimensions and levels that should be queried. This XML file is created by the BI Smart Tag Wizard that is discussed later in this article. The XML information is preserved by saving an in-memory ActiveX® Data Object (ADO) Recordset object to a local XML file. The file has a format similar to the following sample code.

Note   This code has had the schema information that is automatically generated by ADO removed to improve readability.
...
<xml xmlns:s="uuid:BDC6E3F0-6DA3-11d1-A2A3-00AA00C14882" 
  xmlns:dt="uuid:C2F41010-65B3-11d1-A29F-00AA00C14882" 
  xmlns:rs="urn:schemas-microsoft-com:rowset" xmlns:z="#RowsetSchema">
<s:Schema id="RowsetSchema">
- <s:ElementType name="row" content="eltOnly" rs:updatable="true">
- <s:AttributeType name="Server" rs:number="1" rs:write="true">
<s:datatype dt:type="string" rs:dbtype="str" dt:maxLength="255"
    rs:precision="0" rs:maybenull="false" /> 
</s:AttributeType>
- <s:AttributeType name="Provider" rs:number="2" rs:write="true">
<s:datatype dt:type="string" rs:dbtype="str" dt:maxLength="255"
    rs:precision="0" rs:maybenull="false" /> 
</s:AttributeType>
- <s:AttributeType name="Catalog" rs:number="3" rs:write="true">
  <s:datatype dt:type="string" rs:dbtype="str" dt:maxLength="255"
    rs:precision="0" rs:maybenull="false" /> 
  </s:AttributeType>
- <s:AttributeType name="Cube" rs:number="4" rs:write="true">
  <s:datatype dt:type="string" rs:dbtype="str" dt:maxLength="255"
    rs:precision="0" rs:maybenull="false" /> 
  </s:AttributeType>
- <s:AttributeType name="Dimension" rs:number="5" rs:write="true">
  <s:datatype dt:type="string" rs:dbtype="str" dt:maxLength="255"
    rs:precision="0" rs:maybenull="false" /> 
  </s:AttributeType>
- <s:AttributeType name="Level" rs:number="6" rs:write="true">
  <s:datatype dt:type="string" rs:dbtype="str" dt:maxLength="255"
    rs:precision="0" rs:maybenull="false" /> 
  </s:AttributeType>
  <s:extends type="rs:rowbase" /> 
  </s:ElementType>
  </s:Schema>
<rs:data>
  <rs:insert>
    <z:row Server="NUNNM_MOBILE" Provider="MSOLAP" 
        Catalog="FoodMart 2000"                     
        Cube="Sales" Dimension="Product" Level="Product Family" /> 
    <z:row Server="NUNNM_MOBILE" Provider="MSOLAP" 
        Catalog="FoodMart 2000"
        Cube="Sales" Dimension="Store" 
        Level="Store Country,Store State" />
  </rs:insert>
</rs:data>
</xml>
...

In this code segment, the XML Recordset object has two rows of data each with six fields of data. Each row represents a specific dimension in the cube that will be queried for recognizer terms. For each record, the Server, Provider, Catalog and Cube values are the same because these define the generic connection information.

  • Server—Defines the server name of the server to connect to that allows the smart tag to retrieve the cube information.
  • Provider—Tells the system what type of data provider to use to access the server. For the BI Smart Tag solution, this should always be MSOLAP.
  • Catalog—Tells the server which catalog (database) contains the cube and its location.
  • Cube—This is the name of the cube where the data is located.

The last two fields of each data row are different for each row

  • Dimension—Defines the dimension that contains the levels defined in the level fields.
  • Level—Defines the level or levels that are queried to return the list of terms. The terms that are returned are the members of each level.

For each row, the dimension and level fields will be different. The level field can also contain a comma-separated list of the levels contained within its dimension if more than one level is selected to return terms.

Loading the List of Recognizable Terms

When the smart tag initializes, the first function that it performs is to load all of the terms contained in a local, in-memory, Recordset object. This is done by reading the BITerms.xml file and making one or more calls to the server to retrieve the terms as demonstrated in the following code sample:

Public Function GetTermsWithDetails() As ADODB.Recordset
   On Error GoTo ErrorHandler

   Dim rsLocalTerms As ADODB.Recordset
   Dim rsTermsFromServer As ADODB.Recordset
   Dim oAssociatedObject As Object
   Dim connOLAP As New Connection
   Dim rsServerTerms As New Cellset
   Dim iSiblingCount As Long
   Dim dmCur As Dimension, hrCur As Hierarchy, _
      lvCur As Level, mbCur As Member
   Dim arrLevels() As String
   strXMlRec = App.Path & "\BITerms.xml"

   ' Create a term list recordset.
   Set rsTermsFromServer = Nothing
   Set rsTermsFromServer = New ADODB.Recordset
   With rsTermsFromServer
      .Fields.Append "Term", adVarChar, 255
      .Fields.Append "Dimension", adVarChar, 255
      .Fields.Append "Level", adVarChar, 255
      .Fields.Append "UniqueName", adVarChar, 1000
      .Fields.Append "levelDepth", adInteger
      .Open
   End With

   ' Get the connection details from the local XML file.
   Set rsLocalTerms = Nothing
   Set rsLocalTerms = New ADODB.Recordset
   rsLocalTerms.Open strXMlRec
  
   ' Loop through all the dimension records contained
   ' in the local XML file.
   rsLocalTerms.MoveFirst
   Do While Not rsLocalTerms.EOF
      ' Set the connection details from the xml file
      strServer = rsLocalTerms("Server").Value
      strProvider = rsLocalTerms("Provider").Value
      strCatalog = rsLocalTerms("Catalog").Value
      strCube = rsLocalTerms("Cube").Value
      strDimension = rsLocalTerms("Dimension").Value
      strLevel = rsLocalTerms("Level").Value
    
      ' Create an Array of levels if the levels field
      ' contains a comma separated list.
        arrLevels = Split(strLevel, ",")
    
      ' Connect to the cube and extract the terms.
      If fOpenConnection(strProvider, strServer, strCatalog) Then
         Set m_ctActiveCatalog = New Catalog
         Set m_ctActiveCatalog.ActiveConnection = cnActive
      
         Dim i As Integer
      
         ' Loop through each level and extract all the
         ' terms for that level.
         For i = 0 To UBound(arrLevels)
            Set oAssociatedObject = oGetAssociatedObject _
               (strCube, strDimension,    arrLevels(i))
  
            iSiblingCount = 0

            ' Loop through the members of the current level
            ' and add each to the in-memory recordset of
            ' terms along with information about the
            ' dimension and level.
            For Each mbCur In oAssociatedObject.Members
               iSiblingCount = iSiblingCount + 1

               rsTermsFromServer.AddNew
               rsTermsFromServer!Term = mbCur.Name
               rsTermsFromServer!Dimension = _
                  rsLocalTerms("Dimension").Value
               rsTermsFromServer!Level = arrLevels(i)
               rsTermsFromServer!UniqueName = mbCur.UniqueName
               rsTermsFromServer!LevelDepth = mbCur.LevelDepth
               rsTermsFromServer.Update

            Next mbCur
         Next i
      End If
      rsLocalTerms.MoveNext
   Loop
    
Finally:
   Set GetTermsWithDetails = rsTermsFromServer
   Exit Function

ErrorHandler:
   MsgBox Err & vbCrLf & Err.Description
   GoTo Finally
End Function

The function GetTermsWithDetails is used to retrieve the list of all required terms from the data warehouse. It is called from the Class_Initialize event handler of the recognizer. Initially, it creates an in-memory Recordset object that will hold the list of terms as they are retrieved from the data warehouse. It then loads the local XML file that contains the connection information into another Recordset object using the Open method, passing in the local XML filename as a parameter. Once the local information is in the Recordset object, the code loops through each row (this is looping through each of the dimensions that has levels containing terms to be recognized), opens a connection to the data warehouse, and then retrieves all the terms for each of the levels in the dimension.

The code uses two support functions. The first is responsible for making the connection to the data warehouse as shown in the code sample below:

Private Function fOpenConnection(strProvider As String, _
      strServer As String,  strCatalog As String) As Boolean
   On Error GoTo fOpenConnectionErr

   Dim strOLAPCon As String

   strOLAPConn = "Provider=" & strProvider & _
      ";Data Source=" & strServer & _
      ";initial catalog=" & strCatalog & ";"

   ' If connection is nothing, closed, or changed,
   ' then open new connection.
   If cnActive Is Nothing Then
      Set cnActive = New Connection
      cnActive.Open strOLAPConn
   ElseIf Not cnActive.State = adStateOpen Then
      Set cnActive = New Connection
      cnActive.Open strOLAPConn
   End If

   fOpenConnection = True

Finally:
   Exit Function

fOpenConnectionErr:
   fOpenConnection = False
   GoTo Finally
End Function

The fOpenConnection function takes as its parameters the name of the server that will be opened, the catalog (database) to be opened from that server, and the type of provider to use to open the connection. If the connection (represented by the cnActive variable) is not already open, it is opened based on the server and catalog information passed as parameters; otherwise, it simply uses the currently open connection.

The oGetAssociatedObject function returns an object that allows access to all the members of a specified level within the cube:

Private Function oGetAssociatedObject(strCube As String, _
      strDimension As String, strLevel As String) As Object
   Dim cb As CubeDef, dm As Dimension, hr As Hierarchy
   Dim lv As Level

   ' Get the cube using name on top of stack.
   Set cb = m_ctActiveCatalog.CubeDefs(strCube)

   ' Get the dimension using next name on stack, if any.
   Set dm = cb.Dimensions(strDimension)

   ' If current dimension contains only one hierarchy, get that.
   Set hr = dm.Hierarchies(0)

   ' Get the level using next name on stack, if any.
   Set lv = hr.Levels(strLevel)
   Set oGetAssociatedObject = lv
End Function

The oGetAssociatedObject function takes the name of the cube, dimension, and level to be interrogated and returns an object that contains the level details. Each level contains one or members, and these members become the terms that will be used as recognizers.

Recognizing a Term

Now that we have built a list of terms to be recognized, it is time for the recognizer to swing into action. The recognizer is tasked with looking at text passed to it from the smart tag architecture and trying to find any of the terms that it should recognize in that text. If the recognizer finds a term, it then builds a property bag object (a container for value pairs consisting of the term and supporting information about the term) that it commits back to the smart tag architecture. This can be read by the action handler to allow it to display and process any specified actions.

Public Sub ISmartTagRecognizer_Recognize(ByVal Text As String, _
      ByVal DataType As SmartTagLib.IF_TYPE, _
      ByVal LocaleID As Long, _
      ByVal RecognizerSite As SmartTagLib.ISmartTagRecognizerSite)
   Dim i As Integer
   Dim index As Integer, termlen As Integer
   Dim propbag As SmartTagLib.ISmartTagProperties

   ' Make the recognised term lowercase to ensure
   ' a proper match.
   Text = LCase(Text)

   ' Loop through all the terms in the terms recordset
   ' until a match is found.
   rsTerms.MoveFirst
   Do While Not rsTerms.EOF
      ' Find the index number of the beginning of the
      ' term in the recognised text. If the term does
      ' not exist this will be zero.
      index = InStr(1, LCase(Text), LCase(rsTerms("Term").Value))

      ' Get the length of the term that is being recognized.
      termlen = Len(rsTerms("term").Value)

      ' If the term is found then build a property bag to
      ' pass to the action handler and then look for the
      ' term in the remaining text.
      ' beign recognized
      While (index > 0)
         ' Ask the recognizer site for a property bag.
         Set propbag = RecognizerSite.GetNewPropertyBag
         With propbag
            .Write "Server", strServer
            .Write "Provider", strProvider
            .Write "Catalog", strCatalog
            .Write "Cube", strCube
            .Write "Dimension", rsTerms("Dimension").Value
            .Write "Level", rsTerms("Level").Value
            .Write "TermUniqueName", rsTerms("UniqueName").Value
            .Write "LevelDepth", rsTerms("LevelDepth").Value
         End With

         ' Commit the smart tag to the application.
         RecognizerSite.CommitSmartTag _
            "schemas-biolap-com/biolap#analysis", index, _
            termlen, propbag

         ' Look for another smart tag in the string.
         index = InStr(index + termlen, Text, rsTerms("term").Value)

      Wend

      ' Move to the next term.
      rsTerms.MoveNext
   Loop
End Sub

Using a property bag is the only technique supported by the smart tag architecture for passing information from the recognizer to the action handler. It can contain as many value pairs as you require to tell the action handler all of the requisite information about the recognized text. As we stated earlier, a value pair consists of an item that has a name and a piece of associated data. In the BI Smart Tag solution, we need to inform the action handler of the connection information for the recognized term so that it is able to connect to the cube. This connection information allows the BI Smart Tag to connect to the cube and build the PivotTable reports or PivotChart reports that constitute the main actions surfaced by the action handler.

Handling Actions

The action handler for the BI Smart Tag has a number of static actions that it can perform based upon the application that is currently executing the smart tag (Excel or Word). The architecture also supports the ability to load custom actions from an XML configuration file. These actions make calls to specific Uniform Resource Locators (URLs) and can be passed recognition and connection information. This custom action ability is very similar to that provided by the Microsoft Office Smart Tag List Tool (MOSTL) that accompanies the Smart Tag SDK. Over and above these two pieces of functionality, the action handler can also investigate whether certain applications, such as Microsoft Data Analyzer, are installed; if they are, it can make an action relating to that application being available from the action menu. You can see the differences between the list of Excel actions and the list of Word actions in Figure 1 and Figure 2, respectively.

Figure 1. List of actions from the BI Smart Tag available from Excel

Figure 2. List of actions from the BI Smart Tag available from Word

In both of the Excel and Word action lists, the last two actions are calls to URLs. These customizable actions are loaded based on information stored in the local configuration XML file BICustomActions.xml.

Deciding What Actions to Display

The process of deciding what actions to display is something that every smart tag has to do. The BI Smart Tag does this by using a number of the standard smart tag event handlers to define the actions that are displayed.

To allow the BI Smart Tag to recognize and display the custom URL call actions, the first thing the action handler does when it initializes is to load the list of custom actions from the local XML configuration file. This is done in the Class_Initialize event handler which calls the GetCustomActions function (the function is detailed in the Deciding What Actions to Display section), to load the list of custom actions from the local XML file and populate a Recordset object that will be used later to build the action handler menu which is displayed to the user:

Private Sub Class_Initialize()
   Set rsCustomActions = GetCustomActions(iCountCustomActions)
End Sub

Public Function GetCustomActions(ByRef ActionCount As Integer) _
      As ADODB.Recordset
   Dim rsLocalTerms As ADODB.Recordset

   strXMlRec = App.Path & "\BICustomActions.xml"
   Set rsLocalTerms = Nothing
   Set rsLocalTerms = New ADODB.Recordset

   rsLocalTerms.Open strXMlRec

   ActionCount = rsLocalTerms.RecordCount
   Set GetCustomActions = rsLocalTerms
End Function

Once the list of custom actions is loaded into the Recordset object, it is possible to decide how many actions will be displayed by the action handler. Every smart tag has a VerbCount property that returns the number of actions that will be displayed to the smart tag architecture. Normally, you decide the number of actions that will be displayed at design time and it is a fixed number. The ability to have numerous custom actions requires that you set the VerbCount property at run time. This means adding the number of custom actions to the number of fixed actions and then writing this value to the VerbCount variable when the action class initializes. There is one final twist in the BI Smart Tag action handler. One of the operations that the smart tag provides is the ability to analyze the data in Data Analyzer. Because this action should only be displayed if Data Analyzer is installed on the local machine, the smart tag must also call a function ISDataAnalyzerInstalled (located in the section Launching Data Analyzer) to determine whether this is the case. If Data Analyzer is installed then the VerbCount property must be incremented by one:

Private Property Get ISmartTagAction_VerbCount _
      (ByVal SmartTagName As String) As Long
   ' Specify the number of verbs supported for a given smart tag type.
   If (SmartTagName = "schemas-biolap-com/biolap#analysis") Then
      If ISDataAnalyzerInstalled Then
         iActionCount = STANDARD_ACTION_COUNT + 1
      Else
         iActionCount = STANDARD_ACTION_COUNT
      End If
      ISmartTagAction_VerbCount = iCountCustomActions + iActionCount
   End If
End Property

After the number of actions to be displayed is calculated, the next operation is to set the appropriate action titles that the menu displays in the host application will show. This is done by using a Select Case statement in the ISmartTagAction_VerbCaptionFromID property. This property is called once per action to be displayed in the action menu. A VerbID variable is passed to this property and the Select…Case statement uses this to make its decision as to which title to return for that menu item. The VerbID variable is a number that represents the current action from the list of all actions. The Select Case statement looks at the VerbID variable passed in from the smart tag architecture and returns the appropriate action text. Notice that for Case 1 through Case 6, there is an If . . . Then . . . Else statement that determines whether the current host is Excel or Word, and then returns the appropriate text:

Private Property Get ISmartTagAction_VerbCaptionFromID _
      (ByVal VerbID As Long, ByVal ApplicationName As String, _
      ByVal LocaleID As Long) As String
   On Error GoTo ErrorHandler

   Dim strActionText As String

   Select Case VerbID
      Case 1
         If InStr(ApplicationName, "Word.Application") > 0 Then
            ISmartTagAction_VerbCaptionFromID = _
               "View Details in Excel Pivot Table"
         Else
             ISmartTagAction_VerbCaptionFromID = _
                "Insert Pivot Table for this member"
         End If

      Case 2
         If InStr(ApplicationName, "Word.Application") > 0 Then
            ISmartTagAction_VerbCaptionFromID = _
               "View Details in Excel Pivot Chart"
         Else
            ISmartTagAction_VerbCaptionFromID = _
               "Insert Pivot Chart for this member"
         End If

      Case 3
         If InStr(ApplicationName, "Word.Application") > 0 Then
            ISmartTagAction_VerbCaptionFromID = "Insert Pivot Table"
         Else
            ISmartTagAction_VerbCaptionFromID = _
               "Insert Pivot Table Into New Word Document"
         End If

      Case 4
         If InStr(ApplicationName, "Word.Application") > 0 Then
            ISmartTagAction_VerbCaptionFromID = _
               "Insert Static Pivot Chart"
         Else
            ISmartTagAction_VerbCaptionFromID = _
               "Insert Static Pivot Chart Into New Word Document"
         End If

      Case 5
         If InStr(ApplicationName, "Word.Application") > 0 Then
            ISmartTagAction_VerbCaptionFromID = _
               "Insert Active Pivot Chart"
         Else
            ISmartTagAction_VerbCaptionFromID = _
               "Insert Active Pivot Chart Into New Word Document"
         End If

      Case 6
         If ISDataAnalyzerInstalled Then
            ISmartTagAction_VerbCaptionFromID = _
               "Analyze Data with Data Analyzer"
         Else
            rsCustomActions.MoveFirst
            rsCustomActions.Move ((VerbID - iActionCount) - 1)
            strActionText = rsCustomActions("CustomText").Value
            ISmartTagAction_VerbCaptionFromID = strActionText
         End If

      Case Else
         rsCustomActions.MoveFirst
         rsCustomActions.Move ((VerbID - iActionCount) - 1)
         strActionText = rsCustomActions("CustomText").Value
         ISmartTagAction_VerbCaptionFromID = strActionText
   End Select

Finally:
   rsCustomActions.MoveFirst
   Exit Property

ErrorHandler:
   MsgBox Err & vbCrLf & Err.Description
End Property

Case 6 is a special case because it can be either the Data Analyzer action, if Data Analyzer is installed, or the first of the custom actions. To ensure that the correct title is applied, the ISDataAnaylzerInstalled function (located in the section Launching Data Analyzer) is called for a second time and the text that is displayed is based on the resulting value. Case Else of the Select Case statement handles the fact that at design time there is no way to know how many custom actions there will be. Case Else catches any VerbID value that has not already been handled and returns the correct action description for that custom action by finding the appropriate record in the Recordset object that was built when the class was initialized.

Executing an Action

The execution of an action in the BI Smart Tag action handler is managed by the ISmartTagAction_InvokeVerb method. This method has another large Select Case statement that contains a case for each possible VerbID value. To make this function a little easier to understand it is easier to look at each of the cases in isolation.

Inserting a PivotTable Report to Show a Particular Dimension Member

The first case displays a PivotTable report in Excel based on the currently recognized term. It uses the InsertPivotTable function to insert a new PivotTable report into a new worksheet in Excel. If the current host application is Word, it opens a new instance of Excel and then executes the function against this new instance of Excel:

...
   Case 1 ' Display the term in a PivotTable report in Excel.
      ' If the current host application is Word then create 
      ' an instance of Excel.
      If InStr(ApplicationName, "Word.Application") > 0 Then
         Set oex = New Excel.Application
         oex.Workbooks.Add
         oex.Visible = True
      Else
         Set oex = Target.Application
      End If

      ' Create a PivotTable report in Excel.
      strPTableName = InsertPivotTable(VerbID, ApplicationName, _
         oex, Properties, Text, XML)
...

The InsertPivotTable function is responsible for connecting to the cube and displaying the member from within its appropriate dimension and level:

Public Function InsertPivotTable(ByRef VerbID As Long, _
      ByRef ApplicationName As String, _
      ByRef oex As Excel.Application, _
      ByRef Properties As SmartTagLib.ISmartTagProperties, _
      ByRef Text As String, ByRef XML As String) As String
   On Error GoTo ErrorHandler

   Dim pTable As PivotTable
   Dim strOLAPConn As String
   Dim i As Integer
   Dim iSheetIndex As Integer

   Dim iStart As Integer
   Dim iLength As Integer
   Dim strName As String
   Dim strPTableName As String
   Dim shtNew As Worksheet

   ' Build the data warehouse connection string.
   strOLAPConn = "OLEDB;Provider=" & _
      Properties.Read("Provider") & ".1;Data Source=" & _
      Properties.Read("Server") & ";Initial Catalog=" & _
      Properties.Read("Catalog") & _
      ";Client Cache Size=25;Auto Synch Period=10000"

   ' Add a PivotTable report to the workbook based
   ' on the selected cube.
   With oex.ActiveWorkbook.PivotCaches.Add(SourceType:=xlExternal)
      .Connection = strOLAPConn
      .CommandType = xlCmdCube

      .CommandText = Array(Properties.Read("Cube"))
      .MaintainConnection = True

      strPTableName = "PivotTable" & _
         CStr(oex.ActiveSheet.PivotTables.Count + 1)

      Set shtNew = oex.ActiveWorkbook.Sheets.Add
      shtNew.Activate
      iSheetIndex = shtNew.index

      shtNew.Name = Left("PT(" & iSheetIndex & ") For " & Text, 30)
      Set pTable = _
         .CreatePivotTable(TableDestination:=shtNew.Range("A1"), _
         TableName:=strPTableName, _
         DefaultVersion:=xlPivotTableVersion10)

   End With

   ' Walk the Pivot table looking for matches to the
   ' recognised term. Stop when one is found.
   With oex.ActiveSheet
      oex.ScreenUpdating = False
      If Properties.Read("level") = "MeasuresLevel" Then
         .PivotTables(strPTableName).AddDataField .PivotTables( _
         strPTableName).CubeFields("[Measures].[" & Text & "]"), Text
      Else
         With .PivotTables(strPTableName).CubeFields("[" & _
               Properties.Read("Dimension") & "]")
            .Orientation = xlRowField
            .Position = 1
         End With

         NavigatePivotTable oex, strPTableName, Text, Properties

         ' Format the PivotTable report.
         For i = 1 To .PivotTables(strPTableName).CubeFields.Count
            If InStr(1, _
                  .PivotTables(strPTableName).CubeFields(i).Name, _
                  "[Measures]") Then
               IStart = InStr(1, _
                  .PivotTables(strPTableName).CubeFields(i).Name, ".")
               iLength = _
                  Len(.PivotTables(strPTableName).CubeFields(i).Name)
               strName = _
                   Mid(.PivotTables(strPTableName). _
                   CubeFields(i).Name, iStart + 1, iLength)
               strName = Mid(strName, 2, Len(strName) - 2)
               .PivotTables(strPTableName).AddDataField .PivotTables( _
                  strPTableName).CubeFields(i), strName
            End If

         Next i

         With .PivotTables(strPTableName).DataPivotField
            .Orientation = xlColumnField
            .Position = 1
         End With
      End If  
   End With

Finally:
   InsertPivotTable = strPTableName
   oex.ScreenUpdating = True
   Exit Function

ErrorHandler:
   Select Case Err
      Case 1004
         iSheetIndex = iSheetIndex + 1
         Resume Next
      Case Else
         MsgBox (Err & vbCrLf & Err.Description)
         GoTo Finally
   End Select

End Function

The InsertPivotTable function creates a new PivotTable report in a new Excel worksheet and attaches it to the cube. It then calls the NavigatePivotTable function to actually drill down into the PivotTable report to display the correct member (recognized term). This uses the unique name for the member that was extracted from the database when the term list was loaded. This step is required because, unlike the PivotTable component in the Microsoft Office Web Components, the Excel PivotTable report cannot be bound to a custom, multidimensional expressions (MDX) query and still remain active against the entire cube.

Public Function NavigatePivotTable(ByRef oex As Excel.Application, _
      ByRef strPTableName As String, ByRef Text As String, _
      ByRef Properties As SmartTagLib.ISmartTagProperties)
   On Error GoTo ErrorHandler

   Dim a As Variant, arrSplitUnique As Variant
   Dim i As Integer,j As Integer, k As Integer, _
      l As Integer, iArraySize As Integer
   Dim strList As String

   ' Check the level depth of the member being looked for
   ' and set i to be the level above it. This ensures that
   ' we do not navigate down too many levels.
   If Properties.Read("LevelDepth") > 0 Then
      i = Properties.Read("LevelDepth") - 1

   Else
      i = 0
   End If

   ' Take the unique name of the recognized term and 
   ' turn it into an array.
   arrSplitUnique = Split(Mid(Properties.Read("TermUniqueName"), 2, _
      len(Properties.Read("TermUniqueName")) - 2), "].[")

   ReDim a(i) As Variant

   iArraySize = UBound(arrSplitUnique)
   a(0) = Array("")
   l = iArraySize

   ' Loop around building the unique names of the dimensions
   ' and levels above the recognised term. This is done to
   ' assign the members a unique name and creating unique names
   ' for all the other members of the PivotTable report.
   For j = i To 1 Step -1
      strList = ""
      For k = 0 To l - 1
         strList = strList & "[" & arrSplitUnique(k) & "]"
         If k <> l - 1 Then
            strList = strList & "."
         End If
      Next k
    
      a(j) = Array(strList)
      l = l - 1
   Next j
  
   ' Use the Drilled property to drill into the PivotTable
   ' report by assigning the Array created above to it.
   Dim pi As PivotItem
   Dim pf As PivotField
   Dim cbf As CubeField
  
   With oex.ActiveSheet
      .PivotTables(strPTableName).CubeFields("[" & _
         Properties.Read("Dimension") &  _
         "]").TreeviewControl.Drilled = a
    
      On Error Resume Next
         .PivotTables(strPTableName).PivotFields _
            (CInt(Properties.Read("LevelDepth")) _
            ).PivotItems(Properties.Read("TermUniqueName")). _
            DrilledDown = True

      On Error GoTo ErrorHandler

   End With

Finally:
   Exit Function

ErrorHandler:
   MsgBox Err & vbCrLf & Err.Description
   GoTo Finally
End Function

To ensure that the PivotTable report displays the correct information about the member that was recognized, you have to use the Drilled property of the TreeViewControl object and the DrilledDown property of the PivotItems object. The PivotTable report shown in Figure 3 is the result of inserting a PivotTable report for the recognized term Candy in the FoodMart 2000 catalog that is available with Microsoft SQL Server™ 2000 Analysis Services.

Click here for larger image

Figure 3. PivotTable report created from the recognized term Candy (click picture to see larger image)

To make the PivotTable report in Excel display the drilled levels shown in Figure 3, you can use the code in the following segment for the Drilled and DrilledDown properties. The Drilled property of the Treeviewcontrol object returns or sets an Array. Each element of the array corresponds to a level of the cube field that has been expanded where the maximum number of elements is the number of levels in the cube field. Each element of the array is an Array of type String, containing unique member names that are visible at the corresponding level of the control. To show a specific member in a PivotTable report, you drilldown in the TreeViewControl object to the dimension above the member you are interested in. To get to the PivotTable report displayed in Figure 3, use the code in the following segment to drill down to the level above Candy.

...
ActiveSheet..PivotTables("PivotTable1").CubeFields([Product]). _
      TreeviewControl.Drilled = _
   array(array(""), _
   array("[Product].[All Products].[Food]"),  _
   array("[Product].[All Products].[Food].[Snacks]"))
...

Once you have set the Drilled property of the TreeviewControl object to the level above the one where your member appears, you drill into the PivotItems object for the particular item you are interested in by using the code in the following segment:

...
ActiveSheet.PivotTables("PivotTable1").PivotFields(3) _
   .PivotItems("[Product].[All Products].[Food].[Snacks].[Candy]"). _
   DrilledDown = True
...

The function NavigatePivotTable should work for any PivotTable report based on any cube because it generically creates the correct array to pass to the drilldown method that will cause the PivotTable report to drill to a certain level.

The final task that the InsertPivotTable function performs is to format the PivotTable report into a readable format for the user.

Inserting a PivotChart Report to Show Details of a Cube Member

The second case that is handled by the action handlers is to insert a PivotChart report into Excel. To insert a PivotChart report into Excel, you have to create a PivotTable report that is used as the basis for the chart. This is done in exactly the same way as was shown for Case 1.

...
   Case 2 ' Display the current term in a PivotChart report in Excel.
      ' If the current host application is Word then 
      ' create an instance of Excel.
      If InStr(ApplicationName, "Word.Application") > 0 Then
         Set oex = New Excel.Application
         oex.Workbooks.Add
         oex.Visible = True
      Else
         Set oex = Target.Application
      End If
      
      ' Create a PivotTable report in Excel.
      strPTableName = InsertPivotTable(VerbID, ApplicationName, _
         oex, Properties, Text, XML)

      ' Create a PivotChart report in Excel based on
      'the PivotTable report
      oex.Charts.Add
      oex.ActiveChart.Location Where:=xlLocationAsNewSheet
      oex.ActiveChart.Name = Left("PC(" & oex.ActiveChart.index & _
         ") For " & Text, 30)
...

The only thing that is different in Case 2 from Case 1 is that after creating and inserting the PivotTable report, a new chart is added to the workbook. When adding a new chart, it automatically uses the active PivotTable report as its source.

Inserting PivotTable Reports and PivotChart Reports into Word

Case 3, Case 4 and Case 5 perform similar task to Case 1 and Case 2 except that they insert the PivotTable report and PivotChart report into Word instead of Excel. To do this, they must first create the PivotTable report or PivotChart report in Excel, and then copy and paste it into Word. So that the same code can be used in both Excel and Word action handlers and to avoid making the current Excel workbook untidy if the action is called from Excel, an invisible instance of Excel is created that is used for the construction of the PivotTable report and PivotChart report.

In Case 3, a PivotTable report is created in Excel and then added to a Word document. Once the PivotTable report is created in the hidden instance of Excel, it is selected and copied to the Clipboard. Then, either a new instance of Word is started if the current host is Excel or the current instance of Word is used if the recognition happened in Word. The selection is then pasted from the toolbar into Word using the PasteSpecial method that will paste an active OLE object into an Office application.

...
   Case 3 ' Insert a PivotTable report for the current term in Word.
      ' Create a new instance of Excel.
      Set oex = New Excel.Application
      oex.Workbooks.Add

      ' Create a PivotTable report in Excel.
      strPTableName = InsertPivotTable(VerbID, ApplicationName, _
         oex, Properties, Text, XML)

      ' Select the newly created PivotTable report and
      ' copy it to the Clipboard.
      With oex.ActiveSheet.PivotTables(strPTableName)
         .PivotSelect "", xlDataAndLabel, True
      End With
      oex.Selection.Copy

      ' If the current host application is not Word then
      ' create a new instance of Word.
      If InStr(ApplicationName, "Word.Application") > 0 Then
         Set owd = Target.Application
      Else
         Set owd = New Word.Application
         With owd
            .Documents.Add , , , True
            .Visible = True
         End With

      End If

      ' Paste the PivotTable report into the instance of Word.
      With owd
         .Selection.EndKey Unit:=wdLine
         .Selection.TypeParagraph
         .Selection.TypeParagraph
         .Selection.PasteSpecial Link:=False, _
            DataType:=wdPasteOLEObject, Placement _
            :=wdInLine, DisplayAsIcon:=False
      End With

      ' This prevents the large amount of Clipboard data message.
      oex.CutCopyMode = False

      ' Destroy the Excel instance.
      oex.ActiveWorkbook.Close False
      Set oex = Nothing
...

One problem encountered by developers when adding large items to the Clipboard in newer versions of Office is a warning message displayed notifying users that large amounts of data have been placed on the Clipboard. This message is displayed when the hidden copy of Excel is closed. To overcome this problem, you need to set the CutCopyMode property of the Excel Application object equal to False. This clears the Clipboard when Excel closes and stops the message from appearing.

Case 4 and Case 5 both create a PivotTable report as in Case 3 but they also create a PivotChart report that is copied to the Clipboard. The major difference between these operations is that in Case 4, a static image of the PivotChart report is pasted into Word while in Case 5, an active copy is pasted into Word.

...
   Case 4 ' Insert a picture of a PivotChart report into Word.
      ' Create a new instance of Excel.
      Set oex = New Excel.Application
      oex.Workbooks.Add

      ' Create a PivotTable report in Excel.
      strPTableName = InsertPivotTable(VerbID, ApplicationName, _
         oex, Properties, Text, XML)

      ' Create a PivotChart report in Excel based on that PivotTable
      ' report and copy it to the Clipboard.
      oex.Charts.Add
      With oex.ActiveChart
         .Location Where:=xlLocationAsNewSheet
         .ChartArea.Select
         .ChartArea.Copy
      End With

      ' If the current host application is not Word then create a new
      ' instance of Word.
      If InStr(ApplicationName, "Word.Application") > 0 Then
         Set owd = Target.Application
      Else
         Set owd = New Word.Application
         With owd
            .Documents.Add , , , True
            .Visible = True
         End With
      End If

      ' Paste the PivotChart report into Word as a static image.
      With owd
         .Selection.EndKey Unit:=wdLine
         .Selection.TypeParagraph
         .Selection.TypeParagraph
         .Selection.PasteAndFormat (wdChartPicture)
      End With

      ' This prevents the large amount of Clipboard data alert message.
      oex.CutCopyMode = False

      ' Destroy the Excel instance.
      oex.ActiveWorkbook.Close False
      Set oex = Nothing
...

To paste a static PivotChart report into Word, the PasteAndFormat method of the Selection object is used. Passing the wdChartPicture parameter to this method tells Word to insert the image of the chart rather than linking to it.

...
   Case 5 ' Insert an active PivotChart report into Word.
      ' Create a new instance of Excel.
      Set oex = New Excel.Application
      oex.Workbooks.Add

      ' Create a PivotTable report in Excel.
      strPTableName = InsertPivotTable(VerbID, ApplicationName, _
         oex, Properties, Text, XML)

      ' Create a PivotChart report in Excel and copy it
      ' to the Clipboard.
      oex.Charts.Add
      With oex.ActiveChart
        .Location Where:=xlLocationAsNewSheet
        .ChartArea.Select
        .ChartArea.Copy
      End With

      ' If the current host application is not Word then create a new
      ' instance of Word.
      If InStr(ApplicationName, "Word.Application") > 0 Then
        Set owd = Target.Application
      Else
        Set owd = New Word.Application
        With owd
          .Documents.Add , , , True
          .ActiveDocument.Select
          .Visible = True
        End With

      End If

      ' Paste the PivotChart report into Word as a static image.
      With owd
        .Selection.EndKey Unit:=wdLine
        .Selection.TypeParagraph
        .Selection.TypeParagraph
        .Selection.PasteAndFormat (wdChart)
      End With

      ' This prevents the large amount of Clipboard data message.
      oex.CutCopyMode = False

      ' Destroy the Excel instance.
      oex.ActiveWorkbook.Close False
      Set oex = Nothing
...

To paste an active PivotChart report into Word, you use the PasteAndFormat method of the Selection object. But this time, pass the wdChart parameter. This tells Word to insert an OLE object that is a representation of the PivotChart report.

Launching Data Analyzer

One of the features that the BI Smart Tag offers is the ability to analyze the recognized term in Data Analyzer, assuming that it is installed on the local system. Earlier, you saw that I used a function called ISDataAnalyzerInstalled to determine whether or not Data Analyzer was resident on the local machine. This function simply checks the appropriate registry entry for Data Analyzer and returns True if the Data Analyzer key exists. You could use essentially the same function to check if any version of any product exists on the local machine by looking for the appropriate registry key.

Public Function ISDataAnalyzerInstalled() As Boolean
   Dim s As String
   s = GetSettingString(HKEY_LOCAL_MACHINE, _
      "SOFTWARE\Microsoft\Data Analyzer\3.5\Install", "MAINDIR", "")
   ISDataAnalyzerInstalled = (s <> "")
End Function

When we have determined that Data Analyzer is installed, then we can launch it with just the information contained in the Property Bag, which is passed to the action handler from the recognizer. As the code to format Data Analyzer in the correct way when it is launched is rather verbose and to avoid filling this article with pages and pages of code, have a look at the DataAnalyzer.bas module in the BISmartTag.vbp Visual Basic project that is included in a separate folder in the download directory for this solution. This module contains all of the code required to launch data analyzer and have it display pertinent information for the recognized member in the cube.

Launching the Custom Actions

The Data Analyzer action discussed earlier demonstrated one type of custom action that only appears if Data Analyzer is installed. With the BI Smart Tag, there are also custom actions that call out to Web sites and pass the relevant cube, dimension, level, and recognized term information through a URL. As you saw previously, the list of custom actions was loaded when the action handler started because this list of actions is required to enable the action menu to be built correctly. The GetCustomActions function is used to load the list of custom actions from a local XML file into a Recordset object. This local file is located in the same directory as the BISmartTag dynamic-link library (DLL) and is called BICustomActions.xml. As with the BITerms.xml file used to maintain information about the connections required to extract the list of terms to be recognized, the BICustomActions.xml file is created by exporting the Recordset object to XML. The creation of the local file is covered in more detail later when the BI Smart Tag Wizard is discussed. The format of the XML file is as follows:

...
<xml xmlns:s="uuid:BDC6E3F0-6DA3-11d1-A2A3-00AA00C14882"
   xmlns:dt="uuid:C2F41010-65B3-11d1-A29F-00AA00C14882"
   xmlns:rs="urn:schemas-microsoft-com:rowset"
   xmlns:z="#RowsetSchema">
<s:Schema id="RowsetSchema">
<rs:data>
<rs:insert>
   <z:row CustomText="View store sales for this product" 
      CustomURL="http://localhost/BISmartTags/CustomMDX2.htm?%TEXT%" />
   <z:row CustomText="Analyze products over time"
      CustomURL="http://localhost/BISmartTags
        /CustomMDXChart.htm?%TEXT%" />
   </rs:insert>
   </rs:data>
   </xml>
...

The XML file contains one or more rows of data with each row containing two fields:

  • CustomText—This field stores the title of the custom action that is displayed in the Action menu.
  • CustomURL—This field holds the URL that will be called when the custom action is selected. For each URL, you can define a number of placeholders that are replaced by database connection values when the URL is formatted and sent to the browser. For more information on these placeholders see the custom URL description in the following section that talks about the BI Smart Tag Wizard.

The custom actions are called from the Case Else section of the Select Case statement. The VerbID variable is used to navigate to the appropriate record in the in-memory Recordset object of custom actions and then the CustomURL value is extracted from the selected record.

...
   Case Else ' Open one of the custom URL actions.

      ' Move to the first custom URL record.
      rsCustomActions.MoveFirst

      ' Move to the custom action record associated with 
      ' the current VerbID returned from the action list.
      rsCustomActions.Move ((VerbID - iActionCount) - 1)

      ' Get the URL and replace any placeholders with 
      ' the appropriate values.
      strURL = rsCustomActions("CustomURL").Value
      strURL = ReplaceTerms(strURL, Text, Properties)

      ' Open Internet Explorer and display the page.
      Set ie = CreateObject("InternetExplorer.Application")
      ie.Navigate2 (strURL)
      ie.Visible = True
...

Before the URL is sent to the browser, the ReplaceTerms function is called to replace any placeholders that appear in the URL with the data warehouse connection information:

Public Function ReplaceTerms(strURL As String, _
      ByVal Text As String, _
      ByVal Properties As SmartTagLib.ISmartTagProperties) As String
   On Error Resume Next

   strURL = Replace(strURL, "%Server%", Properties.Read("Server"))
   strURL = Replace(strURL, "%Provider%", Properties.Read("Provider"))
   strURL = Replace(strURL, "%Catalog%", Properties.Read("Catalog"))
   strURL = Replace(strURL, "%Cube%", Properties.Read("Cube"))
   strURL = Replace(strURL, "%Dimension%", _
      Properties.Read("Dimension"))
   strURL = Replace(strURL, "%Level%", Properties.Read("Level"))
   strURL = Replace(strURL, "%Text%", Text)

   ReplaceTerms = strURL
End Function

Once any placeholders have been replaced with the actual values from the data warehouse connection information, an instance of Microsoft Internet Explorer is created, the URL is navigated to, and the instance of Internet Explorer is displayed.

BI Smart Tag Wizard

To support the operation of the BI Smart Tag, some mechanism is required to create the local XML files that are used to connect to the data warehouse and launch custom actions. This is achieved using a wizard that allows the user to select the server, catalog, and cube to connect to and by enabling the selection of one or more dimensions and associated levels. Once all the selections have been made, the wizard writes to a local XML file called BITerms.xml. The wizard also contains functionality to define, test, and save the custom action URL calls. These are saved to a second XML file called BICustomActions.xml.

The BI Smart Tag Wizard is a good example of when it is appropriate to use Microsoft Visual Basic® rather than Office to achieve some of the goals required from your Enterprise Office application. In this case, what is required is a standalone application that can be used to build custom XML files. It is quite possible that someone could use this tool in an administrative capacity to define specific files that could then be distributed to all users of the BI Smart Tag solution. Alternatively, an individual user of the BI Smart Tag solution could create their own local file either by launching the BI Smart Tag Wizard from its installed location on the Start menu or from a menu within either Word or Excel. The Word and Excel menus are added by the third piece of the solution, the BI Smart Tags COM add-In.

The wizard consists of five screens that walk the user through the process of connecting to the data warehouse and selecting the levels that will generate the terms to be recognized. The user goes through each screen of the wizard selecting the appropriate information, and in the final screen a summary of the chosen information is shown. Once they are satisfied with this information, they can then save the selections to a local XML file that will be used by the smart tags in Word and Excel to connect to the correct cube.

One problem with the smart tag architecture in an application like this, is that the recognizer only initializes itself once when its parent application starts up. There is no practical way to force the recognizer to reinitialize, which means that it is very hard to have it re-read the list of recognized terms. The easiest way to overcome this problem is to ask the user to re-start the host application whenever they make changes that will affect the recognized term list. This is the technique chosen in the BI Smart Tag solution in that once the local XML file is written, the user is prompted that if they have Word or Excel open and make a change that affects the recognizer term list, they must close and restart the application.

While the focus of this article is not on how to build Visual Basic applications, it is important to understand some of the techniques used to extract the data warehouse information. By addressing some of the techniques used in the wizard, it will give you an indication for how to implement this type of system and will also make it easier to understand how to access the same type of information from within an Office application.

The Main Wizard Form

The main form used by the wizard is constructed using a single form with multiple panels (implemented using Visual Basic picture boxes). Each picture box contains the controls required to service the user features for that particular screen of the wizard. There is a navigation bar at the bottom of the screen that allows the user to move forwards and backwards through the screens of the wizard. This movement is achieved by showing and hiding the picture boxes and moving them to the correct position on the screen (see Figure 4).

Click here for larger image

Figure 4. The Main form of the BI Smart Tag Wizard (click picture to see larger image)

The first screen of the wizard allows the user to choose both a server to connect to as well as the type of data provider to use to make the connection. The server can be either a physical database server or an offline cube file.

Comparing OLAP Databases to Offline Cubes

Because so much of the Office story revolves around the great offline functionality that you get with Office, an important consideration when building applications using Office programs that query OLAP data is to ensure that if the user does not have access to the OLAP server, there are still useful functions they can perform. This can be addressed by using an offline cube file (.cub). In the BI Smart Tag Wizard, the user can select an offline cube by entering the local path in to the text box on the screen. The processing inside the wizard is exactly the same as with a cube located on a server except that the server name is now set to the path of the local cube. In the folders containing the BI Smart Tag solution, a number of offline cubes have been included to allow you to test this functionality and to ensure that you can use the tool without installing Analysis Server. There are two cubes based on the Fabrikam data (Sales and Purchase Orders) and there is also a cube based on the Great Plains sample database that was created using a product called QuickOLAP from Corporate Renaissance Group. Corporate Renaissance Group is a Microsoft Great Plains partner.

Returning a List of OLAP Catalogs

Once the server connection is made, a list of databases is retrieved from the server and displayed in the lvwCatalogs control. The list of databases returned are those defined as OLAP catalogs because the default provider used is MSOLAP. To do this, a connection to the server is opened using the fOpenConnection function detailed in the Loading the List of Recognizable Terms section. Once this is done, a second subroutine, UpdateDatabaseListView, is called which extracts all of the valid catalogs available on the server:

Private Sub UpdateDatabaseListView()
   Dim rs As ADODB.Recordset, sCatalogActive As String, _
      sCatalogCur As String
   Dim i As Integer
 
   lvwCatalogs.ListItems.Clear
   i = 0

   If cnActive Is Nothing Then
      lvwCatalogs.Enabled = False
   ElseIf cnActive.State <> adStateOpen Then
      lvwCatalogs.Enabled = False
   Else
      lvwCatalogs.Enabled = True
      sCatalogActive = cnActive.DefaultDatabase
      Set rs = cnActive.OpenSchema(adSchemaCatalogs)
      Do Until rs.EOF
         sCatalogCur = rs.Fields(m_sFieldCatalogName)
         lvwCatalogs.ListItems.Add , sCatalogCur, sCatalogCur, _
            "Catalog", "Catalog"
         i = i + 1
         If sCatalogCur = sCatalogActive Then
           lvwCatalogs.ListItems(i).Selected = True
         End If
         rs.MoveNext
      Loop
   End If
End Sub 

Returning a List of Cubes for a Catalog

When a user selects a catalog from the list in the second screen of the wizard and then presses the Next button, the GetCubesLV function is called which extracts all of the valid cubes from that catalog:

Private Function GetCubesLV()
   Dim i As Integer

   cnActive.DefaultDatabase = lvwCatalogs.SelectedItem
   Set m_ctActiveCatalog = New Catalog
   Set m_ctActiveCatalog.ActiveConnection = cnActive

   ' Repopulate the cube selecting the old cube, if present.
   lvwCubes.ListItems.Clear
   With m_ctActiveCatalog.CubeDefs
      For i = 0 To .Count - 1
         lvwCubes.ListItems.Add , .Item(i).Name, .Item(i).Name, _
            "Cube", "Cube"
      Next i
   End With

   If lvwCubes.ListItems.Count > 0 Then
      lvwCubes.ListItems(1).Selected = True
   End If
 End Function

Returning Dimensions and Levels into a TreeView Control

When the user selects a cube and then presses the Next button, the SelectCube subroutine is called. This subroutine is responsible for building a tree view that allows the user to select the dimensions and levels that they wish to extract terms from. The SelectCube subroutine connects to the catalog and then adds a parent node to the tree view containing the cube information. Dimensions and levels are added to the tree view using the Expand event handler of the TreeView control. This event fires every time a node of the tree view is expanded. To ensure that this happens the first time a node is visited, a dummy node containing no information is added as a child of the cube node and the tree view is then forced to expand by setting the Expanded property of the current node. This causes the Expand event to fire and the Expand event handler catches and processes the event:

Private Sub SelectCube()
   On Error GoTo lstCubes_ClickErr

   Dim ndCur As MSComctlLib.node
   Dim cbCur As CubeDef

   tvwLevels.Nodes.Clear
   ListNodeMembers Nothing

   Set cbCur = m_ctActiveCatalog.CubeDefs(lvwCubes.SelectedItem.Text)

   tvwLevels.Enabled = True

   With tvwLevels.Nodes
      .Clear

      Set ndCur = .Add(Text:=cbCur.Name, Image:=icoCube)
      ndCur.Tag = sGetLegalName(cbCur.Name)
      AddDummyChild ndCur.Index

      ndCur.Expanded = True

   End With

Finally:
   Exit Sub

lstCubes_ClickErr:
   SetStatus
   SetHourglass False
   DisplayError "Unable to retrieve metadata for the cube '" & _
      lvwCubes.SelectedItem & "'", cnActive
End Sub

When the statement ndCur.Expanded = True in the SelectCube subroutine is executed, the Expand event handler catches the Expand event for the current node and if the node does not already have children, calls the PopulateNode subroutine which is responsible for adding the children to the current node:

Private Sub tvwLevels_Expand(ByVal node As MSComctlLib.node)
   On Error GoTo tvwLevels_ExpandErr

   m_fDblClickedNode = False
   If Not fChildrenPopulated(node) Then
      PopulateNode node.Index
   End If

Finally:
   Exit Sub

tvwLevels_ExpandErr:
   SetHourglass False
   DisplayError "Unable to expand node.", cnActive

End Sub

The PopulateNode subroutine takes the current node and using the oGetAssociatedObject function detailed in the Loading the List of Recognizable Terms section, gets an object that represents either a dimension or a level. The object is subsequently examined to determine its type and the appropriate node type is added to the tree view:

Private Sub PopulateNode(iNodeIndex As Integer)
   On Error GoTo PopulateNodeError

   Dim ndNew As MSComctlLib.node
   Dim oAssociatedObject As Object
   Dim dmCur As Dimension, hrCur As Hierarchy, lvCur As Level
   Dim eIcon As MetadataIcons
   Dim eFirstLevelIcon As MetadataIcons

   With tvwLevels.Nodes
      ' Remove the dummy node.
      .Remove tvwLevels.Nodes(iNodeIndex).Child.Index

      ' Get object associated with the current node.
      Set oAssociatedObject = _
         oGetAssociatedObject(tvwLevels.Nodes(iNodeIndex))

      ' Based on the type of the ADOMD object associated 
      ' with the node, loop through the objects contained 
      ' in this associated object and add child nodes to 
      ' represent each of these contained objects.
      If TypeOf oAssociatedObject Is CubeDef Then
         For Each dmCur In oAssociatedObject.Dimensions
            Set ndNew = .Add(relative:=iNodeIndex, _
               relationship:=tvwChild, _
               Text:=dmCur.Name, Image:=icoDimension)
            ndNew.Tag = dmCur.UniqueName
            AddDummyChild ndNew.Index

         Next dmCur
      ElseIf TypeOf oAssociatedObject Is Dimension Then
         For Each hrCur In oAssociatedObject.Hierarchies
            Set ndNew = .Add(relative:=iNodeIndex, _
               relationship:=tvwChild, _
               Text:=hrCur.Name, Image:=icoHierarchy)
            ndNew.Tag = hrCur.UniqueName
            AddDummyChild ndNew.Index
         Next hrCur
      ElseIf TypeOf oAssociatedObject Is Hierarchy Then
         eFirstLevelIcon = icoLevel1
         For Each lvCur In oAssociatedObject.Levels
            If lvCur.Properties("LEVEL_TYPE") = MDLEVEL_TYPE_ALL Then 
               ' Do not localize.
               eIcon = icoLevelAll
               eFirstLevelIcon = icoLevelAll
            ElseIf lvCur.Name = MEASURES_LEVEL_NAME Then
               eIcon = icoLevel1
            Else
               eIcon = eFirstLevelIcon + _
                  CInt(lvCur.Properties("LEVEL_NUMBER").Value) 
                  '* Do not localize
               If eIcon > icoLevel16 Then 
                  ' Deepest level icon.
                  eIcon = icoLevel16
               End If
            End If
            Set ndNew = .Add(relative:=iNodeIndex, _
               relationship:=tvwChild, _
               Text:=lvCur.Name, Image:=eIcon)
            ndNew.Tag = lvCur.UniqueName
            AddDummyChild ndNew.Index
         Next lvCur
      ElseIf TypeOf oAssociatedObject Is Level Then
      ' Don't populate members.
      ElseIf TypeOf oAssociatedObject Is Member Then
      ' Don't populate.
      Else
         Debug.Assert False
      End If
   End With
Finally:
   Exit Sub

PopulateNodeError:
   Set ndNew = tvwLevels.Nodes.Add _
      (relative:=iNodeIndex, relationship:=tvwChild, _
      Text:="Error:  " & Err.Description)
End Sub

As well as adding a name to the tree view, the unique name of the node is added to the Tag property. The unique name takes the format:

[Product].[Product Family]

Where [Product] is the dimension and [Product Family] is a level within that dimension. This unique name is important when the smart tag has to use the Drilldown function in Excel to drill into the PivotTable report. It also provides an easy way to uniquely identify the specific level that is being examined.

Confirm the Selections and Save to XML

The final screen of the wizard allows the user to confirm that the selections they have made are correct before saving the details to the local XML file. The local XML file, BITerms.xml, is written when the user clicks the Finish button. The event handler for the Finish button calls the subroutine WriteLocalXMLTermList that builds a Recordset object of all of the term information to be exported and then persists the contents of the Recordset object to the local XML file:

Private Sub WriteLocalXMLTermList()
   Dim rsTerms As New ADODB.Recordset
   Dim i As Integer, j As Integer
   Dim strLocalXML As String
   Dim arrDimensions() As String
   Dim fso As New FileSystemObject
   Dim arrLevels() As String, arrlevel() As String
   Dim strLevel As String, strLevels As String

   With rsTerms.Fields
      .Append "Server", adVarChar, 255
     .Append "Provider", adVarChar, 255
     .Append "Catalog", adVarChar, 255
     .Append "Cube", adVarChar, 255
     .Append "Dimension", adVarChar, 255
     .Append "Level", adVarChar, 255
   End With

   rsTerms.Open
   arrDimensions = Split(lblDimension, ",")
   arrLevels = Split(lblDimlevel, ",")

   For i = 0 To UBound(arrDimensions)
      With rsTerms
         .AddNew
         rsTerms!Server = lblServer
         rsTerms!Provider = lblProvider
         rsTerms!Catalog = lblCatalog
         rsTerms!Cube = lblCube
         rsTerms!Dimension = arrDimensions(i)
         strLevels = ""
         For j = 0 To UBound(arrLevels)
            arrlevel = Split(arrLevels(j), ".")
            If arrlevel(0) = ("[" & arrDimensions(i) & "]") Then
               strLevel = arrlevel(1)
               strLevel = Mid(strLevel, 2, Len(strLevel) - 2)
               If strLevels = "" Then
                  strLevels = strLevel
               Else
                  strLevels = strLevels & "," & strLevel
               End If
            End If
         Next j
         rsTerms!Level = strLevels 'lblLevel
         .Update
      End With
   Next i

   strLocalXML = App.Path & "\" & "BITerms.xml"

   If fso.FileExists(strLocalXML) Then
      fso.DeleteFile strLocalXML, True
   End If

   rsTerms.Save strLocalXML, adPersistXML

   lblLocalFileLoc.Caption = " Lookup file saved to: " & strLocalXML
   MsgBox "Cube Selection Information Saved to:" & _
      vbCrLf & vbCrLf & strLocalXML, _
      vbOKOnly + vbInformation, "Local File Saved"
End Sub

The final screen of the wizard also gives the user access to the Add Custom URL Actions Lookup Actions dialog box where they can define any custom actions for the smart tag.

Custom Action Definitions

The creation of custom actions for the BI Smart Tag is handled by a separate screen that is launched from the last screen of the BI Smart Tag Wizard by pressing the Advanced button.

Click here for larger image

Figure 5. Form to create and edit the custom actions in the action handler (click picture to see larger image)

The screen (see Figure 5) allows users to define the URL and display a name for a custom action and then store all of the custom action information in a local XML file. At the bottom right of the screen is a list of the connection information selected using the wizard. The custom action screen allows placeholders for this information to be added to the URL by placing the cursor in the required location of the URL text box and then double-clicking the required connection value. These placeholders are replaced when the URL is opened in Internet Explorer as described previously in Launching the Custom Actions section.

BI Smart Tag COM Add-In

One of the biggest challenges when developing solutions for Office is to make all of the pieces that comprise the solution, appear as a cohesive whole. While you can build an architecture that defines the way multiple applications interact, there is a slightly different challenge when you are attempting to create a solution that must appear as if it were a single application. In the case of the BI Smart Tag solution, there are two separate applications that combine to form a single solution. And while smart tags form the heart of the solution, a stand-alone executable file provides the wizard-style administrative interface. It is preferable to have these different applications combined into a cohesive interface with which a user can interact directly from within Office. To achieve this, you can use the power of the COM add-in architecture that is integrated into each Office program. A COM add-in can add menus or features to Office that allow any custom actions required to support the solution to be accessed.

In the case of the BI Smart Tag solution, the COM add-in provides a unifying interface that gives access to the BI Smart Tag Wizard. In addition, the solution provides a custom form that allows a user to select a recognizer term from a list and then have that term inserted into a document. This happens without the user needing to know what terms are being recognized when they type them into the document.

Choosing a Language for Your COM Add-In

One of the first decisions to make when building a COM add-in is which programming language to use. You can build COM add-ins using Microsoft Office XP Developer or if you want a little more control over the add-in then you may want to use Microsoft Visual Basic 6.0 as the development environment. It is also possible to build COM add-ins using the Microsoft Visual Basic .NET language. However, this means that the common language runtime must be resident on the local machine; currently there are compatibility issues between the two architectures due to complexities in the COM interoperability infrastructure.

The BI Smart Tag COM add-in was built using Visual Basic 6.0 and the integrated add-in designer. Because it will be used in both Word and Excel, it required two add-in designers for the project. One handles the creation of the COM add-in for Excel and the other handles the Word COM add-in.

Creating a Menu from a COM Add-In

One of the first tasks for a COM add-in is the creation of a new menu within the Office application where the add-in will be resident. This menu provides users access to the various functional areas of the solution. There are a number of steps involved in adding custom menus to an Office application from within a COM add-in.

First, you declare one or more objects that will be used to hold references to the menu items that are being added to the Office interface. These are declared using the WithEvents keyword in order to add code into the event handlers that will execute when a menu item is selected:

...
' Button we will use in a menu.
Private WithEvents mbtnRunWizard As CommandBarButton
' Button we will use in a menu.
Private WithEvents mbtnShowTermList As CommandBarButton...

Next, declare another object that will hold a reference to the parent application of the COM add-in (the application that instantiates the add-in). This also will be declared using the WithEvents keyword to ensure that application events can be trapped and acted upon.

...
Public WithEvents mobjExcel As Excel.Application
...

Once these objects have been declared, you can initialize them by placing code in the OnConnection event handler of the COM add-in. This event fires when the add-in is initialized by its parent application. A reference is also set to the current parent application and then the AddCustomMenus function is called to add the custom menus:

Private Sub AddinInstance_OnConnection(ByVal Application As Object, _
      ByVal ConnectMode As AddInDesignerObjects.ext_ConnectMode, _
      ByVal AddInInst As Object, custom() As Variant)
   Set mobjExcel = Application

   AddCustomMenus

   InitServerTermsRS
End Sub

The process of adding the menu items to the interface is addressed by using the CommandBars collection made available by VBA. This collection holds references to all of the menus and toolbars available within the parent application. Unless there is a specific reason to add your menu items to an existing menu, it is often a better technique to create a new custom menu or toolbar that will contain all of the items for your application. This makes it less likely that the user overlook the functionality you add to their system. In the case of the BI Smart Tag, it was decided to use a custom toolbar that contained a drop-down menu:

Public Sub AddCustomMenus()
   ' Holds reference to Commandbars collection in Excel.
   Dim objCmdBars As CommandBars
   ' Holds reference to a command bar that will be added.
   Dim objMenuBar As CommandBar
   ' Holds reference to a control that will be placed
   ' on the command bar.
   Dim objCmdBarCtrl As CommandBarControl

   ' Set a reference to the parent application’s Commandbars
   ' object.
   Set objCmdBars = mobjExcel.CommandBars

   ' Create a new command bar in the Commandbars collection.
   Set objMenuBar = objCmdBars.Add("BI Smart Tag", , , True)

   ' Set the new command bar’s properties to make it
   ' visible and docked.
   With objMenuBar
      .Visible = True
      .Position = msoBarTop
      .RowIndex = objCmdBars("Standard").RowIndex
      .Left = objCmdBars("Standard").Width
   End With

   ' Add a popup control to the new command bar. This is
   ' effectively a drop-down menu.
   Set objCmdBarCtrl = objMenuBar.Controls.Add _
      (msoControlPopup, , , , True)
   objCmdBarCtrl.Caption = "&BI Smart Tag"

   ' Add a new control to the drop-down menu and set
   ' a reference to it using one of the object variables
   ' declared earlier. This will allow you to hook into
   ' the controls events. A button control with the
   ' msoControlPopup attribute works like a menu item.
   Set mbtnRunWizard = _
      objCmdBarCtrl.Controls.Add(Type:=msoControlButton, _
      Temporary:=True, Before:=1)
   With mbtnRunWizard
      .Caption = "BI Smart Tag &Wizard"
      .Tag = "ShowWizard"
      .Enabled = True
   End With

   ' Add a second new control to the drop-down menu
   ' and then set a reference to it using one of the
   ' object variables declared earlier. This will
   ' allow you to hook into the control’s events.
   Set mbtnShowTermList = _
      objCmdBarCtrl.Controls.Add(Type:=msoControlButton,  _
      Temporary:=True)
   With mbtnShowTermList
      .Caption = "Current &Term List..."
      .Tag = "GetTermList"
      .Enabled = True
   End With
End Sub

Notice the use of the Temporary:=True statement. This statement ensures that the control is not added permanently but has the same lifetime as the application. If you do not do this, you can easily end up adding multiple toolbars for the same application each time the add-in is instantiated.

Having added a menu and custom buttons to the Office interface, the process of adding code to handle the user's selection of one of these menu items requires adding code to the Click event handler of the CommandBarButton object. Because the CommandBarButton objects were declared using the WithEvents keyword, you will be able to navigate to their list of events in the same way as you would for any other controls within Visual Basic.

Click here for larger image

Figure 6. Custom command bar buttons appear in list of objects in Visual Basic (click picture to see larger image)

There are two tasks that the BI Smart Tag COM add-in must facilitate: the launching of the BI Smart Tag Wizard, which is a standard windows executable, and the display of the list of terms that are recognized by the smart tag.

Launching an Executable

Launching an executable file EXE from within Visual Basic or VBA is a one-line function. All that is required is to call the Shell function, passing it a path to the executable file:

Private Sub mbtnRunWizard_Click _
      (ByVal Ctrl As Office.CommandBarButton, CancelDefault As Boolean)
   ' Launch the BI Smart Tag Wizard.
   Shell App.Path & "\BI Smart Tag Client.exe", vbNormalFocus
End Sub

Displaying and Inserting Terms

The list of terms is constructed using the same code that the smart tag uses for building its recognition list. The only difference being that once the Recordset object has been constructed, each term is added to a List Box control contained in a form. The Load function (described later in this section) is used to display the form. This ensures that the form does not need to know about the parent application, but is just responsible for showing the list of terms and returning the term that the user selects. The calling function is then responsible for inserting the term into either Excel or Word. The next two code samples demonstrate this when Excel is the parent application and when Word is the parent application, respectively:

Private Sub mbtnShowTermList_Click _
      (ByVal Ctrl As Office.CommandBarButton, _
      CancelDefault As Boolean)
   Dim strTerm As String
   ' Load the frmTermList form and collect the returned value.
   strTerm = frmTermList.Load(True)

   ' If there is a value returned then insert it into the
   ' active Excel cell.
   If strTerm <> "" Then
      mobjExcel.ActiveCell = strTerm
   End If
End Sub

Private Sub mbtnShowTermList_Click _
      (ByVal Ctrl As Office.CommandBarButton, CancelDefault As Boolean)
   Dim strTerm As String

   ' Load the frmTermList form and collect the returned value.
   strTerm = frmTermList.Load

   ' If there is a value returned then insert it into the
   ' Word document at the cursor.
   If strTerm <> "" Then
      mobjWord.Selection = strTerm
   End If
End Sub

The next sample shows the code that is included in the frmTermList form’s Load function. The variable bCancel is used to determine whether the user actually selected a term and is initialized to False when the form is loaded:

Public Function Load(Optional bNotAddin As Boolean) As String

   ' Call a function to extract the list of terms
   ' and place them in the list box.
   BuildTermListNoProgress

   ' Set a Boolean variable that determines whether
   ' the user cancelled the form or selected a term.
   bCancel = True

   ' Show the frmTermList form modally.
   Me.Show vbModal
  
   ' If the Boolean value is not set to Cancelled
   ' then return the currently selected term.
   If Not bCancel Then
      Load = lvwTerms.SelectedItem.Text
   Else
      Load = ""
   End If

   ' Unload the frmTermList form.
   Unload Me
End Function

The bCancel variable is only set to True if the user double-clicks a term in the term list. This tells the Load function to return the selected value. Otherwise, it returns an empty String;

Private Sub lvwTerms_DblClick()
   bCancel = False
   Me.Hide
End Sub

Summary

You should now have a firm foundation on which to start building solutions that utilize the power of Office to analyze OLAP data through the use of smart tags and wizards. This knowledge should endear you to your managers by providing them with the powerful analytic functionality they require. More than that, you can take this knowledge and engineer it into other systems that can provide an integrated solution for data capture, processing and review.

Did you find this helpful?
(1500 characters remaining)
Thank you for your feedback
Show:
© 2014 Microsoft. All rights reserved.