Financial Reporting optimization and time-saving tips
Hey everyone
The following blog is about Oracle Hyperion Financial Reporting and is based on my experiences with the product thus far. Hopefully, these few tips will help you get the most out of Financial Reporting and will allow you to optimize not only the processing time of the reports themselves but the amount of time spent in creating them.
1. Become familiar with your outline hierarchy or model structure
I’ve had the privilege of working with FR connecting to both Essbase and HFM (Hyperion Financial Management) databases in my short career. HFM cubes generally tend to look similar as they have the same 12 dimensions with generally similar content. For Essbase, outlines differ from client to client and industry to industry and so understanding the structure might take a little more time. Knowing your structure allows for quick and efficient Member Selections on reports and learning member names by heart can save an enormous amount of time, as you can type out the member name into the formula box and then click the Tick sign, instead of sifting through the member list in the Member Selection box:
2. Use the available Functions when selecting members
There are functions within Financial Reporting which are specifically designed for multi-dimensionality and more importantly to be dynamic! For instance, using the Children function when selecting members from the hierarchy will ensure that all sibling members are accounted for within the selection even if an additional member were to be added. Of course, if members were chosen one by one on the report, an additional sibling member would have to be added manually. When outlines are huge and many changes are made regularly, this can become an extremely cumbersome task. Another advantage of Functions or choosing members in a single line of a grid, instead of using multiple lines is that it greatly improves retrieval time. It should be noted however that the drawback of using the Functions or choosing members in the same line is the assumption that all members within the selection would have to have the same formatting applied to them.
3. Use Grid and Chart templates
When creating a large number of reports, it is advisable to create a few templates for which the Dimension selections of the Grid or Chart stay constant. To create a template, simply right-click on the Grid or Chart that you feel you might use frequently and click on “Save Object”. When creating a new report, this saved object can then be inserted by clicking on “Insert” at the top of the screen, followed by a click on “Saved Object”. See the screenshots below:
Followed by:
Due to space considerations, I will continue to add tips in follow-up blogs. My next post will focus on a few ways to make the formatting of a report a little easier. Please be sure to comment and also let me know if there’s anything specific you’d like me to blog about.
Aaron
Intellient is the performance management and business intelligence company to connect with.
Why does every financial department need Financial Data Quality Management?
If I had a dollar for every time a finance professional asked me this question I’d be a very rich man! Luckily the answer is easy to explain since every organisation has the same problem with data, managing it productively and easily comes at a price and is not as simple as 1-2-3.
In general data quality is a must have. The value of consolidation, reporting, planning and analytical applications is eliminated if the data that comes in is “Bad Data”. Companies must avoid the garbage in garbage out trap by eliminating manual data handling wherever possible and providing error identification and notification along with a vehicle to investigate and correct errors. Any ETL or Integration tool can put data into a waiting application, but quality issues arise when there are problems with the data and errors need to be addressed. FDM helps companies deliver the best process for achieving data quality reporting by delivering:
I. Data Quality
· Mapping Validation- provides immediate mapping error identification and notification as well as a vehicle for quick and easy mapping correction.
· User Defined Data Quality Checking- applies rules and restrictions to the load file ensuring corporate receives the highest level of quality.
II. Data Accuracy
· Read any ledger report, file or even some ERPs in any format eliminating the need for manual data manipulation and data keying thus improving accuracy and shortening the data collection process.
· FDM delivers easy reconciliation from the ledger via dynamic drill-back to source, whether it’s a file, spreadsheet or ERP system.
III. Data Consistency
· FDM can provide visibility to mapping consistency across all locations.
· Drill into any target account and see every source account from every location.
IV. Audit Trail/Log and 100% Process Transparency
· FDM delivers a vehicle to investigate any material discrepancy with 100% transparency to the data translation and mapping and a log of all new, changed or deleted map entries giving finance the tools needed to find the needle in the haystack quickly and painlessly.
Process Improvements
Flexible GL importing – Because of FDM’s powerful importing capabilities, you can read in any source system or trial balance report that is printed to a file. Alternatively you may load spreadsheets or even connect to a growing list of ERP systems direct. FDM will simultaneously improve throughput and accuracy by reading your GL file in a pure format. Bottlenecks are reduced and hard IT costs are eliminated.
ERP Integration – As from 2009 FDM users can now also connect FDM direct to their ERP, including Oracle E-Business Suite GL, PeopleSoft GL and SAP Financials. This allows for adapter-based data transfer and mapping, with drill-back to source from the WorkSpace, Financial Reports and SmartView for Excel as standard.
Powerful Mapping – Power and flexibility to handle the most complex mapping rules such as ranges, globals, wildcards, conditional mapping and double sided wildcards. FDM allows end users to easily map new accounts on the fly eliminating bottlenecks and opportunities for errors.
Data Validation and Error Prevention– FDM is best practice for loading data that reduces bottlenecks in the closing process while eliminating chances for errors. FDM forces end-users to validate files prior to loading into the Hyperion target application.
Meta data Management– With the release of the FDM ERP Integration module FDM users may also load meta data from their source ERPs (Oracle E-Business Suite GL, PeopleSoft GL) to Financial Close and Planning applications.
Data Check Reports–Force end-users to view and pass data validation rules that corporate has enforced to improve the quality of data provided by the locations.
Supplemental Journal Entries and Multi-Period Budget Data Loading – FDM can read and interpret supplemental information or budget templates produced in Excel. IFRS Adjustments, PP&E Roll forwards, Headcount, and Multi-period Budget/Plan information can be loaded quickly and easily with the same audit trail accountability. Standard templates can be used month after month and load in seconds. All supplemental data is subject to process visibility and a complete audit trail.
External Auditor Time Savings- Initial data from auditors suggest FDM can eliminate multiple weeks of external auditing time due to electronic linkage of source trial balance data to Hyperion. Support for all auditing questions from end-users to administrators is stored centrally in FDM and is easily accessible with standard reports.
Flexible Acquisition Tool – FDM has flexibility and adaptability to change as your business changes. New requirements and new business rules can easily be accommodated. Acquisitions can be converted into Hyperion in hours rather than weeks. The scripting capabilities allow FDM to adapt to any changing requirements that may arise.
The elimination of manual manipulation, ease of use, audit trail, data validation, consistency of mapping, and 100% transparency achieved with FDM is necessary for companies to simultaneously shorten the close cycle and achieve data integrity.
If you’re not already using Financial Data Management to manage your Hyperion data, what are you waiting for?
Intellient is the performance management and business intelligence company to connect with.
How to make Essbase Aggregate Storage member formulas faster
Ever since the Aggregate Storage option in Essbase (hereafter referred to ASO for brevity (and my sanity) sake), I have been using it quite extensively. Except when really serious calculations are required, I find ASO cubes handles everything faster, from data loads to calculations and retrievals, I can build cubes with much wider dimensionality and more members, and it has a lot less optimization complexity than block storage.
However one drawback with ASO is that in some of the more complex ASO cubes I found that retrievals could be quite slow. This is because members with formulas are always handled as dynamically calculated in ASO, unlike BSO where you can specify a calculated member to be stored. Usually this is not a problem, but if you have a very large cube, and a relatively complex piece of MDX code as a member formula, and you do a large retrieval, performance can be quite bad.
Because ASO does not offer many option for optimization, I have usually found workarounds for these cases, either by making the formulas less complex, or using much more focused retrievals. However recently a colleague of mine alerted me to a MDX function which can, in the right cases, make these type of retrievals much, much faster. So thanks to Johan Hammes, all the credit for this goes to him for alerting me to his functionality.
So what am I talking about? Two functions in MDX called NONEMPTYMEMBER and NONEMPTYTUPLE. And yes, they do appear in the Essbase help files (look in the Technical Reference under MDX Optimization properties), so I must admit I broke my own rule about first consulting the manuals.
So how do they work? Basically you put them as the first line in a member formula (of course you can use them in MDX scripts as well, but in this post I will focus on member formulas). As an argument you specify either a single member or member list (for NONEMPTYMEMBER), or a cross-dimensional member specification (for NONEMPTYTUPLE). This basically acts as a filter, which checks the specified member or member combination, and if it is empty, it stops execution of the rest of the member formula. So is almost acts like an IF statement where you test a member and just do nothing if it is empty, however it seems to speed up things a lot more than using a IF. An example of how this function looks is as follows, assume all the quoted text is a member formula:
NONEMPTYMEMBER([Sales])
SUM(Children([Entity].CurrentMember),[Sales])
So if Sales is empty, the SUM is not executed.The challenge with these functions is to choose the right member(s) for the filter, but it could make a huge difference. In one case where this was used, a large retrieval which took 10 minutes now takes 2 minutes, with the only difference being the usage of the NONEMPTYMEMBER function.
Intellient is the performance management and business intelligence company to connect with.
How to best do YTD calculations in Essbase
As we all know, Essbase is unmatched when it comes to performing complex calculations. I sometimes think the problem when doing member formulas or calculation scripts is not so much whether Essbase can actually do some horrendously difficult calculation, but rather choosing one of the various ways Essbase can do the task.
One example of this is something conceptually extremely simple, namely doing YTD calculations in Essbase. Before I go any further, I have to state that the built-in dynamic time series functionality in Essbase has never really done it for me. I prefer to create specific YTD members in Essbase, as I find that makes the usage in reports and analysis much easier.
So if we focus on YTD members, the question now becomes where do we put them? As these are related to the time aspect of your cube, it seems logical to plonk them into the time dimension. This is the route some of my colleagues prefer, I however do not like this, as I prefer to minimize maintenance of formulas. Assuming you have Month as your most granular time dimension level, then if you put YTD members in your time dimension, you have to create 12 new members with a member formula for each of them. And assuming you don’t make use of a separate Year dimension, but have all years in one time dimension, then you have to create a duplicate of all your months, each with their own YTD formula.
This approach obviously does not lessen maintenance. What it does offer though are easier formulas, because you specifically write a formula for each month, so you know exactly the logic for that month. In essence you hard-code the YTD logic for each. This is something else I don’t like, anything leaning towards hard-coding in Essbase probably means you are missing a more elegant, simpler solution.
The approach I like to take with YTD is to create a new dimension, let’s call it Periodicity for arguments sake. Assume for this example that years and months are in 2 separate dimensions, as you will see this approach will work even if they are not. Also assume the input data is monthly, and Essbase needs to calculate the YTD values. Then the Periodicity dimension will have only 2 members, Monthly and YTD. Monthly will be the default input member and will be stored, while YTD will be a dynamically calculated member (it could be stored as well for query performance sake, but to prove this approach we will make it YTD for now). The Periodicity member itself will be Label Only. This means the new dimension has only one stored member, and therefore will not increase the size of the cube. In addition you probably want to make it a dense dimension, especially if Time is a dense dimension.
So the issue now becomes what should the formula for YTD be? Do we have to test for each month in an IF statement and hard-code the formula per month? Definitely not, remember hard-coding is preferably to be avoided. If possible the same formula should be used across all months, irrespective of where in the year.
Luckily Essbase has some fancy built-in formulas which will make this easy, in this case specifically @CURRMBRRANGE provides the magic key to do this easily, in conjunction with @SUMRANGE. Assuming the Time dimension is called Periods, the months are at level 0, and the year dimension is called Years, the formula is:
@SUMRANGE(“Monthly”,@CURRMBRRANGE(“Periods”,LEV,0,,0));
This will always calculate the sum of months starting with the first month in your time dimension, and ending with the current month being calculated. This works because of the startoffset and endoffset settings for @CURRMBRRANGE. The ” (null value) for startoffset means start the range at the first member in the list, and the ’0′ for endoffset means stop at the current member being calculated.
The main benefit of doing YTD in a separate dimension, apart from the easy maintenance of just one formula, is that it makes it extremely easy for an end user to switch between YTD and Monthly, or do a YTD vs Monthly comparison. The user does not need to select specific YTD month members, they only need to select YTD or Monthly from the Periodicity dimension.
Managing your financial challenges with Enterprise Performance Management (EPM)
Remember when you were a kid and you were scared of ghosts and monsters or the boogeyman hiding in the closet? It’s funny … As we grow up, most of us lose our childhood fears, but all of us will certainly acquire new ones. Our mature fears tend to fall well within the bounds of realism and deviate from the realm of the impossible (although some grown men and women might still be afraid of Casper for all we know).
What scares us most often is that which CAN happen, that which IS possible – for instance, the loss of a loved one, loneliness, disappointment, inadequateness or the more tangible phobias such as heights or small spaces or having to live with the huge tarantula that shows up every few days for but an instant, only to disappear just as quickly, thus causing you to watch your every step.
As much as the above fears are apparent, there is one that is consistent with most of us, if not all of us – the fear of losing or not having money! According to British scientists, losing money or experiencing an economic loss (i.e. financial pain) has the same neurological roots to physical pain.
From a business or corporate or enterprise perspective, this “pain” is aggregated through the encompassment of fears of its employees. As an employer, how do you curb these fears? Well, the answer is fairly simple – you give them the assurance of a safe and secure future at your enterprise just as a parent would assure their child a pleasant night’s sleep by tucking them in or leaving the light on until they’ve fallen asleep.
In order to be in a position to offer these assurances however, your enterprise needs to make a profit. This is the bottom line to every business. The biggest profits are realized when every process, every individual, every team, and every department are working together toward that uniform goal. Managing this synergy can be a cumbersome task for any employer … but not anymore! Enterprise Performance Management (EPM) systems are here to help keep those business demons hidden in the closet for good!
EPM systems are a specific tier of the Business Intelligence (BI) toolset. Managers can now assess the performance of every component of their enterprise by understanding and analyzing where they’ve come from and where they currently stand (i.e. using the historical and current data of the business). Once this is done, a link can be formed between these operational activities and a business strategy that increases future profits. This is the goal of Performance Management – it gives you insight into the future so that you can cure your employees’ and consequently your enterprise’s fears of the present. Go ahead and turn your business’ lights on by investing in an EPM tool – knowing is always better than not knowing!
“I am not afraid of tomorrow, for I have seen yesterday and I love today” – William Allen White
For more information on Oracle Hyperion Enterprise Performance Management products, see http://www.intellient.co.za/live/content.php?Item_ID=1142
