Why I created a blog

Its been many years since I first created this blog. It has remained true to Essbase and related information over those years. Hopefully it has answered questions and given you insight over those years. I will continue to provide my observations and comments on the ever changing world of EPM. Don't be surprised if the scope of the blog changes and brings in other Hyperion topics.

Thursday, September 15, 2011

Smartview Enhancements

It is rare that I have multiple blog posts in a single day, but I am so excited to see this come out. Smartview is available as a patch set on Support.oracle.com. Big deal you say, its just another patch! Oh how wrong you are my friends. This is the patch of all patches. The patch we have been waiting our collective lives for.  Why, because it makes Smartview act and behave like the Essbase add-in and more.

The first thing that I consider a fix is you can no do a submit data without first refreshing the sheet if you make changes to it. Second, we have all checked the “Use Excel formatting” option only to find the formatting disappears if we zoom in or pivot. Well, now it really does work. and it works in two ways. If checked, it leaves the excel formatting completely alone. For example, if you highlight cell C3 as red, and you pivot a page member to a row, cell C3 will still be red.  But the Smartview team has figured how to set it so if you zoom in, the formatting gets carried along with the zoom in. Very nice!!

But that is just the beginning. Here are some of my favorite new things. (note, I’m writing this from my experience with the Beta, some features in the final version may not have been implemented exactly as I describe them. Since this patch came out, I’ve not had time to install and test it)

Items that are now in Parity with the Add-in

1. You can select multiple members to do Ad Hoc operations on at one time (zoom in, zoom out, Keep only, remove only). This is not limited to just row or column member, you can select both at the same time

2. Support for LROs and Linked Partitions.

3. Aliases. There are two items here. first, you can have both the member name and aliases on rows in the report and second and one of my favorites, if you use an alias from an alias table that is not active, Smartview now understands it and returns the member name or alias for the active alias table (depending on what you have your options set to)

4. SHEET LEVEL OPTIONS are back!!!!!!!!! All options are defined as sheet level options and are stored with the sheet. that means you no longer have to worry about what happens if you change options and then do a retrieve on a different sheet. Also, There is now a dropdown on the options dialog so when you change your options, you can set a default set. This default set is used when new sheets are created.

5. All of the zoom options that you know and love in the add-in are now available. They are also on the ribbon bar as a dropdown on zoom in so you can select the zoom level/type you want to do.

6. Formula preservation and Formula fill are working like they should and then some. In the add in, if you pivoted in the add in you would lose the formulas. In this release, in most cases, the formulas are preserved. They may no longer be accurate based on what you did, but they will be there.

7. While the new features document does not talk about it, I believe range retrievals are back. You can highlight an area of a report and as long as al of the dimensions are represented , you can retrieve just that part of the report

New and enhanced items.

While the above items allow for Parity, the Smartview team was not satisfied with just making things the same. In a number of areas, the went miles beyond what the Add in could do. Here are some of the things.

1. You can have multiple grids on a single sheet. Remember the old message “Multiple grids on a single sheet are not supported at this time” Well the time has come and that message can now be archived forever. You can set up multiple connections to the same or different databases and do a refresh and they all update. I had 4 different reports on a single sheet and they all pulled data properly.

2. Butterfly and reverse formatted reports are now available. A butterfly report is where member names are in the middle with number on each side (as shown below. Note, the numbers are not from sample basic. I made then up to show a report sample)


You can also do a reverse report where the member names follow the data values


3. While allowed you to hide the POV, in this new version, you actually are not hiding it, but putting it onto the sheet. In this new version, the POV members are stored on the sheet in row 1 (or wherever you designate them to be. When you enable the POV, it hides the row on the sheet. Changing the member names on the sheet or in the POV bar affects both locations. Another nice feature with the POV is you can type in member names directly if you know them. (it was actually introduced in

4. A Member information button has been added to the ribbon bar. From this you can get almost all of the information about a member such as generation and level, consolidation properties, aliases, attribute associations, formulas, comments and UDAs.

5. within the Smartview connection information dialog, new functionality has been included. for multiple grids on a single sheet, you can delete the connection info (currently the only way to reset multiple connections) and also copy off the connection into to a sheet to send to someone if you are having issues with your retrievals so they know what you are connected to.

6. A host of new VBA toolkit functions have been added. The development team asked what APIs people are using and turned most of them into toolkit macros. This is very handy so you don’t need to code to the API any more, you can do it all with macros.


as with any release, there are disclaimers and this is no exception. In the new features document is the following

Unless otherwise noted, the features described here are available only in Smart View connected to Oracle Essbase through Oracle Hyperion Provider Services They are not available in other releases of Smart View, Essbase, or Provider Services. They are not available in data providers other than Essbase.

This means that you have to install the patches for Smartview, APS and Essbase to take advantage of the features. You can use this version of Smartview with older versions of APS but won’t have the new features available.

AS for upgrades, here is what the support matrix says

You can upgrade to Smart View Release from the following releases:

Release Upgrade Path From To

I am truly excited about this release. It gives us things we have been asking for in the add in for a long time and now puts Smartview ahead of the add in in functionality.    

I should also note, Cameron Lackpour has posted on his blog an Oracle presentation you can attend to see the new features demonstrated. Read about it here


srx said...

Great detailed paper Glenn! This features look really promising, now does the release notes says smthg about perfs improvment vs classic addin? (I now I'm an unsatisfied basterd!)

GlennS said...

Although they do not say it, they ar using the grid api to do most of the work.Performance is much better than before.

srx said...

Glenn this is a great news indeed regarding grid API comeback, it may then seduce many customers that were SmartView phobic, of course under the condition of going to

sam said...

So here's the magic question. Can you drill into a dimension that has more than 65K rows? Excel Add In doesn't allow this but wondering about Smartview???

GlennS said...

By default Smart View is set up to retrieve 5000 rows, you can modify this in the essbase.properties file in APS. If you set it to 0 it means unlimited. In addition, in the Essbase patch they have changed ssprocrowlimit to be unlimited if it is not in the essbase.cfg file.

Stephen said...

Hi Glen,

I'm using Essbase/Smartview I have 4 main dimensions in my Essbase database and 1 attribute dimension. When I do an ad-hoc query, the functionality is very weird. It appears that the old Essbase spreadsheet addin is back. Here's the problem. With a total of 4 dimensions, I cannot have Scenario and Period in the columns and accounts in the rows. For some reason it won't let me have just 1 dimension in the POV. Is this a bug in the new version of the software. Thanks in advance for your help!

GlennS said...

I've not had much opportunity to play with yet, but the POV should allow you to have from 0 to Dimensions -2 in it (I say minus 2 because you have to have at least one row and column dimension). Smart View now starts out with all dimensions on the sheet and you pivot them into the POV box.Perhaps where you are placing them on the sheet confuses the POV box into thinking there is a problem. What happens when you turn off and on the POV box?

Andres Santos said...


Very informative.
I tried the "Multiple Grid" and "Butterfly report" feature using smart view but I can't seem to make it work.

I am using smart view provider Do you think the provider is the culprit why the feature is not available?


GlennS said...

For multiple grid and butterfly reports you need to have upgraded both APS and Essbase to at least

Peter said...

we are using and it seems as if the multiple grids are not supported. I am getting the old error message saying that multiple grids are not supported.

Is this a bug or has the newer version taken this fucntionality away?

GlennS said...

Multiple grids are certainly supported in (I demo'ed it yesterday. Are you sure your ASP and Essbase server are on or higher? There are still cases when you will get that message. If you are an Oracle customer (not a consultant) come to my webcast tomorrow (2/21/13) and I demo how to do multiple grids. email dwhate@interrel.com for the goto meeting invite

Scott A said...

Hi Glen,
Our company is in the process of upgrading essbase. We are still using the essbase add-in not smartview. When attempting to run essbase macros we get this error "This version of essbase ( is older than the version of the essbase API ( you are using". Any recommendations you can give would be greatly appreciated.
Scott A (CWT)

Barry Tastet said...

My biggest obstacle with Smartview is that it doesn't allow me to use member names in formulas. I support my 'low-tech' users with drop down boxes and retrieve buttons. Smatview refuses to recognize the member from the drop down and assigns a top level member to the POV. That's a deal-breaker for me.

GlennS said...

I don't know what version of Smart View you are on, but it works with (It should also work with anything higher that
I just tested where I have a member named Tier 1. I put it on sheet 2 and on sheet 1 I put the formula =Sheet2!A1 then retrieved and it retrieved my data. I changed Tier 2 to Tier 1 and re-retrieved and my data changed to be Tier 1 totals.
I've seen samples where multiple grids (Multigrid sheets) where they shared a dropdown and all the grids changed with a change in the dropdown.

Anonymous said...

In the Essbase addin, you can format different dimensions with different background colors. How can I do the same in Smart View formatting? Thanks.

GlennS said...

The same thing can be done in Smart View. In the formatting tab, select use styles (you have to turn off Excel formatting). Then in the styles tab set up the colorization you want.

Anonymous said...

There is not an option to format the dimensions individually. For example, different colors for Years, Scenarios, Accounts, etc...I only see Parent or Child, not Dimensions. Thanks.

Anonymous said...

Is there an 'Unknown Members' notification setting in SV? There used to be one in Essbase Add-in. Thanks.

GlennS said...

You are correct,/I forgot they took that out. not sure why you need different dimensions colored differently, but, if you are not pivoting dimensions around, you could use excel formatting and color the column or row for the dimension

GlennS said...

There is not an option for unknow members. In at least, by default when you retrieve, you get a list of unknown members in a dialog box. I don't know of a way to turn that off like you could with the "display unknown members "option fro mthe add-in

Fran said...

Can you place the POV somewhere other than Row 1? We are converting a lot of spreadsheets from and the additional row throws off the cell references.

GlennS said...

In theory you could place the POV on a different row, but it would still have to be above the rows and columns. I think the best way would be to use Excel to insert a row which would shift your formulas down and then manually add the pov members and retrieve