Transcript No Slide Title

HFM Excel SmartView Tips
November 12, 2009
Jim Farley
Who is Honeywell ACS?
• Honeywell International is a diversified technology and
manufacturing leader, serving customers worldwide with
aerospace products and services; control technologies for
buildings, homes and industry; automotive products;
turbochargers; and specialty materials.
• Honeywell Automation and Control Solutions (ACS) is a
$13 billion strategic business group of Honeywell, applying
sensing and control expertise that helps create safer, more
comfortable, more secure and more productive
environments.
2
Honeywell ACS - - HFM Vital Statistics
• HFM Version: 9.2.0.3
• Entities: 2,000 base; 15,000 total
• Hyperion Reports Version: 9.2.0.3
• Scenarios: 75 (including FX)
• FDM (Upstream) Version: 8.3
• ICPs: 5,000
• Users: > 1,500
• Concurrent Users: 250
• Accounts: 2,500 base; 6,000 total
• C1 (Line of Business): 450 base; 800
total
• Geography: 62 countries / 170 Cities • C2 (Sub-account): 350 base; 600 total
• Security: LDAP (23 groups)
• C3 (Function): 35 base; 60 total
• HFM secured by Role, Entity (region), • C4: Not used
Scenario, Account
• Rules (Lines of Code): 4,700 (CTA,
• FDM secured by Entity
• Activities: Actual, Supplemental
(10K/10Q), Plan/Est
• Consol. during close: > 7,000
• HFM Database: 250 GB
3
FCF, Working capital)
• Avg. Consol. Times:
- 1 entity for 1 month: < 45 sec
- Top entity for 1 month: < 90 minutes
Production HFM Servers
4
Tip #1 – Minimum HFM / SmartView PC Memory
• Minimum recommended memory is 512 Meg.
• Large HFM spreadsheets will benefit from additional PC
memory.
• To determine how much memory a computer has:
- Depress the ‘Ctrl’ ‘Alt’ and ‘Del’ keys, and then choose ‘Task Manager’
the ‘Total Physical Memory (K)’
shows how much memory
is installed In this example there
is 1039732 total memory (1 Gig)
5
Tip #2 – Optimal PC Memory usage
• Examine memory usage of the PC while running a retrieve
• Performance will start to decline if the ‘Total Commit Charge
(K)’ is larger than the ‘Total Physical Memory’, as the PC will
have to page memory to disk.
• The steps to determine this are:
- Depress the ‘Ctrl’ ‘Alt’ and ‘Del’ keys
- Choose ‘Task Manager’
Compare the ‘Total Commit Charge (K)’
with the ‘Total Physical Memory (K)’.
In this example there is
1039732 total memory, with only
560372 Committed, so there are
not any paging issues here
6
Tip #3 – Excel memory fragmentation
• Excel memory usage can become fragmented with each
SmartView retrieve and Excel tends to hold memory that it
no longer is using
• If a computer is found to be paging during a refresh and
more than three refreshes have been performed since
initiating the Excel session:
- Shut down Excel
- Wait before restarting Excel, as it can take Excel up to 15
minutes to release memory
- Restarting the entire computer will also release memory.
7
Tip #4 - Excel File Size Growth issue
• Excel file sizes can grow significantly when using SmartView.
• The larger file sizes should not affect performance, but are
cumbersome to manage / share
• To reduce a file back to its original proportion:
- Open the spreadsheet
- Log into HFM
- Immediately re-save the spreadsheet.
• The newly saved spreadsheet will approximate the size of
the original spreadsheet.
8
Tip #5 – General Excel tips
• For large SmartView spreadsheets, the only Excel
session should be the single SmartView session.
• PowerPoint uses a large amount of memory and should
be closed when working with a large SmartView
spreadsheet.
• For large retrievals, “Refresh” one worksheet at a time
rather than initiating a “Refresh All”. Refreshing All
worksheets at the same time on a very large workbook
may result in system time outs which cause the process
to run extremely long.
• Excel should be set to
Manual calculation on large workbooks.
Select Tools : Options : Calculation tab
Check the Manual calculation option.
Use F9 to manually calculate
9
Tip #6 - Drill down data analysis
• Drill down data analysis (i.e. hunting for data issues) will
be performed more efficiently by using Data Grids &/or
SmartView Ad-Hoc functions rather than SmartView
retrieve.
• Grids and SmartView Ad-Hoc refresh faster than
SmartView by limiting cubes pulled into memory.
• SmartView Ad-Hoc and BI+ reports are more efficient in
data transfer, so they are preferred for large data
requests on slower networks.
10
Tip #7 - Disk space
•
A SmartView PC should have a minimum of 1 gig of
free space available on disk drive C:
•
Occasionally defragment the disks on SmartView
computers.
•
To defrag a disk:
- From the Start Menu, select ‘My Computer’
- Right Click on the drive to be checked for Defragmentation and
select ‘Properties.
- From the Tools tab, select ‘Defragment Now’
- Select the drive to defragment, and then choose ‘Analyze’
- Run the defragment of the drive if recommended by the
Analyze.
11
Tip #8 – Working with ‘#NoData’
• SmartView may display no data values with a blank,
dash, or “#NoData”.
• This can create issues because these values cannot be
used in Excel computations.
• To fix this problem:
- Select Hyperion : Options from the Excel header menu,
- Depress the Display tab,
- Enter a 0 to the #NoData/Missing Label field,
- Depress the OK button in the Options Display dialog box to
accept the change.
12
Tip #9 - Excel Crashes with Large Workbooks
Connected to both Essbase & SmartView
• Conflict having the Essbase and SmartView addins
active at the same time.
• The best work around is to turn off either the Essbase or
SmartView addin.
• To turn the Hyperion addins off,
- select Tools : Add-Ins
- deselect the Hyperion Essbase OLAP Server DLL & Hyperion
Essbase Query Designer addins when working in SmartView.
- Similarly, select the Essbase addins when working with Essbase
data and deselect the Hyperion SmartView for Office addin.
• The Hyperion menu may not display when toggling the
Hyperion addins on/off unless the Excel session is
restarted.
13
Tip #10 HFM – Control # of cubes in memory
• SmartView performance is highly dependent on the number of cubes pulled into
memory.
• A SmartView/HFM cube = all accounts and custom dimensions for a single entity in
one scenario for a year. The number of cubes pulled into SmartView equals entities
* scenarios * years that are refreshed at a time.
• The number of cubes pulled into memory with a single SmartView refresh should not
exceed 400.
• If a workbook pulls more than 400 cubes, the entities/scenarios/years should be
broken into worksheets so that each worksheet contains less than 400 cubes.
• Subsequently, only one worksheet should be refreshed at a time so SmartView does
not pull more than 400 cubes into memory at a time which could result in system
timeouts.
• Generally, the number of accounts and custom dimensions associated with each
entity/scenario/year combination does not matter.
Entities
100
10
14
Scenarios
3
2
Years
3
1
Accounts
10
1,000
C1/C2/etc
1
20
Cube Size
100 * 3 * 3 = 900
10 * 2 * 1 = 20
The 20 cube worksheet will refresh faster than the 900 cube worksheet
Tip #11 - Working with both Essbase & HFM
• To use Essbase & HFM at the same time with only one application connected:
- Log into Essbase and Retrieve Essbase Data, then disconnect from Essbase
- Log into HFM System 9 and Refresh HFM Data
• If Essbase and SmartView addins must be used together, changing Essbase
settings will mitigate the issue:
- Select Essbase : Options from the Excel header menu then choose the Global tab.
- Change the Global Essbase settings to:
 CHECK Limit to Connected Sheets.
 CHECK Enable Secondary Button (Right Click button is usually your secondary button)
 CHECK Enable Double Clicking
• With these settings:
- Essbase Mouse actions will supersede Excel mouse actions
- You will no longer be able to right click on a cell in an Essbase connected sheet to get the
context menu (copy, paste, etc.) and no longer be able to double click on a cell to allow incell editing.
- If you right click in an Essbase connected tab on the row or column, you will have the
context functionality (copy, paste, insert, etc.).
- Left double clicking zooms into a dimension member and right double-clicking zooms out.
15
Tip #12 - Limit your retrieve formulas
• The total number of HFM retrieve formulas in an Excel
workbook (all worksheets) should not exceed 250,000
otherwise the workbook will likely exceed the local
memory available on a 2 GB computer.
• To count HFM formulas, select Edit : Replace from the
Excel Edit header menu then replace each instance of
“=HS” with “XYZ” for all sheets. The number of
replacements equals the number of SmartView formulas
in the workbook.
• Users should find alternate reporting solutions for
spreadsheets that exceed this limit as HFM was not
designed to be a data warehouse for Excel.
16
Tip #13 - Hyperion Menu has disappeared
• Due to some previous crash or problem, Excel has
disabled the Smart View addin.
• The issue resides with Excel locating or using the dll.
• To recover the menu:
1. Go to HELP->About Microsoft Excel->disabled items
2. Select HSaddin.dll
3. Press the enable button
17
Ending comments….
• Financial users love SmartView, but hate it’s performance
• Network performance critical to consistent SmartView performance
• HFM upgrade = improved SmartView performance
• Manage HFM subcube size (Finance)
• Keep your Oracle DB running efficiently (IT)
- Tables analyzed
- Eliminate chained rows
- DB settings according to Oracle recommendations
- Ongoing purge of task audit and Error tables
• HFM app server configurations (IT)
18
- 3 GB switch
- Server settings (i.e. MaxNumDataRecordsInRAM, ….)
QUESTIONS???
19