Hyperion Financial Management Application Design for Performance

  • Published on
    13-Sep-2014

  • View
    10

  • Download
    2

Embed Size (px)

DESCRIPTION

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.

Transcript

<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 11.1.2.1</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 11.1.2.2???</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>