So as we were saying in January this not very known feature of HP is not a miracle cure. But after spending some days on trying to prototype it for an example with something like 11 dimension application (+ all the attribute dimensions) I thought it would be good to add something to my previous post.

I had one HP application (one plan type), which is an EPMA application. After few attempts I was able to create an application that has a compatible data model as the HP one. Reused few dimensions from the Shared Dimension Library and removed members, like the one linked to SmartLists from the Account dimension. I created two additional dimension to describe the options we have in SmartLists and deployed. Now something I have been chasing around for a bit was the labels. So if you read the documentation you will noticed that name or alias of the new dimensions has to match label of the SmartList members/options. This point is very very important.

The application created is an Essbase BSO and deployed from EPMA. Now I made it as BSO just because…in fact you should think about this very carefully. There is much sense in make it a ASO application.

This is an example out of the prototype (changed some names, but you get the point):


Something that took me a while to grasp is the two SmartLists are now real dimensions, which means you need an intersections of at least one member out of it to hold the data. So what we can do is add a “N/A” member, for those combination of data, in the Prototype I just choose the first member out of the lists, anyway what transpires is that this new dimension would probably be dense…making all “a bit” bigger, on both sides source (HP) and target (Essbase BSO). Maybe ASO? Maybe custom mapping?

I explored the custom option as well. Thought of exporting data from HP application Essbase cube, first and re-mapping it on the base of what is in the metadata of the application. Here is a sample Select statement for this:

Select entry_id, name, label
from HSP_ENUMERATION_ENTRY
where enumeration_id = (Select ENUMERATION_ID
from HSP_ENUMERATION
where name = 'SmartListName');

HSP_ENUMERATION, contains the all the SmartList and their id codes.
HSP_ENUMERATION_ENTRY, the SmartList members are in here with their id, labels and their id to tide them to their SmartList.

Point being is doable and with not THAT much effort and this would mean the source target does not need to have any value of the original SmartList instead only the target would have the N/A member where to map this slices of data.

Anyway, a bit of a long post. And last but not least my question is: Does anyone has any “how to”, that they would like to recommend for this?

I thought of posting an update about one of the new Hyperion Planning (11.1.2+) features. I will keep it short:
– This feature is not the miracle pill (sorry developers). Why not? ‘Cause it’s not what the name suggests. It (only) maps data from one application to the other,
knowing how to interpret the Smart List data. Keep in mind names/labels are only available on the level of Hyperion Planning metadata,since they are stored in the DB not Essbase.
– The reporting application (yes it can be an ASO) + database + all dimensions and members need to exist before you can push data in.
– The Smart Lists you want to analyse need their own dimension(s) – careful with names/labels, if you confused about it look at the export file created and throw out the dimension members (probably accounts)
– If you create it in EAS, you will need to provision the user. I did it with admin user and it took me 30 minutes to figure out that one of my problems was due to only having “Provisioning” rights on the new application.

For the rest “how to” this two links were really useful (more then the official documentation):
11.1.2 Planning Mapping Reporting
Mapping Applications for Reporting

Other considerations:
– Licenses, since you make a new Essbase application.
– I would suggest EPMA should be able to handle this better/with less hassle then if you use a classical planning application, since you can reuse dimensions/members…
– Behind the scenes no real magic happens (more info in John Goodwin blog and is easy to find the generated files so you can look for yourself, plus is always good to check the Essbase logs) and as I see it this all could be build as part as a bigger integration work with some “ETL” magic and maybe a bit more wits.

This is just my thoughts as usual 🙂

I have a new box: A Windows Server 2008 Standard edition with OBIEE11g with my first dashboard from a star scheme I made out of a model in HFM (not yet as elegantly as I would hope for). I will sum up how I got to it.

Infrastructure:
– Create a new VBox and install Standard Edition Windows Server (of choosing)
– Install the Vbox Additions
– Set the box on Internal network and configure it (or install the Loopback Adapter )
– Take Snapeshot (!)
– Install Oracle DB
– Run RCU Win 32-bit and create the two the metadata repository you will need:

  • Metadata Services (MDS)
  • Business Intelligence Platform (BIPLATFORM)
  • – Install Obiee as »Simple Install«. Give the installer all the needed info. If you have problems at the Weblogic step like the left handed menu »Administration details« turns red and the installer want continue, you didn’t do the Loopback Adapter/internal network setup correctly. Go back to Snapshot and try again.
    – Wait for it to be over

    Possible problems:
    – You don’t know the default repository password, it’s Admin123
    – You can’t connect to the DB to import metadata in AdminTool:

    1. Check if you have tnsnames in the Middleware home
    2. Check if they contain the ip of the host and not the name
    3. Check if the file user.cmd in:

    \instances\instance1\bifoundation\OracleBIApplication\coreapplication\setup, points to the tnsnames.ora file, even remotely.
    More info: Oracle Forums

    I found a good link that explains the new directory structure.

    Data Model:
    After all this I was able to connect to Essbase. Don’t forget if Essbase server is not on the same box you will need the client on the OBIEE box as well. To install that go on the Download page for EPM and get the following things: SystemInstaller-11120-xx, FoundationServices-11120-xx-Part1 and EssbaseClients-11120, where xx is your platform, mine being in this case Win32.

    I was not able to connect to Hyperion ADM so I decided to find a way to export HFM application into something OBIEE is more familiar with. I found Extended Analytics. If you go this way you will first need to set a DSN. Just create a new text file and save it as an .udl. Double click to open it and choose Oracle OLE DB. Now you need to add it in the FM Client configuration (which can be find in this release in program menu under HFM). Look under second tab. After this you will find the DSN available in HFM on Workspace under Administrator menu.
    In the end I relied on ETL skills to manipulate the star scheme Extended Analytics created and make it almost fit into OBIEE11g.

    Anyway all this story did not turn out as I hoped so atm I’m looking at Essbase Analytics Link for Hyperion Financial Management (Ex-Hyperoll, from what I understood), when I know something more on how it work I blog it.

    So I had a new brilliant idea. “Why don’t I install DB + OBIEE11g on a Win Sever 2008 (yes 64bit).”

    Guess what? Again a really bad idea.

    As it seems there is a bug, that Oracle will fix in the next release, which makes it impossible to start the AdminTool. At least one thing you can notice is the problem of mismaching between the dll existing on disk and the one in the registry. For starters you can try looking if you have a similar mismatch in your installation.

    I’m looking in how it works with client 32bit, but not much hope for now. Will let you know when or if I succeed.

    Anyway to wrap up for now if you are doing POC or Demos I warmly advice you to stick to a Windows Server (32bit) installation of this new wonder Oracle created.

    So if you tried installing OBIEE11G in a similar environment I did (Linux 64bit) you have probably realized that it can be pretty hard work getting the AdminTool, out of an successful OBIEE11G installatiaion, to work. I need to admit I tried both Wine and Windows version solution and one step before I got it to work my dear VBox crashed. The crash was due to disk problems on my laptop, which costed me my Obiee installation (and thought me the importance of snapshots). You can imagine my rage when it happened.

    Anyway, today while trying to install OBIEE11G on a SQL Server I realize that there was something new on the download page. As it seems Oracle released an install for theclient (only 32bit x86 architecture atm).

    Hope this helps 🙂 Even those I was a bit upset when I saw it, since they could have released it before and save few days of my time, I was really glad they did at last.

    So a few weeks ago I got this great idea (as I already mentioned in my 1st blog entry) to make a Linux Box with OBIEE 11g.

    This blog will try to summarize all the problems I encounter and the workaround I had to do. Since is only a summarize I will just link you to pages useful to help you get it right.
    Just for the record I haven’t worked with Linux before so probably some of my mistakes were consequences of this.

    As I stated before the idea was to have everything one one Linux VBox. I ended up with two virtual boxes and Admin tool on my laptop (you can always consider a 3rd win box).

    I choose to use Oracle’s acquisitioned tool VirtualBox (from Sun package). At home I have a bit of a mediocre net line. So just the install of CentOS 5.5 (cause is Red Hat as is Oracle Enterprise Linux, more or less) was an adventure. All the components I used are 64-bit, so OS, Oracle DB 11R2 and OBIEE 11g.

    Now two things you should know about VBox:
    I. Installing guest additions is a must if you want to mount shared folder from the host
    II. Make the machine big enough (OBIEE alone demands something like 12G) it’s a pain to resize it and I was able to make it work just once

    More details:
    I. VBox Guest Additions for Linux are provided on a ISO CD-ROM.
    Addition

    Addition

    Installation generally involves the following steps:
    A) It is recommended using DKMS for Linux guests as well. On CentOS:

    yum install dkms
    Make sure to install DKMS before installing the Linux Guest Additions.

    B) Install the addition (don’t forget the sh, if you open the file you will see why)

    sh ./VBoxLinuxAdditions.run

    Now you should be able to do things like :
    Mount

    II. So if you by mistake make a to small image you can try CloneVDI, which has the option of resizing it. To do anything with that space you than need QTParted and system-config-lvm 1.1.5 to deal with it. I would really recommend you don’t mess with this if you really don’t have to. More info on the cloning topic can be find here.

    At this point I had 2 VBoxes. Clones in fact. To install Oracle I found this some useful blogs:
    Blog #1
    Blog #2
    Article

    The only real problem I found is that they changed some of those kernel parameters in the newest version so be carefull which documentation you follow.

    Useful commands:

    emctl start(stop) dbconsole lsnrctl start(stop) dbstart(dbshut) dbca

    I had an adventure with my DB cause I swapped from Nat to Bridged and set static IP-s to DB after first run. So I had to redo the configuration of my DB more info here. And don’t forget to open 1521 port to the rest of the world (or better the obiee box) when you are ready to share your OracleDB.

    Now OBIEE 11g is something else – not sure if it’s really easy, if you ask me it has it’s tricks. I searched for info on installing it till almost at the end of my mission I found this site.
    There is even an explanation on how to start the system once you restart the box. The only thing I would like to add is if you happen to encounter something like this in the log:


    <BEA-001128> <Connection for pool "bip_datasource" closed.>
    <BEA-001128> <Connection for pool "mds-owsm" closed.>
    <BEA-001128> <Connection for pool "mds-owsm" closed.>
    <BEA-001128> <Connection for pool "rtd_datasource" closed.>
    <BEA-001128> <Connection for pool "rtd_datasource" closed.>
    <Created "1" resources for pool "rtd_datasource", out of which "1" are available and "0" are unavailable.>
    <Created "1" resources for pool "mds-owsm", out of which "1" are available and "0" are unavailable.>

    Then you should try the following command: ./opmnctl startall

    If it gives you an error about ORACLE_INSTANCES this might help.
    So next problem is that the AdminTools exist only for Win. I need to stay I didn’t realize this till the end. So you can resolve this by following this.

    Anyway after all this effort I know you want to at least see how this “monster” looks like so there you go:

    Will add my 7min long video as soon as I get it uploaded on MegiaVideo

    I hope my experience helps someone, somehow.