Dremio Cloud
Dremio Software
 
On this page

    Microstrategy Desktop (Beta Integration)

    Supported Versions

    Microstrategy Desktop 10.0+ (Windows)

    note:

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

    Prerequisites

    • Ensure that you are using Dremio v22.0 or later.
    • Ensure that your operating system is 64-bit Windows 10 or later.
    • Download, install, and configure the ODBC driver for Arrow Flight SQL.

    Updating the DSN Configuration

    1. Launch ODBC Data Sources on your Windows system.

    2. Select the System DSN tab.

    3. Select the DSN entry that you created when you configured the ODBC driver for Arrow Flight SQL.

    4. Click Configure.

    5. In the Advanced section, add these key/value pairs:
      First pair

      • Key: quoting
      • Value: BACK_TICK

      Second pair

      • Key: StringColumnLength
      • Value: 1000000

    Configuring Microstrategy Desktop

    1. Go to your Microstrategy installation directory (for example, under Program Files/Microstrategy).
    2. Make a backup of the DATABASE.PDS file: rename DATABASE.PDS to DATABASE.PDS.NODREMIO.
    3. 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>
    4. Restart Microstrategy.

    Connecting to Dremio

    1. Open Microstrategy.

    2. Click New Dossier.

    3. Click New Data.

    4. Click Databases.

    5. Select Build a Query and click Next.

    6. Click +. 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.

    7. 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 XML 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;)" />