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

      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:

      STUDENTS (_ID, STUDENT_CODE, STUDENT_NAME)

      STUDENTS_SUBJECTS (STUDENTS_ID, SUBJECTS_GENERATED_ID, SUBJECT_COD, SUBJECT_NAME)

      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 
      Error Codes: OPR4ONWY:U9IM8TAC:U9IM8TAC:U9IM8TAC:U9IM8TAC:OI2DL65P 
      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:

      [mymongodb] 
      Driver=$MW_HOME/modules/oracle.bi.datadirect.odbc/8.0.1/lib/ARmongo28.so 
      Description=Oracle 8.0 MongoDB 
      ConfigOptions= 
      CreateDB=2 
      Database=mydb 
      HostName=myhost 
      PortNumber=27017 
      LogonID=user 
      Password=password 
      FetchSize=100 
      JVMArgs=-Xmx1024m 
      LoginTimeout=15 
      ReadOnly=1 
      ReadPreference=primary 
      ReportCodepageConversionErrors=0 
      SQLEngineMode=2 
      SchemaDefinition=$DOMAIN_HOME/bi/config/fmwconfig/bienv/core/mymongodb.config 
      TransactionMode=0
      

      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?