MicroStrategy ONE

How to Connect to Druid

Druid is a column oriented, open source, distributed data store. For more details about Druid capabilities and design see the official Druid documentation.

See the following article on MicroStrategy Community for a Performance comparison between Druid data store and MicroStrategy Super Cubes.

How to Connect to Druid with MicroStrategy Developer

  1. Create a database instance. Select Generic Database for the Database connection type.

  2. Add a new database connection.

  3. In the database connection editor, click on the Advanced tab > Additional connection string parameters and add the following:
    • JDBC;MSTR_JDBC_JAR_FOLDER=/opt/mstr/MicroStrategy/install/JDBC;DRIVER=org.apache.calcite.avatica.remote.Driver;URL={jdbc:avatica:remote:url=http://34.227.151.55:8082/druid/v2/sql/avatica/};
    • Download the Druid driver.
    • Replace the ip address and port (34.227.151.55:8020) with the Druid's broker ip address and port.
    • Replace /opt/mstr/MicroStrategy/install/JDBC with path where your Druid JDBC driver is located.
  4. Add a new database login.

  5. Click OK in the Database Login editor and in the Database Connection editor.
  6. Click OK in the two warning messages that appear.
  7. Click OK in the Database Instance editor.
  8. Make a backup of DATABASE.PDS file located in C:\Program Files (x86)\Common Files\MicroStrategy
  9. Edit the DATABASE.PDS file:
    • Search for the string "<DSSOBJECT TYPE="DBMS" NAME="Generic DBMS" ID="@dbms38".
    • Then, search for the next tag "<PROPERTY NAME="CatalogRetrievalMode" VALUE="2" />" and

      Change VALUE="2" to VALUE="0".

    • Next, in the same DSSOBJECT tag context, add the following entry:

      <PROPTERYSET NAME="VLDB Syntax">

      <PROPERTY NAME="Space In Tablename Pattern" VALUE= '"#0"' />

      <PROPERTY NAME="Space In Columnname Pattern" VALUE= '"#0"' />

      <PROPERTY NAME="Space In Columnalias Pattern" VALUE= '"#0"' />

      </PROPERTYSET>

  10. Save DATABASE.PDS file.
  11. Go to MicroStrategy Developer, edit the database instance created earlier.
  12. Click Upgrade.

  13. Select the edited DATABASE.PDS file and click Load.

  14. In the Available database types list, select Generic DBMS and move it to the Existing database types list.

  15. Click Yes when prompted to replace the existing Generic DBMS database type.
  16. Click OK multiple times.
  17. Restart the Intelligence Server.

Connecting a MicroStrategy Project to Druid

  1. Log into a project in Developer and open Schema > Warehouse Catalog.
  2. Open the Warehouse Catalog options and click Yes.

  3. Under Categories, choose Read Settings and select Use one or more SQL statements that query directly the database catalog tables. Click Settings.

  4. Replace the top query with: 

    SELECT TABLE_SCHEMA  NAME_SPACE, TABLE_NAME TAB_NAME FROM INFORMATION_SCHEMA.TABLES

  5. Replace the bottom query with:

    SELECT TABLE_SCHEMA NAME_SPACE,TABLE_NAME TAB_NAME,COLUMN_NAME COL_NAME,DATA_TYPE DATA_TYPE,CHARACTER_MAXIMUM_LENGTH DATA_LEN,NUMERIC_PRECISION DATA_PREC,NUMERIC_SCALE DATA_SCALE
FROM INFORMATION_SCHEMA.COLUMNS
    WHERE TABLE_NAME in (#TABLE_LIST#)
    ORDER BY 1, 2
  6. Click OK.
  7. Click the icon to Read the Warehouse Catalog and see the available tables from the Druid data store.