Analytics Software

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.

  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.

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)
      
       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:

  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.

   
         Figure 5

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

   
         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:

   
         Figure 7

   
         This will create a new module named SmartViewVBA.

   
         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.
        
         Figure 9

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

   
         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.
      
       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.

   
             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.

   
             Figure 13
   
   

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

   

   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.
   

   Figure 15

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

   
             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.
   

   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
   
   
   

   

   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.

   
   Figure 19
   
   
3. AnalysisProperties (Official Oracle Documentation Reference)
   
   

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

   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.
   

   Figure 21
   
   
5. InvokeMenu (Official Oracle Documentation Reference) 
   
   

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

   Menu	ID
   View Designer	ViewDesigner
   Publish View	PublishView
   Refresh	Refresh
   Edit Prompts	EditPrompts
   Edit Page Prompts	EditPagePrompts
   Copy	CopyView
   Paste	PasteView
   Delete	DeleteView
   Mask Data	MaskView
   Mask Document Data	MaskDocumentView

   
   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.
   

   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.
   

   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.