Essbase 11.1.2 New Features review
In my last post I gave a brief overview of some of the new features in EPM 11.1.2, specifically from an installation and environment point of view. Today I want to focus specifically on Essbase, and what is good, great and maybe unfortunate in the new release. This is by no means a comprehensive overview of all the new features, but rather my take on some of the more interesting aspects.
EPM security link – Essbase apparently does not store user details in the .SEC file, if running in EPM security mode. This means there is no need to synchronize security between Shared Services and Essbase any more. This is very good news, as this synchronization added an extra step which could be forgotten and meant security issues all the time.
Allocations on ASO cubes – You can now perform allocations on ASO cubes, previously this could only be done in BSO. One more reason to go ASO rather than BSO as a default? If that is not all, the next feature might convince you…
Custom Calculations on ASO – Basically this means you can now write calc scripts for ASO cubes. Not as fully featured yet as for BSO, for example you can only target level 0 cells, but a step in making ASO on par with BSO in terms of calculation capability. Will BSO eventually fall away…?
New @XWRITE function – the inverse of the @XREF function. During calculation you can write to data blocks in the same or other remote cubes (whereas @XREF pulls data from remote cubes). Very useful if you have distributed cube environment with data dependencies, as from a central calculation you can update all dependent cubes, rather than pulling data from calcs in each cube.
32 alias tables – Instead of 10 you can now have up to 32 alias tables. I guess this is good for some applications, I must be honest in all my years of using Essbase I have never used more than 6 alias tables in a single cube, and that was an extreme case.
IPv6 support – As all the other modules in EPM 11.1.2, Essbase also supports the new IPv6 internet protocol, which aims to allow many more addressable internet addresses.
OCI support – Essbase can now use OCI to connect directly to Oracle sources in load rules, rather than ODBC. It helps that Oracle is now the boss…
Monitor progress of data loads and dim builds – While data loads or dim builds are in progress, you can now query the progress, to get info such as which stage of the process is taking place, number of records processed and rejected, etc. This can be done if you kick off an asynchronous data load or dim build only. Very useful to track progress, something you previously couldn’t really do, short of continually refreshing the log file to get some idea of what is happening.
Error handling in Calc Scripts – Using the new @RETURN function you can exit a calc script with a custom error code based on results of an IF..THEN statement. This means you can add a lot more logic to error handling in calc scripts, and return meaningful codes.
MaxL Error Handling improved – You have an IfError in the MaXL Shell that can detect errors like syntax and no permission errors. You can therefore directly in the MaxL shell test for more complicated errors and exit with a return code, or continue processing. This should make error trapping and processing in MaxL scripts a little better than currently possible.
Export metadata to XML files – Another addition to the sorely lacking metadata export in Essbase has been added. You can now export metadata to an XML file, using a MaxL command (EXPORT OUTLINE). These XML files can then be used by other proceses where you need outline information, or to compare outlines. I assume at a later stage you will be able to import from these XML files?
Failover support – You could do failover for Essbase with High Availability Services, but they now made it possible through using Oracle Process Manager and Notification Server (OPMN). This offers comprehensive failoverand clustering support for Essbase.
This is some of the more important new features, there are more, if you want to read about these yourself you can access the EPM documentation at the following link:
How best to export Essbase outline information
Quite a few people have asked me recently how to export Essbase outline information from an existing Essbase cube. This might seem like a trivial exercise, but in reality is not.People want to export this information either to document outlines, or to make use of the Essbase metadata in other systems, so it is a valid request.
One reason why this export can be problematic is that Essbase does not store its metadata in a relational database, unlike some other OLAP systems. Instead the metadata is stored in a .OTL file which is not readable by text editors. The Essbase outline editor in Administration Services makes it very easy to view and maintain the outline, but for some or other reason Hyperion (and now Oracle) just never got around to including proper outline export functionality for Essbase.
There are a couple of ways to get outline data out of Essbase. The best approach really depends on exactly what information is required – do you need just the outline hierarchy with member names, or do you need extra information such as consolidation symbols, UDA’s and Attributes, formulas etc.
If you need just the outline hierarchies, I would recommend using the Essbase Excel add-in (or Smartview). Set the options to turn of navigating with no data and to drill to bottom level, put the dimension you want to export in the row dimension, and drill. You will now have all the members in hierarchical order. One issue is that the different levels are not placed in seperate columns, but all in one column with indents to indicate generations. If you want seperate columns, it is relatively easy to write an Excel formula which parses the number of open spaces and places each member in the right column.
If you need a more detailed outline export, the best way is to make use of a third-party tool, freely available, called the Essbase Outline Extractor (see URL below to download it). This tool has been around for a long time, and thankfully the people at Applied OLAP, who develops the brilliant Dodeca Essbase front-end, has taken ownership of this tool and keeps it up to date with the latest Essbase versions, which still making it available as a freeware download.
The Outline Extractor gives you the option to set exactly what you want to export, including items such as UDA’s, formulas, aliases etc., and then exports this to a text file. You can also automate this using a script file using command line functionality. You can get the Outline Extractor at the link below:
http://www.appliedolap.com/free-tools/outline-extractor
So until Oracle decides to include a proper outline extraction tool with Essbase, or makes the .OTL file readable (anyone heard of XML???), your best best is the third-party Outline Extractor, or doing something manual in Excel.
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.
What are my competitors using Essbase for (or: why do they know more faster)?
Essbase has a reputation of being an excellent platform for financial analytical applications, but not much else. In some part this perception is created by competitive vendors who benefits from pigeon-holing Essbase, so that their tools can rather be used. Also Hyperion were traditionally strong selling into finance departments, and neglected IT in the pure BI applications arena. While it is certainly true that certain types of financial uses hit the sweet spot for Essbase, it can definitely be used for a large number of other analytical applications beyond the financial arena.
Essbase offers a OLAP platform for the construction of basically any kind of analytical application. With the addition of aggregate storage functionality the possible uses for Essbase expanded exponentially. While very strong in complex calculations and financial-type functionality, Essbase suffered badly from multidimensional explosion if the number of dimensions or number of members in dimensions increased to very large numbers (more than 10 dimensions, more than million member dimensions). Aggregate storage addressed these issues and it is now possible to create cubes with large numbers of dimensions, and multi-million members in dimensions. All this while not loosing the intuitive and responsive end-user experience Essbase offers.
In this blog I would like to share some of the Essbase applications I have experience of beyond just pure finance. Hopefully this will provide a reference of other uses for Essbase to help people trying to sell Essbase, as well as people already using Essbase and thinking of ways to expand its usage. Note this is just a selection of some of the applications I have personal experience with, please feel free to comment on any other non-financial uses of Essbase you might have come across.
Telecommunications industry
- Customer usage analysis – a cube based on the call detail records (CDR’s) which allows analysis of subscriber usage by minute, region, package customer demographic. Helps to understand usage of packages and customer segments and plan for better focused products.
- Customer demographic analysis – analyze the customers by all demographic information, such as age, gender, income, region etc. Helps to understand the customer base and better target products.
- Customer profitability – understand which customer segments are the most profitable, and maybe more importantly which are not, and who to target for better structured packages.
- Network quality – a cube which get loaded with hourly raw data from base stations, and which then calculates a large number of key statistics. which allows for better analysis of the quality of the network, where problem areas are, and helps plan expansions an network reconfigurations.
Motor vehicle manufacturing industry
- Market share analysis – use publicly available data to build a cube which shows all car manufacturers market share, by all vehicle attributes. Helps to determine where strong and weak points are, and support marketing efforts.
- Car flow planning – plan the flow of vehicle construction from parts to vehicle stock yard, based on demand forecast. Handles complex issues like ensuring optimum stock levels, planning for enough raw parts to meet demand, and the flow tempo through each segment of vehicle construction.
- Sales analysis and planning – analyze the vehicle sales by region, dealer and vehicle type, as well as customer demographic, to better understand the market and plan distribution to the dealer network.
Banking industry
- Customer Profitability analysis – understand profitability per individual customer (in the millions, this is an aggregate storage cube) to see which offerings actually make money, and across which customer segments.
- Market Share analysis – use publicly available data from banking regulators to understand market share in a large number of banking instruments, such as loans, mortgages, deposits etc. Helps to quickly identify growth areas as well as areas with declining share, and to plan better products.
- Risk Scoring models – use Essbase to calculate the risk for corporate customers based on quantitative (financial statements) as well as qualitative (perception of the company, market strength) factors. Essbase calculates key ratios, scores, and risk ratings based on a complex set of rating business rules.
- Exposure analysis – understand the exposure the bank has across various instruments and clients, to help early identification of risks and better plan investment activity.
- Funds Transfer Pricing modeling – perform complex FTP modeling in Essbase, where the flow of cash within the bank based on complex rules is calculated.
- Cash flow forecasting – model cash flow on a daily basis based on surplus and shortfalls, using treasury rules to determine investment options and movements of cash.
Energy utility
- Demand forecasting – use Essbase to forecast energy production and usage demand based on various factors such as seasonality, historical usage, expected growth factors etc. Use as a basis for revenue planning, as well as production plans.
- Environmental analysis – understand the environmental impact of energy generation based on models in Essbase. Track emissions and pollution factors per generating unit over time, and serve as early warning system.
- Production/generation analysis – cube which houses production of energy per generating unit to serves as basis for dashboards used at board meetings to discuss generation issues and forecasts.
Insurance industry
- Broker performance – a cube which provides daily updates of each broker’s performance compared to their peer group, based on sales per product, region, demographic. Used as a basis for broker commission calculations, and to run broker competitions to award top-performing individuals.
- Customer profitability – understand profitability per customer and customer segment across all types of insurance products.
So as you can see, the usage of Essbase spans way beyond the pure financial reporting and analysis applications most companies use it for. If you would like details on any of the applications above feel free to contact me, details below.
To read more on Hyperion Essbase go to the following URLs:
http://www.intellient.co.za/live/content.php?Item_ID=1157
http://www.oracle.com/appserver/business-intelligence/essbase.html
