2 Replies Latest reply on Feb 21, 2018 11:25 AM by Ana GH

    Presentation Services. Connection to MongoDB using Relational View Data Model

    Ana GH

      I'm building a pilot to connect to a MongoDB database with OBIEE

      Using OBIEE's provided DataDirect Driver, I set up the ODBC System DNS connection in Windows for the Administrator Tool. Setting this up, I used the Schema Tool (you call it with a button in the DNS configuration) creating a Relational View, meaning that, if you have documents in Mongo with this structure:

          "_id" : ObjectId("XX"),
          "student_cod" : "1",
          "student_name" : "student 1",
          "subjects" : [
              {  "subject_cod" : "Subject 1",
                 "subject_name" : "Subject Name 1"
              {  "subject_cod" : "Subject 2",
                 "subject_name" : "Subject Name 2"

      The Schema Tool, if you choose Relacional View, will create two tables:



      This is imported to the Administrator Tool and creating a Subject Area is quite straightforward.

      Then I set up the ODBC connection in the server (Oracle Linux), and when I try to create an analysis, I don't have problems querying the main table columns (STUDENT_CODE or STUDENT_NAME in my example) but when I try to query the relational tables, I get this error:

      Error: ODBC Driver returned an error (SQLExecDirectW). 
      Error Details 
      Status: HY000. Code: 10058. [NQODBC] [SQL_STATE: HY000] [nQSError: 10058] A general error has occurred. (HY000) 
      Status: HY000. Code: 43113. [nQSError: 43113] Message returned from OBIS. (HY000) 
      Status: HY000. Code: 16001. [nQSError: 16001] ODBC error state: S1000 code: -458 message: [Oracle DataDirect][ODBC MongoDB driver][MongoDB]java.lang.NullPointerException java.lang.NullPointerException. (HY000) 
      Status: HY000. Code: 16015. [nQSError: 16015] SQL statement execution failed. (HY000)


      This is the configuration in my odbc.ini file in the Linux Server:

      Description=Oracle 8.0 MongoDB 

      Where the mymongodb.config file is a copy of the file the Schema Tool generates with the relational view.


      If I use the flattened view in the Schema Tool everything works, but it's ugly and building the Business Area in the Administration Tool is a pain and not at all dinamic.


      Has anyone been able to use a Relational View model to query MongoDB? Any idea on something I could check up to solve this?

        • 1. Re: Presentation Services. Connection to MongoDB using Relational View Data Model
          Ana GH

          A follow up on this. I've been performing various tests, and it seems the problem is that not all documents in Mongodb have information in the relational view, I mean, in my example, I have some documents without subjects information, so the moment I try to create an analysis with subjects information, I get the error.

          When I have no subjects, the document has this structure:

              "_id" : ObjectId("YY"),  
              "student_cod" : "2",  
              "student_name" : "student 2",  
              "subjects" : []  

          The problem is not OBIEE related, it has to do with the ODBC configuration, because I get the same error just performing a sql query using the example aplication tool Progress Datadirect driver provides https://documentation.progress.com/output/DataDirect/odbcmongohelp/index.html#page/odbcmongohelp%2FThe_example_Applicati… .

          Does anybody know of a configuration property in the odbc.ini file to allow querying documents without all the substructures?

          • 2. Re: Presentation Services. Connection to MongoDB using Relational View Data Model
            Ana GH

            A third update... I have just performed another test, and I have found out that if the document has this structure:

                "_id" : ObjectId("ZZ"),
                "student_cod" : "3",
                "student_name" : "student 3"

            Then there's no error, so it seems it's the "subjects" : [] empty structure in the document that the driver has problems to interpret. I'm thinking maybe there's something in the datamodel file (mymongodb.config) that I can change to make it work. This file is mapping the json structure of the document to the relational model, anyone knows how to map this?