Code Walkthrough: The Business Intelligence Smart Tag SolutionThis content is no longer actively maintained. It is provided as is, for anyone who may still be using these technologies, with no warranties or claims of accuracy with regard to the most recent product version or service release.
Matthew Nunn, Dell | Plural Professional Services
Frank C. Rice, Microsoft Corporation
Business Intelligence Smart Tag Wizard
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)
Accessing Data Warehoused Data
A Brief Introduction to Cubes, Dimensions, Levels and Members
BI Smart Tag Solution
BI Smart Tag
Loading the List of Recognizable Terms
Recognizing a Term
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
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.
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.
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.
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.
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.
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
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
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.
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
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
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.
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
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.
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.
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.
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.
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.
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) ...
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
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.
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 ...
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 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.
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.
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.
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.
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 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).
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.
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.
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
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
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
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] 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.
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.
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.
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.
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.
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.
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.
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 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
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
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
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.