Saturday, April 30, 2016

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!

Thursday, April 28, 2016

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

Tuesday, April 26, 2016

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
*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*


Monday, April 25, 2016

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

Features Introduced in Release 11.1.2.5.500

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

Saturday, April 23, 2016

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!


Friday, April 22, 2016

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


Monday, April 18, 2016

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

 


Sunday, April 17, 2016

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:

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