essbaselabs

I get a lot of questions about Smart View and one that comes up a lot is how to create a multi-grid sheet.  I thought it would be good to share a short tutorial on the topic.  

Had to create a quick video on Smart View OBIEE integration and thought it would be useful to share.

At KScope 2015 I did a session on using Smart View with OBIEE and I though it would be good to share the bulk of the content.

The following is based on the session ‘Smart View with OBIEE’ presented at KScope 2105.  

http://kscope15.com/component/seminar/seminarslist#Smart%20View%20with%20OBIEE

For this document it is assumed that the reader has a basic understanding of Smart View and how to connect to Oracle Business Intelligence using Smart View.  For help with Smart View visit the following page.

http://docs.oracle.com/cd/E63606_01/index.htm

Overview

Smart View connected to Oracle Business Intelligence is a powerful combination of access to data while working with familiar tools (Microsoft Office).   For this session we will focus on some key use cases learning how and when to leverage each of the following for success:

Presentation of Data and Visualizations

Data and Visualizations in Documents

Analysis with Excel

Template Reporting with Excel

 Data Extracts in Excel

Let’s explore each use case!

Presentation of Data and Visualizations

The key tool we all use for presentations is PowerPoint and in the past when we wanted to present our enterprise data we would spend a bunch of time copying and pasting the data or ‘snapping’ screenshots to add the much desired data and visualizations to our presentations.  Now we can leverage Smart View to create a live link to our enterprise data in Oracle Business Intelligence.  With this live link we can refresh data making sure we are always viewing one version of the truth and we can leverage the visualizations we already have in our Oracle Business Intelligence dashboards and reports. Here is an example of a live presentation:

Note that in the presentation you are looking at live, refreshed data.  Also based on the views and options selected these are native office charts and tables. The presentation was build based on visualizations and data in existing reports and dashboards.  Here are the steps:

Open a connection to the Oracle Business Intelligence server where the visualizations and data exist.  In this example we used ‘Private Connections’ and selected the OBIEE connection we already had setup.

Browse to the view you want to insert.  For dashboard pages you can import all views and for reports you can select the view you want to import specifically.  Notice the various view options that appear in the panel when you select an object.  All actions are also available via a right click.

Insert the view.  Locate the view in the Smart View Panel, right click and select ‘Insert’.  Notice the visualization will be added as a native chart or table depending on the view selected.  Each view will have other options like ‘Insert as Image’ or ‘Insert as Pivot Table’ depending on the view type.

Edit the view options and formatting.  Each view has a set of properties that you can access from the ‘Document Contents’ panel and when refreshing view you have a few options. In this case we are going to leave the default of ‘Refresh Data’ this way we can modify the native chart and just refresh the data later.  Other options like ‘Replace View’ and ‘Do Not Refresh’ are also available.  At this point we can use standard Microsoft Office tools to edit the view (a chart in this case).

Save the presentation and repeat for other visualizations or data.

At any time this presentation can be refreshed and furthermore it is portable.  Meaning all the necessary metadata is stored securely in the document so we can email or share this document like we would any other PowerPoint presentation.  If another user opens it they will be prompted for their login as long as they have Smart View installed, if not they will see all the content but it is not refreshable.  You can ship the presentation ‘as is’ or you can ‘Mask’ the data and the user is forced to refresh the presentation to see the data. This is a great security feature that allows us to share presentations with enterprise data knowing it will not cause any unnecessary information leakage.  Use the Smart View ribbons to see the key features available in PowerPoint. Note that this is available in all Office tools (like Word and Excel) that support consuming Oracle Business Intelligence data with Smart View.

Data and Visualizations in Documents

Like PowerPoint data and visualizations from Oracle Business Intelligence can inserted in formal documents, whitepapers, or anything you can create with Microsoft Word.  For instance, imagine you wanted create a whitepaper with data about a seasonal trend you are seeing in your business, this is easy with Smart View, and you follow about the same steps as the last section.

Open a connection to the Oracle Business Intelligence server where the visualizations and data exist.  In this example we used ‘Private Connections’ and selected the OBIEE connection we already had setup.

Browse to the view you want to insert.  For dashboard pages you can import all views and for reports you can select the view you want to import specifically.  Notice the various view options that appear in the panel when you select an object.  All actions are also available via a right click.  Also note that the view will be inserted at the current position of the cursor in the document.

Insert the view.  Locate the view in the Smart View Panel, right click and select ‘Insert’.  Notice the visualization will be added as a native chart or table depending on the view selected.  Each view will have other options like ‘Insert as Image’ or ‘Insert as Pivot Table’ depending on the view type. 

Edit the view options and formatting.  Each view has a set of properties that you can access from the ‘Document Contents’ panel and when refreshing view you have a few options.  In this case we are going to leave the default of ‘Refresh Data’ this way we can modify the native chart and just refresh the data later.  Other options like ‘Replace View’ and ‘Do Not Refresh’ are also available.  At this point we can use standard Microsoft Office tools to edit the view (a chart in this case). 

Save the document and repeat for other visualizations or data.

 Below is an example of the finished document along with a formatted table.

Like PowerPoint or any Smart View enabled document this can be refreshed and it is portable.  Updating and sharing documents with live data has never been easier!

Analysis with Excel

Microsoft Excel is a powerful data analysis tool and you can leverage Smart View connected to enterprise data in Oracle Business Intelligence to enhance the analysis experience.  For those familiar with other Smart View providers, like Essbase, please do not confuse this with Ad hoc Analysis.  When connected to Oracle Business Intelligence the main driver for analysis are reports and dashboards that already exist.  You can create new OBI analysis right in Excel but this much more structured than traditional ad hoc analysis.  Here are the key analysis concepts. 

Import Table Data

When importing data you can import it in a few different table options that are likely familiar. Oracle Business Intelligence table objects can be imported as a standard table, an Excel table, or as an Excel pivot table. 

Standard tables are the default option and this simply fills the cells (starting at the selected cell) with the data as it is laid out on the Oracle Business Intelligence object.  In this case transferable formatting is converted to native Excel formatting.  This is supported for both Oracle Business Intelligence tables and pivots.

To do this browse the catalog in the Smart View panel for the report object to be imported then right click or select from the action menu ‘Insert’.  This will insert the table starting in the selected cell.

Excel Tables work like standard table except they are automatically defined as an excel table object allowing for simple formatting, sorting, and more. To do this browse the catalog in the Smart View panel for the report object to be imported then right click or select from the action menu ‘Insert as Excel Table’.

The Excel Pivot Table option will import the data and convert it to an Excel pivot table.  This is useful for large tables with lots of dimensions.  This option only applies to Oracle Business Intelligence pivots. 

To do this browse the catalog in the Smart View panel for the report object to be imported then right click or select from the action menu ‘Insert as Excel Pivot Table’

 Import Charts

Though not interactive the ability to import existing charts form Oracle Business Intelligence reports can be just a powerful as it is in the previous use cases for Microsoft Word and PowerPoint.

To do this browse the catalog in the Smart View panel for the chart object to be imported then right click or select from the action menu ‘Insert’.  Optionally you can also import it as an image.  This is useful if you want an exact representation of the chart.

New Analysis using the View Designer

Another option is to browse the Oracle Business Intelligence subject areas and create a new analysis from scratch.  To do this right-click the Catalog Root node in the catalog tree, and then select ‘Create New View’.  Also from the Oracle BI EE ribbon, click the ‘View Designer' button. 

You must already be connected to a view in the catalog tree to enable the Oracle BI EE ribbon.

Once the view designer is open, in the left pane, expand a subject area and the folders to view the columns that you want to work with. Drag columns from the expanded subject area tree and drop them in the layout area.  The edges in the layout area vary depending on the selections that you made in the View Type.  There are number of other design options and view creation can be complex.  For more details visit the Smart View help for the View Designer.

http://docs.oracle.com/cd/E63606_01/epm.11125/smart_view_user/frameset.htm?view_designer_layout.html

Template Reporting with Excel

Leveraging Microsoft Excel tools and formulas along with the ability to refresh enterprise data from Oracle Business Intelligence in a simple table format as described above allows for creative template based reporting.  

The key to this is to have a ‘Data’ sheet where a live refreshable link has been created to an Oracle Business Intelligence report object.  This is described in the section above and can be achieved with the following steps.

Browse the catalog in the Smart View panel for the report object to be imported then right click or select from the action menu ‘Insert’.

This will insert the table starting in the selected cell. Rename this tab to something creative like ‘Data’.  This is your live data link.

Now insert another sheet and use Microsoft Excel functionality to create a formatted report.  You can refresh the data in this highly formatted report without impacting the formatting or having to redo any data entry.  At this point it is all about Microsoft Excel functionality.  Use features like formulas, conditional formatting, and charts, to enhance the enterprise data you are sourcing from Oracle Business Intelligence.

Data Extracts in Excel

This topic is a bit controversial because you can hit some real performance limitations depending on the data volume you are attempting to extract.  Basically be realistic, if you are trying to export 100’s of thousands of rows you will hit some physical limitations and likely be disappointed.  Most databases have tools that should be used for large data extracts and Oracle Smart View connecting to Oracle Business Intelligence is not one of them.  With that there are many use cases for data extracts that makes sense using Smart View but here are some things to consider:

Data Volume – As stated above big volumes can take a long time, anything over 100,000 rows starts to get cumbersome and should be avoided.  This will depend on many factors; like client memory, server query performance, formatting on the table object, and more; therefore there is no exact number as a physical limit.  In short be smart!

Why Data Dump – When extracting data consider ‘why’ there is a need to dump the data into Excel.  Maybe this is about self-service BI; maybe IT and business are not working together.  This sort of need often points to an issue in the current reports or dashboards available and solving that should, at least, be explored.

Once all this is out of the way and you are ready to do a data extract, it can be accomplished with the following steps:

Select or create a table object in Oracle Business Intelligence that meets the needs for the extract.  Keep it simple and limit formatting and special conditions as much as possible.

Browse the catalog in the Smart View panel for the report object mentioned above then right click or select from the action menu ‘Insert’

This will insert the table starting in the selected cell.  What you do with this data is up to you.

Summary

As you can tell by now, Smart View connected to Oracle Business Intelligence is a powerful combination of access to data while working with familiar tools (Microsoft Office).  Just try to keep a few things in mind before using Smart View with Oracle Business Intelligence or unleashing it on to your end users.

Understand the Use Cases – The key to success is making sure the right tool is used to solve the right use case.  

We had some really good sessions on Smart View at KScope 2014.  First we gave an product update and a glimpse at some new features in the Sunday Symposium (sorry not sharable) then Monday we had a great session on multiple data sources.  The session was in a packed room and we had some great follow-up conservations throughout the conference. Here is Monday's presentation.

Smart View News

August 2013

Release 11.1.2.5.000

11.1.2.5.000 is the latest release of Smart View and was made generally available by Oracle starting August, 2013. This release of Smart View while having made other improvements supports Office 2013.

Quick look at areas of interest for this release: 

Simplified Installer

Modifying Connections

Document Contents Pane Show/Hide

Forms - Submit without Refresh

Platform Update

Duplicate Variable Name support

VBA Updates

Simplified Installer

Starting with this release there is a single installer for 64-bit and 32-bit versions of Microsoft Office. Upgrades can be done without uninstall or deleting files. Installer automatically detects and displays in language based on Windows Region and Language dialog box.

Download Made Easy

The latest version of Smart View is available on Oracle Technology Network (OTN).

While OTN has the latest version (11.1.2.5.000 as of August 2013), My Oracle Support has prior versions as well.

  Note: OTN will only maintain the latest version of Smart View.  So in the future when a new release of Smart View comes out, 11.1.2.5.000 will be replaced by the newer version.  However 11.1.2.5.000 will continue to be available in MOS.

Modifying Connections

Changing connections to point to different data sources for existing sheets is now possible using Document Contents pane. It is easy point to a production system after having developed a sheet (or a workbook) with a connection to the test environment. Alternately if a sheet is already in use against a production system and you wish to try out the newer changes in a test environment, just point to the test system and try out and make sure everything works as expected.

The connection change can be applied to:

· All Sheets (or)

· Specific Sheets

  Document Contents Pane

Changing connections to point to different data sources for existing sheets is now possible using Document Contents pane. It is easy point to prod after having developed a sheet with a connection to the test environment. This connection change can be applied to

  Refresh Not Required Before Submitting Data on Forms

You are no longer required to refresh the contents of a form before submitting data after a POV change.

· Open a Form

· Change POV as needed

· Update Cells as needed

· Submit (only affected cells get updated on the server)

Keep in mind that when you are working in a form and you click Submit Data, you are actually writing data back to the latest POV selected in the POV toolbar. Oracle however recommends performing a Refresh whenever you make changes to the POV. The refresh updates the data on the sheet to reflect the latest POV change.

Platform Update

Starting with 11.1.2.5.000 Smart View added support for:

1. Windows 8

2. Microsoft Office 2013  

  Smart View works on Surface Pro tablets.

Change to Display of Duplicate Variable Names

Starting with this release Smart View allows display of fully qualified variable names when variable names are duplicated. This helps to identify variables defined at the global, application, and database levels.

New VBA Functions

HypHideRibbonMenu() HypHideRibbonMenuReset()

Essentially allows hiding or un-hiding items on the Ribbon. Check Developers Guide for more details.

A friendly suggestion!

Does Smart View support Office 365?

Smart View is ONLY supported for Desktop versions of Excel, Word, PowerPoint and Outlook included in any Office 365 plan and which are locally installed on any supported Windows Operating System.

Office 365 is a subscription-based online office and software plus services suite which offers access to various services and software built around the Microsoft Office platform. Other services include Lync web conferencing, Exchange online, Sharepoint, SkyDrive and Skype. The licensing determines what component or service is included as part of a bundle.  Many of these components do not relate to Smart View directly.

While users frequently ask if Smart View supports office 365, most Smart View users are actually trying to find out if Smart View supports Office web apps, a lightweight version of Office intended for use and collaboration over the web. 

I have proudly been working with Essbase for over 20 years, starting with the Spreadsheet Add-In and now working with the Smart View Add-In. Anyone who has worked with software knows that there is not a perfect piece of code. Decisions are made for a variety of reasons and there are always idiosyncrasies in the way the software behaves. I would like to discuss one of those idiosyncrasies in the Excel Add-Ins (both the Spreadsheet Add-In and Smart View Add-In).

First, a little bit of background. Both Add-Ins are now based upon the Essbase Grid API. It is a very powerful API that is uniquely designed for a spreadsheet experience. It is a very free form, syntax less form of querying that has been widely popular from the day it was introduced into the market. The grid API allows a user to place member names in almost any location and it will figure out the correct placement of those names and retrieve the data.  The grid API does well when the queries are perfectly symmetric and the member names can be centered (first grid below).

Now, the problem and its simple solution. There are times when stacking dimensions in the column and not using the “Repeat Member Names” option causes the grid API to misinterpret the correct definition of the member ranges. The incorrect shape of the member ranges leads to unexpected data getting pulled into the grid. This is especially true if you only anchor the first cell of a column range (second grid below).  The simple solution to this issue is to place a member name in the first column and the last column of each column member range thus “anchoring” the column definitions (third grid below).

Aside from the Sunday symposiums I had 2 great sessions at ODTUG KScope 2013.  Rooms where packed, I saw a bunch of new faces, and there was some great interactivity.

The first session was Leveraging Office and Smart View to Create a Data Entry Experience.  This session took users though the steps necessary for creating advanced input templates for distribution to end users. The templates will included data from multiple sources and the examples shown leveraged new features in Office 2010 and 2013 along with Excel features that have been around for many versions. (charting, functions, sparkline's, outline, and formatting) It touched on the use of VBA to automate common tasks and we will go through some of the challenges of template distribution and maintenance.

Here is a link to the presentation:

http://prezi.com/x4vpbmnu45l6/?utm_campaign=share&utm_medium=copy

The second was Smart View’s New Features; in this session I talked about all the new features available in the latest versions of Smart View. We will discussed general client features like sheet level options, re-designed function builder, platform support, as well as provider specific features like in-cell Point of View and improved Planning Smart Lists. This session also contained details on, and showed demos of new extensions; most notably the OBI (Oracle Business Intelligence) extension.

Here is a link to the presentation:

http://prezi.com/kvcvhe2qfcrg/?utm_campaign=share&utm_medium=copy

Smart View News

May 2013

Release 11.1.2.3.000 was made generally available by Oracle at the end of April, 2013. While the preceding release (11.1.2.2.310) was primarily targeted towards Oracle Business Intelligence Enterprise Edition users, the latest release added some new features that need to be called out.

HTTP Access for Shared Connection

Starting with the 11.1.2.2.310 release, when connecting to Oracle Hyperion Smart View for Office data sources, shared connection information could be added to an XML file, stored on a user's local drive, and used to connect to shared providers. In Release 11.1.2.3, the shared connection XML file can now be stored on a web server and accessed by all users that you, as the Smart View administrator, designate.

Storing connection information in the XML file allows administrators to combine Oracle Business Intelligence Enterprise Edition provider with other EPM providers such as Planning, Essbase etc. It may be useful for a user to have a local copy but generally it might be more useful if the administrator stores the file on an HTTP server granting access to the users.

You can name the file as long as the entries follow the standards, which are simple and easy to follow. Look up the documentation for more information.

Here is how the entries appear in the XML file:

Once the XML file is ready and posted, users need to point to the file in the Shared Connections URL in the options dialog.

Smart View Help

Help is now hosted online on a Oracle website. When user launches help, requests are redirected to this location.

There may be reasons to host help either on the local drive for Section 508 accessibility purposes. It is also possible that some organizations may have restricted web access in which case Smart View help can be hosted on the intranet. In such cases Help files can be downloaded from My Oracle Support and hosted appropriately.

See “Installing and Configuring Help Locally or on a Web Server” in the Oracle Hyperion Smart View for Office User's Guide for more information.

Function Builder Enhancements

Function Builder usability is improved with two new enhancements in this release. Prior to this release, users could enter references to single cells for connection, label, data/text, or variable arguments, as shown here.

  Cell references to two cells

Starting with Release 11.1.2.3, users can also enter references to two contiguous or noncontiguous cells for dimension and member name references. Here is an example showing dimension and member names in contiguous cells.

   This is in addition to the behavior that existed before. This allows member name and dimensions to be on separate cells. This is the first improvement.

The second improvement is related to Function validation. A new Validate button in the Function Arguments dialog box allows users to see and correct errors immediately, before executing a function.

The types of errors that may be noted are:

· Connection offline or invalid

· Invalid selection

· Member name is invalid or not matching selected alias

· Dimension name is invalid or not matching selected alias

· Incomplete Dimension#Member combination

· Generic “Error” in cases of missing quotation marks or other minor syntax errors

Because you can see and correct errors on-the-fly, the validation feature saves you time in troubleshooting any problems in your function.

New VBA Function: HypUIConnect

HypUIConnect() prompts the user with the Connect to Data Source dialog box when the user name and password is not provided. It does not prompt if already connected.

Example

Public Declare PtrSafe Function HypUIConnect Lib "HsAddin" (ByVal vtSheetName As

Variant, ByVal vtUserName As Variant, ByVal vtPassword As Variant, ByVal vtFriendlyName

As Variant) As Long

HypUIConnect(Empty, UserName, Password, "My Connection")

Function Builder Enhancements

New Planning Extension

The new Hyperion Planning Admin Extension for Smart View allows Oracle Hyperion Planning administrator users to import and work with metadata in Smart View. With this extension, Planning administrators can use Oracle Hyperion Smart View for Office applications to perform these tasks:

· Add, edit and import existing Oracle Hyperion Planning dimensions and members, including moving members and designating members as shared in Smart View

OK that title may be a bit over the top but with the release of OBIEE 11.1.1.7, Smart View users can now view, import, manipulate, distribute and share OBIEE data in Microsoft Excel, Word and PowerPoint.  Smart View users now share a single client (Smart View release 11.1.2.2.310 or newer) for all application providers and this allows users to access and integrate EPM and BI content within Microsoft Office products.  Users are also able to leverage the shared capabilities of Smart View connected to an OBI provider; including a common approach to connectivity, security, credential management, scripting, development tools and the ability to federate data across multiple sources.

Some key features when accessing OBIEE with Smart View include:

Self-service modification of BI Views – Modifying the chart type, chart layout and the design properties associated with the visualizations. This ensures that end users can control the formatting and representation based on business need or personal preference without the involvement of IT or BI report designers.

Interactive analytic capabilities – Users can make use of the full breadth of BI View interactivity at the Office level. This includes page and report prompts accessible at the time of import and subsequently within the Office component. The level of interactivity ensures end users can build highly complex visual and table based analyses within a page, document or workbook.

Leverage customizations - The Import of BI content can leverage the customizations and view standards used within an OBIEE environment. All view designed modifications such as conditional formatting, background colors or data configuration is automatically translated to the Office environment.

Policy based import and lineage tracking – Smart View for OBIEE provides the ability set preferences on how content is refreshed and updated. Through policy settings on a per view basis, users can decide if a view can be updated based on data only, metadata changes or none at all. In all cases users can control the behavior ensuring investments in local customization can be retained or the standards from OBIEE and take precedence. This capability also allows users to track the location of imported content.

Native Microsoft Power Point features – Smart View enables the full support and maintenance of BI Analyses in Power Point. Features unique to Power Point include the ability to apply native templates and leverage core design features. This will allow end users to build and maintain a consistent presentation adhering to appropriate business standards.

Creation of New Analytics from with Microsoft Office – Smart View for OBIEE enables users to create new Views from all of the Microsoft Office components. This self-service reporting capability permits users to connect to the OBIEE semantic layer, select dimensions and measures, apply filters, page prompts, totals and either OBIEE or Office based analytic types. For example a business user who is comfortable working with native Microsoft Excel pivot charts can build a new BI analysis, save it as a native Excel pivot and leverage all of the capabilities that Microsoft Office has to offer including Pivot Charts. The resulting analysis can also be saved back to the OBIEE Web Catalog and shared through the standard dashboard or distributed reporting mechanisms.

So I hear a lot of really ‘generic’ questions and lately this one has been coming around a bunch.

“Our company is rolling out Office 365, does Smart View support it?”

This is a question that requires a full understanding of what Office 365 is and is not.  I found a great link on this topic and thought I would share a bit.

“We use the name "Office 365" for products that include features enabled over the internet, such as additional online storage with SkyDrive, Skype world minutes for home use, Lync web conferencing or Exchange Online hosted email for businesses. Most Office 365 plans also include the full-featured Office 2013 applications, which users can install across multiple computers and devices.”

http://office.microsoft.com/en-us/products/office-2010-frequently-asked-questions-FX102926087.aspx

It has been awhile for me to post so I thought it was a good time to get everyone up to speed on some of the latest news with Smart View.

Smart View 11.1.2.2.310 has been released.  This is the latest version and is recommended for just about any user regardless of server version as Smart View has great backwards compatibility.  You can download this version on Oracle Support or on OTN.

As always there are plenty of defects fixed and a few new features:

Defects -

http://docs.oracle.com/cd/E38438_01/epm.111223/readme/sv_11122310_readme.html#sv_11122310_readme_186

New Features -

http://docs.oracle.com/cd/E38438_01/epm.111223/readme/sv_11122310_readme.html#sv_11122310_readme_186

One feature to point out is Document Contents; it is a task pane in the Smart View Panel that provides a view of, and allows you to perform actions on the content existing in the current Office document.

The Document Contents pane displays Office objects in a tree format, allowing you to easily see and interact with the content in a workbook, presentation, or document, regardless of the provider or type of content and including content from extensions.

OK I am going to try out this video thing to show off a new feature in 11.1.2.2.300.  This is available when connecting to new versions of Essbase (11.1.2.1.102 and above) with the latest SV Client.