A while ago I got asked to come up with a solution for Smart Merge. Now I’m more a database developer then much else, so my first though was lets do it on the DB. As I spend much time coding this and I got to say I’m not yet really happy with my jython skills I’m not sharing the code, but the idea. Hopefully it will help you get trough getting it developed.

I used 2 additional artefacts on the DB. One table and one view. The view is droped and recreated during the BefLoad. Basically what I did is create a table that is similar to TDATASEG and into which before a new load to TDATASEG is done copies the lines for the pertinent CATKEY, PERIDKEY and ENTITY, to the auxiliary table.

The overview of steps executed:
I. In BefImport: Data from TDATASEG for previous relevant load is stored in TDATASEG_SM, and view TD_TO_TDSM (which holds: TDATASEG_SM – TDATASEG) is created for current CATKEY, PERIDKEY and ENTITY
II. In AftExportToDat, based on the view we append lines with NODATA

The additional candy is the fact there a function that sorts trough the file and makes a list of all entities in there, so to query dynamically which Entities are present in the file.

Got questions about it? Just leave me a comment bellow and I will try to explain, without giving out the code 🙂

UPDATE: After few months of testing I had to move the load of TDATASEG_SM into the AftLoad Event script so to ensure the data loaded in there is the data that is relevant for the next load.

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.

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