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.
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
