How to Collect Budget Data Across 20-30 Dimensions Curtis P. Neumann, AT&T 1 Agenda • • • • • • Introduction Problem Initial Solution Final Solution Tips and Tricks Conclusion 2 Intro: Mergers and Financial Process Synergies • Mergers create opportunities to review financial processes and determine cost synergies. • Antiquated financial processes don’t meet the increasingly demanding and technology savvy financial users. • My role at AT&T Finance is to spearhead opportunities to use System 9’s capabilities to add value by converting outdated financial software tools and processes into “Best Practice” states. • Getting more granular detail to decision makers quicker allows them to make important operational decisions sooner, thus capitalizing on opportunities to grow profits. • Our budgeting and reporting systems are centralized into a group called MR2020 (Management Reporting 2020). 3 Intro: New Opportunities to Migrate off Finance “One Offs” • In many cases, acquired companies have similar processes as the acquiring company. – AT&T Inc. was formed in 2005, when "Baby Bell" SBC Communications Inc. purchased former "Ma Bell" AT&T Corporation. – Although Ma Bell had great products, there was less attention spent on best practice budgeting and reporting practices. – At Ma Bell, several financial processes were built on large MS Access databases and numerous Excel applications. – The “Single Version of the Truth” principle is important. • One-Off verses Central Reporting – Timing Issues – Definition Differences – Incomplete Data 4 Intro: Interested in Migration Projects? • This presentation deals with the specific need to migrate from a very sparse (detailed) data set in a relational database to Hyperion Planning, an OLAP planning tool. • A textbook design in Hyperion Planning won’t always accomplish this migration requirement, if the relational database has more than 20 dimensions. • I will detail an example of this instance. 5 Problem: Ma Bell Product Forecast Needs and the Legacy Process • The Ma Bell subsidiary that was merged into SBC in 2005 is now called the AT&T Business Solutions (ABS) Unit. • Various ABS organizational financial modelers need to forecast these products volumes and their characteristics in order to perform: – – – – Operations Expense Forecasts Capital Spending Forecasts Product Profitability Analysis A feed of ABS products into the AT&T Consolidated Budget Solution (CBS) –“Single Version of the Truth” • A “home grown” web tool connected to an MS Access database was the process used to gather, approve and report out on forecasted product volumes. This was the legacy process, called the ABS Volumes Database (VDB). 6 Problem: The Legacy Volumes Database (budgeting) Requirements • • • A process to gather volumes around the business was the primary need in order to provide data modelers input data to load their driver based models. The original budget volumes gathering process had the following objectives: – Users needed to load data to standard templates easily prepared in Excel. Provide different levels of security for administration related tasks. – A process to create high level target volumes that volume suppliers could use as a guide to submit lower level detail. – A template submission and approval process. – A repository that could hold very sparse data across 21 or more fields. – A reporting process that could summarize the volumes collected and provide lower levels of detail. – Overall, the process had to be fairly dummy-proof and repeatable every quarter. The process did not need to: – Run any calculations during the template data input task, except rollup of months to a total year column where necessary. – No immediate reporting of totals across templates. A process could be run manually to create total reports. 7 Problem: The Legacy Volumes Database (budgeting) Process • At the time the Volumes database was built, there were no licensed software programs that could meet all the requirements. • Therefore, an existing process management website was used in combination with a MS Access database. • Templates included: – Year, Period – Network – Scenario – Driver – Version – Speed – Template_ID (Enitity) – Period_Activity – Legal_Entity – Hand_Off_Group – Accounts – Template_Type – Common Bus Language (CBL) – Row Number – Region – Service_code – Business_Unit – NPSC - (Product Code) – Geo_Market, Sides 8 Problem: The Legacy Volumes Database Process (Figure 1) • To manage the submission process, a web-based (userinterface) budgeting process management tool was used to enable various forecasting tasks and reports. The process included: Web Based Process Management for Forecast Templates High Level Templates 3 2 Planners Submit Template Approve or Reject (emails) 4 Low Level Templates Targets 1 Blank Low Level Template w/ Targets Approved Low Level Templates VDB (MS Access) Forecast 1. The creation of high level 5 Aggregation targets to be submitted via high level Excel templates Pivot Table into a relational database Reports Supervisor Review (VDB repository). 2. The on-demand creation of 6 blank low level templates with high level targets. Figure 1. Legacy Volumes Database Collection Process 3. Completed low-level template submissions for 5. Approved low-level template loading to a approval. relational database (MS Access) for storage. 4. Supervisor approval or 6. Reporting of historical and forecasted data rejection paths with email utilizing static pivot tables in Excel sourced alerts. from the relational database. 9 Problem: Why a New Solution was Needed • After the Ma Bell merger, IT was reshuffled and the developer maintaining the solution was reassigned. This is a risk of “One-Offs”. – Because they are not part of a centralized set of tools that are maintained by a group of IT professionals. When the only developer maintaining the customized solution departs, all of a sudden the process becomes unsustainable. – In addition, this process was identified as a synergy project that needed to be halted to reduce expenses. • It took several people to maintain the templates, train the sixty users, change the MS Access scripts and tables, and maintain the website. This multi-vectored process significantly increased the cost to run the process verses a self contained off-the-shelf-solution. • As we studied the legacy process, it became apparent that there was a packaged solution that could perform the same functions. Better yet, we believed we could cut the cost by half and maintain a better process. That solution we recommended was Oracle Hyperion Planning 9.3.1. We started working on this solution in February 2008. 10 A First Attempt at a Textbook Hyperion Planning Application • By coincidence, the Oracle Hyperion Planning architecture resembled the legacy VDB process in that there was a web-based workflow management module and a separate link to a database. • Hyperion Planning is very similar, except that the data repository is a BSO cube, not a relational database. Figure 2 illustrates the high level system architecture of Hyperion Planning: Data Form Input Thru SmartView Submisson / Reporting Hyperion Planning Web Server Relational Tables Submission Data Form Reporting Oracle Tables Push O utline & Securi ty Planning Submit Essbase BSO Data Form Database Reporting Tier Figure 6 Figure 2. Oracle Hyperion Planning 9 Architecture 11 Essbase Scale Limitation • Having built several BSO cubes previously, my first concern was to test the calculation time of a BSO cube (plan type) with 20 dimensions, the allowed limit. • Before going to Hyperion Planning, we proceeded to build a BSO cube with 15 dimensions, including the NPSC (product) dimension which had 1,000 level zero members. • We loaded test data to the Essbase test cube and started a simple default calculation. Basically, we were testing the scalability of this very sparse (detailed) dataset by aggregating it. Did it finish calculating? We don’t know, because I stopped the calculation after three hours. • It became clear to us, that our outline was simply creating too many potential blocks. There were few optimization steps remaining after we set Accounts, Period and Year to dense storage settings. We were also using four processors, so that should have provided enough horsepower. 12 Multiple Plan Type Option • We did look at the idea of using more than one plan type (cube), because up to three plan types are allowed per Planning application. • But, that didn’t seem like a good idea when we thought about how the data forms would need to be set up. It would have been more work for the inputter to potentially have different forms. • Also, the plan types would need to be consolidated using partitioning. Trying this with a replicated partition using the same outline achieves little from the original approach. We would have ended up with lots of sparse volumes data in one cube with the need to roll it up. • We didn’t explore the use of transparent partitions either, because the retrieval times would have been unacceptable, if we could get it to work. 13 Initial Conclusion and Next Steps • Our initial conclusion was that we could not use Hyperion Planning for a “textbook” application that required more than 15 to 20 stored dimensions, depending on the number of members per dimension. • After realizing this, we needed some help. I called my colleague, Chris Werle of Key Performance Ideas (KPI). • He and his team met with me and came up with a very unique and innovative solution utilizing Hyperion Planning that captured data and reported it out into one cube with 21 stored dimensions. 14 Final Solution: Part I of II. Hyperion Planning’s Strength – Collection • • • • Hyperion Planning’s strength is data collection, if Smart Lists are used extensively. This was the main idea that made this project a success. The key here is to utilize the relational table relationships to the BSO cube to store the data intersections. The Smart List intersections are stored as values within the Accounts dimension. Given that capability, the number of possible data intersection combinations has no impact to the stored dimensions in the cube. All that is required are the required stored dimensions and one extra stored dimension we called Row Number. Figure 3 shows the outline in the administration console: However, with this design we could only load to level 0 members. 15 Final Solution: Part I. How does the data form look? • From a user’s standpoint, the form looks like a super model; – No navigation and no flipping of drop downs. – The forms in Smart View were prepared in advance for users to look very close to what the users had under the legacy process. See Figure 4. – The Smart List drop down functionality shown in column C makes changing the intersections a breeze. Normally, users would have to navigate a huge form when this number of dimensions (fields) are involved. 16 Final Solution: Part I. Plan Type Design with Smart Lists • • Looking at the plan type (cube) in Essbase, it definitely looks different. There is virtually no analytic capability available in this cube because the Smart List members are stored as values. Again, all we were trying to do for this part was to store the data, then report it out. For those that are curious about how that data looks, Figure 5 shows a retrieval from Smart View with a connection to the plan type directly, (i.e. direct connection to the Essbase server) thus, no translation of the Smart List ID’s back to the Smart List Labels. 17 Final Solution: Part II. Utilize Essbase for What It Does Best – Reporting • • Once the data was residing in the plan type (cube), we needed to build an Export/Transpose/Load (ETL) process to report it out. KPI advised us to flow the data from Hyperion Planning to an Essbase Aggregate Storage Option (ASO) cube. That translation as done in SQL server. – Utilized the Smart List flat files, used to load the Smart List members into Planning, to convert the data from Smart List ID’s back to the Smart List labels. • From here, we created an ASO cube that could easily hold 21 dimensions of data and loaded the translated (“ETL’d”) data to that ASO cube. See Figure 6. Figure 2 Export data to SQL SQL server convert Smart List ID’s to labels Load to 21 Stored Dims VDB ASO Reporting Cube Figure 6. ETL from Planning BSO cube to ASO Reporting Cube 18 Final Solution: Part II. ASO Reporting Cube • Although, this represents some ETL work, the beauty of this process allows for parents to be represented in the ASO cube. Therefore, we went from a flat structure in the Planning cube to a parent/child hierarchical structure in the ASO cube. Fortunately, this design worked well because there was no immediate need to report aggregated data in Planning. Figure 7 shows the ASO outline in the administration console. • The CBL dimension in Figure 7 has parents at level 1. These are not in the Planning load, but are not required. • It’s certainly possible to extend this beyond 30 dimensions, but I’m not aware of when that would ever be needed. Usually, budget data is collected at a higher level than actual data is reported. 19 Tips and Tricks • Loading Smart Lists from a Flat File – If any one Smart List is more than 100 ID’s, the manual entry process should not be used. With the 9.3.1 version we were using, after ID 200, the refresh time between ID entries took several minutes. So, KPI came up with some SQL loads to the Planning Enumeration table on the Oracle server. This was another fantastic idea and was supported by Oracle. I recommend that Oracle Support be involved in any back end loading. INSERT INTO [HP_???].[dbo].[HSP_ENUMERATION_ENTRY] ([ENUMERATION_ID],[ENTRY_ID],[NAME],[LABEL]) SELECT 6,1,'NA_BU','NA_BU' UNION ALL SELECT 6,2,'S002 ','BCS' UNION ALL SELECT ETC……………… List all 1,000 or more entries here…..GO • What about Budget Data Users that don’t have the Smart View Client? – For this project we were able to create pivot tables into Excel by using Hyperion Financial Reporting to extract the data from Planning and create a raw dump into Excel. Hyperion Financial Reporting converts the Smart List ID’s to labels, so that’s not an issue. 20 – This is a low tech but a practical solution. What Could be Done Differently • Try to Negotiate Fewer Dimensions with the Users • What About Using a Planning (BSO) Plan Type and Not Aggregating It? Some may believe that this could have been accomplished with a BSO cube because there would be no need to aggregate the Planning BSO cube anyway. Here are the following reasons why that would not be practical: – Forms would open very slowly. We’re talking at least several minutes. – Navigation to the appropriate intersections would be slow. Would require lots of scrolling down a large form or selecting page drop downs. – Cube would not be very compact. Lots or irrelevant intersections would exist. – We would have had to ETL this over to an ASO as was done in this solution anyway. • Sometimes different approaches work. We were looking for the least painful route. 21 Conclusion • This presentation drives home the fact that Oracle Hyperion tools are more flexible and scalable than the training classes and administration manual might suggest. • In the case of Oracle’s Hyperion Planning product, the relational table structure and the resulting Smart List capabilities effectively extend the scale capabilities of Essbase. • Look for the BSO to ASO integration capabilities in future versions of Hyperion Planning, as we are working with Oracle to better the product for use by larger customers. • See Appendix A for a diagram of the entire VBD process. • Thank you very much. You may contact me at [email protected] or 925/803-6144. If you would like a copy of my Hyperion Solutions 2006 presentation entitled “Hyperion Essbase at AT&T Long Distance: Utilizing Key Aspects to Run Finance in Sixth Gear”, please email me your request. • Key Performance Ideas contact info is as follows: Chris Werle’s Office: 408/960-7400 | Cell: 408/930-6061 or [email protected] 22 Q&A 23 Recent Updates 1. Moved most customer defined dims to SmartLists, leaving only 7 dims in the HP cube. 2. Thus, for analytics, add ASO cube to get populated with HP data. ATT – MR2000 Finance VDB Migration to Hyperion Planning 9.3.1 Design Proposed by KPI as of 4/10/08 Appendix A Development and Maintenance The remaining 14 custom defined “dimensions” will be stored in Hyperion Planning Oracle repository as This Hyperion Planning 9 solution will leverage both the relational and OLAP storage capabilities of the software to maximize a detailed data collection scope and performance. A total of 21 fields will be set-up. The six required dimensions plus the Row_Number custom defined dimension will be stored in the cube. SmartLists. The SmartList members will be keyed by hand. The cube dimension members will be loaded using HAL. HP Cube Dimension Loading (Task Owner: HP Admin) Dimension Load Flat Files Hyperion Application Link (HAL) Run HAL routines SmartLists Loading (Task Owner: HP Admin) Design and Maintain Web Forms (Owner: VDB Client) Sm artLists Security – Grant Access Website (Owner: Debbie Hutcher son) 1 BU 2 3 4 5 6 7 8 9 10 11 12 13 14 SmartList Flat Files (Outside of HP) Ref Tables Lo About 50 Planners will either use the HP WorkSpace interface or SmartView in Excel to load to the Web Forms. ad VDB Webforms for Planner Forecast Loading (Task: Planners) Table join m PER L of m load, ins anua te l key ad ing Di Web Forms and MR2K Actuals CBL Driver Geography Hand Off Group Legal Entity Netw ork NPSC Period Activity Region Service Code Description Sides Speed Tem plate Type Convert MR2K member names to SmartList Numeric ID’s for loading via Essbase Lock and Send. Hyperion Essbase will not recognize MR2K member names that are in SmartLists. The SmartList member names need to be converted (via SQL) to SmartList ID’s before loading in Essbase. ETL of MR2K Actual Data into VDB Join SmarList Ref tables to MR2K Actuals Data Reporting & Analytics FR maps the Smartlist numeric ID’s back to names. Hyperion Financial Reporting (FR) – Post Load (Task Owner: VDB Client) To maintain a consistent interface for the current 300 users of the VDB process, the VDB solution will port out a complete flat file dump into Hyperion Financial Reporting then saved to Excel. Cannot use SmartView into Excel because the SmartList members will be retrieve as numeric ID’s not the names of the members. This data will then be taken by the VDB client team and pivoted to create a set of pivot tables that exactly match the PMO process In the future, we expect that as users be comfortable with using SmartView, the pivot process will be replaced with simple retrievals of relevant data from an ASO cube, design specifically to meet analytical slice and dice needs. Table Join (Accounts, Time, Period, Scenario, Version, Legal Ent., Row #) pVDB.VDB App. Oracle Relational Repository (HP SmartList tables eg.) Data – SmartList ID’s Converted MR2K–Actual Run Rpt pVDB.VDB Cube Ex p Hyperion Financial Reporting Save as Excel and Create Pivots 300 Users or td ata SQL Server – Join MR2K names to Smartlist tables to get SmartList ID’s Export MR2000 MRVOL data Export HP cube, ETL data and Load to ASO for Analytics (Owner: HP Admin) VDB_ASO Cube Data SQL Server – Join Smartlist ID’s to Ref tables to get SmartList Names b4 loading to VDB_ASO cube Load All 21 fields become dimensions allowing for slicing and dicing on all fields used in HP. Notes: 1) Efficiencies could be gained by allowing a direct feed to the HP SmartList tables. For example, the Service Code Description SmartList table will contain 800 members. To load this via a direct interface with HP will take seconds. To key manually will take several hours. Also to maintain the SmartList tables, will also have to be done by hand including Geography, Period Activity, Hand Off Group, Driver, Network, Side, Speed, NPSC, and Template Type. = Table join 24