Smart View - Query Govenors, Limits & Choices, Oh My!

This post is about Essbase, HFM & Planning queries run in Smart View.
Opinions on other data Providers in Smart View are protected by the innocent.

There used to be a simple solution to the way you were able to govern and or limit run-away queries and report grids in Smart View. As many of us know, users, all kinds of users, do not always know, how, when, from where or even sometimes why an application or cube was built in the first place. In that, the answer to finding data sometimes results in LARGE Smart View Ad-hoc queries-reports, etc.

With all that said, over time the settings and-or configurations that a Hyperion Administrator has to limit potential runaways from happening has changed. In many cases, in Essbase-Planning there was the simple ability to change the default setting of 5000 max rows (Office 2000-2003 to the Excel maximum row limit of 65,536 (sadly, I have that number memorized and here is the Wiki context, long version of why)) and in Excel 2007, 2010, 2013, & 2016 the maximum row limit is 1,048,576; no matter if you have a 32-bit or 64-bit version of Office.

For HFM, you are bound by time. Sounds profound! The setting for HFM is time bound (in minutes) and the default is set to 31 minutes (not sure why this was the precise number of minutes, maybe there was a time study done on the Finance functions of the world) on the server side, session timeout (web.xml).

For Essbase, it is no longer the case to simply limit rows & columns, as it is now deprecated, starting in Essbase version 11.1.2.4.008!

This means you can't simply change the APS setting anymore for Max rows & columns to let's say, 1M rows and 256 columns.

So, here are a few of your configuration options instead:

• SSPROCROWLIMIT: (Suppression REQUIRED! #Missing Rows) - this setting in Smart View applies to any grid or query (11.1.2.4 tech ref). Be careful with this setting as there are many 'notes' as to who wins when, why and how...

SSPROCROWLIMIT n

The default value is 250,000

• APS: (Smart View Only) - maxrows, maxcolumns only up until Essbase version 11.1.2.4.008 as mentioned above

• QUERYRESULTLIMIT: This applies to any MDX or Spreadsheet query
• QUERYGOVEXECTIME: Sets the maximum amount of time a query can use to retrieve and deliver information before the query is terminated (any query)

QRYGOVEXECTIME [appname [dbname]] n

• QUERYGOVEXECBLK:Sets the maximum number of blocks that a query can access before the query is terminated. This setting does not apply to aggregate storage databases (ASO), BSO only

 QRYGOVEXECBLK [appname [dbname]] n



Note: For the 'QUERY' settings, these place limits on queries, if any one of the limits are reached, it will prevent the query from being returned.

Lastly, if I didn't state this, I wouldn’t be able to sleep at night. Be careful kids, there are only a few settings out there for trying to set boundaries and limits for our runaways (queries that is). Always keep in mind, that some of the settings contradict or override others. Some settings only apply to certain database types. And there may, or may not, possibly be.. undocumented settings still lurking out there!

Smart View - Change Alias Table(s) VB

This can either be run as a macro or 'attached' to a button on a custom ribbon (below). 

The syntax allows a change the Alias table on the current active sheet, not only a specific sheetname explicitly named i.e. "Sheet1".
Using; sheetName = ActiveSheet.Name

 Custom Ribbon button call:

Option Explicit
'Callback for rxAlias onAction
Sub rxAlias_change(control As IRibbonControl, id As String, index As Integer)
    With ActiveSheet.Cells.Interior
   
    Select Case index
Case 0
        Sample_SetALIASnone
       
Case 1
        Sample_SetALIASDefault
       

    End Select
    End With

End Sub


*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*
*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*

Change Alias sub-routine:

 'Set ALIAS table(s)
*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*
Public Declare Function HypSetAliasTable Lib "HsAddin.dll" (ByVal vtSheetName As Variant, ByVal vtAliasTableName As Variant) As Long

Sub rx_SetALIASDefault(control As IRibbonControl)
'Set ALIAS table = "Default"

    sts = HypSetAliasTable(sheetName = ActiveSheet.Name, "Default")

End Sub


Sub rx_SetALIASnone(control As IRibbonControl)
'Set ALIAS table = "none"

    sts = HypSetAliasTable(sheetName = ActiveSheet.Name, "none")

End Sub

Smart View Custom Excel Formulas - Gen or Level

Have you ever wanted to know what the generation or level of a particular member is with ease? Quickly? On the fly? From Excel even? Without a refresh?

Use the Smart View VB toolkit and "retrieve" the generation or level of a particular member or members with an Excel Function!


*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*
Generation Formula:

Public Function GetGeneration(strDimension As Range) As String
Attribute GetGeneration.VB_Description = ":: This function ""=GetGeneration(cell)"" allows you to reference a cell which IS A VALID MEMBER of the outline and returns the GENERATION NUMBER of that member ::"""
         
    Dim strDimG As String
    strDimG = CStr(strDimension.Value)
    If strDimension <> "" Then
   
X = HypFindMember(Empty, strDimG, "Default", dimName, aliasName, GenName, levelName)

    GetGeneration = (GenName)

    End If
    
End Function

*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*
Level Formula:  
Public Function GetLevel(strDimension As Range) As String
Attribute GetLevel.VB_Description = ":: This function ""=GetLevel(cell)"" allows you to reference a cell which IS A VALID MEMBER of the outline and returns the LEVEL NUMBER of that member ::"""
         
    Dim strDimG As String
    strDimG = CStr(strDimension.Value)
    If strDimension <> "" Then
   
X = HypFindMember(Empty, strDimG, "Default", dimName, aliasName, GenName, levelName)

    GetLevel = (levelName)

    End If
    
End Function
*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*

New Features by Smart View Release: 11.1.2.3 - 11.1.2.5.520

Oracle® Smart View for Office:New Features - Release 11.1.2.5.520

Behavior Change for Duplicate Member Databases

In some Oracle Smart View for Office reports based on duplicate member name databases, the distinct member name may not be easily readable. For example, the distinct member name may consist of a very long string of characters. To improve the readability of the report when working with duplicate member databases, the Smart View Member Name Display option now automatically defaults to Member Name Only. In previous releases, the default for duplicate member databases was Distinct Member Name. Note that you can always change the display to Distinct Member Name.

Features Introduced in Release 11.1.2.5.510

• Easy Access to Attribute Members in Essbase
• Ability to Import Metadata in PowerPoint
• Individual Report Refresh Capability
• New Features in Oracle Enterprise Performance Reporting Cloud Service

Features Introduced in Release 11.1.2.5.500

• New Smart Forms for Saving Client-side Calculations
• New Oracle Journals Extension for Financial Management

Features Introduced in Release 11.1.2.5.410

New Extension for Oracle Enterprise Performance Reporting Cloud Service

This release introduces the Oracle Enterprise Performance Reporting Cloud Service extension for Smart View. With the Oracle Enterprise Performance Reporting Cloud Service extension, you use familiar Microsoft Office tools to access and work with data in Oracle Enterprise Performance Reporting Cloud Service.

Features Introduced in Release 11.1.2.5.400

• Enhanced Submit Data Functionality
• POV and Pivot Enhancements
• Improved Extension Update and Distribution
• New Diagnostics Tools
• New Member Selection Dialog Box for Business Rules
• User Name and Password Support for Drill-Through Reports
• Oracle BI EE Filter Enhancement
• Oracle BI EE Prompt Enhancement
• Measure Label Flexibility in Oracle BI EE View Designer
• Sheet Information Improvements

Features Introduced in Release 11.1.2.5.200

• Views Created in Smart View Can Be Edited in Smart View
• Support for Multiple Oracle BI EE Private Connections
• Enhanced Drag and Drop in View Designer
• New “Apply to All Sheets” Option
• “Improve Metadata Storage” Option Default Setting
• New Alias Table Display Option in Member Selection
• Native Excel Formatting Can Be Saved in Planning
• Planning Spreading Enhancement
• New “Repeat Member Labels” Option for Forms
• Improved Column Indentation in Planning
• Smart View Name Change

Features Introduced in Release 11.1.2.5.000

• Simplified Installer
• Modifying Connections from Document Contents
• Ability to Hide Document Contents Pane
• Refresh Not Required Before Submitting Data on Forms
• Change to Display of Duplicate Variable Names
• New VBA Functions: HypHideRibbonMenu and HypHideRibbonMenuReset
• Expanded Platform Support
• Tablet Support

 Features Introduced in Release 11.1.2.3.000

• HTTP Access for Shared Connection XML File
• Accessing Help Locally or from an Internal Web Server
• Function Builder Enhancements
• New VBA Function: HypUIConnect
• New Planning Extension
• Documentation Certified on Apple Mobile Devices

New Features by Smart View Release: 11.1.2 - 11.1.2.2.310

Great link to see what was new in each Smart View Release starting with version 11.1.2 until 11.1.2.3!

Features Introduced in Release 11.1.2.2.310

• Support for Oracle BI EE
• New VBA Functions to Support Oracle BI EE
• Shared Connections Can Be Stored Locally in an XML File
• New Document Contents Pane
• HsGetValue Functions Can Be Copied and Pasted
• Support for Shared Connections in Functions
• New Reporting Object: Office Table
• POV Supports 5,000 Members

Features Introduced in Release 11.1.2.2.300

• Enhanced Performance
• Support for Financial Management Data Grids
• Support for Essbase Database Notes
• Cell-Based POV for Essbase
• Improved Function Builder
• Fix Links
• User Variables in Financial Management Data Forms
• VBA Functions

Features Introduced in Release 11.1.2.2

• Smart Query
• Support for 64-bit Microsoft Office 2010
• New Extensions
• Excel Formatting Enhancements
• Planning Enhancements
• Financial Management Configurable Dimensionality
• New VBA Functions
• Logging Levels
• Miscellaneous
• Hosted Online Help

Features Introduced in Release 11.1.2.1.102

• Ad Hoc Operations in Multiple Cells
• Member Information
• Linked Objects
• Aliases and Alias Tables
• Sheet Level Options
• POV Toggle Button
• Multiple Grids
• Butterfly Reports
• New Zoom Options
• Formula Preservation
• Formula/Format Fill
• Use Excel Formatting Improvements
• Submit Data Without Refreshing
• New Smart View and VBA Functions

Features Introduced in Release 11.1.2.1

• Overview
• Financial Close Management Integration with Outlook
• Planning Features
• Redesigned Member Selection Dialog Box
• POV Enhancements
• Smart View Options Enhancements
• Support for Office 2010
• New Smart View and VBA Functions
• Alias Tables
• New Languages
• Right-Click Data Provider Preferences

Features Introduced in Release 11.1.2

• Smart View Panel
• Context-Sensitive Ribbons
• Integration with Outlook
• Redesigned Options Dialog Box
• Planning Features
• Financial Management Features
• Smart View and Disclosure Management Integration
• New VBA Functions
• EPM System New Features

How to Hide & Reset SV Ribbon Items

I was recently asked how to hide Smart View from users.

You are able to hide Smart View buttons and/or menus from the Smart View Ribbon or Smart View contextual ribbons (HFM, Essbase, etc.)

This is the VB code on how to do that:

- Hide -
 
Public Declare Function HypHideRibbonMenu Lib "HsAddin" (ByVal vtSheetName As Variant, ParamArray vtMenus() As Variant) As Long

Sub HideMenus_customRibbon()
'Hides menu items for Smart View ribbon


sts = HypHideRibbonMenu(Null, "Smart View->Panel", "Smart View->Connections", "Smart View->Undo", "Smart View->Redo", "Smart View->Copy", "Smart View->Paste", "Smart View->Functions")

End Sub


*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*
*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*


- Reset -

Sub ResetMenus_customRibbon()'Resets the visibility of menu items using the current sheet

sts = HypHideRibbonMenuReset(Null)

End Sub

New Smart View Feature in 11.1.2.5.510

It has been a long time in the making but it has finally arrived!
In Smart View version 11.1.2.5.510, this new feature made its debut.
Insert Attributes on the Essbase contextual ribbon.

This provides the ability to insert attribute dimensions onto-into a Smart View grid without having to type them on-in!!

Take a look

 

Where Did Arbor Go?

After reading an interesting article, I clicked the link to this website

http://www.arborsoft.com http://www.arborsoft.com

Where did it take you?

Essbase on a Mac, Why Not?

So why does Essbase not run on a Mac today, 20 years and 2 days after this original article was published? - April 15, 1996 original article

"Essbase scales from a single-user solution to an enterprise OLAP (on-line analytical processing) server within a data warehousing strategy. Essbase operates on servers running Windows NT, OS/2 and UNIX across all major networks connected to Windows, Macintosh and UNIX clients. Pricing starts at $37,500 for a five concurrent user license."

What shelf is this functionality hiding on collecting dust?

Versions of Smart View Add-in

Let's List Them All Oracle Hyperion Smart View for Office Add-in, Essbase Add-in

Versions of the Oracle Hyperion Smart View for Office Add-in:
(along with a name change between 11.1.2.5 and 11.1.2.5.200)

• Oracle Smart View for Office Release 11.1.2.5.520 Oracle docs: 520
• Oracle Smart View for Office Release 11.1.2.5.510 Oracle docs: 510
• Oracle Smart View for Office Release 11.1.2.5.500 Oracle docs: 500
• Oracle Smart View for Office Release 11.1.2.5.410 Oracle docs: 410
• Oracle Smart View for Office Release 11.1.2.5.400 Oracle docs: 400
• Oracle Smart View for Office Release 11.1.2.5.200 Oracle docs: 200
• Oracle Hyperion Smart View for Office Release 11.1.2.5 docs: 11.1.2.5
• Oracle Hyperion Smart View for Office Release 11.1.2.3 docs: 11.1.2.3
• Oracle Hyperion Smart View for Office Release 11.1.2.2.310 PS 310 PS
• Release 11.1.2.2.0
• Release 11.1.2.1.0
• Release 11.1.1.4.0
• Release 11.1.1.3.0
• Release 11.1.1.2
• Release 11.1.1.1
• Release 11.1.1.0
•  

Versions of the Oracle Essbase Spreadsheet Add-in:

• v11.1.2.1.0
• v11.1.1.4.0
•  

Version 9:

• Hyperion Smart View for Office Release 9.3.1.2.0 v9 docs: 9.3.1.2.0
• Hyperion Smart View for Office 9.2.1 v9 docs: 9.2.1 

Are There Any Other Add-in(s), Were There Any Others Out There?

• Arbor Essbase OLAP Server 5
• Arbor Wired for OLAP
• Arbor Essbase Web Gateway
• Arbor Essbase Objects
• Crystal Info for Essbase
• Essbase Spreadsheet Add-in
• Essbase 4.0
• Essbase Spreadsheet Client
• OLAP Retrieval Wizard?
• Essbase 3.2
• Essbase Web Gateway
• Smart Object Interface 1995 article