Welcome to Dremio Cloud Documentation - Dremio > > >

Microstrategy Desktop (Beta Integration)

Prerequisites

Dremio Microstrategy integration requires:

Microstrategy Desktop 10.0+ (Windows)
Install Dremio Connector

note:

For Microstrategy Server configuration information, email Dremio Support at support@dremio.com

Updating Dremio DSN Configuration for Microstrategy

Complete Dremio Connector installation, configure and test connectivity to cluster.
Run the ODBC Data Sources (64-bit) Windows application.
Open the System DSN tab, select ‘Dremio Connector,’ and hit the Configure button.
Add the following to the end of the Advanced Properties in Dremio Connector DSN:

;quoting=BACK_TICK;StringColumnLength=1000000

Microstrategy Configuration

Go to your Microstrategy installation directory (for example, under Program Files/Microstrategy).
Make a backup of the DATABASE.PDS file: rename DATABASE.PDS to DATABASE.PDS.NODREMIO.

Find an unused DB type in DATABASE.PDS and replace the contents of this section with the Dremio provided configuration.
For example:

<DSSOBJECT TYPE="DBMS" NAME="MapR Drill" ID="@dbms226">
      <DBMSType>59</DBMSType>
      <DBMSVersion>MapR Drill</DBMSVersion>
      <MSIVersion>MapR Drill DBMS version 1.0</MSIVersion>
      <MSIDescription>Initial version</MSIDescription>
      <!---->
      <!-- Paste in the DREMIO PROVIDED CONFIGURATION here.-->  
      <!---->
</DSSOBJECT>

Restart Microstrategy.

Connecting to a Dremio Cluster

Open Microstrategy, in a Dossier of your choice, click New Data, pick Databases and click on Select Tables.
Click + (Add) and go to the DSN Data Sources tab in the dialog. Select the Dremio DSN you configured above for Microstrategy and select the DSSOBJECT that was modified in DATABASE.PDS for version. Enter Dremio cluster credentials and Save.
Click Back and select MapR Drill from the list of data source types. Click on Select Tables. Your newly created source should be listed under here. You can now select tables of interest and connect to your data using Connect Live option.

Dremio Provided Configuration

Paste the following Dremio-provided configuration into the unused DB type section of the DATABASE.PDS file.

    <PROPERTYSET NAME="System SQL Templates">
    
      <PROPERTY NAME="CatalogTableSQL" VALUE="SELECT DISTINCT TABLE_SCHEMA as NAME_SPACE, TABLE_NAME as TAB_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA &lt;&gt;'INFORMATION_SCHEMA' and TABLE_SCHEMA &lt;&gt; 'sys'"/>
      <PROPERTY NAME="CatalogColumnSQL" VALUE="SELECT DISTINCT TABLE_SCHEMA NAME_SPACE, TABLE_NAME TAB_NAME, COLUMN_NAME COL_NAME, DATA_TYPE DATA_TYPE, CHARACTER_MAXIMUM_LENGTH DATA_LEN, NUMERIC_PRECISION_RADIX DATA_PREC, NUMERIC_SCALE DATA_SCALE From INFORMATION_SCHEMA.COLUMNS Where TABLE_NAME IN (#TABLE_LIST#) Order by  1,2,3" />
      <PROPERTY NAME="CatalogFullSQL" VALUE="SELECT DISTINCT TABLE_SCHEMA NAME_SPACE, TABLE_NAME TAB_NAME, COLUMN_NAME COL_NAME, DATA_TYPE DATA_TYPE, CHARACTER_MAXIMUM_LENGTH DATA_LEN, NUMERIC_PRECISION_RADIX DATA_PREC, NUMERIC_SCALE DATA_SCALE From INFORMATION_SCHEMA.COLUMNS" />	
      <PROPERTY NAME="TempTableDropSQL" VALUE="DROP TABLE #T#"/>
      <PROPERTY NAME="AllCatalogTableSQL" VALUE="SELECT DISTINCT TABLE_SCHEMA as NAME_SPACE, TABLE_NAME as TAB_NAME FROM INFORMATION_SCHEMA.COLUMNS" />
      <PROPERTY NAME="AllCatalogColumnSQL" VALUE="SELECT DISTINCT TABLE_SCHEMA NAME_SPACE, TABLE_NAME TAB_NAME, COLUMN_NAME COL_NAME, DATA_TYPE  DATA_TYPE, CHARACTER_MAXIMUM_LENGTH DATA_LEN, NUMERIC_PRECISION_RADIX DATA_PREC, NUMERIC_SCALE DATA_SCALE From INFORMATION_SCHEMA.COLUMNS Where TABLE_NAME IN (#TABLE_LIST#) Order by  1,2,3" />
      <PROPERTY NAME="AllCatalogFullSQL" VALUE="SELECT DISTINCT TABLE_SCHEMA NAME_SPACE, TABLE_NAME TAB_NAME, COLUMN_NAME COL_NAME, DATA_TYPE  DATA_TYPE, CHARACTER_MAXIMUM_LENGTH DATA_LEN, NUMERIC_PRECISION_RADIX DATA_PREC, NUMERIC_SCALE DATA_SCALE From INFORMATION_SCHEMA.COLUMNS" />
      <PROPERTY NAME="CatalogCacheMode" VALUE="3" />
      <PROPERTY NAME="CatalogRetrievalMode" VALUE="1" />
    </PROPERTYSET>

    <PROPERTYSET NAME="VLDB Syntax">
      <PROPERTY NAME="Insert Pattern" VALUE="#0INSERT INTO TABLE #1#2#3" />
      <PROPERTY NAME="Insert Pattern1" VALUE="#0INSERT INTO TABLE #1 #5#2#3#4"/>
      <PROPERTY NAME="Union Separator Pattern" VALUE="#0UNION ALL#0" />
      <PROPERTY NAME="Create Temp Table Pattern" VALUE="CREATE TEMPORARY TABLE #0#1#2" />
      <PROPERTY NAME="Temp Implicit Create Pattern" VALUE="CREATE TEMPORARY TABLE #7 AS #6#0#1#2#3#4#5#8" />
      <PROPERTY NAME="Perm Implicit Create Pattern" VALUE="CREATE TABLE #13 AS #6#0#1#2#3#4#5#8" />
      <PROPERTY NAME="Cross Join Pattern92" VALUE="#1#0join&#9;#2" />
      <PROPERTY NAME="Table Pattern" VALUE="`#0`&#9;#1" />
      <PROPERTY NAME="Column Pattern" VALUE="#0.`#1`" />
    </PROPERTYSET>

    <PROPERTYSET NAME="VLDB Insert">
      <PROPERTY NAME="UNION Multiple INSERTs" VALUE="0" />
    </PROPERTYSET>

    <PROPERTYSET NAME="VLDB Data Type">
      <PROPERTY NAME="Date Format" VALUE="yyyy-mm-dd" />
      <PROPERTY NAME="Date Pattern" VALUE="'#0'" />
      <PROPERTY NAME="TimeStamp Pattern" VALUE="cast('#0' as TIMESTAMP)" />
    </PROPERTYSET>

    <PROPERTYSET NAME="VLDB Create">
      <PROPERTY NAME="Table Creation Type" VALUE="1" />
      <PROPERTY NAME="Fallback Table Type" VALUE="0" />
      <PROPERTY NAME="Intermediate Table Type" VALUE="1" />
    </PROPERTYSET>

    <PROPERTYSET NAME="VLDB Select">
      <PROPERTY NAME="Sub Query Type" VALUE="6" />
      <PROPERTY NAME="JOIN Type" VALUE="1" />
      <PROPERTY NAME="ORDER BY Column" VALUE="1" />
      <PROPERTY NAME="SELECT DISTINCT at Same Level" VALUE="2" />
      <PROPERTY NAME="Full Outer Join Support" VALUE="1" />
      <PROPERTY NAME="Integer Constant In Metric" VALUE="1" />
    </PROPERTYSET>

    <PROPERTYSET NAME="VLDB Report">
      <PROPERTY NAME="DBRole DB Type" VALUE="7300" />
      <PROPERTY NAME="DBRole DB Version" VALUE="254" />
    </PROPERTYSET>

    <PROPERTYSET NAME="VLDB Function">
      <PROPERTY NAME="Null Check" VALUE="0" />
      <PROPERTY NAME="Zero Check" VALUE="0" />
    </PROPERTYSET>

    <FUNCTION_REF ID="8107C33EDD9911D3B98100C04F2233EA" SQLPATTERN="CASE WHEN #0 IS NULL Then CAST (0 as DOUBLE) ELSE CAST (#0 as DOUBLE) END" />
    <FUNCTION_REF ID="8107C33FDD9911D3B98100C04F2233EA" SQLPATTERN="CASE when #0 = 0 then NULL else #0 end" />
    <FUNCTION_REF ID="B2C0FA4420C711d6AD1400C04F0423DE" SQLPATTERN="(Case when #0 then #1 else #2 end)" />
    <FUNCTION_REF ID="8107C320DD9911D3B98100C04F2233EA" SQLPATTERN="#0 BETWEEN #1 and #2"/>

    <!-- Mathematical functions -->
    <FUNCTION_REF ID="F262C829DDA611D3B98100C04F2233EA" SQLPATTERN="ROUND(#0)" />
    <FUNCTION_REF ID="5CCBF590DAB311d5AD0E00C04F0423DE" SQLPATTERN="ROUND(#0, #1)" />
    <FUNCTION_REF ID="F262C817DDA611D3B98100C04F2233EA" SQLPATTERN="CEIL(#0)" />
    <FUNCTION_REF ID="F262C81EDDA611D3B98100C04F2233EA" SQLPATTERN="FLOOR(#0)" />
    <FUNCTION_REF ID="F262C81CDDA611D3B98100C04F2233EA" SQLPATTERN="EXP(#0)" />
    <FUNCTION_REF ID="F262C820DDA611D3B98100C04F2233EA" SQLPATTERN="LN(#0)" />
    <FUNCTION_REF ID="F262C822DDA611D3B98100C04F2233EA" SQLPATTERN="LOG10(#0)" />
    <FUNCTION_REF ID="F262C821DDA611D3B98100C04F2233EA" SQLPATTERN="LOG(#1, #0)"/>
    <FUNCTION_REF ID="F262C824DDA611D3B98100C04F2233EA" SQLPATTERN="POWER(#0, #1)" />
    <FUNCTION_REF ID="F262C82CDDA611D3B98100C04F2233EA" SQLPATTERN="SQRT(#0)" />
    <FUNCTION_REF ID="F262C831DDA611D3B98100C04F2233EA" SQLPATTERN="Mod(#0,#1)" />
    <FUNCTION_REF ID="F262C82ADDA611D3B98100C04F2233EA" SQLPATTERN="SIN(#0)" />
    <FUNCTION_REF ID="F262C813DDA611D3B98100C04F2233EA" SQLPATTERN="ASIN(#0)" />
    <FUNCTION_REF ID="F262C830DDA611D3B98100C04F2233EA" SQLPATTERN="LN(#0+SQRT(POW(#0, 2)+1))" />
    <FUNCTION_REF ID="F262C82BDDA611D3B98100C04F2233EA" SQLPATTERN="((EXP(#0) - EXP(#0*(-1)))/2)" />
    <FUNCTION_REF ID="F262C819DDA611D3B98100C04F2233EA" SQLPATTERN="COS(#0)" />
    <FUNCTION_REF ID="F262C81ADDA611D3B98100C04F2233EA" SQLPATTERN="(EXP(#0)+EXP(-#0))/2" />
    <FUNCTION_REF ID="F262C811DDA611D3B98100C04F2233EA" SQLPATTERN="ACOS(#0)" />
    <FUNCTION_REF ID="F262C812DDA611D3B98100C04F2233EA" SQLPATTERN="LN(#0+SQRT(#0-1)*SQRT(#0+1))" />
    <FUNCTION_REF ID="F262C82DDDA611D3B98100C04F2233EA" SQLPATTERN="TAN(#0)" />
    <FUNCTION_REF ID="F262C82EDDA611D3B98100C04F2233EA" SQLPATTERN="(-1+(2/(1+EXP(-2*#0))))" />
    <FUNCTION_REF ID="F262C814DDA611D3B98100C04F2233EA" SQLPATTERN="ATAN(#0)" />
    <FUNCTION_REF ID="F262C816DDA611D3B98100C04F2233EA" SQLPATTERN="((LN(1+#0)-LN(1- #0))/2)" />
    <FUNCTION_REF ID="F262C81BDDA611D3B98100C04F2233EA" SQLPATTERN="DEGREES(#0)" />
    <FUNCTION_REF ID="8107C331DD9911D3B98100C04F2233EA" SQLPATTERN="EXP(AVG(LN(#0)))" />
    <FUNCTION_REF ID="F262C825DDA611D3B98100C04F2233EA" SQLPATTERN="CAST((#0)/(CASE WHEN (#1)=0 THEN NULL ELSE (#1) END) AS INT)" />
    <FUNCTION_REF ID="F262C826DDA611D3B98100C04F2233EA" SQLPATTERN="RADIANS(#0)" />
    <FUNCTION_REF ID="B2C0FA4220C711d6AD1400C04F0423DE" SQLPATTERN="MIN(#0)" />
    <FUNCTION_REF ID="B2C0FA4320C711d6AD1400C04F0423DE" SQLPATTERN="MAX(#0)" />
    <FUNCTION_REF ID="B2C0FA4120C711d6AD1400C04F0423DE" SQLPATTERN="MIN(#0)" />
    <FUNCTION_REF ID="B2C0FA4020C711d6AD1400C04F0423DE" SQLPATTERN="MAX(#0)" />
    <FUNCTION_REF ID="8107C327DD9911D3B98100C04F2233EA" SQLPATTERN="ABS(#0)" />
    <FUNCTION_REF ID="F262C82FDDA611D3B98100C04F2233EA" SQLPATTERN="CAST(#0 AS INT)" />

    <!-- STATISTICAL functions -->


    <!-- DATE functions -->
    <FUNCTION_REF ID="6F7DF5F8449111d5BEA300B0D01A55EF" SQLPATTERN="date_part('year',#0)" />
    <FUNCTION_REF ID="6F7DF5F6449111d5BEA300B0D01A55EF" SQLPATTERN="date_part('month',#0)" />
    <FUNCTION_REF ID="6F7DF605449111d5BEA300B0D01A55EF" SQLPATTERN="SUBSTRING(#0, #1, #2)" />
    <FUNCTION_REF ID="C50F2A4F75AE49d6B20E00224967B500" SQLPATTERN="cast(#0 as date)" />
    <FUNCTION_REF ID="6F7DF5F1449111d5BEA300B0D01A55EF" SQLPATTERN="current_date" />
    <FUNCTION_REF ID="6F7DF5F9449111d5BEA300B0D01A55EF" SQLPATTERN="current_timestamp" />
    <FUNCTION_REF ID="6F7DF5FA449111d5BEA300B0D01A55EF" SQLPATTERN="current_time" />
    <FUNCTION_REF ID="6F7DF5F2449111d5BEA300B0D01A55EF" SQLPATTERN="DAYOFMONTH(#0)" />
    <FUNCTION_REF ID="6F7DF5FB449111d5BEA300B0D01A55EF" SQLPATTERN="date_part('hour',#0)" />
    <FUNCTION_REF ID="6F7DF5FC449111d5BEA300B0D01A55EF" SQLPATTERN="date_part('minute',#0)" />
    <FUNCTION_REF ID="6F7DF5FD449111d5BEA300B0D01A55EF" SQLPATTERN="date_part('second',#0)" />
    <FUNCTION_REF ID="0150F348CB0B4d9aB8CCA458C1ACC4B0" SQLPATTERN="DATEDIFF(#1,#0)" />
    <FUNCTION_REF ID="AB08D4FC700B4ee58C021BFA2ABD77D0" SQLPATTERN="DATE_ADD(#0,#1)" />
	<FUNCTION_REF ID="6F7DF5F3449111d5BEA300B0D01A55EF" SQLPATTERN="CAST(TO_CHAR(#0,'e') AS INTEGER)" />
	<FUNCTION_REF ID="6F7DF5F5449111d5BEA300B0D01A55EF" SQLPATTERN="CAST(TO_CHAR(#0,'w') AS INTEGER)" />

    <!-- STRING functions -->
    <FUNCTION_REF ID="6F7DF600449111d5BEA300B0D01A55EF" SQLPATTERN="LENGTH(#0)" />
    <FUNCTION_REF ID="8107C347DD9911D3B98100C04F2233EA" SQLPATTERN="UPPER(#0)" />
    <FUNCTION_REF ID="6F7DF601449111d5BEA300B0D01A55EF" SQLPATTERN="LOWER(#0)" />
    <FUNCTION_REF ID="6F7DF607449111d5BEA300B0D01A55EF" SQLPATTERN="TRIM(#0)" />
    <FUNCTION_REF ID="6F7DF602449111d5BEA300B0D01A55EF" SQLPATTERN="LTRIM(#0)" />
    <FUNCTION_REF ID="6F7DF604449111d5BEA300B0D01A55EF" SQLPATTERN="RTRIM(#0)" />
    <FUNCTION_REF ID="6F7DF603449111d5BEA300B0D01A55EF" SQLPATTERN="INSTR(#1, #0)" />
    <FUNCTION_REF ID="6F7DF608449111d5BEA300B0D01A55EF" SQLPATTERN="SUBSTR(#0,(LENGTH(#0) - #1 + 1))" />
    <FUNCTION_REF ID="6F7DF609449111d5BEA300B0D01A55EF" SQLPATTERN="SUBSTR(#0, 1, #1)" />
    <FUNCTION_REF ID="8107C357DD9911D3B98100C04F2233EA" SQLPATTERN="CONCAT(#0#&lt;,' ', #*#&gt;)" />
    <FUNCTION_REF ID="6F7DF606449111d5BEA300B0D01A55EF" SQLPATTERN="CONCAT(UPPER(SUBSTR(#0, 1, 1)),LOWER(SUBSTR(#0, 2)))" />

    <!-- OLAP functions -->
    <FUNCTION_REF ID="6F7DF5FF449111d5BEA300B0D01A55EF" SQLPATTERN="CONCAT(#0#&lt;, #*#&gt;)" />
    <FUNCTION_REF ID="8107C32FDD9911D3B98100C04F2233EA" SQLPATTERN="STDDEV(#0#&lt; #*#&gt;)" />
    <FUNCTION_REF ID="548C68A0F60811d3ACC300C04F0423DE" SQLPATTERN="stddev_pop(#0#&lt; #*#&gt;)" />
    <FUNCTION_REF ID="8107C330DD9911D3B98100C04F2233EA" SQLPATTERN="VARIANCE(#0#&lt; #*#&gt;)" />
    <FUNCTION_REF ID="D64F1B10F6C011d3ACC300C04F0423DE" SQLPATTERN="VAR_POP(#0#&lt; #*#&gt;)" />

Was this page helpful?

Glad to hear it! Thank you for your feedback. Sorry to hear that. Thank you for your feedback.