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.PDSand 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, pickDatabasesand click onSelect Tables.Click
+(Add) and go to theDSN Data Sourcestab in the dialog. Select the Dremio DSN you configured above for Microstrategy and select the DSSOBJECT that was modified inDATABASE.PDSfor version. Enter Dremio cluster credentials and Save.Click
Backand selectMapR Drillfrom the list of data source types. Click onSelect Tables. Your newly created source should be listed under here. You can now select tables of interest and connect to your data usingConnect Liveoption.
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 <>'INFORMATION_SCHEMA' and TABLE_SCHEMA <> '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	#2" />
<PROPERTY NAME="Table Pattern" VALUE="`#0`	#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#<,' ', #*#>)" />
<FUNCTION_REF ID="6F7DF606449111d5BEA300B0D01A55EF" SQLPATTERN="CONCAT(UPPER(SUBSTR(#0, 1, 1)),LOWER(SUBSTR(#0, 2)))" />
<!-- OLAP functions -->
<FUNCTION_REF ID="6F7DF5FF449111d5BEA300B0D01A55EF" SQLPATTERN="CONCAT(#0#<, #*#>)" />
<FUNCTION_REF ID="8107C32FDD9911D3B98100C04F2233EA" SQLPATTERN="STDDEV(#0#< #*#>)" />
<FUNCTION_REF ID="548C68A0F60811d3ACC300C04F0423DE" SQLPATTERN="stddev_pop(#0#< #*#>)" />
<FUNCTION_REF ID="8107C330DD9911D3B98100C04F2233EA" SQLPATTERN="VARIANCE(#0#< #*#>)" />
<FUNCTION_REF ID="D64F1B10F6C011d3ACC300C04F0423DE" SQLPATTERN="VAR_POP(#0#< #*#>)" />
Was this page helpful?
Glad to hear it! Thank you for your feedback.
Sorry to hear that. Thank you for your feedback.