|Share this article:|
Vertica Integration with MicroStrategy: Connection Guide
About Vertica Connection Guides
Vertica connection guides provide basic instructions for connecting a third-party partner product to Vertica. Connection guides are based on our testing with specific versions of Vertica and the partner product.
Vertica and MicroStrategy: Latest Versions Tested
MicroStrategy 11 Server on Premises
MicroStrategy 11 Desktop
|Partner Product Platform||Windows Server 2012 R2|
Vertica ODBC 9.2
|Vertica Server||Vertica Database 9.2.0-0|
MicroStrategy is an enterprise data warehousing and business intelligence platform, offering powerful analytics and the ability to build and deploy interactive dossiers.
The MicroStrategy suite of products includes the following:
- MicroStrategy Server: Multi-user environment that enables collaborative access to MicroStrategy dossiers.
- MicroStrategy Desktop: Desktop application that offers a graphical interface for creating visualizations and interactive dossiers.
For details about MicroStrategy, see the MicroStrategy website:
To install a trial version of MicroStrategy:
Navigate to Get Started with MicroStrategy.
- To install MicroStrategy Server on premises, select MicroStrategy 2019 Pilot and click REQUEST YOUR PILOT.
- To install MicroStrategy Desktop, select MicroStrategy Desktop and click DOWNLOAD DESKTOP.
- Provide your account details.
- Follow the on-screen instructions to download the zip file.
- Extract the contents of the zip file on your computer.
For details about MicroStrategy installation, see the MicroStrategy documentation.
Install the Vertica Client Driver
Before you can connect MicroStrategy to Vertica, you must install the Vertica ODBC driver. Follow these steps:
- Navigate to the Client Drivers page on the Vertica website.
Download the client driver package that is compatible with the architecture of your operating system and Vertica server version.
For details about client and server compatibility, see Client Driver and Server Version Compatibility in the Vertica documentation.
Double-click the installer and follow the prompts. When prompted to select one or more drivers, select ODBC.
Create a DSN as described in Creating an ODBC Data Source Name (DSN).
From the Windows Start menu, open MicroStrategy Secure Enterprise to open the MicroStrategy landing page in your browser.
On the MicroStrategy Web tab, click Launch.
Log in to MicroStrategy Web, then select a project from the list.
When you create reports, they will be available in this project only.
Click Add External Data to create a connection to Vertica.
The Select Import Option page opens.
Select an option for defining the data to import and click Next.
In this example, we have chosen to import the data from tables.
On the Import from Tables page, click the plus sign (+) to create a new connection.
The Data Source page opens.
On the Data Source page, you can choose to connect to Vertica using the DSN that you created earlier, or you can specify the connection details to connect without the DSN.
If you choose the DSN-less Data Sources option, then you must supply the connection details:
- Server Name: Vertica server name or IP address and port
- Port Number: The default port is 5433
- Database Name:.Vertica Database Name
- User: Username for Vertica Database
- Password: Password for Vertica Database
- Data Source Name: Name of Data Source
- Click Save to save the connection information.
On the Import from Tables page, select the tables from which you want to import data, then click Prepare Data.
On the Preview page, you can verify that the joins are configured correctly and preview the data.
- Click Finish.
On the Data Access Mode page, choose one of these options:
- Connect Live: MicroStrategy queries the Vertica database to fetch the latest data.
- Import as an In-memory Dataset: MicroStrategy imports all the specified data and stores it in memory.
We recommend that you select Connect Live to take advantage of the processing power of your Vertica database.
Provide a name for this dataset definition and click Save.
You can choose to create a dossier or a document based on the dataset.
In this example, we have chosen to create a dossier.
On the New Layout page, select the measures and attributes to include in the dossier.
Click the Save icon in the upper left of the page and specify a location to save the dossier.
Open the dossier and click the Presentation icon on the upper right of the page to preview the data.
Connect MicroStrategy Desktop with MicroStrategy Server
- From the Windows Start menu, open MicroStrategy Secure Enterprise to open the MicroStrategy landing page in your browser.
Select MicroStrategy Desktop and click Configure.
The file MACHINE_NAME
Open MicroStrategy Desktop from the installation location.
To connect MicroStrategy Desktop with MicroStrategy Server, select Environments then Add New Environment Connection.
Select the file MACHINE_NAME
_8080.mstrcfrom the download location.
The new environment is configured.
Select a project in MicroStrategy Server.
In this example, the VMart project is selected.
Click New Dossier to create a new dashboard.
The data sources created in MicroStrategy Server are displayed in MicroStrategy Desktop.
If you can create new data, then follow steps 5 through 13 in Connect MicroStrategy Server with Vertica.
To use datasets that were created in MicroStrategy Server, select Existing Dataset from the DATASETS pane and click Save to save this dossier to the server project.
Select a dataset from the list and click Insert.
Select measures and attributes from the dataset to prepare a dossier.
Click the Presentation icon to preview the dossier.
Close the dossier windows and open MicroStrategy Server.
The new dossier is now available in MicroStrategy Server.
- MicroStrategy does not support Binary, LongVarBinary, and VarBinary data types.
- MicroStrategy truncates the milliseconds in TimeStampTz, TimeTz, TimeStamp and Time data types.
- MicroStrategy does not support Timezone values.
- MicroStrategy supports a maximum length of 32,000 for Char, VarChar, and LongVarChar data types.
For More Information
- MicrStrategy Product Documentation
- MicroStrategy and Vertica: Partner QuickStart (Pharma Sales)
- Vertica Community Edition
- Vertica User Community
- Vertica Documentation