Thoughts, ideas and solutions from a few EPM consultants.

EPM 11.1.2.4 and SQL Server on Ubuntu

Today we try something different. I have a stock Ubuntu installation in Hyper-V. Microsoft has released SQL Server for Linux, I think SQL Server for Linux is a Release Candidate (RC), which we over at Tevpro have been using with some .NET Core applications. Lastly, I need a VM of 11.1.2.4 for testing some code I am working on. So... I am going to try and run EPM 11.1.2.4 on the Linux version of MS SQL Server.

What am I going to install:

  • Foundation Services
  • Calculation Manager
  • Planning
  • Essbase

Why am I not installing FDMEE, HFM, etc? I don't need them at the moment but that could change so this may turn into a multi-part post.

Why do I have the desire to try this now? Well I recently rolled off the project I have been on for close to two years and had some free time while looking for a new project (hire me).

Installing SQL Server

If you didn't know Microsoft has become really cool in the last couple years and is embracing Open Source and Linux. Having been a .NET/ASP.NET/Classic ASP/Cold Fusion developer prior to coming into the EPM community I have long loved SQL Server. A big reason for that is SQL Server Management Studio (SSMS), which in my opinion the standard by which all other SQL Tools are judged. Anyway, Microsoft has some really good documentation on getting SQL Server installed on Linux, so the process is a snap. About the only requirement for installation is that the system has at least 3.25 GB of Memory, which since this going to be an EPM server is pretty much guaranteed.

  1. Import the Microsofts public GPG keys:

    crl https://packages.microsoft.com/keys/microsoft.asc | sudo apt-key add -
    
  2. Register add Microsoft SQL Server repository to APT:

    curl https://packages.microsoft.com/config/ubuntu/16.04/mssql-server.list | sudo tee /etc/apt/sources.list.d/mssql-server.list
    
  3. Update APT:

     sudo apt-get update
    
  4. Install SQL Server using APT

     sudo apt-get install -y mssql-server
    
  5. Run the configuration utility and setup the sa (System Administrator) password.

    sudo /opt/mssql/bin/mssql-conf setup
    
  6. Verify that SQL Server is running

    systemctl status mssql-server
    

    or

    sudo service mssql-server status
    
  7. Open the SQL Server port (1433) on your firewall[1]

To anyone who has installed Oracle on Linux, you will be amazed at how fast and easy this is by comparison.

Setup Databases

I already have SSMS but if you do not I would suggest you download SSMS[2]. Open up SSMS and connect to the SQL Server.

  1. Select New Database under databases to open up the new database dialog.
    2017-07-21-14_31_51-Microsoft-SQL-Server-Management-Studio

  2. Define the name of the database as well as any specific settings you might want.
    2017-07-21-14_32_25-Microsoft-SQL-Server-Management-Studio

  3. Repeat steps one and two until all necessary databases are created.[3]

  1. Create a user(s) that will be used to connect to your databases.
    1. Find Security then locate Logins, right click and New Login
      2017-07-21-23_32_47-Microsoft-SQL-Server-Management-Studio
    2. Specify the Login name and Password
      2017-07-21-23_33_36-Microsoft-SQL-Server-Management-Studio
    3. Select User Mapping
    4. Select the desired database you want to assign this user access to.
      2017-07-21-23_49_23-Microsoft-SQL-Server-Management-Studio
    5. Assign the user to the desired roles for the database.
      2017-07-21-23_49_45-Microsoft-SQL-Server-Management-Studio
    6. Repeat steps four and five for each database this user should have access to.

Downloading EPM 11.1.2.4

Oh, E-Delivery you are a pain. A necessary pain that I do not enjoy. E-Delivery has the option to download a wget.sh file that can be used download the installation files.

  1. Login to E-Delivery

  2. Locate the desired software package and set Linux x86 64
    Select Hyperion Financial Planning for Linux x86 64

  3. Uncheck the software you don't need (never understood why DRM, Client tools, and other non-Linux items were selected by default.)
    2017-07-21-13_30_25-SearchSoftware

  4. Select WGET Options
    2017-07-21-13_31_11-SearchSoftware

  5. Download the wget.sh script
    2017-07-21-13_31_30-SearchSoftware

  6. Copy the file to the Linux server.

  7. Edit the first line of the wget.sh script since its targeting the sh shell which does not support the -s option on the read command.

    #!/bin/sh
    

    Change this line to use bash shell.

    #!/bin/bash
    
  8. Run the wget.sh command and follow the prompts.

    ./wget.sh
    

    2017-07-21-14_24_05-kkikta@epm-11-1-2-4_--

  9. Go get coffee. Even though I have a 200Mbs connection its going to take a while.

Prepare for installation

  1. Unzip the files into a single directory

    unzip 'V*.zip' -d InstallFiles
    
  2. Create the user who will do the installation and run the applications once installed.

    sudo adduser epm
    
  3. Modify /etc/security/limits.conf to increase the hard and soft file descriptor limits.

    epm    hard    nofile    131072
    epm    soft    nofile    4096
    
  4. Install make, gcc, rpm, libxtst6, libxi6 and libaio (see footnote 15)

    sudo apt-get install build-essential rpm libxtst6 libaio1 libpth-dev libpthread-stubs0-dev
    
  5. Link rpm to the location the EPM installer is expecting it at and x86-64-linux-gnu to /usr/lib64.

     ln -s /usr/bin/rpm /bin/rpm
     ln -s /usr/lib/x86_64-linux-gnu /usr/lib64
    
  6. Set the X11 display setting (even though I am using console, something in OHS requires it)

     export DISPLAY=:0.0
    
  7. Move the install files to a directory owned by that user

    sudo mv InstallFiles /home/epm/
    
  8. Change the owner of the files

    sudo chown -R epm:epm /home/epm/InstallFiles
    
  9. Become the new user

    sudo su - epm
    
  10. Change directory to the install folder.

    cd ~/InstallFiles
    

Install Oracle EPM

So for this particular installation, I forgot to add the GUI and don't really want to install Xming. So console it is.

  1. Run the install tool.

    ./installTool.sh -console
    
  2. Since I want English I select 0

  3. Verify prerequisite checks are successful[4]

  1. Define the EPM installation path (/home/epm/Oracle/Middleware)

  2. Select a New Installation (Option 0)

  3. Select components to install (this is where it gets tricky in the console.)

    1. At Oracle EPM System - 2: Expand
    2. Foundation Services - 2: Expand
    3. Foundation Components - 0: Select
      • Foundation Services Java Web Application
      • Static Content Files
      • Oracle HTTP Server
      • WebLogic Application Server
    4. Oracle Database Client 32-bit - 0: Next[5]
    1. Oracle Database Client 64-bit - 0: Next
    2. Performance Management Architect - 1: Deselect[6]
    1. Calculation Manager - 0: Select

    2. Essbase - 2: Expand

      1. Essbase Administration Services Java Web Application - 0: Select
      2. Provider Services Java Web Application - 0: Select
      3. Provider Services Java Web Application Samples - 1: Deselect
      4. Essbase Studio Server - 1: Deselect
      5. Essbase Studio Server Samples - 1: Deselect
      6. Essbase Server - 0: Select
      7. Essbase Server Samples - 0: Select[7]
    3. Reporting and Analysis - 0: Select[8]

    1. Planning - 0: Select
    2. Financial Close Managment - 1: Deselect
    3. Tax Managment - 1: Deselect
    4. Disclosure Management - 1: Deselect
    5. Financial Management - 2: Expand
      1. Financial Management SDK - 0: Select
      2. Financial Management Java Web Applications - 1: Deselect[9]
      1. Financial Management Server - 1: Deselect
    6. Strategic Finance - 1: Deselect
    7. FDM Enterprise Edition - 0: Select[10]
    1. Profitability and Cost Management - 1: Deselect
  4. Confirm my selections

    Checking for available disk space in the /home/epm/Oracle/Middleware/EPMSystem11R1 directory.
    The selected components require 12703 MB free disk space.
    You have 91985 MB free disk space.
    
    EPM Oracle Home: /home/epm/Oracle/Middleware/EPMSystem11R1
         Common Components
         Essbase Product Common Components
         Product Common Components
         Oracle Common Files
         Tools
         OPMN
    Foundation Services
         Calculation Manager Java Web Application
         WebLogic Application Server
         Oracle HTTP Server
         Static Content Files
         Workspace Java Web Application
         Foundation Services Java Web Applications
    Essbase
         Essbase Client Samples
         Essbase Client
         Essbase Server Samples
         Essbase Server
    Provider Services Java Web Application
    Essbase Administration Services Java Web Application
    Reporting and Analysis
         Financial Reporting Java Web Application
         Framework Services and Common Libraries
         Framework Java Web Application
    Planning
         Planning Java Web Application
    Financial Management
         Financial Management SDK
    FDM Enterprise Edition
         FDM Enterprise Edition Java Web Application
         ODI
    

Configure Oracle EPM

Next, we move on to the configuration.

  1. Change to configuration directory

    cd ~/Oracle/Middleware/EPMSystem11R1/common/config/11.1.2.0
    
  2. Run configuration utility

    ./configtool.sh -console
    
  3. Set the EPM Oracle Instance path

    /home/epm/Oracle/Middleware/user_projects
    
  4. Set the Oracle EPM Instance Name

    epmsystem1
    
  5. Setup Shared Services and Registry Database Connection - 1: Perform the first-time configuration of a Shared Services database

    1. Set the Database Type - 0: Oracle's MS SQL Server Driver (Type 4)
    2. Server: localhost
    3. Port: 1433
    4. Database Name: EPM[11]
    1. User Name: epm
    2. Password: ********
    3. Launch Advanced Options - 1: No
  6. Select initial items selection - 2: Uncheck All

    1. Hyperion Foundation - 0: Select
    2. FDM Enterprise Edition - 1: Deselect
    3. Essbase - 0: Select
    4. Financial Management - 0: Select
    5. Planning - 0: Select
    6. Reporting and Analysis - 0: Select
  7. Use SSL - 1: No

  8. Mail Server Host: None

  9. Outgoing Port: 25

  10. Incoming Port: 143

  11. Administrator's Email Address: None

  12. Use SSL for mail - 1: No

  13. Enable SSL offloading - 1: No

  14. Use authentication to send email - 1: No

  15. LCM Export Import Location:

    /home/epm/Oracle/Middleware/user_projects/epmsystem1/import_export
    
  16. Configure Database - 0: Oracle's MS SQL Server Driver (Type 4)[12]

  1. Enter the number of components you want to configure, divided by a comma: 0, 1, 2, 3. This will configure the following in my environment:

    • Essbase Administration Services
    • Planning
    • Reporting and Analysis
    • Calculation Manager
      2017-07-22-00_10_30-Microsoft-SQL-Server-Management-Studio
  2. Server: localhost

  3. Port: 1433

  4. DB Name: EPM

  5. Username: epm

  6. Password: ********

  7. Advanced database options for selected rows - 1: No
    2017-07-22-00_13_10-epm@epm-11-1-2-4_--_Oracle_Middleware_EPMSystem11R1_common_config_11.1.2.0

  8. Repository Directory

    /home/epm/Oracle/Middleware/user_projects/epmsystem1/ReportingAnalysis/data/RM1
    
  9. Port Range - 0: Default range (6800-6805)

  10. Deploy to Application Server (WebLogic) - 1: Deploy to new domain

  11. Domain Name: EPMSystem

  12. Administration Server Port: 7001

  13. Administrator User: epm_admin

  14. Administrator Password: ********

  15. Deploy the Java web applications to a single managed server - 1: No[13]

  1. Deploy this Java web application - 0: Yes

    1. EAS
    2. Workspace
    3. Planning
    4. APS
    5. Shared Services
    6. RA Framework
    7. Financial Reporting
    8. CALC
  2. Essbase Cluster Name: EssbaseCluster-1

  3. Agent Port: 1423

    1. Active: Yes
  4. SSL Agent Port: 6423

    1. Active: No
  5. Start Port: 32768

  6. End Port: 33768

  7. Full path to application location (ARBORPATH)[14]

    /home/epm/Oracle/Middleware/user_projects/epmsystem1/EssbaseServer/essbaseserver1
    
  1. Set Essbase language: 0 - English
  2. Binding Host Name: epm-11-1-2-4
  3. Configure Reporting and Analysis Framework Agent:
    1. Agent Port: 6860
    2. Agent RMI Port: 6861
  4. Financial Reporting - Configure RMI Ports Range - 0: Default (8205-8228)
  5. Setup Oracle BIEE - 1: No
  6. Setup Oracle BI Publisher - 1: No
  7. Planning - Configure RMI Server
    1. Port: 11333
  8. Web Server Type - 0: Oracle HTTP Server
  9. Web Server Port: 19000
  10. Set logical address for applications - 0: Yes
  11. Launch Advanced Options - 1: No
  12. Update the logical address for the Java web applications - 0: Set all
  13. Host: epm-11-1-2-4
  14. Port: 19000
  15. SSL Port: 19443
  16. Admin Name: admin
  17. Password: ********
  18. Verify the tasks and "Let 'er rip tater chip"
    2017-07-22-00_42_48-epm@epm-11-1-2-4_--_Oracle_Middleware_EPMSystem11R1_common_config_11.1.2.0

Results

Well, I would like to say it was a complete success but as with many installs, something always seems to come up. Luckily it's just OHS (Apache), but it's always something.
2017-07-22-00_47_25-epm@epm-11-1-2-4_--_Oracle_Middleware_EPMSystem11R1_common_config_11.1.2.0
So now I got to go track down why OHS has decided to not play nice[15]. On a positive note, this was by far the fastest configure/deploy I have ever seen and this is a single virtual CPU VM on my laptop, not sure if its the GUI overhead but this was like 60 - 90 seconds for configuring and deploy.

Final Thoughts

After pouring through logs and adding libraries I eventually I gave in and installed some X11 libraries, configured Xming. Once I ran the install tool again I found that the GUI was reporting that Oracle HTTP Server had not installed correctly. I made several attempts to resolve issues but as when I eventually resolved the dependencies it started to report bizarre permissions problems. I suspect the easiest solution is to clear the install and restart since it is now complaining that it can't overwrite files created by the installer.


  1. I plan on using SSMS but this step is optional. This is only necessary if you are running a software firewall (not enabled by default) and want to allow external connections. ↩︎

  2. If you prefer to use the command line tools you don't need SSMS. ↩︎

  3. Databases in SQL Server are similar to schemas in Oracle. It's uncommon to have multiple databases on the same server in Oracle, however, in SQL Server it's quite normal. ↩︎

  4. Many of these steps require you to select a number indicating how to proceed in the installer similar to the GUI. 1: Previous, 2: Next, 3: Cancel, 4: Redisplay or 5: to Load (whatever that means). The default is generally 2. ↩︎

  5. The installer complains that these clients are not installed and they are required but I think it should work. ↩︎

  6. To my knowledge EPMA still doesn't work on Linux but since doing more DRM I haven't used it so not sure. ↩︎

  7. Default is Deselect but since I am going to be doing some tests why not have the sample apps ↩︎

  8. With the install files I downloaded Interactive Reporting and SQR Production Reporting is not included so it is safe for me to make this choice. ↩︎

  9. I have no need for HFM in this environment. ↩︎

  10. No immediate plans but I might need it later so ill install and not configure it. ↩︎

  11. These settings will depend on what values you entered in the SQL Server database configuration steps above. ↩︎

  12. This time it's for configuring the applications we selected to configure. I am going to keep it simple and only use a single database for the selected applications. ↩︎

  13. Since this is a test box I probably should save on memory usage but I just haven't had much luck with this setting. ↩︎

  14. Love the nostalgia but I would have thought by now they would have modified this to be ESSBASEPATH or something. ↩︎

  15. Checked the configure log and found that OHS couldn't find make and quite a few other libraries related to Xorg (X11) (XWindows). I am not sure why the console bases config needs X. ↩︎