QUERYRESULTLIMIT and its journey to-date

This setting started to appear in version 11.1.2.4.007 and has been making an appearance in a few releases, PSUs, or both for Essbase server and APS, let's take a look

Oracle Essbase - Release 11.1.2.4.000 Patch Set Update (PSU): 11.1.2.4.007
Defects Fixed in this Patch:

21881863 - When running large MDX queries against an Aggregate Storage Database can result in running out of memory. In this case, use this essbase.cfg setting:

Name of setting: QUERYRESULTLIMIT

Syntax: QUERYRESULTLIMIT [appname [dbname]] n

Where n is an integer value specifying the maximum number of query result cells.

See the "QUERYRESULTLIMIT Configuration Setting" topic in the "Documentation Updates in this Patch" section in this Readme.

>>>
Documentation Updates in this Patch

QUERYRESULTLIMIT Configuration Setting

Sets the maximum number of cells returned by an MDX query. This configuration setting applies to block storage, aggregate storage and hybrid aggregation databases.

Syntax

QUERYRESULTLIMIT [appname [dbname]] n

• appname—Optional. Applies the query result limit to the application specified. If you specify appname, you must also specify a value for n, or Essbase Server ignores QUERYRESULTLIMIT. If you do not specify an application, you cannot specify a database, and the query result limit applies to all applications and databases on the server. If you specify a value for appname and do not specify a value for dbname, the query time limit applies to all databases in the specified application.

• dbname—Optional. Must be used with appname and n, or the server ignores QUERYRESULTLIMIT. If you specify dbname, appname, and n, the query result limit is applied only to the specified database.

• n—Integer value of n specifies the number of query result cells that the server allows a query to return. You must specify this parameter or the server ignores QUERYRESULTLIMIT. If you do not specify appname or dbname, the query result limit applies to the entire server.

Description

QUERYRESULTLIMIT specifies the maximum number of result cells that an MDX query can retrieve before Essbase Server terminates that query. You can apply this setting to an entire server, to all the databases in a single application, or to a single database.

If no limit is defined in essbase.cfg, there is no results limit.

When the number of returned cells for a query exceeds the result limit, an error message is returned.

Use QUERYRESULTLIMIT to limit the result volume of MDX queries and prevent a query from freezing when a very large number of result cells are returned.

Examples

QUERYRESULTLIMIT Sample Basic 100000

Sets 100,000 cells as the maximum number of results cells returned in a query to the Basic database for the Sample application.

QUERYRESULTLIMIT 150000

Sets 150,000 cells as the maximum number of cells that a query can return before being terminated. The query result limit applies to all applications and databases on Essbase Server that correspond to the essbase.cfg file containing this setting.

>>>

Oracle Hyperion Provider Services - Release 11.1.2.4.000 Patch Set Update (PSU): 11.1.2.4.008

Defects Fixed in this Patch

22822213 - Provider Services support for Essbase Server update for QUERYRESULTLIMIT configuration setting in the essbase.cfg file.

See the Essbase 11.1.2.3.508_22314799 PSE Readme for more information.

22976584 - Essbase properties service.olap.dataQuery.grid.maxRows and service.olap.dataQuery.grid.maxColumns in the essbase.properties file are deprecated in this release. The results of the grid are now controlled by the QUERYRESULTLIMIT configuration setting in the essbase.cfg file on Essbase Server.

See the "Deprecated Essbase Properties in the essbase.properties File" topic in the "Documentation Updates in this Patch" section in this Readme.

>>>

Documentation Updates in this Patch

Deprecated Essbase Properties in the essbase.properties File

Essbase properties service.olap.dataQuery.grid.maxRows and service.olap.dataQuery.grid.maxColumns in the essbase.properties file are deprecated.

These properties, if defined in essbase.properties, do not have any effect on the grid result.

The results of the grid are controlled by the QUERYRESULTLIMIT configuration setting in the essbase.cfg file on Essbase Server.

With these changes, existing use cases that expect an error for the previous lower row and column limits set in JAPI will not get an error unless the QUERYRESULTLIMIT limit is exceeded.

With these changes, Provider Services JAPI does not apply any limits against a previous version of Essbase. Essbase has to be upgraded to use the QUERYRESULTLIMIT configuration setting.

>>>


Oracle Essbase - Release 11.1.2.4.000 Patch Set Update (PSU): 11.1.2.4.009

Known Issues in this Patch

QUERYRESULTLIMIT configuration setting does not honor all values in this patch.

This version of the QUERYRESULTLIMIT configuration setting documentation replaces the content in the Essbase 11.1.2.4.008 Readme. The default value is 1,000,000 and can be increased to 100,000,000 but no other value will be honored.


Oracle Essbase - Release 11.1.2.4.000 Patch Set Update (PSU): 11.1.2.4.010

Defects Fixed in this Patch:

22953962, 22999617 - QUERYRESULTLIMIT setting has an upper limit of 2^31 and when the limit is set to a greater value it is treated as the value 0.

22863123, 22861985 - When using QUERYRESULTLIMIT setting and pivoting a member from column to row or when you have particular spreadsheet layout while staying within the limit setting can result in a terminated query.


Patch Set Updates for Hyperion Essbase 11.1.2.4.011 – NONE


Patch Set Updates for Hyperion Essbase 11.1.2.4.012 – NONE


Oracle Hyperion Provider Services - Release 11.1.2.4.000 Patch Set Update (PSU): 11.1.2.4.013

Defects Fixed in this Patch:

23666602, 23149351 - Drill Through reports can exceed the limit of the QueryResultLimit Function and an error message is returned:

Error executing report \'EEGL'\'DT_REports'\'EEPL' in the Essbase Studio, message: Runtime error. Line =671.


Patch Set Update for Oracle Hyperion Essbase 11.1.2.4.014 – NONE

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

 
AND, with all of that I think that there might be a few other points to note.

• Default value is 1000000
• The maximum value of 2^31 is equal to 2147483648 (or 2,147,483,648) that is over 2 billion, why?! Really, WHY?!?! What do you do with that many cells of data in Excel anyway?! Please don't share as I already know the answer, it starts with PI and ends with VOT table, please stop the insanity!
• QUERYRESULTLIMIT does NOT have an 'unlimited' setting
• Make sure that your Smart View client is updated to version 11.1.2.5.610 :)
• 'unpublished bug' - huh?!? BUG 16005347 (MULTIPLE MEMBER SELECTION- ZOOM IN CAUSES "ESSBASE ERROR (1013295)" IN SMARTVIEW [UNPUBLISHED & INTERNAL]) ... but glad to hear it was fixed here with
• Essbase 11.1.2.4.013, Patch 24589660

• Smart View v11.1.2.5.610, Patch 24711736

 

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

MaxL Grammar Changes

Oh my, it has been a while since my last post .. and feel that there may be more than one in addition to this one.

So recently I was working with a Planning application that SPARC'ed me to write this post ;)

Not sure how many know, but in version 11.1.2.4 there was change made to the MaxL grammar that now allows for the export of data to be made anonymous!

The MaxL export data statement includes grammar you can use to make exported data anonymous, wherein real data is replaced with generated values. This removes the risk of sensitive data disclosure, and can be used in case a model needs to be provided to technical support for reproduction of certain issues.

Here is the 11.1.2.4 Technical reference: link

ASO/BSO keyword:
export database <dbs-name> ... data anonymous

Description: Export data in anonymized format. Anonymization removes the risk of sensitive data disclosure, and can be used in case sample data needs to be provided for technical support. Essbase replaces real data values with 1, for each value in the block.

These are the railroad diagram for each ASO & BSO, and enjoy exporting data for analysis, support, performance testing, etc.

SV ++ Utility update

There is a new update to the SV ++ Utility!! version 8.6.4 now available!

Check out the downloads or SV ++ blog sections

Oracle Smart View for Office 11.1.2.5.610 - Patch Set Update (Patch 24711736)

Oracle Smart View 11.1.2.5.610 GA build is now available!

The download and install from My Oracle Support: download

Or search for patch# 24711736


New Features in this version

• New VBA Functions
• Planning and Budgeting Cloud Service Features
• Locating New Features Information for Past Releases 
   

New VBA Functions

Several new VBA functions are introduced in this release. At this time, these functions are supported by Essbase only.

• HypCreateRangeGrid enables users to refresh multiple selected or named ranges of cells in a multiple-grid worksheet. You can also use it to add a grid in the selected range if there is not already a grid in the particular location, thus creating a multiple-grid sheet. If the ranges specified for this function contain more rows or columns than the actual grid has, the additional rows and columns are treated as comments and are thus part of the grid.
• HypSubmitSelectedRangeWithoutRefresh updates the database with data, as is, from the selected data range; it ignores cells outside the selected range and does not overwrite (or refresh) them. The selected range does not automatically get refreshed after submit; the user must manually refresh the grid to retrieve the updated data. For a successful submit, the user must select a range which constitutes a valid grid.
  This function requires Oracle Hyperion Provider Services release 11.1.2.4.13.
• HypSubmitSelectedDataCells allows the selected data cells to be submitted. For free-form grids, this VBA function allows selected blank cells to be submitted as #Missing.

These VBA functions are described in detail in the Oracle Smart View for Office Developer's Guide.

Planning and Budgeting Cloud Service Features

Subtopics

• Drop-down Member Selection on Row Dimensions in Forms
• Change in Accessing the User Preferences Dialog Box

Drop-down Member Selection on Row Dimensions in Forms

In forms, Planning administrators and form designers can set up easy-to-use drop-down member selectors on row dimensions. When these drop-down selectors are enabled, the form users can directly select a member from the drop-down list on the row dimension in the form. The drop-down member selector also allows users to add data to member rows that may have otherwise been suppressed.
For the Smart View user, keep in mind the following:

• There is no visual notification as to which members have drop-downs enabled; you must click a dimension cell and, if a down arrow appears next to the cell, then there is a drop-down list enabled.
• The drop-down content (list of members) is controlled by the form definition for the corresponding segment.
• Selections are not retained. The drop-down selection will be lost on refresh. If you perform a Refresh after selecting a member from the drop-down, the selection is lost and the form returns to its original state.
• The drop-down selection will be lost on saving data. Modifying data cells and saving the Excel worksheet will not retain modified drop-down selections.

To use this feature in Smart View requires configuration in the Form Designer in the Planning web interface. The Planning administrator or form designer specifies the drop-down selectors as segment properties for particular dimensions. For example, suppose there are two dimensions placed on row axes, Entities and Line item; the member selector drop-down may be enabled for the Entity dimension members, for the Line item dimension members, or for both. Configuration is described in Administering Planning for Oracle Planning and Budgeting Cloud.

Change in Accessing the User Preferences Dialog Box

This release changes the way you access the User Preferences dialog box from the Smart View Panel. Instead of accessing the dialog box from the plan level, you access User Preferences from the application level.
Additionally, you will no longer be prompted to select a plan type. Instead, the preferences you set will apply to all plan types.

Locating New Features Information for Past Releases

• For Smart View Release 11.1.2.5.600, see the Oracle Smart View for Office New Features available in the Smart View 11.1.2.5.600 library available on the Oracle Help Center.
  https://docs.oracle.com/en/applications/?tab=8
• For new features relating to Oracle Enterprise Performance Management System installation, architecture, and deployment changes in this release, see “New Features in this Release” in the Oracle Enterprise Performance Management System Readme.
• Use the Cumulative Feature Overview tool to review the list of new features added between earlier releases for Smart View and other EPM System products. This tool enables you to identify your current products, your current release version, and your target implementation release version. With a single click, the tool quickly produces a customized set of high-level descriptions of the product features developed between your current and target releases. This tool is available here:
  https://support.oracle.com/oip/faces/secure/km/DocumentDisplay.jspx?id=1092114.1

 

Smart View Tips & Options

Not to take away anyone's Thunder & Lightning from Oracle Open World this year, I was not in attendance, but it seems from all of the emails, posts, tweets and other social media outlets that, well, there is a push for all things Cloud?!?!

But in other news, for those of you that still need to use Smart View, here are a few tips, options, settings etc. for you to keep in mind to help in your data retrieval journey while you are not cloud surfing.

First, when you install Smart View, this link can be a great tool to read over for both Admins and Users alike: Oracle Smart View for Office Release 11.1.2.5.600 Installation and Configuration Guide

There are a few great sections:

• Installing Smart View
• Administering Extension Installations and Updates
• Accessing Shared Connections from an XML File
• Installing and Configuring Help Locally or on a Web Server
• Tips and Troubleshooting
• Limitations

 Let us focus on the Tips and Troubleshooting section!

• Diagnostics Tools
• Installation and Languages
• Windows, Office, and Smart View Locale Settings
• Error When First Launching Excel After Installing Smart View
• Internet Explorer Compatibility View Settings for Financial Reporting
• Working with Custom HTML-based Login Forms
• Browser Settings When Working with XML Files on HTTP Server
• Flickering Issues on Office 2013 32-bit and 64-bit
• Issues When Starting Excel in Automation Mode
• Slowness with Extension Options Dialog Box
• Performance and Memory Usage Considerations for Smart View with Excel
• Smart View HTTP Session Timeout
• Duplicate Queries in Word Slow to Refresh
• Issues with Dynamic Data Exchange Option in Excel
• Transition Navigation Keys Not Supported 

I hope that the links, tips and options help all users easliy hurdle some of the more common questions that I get from users and also see on many blogs and forums.

In preparation for soon to be submitted KScope 17 abstracts, the deadline is quickly approaching, Oct-14th!, I hope that you are able to Wrangle some more data via Smart View .. or in my case, maybe not Smart View, stay tuned!

Information Regarding NEW Smart View Installs (Doc ID 2174747.1)

posted by Oracle yesterday, Sep-06-2016

Information Regarding NEW Smart View Installs: New Installs Displays Certificate Message: "Warning: The digital signature on the application add-in is invalid and cannot be trusted. Application add-in is disabled.The following new My Oracle Support Knowledge Article is available:

Doc ID 2174747.1

per the document:

Applies to:

• Hyperion Essbase - Version 11.1.2.4.000 and later
• Hyperion Planning - Version 11.1.2.4.000 and later
• Hyperion Financial Management - Version 11.1.2.4.000 and later
• Generic Windows
• This issue applies to all versions of Smart View

Symptoms

After installing Smart View and opening Excel, a Microsoft Office Security Options window opens with:

"Warning: The digital signature on the application add-in is invalid and cannot be trusted. Application add-in is disabled."

This affects all new installations of Smart View. Existing installations are not impacted.


Changes

New installation of Smart View.

Cause

The certificate for the "Digital Signature" for the HsTbar.xla has expired. This is used for Spreading and VBA's.

Solution

To workaround the certificate warning:

1. Install Smart View ("Run as Administrator"). Do not launch Excel prior to performing the next step. 
2. Rename the HsTBar.xla file to HsTBar.xla_badcert.  This file is located in the \SmartView\bin folder. 
3. Launch Smart View.

Not Just Any Essbase Studio Drill-Through!

It has been a long time coming to finally enable Essbase Drill-Through reports!

But not just any Essbase Drill-Through reports. These are reports against an OBIEE Subject Area. And again, not just any ole' Subject Area. The Subject Area is a Pivotal Greenplum data source!

For the last plus year, I have been wanting to enable Drill-Through reports using Essbase Studio (build Essbase ASO cube model(s) plus create reports). The main and only reason that I was interested in using Studio for the cube build and deploy was for the ability to provide Drill-Through reports using the SQL-type reports!

Why? If you are not familiar, SQL-type Drill-Through reports run the SQL and 'spawn' a new worksheet of the SQL results in that worksheet in the current workbook! Truly an amazing, brilliant and mind blowing feature!


If you are not familiar to what the drill-through report feature is, a brief explanation is this... Once you create your cube (model) in Essbase Studio, hierarchies, dimensions, Alias, etc. and finally cube deployment, you can also create Drill-Through reports.

 

In creating reports, there are 3-options (types) in the Essbase Studio client:

 



1 - Relational-type (SQL)
2 - URL-type
3 - Java Method-type

I am going to go over the URL-type and only mention that I have not been able to wrangle up any viable examples and/or documentation on how/why/where/who of the 3rd, Java-type is being used or is in use.

URL-type drill through reports. These are pretty much as you would think. When creating the report, you define the grid (POV) context for the report.

In the screen captures (below), I am passing 2 contexts;
for Time (Period & Year).

This is pretty straight forward.

In short, you are defining the intersections to be enabled by Smart View for drill-through. The intersections can be defined by either Levels or Generations in the cube model.

You can either click the 'Add' button or simply drag and drop the desired hierarchy from Metadata Navigator into the 'Intersections' window.


 









The "Advanced Settings..." is where you can, in addition, define the hierarchy Generation or Level for each dimension context.
In this case, both hierarchies are set at Level-0. 


You then go to the next tab, 'Report Contents', select the URL-type radio button.

In this example, I am using the Sample URL Template (Google search). This is actually a good way to validate that the drill report contexts are getting passed properly to the report being executed.

You can see from the syntax, created for you, that the 2 contexts on the previous tab where placed directly into the URL drill path as needed plus in the proper syntax format just the same.


The context, as stated in the Essbase Studio guide is as follows:
$$Period-VALUE$$
$$Year-VALUE$$

By using this, the drill-through report will run against these members from your Smart View reporting grid (POV) and the specific intersection of data that is being called on for the drill-though report.

Now, the last and final step, is to associate the drill-through report to the desired cube (model).

Simply make sure that the checkbox is checked for the cube or cubes that make sense to enable the desired drill-through report.

Click 'Save'.



Now, that might still leave you without seeing your drill-through report in Smart View! Don't get frustrated. I will try and save you the time, as I was able to breakthrough and find this workaround that worked for me.

The workaround:
## Note you do NOT have to re-deploy the entire cube ##
Only for the hierachy(s) that you have used as a context.

You can use the Cube Deployment Wizard to do a partial cube build. While retaining all of the data already loaded.

 














From the Metadata Navigation window, Expand the Cube Schema and Right click on the Essbase Model.

You should now see a menu list.
Select "Cube Deployment Wizard"

Make the selections accordingly:

Server, Application, Database

Click Next
 


Build outline
Check the box "Incremental load"




Click Next

This is important assuming that you don't want to rebuild the entire cube and redeploy again.

Make sure to select the radio button:

"Update or rebuild selected hierarchies"

Choose the dimensions that are associated contexts in which the drill through report(s) were created.

Make sure if you want to retain any data already loaded in the cube that you 'Preserve: All Existing Data'. Update or Rebuild, click-one of the buttons; your choice.

Click 'Finish'



#######



Going back and a little bit of background on the setup.

1. The Subject Area was created first (Pivotal Greenplum)
2. The report/analysis was created in OBIEE to be able to launch the Drill-Through report as either 1-SQL or 2-URL based report but also passing the proper context(s) from Smart View
3. The Essbase cube (model) was built & deployed, verified, validated, etc.
4. Then create the drill-through reports in Essbase Studio were created
5. Workaround: Redeploy hierarchy-dimensions as needed to 'enable' reports in context