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?

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?

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?