OBIEE 11g Integration with MongoDB (Star Schema in OBIEE)

OBIEE does not have inbuilt functionality to directly connect with MongoDB, so it requires API/JDBC/ODBC for integration.

After evaluating a couple of ODBC drivers, I have decided to use the Simba ODBC driver. The Simba ODBC driver for MongoDB is standard database driver that can integrate real-time access to MongoDB data.

ODBC Driver with SQL Connector

Simba’s ODBC connector allows you to make quick analytic insights and to leverage back end data source high-performance calculation capabilities for OBIEE. Since MongoDB is schema-less, a powerful mechanism within the SQL Connector allows users to define schemas on the fly for use by the ODBC 3.8 driver, providing a powerful SQL window to MongoDB data.

The OBIEE can easily connect and quickly deliver performance and broad compatibility and ensure full functionality for users analyzing and reporting on MongoDB.

                                              

Installing the driver

Windows Driver

System Requirements

Each computer where you install the driver must meet the following minimum system requirements:

• One of the following operating systems (32- and 64-bit editions are supported):

• Windows XP with SP3

• Windows Vista

• Windows 7 Professional

• Windows Server 2008 R2

• 25 MB of available disk space

Important: To install the driver, you need Administrator privileges on the system

On 64-bit Windows operating systems, you can execute 32- and 64-bit applications transparently.

You must use the version of the driver matching the bitness of the client application accessing data in MongoDB databases:

·         SimbaMongoDBODBC32.msi

·         SimbaMongoDBODBC64.msi

You can install both versions of the driver on the same computer.

Steps to Install Simba ODBC Driver

1.         Depending on the bitness of your client application, double-click to run SimbaMongoDBODBC32.msi or SimbaMongoDBODBC64.msi.

The Simba MongoDB ODBC Driver Setup window opens and Click Next.

 Select the checkbox to accept the terms of the License Agreement, and thenClick Next

Click Next. The Ready to install Simba MongoDB ODBC Driver page is displayed.

          Click Install.

When the installation completes, click Finish

6.      If you received a license file via e-mail, then copy the license file into the \lib subfolder in the installation folder.

Creating User in MongoDB

MongoDB Shell (mongo)

You can use the mongo shell to query and update data as well as perform administrative operations.

Start mongoDB

Once you have installed and have started MongoDB, connect the mongo shell to your running MongoDB instance. Ensure that MongoDB is running before attempting to launch the mongo shell.

Note:- If it has not started then start the mongoDb.

 To Start Mongo DB navigate to bin/ and launch the mongod.exe

On the same system where the MongoDB is running, open a terminal window (or a command prompt for Windows) and run the mongo shell with the following command:

Mongo.exe

Add a user on MongoDB database

Add a user with the userAdminAnyDatabase role. 

Syntax:-

use admin

db.createUser(

  {

    user: "MyDB1",

    pwd: "abc123",

    roles: [ { role: "userAdminAnyDatabase", db: "admin" } ]  } )

  

Load data into the database

Mongoimport

The mongoimport tool imports content from an Extended JSON, CSV, or TSV export created by mongoexport, or potentially, another third-party export tool.

Syntax:-

Run below command if you are importing data remotely

mongoimport --host mprmana01 --port 27017 --username Mydb --password abc123 --collection contacts --db json1 --file c:/json1.json

Run below command, In case of localhost (Where MongoDB installed)

mongoimport --db mydb1 --collection data --file c:/data.json

Insert Data with the mongo Shell

You can use the insert() method to add documents to a collection in MongoDB. If you attempt to add documents to a collection that does not exist, MongoDB will create the collection for you.

Insert a Document

Insert a document into a collection named restaurants. The operation will create the collection if the collection does not currently exist.

Example:-

  db.custDetails.insert:(

    {

      "indvCorpFlag": "C",

      "customerID": "99122850",

      "existingCustomerID": "",

      "tinNo": "",

      "companyName": "purna",

      "custSegment": "11",

      "natureBusiness": "85_NOB",

      "groupID": "G00000000000021",

      "typeOrganization": "",

      "industry": 2,

      "hostAccountNo": "",

      "registrationNo": "",

      "dateOfInception": "27-09-2015",

      "yearOfBusinessInCountry": 23,

      "tradeLicenceExpiryDate": "",

      "contactPerson": "asdfghjkllk",

      "designation": "",

      "entityFlag": "Y",

      "ncrNo": "",

      "companyBusiness": "",

      "cifID": "",

      "constitutionId": "13",

      "turnover": "" })

Creating a Data Source Name

After installing Simba ODBC Driver with SQL Connector and importing data into mongodb, you need to create a Data Source Name (DSN).

To create a Data Source Name:

1.       Now go to Control panelà Administrative tool àData source(ODBC)

2.       Click on System DSN tab

 Click the Drivers tab and verify that the Simba MongoDB ODBC Driver is displayed in the list of ODBC drivers that are installed on your system.

 In the Data Source Name text box, type a name for your DSN.

5.       Click then test to make sure ODBC connected the other database.

Connecting Mongodb to OBIEE 11g

1.       Now go to obiee11g  --> In that select the database as ODBC

 

2.       Create a connection pool for that database

3.       Now you can Import that tables from Mongodb

   

Data Modeling

OBIEE Dashboard

 

 

 

Power BI vs Tableau

FDMEE showCustomMessage() in multiple Lines

FDMEE API has a very good function to show custom messages on screen during on-line process.

This post is not to explain on how to use the function(as others have already posted about it) but to identity a bug in the function and to have a quick work around. This bug is found in recent patch-set, i haven't checked it in the base version

We had a requirement at a client to calculate certain measures and output it on the custom message box during the event script executions. The client gave us a format and we were not able to replicate it. The message box is designed in such a way that if you suppose enter a 3 line sentence in a string using \n parameter and print it using the function. The output on the screen will always be a single line. 

lets consider the following text. 

this is what typically Jython prints.

lets put this in a custom script and see what it displays

I have traced the CSS class to x15t which is responsible for this formatting.

skyros-v1-desktop-tav2tz-en-ltr-gecko-cmp.css   is the file which takes care of this CSS class(this might be different if you are using a single managed server located at servers\EPMServer0\tmp\_WL_user\AIF_11.1.2.0\2fpww3\public\adf\styles\cache ). We added an extra tag called white-space:pre which would identify the \n as the new line and print it.

this is what the class finally looks like .x15q,.x15t {font-family:Tahoma, Verdana, Helvetica, sans-serif;font-weight:normal;font-size:11px;color:#000000;white-space:pre}

Make sure you don't change anything else because this class is used in almost all the pop-up windows. as white space just formats the strings in multiple lines we were fine with it.

Restart the web application after making the changes. Run the script and check the output.

Exactly like how we wanted. There are a couple of points to note when doing the change. X15T is the class used in almost all information boxes so the change will effect everywhere, We are doing a change in the temporary file which means if deleted the settings are gone, Restart of the services will not delete this temporary.

While writing this post i observed that this behaviour is only in the new versions of IE and Mozilla. IE8 however is able to get the output in multiple lines using <br> 

I know this is not a convincing solution but it served our purpose. We are able to get the output exactly how we wanted. Hopefully Oracle will give us a fix in the next patch set.

Journal Extract using HFM JAVA API

We always get requirement from the client to load the Journals to Essbase cubes with Entity Currency Adjs or with Parent Currency Adjs. So as we all do extracting of the journals from the application with a POV and some additional options, the same can be achieved using the HFM API and why do we require it? because using the API we can do the automation of the Journal extracts :-).
I am writing the snippet of the code here because the sample code available as part of the out of box EPM home doesn't have the extract journals sample.
Our requirement in writing the code for Journal Extracts is for automating it using the ODI tool and then to massage the extracted data and then to push it to the Essbase cube or a staging table.
As you already know ODI HFM knowledge modules doesn't work with the 11.1.2.4 version so if there are any native ODI interfaces on the older versions are needed to be rewritten in the API or use the FDMEE component directly for the integration.
We can use Oracle JDeveloper for writing the classes and executing them . Setting up the JDeveloper to use the sample code and API is explained in the Oracle document Oracle_HFMAPI .
Below is the class for extracting the journals using the inbuilt classes "LoadExtractOM" and "JournalExtractOptions"

// Extracting Journals using JAVA API
// Written by Naga
// Applicable for HFM version 11.1.2.4

package oracle.epm.fm.hfm;
import java.io.File;
import java.util.ArrayList;
import java.util.List;
import java.util.Locale;
import oracle.epm.fm.common.datatype.transport.JournalExtractOptions;
import oracle.epm.fm.common.datatype.transport.SessionInfo;
import oracle.epm.fm.common.exception.HFMException;
import oracle.epm.fm.domainobject.loadextract.LoadExtractInfo;
import oracle.epm.fm.domainobject.loadextract.LoadExtractOM;
import oracle.epm.fm.common.datatype.transport.JOURNAL_BALANCE_TYPE;
import oracle.epm.fm.common.datatype.transport.JOURNAL_STATUS;
import oracle.epm.fm.common.datatype.transport.JOURNAL_TYPE;

public class JournalExtract {
    public JournalExtract() {
        super();
    }
    public void extract() throws HFMException {
        Application application = new Application();
        SessionInfo session = application.open();
 
        try {
            LoadExtractOM loadOM = new LoadExtractOM(session);
            JournalExtractOptions JEO = new JournalExtractOptions();
            File nfile = new File("E:\\jnl.txt");
            nfile.delete();
            List<Integer> L1 = new ArrayList<Integer>();
            for (JOURNAL_BALANCE_TYPE F1 : JOURNAL_BALANCE_TYPE.values())
                L1.add(F1.getValue());
            List<Integer> L2 = new ArrayList<Integer>();
            for (JOURNAL_STATUS F1 : JOURNAL_STATUS.values())
                L2.add(F1.getValue());
            List<Integer> L3 = new ArrayList<Integer>();
            for (JOURNAL_TYPE F1 : JOURNAL_TYPE.values())
                L3.add(F1.getValue());
            JEO.setDelimiter(";");
            JEO.setRecurring(true);
            JEO.setStandard(true);
            JEO.setRegular(true);
            JEO.setBalanceType(L1);
            JEO.setStatus(L2);
            JEO.setType(L3);
            JEO.setPov("S#Actual.Y#2016.P#JUL.E#{GRP.[Descendants]}.V#<Entity Curr Adjs>");
            LoadExtractInfo info = loadOM.extractJournals(JEO);
            System.out.println("Status of the job " + info.getStatus());
            File lfile = info.getDataFile();
            System.out.println("is the file renamed : " + lfile.renameTo(nfile));
        }
        finally {
            application.close(session);
        }
        }

The JournalExtractOptions class plays a key role here, the options can be customized as per the requirement on how you would like to have the Journals Extracted with selected Type,Status &Balance_Type. Also the POV can be passed using an argument so we don't need to change it all the time.
Now just class the above class in the Main() class using the below snippet.

        public static void main(String[] args) {
          try {

            String epmOracleInstance = System.getProperty("EPM_ORACLE_INSTANCE");

            if (epmOracleInstance == null || epmOracleInstance.isEmpty()) {
              throw new Exception("EPM Instance home not set");
            }

            JournalExtract jext = new JournalExtract();
            jext.extract();

          } catch (HFMException e) {
            e.printStackTrace();
            System.out.println(e.getErrorCode());
            System.out.println(e.getHResult());
            System.out.println(e.getLocalizedMessage(Locale.ENGLISH));
          } catch (Exception e) {
            e.printStackTrace();
          }

Here I am renaming the extracted file to a custom file and location of my choice as the getDataFile() method would download the file into a HFM default working directory.
So how to automate this .....we can simply create a batch script to call the Java class. Below is a sample which i am using

@echo off
REM This is HFM Journal Extract script

if "%EPM_ORACLE_HOME%" == "" (
    echo "ERROR: EPM_ORACLE_HOME not set."
    exit /B 1
)

cd ..
set EPM_ORACLE_INSTANCE=E:\Oracle\Middleware\user_projects\epmsystem1
set EPM_ORACLE_HOME=E:\Oracle\Middleware\EPMSystem11R1
call %EPM_ORACLE_HOME%\common\config\11.1.2.0\setJavaRuntime
set JAVA_OPTS=-Djavax.net.ssl.trustStore=E:\Oracle\Middleware\wlserver_10.3\server\lib\DemoTrust.jks -Djava.util.logging.config.class=oracle.core.ojdl.logging.LoggingConfiguration -Doracle.core.ojdl.logging.config.file=logging.xml -DEPM_ORACLE_INSTANCE=E:\Oracle\Middleware\user_projects\epmsystem1
set CLASSPATH="F:\HFMAPI\hfm\.adf;F:\HFMAPI\hfm\hfm\classes;E:\Oracle\Middleware\EPMSystem11R1\common\jlib\11.1.2.0;E:\Oracle\Middleware\EPMSystem11R1\common\jlib\11.1.2.0\epm_hfm_web.jar;E:\Oracle\Middleware\EPMSystem11R1\common\jlib\11.1.2.0\epm_j2se.jar;E:\Oracle\Middleware\EPMSystem11R1\common\jlib\11.1.2.0\epm_thrift.jar"

"%JAVA_HOME%\bin\java" %JAVA_OPTS% -cp %CLASSPATH% oracle.epm.fm.hfm.JournalExtract

endlocal

Save the batch script and schedule it with either windows scheduler or ODI agent.
Let me know if you have a better way to extract the journals, happy to learn :-).