Hyperion Financial Management Application Design for Performance

  • Published on

  • View

  • Download

Embed Size (px)


Edgewater Ranzal Practice Director and Oracle ACE Chris Barbieri conducted this presentation at the HUGmn TechDay and Vendor Expo in Chaska, Minnesota on March 20, 2012.


<ul><li><p>Hyperion Financial Management </p><p>Application Design for PerformanceMarch 20, 2012</p><p>Chris Barbieri</p><p>Practice Director</p></li><li><p>Copyright 2012 Edgewater RanzalCopyright 2012 Edgewater Ranzal</p><p>Personal Background</p><p> Established HFM performance tuning techniques and </p><p>statistics widely used today</p><p> 4+ years as Sr. Product Issues Manager at Hyperion</p><p> HFM, Smart View, Shared Services, MDM</p><p> 2001 HFM launch team 2001</p><p> Certified HFM, Hyperion Enterprise</p><p> B.S. Finance &amp; Accounting, Boston College</p><p> MBA, Babson College</p><p> Established HFM Performance Tuning Lab at Ranzal</p></li><li><p>Copyright 2012 Edgewater RanzalCopyright 2012 Edgewater Ranzal</p><p>Foundation of Performance</p><p> Hyperion Financial Management</p><p> Metadata design as it impacts performance Volume of members</p><p> Impact of structures</p><p> Data Content</p><p> Density</p><p> Rules</p><p> Environment</p></li><li><p>Copyright 2012 Edgewater RanzalCopyright 2012 Edgewater Ranzal</p><p>Metadata</p></li><li><p>Copyright 2012 Edgewater RanzalCopyright 2012 Edgewater Ranzal</p><p>Designing HFMs 12* Dimensions</p><p>Application Profile</p><p>1. Year</p><p>2. Period</p><p>3. View</p><p>System</p><p>4. Value dimension, </p><p>includes </p><p>currencies</p><p>User controlled</p><p>5. Entity</p><p>6. Account</p><p>7. ICP</p><p>8. Scenario</p><p>User defined</p><p>9. Custom 1</p><p>10. Custom 2</p><p>11. Custom 3</p><p>12. Custom 4</p><p>* Through release</p></li><li><p>Copyright 2012 Edgewater RanzalCopyright 2012 Edgewater Ranzal</p><p>Application Profile</p><p>Year No inherent impact on performance</p><p> Can be increased after the application is built</p><p> Impacts database table volume</p><p>Period Base periods comprise column structure of every table, </p><p>whether you use them or not</p><p> Avoid weekly profiles unless it is key to your entireapplications design</p><p> Yearly is inadvisable</p><p>View No impact, but only YTD is stored</p><p> Other views are on-the-fly derivations Consider the UI click volume</p></li><li><p>Copyright 2012 Edgewater RanzalCopyright 2012 Edgewater Ranzal</p><p>System Dimension</p><p>Value Dimension Can not directly modify this</p><p> points to entitys default currency</p><p> points to default currency of the entitys parent</p><p> Anything above must be Parent.Child format</p><p>Currencies Dont add currencies you arent using</p><p> Sets of calc status records for (every entity * every currency) Impact of loading metadata with entity or currency changes</p><p> Normally translate from the entitys currency only into its parents currency</p><p> Beware of non-default translations Impacted calc status</p><p> Data explosion</p><p> Adds to cycle time</p></li><li><p>Copyright 2012 Edgewater RanzalCopyright 2012 Edgewater Ranzal</p><p>User Controlled Dimensions</p><p>Entity</p><p> Sum of the data of the children</p><p> Avoid Consolidate All or All With Data on each hierarchy</p><p> Assign Adj flags sparingly</p><p> Dont disable if you ever had journals on entity</p><p>ICP</p><p> Hidden dimension</p><p>Scenario</p><p> Number of tables</p></li><li><p>Copyright 2012 Edgewater RanzalCopyright 2012 Edgewater Ranzal</p><p>Impact of Account Depth</p><p> Effect is multiplied when you consider the custom dimensions</p><p> Parent accounts dont lock</p><p>4- Net Income</p><p>3- Optg Income</p><p>2- Gross Margin</p><p>1- Sales</p><p>4- Optg Income</p><p>3- Gross Profit</p><p>2- Gross Margin</p><p>1- Sales</p><p>5- EBIT</p><p>6- Net Income</p></li><li><p>Copyright 2012 Edgewater RanzalCopyright 2012 Edgewater Ranzal</p><p>User Defined Dimensions</p><p>Custom 1..4</p><p> Think dozens or hundreds, but not thousands</p><p> If Thousands are necessary, 64 bit makes this possible</p><p> Rules remain a major factor in performance</p><p> Avoid:</p><p> Employees</p><p> Products</p><p> Anything that is very dynamic, changing greatly from year to year</p><p> One to one relationship with the entities</p><p>Configurable dimensions in</p></li><li><p>Copyright 2012 Edgewater RanzalCopyright 2012 Edgewater Ranzal</p><p>Metadata Efficiency Ratio</p><p>What does the average entity have in common with the top entity? Density measurement of re-use of the accounts and customs </p><p>across all entities</p><p>top entity</p><p>base</p></li><li><p>Copyright 2012 Edgewater RanzalCopyright 2012 Edgewater Ranzal</p><p>Metadata Volumes (Americas)80 Applications Median +1 Std Deviation High</p><p>Accounts 1,383 2,814 7,491 </p><p>ICP Accounts With Plug 17 291 2,273 </p><p>Accounts With Data Audit 26 1,356 7,490 </p><p>Consolidation Rules 45%</p><p>OrgBy Period 16%</p><p>Phased Submission 19%</p><p>Consolidation Methods - 3 16 </p><p>Currencies 25 57 150 </p><p>Custom1 177 3,248 23,897 </p><p>Custom2 67 2,397 20,484 </p><p>Custom3 46 919 5,681 </p><p>Custom4 19 184 1,199 </p><p>Entity Hierarchies 4 12 44 </p><p>Entities (unique) 672 4,242 21,199 </p><p>ICP Members 200 1,161 7,770 </p><p>Scenarios 10 27 81 </p><p>Scenarios Using Process Management - 6 37 </p><p>Scenarios Using Data Audit - 11 78 </p></li><li><p>Copyright 2012 Edgewater RanzalCopyright 2012 Edgewater Ranzal</p><p>Data</p></li><li><p>Copyright 2012 Edgewater RanzalCopyright 2012 Edgewater Ranzal</p><p>Whats a Subcube?</p><p> HFM data structure</p><p> Database tables stored by </p><p> Each record contains all periods for the [Year]</p><p> All records for a subcube are loaded into memory together</p><p>Parent subcube, stored </p><p>in DCN tables</p><p>Currency subcubes, </p><p>stored in DCE tables</p></li><li><p>Copyright 2012 Edgewater RanzalCopyright 2012 Edgewater Ranzal</p><p>Take it to the Limit</p><p>Reports, Grids, or Forms that:</p><p> Pull lots of entities</p><p> Lots of years</p><p> Lots of scenarios</p><p>Not so problematic:</p><p> Lots of accounts</p><p> Or Custom dimension members</p><p>Smart View</p><p> Cell volume impacts bandwidth</p><p> Subcubes impact server performance</p></li><li><p>Copyright 2012 Edgewater RanzalCopyright 2012 Edgewater Ranzal</p><p>Data Design</p><p> Density</p><p> Content Specifically: zeros</p><p> Tiny numbers</p><p> Invalid Records</p><p>Metadata volume is interesting, but its </p><p>how you it that matters most</p></li><li><p>Copyright 2012 Edgewater RanzalCopyright 2012 Edgewater Ranzal</p><p>Data Volume Measurement</p><p> No perfect method</p><p>Method How-To Pros Cons</p><p>Data Extract Extract all data, </p><p>count per entity</p><p>Simple, easy to see input </p><p>from calculated</p><p>Can only extract </p><p>FreeLRU Parse HFM event </p><p>logs</p><p>Good sense of average </p><p>cube, easy to monitor </p><p>monthly growth</p><p>Cant identify </p><p>individual cubes, </p><p>harder to understand</p><p>Database </p><p>Analysis</p><p>Query DCE, DCN </p><p>tables and count</p><p>Easy for a DBA, see all </p><p>subcubes</p><p>Doesnt count dynamic </p><p>members, includes </p><p>invalid records</p></li><li><p>Copyright 2012 Edgewater RanzalCopyright 2012 Edgewater Ranzal</p><p>Data Density Using FreeLRU</p><p> Survey of data density using FreeLRU methodNumber of applications reviewed: </p><p>44</p><p>Median Min Max +1 Std Dev</p><p>NumCubesInRAM 1,369 72 15,152 5,068</p><p>NumDataRecordsInRAM 1,170,908 247,900 23,019,754 4,574,074</p><p>NumRecordsInLargestCube 53,089 2,508 593,924 169,272</p><p>Records per cube 1,352 24 91,418 15,832</p><p>Metadata efficiency 3.4% 0.3% 39.7% 12.3%</p></li><li><p>Copyright 2012 Edgewater RanzalCopyright 2012 Edgewater Ranzal</p><p>Loaded vs. Consolidated Data</p><p> What percent of the loaded data is a zero value? </p></li><li><p>Copyright 2012 Edgewater RanzalCopyright 2012 Edgewater Ranzal</p><p>Growth Up the Entity ChainLevel Number Records</p><p>Top Entity 1 261,593 </p><p>Average Subcube 814 5,193 </p><p>Base entities including calculated data 516 680 </p><p>Base entities input data only 443 421 </p><p>Base 421</p><p>Average 5,193</p><p>Top 261,593</p></li><li><p>Copyright 2012 Edgewater RanzalCopyright 2012 Edgewater Ranzal</p><p>Loaded, Calculated, and Consolidated</p><p>Data Rough stats: median from 10 applications</p><p>Monthly Zeros %</p><p>Monthly </p><p>Growth</p><p>Rules </p><p>Growth </p><p>Loaded Records 153,826 4.1% 3.3%</p><p>Loaded + Calculated </p><p>Records 353,122 19.7% 2.7% 2.0</p><p>Consolidated Records 63,432 6.9% 3.2%</p><p> Total data for all base (or top) entities</p><p> Can be easily managed by better rule writing!</p></li><li><p>Copyright 2012 Edgewater RanzalCopyright 2012 Edgewater Ranzal</p><p>Invalid Records</p><p> Type 1: Orphaned records from metadata that has been deleted</p><p> Member is removed from dimension_Item table, but not from the data tables</p><p> These can be removed by Database &gt; Delete Invalid Records</p><p> Type 2: the member still exists, but is no longer in a valid intersection</p><p> Most often from changing CustomX Top Member on an account</p><p> These cannot be removed by HFM, but are filtered out in memory</p></li><li><p>Copyright 2012 Edgewater RanzalCopyright 2012 Edgewater Ranzal</p><p>So How Much Memory Do I Really Need?</p><p>Plan A Plan B</p><p>Number of entities 814 814 </p><p>* 2 cubes: entity currency + contribution 1,628 1,628 </p><p>Non-USD entities 483 483 </p><p>add another cube for parent currency** 483 483 </p><p>Entity_value cubes 2,111 2,111 </p><p>Actual 2011, 2012 2 2 </p><p>4 Currency scenarios, 3 Estimate, 3 Forecast scenarios 3 10 </p><p>Total Year_scenarios 5 12 </p><p>Total cubes 10,555 25,332 </p><p>Average records per cube 5,193 5,193 </p><p>Optimal MaxRecordsInRAM setting 54,812,115 131,549,076 </p><p>bytes per record 120 120</p><p>Records * bytes converted to MB = </p><p>MaxDataCacheSizeInMB 6,273 15,055</p><p>** Many entities are translated into other currencies as well, making this value low.</p></li><li><p>Copyright 2012 Edgewater RanzalCopyright 2012 Edgewater Ranzal</p><p>Rules Timing</p></li><li><p>Copyright 2012 Edgewater RanzalCopyright 2012 Edgewater Ranzal</p><p>Data Density Calc Time</p><p>correlation between density and calc times</p><p> Most applications are rules bound</p><p> -</p><p> 0.500</p><p> 1.000</p><p> 1.500</p><p> 2.000</p><p> 2.500</p><p> -</p><p> 100</p><p> 200</p><p> 300</p><p> 400</p><p> 500</p><p> 600</p><p> 700</p><p> 800</p><p> 900</p><p>Jan Feb Mar Apr May Jun Jul Aug Sep Oct Nov Dec</p><p>S</p><p>e</p><p>c</p><p>o</p><p>n</p><p>d</p><p>s</p><p>R</p><p>e</p><p>c</p><p>o</p><p>r</p><p>d</p><p>s</p><p>Average Rule Execution Time in Contrast with Data Volume</p></li><li><p>Copyright 2012 Edgewater RanzalCopyright 2012 Edgewater Ranzal</p><p>But Some Applications are I/O Bound</p><p>Time vs. Volume</p><p>050</p><p>100150200250300350400450</p><p>8</p><p>3</p><p>8</p><p>2</p><p>0</p><p>.</p><p>8</p><p>3</p><p>8</p><p>2</p><p>0</p><p>_</p><p>D</p><p>F</p><p>R</p><p>.</p><p>F</p><p>R</p><p>_</p><p>N</p><p>B</p><p>M</p><p>T</p><p>O</p><p>T</p><p>A</p><p>L</p><p>_</p><p>R</p><p>E</p><p>G</p><p>I</p><p>O</p><p>N</p><p>S</p><p>.</p><p>U</p><p>S</p><p>U</p><p>S</p><p>C</p><p>A</p><p>.</p><p>U</p><p>S</p><p>E</p><p>M</p><p>E</p><p>A</p><p>.</p><p>D</p><p>E</p><p>A</p><p>P</p><p>.</p><p>C</p><p>N</p><p>C</p><p>Z</p><p>.</p><p>C</p><p>Z</p><p>_</p><p>N</p><p>B</p><p>M</p><p>D</p><p>E</p><p>_</p><p>N</p><p>B</p><p>M</p><p>.</p><p>8</p><p>3</p><p>7</p><p>0</p><p>4</p><p>F</p><p>R</p><p>_</p><p>N</p><p>B</p><p>M</p><p>.</p><p>8</p><p>3</p><p>5</p><p>1</p><p>9</p><p>T</p><p>H</p><p>.</p><p>8</p><p>3</p><p>8</p><p>9</p><p>9</p><p>U</p><p>S</p><p>.</p><p>U</p><p>S</p><p>G</p><p>O</p><p>U</p><p>S</p><p>.</p><p>8</p><p>0</p><p>8</p><p>0</p><p>8</p><p>B</p><p>R</p><p>.</p><p>8</p><p>3</p><p>5</p><p>4</p><p>5</p><p>8</p><p>3</p><p>8</p><p>2</p><p>0</p><p>.</p><p>8</p><p>3</p><p>8</p><p>2</p><p>0</p><p>_</p><p>1</p><p>8</p><p>0</p><p>1</p><p>O</p><p>T</p><p>H</p><p>A</p><p>P</p><p>.</p><p>8</p><p>2</p><p>8</p><p>2</p><p>8</p><p>8</p><p>3</p><p>8</p><p>2</p><p>0</p><p>.</p><p>8</p><p>3</p><p>8</p><p>2</p><p>0</p><p>_</p><p>1</p><p>8</p><p>5</p><p>1</p><p>E</p><p>M</p><p>E</p><p>A</p><p>.</p><p>B</p><p>E</p><p>L</p><p>A</p><p>.</p><p>B</p><p>R</p><p>U</p><p>S</p><p>.</p><p>8</p><p>0</p><p>8</p><p>2</p><p>0</p><p>A</p><p>R</p><p>.</p><p>8</p><p>3</p><p>8</p><p>5</p><p>6</p><p>S</p><p>e</p><p>c</p><p>o</p><p>n</p><p>d</p><p>s</p><p>010,00020,00030,00040,00050,00060,000</p><p>elapsedtotalrecords</p></li><li><p>Copyright 2012 Edgewater RanzalCopyright 2012 Edgewater Ranzal</p><p>How Long Should Rules Take?</p><p> Total consolidation time for all entities, 12 </p><p>periods</p><p> Divide by 12 periods and total number of entities</p><p>0 0.25 2.0 4.0 10.0</p><p>Seconds Per Entity</p></li><li><p>Copyright 2012 Edgewater RanzalCopyright 2012 Edgewater Ranzal</p><p>Rules Impact Ratio</p><p> Total consolidation time with </p><p>rules</p><p> Divided by time with Blank </p><p>Rules</p><p> Typically 2- 5 times</p><p> More than that is an </p><p>opportunity for improvement</p></li><li><p>Copyright 2012 Edgewater RanzalCopyright 2012 Edgewater Ranzal</p><p>Reference </p><p>Application</p></li><li><p>Copyright 2012 Edgewater RanzalCopyright 2012 Edgewater Ranzal</p><p>Small but Constant Application</p><p> Applied across multiple environments</p><p>0:00:00</p><p>0:00:43</p><p>0:01:26</p><p>0:02:10</p><p>0:02:53</p><p>0:03:36</p><p>0:04:19</p><p>physical physical virtual virtual virtual virtual virtual virtual</p><p>HFM lab Cust E Ranzal dev T-61 laptop Cust A Cust B Cust C Cust D</p><p>Full Rules Blank Rules</p></li><li><p>Copyright 2012 Edgewater RanzalCopyright 2012 Edgewater Ranzal</p><p>Chris Barbiericbarbieri@ranzal.com</p><p>Needham, MA</p><p>USA</p><p>+1.617.480.6173</p><p>www.ranzal.com</p></li></ul>