Home Partners Company
MicroStrategy and NoSQL MongoDB ODBC Meetup

MicroStrategy and NoSQL MongoDB ODBC Meetup

March 11, 2015 0 Comments

Last week, I was invited by the local MicroStrategy User Group to talk about making sense of MongoDB data with MicroStrategy. According to my new friends, MongoDB is surfacing in business applications including call centers, master data management, order management repositories, and more. Rather than letting the new technology cause disruption, this group decided to take control and contact Progress DataDirect for a NoSQL data dive.

DataDirect MongoDB ODBC drivers disrupt disruption at MicroStrategy shops

It was great to get feedback from the group that reinforces our unique approach to MongoDB connectivity. Because I think this information is so important, I wanted to share it with you as well.

The following is a recap of the demo I gave for MongoDB ODBC connectivity from MicroStrategy Analytics Desktop:

  1. Download and install the Progress® DataDirect® MongoDB ODBC driver (32-bit for MicroStrategy Analytics Desktop).
  2. Launch 32-bit ODBC Administrator using the shortcut from the DataDirect MongoDB ODBC program group and create an ODBC Data Source, “MongoDB."

    Progress DataDirect ODBC Driver for MongoDB setup screenshot

    ODBC MongoDB Driver Setup

     

  3. In the advanced tab, set the following value in the extended options field for MicroStrategy compatibility:
    defaultvarcharsize=255;leadingunderscorereplace=mongo
     

    MongoDB ODBC Tutorial 2

    ODBC MongoDB Driver Setup, Advanced Tab Settings

     

  4. Launch MicroStrategy Analytics Desktop.

    An example screenshot of MicroStrategy Active Desktop

    MicroStrategy Active Desktop example

     

  5. Click Database > Import.

    A screenshot of MicroStrategy Active Desktop's Import Database wizard

    Importing your database into MicroStrategy

     

  6. Select the DSN Connections radio button and choose your “MongoDB” ODBC DSN.

    How to select a DSN connection iin MicroStrategy using Progress DataDirect

    Choosing your "MongoDB" ODBC DSN

     

  7. Bring in the tables from the DataDirect MongoDB schema which normalized the collection designed for reliable SQL querying. You will notice child tables were set up with foreign key relationships to avoid data loss from embedded documents or arrays. In my example, here are the collections stored in MongoDB:> db.worldcup.insert({ “_id” : 1, “year” : 2014, “location” : “Brazil”, “teams”: [ {“country” : “Brazil”, “first” : null, “last” : “Jefferson”, “goals” : 0 },{“country” : “Brazil”, “first” : “Dani”, “last” : “Alves”, “goals” : 0 }, {“country” : “Brazil”, “first” : “Thiago”, “last” : “Silva”, “goals” : 1 } , {“country” : “Chile”, “first” : “Claudio”, “last” : “Bravo”, “goals” : 0 }, {“country” :”Chile”, “first” : “Eugenio”, “last” : “Mena”, “goals” : 0 } , {“country” : “Colombia”, “first” : “David”, “last” : “Ospina”, “goals” : 0 }, {“country” : “Colombia”, “first” : “Cristian”, “last” : “Zapata”, “goals” : 0 }, {“country” : “Colombia”, “first” : “Mario”, “last” : “Yepes”, “goals” : 0 } , { “country” :”Germany”, “first” : “Manuel”, “last” : “Neuer”, “goals” : 0 }, {“country” : “Germany”, “first” : “Kevin”, “last” : “Grosskreutz”, “goals” : 0 } , {“country” : “Mexico”, “first” : “Jose”, “last” : “Corona”, “goals” : 0 }, {“country” : “Mexico”, “first” : “Francisco”, “last” : “Rodriguez”, “goals” : 0 }, {“country” : “Mexico”, “first” : “Carlos”, “last” : “Salcido”, “goals” : 0 }, {“country” : “Mexico”,”first” : “Rafael”, “last” : “Marquez”, “goals” : 1 } , { “country” : “USA”, “first” : “Tim”, “last” : “Howard”, “goals” : 0 }, {“country” : “USA”, “first” : “DeAndre”, “last” : “Yedlin”, “goals” : 0 }, {“country” : “USA”, “first” : “Omar”,”last” : “Gonzalez”, “goals” : 0 }, {“country” : “USA”, “first” : “Michael”, “last” : “Bradley”, “goals” : 0 }, {“country” : “USA”, “first” : “Matt”, “last” :”Besler”, “goals” : 0 } ] } ) >WriteResult({ “nInserted” : 1 }) > db.worldcup.insert({ “_id” : 2, “year” : 2010, “location” : “South Africa”, “teams” : [ {“country” : “Uruguay”, “first” : “Fernando”, “last” : “Muslera”, “goals” : 0 }, {“country” : “Uruguay”, “first” : “Diego”, “last” : “Lugano”, “goals”: 0 } , { “country” : “Netherlands”, “first” : “Maarten”, “last” : “Stekelenburg”, “goals” : 0 }, {“country” : “Netherlands”, “first” : “Gregory”, “last” : “van der wiel”, “goals” : 0 }, {“country” : “Germany”, “first” : “Manuel”, “last” :”Neuer”, “goals” : 0 }, {“country” : “Germany”, “first” : “Marcell”, “last” : “Jansen”, “goals” : 1 }, { “country” : “Spain”, “first” : “Iker”, “last” : “Casillas”, “goals” : 0 }, {“country” : “Spain”, “first” :”Raul”, “last” : “Albiol”,”goals” : 0  } ] }) WriteResult({ “nInserted” : 1 }) From the available tables, you’ll see WORLDCUP and WORLDCUP_TEAMS.

    Example child table settings with foreign key relationships

    Example child table settings with foreign key relationships

     

  8. If you were doing this prior to Progress DataDirect’s introduction of reliable SQL query capabilities to the industry, the same data would look something like the following, which scared the room. (Sorry, guys!)

    MondoDB data in MicroStrategy view without Progress DataDirect SQL query

    The same data shown without Progress DataDirect SQL query capabilities.

     

  9. After selecting the columns (shown in #7), we created a simple visualization calculating the count of teams and goals for each World Cup game. The MicroStrategy professionals thought this was pretty awesome when looking at the sample JSON documents.

    A screenshot of a simple calculation visualize as a table with three columns and three rows.

    Data calculation from MongoDB data

     

Empowering User Groups one at a time

I always try to stay active in the community, so I had a fantastic time meeting with end users of our partner, MicroStrategy. I was also excited to learn about cool data capabilities in MicroStrategy that include data blending, data wrangling, multi source support and the Usher mobile identity platform … cool stuff!!

My favorite feedback was from a BI architect who planned on going back to the office to proactively look for disruptive sources to consume directly with the new MSTR data capabilities rather than play hot potato with the data warehousing team.

Other data source topics of interest mentioned include:

    • Salesforce (SaaS)
    • Cassandra (NoSQL)
    • Google Analytics (Hyper Cube)
    • Apache Spark (Big Data)
    • Oracle Clouds (Eloqua, RightNow)
    • VoltDB (NewSQL)

What next?

Sound interesting? If you want to give my demo a try for yourself, make sure you pick up a trial of our DataDirect MongoDB ODBC driver from our website.

Sumit Sakar

Sumit Sarkar

Sumit Sarkar is a Chief Data Evangelist at Progress, with over 10 years experience working in the data connectivity field. The world's leading consultant on open data standards connectivity with cloud data, Sumit's interests include performance tuning of the data access layer for which he has developed a patent pending technology for its analysis; business intelligence and data warehousing for SaaS platforms; and data connectivity for aPaaS environments, with a focus on standards such as ODBC, JDBC, ADO.NET and ODATA. He is an IBM Certified Consultant for IBM Cognos Business Intelligence and TDWI member. He has presented sessions on data connectivity at various conferences including Dreamforce, Oracle OpenWorld, Strata Hadoop, MongoDB World and SAP Analytics and Business Objects Conference, among many others. 

Read next First Public Viewing of ODBC 4.0 Specification
Comments
Comments are disabled in preview mode.