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

Technology researcher, thought leader and speaker working to enable enterprises to rapidly adopt new technologies that are adaptive, connected and cognitive. Sumit has been working in the data access infrastructure field for over 10 years servicing web/mobile developers, data engineers and data scientists. His primary areas of focus include cross platform app development, serverless architectures, and hybrid enterprise data management that supports open standards such as ODBC, JDBC, ADO.NET, GraphQL, OData/REST. He has presented dozens of technology sessions at conferences such as Dreamforce, Oracle OpenWorld, Strata Hadoop World, API World, Microstrategy World, MongoDB World, etc.

Comments
Comments are disabled in preview mode.
Topics
Latest Stories
in Your Inbox

Subscribe to get all the news, info and tutorials you need to build better business apps and sites

More From Progress
2020 Progress Data Connectivity Report
2020 Progress Data Connectivity Report
Read More
 
232x131_resourceimage
Getting Ahead of the Hybrid Data Curve
Read More
 
570x321__Top 7 Considerations Before Choosing a Chatbot for Your Enterprise
Top 7 Considerations Before Choosing a Chatbot for Your Enterprise
Read More