OBIEE - creating new SmartView connection

I talked about Smart View in a previous post: OBIEE 11.1.1.7 - The Excel Ready Version, including Smart View. So we know Smart View is Hyperion tool that enables you to create new reports and run existing reports/Analysis, directly from Excel.
We know you can download the 32bit Office version from OBIEE home page and 64bit version from Oracle.

I wanted to write this post specifically how to create Smart View connections to OBIEE. All 3 types of them.

I use here the new Smart View 11.1.2.3.0, since I already upgraded to OBIEE 11.1.1.7.1 as described here: Installing OBIEE patch set 11.1.1.7.1 on Windows
I'm not aware of major differences regarding this post.


When we are using Smart View with OBIEE, there are 3 types of connections to OBIEE we can create in Smart View:

1. Private

2. Shared Local

3.Shared on the Server

Lets review the 3 options:

Private Connection:

From Smart View ribbon, click Panel.

Select Private Connection.

Press the right arrow and Create New Connection.

Select Oracle BI EE.

Enter the URL http://servername:port/analytics/jbips

In my case http://bdahab-lap:9704/analytics/jbips

Enter the user / password.

Save it as Private Connection.

Now you can start working.

Press Connect. Enter Password if needed...

You can select anything from the connection to run.

After you do it, you can see the Oracle BI EE menu.

And create new reports with Smart View.  (I couldn't find any option to open this menu without opening an existing report first... Anyone?).

Next time we enter Excel, we can select Panel -> Private Connection again. 

This time we only need to use the previous connection from the pull down.

Shared Local Connection

Create an XML file. For example SmartView.XML. All we care for is the XML extension.

In the file I used the following content:

 <?xml version="1.0" encoding="UTF-8"?>
<res_GetProvisionedDataSources>
<Product providerType="ExtensionProvider" id="27EE9B0D-D5F5-42c9-AFC5-44FEA770C693"
name="OBI EE-11.1.1.7" displayVersion="version">
<Server name="Oracle BI, Fusion Edition" context="OBI:http://bdahab-lap:9704/analytics/jbips"></Server>
</Product>
</res_GetProvisionedDataSources>

• Replace bdahab-lap:9704 with your server name and port.
• Replace "Oracle BI, Fusion Edition" (Server Name), with the Name you want to see in the connection in Smart View.

Save the file.

Copy this file (SmartView.xml) to the bin directory of SmartView installation (other locations are possible). In my case it's C:\oracle\SmartView\bin.

Enter Excel, in Smart View ribbon select Options / advanced.

Update the Shared Connections URL to point to your XML file. In my case: C:/oracle/SmartView/bin/SmartView.XML
(Ensure that you use forward slashes (/) as separators in the file path)

Now you can use Smart View Shared local connection:
As usual Panel / Shared Connections

select your connection from the Pull Down and Enter password.

Shared Server Connection

Is very similar to Shared Local Connection. The only difference, is locating the XML file on a shared server. In OBIEE, the logical place is analyticRes, described here: analyticsRes - having your own place on the OBIEE. So I copied the same xml file as described  above (I copy / paste the same text, except the file name):

Create an XML file. For example SmartViewProviders.xml.


In the file I used the following content:

 <?xml version="1.0" encoding="UTF-8"?>
<res_GetProvisionedDataSources>
<Product providerType="ExtensionProvider" id="27EE9B0D-D5F5-42c9-AFC5-44FEA770C693"
name="OBI EE-11.1.1.7" displayVersion="version">
<Server name="Oracle BI, Fusion Edition" context="OBI:http://bdahab-lap:9704/analytics/jbips"></Server>
</Product>
</res_GetProvisionedDataSources>

• Replace bdahab-lap:9704 with your server name and port.
• Replace "Oracle BI, Fusion Edition" (Server Name), with the Name you want to see in the connection in Smart View.

Save the file.

Place the file in the analyticsRes directory. In my case I created SmartView directory in the analyticsRes directory (D:\or\MWHOME\instances\instance1\bifoundation\OracleBIPresentationServicesComponent\coreapplication_obips1\analyticsRes\ ) and placed the file there.

Now we have access to it using the URL: http://bdahab-lap:9704/analyticsRes/SmartView/SmartViewProviders.xml (pay attention to upper / lower case letters.)

Please note: when I tried to use the file SmartView.XML, I used in the previous section, it didn't work. It did work after I renamed the file to SmartViewProviders.xml . Why? Sorry, you should look for a deeper person.

Most of the materials here are from the Smart View Users guide. There you can find also possible settings of the XML file, to fit additional EPM products.

The Smart View 11.1.2.3 documentation is located here.

(we can get to Smart View docs from this EPM documentation page.)

OBIEE 11.1.1.7 - The Excel Ready Version, including SmartView

One of important features of OBIEE 11.1.1.7 is the drastic improvement in Excel integration. 

1. SmartView.
2. Real Excel file exports.
3. Export of Dashboards to Excel.

Lets Summarize OBIEE Office capabilities in 11.1.1.6 vs. 11.1.1.7 in a table:  

11.1.1.6	11.1.1.7
Office Add-in for Excel	SmartView for Excel
Office Add-in for Power Point	--No change --
Excel ready export	Real Excel format export
--	Export of Dashboards to Excel
--	SmartView in MS Word
BI Publisher Word Temple	--No change --
BI Publisher reports with local Excel	--No change --
BI Publisher Excel Template Builder	BI Publisher Excel Template Builder Enhancements

 
 Now lets see examples of the 3 points above:

SmartView

SmartView is Hyperion tool that enables you to create new reports and run existing reports/Analysis, directly from Excel..

From the Home page of OBIEE you can download SmartView

 Lets zoom in:

And install it (add-in to Excel).

Now you can run it from Excel Menu. 

To begin, from the Panel create a new connection. Select Oracle BI EE.

And Enter URL.

Here come a critical (not so well documented) part. The Smart View URL is your regular OBIEE URL with jbips/ addition. For example here is mine: http://bdahab-il:9704/analytics/jbips/

A better explanation how to create new connections for SmartView are here: OBIEE - creating Smart View connection. 

I will do just a basic example:
Once connected you can view the content of the BI Server and select content. For example a Dashboard Page:

You are asked in this case:

I selected all objects and the result is, all report one bellow the other (for several thousand rows, in this case).

 Once you are connected to BI Server additional option appears in the menu:

 And in this option, with View Designer, you can create things from scratch, by selecting Subject Area and assigning columns to Page/Section/Row/Column/Measure in several types of presentations:

So this is what I requested:

And this is the result:

Now you can actually work, add filters, redesign... Lots of thing, all of them later.

 Now I'll cover the other 2.

Real Excel Export

 Why is it important? 2 reasons:

1. You had issues when opening Excel exports on mobile devices in the past. There shouldn't be any problem any more.

 2. It will prevent many little "issues" that could happen in non real Excel format export.

Export of Dashboard to Excel

Here is an example:

I exported Entire Dashboard. And now have several sheets in Excel with all Dashboard pages:

Graphs are pictures but all the rest is real data. As you can see at the bottom, there are several sheets in the Excel.