Essbase and SQL Server Express: just don’t

I’m working on a writeup on how to get a fully functional Essbase server up and running in a virtual machine (using Sun VirtualBox).  I’m currently working on the steps — setting up the Essbase funtionality is relatively straightforward, although you need a relational database to store various information.

“No problem,” I thought to myself, “I’ll just pop on a copy of SQL Server Express.” Oh how naive I was.  In theory, I’m sure it’s possible to use SQL Server Express as the backend for a Hyperion installation, but after fiddling around with various options, enabling TCP/IP, and doing everything else I could think of, I just couldn’t get things to work.  So I yanked it off the virtual machine and put the real deal on — a full copy of SQL Server 2005 — and presto!  I was up and running in no time.  So if you happen to reach this blog article because you googled “SQL Server Express Essbase oh for the love of god why do you mock me” or something similar, I hope this popped first to let you know you may be in for a bumpy ride.

Update: Several of my readers have commented on how they’ve been successfully using SQL Server Express for their purposes.  I didn’t spend an incredible amount of time trying to configure Express to work, and given that the full version of SQL Server is available to me, I went ahead and installed that without looking back.  As I suspected, you need to play with the connection settings a bit in order to get things to work, and if someone cares to do a writeup of what that procedure looks like, I’d be more than happy to post it here.  Thanks for the suggestions, all.

7 thoughts on “Essbase and SQL Server Express: just don’t

  1. Hello,
    we made some virtual machines with installations of Essbase, Planning and Financial Management (v11.1.1.1.0) always using SQL Server Express; maybe our success is due to the fact that we named the database instance like the “default” instance (MSSQLSERVER instead of SQLEXPRESS).
    Regards
    Massimo

  2. I remember the pain of trying to get SQL Express to work with EIS. It required going into the advanced properties of the JDBC connection to edit the connection string in order to get it running, but I did finally get there. Perhaps that would also work for the other repositories.

  3. Hi,

    You can definitely use sql server express with Hyperion. What you need to be careful of is that express does not use a fixed tcp port. In the configuration manager under tcp/ip properties > ip addresses tab > IPALL TCP Port can be set to the default 1433.

  4. Hi Jason,

    Not sure why you got the problem.
    I’ve been using Hyperion package (since 9.3 to 11.1) and SQLServer 2005 Express in all of my machine (real machine and vmware), and everything is doing just fine.
    Haven’t tried it using Sun VirtualBox though.

    Indra

  5. Hi Jason,
    I posted installation steps using SQL server in my blog

    http://www.dornakal.blogspot.com

    hope this helps.

    with regards,
    Dornakal

  6. Especially, if you are using Essbase Studio you’ll see many problems with SQL Server. I highly recommend anyone to use Oracle DB with Essbase.

    You can checkout more on Essbase Studio relates issues on my blog:
    Essbase Studio Blog

    Best

  7. […] Essbase and SQL Server Express: just don’t – jason’s … – Hello, we made some virtual machines with installations of Essbase, Planning and Financial Management (v11.1.1.1.0) always using SQL Server Express; maybe our …… […]

Leave a Reply

Your email address will not be published.