Version 2021

PostgreSQL Support in MicroStrategy

This document discusses PostgreSQL supports in MicroStrategy, provides an overview of the integration, summarizes supported workflows, and describes configuration and implementation details. We have focused on the features that have been developed specifically to support and enhance PostgreSQL integration.  

Executive Summary

  • All relevant MicroStrategy capabilities are supported for use as a data source for Intelligence server and Workstation.

  • PostgreSQL 14.x is supported and supported in MicroStrategy 2021 Update 4.

Key Takeaways

Seamless connection. Connectivity is supported from Intelligence server as well as from Workstation. Connectivity is supported via ODBC drivers with DSN or DSN-less, as well as JDBC drivers. 

Data security. Security is enforced at multiple layers. For data transfer, encrypted connections, like SSL encryption are supported.  Reusing PostgreSQL security models is supported by assigning PostgreSQL credentials to a MicroStrategy user or group. Enterprise authentication solutions like LDAP or integrated authentication using Kerberos are also supported. 

Advanced analytic capabilities. MicroStrategy functions mostly reuse PostgreSQL functions for analysis so these are executed on the database for faster computation. Support exists for OLAP functions. PostgreSQL user-defined functions can also be invoked.  

Optimal analysis and data retrieval from PostgreSQL. MicroStrategy follows multiple strategies to optimize analysis. Smart query generation that distributes computation between PostgreSQL and Intelligence server, computing aggregations on the database, and normalization of data. SQL global optimization also reduces the number of SQL statements needed for a calculation. 

Model your data.  Architects explore and customize how PostgreSQL catalog is retrieved. Users can model PostgreSQL tables, views, and columns into tables, attributes, and facts for further analysis. MicroStrategy identifies the best data type for a PostgreSQL table column. Such data assets are modeled as attributes or facts. MicroStrategy can also use PostgreSQL capabilities like indexed views, in-memory OLTP, and clustered column store indexes. 

Blend your PostgreSQL data with other sources. Using MicroStrategy multi-source capabilities, data from your PostgreSQL database can be integrated into other RDBMS supported by MicroStrategy. With MicroStrategy optimal handling, calculations can still be distributed with the project’s primary database instance. 

PostgreSQL data with multiple tools. Your PostgreSQL analysis is accessible to the MicroStrategy platform as a report, datamart, Intelligent Cube, or on-demand retrieval using live mode. Data can be analyzed as a report, document, or dossier.

Self-service support. MicroStrategy users can directly query your PostgreSQL database using our data import component. Users can wrangle, model, and enrich your data from PostgreSQL, as well as blend with other data sources. Users can directly import a table, build a custom query, or execute stored procedures and scripts. MicroStrategy supports storing PostgreSQL analysis in-memory as well as offering live connectivity for efficient retrieval by dossiers.  

Distribution of analysis from PostgreSQL. MicroStrategy allows you to distribute PostgreSQL included in MicroStrategy dossiers. These can be between MicroStrategy products like Workstation or other MicroStrategy platform environments using the same MicroStrategy release.  

Transaction services support. With MicroStrategy transactions services support for PostgreSQL, users can record selections in a PostgreSQL table.

Optimal performance for your PostgreSQL data analysis.  Advanced MicroStrategy users can take full advantage of PostgreSQL SQL capabilities, such as querying data based on a table index. MicroStrategy administrators can also fine tune the PostgreSQL access at the database connection level.

PostgreSQL as MicroStrategy repository. PostgreSQL can also be used as a repository for metadata, Platform Analytics, history list, and Collaboration server. 

Commitment for support. MicroStrategy supports multiple PostgreSQL versions such as PostgreSQL 10.x, PostgreSQL 11.x, PostgreSQL 12.x, PostgreSQL 13.x, and PostgreSQL 14.x. Amazon Aurora PostgreSQL and Azure Database for PostgreSQL are also supported by MicroStrategy. MicroStrategy supports PostgreSQL at diamond level. MicroStrategy frequently executes regression and performance testing to ensure our platform is optimized to work best with this data source. 

Customer success.  PostgreSQL is the out-of-the-box repository for MicroStrategy products. MicroStrategy uses PostgreSQL as the default database in our cloud environments. The setup is done during the installation process.