The value of Apache Drill becomes apparent when integrated with powerful analytics and BI platforms. Today, MicroStrategy announced that Apache Drill is certified with the MicroStrategy Analytics Enterprise Platform™. MicroStrategy Analytics Enterprise connected to Apache Drill allows users to explore multiple data formats instantly on Hadoop, enabling direct access to semi-structured data, without having to rely on IT teams for schema creation.
Here are the steps to get started with Drill on MicroStrategy Analytics Enterprise:
- Install the Drill ODBC driver from MapR
- Configure the MicroStrategy Drill Object
- Create the MicroStrategy database connection for Drill
- Query and analyze the data
Install the Drill ODBC driver from MapR
The installation instructions for the MapR Drill ODBC driver can be found here:
It is important to note that for MicroStrategy 9.4.1 the 32-bit Windows driver is used.
Always verify that the ODBC driver version is compatible with the Apache Drill version being used.
It is recommended to upgrade to Drill 0.7, which uses the v0.07.0.0608 version of the driver:
Note: Earlier versions of Drill uses the v0.07.0.0600 ODBC driver version that can be found here: http://package.mapr.com/tools/MapR-ODBC/MapR_Drill/MapRDrill_odbc_v0.07.0.0600/
Once the 32-bit ODBC Driver is installed, it can be configured as per the installation instructions. Since both the 64-bit and 32-bit drivers can coexist on the same system, make sure to use the 32-bit ODBC Administrator (showed in picture below).
Configure the DSN for Drill in the ODBC Administrator as described in the link provided above. Below is an example of the configuration.
Note the ODBC driver version number in the bottom left hand corner.
Make sure to test the ODBC configuration (by using the Test option on the window) before attempting to use it with MicroStrategy.
Install the Drill Object on MicroStrategy Analytics Enterprise
See the MicroStrategy technote as a reference on how to install DBMS objects provided by MicroStrategy:
The Drill Object is stored in a file called 'MapR_Drill.PDS' and is available through MicroStrategy Technical Support. Once the Drill Object is obtained, store it on the local Windows file system.
Open MicroStrategy Developer, then expand Administration and open Configuration Manager. Click on Database Instances.
Right-click in the area where the current database instances are shown. Select New – Database Instance, as shown in the picture below.
Once the Database Instances window opens, select the Upgrade button.
Enter the path and file name for the Drill Object file in the DB types script file. (The browse button next to the text box can be used to browse and find the file as well). Then click Load. Once loaded, select the MapR Drill database type on the left hand column and then use the > button to load it to the Existing database types. Once completed, click OK to save the database type.
Restart MicroStrategy Intelligence Server if it is used for the project source.
MicroStrategy Analytics Enterprise will now be able to access Apache Drill.
Create the MicroStrategy database connection for Drill
A simple way to create a database connection in MicroStrategy is to utilize the Database Instance Wizard. Simply click on the Administration menu in the Developer and then click on Database Instance Wizard.
Enter a name for the database and select MapR Drill as the database type from the drop-down list. Then click next.
Select the ODBC DSN that was configured with the ODBC Administrator. Provide the login information for the connection and then click Finish.
Drill will now be available as a database instance to be utilized by MicroStrategy Analytics Enterprise.
Query and analyze the data
Here is very simple example were MicroStrategy is used to analyze Twitter data stored as complex JSON documents using Drill.
The Drill distributed file system plugin is configured to read the Twitter data in a directory structure. A view is created in Drill to capture the most relevant maps, nested maps and arrays for the Twitter JSON documents. To read more about how to configure and use Drill to work with complex data, see: https://cwiki.apache.org/confluence/display/DRILL/Query+Data
In MicroStrategy Developer, the Project Creation Assistant is used to create a new project.
Once the Assistant is started, click on the Create Project button and enter a name for the new project. Click OK.
Next click on the Select tables from the Warehouse Catalog. In the window select the Drill database instance connection from the drop-down list and click OK.
MicroStrategy will query Drill and display all the available tables and views. In this case we will select two views that we created for the Twitter data, we will use the > button to move the views to the Tables being used box. Click on Save and Close.
Click OK and the new project is created in MicroStrategy Developer.
In Developer, open the Project, then open Public Objects and click on Reports. In the right-hand box, right-click and select New -> Report.
Click on the Freeform Sources tab and select the Drill data source. Make sure Create Freeform SQL Report is selected and click OK. This will allow us to enter a quick query to gather data.
The Freeform SQL Editor window will appear. Here a SQL query can be entered and Attributes can be specified for display. In this case a simple query was used that selects and groups the Tweet source and counts the number of times the same source appeared in a day. The Tweet source was added as a Text metric and the count as a number.
A bar chart is used to display the output; click on Data / Run Report to run the query. It can quickly be noticed that three sources makes up the majority of the captured Tweets.
Changing the view to tabular format and applying a filter - allows the user to see that iPhone, Android and Web Client are the three major sources of Tweets for this specific data set.
In this quick tutorial, we saw how MicroStrategy Analytics Enterprise can be configured to work with Apache Drill. Drill uses standard ODBC connectivity to provide the end user with easy data exploration capabilities on complex, schema-less data sets.
MicroStrategy announced post certification of Drill 0.6 and 0.7 with MicroStrategy Analytics Enterprise 9.4.1
Click on these links to get started with Apache Drill: