Without fail, every project I have ever worked on includes a requirement for input schedule or report performance.
"30 seconds refresh time"
"15 seconds submission time"
"5 seconds, do it all... tops"
"I want the data to appear before my laptop boots up"
These are all standard requests that we attempt to meet. To understand how we meet these requests it is important to understand the technical components of refreshing both BPC input schedules and reports and how different SAP upgrades will influence your performance. Each refresh includes the following steps:
- Produce a query for each defined report within the worksheet or workbook.
- Retrieve relevant data from Database.
- Execute non-data EPM functions and formatting.
- Execute custom VB.
- Process Excel formulas and conditional formatting.
Both BIA and HANA have been shown to significantly improve steps 1-3, but items 4 and 5 happen distinctly after BPC functions have concluded. What does this mean? Our performance will ultimately be tied to front end functions we use to customize data displays, user proof or dumb down input schedules, VB functions we use to circumvent standard excel or BPC functionality, or visual enhancements that use conditionally formatting for.
Given this technical landscape, it is important to balance performance requirements with functionality requirements. Use these tips to drive your appropriate performance:
- The first rule of thumb is easy: The more data your try to pull the longer an expansion will take. This is no different from going to iTunes and downloading a song vs a movie. Data grows as you add dimension members, nest multiple dimensions together create multiplication rule factors of intersections, or simply add columns to your front end object. Adding potential data intersections enlarges the data that your schedule has to identify, process, filter, and display.
- Reduce Excel Overhead
- Minimize lookup functions: How many requirements will require text displays of numeric data, drop down lists, analysis of nested intersections, in sheet time shifts corresponding to user date inputs. These functions often require excel VLOOKUPs, HLOOKUPS, or INDEX functions which notoriously take time to crunch data once the pure data is retrieved.
- Conditional formatting ultimately slows refresh times. These are functions that will run after all other functions have run and they tag on additional "refresh" time in the eyes of users.
Learn, grow and expand your BPC interests by registering for one of our upcoming webcasts!