Tips for using Smartview and Excel Add-in for Essbase

Smartview for Office and the traditional Essbase Excel Add-in offer analysts a powerful adhoc capability to query an Essbase database. Both tools allow for real time drilldowns, pivots, and custom reporting directly from within Excel. In addition, users can utilize all of Excel's built in calculation commands against retrieved Essbase data to perform unique analysis and problem solving. However, getting started with either adhoc tool can be daunting. Here I'll list several options every new user of the Excel add-in should be aware of to help them navigate their Essbase databases.

Color coding of Parent members
One common question is simply "Where can I drill down?" Utilizing styles, it is possible to highlight all members that can be drilled down upon a differently than those which can not be expanded (i.e. the Level Zero members).

Smartview for Office:

1. Connect to an Essbase data source in adhoc mode
   
2. Select "Options" on the Hyperion menu
3. On the "Cell Styles" tab, expand Analytic Services -> Member Cells
4. Check "Parent Cells"
5. Right-click "Parent Cells" and select the manner in which you'd like to format them (I prefer to adjust the font, making Parent cells Bold and Black).
   

Excel Add-in for Essbase

1. Connect to an Essbase database
2. Select "Options" on the Essbase menu
3. On the "Display" tab, ensure "Use Styles" is checked (under "Cells")
4. On the "Styles" tab, check "Parent" under "Members"
   
5. Click the Format button. Set formatting to your preference (I make Parent members Black and Bold)
   

Identification of Read-only vs. Writable data cells
Users utilizing Excel to write to their database can have cells they have access to write to highlighed in a different color than read-only cells.
Smartview for Office:

1. Connect to an Essbase data source in adhoc mode
2. Select "Options" on the Hyperion menu
3. On the "Cell Styles" tab, expand Analytic Services -> Data Cells
4. Check "Writable"
5. Right-click "Writable" and select the manner in which you'd like to format them (I prefer to adjust the font, making Writable cells Bold and Green).
6. You may also want to highlight read-only cells differently. I prefer making these values Red.
   

Excel Add-in for Essbase

1. Connect to an Essbase database
2. Select "Options" on the Essbase menu
3. On the "Display" tab, ensure "Use Styles" is checked (under "Cells")
   
4. On the "Styles" tab, select "Read/Write" under "Data Cells"
5. Click the Format button. Set formatting to your preference (I make writable values Green and Bold)
6. You may also want to make Read-only values a special color. I prefer Red.
   

Retaining Excel formulas during a retrieve
(This applies to the Excel Add-in only; Smartview for Office retains formulas during a retrieve.)
One of the greatest powers of both tools is the ability to utilize Excel's built-in calculations against Essbase data. However, by default, all calculations are deleted from the worksheet whenever a blanket retrieve is performed. One option to prevent this is to highlight only the cells necessary for the retrieve (both the data cells and member cells). Alternatively, both tools have an option to preserve formulas during a retrieve.

1. Connect to an Essbase database
2. On the "Model" tab, select "Retain on Retrieval" under "Formula Preservation". This will retain your formulas when doing a simple retrieval.
3. To have Excel adjust your formulas when performing Zooms or Keep/Remove only operations, use the second and third options.
   

Some other settings I've found useful when using the older Excel Add-in (some I use all the time, some only occasionally):

1. On the "Display" tab, select "Adjust Columns". This setting will automatically adjust the width of each column with either an Essbase data point or an Essbase member to the width of the largest item.
2. On the "Display" tab, change "Indentation" to "Subitems". I find it easier to read an adhoc spreadsheet if a member's component members are indented, as opposed to the default.
3. On the "Zoom" tab, select "Formulas". This will not show me the exact formula utilize in the Essbase database, but will show me all members that appear in the formula from the same dimension as the member drilled upon.
4. On the "Mode" tab, select "Update Mode" when doing a number of data write-backs. This will automatically lock all writable data cells when doing a retrieve, so instead of having to perform a lock, then a send, only a send is necessary.
5. On the "Global" tab, unselect either "Enable Secondary Button" or both "Enable Secondary Button" and "Enable Double-Clicking" if using Excel but not against Essbase. This will give you back full functionality of your right and left mouse buttons.
   

Hopefully you've found these tips helpful for getting started with adhoc analysis directly against an Essbase database.