Transcription

Moving fromEssbase Spreadsheet Add-in toOracle Hyperion Smart View forOffice, Fusion EditionAn Oracle White PaperJuly, 2012

NOTE:This paper assumes the reader has a basic understanding of Smart View, since thispaper focuses on features available in the latest releases of Smart View.Reference to future plan is intended to outline our general product direction. It isintended for information purposes only, and may not be incorporated into anycontract. It is not a commitment to deliver any material, code, or functionality, andshould not be relied upon in making purchasing decisions. The development,release, and timing of any features or functionality described for Oracle’s productsremains at the sole discretion of Oracle.Moving from Essbase Spreadsheet Add-in to Oracle Hyperion Smart View for Office, Fusion EditionPage 2

Moving from Essbase Spreadsheet Add-in toOracle Hyperion Smart View for Office,Fusion EditionNOTE: . 2Introduction . 5Smart View Usage Scenarios . 5Ad Hoc Analysis . 5Pre-defined Form Interaction . 6Pre-created Content Access . 6Reporting. 6Functions. 6Reasons to Move to Smart View. 6Statement of Direction – Essbase Spreadsheet Add-in . 7Planning for the Move . 7Platform Support . 7Essbase and Provider Services . 7Understanding the New Features . 8Re-using Essbase Spreadsheet Add-in Content (without VBA) . 8Converting Essbase Spreadsheet Add-in VBA . 8User Training . 8Feature Highlights . 9POV Toggle . 9Formula Preservation . 10Formula Fill on Zoom Operations . 11Sheet Level Options . 12Member Name and Alias on Rows . 12Resolve Alias Name. 13Zoom Options . 14Submit without Refresh . 14Duplicate Member Name . 14Member Information. 15Range-Based Operations . 15Multi-Cell Based Operations . 16Retain Excel Formatting . 16Butterfly Report . 18Substitution Variable Cell Function . 18Linked Reporting Objects . 19Cascade . 20Smart Query. 20Conclusion . 21Moving from Essbase Spreadsheet Add-in to Oracle Hyperion Smart View for Office, Fusion EditionPage 3

Moving from Essbase Spreadsheet Add-in to Oracle Hyperion Smart View for Office, Fusion EditionPage 4

Moving fromEssbase Spreadsheet Add-in to Oracle Hyperion Smart View for Office, Fusion EditionINTRODUCTIONOracle Hyperion Smart View for Office, Fusion Edition (Smart View) provides acommon Microsoft Office interface for the Oracle Enterprise PerformanceManagement (EPM) suite of products along with Oracle Business Intelligence(BI) data sources. Using Smart View, you can view, import, manipulate, distributeand share data in Microsoft Excel, Word and PowerPoint interfaces.Smart View provides the promise of Officeintegration today.Release 11.1.2.1.102 of Smart View added major features to make it compelling forEssbase Spreadsheet Add-in users to move to Smart View. Smart View, unlikeEssbase Spreadsheet Add-in, is an integrated office add-in that works across theMicrosoft Office Suite and not just Excel. The usage scenarios below outline theareas of usage for Smart View, which is the strategic Office integration add-in forOracle EPM and Oracle BI.This paper provides information on the capabilities of Smart View that have beenintroduced keeping in mind the users of and the functionality that existed inEssbase Spreadsheet Add-in. As such the intent is to provide guidance to users asthey move into the world of Smart View.Note: Screenshots in this document are based on Office 2007 although SmartView is supported for other Office versions.SMART VIEW USAGE SCENARIOSThe Office environment is one of the predominant interfaces that power EPM andBI users. Smart View usage can be classified as described below.Ad Hoc AnalysisAd hoc analysis lets Excel users interactively investigate the data contained in thesource(s), where they “slice and dice” the data. They may start with templates(such as East Cola Sales) or a blank sheet where they begin shaping andaltering the grids of data as they work. Typically, users retrieve the data fromOracle EPM sources—such as Essbase, Planning, Profitability or FinancialManagement—using mouse clicks or drag-and-drop. Free form analysis is avariant of ad hoc analysis where users can type in member names from adimension directly on a spreadsheet and refresh the data.Moving from Essbase Spreadsheet Add-in to Oracle Hyperion Smart View for Office, Fusion EditionPage 5

Pre-defined Form InteractionEPM application users who execute predefined input or reporting forms findSmart View a convenient way of completing tasks within Office. Such users areplanners, consolidators and others who want to work in Excel for a consistentexperience compared to a web application or to tie other spreadsheet-basedmodels into their process. For example, customers use Smart View for Planning toincorporate data that is still housed in spreadsheet- and workbook-based models.Pre-created Content AccessAnother area of use is for importing pre-created content—for example, charts orgrids— from Reporting & Analysis products to PowerPoint, Word, or Excel. Theimported content can be refreshed from the Office environment.ReportingReporting is another dimension of Smart View usage which leverages thecapabilities of EPM and Oracle BI data retrievals. Once the data is availablewithin Office, you can create reports as needed based on a combination of datasources. For example, Planning and Financial Management data could be used tocompare actual to budget. The ability to compare multiple scenarios, for differentperiods, products, locations, etc., enables more complex reports. The power ofOffice can be used to create Reports in the Office environment, which can berefreshed as needed.Functions“When accessing EPM sources, end users liketo see the same type and quality of chartacross all Office products.”Users can customize and automate common tasks by using Visual Basic forApplications (VBA) functions in Smart View. Functions are meant for advancedusers who know exactly what they need (for example, Actual profit for month ofJanuary for Eastern Region for all products) and want to create customapplications. These users typically build VB Applications and incorporate SmartView functions to get relevant information. Functions also allow combiningmultiple data sources on a single grid. For example, it is possible to displayEssbase and Planning data on the same worksheet using functions.The new release of Smart View allows usersaccessing EPM sources from an Officeenvironment to render the same type andquality of charts across Office products - Excel,Outlined above were the general capabilities of Smart View. The next few sectionsspecifically focus on various aspects of moving from the Essbase SpreadsheetAdd-in to Smart View.Word, or PowerPoint.REASONS TO MOVE TO SMART VIEWSmart View is the Strategic Office Add-in for Oracle EPM and Oracle BIproducts including Oracle Essbase. Smart View is vastly improved from theOracle Essbase Spreadsheet Add-in many ways. Here are some reasons toconsider for moving from Essbase Spreadsheet Add-in to Smart View: Improved feature functionality over Essbase Spreadsheet Add-in Single add-in for multiple products as opposed to only Essbase Rich functionality specific to the data source (Essbase, Planning etc.) Support for Word and PowerPoint 64-bit Office support Support for newer versions of Office and Windows O/S’sMoving from Essbase Spreadsheet Add-in to Oracle Hyperion Smart View for Office, Fusion EditionPage 6

Improved architecture keeping pace with newer trends in Office andAnalysis HTTP(s) makes access possible via internet/intranet and is the standardprotocol for modern applicationsSTATEMENT OF DIRECTION – ESSBASE SPREADSHEET ADD-INEssbase Spreadsheet Add-in will be in maintenance mode starting January, 2013.As such, platform updates for Essbase Spreadsheet Add-in will no longer be done,which means newer versions of Office or Windows will not be supported. Newfeatures will only be added to Smart View and not the Essbase Spreadsheet add-in.Releases prior to 1st January, 2013 should be considered terminal releases for theEssbase Spreadsheet Add-in.Official Oracle Essbase Spreadsheet Add-in statement of secure/km/DocumentDisplay.jspx?id 1466700.1PLANNING FOR THE MOVEMoving from Essbase Spreadsheet Add-in to Smart View involves some planning.There are various aspects to consider in order to make this a successful transition.Outlined below are the areas that you will need to be informed about orimplement prior to the move.Platform SupportSmart View is supported for Microsoft Office 2007 and 2010. At the time ofwriting this paper Release 11.1.2.2.000 of Smart View is available and it supportsthe 64-bit version of Microsoft Office. For exact version support and relateddetails, please refer to the support .htmlEssbase and Provider ServicesIn order to take advantage of the new features added to Smart View 11.1.2.1.102(and higher), it is required that Essbase (Essbase Server) and Oracle HyperionProvider Services also be release 11.1.2.1.102 (or higher). Please make sure thatnecessary upgrades to the new version have been completed by the Essbaseadministrator before trying out the new features using Smart View. While SmartView can connect to older versions of Essbase (9.3.3 and up), most of the featuresintroduced in 11.1.2.1.102 will not be available unless Essbase and HyperionProvider Services are upgraded as well.Note: Smart View accesses Essbase data sources, via a middle tier. HyperionProvider Services is commonly referred to as a provider or as APS (AnalyticProvider Services).Moving from Essbase Spreadsheet Add-in to Oracle Hyperion Smart View for Office, Fusion EditionPage 7

Understanding the New FeaturesUnderstanding the new features is very important in order to either use SmartView or guide others who are moving from Essbase Spreadsheet Add-in. The newfeatures are explained in the feature highlights section below.Re-using Essbase Spreadsheet Add-in Content (without VBA)By and large, workbooks created in Essbase Spreadsheet Add-in should be usablein Smart View without any changes. The spreadsheet needs to be connected to theright data source and refreshed. Thereafter, any Smart View operations should bepossible on those workbooks. Remember, the appropriate sheet options should beapplied as they will not be applied automatically in Smart View. While there is noway to test all combinations and spread sheets, 99% of spreadsheets (withoutVBA) can be refreshed in Smart View without any changes on the grid itself. It ispossible that the spreadsheet has some grid combinations rarely used (most usersare unaware of those), which may have to be re-created in Smart View based onsupported features or functionality.Converting Essbase Spreadsheet Add-in VBAIt is common for Essbase Spreadsheet Add-in users to create and use Visual BasicApplications. This will involve redesign and rewrite of functions or applications towork with Smart View. Prior to going dow