Patch Set Update: Oracle Smart View for Office 11.1.2.5.800

Hyperion Product Management recently advised the release of a Patch Set Update (PSU) for Oracle Smart View for Office 11.1.2.5.x

You can download this new version from here :) PATCH 28120145.

This document includes important information about this release of Oracle Smart View for Office. Review the Readme thoroughly before installing Smart View.

New Features:

1. New Essbase Expense Type Option.
2. Display Varying Attributes in Ad Hoc Grids.
3. Support for MDX Calculations for Aggregate Storage Databases.
4. New View Properties Button in Oracle BI EE Ribbon.
5. Enhanced Move Formatting Behavior.
6. Perform a Health Check On Your System

README:

Refer to the Readme files for information pertaining to the above requirements. The Readme file should also be consulted prior proceeding with the PSU implementation. This document contains important information that includes supported paths, implementing and configuration steps, list of new features and defects fixed, along with additional support information.

It is important to ensure that the requirements and support paths to this patch are met as outlined within the Readme file.

More Information:

Available Patch Sets and Patch Set Updates for Oracle Hyperion Smart View for Office (Doc ID 2220997.1)

Smart View Support Matrix and Compatibility FAQ (Doc ID 1923582.1)

Supported Paths to this Release

You can upgrade to Oracle Smart View for Office Release 11.1.2.5.800 from the following FROM 11.1.2.x TO 11.1.2.5.800.

Note: When upgrading from release 11.1.1.3.x and earlier, Oracle recommends uninstalling Smart View before installing release 11.1.2.5.800.

Defects Fixed in This Release

About Defects Fixed in This Release

The topics in this section include defects fixed in Release 11.1.2.5.800. To review the list of defects fixed between earlier releases, use the Defects Fixed Finder This tool enables you to identify the products you own and your current implementation release. With a single click, the tool quickly produces a customized report of fixed-defect descriptions with their associated platforms and patch numbers. This tool is available here.

General

• 17215221 -- On Microsoft Surface Pro tablets, in the Task List pane, the text now displays correctly in the Action Panel and in Task Details. Items in these areas are no longer squeezed together vertically, and the fonts are not cut off at the bottom of each item.
• 26199400 -- The Member Selection dialog now displays correctly when launched from the French Canadian version of Oracle Smart View for Office.
• 27148137 -- After inserting several reporting objects into a PowerPoint presentation, then performing a Cascade Across and selecting members, and then attempting a Refresh All, entering slide show mode may cause the POV to disappear and PowerPoint to stop responding.
• 27679149 -- The Member Selection dialog box is now editable in 4K high-resolution displays.

Essbase

• Bug 27405835 - After inserting a chart and a POV into a PowerPoint slide, and then changing the POV, both objects are refreshed when performing a Refresh.
• 27431964, 28135613 -- In sheets with formulas, a refresh no longer removes formulas or changes the sheet composition.
• 27560276, 27730355 -- After a Refresh, member names defined by formulas are not moved or overwritten.
• 27875032 -- Formulas in an ad hoc grid are now not moved to other cells when the sheet is refreshed.
• 27566388 -- For MDX queries, you can now specify a non-default port number when required. In the Manage and Execute Queries dialog, click Modify Connection , and in the Edit Connection - Application and Cube page of the wizard, select the Advanced Setup check box. In Server, specify the server and non-default port number in this format:
  <server_name>:<port_number>
  Then specify the Application name and Cube/Database name, and click Finish. Toggle the Change View button in the Manage and Execute Queries to view the MDX queries sorted server and non-default port number.
• 28119650 -- Oracle Analytics Cloud - Essbase only. Excel no longer stops working while executing a calculation containing runtime prompts.

Financial Management

• 25902276 -- In Excel worksheets, users are now able to scroll using the mouse wheel after connecting to Oracle Hyperion Financial Management and opening a new worksheet.
• 27450351 -- In an Oracle Managed Cloud Service environment, users will no longer see the message, 'The XML page cannot be displayed', in the login screen when opening a saved workbook and logging in to Financial Management from Oracle Smart View for Office.
• 27524993 -- In an SSO environment with Oracle Access Manager, after successfully signing in to Financial Management in Smart View using a private connection, the Login window no longer becomes blank.
• 27738748 -- When opening a saved workbook that uses a private connection to Financial Management, the error message, 'Failed to instantiate Planning Offline Provider -%x', is no longer displayed and users are able to connect.

Oracle Planning and Budgeting Cloud, Oracle Enterprise Planning and Budgeting Cloud

• 23265152 -- In an ad hoc grid that is based on a form that has Smart Lists, copying data cells and then pasting them no longer cause the pasted cell data to change from text to numeric.
• 25768064 -- In PowerPoint, the Document Content pane remains populated in the following scenario: Insert a chart and then, in the Document Contents pane, insert two reporting objects, a table and a POV. View the slide in slide show mode. Make a change to the POV and then exit slide show mode.
• 26885152 -- A Planning power user is now prompted when trying to save a Smart Form with the name of a form or Smart Form that already exists in Planning.
• 27272782 -- Formatting is no longer lost when refreshing an ad hoc sheet in a saved workbook.
• 27341510 -- In forms, after changing a member for the first time in the POV, the form and the POV retain the change.
• 27388571 -- In the Task list pane, rows no longer overlap.
• 27405666 -- After clearing the cell, you can now enter and submit the same value that was previously submitted.
• 27593442 - There is no longer a lengthy delay in transferring members from the Member Selection to the worksheet.
• 27608935 -- When the #NoData/Missing Label option is set to #NumericZero, "0" is being properly displayed and does not reset to #Missing when switching between sheets.
• 27678819 -- When cascading, the special character, "/", used in a member name is dropped from the name of resulting sheets while keeping the rest of the member name intact for each applicable sheet name.
• 27995997 -- Erroneous formulas and figures are no longer added to blank cells after performing a Refresh.

Supported Platforms

Oracle Smart View for Office 11.1.2.5.800 follows the platform requirements which are stated in the 11.1.2.x version of the Oracle Enterprise Performance Management System Certification Matrix. The topics in this section list platforms supported by Smart View that are in addition to those listed in the matrix, or platform support introduced in this release.

Microsoft Windows 10 Operating System Support

Oracle Smart View for Office 11.1.2.5.520 was the first Smart View release to be supported on Windows 10. Earlier versions of Smart View are not supported on Windows 10.

Fixing Smart View Display and Operations Issues in Windows 10

After a Windows update on some high-resolution displays, drag-and-drop may not work as expected, some controls may look blurry, and some operations may not work properly. For example, an "Invalid selection" error occurs when trying to drag and drop members from the POV.

To avoid the "Invalid selection" error and other display issues after a Windows update:

• Select the Windows 10 Start button, then Settings, then System, then Display, and then Advanced scaling settings.
• Under Fix scaling for apps, set Let Windows try to fix apps so they are not blurry to On.
• Restart Excel.

Drag-and-drop and other operations should work as expected, and the display of Smart View controls should be improved.

Microsoft .NET Framework Support

To install and use Oracle Smart View for Office release 11.1.2.5.600+, Microsoft .NET Framework 4.5 or higher is required.

Microsoft Office Support

Oracle Smart View for Office support for Microsoft Office 2016 began with the 11.1.2.5.520 release. Smart View versions earlier than 11.1.2.5.520 are not supported on Office 2016.

Smart View works on locally-installed Windows versions of Office applications, including Office 365 Pro Plus 2016 installed using Click-to-Run. For more information, see the Knowledge Article entitled Oracle Hyperion Smart View Support for Office 365 (Doc ID 1581197.1).

Windows, Office, and Smart View Locale Settings

18377748 -- On each Oracle Smart View for Office client machine, the following locale settings should match:

• Smart View language option.
• Microsoft Office language.
• Windows Regional Settings.
• Windows operating system

Running Smart View in a language other than the language of the machine's Windows operating system, system locale, or Office language, may result in incorrect data being returned. For example, if the Windows operating system, system locale, and Office language are English, and you run Smart View in Russian, reports or chart data may display incorrectly.

Browser Support

Mozilla Firefox

To use Oracle Smart View for Office 11.1.2.5.800 with Mozilla Firefox ESR 60 and later, you must install the Oracle Smart View for Office web extension for Firefox. You can get the extension by searching on "smartview" on the Firefox Add-ons download site or by going directly to this link. Click the + Add to Firefox button and follow the prompts to add the extension.

Google Chrome

Oracle Planning and Budgeting Cloud 17.06+ and Oracle Enterprise Planning and Budgeting Cloud 17.06+ support interoperability with Google Chrome and Oracle Smart View for Office 11.1.2.5.700+. In Oracle Planning and Budgeting Cloud or Oracle Enterprise Planning and Budgeting Cloud, you may select Open In Smart View to access the same data in Office. Conversely, you may launch Chrome from Smart View in order to access Oracle Planning and Budgeting Cloud or Oracle Enterprise Planning and Budgeting Cloud when performing drill-through operations.

To use Google Chrome with Smart View, install the Oracle Smart View for Office web extension from the Chrome Web Store. You can get the extension by searching on "smartview" in the Chrome Web Store or by going directly to this link. Click the + Add to Chrome button and follow the prompts to add the extension.

Mobile Device Support

Oracle Smart View for Office is supported on Microsoft Surface Pro tablets only. Smart View is not supported on any other tablet or smartphone.

Financial Management Journals Extension Support

The Oracle Journals for Financial Management extension works with Oracle Smart View for Office version 11.1.2.5.500 and later, and with Oracle Hyperion Financial Management 11.1.2.4.100 and later.

The Oracle Journals for Financial Management functionality introduced in Smart View 11.1.2.5.700 requires the following:

• Financial Management 11.1.2.4.204 with the 11.1.2.4.204 PSE 25575478
• Smart View 11.1.2.5.700+
• Oracle Journals for Financial Management extension 11.1.2.5.700+ (included with Smart View 11.1.2.5.700+)

Oracle Analytics Cloud Support

To use the Oracle Analytics Cloud-related features in Oracle Smart View for Office, you must have Oracle Analytics Cloud 17.3.2 or later.

Thank you.

Warm Regards,

SST.! 

PLANNING PASSWORD ENCRYPTION UTILITY

The documentation around the PasswordEncryption utility is very straightforward but I still wanted to make that post  for anyone that might not know that such a utility exists or might not know how to use it.

Here is what the Oracle documentation says about this utility:

When running Planning utilities that prompt for passwords, administrators can use an option to suppress password prompts, for example, when running utilities in batch mode. To enable suppressing password prompts, use the PasswordEncryption utility to create a file that stores a password in encrypted form. After the file is set up, you can run Planning utilities with the [-f:passwordFile] option as the first parameter in the command line to skip the password prompt and use the password from the encrypted file. Each password file contains one password, and is stored in the location that you specify when running the utility.

To encrypt a password to be used in Planning utilities:

On a Windows environment:

Go on the server where Planning is installed using remote desktop. Locate the PasswordEncryption.cmd. It should be under the planning1 folder where all the planning utilities are stored.

Then open the Command prompt. In my case utilities are on the E drive so once I switched to drive E, I just navigate to the planning utility folder E:\Oracle\Middleware\user_projects\epmsystem1\Planning\planning1

The syntax to start encrypting a password is: PasswordEncryption.cmd passwordfile. If you only specify the file name then the passwordfile will be created under the default folder which is planning1 folder. In my case, I want the Encryptedpassword file to be placed under E:\Oracle, so I will enter: PasswordEncryption.cmd E:\Oracle\Encryptedpassword.txt

Then press enter. When prompted, enter the password you wish to encrypt. While you typing the password nothing will be visible on the command prompt.

Once the password has been entered, press Enter again. You should get a successful message.

Finally go to the Planning utility folder (default folder) or the one you specified (E:\Oracle) to get your Encryptedpassword.txt file.

When opening the file, you should see the actual encrypted password.

On a Unix environment:

Go on the server where Planning is installed using Putty. If you don’t have Putty, just go online and download it. Locate the PasswordEncryption.sh. It should be under the planning1 folder where all the planning utilities are stored.

Use cd to navigate to "u11/Oracle/Middleware/user_projects/epmsystem1/Planning/planning1"

The syntax to start encrypting a password is: PasswordEncryption.sh passwordfile

If you only specify the file name then the passwordfile will be created under the planning1 folder. I want the Encryptedpassword file to be placed under u11/Oracle, so I will enter:
./PasswordEncryption.sh u11/Oracle/Encryptedpassword.txt

Note - Make sure you type ./ before calling the utility otherwise the utility won’t be executed.

Then press enter. When prompted, enter the password you wish to encrypt.

Once the password has been entered, press Enter again. You should get a successful message.

Finally go to the Planning utility folder or the one you specified to get your Encryptedpassword.txt file.

When opening the file, you should see the actual encrypted password. (to open the text file in putty, you have to use "vi" command - vi Encryptedpassword.txt )

Thank you.

Hope you like this post.

Best Regards,

SST.!

FRAGMENTATION

Fragmentation is unused disk space. Fragmentation is likely to occur with the following:

1. Read/write databases that users are constantly updating with data.
2. Databases that execute calculations around the clock.
3. Databases that frequently update and recalculate dense members.
4. Data loads that are poorly designed.
5. Databases that contain a significant number of Dynamic Calc and Store members.
6. Databases that use an isolation level of uncommitted access with commit block set to zero.

How can we measure fragmentation?

We can measure fragmentation using the Average Clustering Ratio or Average Fragmentation Quotient.

Average Clustering Ratio

The average clustering ratio database statistic indicates the fragmentation level of the data (.pag) files. The maximum value, 1, indicates no fragmentation.

Average Fragmentation Quotient

Any quotient above the high end of the range indicates that reducing fragmentation may help performance, with the following qualifications:

• The reported value of the Fragmentation Quotient is more accurate when there are no other write transactions running on the database.
• For databases less than 50 MB using the Direct I/O access mode, the fragmentation quotient tends to be high. A high fragmentation quotient does not necessarily indicate a need to reduce fragmentation, because the free space is created in 8 MB chunks and all of it might not get used right away.

How we can prevent and remove fragmentation?

We can prevent and remove fragmentation:

1. To prevent fragmentation, optimize data loads by sorting load records based upon sparse dimension members. For a comprehensive discussion of optimizing data load by grouping sparse members.
2. To remove fragmentation, perform an export of the database, delete all data in the database with CLEARDATA, and reload the export file.
3. To remove fragmentation, force a dense restructure of the database.

With Warm Regards

SST.!

ESSBASE OPTIMIZATION

Essbase Optimization

One of the reasons of Essbase optimization can be somewhat tricky, to put it mildly, is because Essbase cube performance is almost directly linked to the design of the cube, i.e. the dimensions, hierarchies and members in each dimension, stored vs. dynamic members, etc. This is unlike other (relational) systems where there are general technical items to check for optimization, which has nothing to do with the data in the system (except maybe for the amount of data).

In Essbase the data dictates the cube structure, and the way the cube storage works in blocks and indexes, and therefore every cube is different and has different performance implications. Also Essbase is unique in that just increasing hardware specifications (specifically memory and CPU power), while causing some improvements as a matter of course, will not cause as dramatic performance improvement as changing the cube design to be optimal for the data set used.

Please note this is applicable to block storage cubes.

The Essbase optimization main items checklist :

Block size

Large block size means bigger chunks of data to be pulled into memory with each read, but might also mean more of the data you need is in memory IF your operations are done mainly in-block. Generally we prefer smaller block sizes, but there is not a specific guide. In the Essbase Admin Guide they say blocks should be between 1-100Kb in size, but now a days with more memory on servers it can be larger. Blocks is all dependent on the actual data density in the cube. Do not be afraid to experiment with dense and sparse settings to get to the optimal block size, we have done numerous cubes with just one dimension as dense (typically a large account dimension), and cubes where neither the account nor time dimension is dense.

Block density

This gives an indication of the average percentage of each block which contains data. In general data is sparse, therefore a value over 1% is actually quite good. If your block density is over 5%, then your dense/sparse setting is generally spot-on. A large block with high density is OK, but large blocks with very low density (< 1%) not.

Cache settings

Never ever leave a cube with the default cache settings. Often a client complains about Essbase performance, and sure enough when I look at the cache settings it is the default settings. This is never enough (except for a very basic cube). Rule of thumb here is to see if you can get the entire index file into the cache, and make the data cache 3 times index cache, or at least some significant size. Also check that the cube statistics to see the hit ratio on index and data cache, this gives an indication what % of time the data being searched is found in memory. For index cache this should be as close to 1 as possible, for data cache as high as possible.

Outline dimension order

Remember the hourglass principle. This means order the dimensions in your outline as follows – first put the largest (in number of members) dense dimension, then the next largest dense dimension, and continue until the smallest dense dimension. Now put the smallest sparse dimension, then next smallest, and continue until the largest sparse dimension. Because of the way the Essbase calculator works, this arrangement optimizes number of passes through a cube. A variation of this which also seems to work well is the hourglass on a stick, where you put all non-aggregating sparse dimensions (i.e. years, verison, scenario) beneath the largest sparse dimension.

Commit Block settings

This controls how often blocks in memory are written to disk while busy loading or calculating a cube. You want to minimize disk writes, as this takes up a lot of processing time, so set this to be quite high. The default setting is 3000 blocks, if your block size is relatively small (< 10KB) make this much higher, 20000 to 50000. This setting alone can cause dramatic performance improvements specifically on Calc All operations and cube loads.

Use of FIX..ENDFIX in calc scripts or in BR’s

One of the most misunderstood and common mistakes in calc scripts is the usage of FIX..ENDFIX. Always FIX first on sparse dimensions only, and then within this FIX again on dense members, or use dense members in IF statements within the FIX statements. The reason for this is that if you FIX only on sparse members first, it filters on just specific blocks, which is faster than trying to fix within blocks (i.e. dense members).

Optimizing data loads

The best technique to make large data loads faster is to have the optimal order of dimensions in the source file, and to sort this optimally. Do this, order the fields in your source file (or SQL statement) by having as your first field your largest sparse dimension, your next field your next largest sparse dimension, and so on. So if you are using the hourglass dimension order, you data file should have dimensions listed from the bottom dimension upwards. Your dense dimensions should always be last, and if you have multiple data columns these should be dense dimension members. Then you should sort the data file in the same order, i.e. by largest sparse dimension, next largest sparse dimension, etc. This will cause blocks to be created and filled with data in sequence, making the data load faster and the cube less fragmented.

These are just the initial general optimization points which can cause huge performance improvements without too much effort,  generally these ones should handle 70% of our optimization issues.

With Warm Regards
SST.!

ABOUT ORACLE PBCS?

What is Oracle PBCS?

Oracle PBCS is an Oracle Cloud product called Planning Budgeting Cloud Service which has been recently renamed as Planning and Budgeting Cloud. PBCS is the first Hyperion EPM product on cloud and it is an application based on Oracle Hyperion Planning that supports enterprise-wide planning, budgeting, and forecasting in the cloud. PBCS’s core functionality is the same as Oracle Hyperion Planning on-premises.

Oracle Planning and Budgeting Cloud offers integrations with Microsoft Excel, Word, Outlook and PowerPoint using the “Smart View” plugin for planning and financial reports, this means that users can view planning data forms from within Microsoft Excel with the same functionality as the web-based interface. Both interfaces support ad-hoc analysis of data where row, column and page dimensions can be pivoted to create different views of the same data.

Oracle PBCS Pricing

Data centers or cloud servers where PBCS is hosted are owned and maintained by Oracle. Since there is no CAPEX infrastructure cost, virtually no learning curve and minimal IT resources required, the PBCS solution can be used by a company of any size. The cost structure for PBCS is a per user per month license.

Maintenance Costs

Software versions are always up-to-date and maintained by Oracle, allowing clients to the latest and greatest version without requiring any downtime to upgrade. Oracle provides test and production environments for every PBCS implementation. Oracle updates test environments first and then gives notice for when updates are planned, what the changes are and the issues that are resolved in the new version.

Loading Data

Financial Data Quality Management Enterprise Edition (FDMEE) is also part of the Planning and Budgeting Cloud and is used to load data to planning BSO (block storage) and ASO (aggregate storage) cubes. Planning data can be moved from BSO to ASO cubes and other reporting data can also be loaded to ASO cubes directly. Data in PBCS can also be loaded through EPM Automate, a utility that enables you to automate certain common administrative tasks.

Deploying Oracle PBCS

Oracle Planning and Budgeting Cloud comes with flexible deployment options. Clients can choose to deploy the planning application on the cloud or it can be used in tandem with on-premise Hyperion Planning applications. It is a great tool for clients who already have on-premise Hyperion Planning as they can use PBCS to develop and test new applications on the cloud before choosing to deploy it on-premises.

Thank you

SST.!

Frequent asked questions related to Hyperion Planning\ Essbase On-Prem vs On Cloud (PBCS).

How to migrate On-Prem Planning\Essbase applications to Oracle PBCS?

For customers with existing Hyperion Planning applications, the migration path from Hyperion Planning to PBCS is very easy. If a customer is on 11.1.2.3.500 or greater, it is a three-click process:

1. Use Lifecycle Management and export the application definition,
2. Upload through the automation utility in PBCS, and
3. Import it on the PBCS side. Earlier versions may require minor modifications to the XML files for the LCM packet, but we have yet to see a migration that takes more than a few hours.

There are also many part of your on-premises Hyperion Planning application that won't migrate and many files you'll need to adjust.

Does PBCS have the same functionality as Hyperion Planning?

It’s been a couple of year that PBCS is announced and couple years ahead of on prem Planning in different ways — some of the important one’s are.

The two applications have a complete different user interface. Hyperion Planning still uses Workspace, while all pods in the cloud no longer have Workspace. The simplified interface is all that's available on PBCS, which has significant advantages.

From an administrative perspective, the cloud environment is significantly easier to administer because you don't have to manage things like server-to-server point communications, logs and tuning, etc.

PBCS doesn’t have rules for data load, report script to export data and Maxl Automation scripts.

PBCS comes with EPM automate utilities to schedule processes. Data management to create load rules.

How do data loads and pushes occur?

PBCS comes with inbuilt data integration tools. If you are familiar with Oracle FDM or FDMEE, FDMEE is embedded directly within the PBCS application called data management.

Load data directly into PBCS using the web interface.

PBCS includes the EPM Automate Utility to schedule simple data loads or using data management rule. Users can run EPM automate from their local machines as well.

How do dimension builds occur?

There are different ways we can use load dimensions.

PBCS allows direct dimension maintenance inside of Smart View, if you have hierarchy definitions that live in an Excel file, you can import them directly through Smart View or apply them.

Another way is to load the .csv\text file directly into the web interface.

Users can use the EPM Automate Utility to upload the dimension build files.

Make change directly in create and manage – Dimensions.

Enterprise Data management cloud also supports PBCS and EPBCS formats and import metadata to EPM Cloud.

Cube need to be refreshed to apply changes. If refresh is required during day the application can be put into maintenance mode to ensure that users are all out, and changes applied to the database.

How many plan types are available in PBCS?

3 BSO, 3 ASO, and 1 consolidated plan type for reporting.

How to move\copy data between PBCS cubes?

Smart Push is the new feature which allows the data to be moved to the reporting cube almost immediately. You can move application data from:

• Block Storage cubes to aggregate storage cubes
• Block Storage cubes to block storage cubes

Export and import using text\csv files.

Use data management to move data between cubes.

How to connect to PBCS?

Smart View.

PBCS - URL (Oracle provides unique URL for your company to connect to PBCS)

EPM Automate utilities.

On Prem - FDMEE

What web browsers does PBCS support?

Firefox, Google Chrome, Internet Explorer, and Safari

Is it possible to use a spreadsheet located on the client machine to connect to the Cloud application?

Yes. Just like any On-Premise Planning application, PBCS can use Financial Reporting Studio and Smart View to perform both ad-hoc retrieves as well as perform Financial Reporting for those who want a better visual display of the data. 

How would we load ERP source data into PBCS?

FDMEE, which has replaced FDM, would be the best solution for providing an ERP flat file in addition to performing mappings and importing the source data into your PBCS application.

Can the "Planner" role carry out ad-hoc analysis in read-only mode in PBCS?

Yes, this is a common scenario. Users with read-only access have full capability to do ad-hoc reporting. 

How do we maintain security in PBCS regarding shared services?

PBCS security is not synced with MSAD via connection to the MSAD; it is either manually controlled or imported via .csv for the users who are later provisioned for PBCS. Provisioning is also not performed in Shared Services for PBCS. It is controlled in the Oracle Cloud Identity Console.

How long migration from on prem to cloud takes?

A common misconception about cloud migration is that it will be a one time trip. But the reality is that the process of migrating data infrastructure to the cloud should happen gradually. A successful migration should feel as seamless as possible to the organization, so work isn't disrupted. Usually it takes around 8 weeks for the end to end migration along with consideration of new features of PBCS and 2-3 weeks for data validation after migration.

Thank you.

SST.!