Microstrategy Desktop (Beta Integration)

Prerequisites

Dremio Microstrategy integration requires:

  • Microstrategy Desktop 10.0+ (Windows)
  • Install Dremio Connector
  • Contact Dremio to obtain Microstrategy configuration

Updating Dremio DSN Configuration for Microstrategy

  1. Complete Dremio Connector installation, configure and test connectivity to cluster.

  2. Run the ODBC Data Sources (64-bit) Windows application.

  3. Open the System DSN tab, select 'Dremio Connector,' and hit the Configure button.

  4. Add the following to the end of the Advanced Properties in Dremio Connector DSN:

    ;quoting=BACK_TICK;StringColumnLength=1000000
    

Microstrategy Configuration

  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 configuration provided by Dremio. For example:
     <DSSOBJECT TYPE="XYZ" NAME="XYZ" ID="XYZ">
           <DBMSType>XYZ</DBMSType>
           <DBMSVersion>XYZ</DBMSVersion>
           <MSIVersion>XYZ</MSIVersion>
           <MSIDescription>XYZ</MSIDescription>
           <!---->
           <!--PASTE IN THE DREMIO CONFIGURATION HERE.-->  
           <!---->
     </DSSOBJECT>
    
  4. Restart Microstrategy.

Dremio configuration

<DSSOBJECT TYPE="DBMS" NAME="MapR Drill 1.x" ID="@dbms235">
    <DBMSType>59</DBMSType>
    <DBMSVersion>MapR Drill 1.x</DBMSVersion>
    <MSIVersion>MapR Drill DBMS version 1.0</MSIVersion>
    <MSIDescription>Initial version</MSIDescription>

<PROPERTYSET NAME="System SQL Templates">

      <PROPERTY NAME="CatalogTableSQL" VALUE="SELECT DISTINCT TABLE_SCHEMA as NAME_SPACE, TABLE_NAME as TAB_NAME FROM INFORMATION_SCHEMA.`TABLES` where TABLE_SCHEMA='#?SCHEMA_NAME?#' ORDER BY TABLE_NAME DESC"/>
      <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 &#13;&#10;WHERE TABLE_NAME in (#TABLE_LIST#) AND TABLE_SCHEMA='#?SCHEMA_NAME?#' &#13;&#10;ORDER BY 1, 2" />
      <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#0 , #2" />
      <PROPERTY NAME="Advanced Table Pattern" VALUE= "`#0`" />
      <PROPERTY NAME="Column Pattern" VALUE="#0.#1" />
      <PROPERTY NAME="Space In Columnalias Pattern" VALUE="`#0`" />
      <PROPERTY NAME="Lowercase In Columnname Pattern" VALUE="`#0`" />
      <PROPERTY NAME="Space In Columnname Pattern" VALUE="`#0`" />
      <PROPERTY NAME="Uppercase In Columnname Pattern" VALUE="`#0`" />
    </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="0" />
      <PROPERTY NAME="Table Prefix" VALUE="`__datasetDownload`." />s
    </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="263" />
    </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;)" />

<FUNCTION_REF ID="8107C358DD9911D3B98100C04F2233EA" SQLPATTERN="first_value(#0) over(#1)" />
<FUNCTION_REF ID="8107C359DD9911D3B98100C04F2233EA" SQLPATTERN="last_value(#0) over(#1)" />
<FUNCTION_REF ID="D221F912B35540E3A1204ED6CC9A8267" SQLPATTERN="lag(#0#&lt;, #*#&gt;) over ([#P] [#O])" OLAPDBPATTERN="P:o|O:r" />
<FUNCTION_REF ID="D221F913B35540E3A1204ED6CC9A8267" SQLPATTERN="lead(#0#&lt;, #*#&gt;) over ([#P] [#O])" OLAPDBPATTERN="P:o|O:r" />    
</DSSOBJECT>

Connecting to a Dremio Cluster

  1. Open Microstrategy, in a Dossier of your choice, click New Data, pick Databases and click on Select Tables.

  2. 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.

  3. 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.


results matching ""

    No results matching ""