Here is a tease of a session I encourage you to join on COLLABORATE 15, as I help with the prep work a bit šŸ˜€


I will post some tricks you might need to this on your own, if you want to try it out, after the session tho šŸ˜€

As promised the lessons learned:
I) Essbase Application on which we are reporting should be rolling up possibly to top (Gen1).
II) Name Generations in Essbase – for less confusion
III) Don’t flatten account dimension – keep “Fact” – and change setting on Account dimension
IV) Tag one dimension as a time dimension
V) (“the other option”): Can be done trough Data Model (BI Publisher) created from SQL statement from reports created in Analysis (copied from under “Advanced” tab), example of SQL statement (which you can already adjust):

SELECT s_0, s_1, s_2, s_3, s_4, s_5, s_6, s_7, s_8, s_9, s_10, s_11, s_12 FROM (
0 s_0,
"PRS"."Account"."Accounts" s_1,
"PRS"."Department"."G2: AllDepts and Settings" s_2,
"PRS"."Department"."G3: Department" s_3,
"PRS"."Product"."G2: AllProd and NoProd" s_4,
"PRS"."Product"."G3: Product" s_5,
SORTKEY("PRS"."Account"."Accounts") s_6,
SORTKEY("PRS"."Department"."G2: AllDepts and Settings") s_7,
SORTKEY("PRS"."Department"."G3: Department") s_8,
SORTKEY("PRS"."Product"."G2: AllProd and NoProd") s_9,
SORTKEY("PRS"."Product"."G3: Product") s_10,
"PRS"."IncStmt"."Fact" s_11,
REPORT_AGGREGATE("PRS"."IncStmt"."Fact" BY "PRS"."Department"."G2: AllDepts and Settings","PRS"."Department"."G3: Department","PRS"."Product"."G2: AllProd and NoProd","PRS"."Account"."Accounts") s_12
(("Year"."Year - Default" = 'FY16') AND ("Scenario"."Scenario - Default" = 'Budget') AND ("Version"."Version - Default" = 'Working Version') AND ("Period"."G2: Total Year and BegBalance" = 'YearTotal') AND ("Product"."G2: AllProd and NoProd" <> 'NoProd') AND ("Department"."G2: AllDepts and Settings" <> 'Settings') AND ("Account"."Accounts" IN ('Price', 'Volume', 'Revenue', 'COGS', 'Discount', 'Bonus', 'SalesExp', '')))

A) Working with Data Model
Creating a Data Set

In the Data tab:
I) Click View
II) Click “Table View”
III)(Optional) Either in SQL or in tab Structure change the column names to make it easier to identify in Mobile designer

Ready to save and use

Normal by using Subject Areas

Using Bi Publisher Data Model

And this is how it looks like at end:

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.

    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.


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

    Now you should be able to do things like :

    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

    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.