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. 

OBIEE - selecting session variables using presntation variable

I believe you know everything about OBIEE variables: Session Variables, Repository Variables, Presentation Variables and Request Variables. If you don't, they are lots of good material about it. For example HERE , HERE or HERE.

I had a special need: we created several Session Variables with Row Wise initialization, so each had several values. We wanted the user to be able to select the Session Variable, to be used in the query, from a prompt.   

In my case in each Session Variables they were several dates and I wanted the user to be able to select in a Dashboard Prompt, one of the lists. (In normal condition I can make a regular "date between" filter. This was not such case.)

So assuming we have a regular Session Variable named "last7", I could create a filter in the criteria such as:

"Time"."Date" = VALUEOF(NQ_SESSION."last7")

But I want the user to be able to select: "last1" or "last7" or "last30". What can we do?

A business partner I worked with, Yeal Uziel from Vision BI, had a great idea: pass the session variable name using Presentation Variable.

So we created a Presentation Variable named LastN:

 As you probably remember calling this presentation variable syntax is:@{LastN}. Or with default value:@{LastN}{"last7"}.

So what we did was combining the 2.  We took the Session Variable filter:

"Time"."Date" = VALUEOF(NQ_SESSION."last7") 

and added the presentation variable instead:

"Time"."Date" = VALUEOF(NQ_SESSION.@{LastN}{"last7"})

 And thats it. It's working. Now user can select the Session Variables in the prompt!

OBIEE 11.1.1.7 - Where are all my Right Click options?

When working in OBIEE 11 you can use Right Clicks of the mouse for various action.

At the analysis level you control it in the Analysis properties -> Interactions.

In OBIEE 11.1.1.7 the default of the list is rather restrictive. 

I guess you don't want to change it on the report level, each time. So to set a different default, you will find in the System Administrators Guide, chapter "19.3.4 Manually Configuring for Interactions in Views". There you learn you can set different defaults in the instanceconfig.xml file (located at MWHOME/instances/instance1/config/OracleBIPresentationServicesComponent/coreapplication_obips1)

The bellow are the definitions with the default values.
Those are the default values if you omit the definition from instanceconfig.xml:

<ServerInstance>
   <Analysis>
<InteractionProperties>
<InteractionPropertyAddRemoveValues>false</InteractionPropertyAddRemoveValues>
<InteractionPropertyCalcItemOperations>false</InteractionPropertyCalcItemOperations>
<InteractionPropertyDrill>true</InteractionPropertyDrill>
<InteractionPropertyGroupOperations>false</InteractionPropertyGroupOperations>
<InteractionPropertyInclExclColumns>true</InteractionPropertyInclExclColumns>
<InteractionPropertyMoveColumns>true</InteractionPropertyMoveColumns>
<InteractionPropertyRunningSum>false</InteractionPropertyRunningSum>
<InteractionPropertyShowHideSubTotal>false</InteractionPropertyShowHideSubTotal>
<InteractionPropertySortColumns>true</InteractionPropertySortColumns>
<InteractionPropertyHideColumns>false</InteractionPropertyHideColumns>
 </InteractionProperties>
   </Analysis>
</ServerInstance>

You might want to change some/all to true (as I did):

Don't forget to restart the server.

By the way, the InteractionProperties are not new in 11.1.1.7.
In 11.1.1.6, for example, there was a shorter list (the Hide Columns is new in 11.1.1.7), with all defaults as true. This is not the case in 11.1.1.7.

Upgrading to OBIEE 11.1.1.7

If you want a great post about upgrading to 11.1.1.7 from earlier 11.1.1.X release you can find a post by Robin Moffatt at RittmanMead site HERE.

There is one exception: If you have a Simple Installation (like I did) you should read what Deva wrote HERE.

Just 2 remarks about Deva's post:

1. The actual guide is in the link to the doc at the beginning of the post.
2. In the last step of the upgrade, first run the wlst.cmd and then enter the upgradeOpss... after you fixed it to your system.

I followed Deva's post and now have an upgraded 11.1.1.7.

Good Luck.

OBIEE 11.1.1.7 New Features

Following are new features in OBIEE 11.1.1.7 as appears in the documents.

Before we start, my personal opinions: 

1. Oracle is  doing additional integration steps towards the "one big Oracle BI": Better integration between Dashboards and BI Publisher, better integration with Hyperion tools (Essbase tools in OBIEE installation, Smart View integration) and initial steps of integrating Endeca (search in catalog using Endeca out of the box and BI Publisher working with Endeca as a source).   The future seems interesting.

2. Oracle want to help the "dumb user": Default BI Composer working by default and wizards in Graphics.

3.The Multitanancy (described in the System Admin. Guide), is a big thing in the current tendency of cloud computers and Software as a Service.
4. All the features I wanted in prompts are added in this release.
5. If you can't beat them, join them... A big step for better work with Excel: Smart View, Dashboard export and real Excel export. (See here)

From Certification Matrix we learn they are new sources and new versions of certified data sources:

New Certified Data Sources:

• Apache Hive 0.9.x
• MySQL 5.5.14+

New versions of Data Sources:

• IBM DB2 10, IBM DB2 for z/OS 9.1 (and not only DB2 9.1, 9.5, 9.7 as usual).
• Microsoft SQL Server 2008R2 (not only 2005, 2008)
• Aster Datadabase 5.0 (and not only 4.6.2)

From the Documentation we learn about many new features:

New installation Features:

• Installing Oracle Business Intelligence on IBM WebSphere - Oracle WebLogic Server is the primary platform for Oracle Fusion Middleware software components. In this release, when you perform a Software Only install of Oracle Business Intelligence you can select Oracle WebLogic Server or IBM WebSphere.
• Configuring Oracle Essbase Suite when Installing Oracle Business Intelligence - Oracle Essbase database provides multidimensional analysis, enabling rapid development of custom analytic and enterprise performance management applications. In this release, when you install Oracle Business Intelligence you can select to configure Oracle Essbase database and its associated tools.
• Installing BI Composer - When performing a new 11.1.1.7 BI installation, BI Composer is automatically installed and configured. (in previous 11 versions you needed to configure it.) 
• Smart View - Starting with Release 11.1.1.7, Oracle BI Add-in for Microsoft Office is replaced by Oracle Hyperion Smart View for Office (Smart View) as a comprehensive tool for accessing and integrating Oracle Business Intelligence and Enterprise Performance Management content from Microsoft Office products. Smart View provides a common Microsoft Office interface designed for Oracle Business Intelligence and Oracle Enterprise Performance Management.

 New UI Features:

 

• Recommended Visualizations Feature for Creating Views - In this release, when you create a view, you can choose a recommended view type based on the data in your analysis and, optionally, what you want to use it for (for example, to analyze trends). You can choose a suggested "Best Visualization" as well as a "Recommended Subtype". Or you can instead choose the "Recommended Visualization" option, for which you specify your intent for the new view. For example, will the view compare values, compare percentages, visualize trends, or something else? From a list of suggested view types that is ordered with the best option at the top, you can then choose your preferred view type.
• Breadcrumbs - Breadcrumbs have been added to help users understand their current location within Oracle BI content and the path that they have used to navigate Oracle BI content.  Breadcrumbs display at the bottom of the page, and users can click a breadcrumb or the breadcrumb overflow button to navigate to a specific location on their breadcrumb trail.
• Enhancement to Dashboards - This release provides the following enhancements to dashboards:
• The ability to create custom print layouts for high-fidelity printing of dashboard pages (Using integration with BI Publisher).
• The new default style is FusionFX.
• The addition of the Freeze Column option to the Column Properties menu. This option allows you to freeze a column at an edge (top or left) of a dashboard layout.
• The addition of the following options that allow you to control the position and size of columns and sections: 
• Size option in the Additional Formatting Options area of the "Section Properties dialog" and the "Column Properties dialog"
• Page Size option in the "Dashboard Properties dialog"

• The ability to export an entire dashboard or a single dashboard page to Microsoft Excel 2007+ (I heard many request in that direction. See picture on the right).
• The addition of options to replace an analysis on a dashboard and to return to a dashboard from the Analysis editor:
• The Replace analysis in <dashboard name> option was added to the Save As dialog. This option displays when the user is viewing a dashboard, clicks an embedded analysis' Analyze or Edit link, edits the analysis, and selects Save As to rename and save the analysis. The Replace analysis in <dashboard name> option allows the designer to choose to include the modified analysis on the dashboard.
• The Return to <dashboard name> option was added to the Analysis editor. This option displays in the Analysis editor when the user is viewing a dashboard and clicks an embedded analysis' Analyze or Edit link. The designer can click this option to easily move from the Analysis editor to the dashboard.
• Enhancements to Graphs - In this release graphs have been enhanced in various ways.
• New graph types include:
• Waterfall graph. A waterfall graph lets you visualize how a value increases or decreases sequentially and cumulatively. An initial value is summed with subsequent values (both negative and positive changes or deltas) to arrive at an end value, focusing your attention on how each measure contributes to the whole.
• Stacked graph as a new subtype to the area graph. A stacked graph is useful for comparing the proportional contributions within a category. A stacked graph displays the relative value that each part contributes to the whole.
• 100% stacked graph as subtypes to the bar graph and the area graph. A 100% stacked graph, like the stacked graph subtype, lets you compare the parts to the whole. But while the stacked graph shows cumulative total in the units of the measure, the 100% stacked graph always shows units as percentages of the total, and the axis scale is always zero to 100 percent.
• Zoom to data range. This option lets the system evaluate the range of values on an axis, and choose appropriate minimum and maximum values for the scale. This is useful when graphing a set of large values that are all within a small percentage of each other.
• Hide sliders in graph views that listen to master-detail events. For graphs in master-detail relationships, if you want to simplify the appearance of an analysis or dashboard, you can hide the slider that is created to accommodate detail columns.
• Enhancements to Views - This release includes enhancements to various view types, including those in the following list:
• A new view type named performance tile. This view type displays a single aggregate measure value in a manner that is both visually simple and prominent.
• Action links in trellis views. In simple trellis views, action links can now be used on inner graphs per unit, including the context of the outer edges. Action links can also be used in legends and in axis labels. In advanced trellis views, action links can be used in microcharts, where the microchart functions as a single unit (such as an aggregate number), including the context of the outer edges.
• For map views:
• Vary the width of a line by measure to accentuate a feature.
• Link a map view as a detail in a master-detail relationship.
• Auto Correct option. A map view error can occur for various reasons. If the issue appears to be related to missing layers, maps, or styles, then it might be possible to correct the map by replacing the missing map view components with similar items that exist in the spatial metadata.
• Legend and formatting highlighting.
• Null suppression at view and analysis levels. You can set null suppression options at the view level (which overrides the analysis level) for data views including: table, pivot table, trellis (both simple and advanced), graph, gauge, and funnel views when the entire row or column contains nulls.
• Ability to include or exclude calculated items and groups at the view level for columns and column headers. Specifically, two new options have been added: Include Custom Member and Exclude Custom Member. These interactions are available for tables, pivot tables, and trellises.
• For tables, pivot tables, and advanced trellises, the ability to:
• Specify the method to be used to browse data — either scrolling or paging controls. For more information, see "Table Properties dialog: Style tab", "Pivot Table Properties dialog" and "Trellis Properties dialog: General tab".
• Resize rows and columns.
• Row count. If your table or pivot table view contains a grand total or subtotal on the row edge, the display totals, that is the grand total and subtotals, are now included in the Rows per Page count for content paging. In prior releases, if you had the Rows per Page count set to 10 for example, the table or pivot table would display more than 10 records if display totals were shown in the view results.
• For right-click interactions, the ability to specify whether the Hide Columns interaction is available at runtime.
• Enhancements to Prompts - This release includes enhancements to prompts, including those in the following list:
• Enhancement to the SQL Results prompt option. If you are working with double columns, you can now write the SQL statement so that filtering is done on code values rather than display values.
• Enhancement to the prompt constraint option. The prompts designer can now limit a column prompt value list by more than one column.
• Enhancements to the parameters for prompted links.
• New Menu Options for Exporting Views and Results - Excel 2007+ and Powerpoint 2007+ options for exporting views and results.
• Total Member Placement for Hierarchical Columns - This release includes an enhancement that allows placement of total members on a hierarchy.
• Browse Catalog Search Results by Object Attributes - This release provides the capability to use full-text search to find catalog objects and then filter the search results by attributes. This search is available when the administrator integrates Oracle BI Enterprise Edition with Oracle Endeca Server. After the full-text search results are returned, the Catalog area displays a list of matching  items, and the Search pane displays the search results grouped by attribute (that is, Type, Name, Path, and Created By).
• Navigate from the Total or Grand Total in a Table or Pivot Table - If an analysis contains a total or grand total and the associated attribute or hierarchical column contains an action link or a conditional action link, the action link (or conditional action link) is applied to both the column and the total or grand total.
• New BI Desktop Tools Available for Download - This release adds the following tools to the list of BI desktop tools that are available for download:
• Oracle Hyperion Smart View for Office
• Oracle Hyperion Financial Reporting Studio (if Essbase is installed)
• Oracle Essbase Studio Console (if Essbase is installed)
• Oracle Essbase Administrative Services Console (if Essbase is installed)
• Enhancement to Favorites - This release provides the capability to organize your favorites from the Category Tree area and the Selected Category area in the Manage Favorites dialog. In previous releases, you could organize favorites from the Category Tree area, only.
• Integration of Oracle BI EE with Oracle Enterprise Performance Management System - With this release, Oracle BI EE is integrated with Enterprise Performance Management Workspace.

New Features for Oracle BI Mobile:

This section describes new features for Oracle BI EE 11g Release 1 (11.1.1.7). With this release, there is a brand-new application to install from the Apple App Store.

This release provides end-user enhancements in the following areas:

• Maximize views with double-tap gesture. With this new feature, you can access on your iPad more data—even within dense dashboards—without the restrictions of the smaller form factor. You expand an individual view by double-tapping on it. The individual view then displays within the full screen of the iPad. This maximizing of views can be done on views accessed within a dashboard, and also when viewing an analysis independent of a dashboard.
• New view and graph types (described above): Performance tiles, Waterfall graphs, 100% stacked graph as subtypes to the bar graph and the area graph, Stacked graph as a new subtype to the area graph,Fixed headers.
• Changing between landscape and portrait orientation while viewing Oracle BI content. See "Switching Screen Orientation Between Landscape and Portrait."
• Breadcrumbs. You can see the navigation path you have taken while moving through the catalog as a trail of breadcrumbs (near the top of the screen by the Back arrow). In this way, you can see your current location and how you got there, then decide whether to use the Back arrow to navigate in reverse, or to use the Home icon to quickly return to the Home screen. 
•  Security toolkit. The Oracle Business Intelligence Mobile Security Toolkit provides the ability to generate a signed version of the Oracle BI Mobile HD application. The toolkit includes the instructions and necessary content to build this application making use of Apple's Xcode and the IOS SDKs. The Oracle BI Mobile Security Toolkit will be updated on a regular basis in order to synchronize with the Oracle BI Mobile HD application available on the Apple App store. 
•  Viewing of Oracle BI Publisher reports in dashboards. (Bug in previous version.)

New metadata repository features:

• Changing the Repository Password From the Command Line -You can now change the repository password from the command line using the obieerpdpwdchg utility.
• New Options to Enforce Consistent Versions in Multiuser Development Environments - You can now add options to the multiuser development option file to enforce Administration Tool, MUD protocol, and RPD version consistency among MUD developers. 
• New Utilities Available to Generate and Compare Logical Column Type Information - You can use the Administration Tool utilities Generate Logical Column Type Document and Compare Logical Column Types to generate a list of logical column types, and then compare it with logical column types in a subsequent version of the repository. You can also use the command-line utility biservergentypexml to generate the list of logical column types.
• Additional Database Support for Cardinality Estimates in Oracle BI Summary Advisor -The Prefer Optimizer Estimates feature, which improves performance by using cardinality estimates during the Summary Advisor process, is now available for Microsoft SQL Server and IBM DB2.
• Oracle BI Summary Advisor Measure Subset Recommendations - Oracle BI Summary Advisor now recommends only aggregates that contain specific measures that are both present in the analyzed query workload, and that can optimize the query workload if aggregates are created.
• Model Check Manager Enhancements - Model Check Manager now runs parallel queries against the database for better performance. In addition, you can now check models from the command line using the validaterpd utility with the -L option.
• Access to Apache Hadoop Data Sources - Oracle BI EE now supports Apache Hadoop as a data source.
• Support for Multi-Source Session Variables - Oracle BI EE now supports session variables that can be populated from multiple data sources and retain values from all source systems.
• NUMERIC Data Type Support for Oracle Database and TimesTen - You can now change a configuration setting to enable NUMERIC data type support for Oracle Database and TimesTen.
• Ability to Map Flex Object Changes Using the biserverextender Utility - You can now use the biserverextender utility to import flex object changes from ADF data sources and map them to the Business Model and Mapping layer and Presentation layer.
• Support for Servlet Communication Between the Oracle BI Server and Oracle OLAP - The Oracle BI Server now uses a servlet for communication with Oracle OLAP data sources, rather than relying on the JavaHost service.

New Privileges - The following new privileges were added to the Presentation Services Administration page:

• Access to Export
• User Population - Can List Application Roles
• Access to Permissions Dialog
• Add to snapshot briefing book
• Download Entire Dashboard To Excel
• Download Single Dashboard Page To Excel
• Add/Edit Performance TileView

New system administration features:

• Support for Oracle Endeca Server - You can configure Oracle Endeca Server as a search engine for full-text searching.
• Support for Multitenancy - You can configure Oracle BI EE so that it supports the use of multiple tenants.
• Integration of Oracle BI EE with Essbase - Essbase functionality is closely integrated with Oracle BI EE.
• New Configuration Required for Performance Tiles - To specify the scaling factor that accompanies a number in a performance tile view, you must manually edit the localedefinitions file, as described in Section 15.2.1.5, "Specifying the Scaling of Numbers in Performance Tiles."
• New Configuration Element for Dashboards - A new configuration setting is available to enable the ability to export dashboard pages to Oracle BI Publisher.
• New Configuration Element and New Default Values for Interactions in Views - A new configuration setting, InteractionPropertyHideColumns, is available to specify the default setting for the Hide Columns option in the Analysis Properties dialog: Interactions tab. In addition, only InteractionPropertyDrill,InteractionPropertyInclExclColumns, InteractionPropertyMoveColumns, and InteractionPropertySortColumns are set to true by default.

Calling the Oracle Business Intelligence Metadata Web Service Asynchronously

The Oracle Business Intelligence Metadata Web Service provides asynchronous and synchronous calls to Oracle BI Server stored procedures. You use these procedures to obtain information about the metadata and to modify the metadata.

New features for report designers in Oracle BI Publisher:

• Connect Directly to Oracle BIEE Subject Areas to Create Reports -
• You can now create BI Publisher reports that use a direct connection to an Oracle BI server subject area without having to build an additional data model in BI Publisher!!!
• Excel Template Builder Enhancements - The Excel Template Builder now supports automatic insertion of fields and repeating groups removing the requirement to manually assign defined names to cells. You can also connect directly to the BI Publisher server to download sample data, create new reports in the catalog, and upload templates from your Excel session.
• Layout Editor Enhancements -Enhancements added to the layout editor are:
• Time series axis formatting
• Hide axis option
• Independent axis formatting
• Enhanced Create Report Guide -The Create Report guide is enhanced to assist report designers with the process of creating a report from selecting the data model to configuring the report layout.
• PDF to PCL Conversion for Embedding PCL Commands in RTF Templates
• BI Publisher now provides a PDF to PCL converter. Using this utility, you can embed PCL commands in an RTF template and, after generating PDF output, the delivery manager converts the PDF to PCL before sending it to the printer to enable support for PCL printers. This feature is provided to support check printing.
• Support for PDF 1.7 Specification
• In versions of BI Publisher earlier than 11.1.1.7, the PDF utilities required that all PDF input documents be PDF version 1.4. Starting with this release users can pass PDF documents of later versions to the BI Publisher PDF utilities. The instructions in Chapter 7, "Creating PDF Templates" have been revised, removing this requirement.

New features for administrators in Oracle BI Publisher:

• Integration with Oracle Endeca - BI Publisher now supports integration with Oracle Endeca as a data source for reports.
•  PDF to PCL Converter - BI Publisher now provides a PDF to PCL converter to enable the printing of PDF output to a PCL printer. This enables the embedding of PCL commands in RTF templates that are executed by the printer when the document is printed. For example, the ability to switch to a specific font cartridge.
• Support for Private Data Sources - Data model developers can now create and manage private JDBC and OLAP data source connections for use in SQL or MDX data sets without having to depend on Administrators. However, Administrator users can still view, modify, and delete private data source connections, as well as extend access to other users.

New features for data model developers in Oracle BI Publisher:

• MDX Query Builder - You can now use the MDX Query Builder to build a MDX query by selecting OLAP cube dimension members for Column, Row, Page, & slicer (for Point of View) axes. This feature enables you to build member selections in the MDX query by using specific members, relations such as children and descendants, and layers such as levels and generations.
• Support for Local XML File Data Source - You can now use a locally stored XML or shared XML file as data source for data sets. This file can then be refreshed on demand from the data model definition.
• Support for CSV File as a Data Source -You can now use a locally stored CSV or shared CSV file as data source for data sets. This file can then be refreshed on demand from the data model definition.
• Support for Endeca as a Data Source -You can now use Endeca as a data source.
• Support for Private Data Sources - You can now use private JDBC or ODBC data source connections to create a data set with a BI Publisher data model.
• Enhancements for Sample Data Usability - You can now view and save data model results on the new View tab without browser dependencies. These improvements are intended to make working with sample data in BI Publisher easier and more consistent.

New features for report consumers in Oracle BI Publisher:

• Enhancements to Event-Driven Schedules - Now when you define an event-driven schedule, you can also set a retry interval to have BI Publisher automatically re-execute the trigger to test for the condition to run the report. Two new fields have been added to support this feature: Retry Limit and Pause Time. Using these fields you can define the number of times for BI Publisher tocheck the condition and the time interval to wait between attempts.
• New Parameter Type: Flexfields (for Oracle E-Business Suite Users) - For Oracle E-Business Suite users, support for the flexfield parameter type has been added in this release. BI Publisher's report viewer can now integrate with flexfield definitions in Oracle E-Business Suite to enable you to select flexfield segment value ranges to pass to your report.