Taking Your OBIEE to the Next Level with SmartView VBA 11.1.1.7.1 [TECH ARTICLE]

Version 8

    Steve Yeung's article walks you through an advanced approach to starting your own Smart View for OBIEE, using Excel Visual Basic for Applications (VBA). The objective is to inspire you with what you can do with Smart View by providing solutions that do not exist in the current OBIEE versions.


     

    by Steve Yeung

     

    Introduction

     

    Since the launch of Oracle Business Intelligence Enterprise Edition (OBIEE) 11.1.1.7, Smart View was introduced as a replacement for BI Office. From a Smart View point of view, the version number is a little different since it's evolving from the Hyperion Enterprise Performance Management (EPM) side. So, Smart View version 11.1.2.3 will work properly with OBIEE 11.1.1.7, but it is recommended that you use the latest version, 11.1.2.5.216 (at the time of writing).

     

    The introduction of Smart View for OBIEE greatly enhanced the flexibility of personal Desktop Business Analytics that everyone can use; it can be both really simple and very advanced, providing end users with answers to questions and giving rapid results. Since Smart View highly leverages OBIEE, it allows end users to build BI ad hoc and Answer reports, and share insights by publishing to OBIEE catalog or even dashboards with all securities in place.

     

    In this article, I will take you through the steps required to start your own Smart View for OBIEE in a more advanced way, using Excel Visual Basic for Applications (VBA). The objective of this article is to inspire you with what you can do with Smart View by providing solutions that do not exist in the current OBIEE versions.

     

    Prerequisites:

     

    This document assumes that you have basic knowledge of Smart View and have the following installed:

     

    1. OBIEE Version 11.1.1.7+ with Sample Sales Lite
    2. Excel 2007 or Excel 2010 with Developer Mode enabled
    3. Smart View 11.1.2.5+

     

    Skills required for this exercise:

     

    1. Basic Answer Development Knowledge
    2. Basic Excel and Excel VBA

     

    * Further reading for Smart View Oracle BI EE functions can be found here: https://docs.oracle.com/cd/E40530_01/epm.11123/smart_view_developer/frameset.htm?ch16.html.

     

    Getting Started

     

    Our objective was to meet the user requirement of having "Revenue Report by Product Type & Year" in a single Excel workbook, with individual sheets of the same report by Product Type and Year.

     

    To meet this requirement, we must first create an Answer Analysis that has the following objects from Sales Sample Lite:

     

    1. Per Name Year
    2. Per Name Month
    3. Product Type
    4. Product
    5. Company
    6. Revenue
    7. Target Revenue

     

    In Results, we will need to edit the default tableView!1 and drag Per Name Year as well as Product Type to Table Prompts. This will be the basic layout of the report needed for Excel Export.

     

    image001.png
      Figure 1

     


     

    To be able to generate multiple sheets in one Excel Workbook, we will need to create another table that allows Excel to store Per Name Year and Product Type variables to be used. Create another table view with only Per Name Year and Product Type, with all other objects in the Excluded area.

     


     

    image002.png

     

    Figure 2

     

    We can now save this report; for demonstration purposes, I have saved it to a new folder in the Shared catalog called Test, with the report named as Untitled.

     

    Creating your Excel VBA

     

    We will create an Excel template so that future reports can leverage this macro workbook. Start a new workbook and rename sheet1 as "Info." In the range A2:A8, we will have the following names:

     

    • Hostname
    • Port
    • Friendly Name
    • Catalog Folder
    • Report Name
    • Reporting Table
    • Prompt Table
    • Output Folder

     

    In column B, ranges B2:B8, we will fill in these variables. Example as follows:

     

    Your normal analytics link: http://hostname:port/analytics

     

    1. localhost (hostname)
    2. 7001 (port for OBIEE Analytics)
    3. localhost (named when you first create a private connection or found when you try to rename)
          image003.png
          Figure 3
    4. /shared/test/ (Folder in the catalog storing the Answer)
    5. Untitled (Answer saved name)
    6. tableView!1 (The table for reporting)
    7. tableView!2 (The table storing the prompt combinations)
    8. C:\ (A folder you can access for storing your Workbook output)

     

    You may also create additional instructions or format your worksheet. Here is an example:

     

    image004.jpg
      Figure 4

     


      In the example above, there is a Command Button called Run MultiSheet Report. To achieve this, the followings steps are done:

     

    1. In Developers tab, click Insert > ActiveX Command Button.
    2. Draw the button as required.
    3. Right click the button and set properties.
    4. Modify the name to cmdRun and caption Run MultiSheet Report.

     

    Smart View for OBIEE VBA Prerequisites

     

    Before we continue, there are two important steps to be done.

     

    1. Import Smart View.bas

      Go to Developers tab and click Visual Basic.

      image005.png
            Figure 5


            Right click on VBAProject (Book1) and click Import File.

      image006.png
            Figure 6


            Find where your Smart View is installed and go to bin > smartview.bas. Click Open. In my case, C:\Oracle\Smart View\bin\Smart View.bas:

      image007.png
            Figure 7


            This will create a new module named SmartViewVBA.

      image008.png
            Figure 8
           
    2. Enable references Oracle Smart View BI Extension and Oracle Smart View RC 1.0 Type Library. Continuing from above, go to Tools > References.
            image009.png
            Figure 9


            Choose Oracle Smart View BI Extension and Oracle Smart View RC 1.0 Type Library.

      image010.png
            Figure 10


            We are now ready to start our programming.

     

    Smart View VBA

     

    We should still be in Design Mode. To check, go to the developers ribbon and see if Design Mode is highlighted.

     

    1. Right click on Run MultiSheet Report, then click View Code.
          image011.png
          Figure 11
         
    2. Copy and paste the script below in between Private Sub cmdRun_Click() and End Sub:

     

    Private Sub cmdRun_Click()
    'Author: Steve Yeung
    'About the Author: http://steveyeung.com
    'Blog: http://MondayBI.com
    'Created using SmartView Version: 11.1.2.5.216
    'Date Created: 10 December 2014
    'Version: 1.0
    '--------------------------------------
    'Compulsory, do not modify
    'Reference
    'https://docs.oracle.com/cd/E40530_01/epm.11123/smart_view_developer/frameset.htm?launch.htm
    Dim obiee As IBIReport
    Set obiee = New SmartViewOBIEEAutomation
    Dim prompts() As BIReportPrompt
    
    
    '--------------------------------------
    Dim InfoWorkbook As Workbook
    Dim InfoSheet As Worksheet
    'MultiSheetReport.xlsm needs to be modified if this workbook save name changes
    Set InfoWorkbook = Workbooks("MultiSheetReport.xlsm")
    'Info needs to be modified if Worksheet name changes
    Set InfoSheet = Worksheets("Info")
    
    
    '--------------------------------------
    'Below variables are automated from capturing Info worksheet filled in information
    'There is no need to modify this unless there's a change in workflow
    Hostname = InfoSheet.Range("B2").Value
    Port = InfoSheet.Range("B3").Value
    FriendlyName = InfoSheet.Range("B4").Value
    CatalogFolder = InfoSheet.Range("B5").Value
    ReportName = InfoSheet.Range("B6").Value
    ReportingTable = InfoSheet.Range("B7").Value
    PromptTable = InfoSheet.Range("B8").Value
    OutputFolder = InfoSheet.Range("B9").Value
    InsertServer = "http://" & Hostname & ":" & Port & "/analytics/jbips"
    InsertAnswer = CatalogFolder & ReportName
    
    
    '--------------------------------------
    'Connect SmartView using FriendlyName from Info Sheet
    'SmartView will ask you to input username and password
    'If incorrect, program script will exit
    X = HypUIConnect(Empty, UserName, Password, FriendlyName)
    If X < 0 Then
    MsgBox ("Login Failed")
    Exit Sub
    End If
    
    
    '--------------------------------------
    'Turns off Screen updating to avoid the annoying blink screen
    Application.ScreenUpdating = False
    
    
    '--------------------------------------
    'To check if User has filled in the Prompt Table
    'It is required to be filled in
    'A validation can be done in Excel instead of VBA
    'But for this case, VBA is used since I'm lazy
    If PromptTable = "" Then
    Application.ScreenUpdating = True
    MsgBox ("Prompt Table is empty, please fill in and run again")
    Exit Sub
    End If
    
    
    '--------------------------------------
    'Initiate a new worksheet to store Table Prompt Variables
    Sheets.Add.Name = "Prompts"
    Dim PromptSheet As Worksheet
    Set PromptSheet = Worksheets("Prompts")
    PromptSheet.Activate
    ActiveSheet.Range("A1").Activate
    
    
    '--------------------------------------
    'Inserts the Prompt Table from Answers to this worksheet
    obiee.InsertView InsertServer, InsertAnswer, PromptTable, prompts, Default_Format, SameSheet
    
    
    '--------------------------------------
    'Counts and stores the Range for easy referencing later on
    ActiveSheet.Range("A2").Activate
    Dim LastRow As Integer
    LastRow = PromptSheet.UsedRange.Rows.Count
    'To get the total number of combinations to be used
    Dim PromptTimes As Integer
    PromptTimes = LastRow - 1
    'The number of times to loop in the for loop later on
    'This is variable (i)
    Dim LoopRowCount As Integer
    LoopRowCount = LastRow - 2
    Dim LastColumn As Integer
    LastColumn = PromptSheet.UsedRange.Columns.Count
    'LoopColCount is used to check the number of Page Prompts that exist
    'This is variable (j)
    Dim LoopColCount As Integer
    LoopColCount = LastColumn - 1
    
    
    '--------------------------------------
    'Create a new Workbook to store Reports
    Workbooks.Add
    'Uses Info sheet ReportName and the current time to save the report in the output folder
    TempWorkbook = ReportName & "_" & Format(Now(), "yyyymmdd_hh_mm_AMPM")
    TempWorkbook = TempWorkbook & ".xlsx"
    ActiveWorkbook.SaveAs Filename:=OutputFolder & TempWorkbook
    'Stores the workbook name for easy referencing
    Dim ReportWorkbook As Workbook
    Set ReportWorkbook = Workbooks(TempWorkbook)
    
    
    '--------------------------------------
    'Rename the new Report Workbook Sheet1 to TOC
    'This sheet will be used to create a hyperlink table of contents
    'The hyperlinks will be named by the Prompts Chosen
    'So that end users will be able to quickly jump to the worksheet
    'When too many worksheets are produced, this becomes useful
    Dim TOCSheet As Worksheet
    Worksheets("Sheet1").Name = "TOC"
    Set TOCSheet = Worksheets("TOC")
    'Names the Table of Contents with ReportName and Generated Time
    TOCSheet.Range("A1").Value = "Table of Contents for Report " & ReportName & " Generated on "
    & Format(Now(), "yyyymmdd hh:mm AMPM")
    
    
    '--------------------------------------
    'Setting for Number of Page Prompts
    'LoopColCount is already derived previously
    ReDim Pages(0 To LoopColCount) As String
    
    
    '--------------------------------------
    'Initialize the working sheet under generation later on
    'This sheet will be the sheet inserting the Answer table for i number of times
    'For each sheet, it will then edit the prompts and filled in automatically
    Dim CurrentSheet As Worksheet
    
    
    '--------------------------------------
    'Starts the worksheet creation loop (variable i)
    For i = 0 To LoopRowCount
    'Go to the new report workbook
    ReportWorkbook.Activate
    'Add a new sheet to the end of all worksheets
    'and initialize it with a name N + i times
    'Where i is the variable for number of worksheets generated
    Sheets.Add(After:=Sheets(Sheets.Count)).Name = "N" & i
    Set CurrentSheet = Worksheets("N" & i)
    CurrentSheet.Activate
    CurrentSheet.Range("A2").Activate
    'Insert the Answer report to the new worksheet
    obiee.InsertView InsertServer, InsertAnswer, ReportingTable, prompts, Default_Format, SameSheet
    
    
    '--------------------------------------
    'Starts page prompt loop
    'This loop fills the page prompt according to j variable derived earlier
    For j = 0 To LoopColCount
    InfoWorkbook.Activate
    PromptSheet.Activate
    PromptSheet.Range("A2").Activate
    'Stores the current prompt value
    PromptValue = ActiveCell.Offset(i, j).Value
    ReportWorkbook.Activate
    ActiveSheet.Range("A1").Activate
    'Inserts the prompt value to Worksheet Row 1 cells so that
    'end users would know which prompt values was chosen
    ActiveCell.Offset(0, j).Value = PromptValue
    ActiveSheet.Range("A2").Activate
    'Stores the prompt value to page prompt
    Pages(j) = PromptValue
    'Modify the Sheetname so that it is using the prompt values
    CurrentSheet.Name = CurrentSheet.Name & " " & PromptValue
    Next j
    
    
    '--------------------------------------
    'After page prompt loop ends, the page prompt is all applied
    'The below submits/apply the query to OBIEE
    'Retrieves the results
    obiee.EditPagePrompts Empty, Pages
    'After retrieval, the columns should auto fit as a simple format
    ActiveSheet.Cells.EntireColumn.AutoFit
    'The sheetname was initialized with N + i
    'It will be removed here
    RemoveWSInit = Len(CurrentSheet.Name) - Len(i) - 2
    CurrentSheet.Name = Right(CurrentSheet.Name, RemoveWSInit)
    
    
    '--------------------------------------
    'Goes back to TOC worksheet
    'Inserts the hyperlink to the newly generated report
    TOCSheet.Activate
    ActiveSheet.Range("A2").Activate
    HypTemp = "[" & TempWorkbook & "]'" & CurrentSheet.Name & "'!A1"
    ActiveCell.Offset(i, 0).FormulaR1C1 = "=HYPERLINK(""" & HypTemp & """,""" & CurrentSheet.Name & """)"
    'Autofit Columns for TOC
    ActiveSheet.Cells.EntireColumn.AutoFit
    Next i
    
    
    '--------------------------------------
    'Deletes the PromptSheet without askings Yes or No
    Application.DisplayAlerts = False
    InfoWorkbook.Activate
    PromptSheet.Activate
    PromptSheet.Delete
    Application.DisplayAlerts = True
    InfoSheet.Activate
    Application.ScreenUpdating = True
    
    
    '--------------------------------------
    'Message Box and tells user that the report has been generated.
    MsgBox (TempWorkbook & " Generated")
    End Sub
    
    
    

     

    Congratulations on finishing your Smart View BI VBA! Easy peasy, right? Since this is not an Excel VBA tutorial, I will not go through individual coding, but the comments in the coding are self-explanatory. In the following, I will explain individual OBIEE VBA extension functions that you can use to modify the script.

     

    Oracle Smart View BI Extension Functions

     

    In our example, you might have already noticed that I said lines 12 and 13 are compulsory. Actually, we are declaring IBIReport as a variable and using SmartViewOBIEEAutomation class to use the extensions. So if OBIEE extension functions are needed, you must declare this.

     

    There are 10 OBIEE functions; I will provide the representative feature from the Smart View User Interface.

     

    1. InsertView (Official Oracle Documentation Reference)         

      This is the most frequently used OBIEE function. When used, it provides the same context as the insert button found in the Smart View panel.

      From our previous example, insert view is the function used to insert Answer tables. In fact, we can use the same function to insert the compound view. In the Smart View panel, choose Compound View 1 and click Insert All Views.

      image012.jpg
                Figure 12


      In the MultiSheet Report example, Table View was used. In the Smart View panel, this would be choosing Table 1 and clicking Insert.

      image013.jpg
                Figure 13


      The result would be the same as the VBA obiee.InsertView function.

      image014.png

      Figure 14


      Other features of this function can be viewed in the official document. Such features include Insert As Excel Table, Insert As Excel Pivot and Insert Chart.

      With the use of VBA, a report prompt can also be made use intensively through InsertView function.

    2. EditPrompts (Official Oracle Documentation Reference)         

      This function modifies Report Prompt values. In Answers, Report Prompts are set in the Prompts tab.
      image015.png

      Figure 15


      In Smart View, you can Edit Prompts value through the Oracle BI EE ribbon.

      image016.png
                Figure 16
               
    3. EditPagePrompts (Official Oracle Documentation Reference)

      The EditPagePrompts function, used previously in the MultiSheet Reporting example, is used to modify Table View Table Prompts or Pivot View Page Prompts. However, currently Chart View Chart Prompts are not supported.
      image017.png

      Figure 17
              

     

    1. GetPagePrompts (Official Oracle Documentation Reference)         

      The GetPagePrompts feature returns Objects that are present in the Table Prompt or Page Prompt. It will return the column name in an array. If we insert the Untitled Table View 1 into a new sheet and create a command button, the following code can be used to return the column names:

       

      Private Sub cmdGetPagePrompts_Click()
      Dim obiee As IBIReport
      Set obiee = New Smart ViewOBIEEAutomation
      Dim dims() As String
      Dim pageSelections() As String
      obiee.GetPagePrompts Empty, dims, pageSelections
      MsgBox (dims(0))
      MsgBox (dims(1))
      End Sub
      
      
      

      image018.png

      Figure 18

    2. DeleteView (Official Oracle Documentation Reference)   
           

      Delete the View from the worksheet. The selected view will be deleted using this function: obiee.DeleteView Empty.
             

      However, you must first select the View, which means using Sheets.Range("A1").Select before performing the function.

      This is the same as the Delete button on the Oracle BI EE ribbon.

      image019.png
      Figure 19

    3. AnalysisProperties (Official Oracle Documentation Reference)

      Viewing the properties of the analysis can be done through the Smart View panel.
      image020.png

      Figure 20


      Using VBA functions, AnalysisProperties can be used to check and display properties.

    4. DirProperties (Official Oracle Documentation Reference)

      Similar to Analysis Properties, DirProperties is used to check and display Folder Properties.
      image021.png

      Figure 21

    5. InvokeMenu (Official Oracle Documentation Reference

      InvokeMenu calls the object directly in the ribbon. Options to call are:

       

      MenuID
      View DesignerViewDesigner
      Publish ViewPublishView
      RefreshRefresh
      Edit PromptsEditPrompts
      Edit Page PromptsEditPagePrompts
      CopyCopyView
      PastePasteView
      DeleteDeleteView
      Mask DataMaskView
      Mask Document DataMaskDocumentView


      obiee.InvokeMenu "MaskDocumentView" can be made useful to Mask All Document Data before sharing reports in a shared folder. The majority of the features may not be the most useful since, apart from View Designer, Publish View, Refresh, Mask Data and Mask Document Data, they seem repetitive to others.

    6. CopyView (Official Oracle Documentation Reference)   

      An existing View can be copied to another workbook or worksheet when this function is used. Similar to DeleteView, the selected View will be copied.
      image022.png

      Figure 22

    7. PasteView (Official Oracle Documentation Reference

      The PasteView feature follows that of the CopyView feature since, after copying the selected view, PasteView will be used in another worksheet.
      image023.png

      Figure 23

     

    Conclusion

     

    All in all, Smart View OBIEE VBA creates a whole new way of Excel BI reporting, putting together the power of Excel and governed data discovery. In the end, learning VBA is much easier than learning any other languages. Strictly speaking, with the help of Excel Macro Recording, it doesn't take any effort to learn the skills. The Smart View BI Extensions let everyone have the freedom to manipulate their own personal dashboard and reporting system. Enjoy your road to success with Smart View OBIEE!

     

    About the Author

     

    With 10 years of experience in the BI field, Steve Yeung is a Project and Business Development Manager with Elufa Systems Limited, based in Hong Kong. He is also a Committee Member for Hong Kong Computer Society Business Intelligence & Big Data and speaks publicly on Oracle BI solutions. He holds Certified Specialist for Oracle Essbase and Oracle BIEE as well as Certified KPI Professional. Steve is also an instructor for Oracle University Oracle BI courses including OBIEE 11g and Essbase. His personal blog can be found on www.mondaybi.com.

     


    This article represents the expertise, findings, and opinion of the author. It has been published by Oracle in this space as part of a larger effort to encourage the exchange of such information within this Community, and to promote evaluation and commentary by peers. This article has not been reviewed by the relevant Oracle product team for compliance with Oracle's standards and practices, and its publication should not be interpreted as an endorsement by Oracle of the statements expressed therein.