Altius Consulting has a long heritage of developing Essbase and Planning solutions and for this reason occasionally have cause to help clients upgrade or migrate a bespoke planning system built in Essbase, to a full installation of Hyperion Planning. The driving force behind this article is that I ran into some problems trying to import an outline from flat files into EPMa, for use in Hyperion Planning.  The files were exported from Essbase using the OLAP Underground outline extractor and despite the formats appearing to match the documented requirements, after correcting some obvious faults, it just didn’t want to work. Previous versions of EPMa have been fairly unstable and many experienced Hyperion practitioners have advocated avoiding its use for the time being but the current release is much improved and we were keen to use the functionality that was available, so after a disappointing experience with the flat file, I thought I would try the Interface Tables route instead.

The first thing I realised was that when my installation of Planning was installed, the data source had not been created, the documentation regarding this appears to be a little misleading, suggesting that there is a direct menu link from Foundation Services to EPMa data source configurator, but I couldn’t find it, so I used the standard config option:

Launch EPM System Configurator.

Check Hyperion Foundation>Performance Management Architect, click Next.

Create New Datasource Link, click Next.

Select database type, click Next.

Enter your database credentials and a name for the data source, check the ‘Create Interface tables’ box, click Next.

The next page shows a task list, click Next to begin the process. Once the process has completed, indicated by a green icon, click Finish.

Check your database and you should have a number of tables with an ‘HS_’ or ‘IM_’ prefix. The IM tables are system tables, they do the following: IM_Load_Info – logs loads and updates; IM_Dimension – holds dimension definition data, like the !Dimensions section of a flat file; IM_Dimension_Association – is equivalent to the similarly named section of a flat file and is also used to associate attribute dimensions with base dimensions.

The tables beginning with HS_ are the tables that related to the specific model used in Planning, in earlier version s there was a basic set that could be used as templates, in version 11.3 the selection is much greater and four tables are created for each of the ‘standard’ Planning dimensions. Of each of these sets of four, HS_Dimension_Hierarchy and HS_Dimension_Member are required.

EPMa doesn’t include any tools specifically to help with loading these tables, so your favourite SQL tool will become a good friend, or custom build a process in ODI or whatever ETL tool you happen to have.

Today’s meeting of the UKOUG (UK Oracle User Group) BI special interest group was a little unusual in that three of the four presentations were delivered by Dutchmen.  First off Emiel Van Bockel reprised his award winning presentation on his BI project at his employer, Centraal Boekhuis, the Netherlands largest book distributor.   Like so many Dutch Emiel speaks excellent English and his project is genuinely interesting using as it does a normalised data warehouse for large volume analytics, and also including an element of BI as a service, delivering supply chain metrics to Centraal Boekhuis’s customer base via a portal.  Emiel’s BI and business philosophy is also very interesting.

Next up was a preview of some of the new features of Oracle Warehouse Builder from Ragnar Wessels, this was another interesting insight into Oracle’s ‘fusion’ tool set, using as it now does, the familiar interface layout of JDeveloper and SQL Developer.  Although this is likely to be last release of OWB under that name, it’s worth getting familiar with the layout as it is the ‘go forward’ style for Oracle tools.

Thirdly, and occupying the ‘siesta slot’ after lunch, Chris Stanley gave us an insight into the challenges facing him and his colleagues as they try to modernise the BI practices of Network Rail.  Even delegates from large commercial enterprises would have been surprised at the scale of the task I think, running 18 major stations, looking after tens of thousands of decaying fixed assets including 40,000 bridges alone, not to mention HR metrics for 35,000 staff is no mean feat. Add to this the pressure to reduce costs and they have got their work cut out.

Finally, the third Dutchman of the day, Sjaak Vossepoel of Oracle, aided by Craig Stewart, gave a us an overview of ‘Golden Gate’, the real time data reporting tool Oracle acquired a few months ago.  I did know anything about the capabilities of this tool or how it worked so this was a very interesting presentation, it had never occurred to me that one could ‘extract’ transactions from a system with out actually querying the OLTP database, and thus it’s overhead on the transactional system is minimal.  As Golden Gate was a niche player in this market and successful enough to be acquired by Oracle, I guess not many other people had realised this capability either.

All together a very interesting day and an opportunity to meet some new faces, if your business is an Oracle user and your not a member of UKOUG visit  www.UKOUG.org for more details.  You can meet the Altius team at the EPM and Hyperion annual conference beginning 16/06/2010, and the next BI special interest group meeting is scheduled for the 16/09/2010, although there is possibly an extra date to be confirmed if there is demand enough for it.  The user group is a good place to here hands on technical overviews but also to here real world customer stories and experiences, and they are always looking for new volunteers to share their experiences with the community at large.

A note of caution today for anyone using Essbase and OBIEE in an environment where the Essbase cube design is changing and those changes need to be reflected in OBIEE.  There are several ways to add new dimensions and members to the representation of the cube in the repository, the simplest being to manually create the new objects.  My good friend Christian has already blogged about how to edit the UDML to overcome a bug which causes problems with level enumeration when using the manual method. I think I may have found another issue; If a newly added column is subsequently used in a query filter, this results in an error of the '[nQSError: 46008] Internal error: File .\Src\SQ......' kind, very similar to those some people have reported when using direct database requests.

However, I have found that these errors after adding members can be eliminated if the new dimension/members are added following these steps:

use nqudmlgen.exe to create the UDML for the rpd layers relating to the particular data  source you have added to, save the code in a single txt file;

delete all three layers of objects, then save the rpd and close;

then use nqudmlexec.exe to create a new rpd based on the original plus the amended UDML file.

I can only assume the reason this worked is that the udmlexec.exe rebuilds things more gracefully behind the scenes and the internal error is thus avoided, or it could be that I suffered some other as yet unknown issue.  It would be interesting to here from anyone else who has had similar issues?

Last week I attended the latest in Oracle’s ‘Unplugged’ series of partner days, a ‘BI Architecture Masterclass’. As Oracle partners it important for us to keep up to date with the messages they want to put out to the market about their products, unusually this day turned out to be a product and vendor agnostic day, and set out to explore a list of common aims and pitfalls encountered in enterprise sized BI projects, this was quite refreshing as I had expected more of a focus on BI Apps and using OBI on other sources.

Looking at the wider picture made things a lot more interesting; the first suggestion of the day to draw ‘oos’ and ‘ahs’ from the audience being a suggestion to replicate OLTP sources into a staging area and then to use SQL for change data capture to lessen the impact on live systems, rather than doing selective extraction with an ETL tool. An added advantage of this approach would be that the copied data would also effectively be live data and suitable for reporting, again without impacting the live system. Both these reasons are completely valid of course, but the reaction from the more experienced members of the audience suggested there may be a few reasons why this approach may be difficult to sell to a prospective client. Once the data from the primary sources has been captured, data from other sources can then be brought in and federated with the primary system data in a ‘Foundation’ layer using a 3nf database (more oos and arghs and much debate). Finally, this data can be pushed into an ‘Abstraction’ layer of star schemas and/or OLAP cubes as appropriate to your reporting tools.

The most radical suggestion of all was the use of a custom interface to load data to the target tables in the 3nf database, an example cited was a coded interface to generate dynamic SQL to load these tables. This API has the advantage of being a single place to add code to handle additional tables and columns that might be required, rather than editing multiple mappings in an ETL tool.

The pros of this approach are quite convincing: reduced impact on live systems, work load pushed down to database level in easily maintainable SQL; an extract/transform layer that’s easier to maintain than many multiple mappings; a federated data layer that’s more quickly available to end users than waiting for the final reports, dashboards or whatever.

The cons of the approach appear to me to revolve around two major assumptions made: disk space is cheap and your client’s IT department will provide adequate I/O capability to service that disk; and the client will have the in house skills to maintain the custom interface for changes in the data structure.

I think the reality most people will recognise falls somewhere between this approach and the OOTB pre-configured BI Apps with which most Oracle BI Developers will be familiar, largely due to the size of customers most commonly encountered. In the SME market place, implementing one or two flavours of BI Apps may well form the core of the clients BI strategy so adapting the ideas above is likely to be more appropriate, the source feeding the bi app becomes the primary data source and of course the extraction of this data is handled by the preconfigured ETL, that’s what you’ve paid for. So where do those additional data sources fit in? How can you federate data from your legacy system that can only output data in text files? And where in either of the above scenarios does you Hyperion Planning application fit in?, being as it is a data entry point, a data consumer and a data source for reporting all in one? Hopefully you’ll ask Altius for the answers.

In my last blog I referred to the idea of the OBI developer using Essbase tools to their advantage in diagnosing problems and improving their knowledge of where the data they are reporting comes from. Smart View, the tool I suggested using in that blog is great when one knows what to look for, but what if you haven’t got that far because perhaps the list of required members is unclear or ambiguous.

For this type of research there is only really one place to look. The Essbase administration console does what the name suggests and provides access to administer the cube structure as well as other routine maintenance options.

If Essbase is installed on the machine you are working on you can access the console from All Programs>Essbase>Administration Services>Start Administration Console or more likely you will access it via a URL. Once logged in you will see some thing like this.

Expand ‘Essbase Servers’ and then your server to see the available applications.

Applications are instances of Essbase, each application may hold one or more Essbase databases (cubes). Drill down into the application and database until you see ‘Outline’, the outline is the structure of the cube and holds all the dimension members along with any attributes and characteristics applied to them, to view it, right click and select view. Of particular importance are the consolidation operator in brackets after the member name, (~), (+) or (-) being the most common; these control the way the values associated with the members roll up to give the dimension total. Also within the properties of the member (r.click View Member Properties) is the member formula which can be used to create a formula to generate the member value.

For now though we will limit the scope of this exercise to finding members, the ‘Find Members’ option is available from the context menu.

The Find Members dialogue has a number of options, as an OBI developer you are most likely to be searching for a member alias as that is what is exposed in OBI, so you need to remember to select an alias table. Now to set the search criteria, a slight flaw here is that the name you are searching for cannot start with a wildcard character.

Your Results are returned.

To view the member in the outline right click and select ‘Show member in primary tree’, this will show you where the member is in the outline, hopefully from this you will be able to tell if you are using the correct member in OBI.

I was clearing down my hard disk when I stumbled across this note I had made for my self regarding help files added to dashboard pages. I haven’t seen these widely used which I think is often a missed opportunity, it has to be the easiest way to get content or page specific help in front of your users.

In case you haven’t seen this functionality in action, out of the box it uses the ‘Help URL’ box in the title view of an answers request, when the appropriate URL is added the question mark icon appears at the right hand end of the title view when the request is published. These notes explain how I implemented this when using WebLogic.

Create a folder to store the html files, I did this within the folder to which I exploded the analytics.war file during the deployment to web logic. There is no need to change or restart the services after adding or updating the pages, just bare in mind pages may have been cached.

Add a /../ to the fmap path to avoid the system looking for a folder called ‘Missing_’.

It appears that this is the only location to which the files need be copied, it works in design view in answers and in dashboard pages.

Using Smart View to help troubleshoot your Answers query.

When one starts using Essbase as a data source for OBI things can get a little confusing, I have previously offered my own explanation of how an Essbase cube functions and how this related to the star schemas with which OBI developers will be familiar but from time to time it can still be frustrating if the results of your answers query aren’t quite what you were expecting. To get to the bottom of these puzzles it helps to learn how to use the Essbase tools to look at the cubes and their structure, lack of data where one was expecting it is one of the most frustrating things I have found but it is one of the quickest things to verify.

The majority of Essbase users will access their data, read it and even update it through MS Excel, previously this has been through the Essbase Add-in for Excel, but now the tool to use going forward is called Smart View (but it is still an Excel add-in). Smart view should be on the radar for all OBI developers as it likely to replace the BI Office plug-in at some stage.

So, a very basic example demonstrates this:

1. Taking a random selection from a demo Hyperion planning cube in BI Answers.

But this returns no results, this isn’t totally surprising as we have only selected a single measure column, whilst we could just add all the measures to the query to see which return data, it would be better to find out where the data is and what we need to use in query to see it.

2. Launching Smart View is simple, once the add-in is installed, users get a Hyperion tool bar option in Excel, the first tool in the Hyperion menu is the ‘Data Source Manager’, this appears as a floating panel on the right of the Excel window and gives access to all the available sources in the EPM suite.

To access a data source just try and drill into the folder and the login dialogue will open. Drill into the appropriate folder to find the cube you are looking for, right click it and select ad-hoc analysis from the context menu.

3. What you now see is the default view of the data with two dimensions, (Account and Period, both required dimensions and arguably the most important) displayed on the X and Y axes of the spreadsheet, and the other dimensions held in a floating palette.

Without going into too much detail, the total level of each dimension, or a member (value) from within that dimension is required in every Essbase query. In OBI this happens behind the scenes, the BI Server query engine will take our selected ‘columns’ in the query, and in the top level member from the dimensions we haven’t used, and will write an MDX query to issue against the cube. To over simplify it, in Smart View this means that what you see either in spreadsheet cells or in the palette will be used in the query.

There are few simple things to remember to help you find the data you are looking for:

Double clicking a member expands it, exposing the next generation (one can also set it to show all);

These two buttons are a revelation the first time you use them, once you have expanded a dimension, Period for example, but you are only interested in the months Jan – Jun, highlight those cells and click ‘Keep Only’, hey presto all the other members below Period disappear. I’m sure I don’t need to explain what ‘Remove Only’ does;

If you expand a member and then decide you don’t want to see all it’s descendants after all, just double right click on it to collapse it;

To move a dimension from the palette onto the spreadsheet, just drag and drop it;

So having dropped Year for example, we could drill down, see which year value we want and then do a Keep Only or a smarter way to do it, and a more practical way with a larger dimension is to select the members first before dragging them onto the sheet;

Click the drop down arrow next to Year and click the ellipsis below ‘Year’.

This opens the Member Selection dialogue, the style of which should be familiar to most people. Remove Year from the right and side and select the year you want from the left. When you close the dialogue the value FY09 will be displayed in the palette, this can be dragged onto the spreadsheet if you want or if you have selected multiple values.

Once the filters have been set by selecting the appropriate members to match the OBI query filters all that remains is to double click the Accounts dimension member to show all the measure accounts. Sure enough we have to scroll along way across to find a column of data.

So there is the reason we have no results in OBI, there is very little data in the cube, this could be for a number of reasons dependant on the nature of the cube. The final anomaly is the title of the column, a member called 411100, odd as all the measures we have in OBI have alpha character names. The reason for this is that all members in Essbase can have aliases, so for financial accounts numeric codes are usually used as the member name as they are more concise, and the account has an alias which is usually a more verbose name or description. Essbase and Smart View display the member name by default, OBI displays the default alias. To view the alias in Smart View, right click the spreadsheet and choose Hyperion>Ad-Hoc Analysis>Change Alias Table, and select the appropriate alias table.

In this case our figures turn out to be against an account who’s alias is ‘Operating Revenue’.

Altering my Answers query accordingly now returns some data.

Yesterday I attended an Oracle partners workshop on ADF (Application Development Framework); ADF is a framework developed by Oracle to simplify development of Java EE based SOA applications and is focused around the JDeveloper tool.  JDeveloper also provides an interface for designing BPEL workflows and database development, indeed the look and feel will be familiar to anyone using SQL Developer as they were once parts of a single product.

So where does ADF fit in to an EPM and BI consultancy?  Whilst you might not have heard of ADF, if you have been around the Oracle scene or looked the website you will undoubtedly be familiar with ‘Fusion’, Oracle Fusion is the convergence of the bulk of Oracle’s acquisitions from the last six or so years onto a single platform with as many common components as possible.  Now your probably ahead of me here and will have guessed what that common platform is and which tool will be used to develop and customise those applications?..ADF.

Oracle EBS, Siebel & JD Edwards are all getting an update then?  Brace yourselves because the quote of the day has to be ‘think of it more as re-implementation than an upgrade’, assuming your still reading, don’t be too worried by this as there was a lot of speculation flying around and much sucking of teeth, but take it as an indication that the new versions of the applications in the fusion stack will be very different behind the scenes and probably on your screens as well.  My personal area of expertise, OBIEE, is the reporting tool that sits astride this whole stack of applications and is also getting a makeover, version 11g is on it’s way and the screen shots and basic demos are starting make sense now I have seen more of ADF 11g.  So let’s look at the upsides, several of these apps are getting a bit long in the tooth and most have web clients that are lagging some way behind the times in terms of appearance and functionality.  So if a business can build in a major upgrade to their existing system lifecycle it may not be that bad, the new functionality will catch and I suspect overtake some of the opposition and the benefits could be huge; and there in lies the problem, at the moment anyone can download JDeveloper and follow the Oracle by Example exercises on OTN, or follow the cue card exercises included in the help files for the program, these will give you a good indication of the look and feel of the finished components you can build, but it’s still really hard to get to see previews of the apps themselves.

Watch this space and if time allows I will explore what ADF has to offer in terms of possible customisation of or additions to OBIEE 11g.