My Account | Forum | Blog | Careers | Contact Us   888.537.8135  

Home > Products & Services > Software > Intelligence Server > Database Optimizations

Intelligence Server Database Optimizations

MicroStrategy Intelligence Server™

Database Optimizations:
Aster nCluster | Greenplum | HP Neoview | IBM DB2 | Microsoft SQL Server
Netezza | Oracle | Sybase | Teradata | Vertica

 

MicroStrategy Optimizations for Aster nCluster

Aster nCluster-specific SQL syntax

  • RANK function
  • OLAP functions
  • Full Outer joins
  • Use of CASE expressions

Multi-pass SQL for analytical sophistication

  • Partition key on intermediate tables
  • SELECT INTO syntax for speed

Seamless support for key Aster nCluster features

  • Aster nCluster function library

Extensions to Aster nCluster functionality

  • Aggregate awareness with physical summary tables
  • Middle-tier computation of calculations not available in Aster nCluster
  • Middle-tier caching via Intelligent Cubes
  • Report caching

MicroStrategy Optimizations for Greenplum

Greenplum-specific SQL Syntax

  • CASE expressions
  • Sub queries
  • Set operators
  • Intermediate tables use partition key (DISTRIBUTED BY)

Multi-pass SQL for analytical sophistication

  • Use of derived tables
  • CREATE TABLE AS syntax for speed
  • Use of temporary tables

Seamless support for key Greenplum features

  • Greenplum function library

Extensions to Greenplum functionality

  • Aggregate awareness with physical summary tables
  • Middle-tier computation of calculations not available in Greenplum
  • Middle-tier caching via Intelligent Cubes
  • Report caching

MicroStrategy Optimizations for HP Neoview

Neoview-specific SQL Syntax

  • CASE expressions
  • Subqueries

Use of Multi-pass SQL for analytical sophistication

  • Use of volatile tables
  • Use of derived tables
  • Control over clustering keys for intermediate results 
  • CREATE TABLE AS SELECT for speed
  • Statistics collection on intermediate results

Seamless support for key Neoview features

  • Data clustering and partitioning 
  • Neoview function library

Extensions to Neoview functionality

  • Aggregate awareness with physical summary tables
  • Middle-tier computation of calculations not available in HP Neoview
  • Middle-tier caching via Intelligent Cubes
  • Report caching
  • Application-level partitioning

OLAP function support via MicroStrategy Analytical Engine

Unique Optimizations for IBM DB2

DB2-specific SQL syntax

  • Multi-pass SQL for analytical sophistication
  • Seamless support for key DB2 features such as Materialized query tables
  • Extensions to DB2 functionality including aggregate awareness with physical summary tables
  • Integration with DB2 Data Mining and OLAP Acceleration

IBM WebSphere Application Server

  • MicroStrategy Web Universal Edition on IBM Application Server
  • MicroStrategy SDK on IBM WebSphere Application Server
  • IBM WebSphere Portal 6.0.1 is certified

IBM WebSphere Portal Server

  • Portal Integration Kit (PIK) integration with IBM WebSphere Portal Server
  • Fully Certified–“Portlets ready for WebSphere”
  • Single sign-on
  • Seamless unified interface
  • Interoperable out-of-the-box

MicroStrategy Optimizations for Microsoft SQL Server

The MicroStrategy platform leverages Microsoft's highly parallel query processing architecture to deliver the highest performance and efficiency levels.

SQL Server-specific SQL syntax

  • RANK functions
  • CASE expressions
  • Full outer joins
  • Set operators
  • Sub queries

Seamless support for key SQL Server features

  • Materialized views
  • XML data

Multi-pass SQL for analytical sophistication

  • Use of no local temporary tables
  • Use of SELECT...INTO # syntax for temporary tables
  • Use of derived tables
  • Use of common table expressions
  • Statistics collection on intermediate results

Extensions to SQL Server functionality

  • Aggregate awareness with physical summary tables
  • Middle-tier caching via Intelligent Cubes
  • Middle-tier report caching
  • Application-level partitioning

Supported Web Servers

  • Microsoft IIS 5, 6 and 7

MicroStrategy Optimizations for Netezza

Netezza-specific SQL Syntax

  • NZSQL Analytical functions (OLAP Functions) 
  • CASE expressions
  • Full outer joins
  • Set operators 
  • Subqueries

Use of Multi-pass SQL for analytical sophistication

  • Use of temporary tables
  • Use of derived tables
  • Control over distribution keys for intermediate results 
  • CREATE TABLE AS syntax for speed

Seamless support for key Netezza features

  • Materialized views 
  • Netezza function library

Extensions to Netezza functionality

  • Aggregate awareness with physical summary tables
  • Middle-tier computation of calculations not available in Netezza
  • Middle-tier caching via Intelligent Cubes
  • Report caching
  • Application-level partitioning

MicroStrategy Optimizations for Oracle

Oracle tests the MicroStrategy platform at its Enterprise Technology Center in order to optimize performance and stability for our joint customers. Through rigorous pre-release testing, MicroStrategy has consistently supported each new Oracle release before it reached the market.

Oracle-specific SQL syntax

  • Analytic functions (OLAP functions)
  • CASE expressions
  • Full outer joins
  • Set operators
  • Sub queries

Multi-pass SQL for analytical sophistication

  • Use of no logging tables
  • Use of global temp tables
  • Use of inline views
  • Use of WITH clause
  • Statistics collection on intermediate results

Integration with key Oracle features

  • Real Application Clusters (RAC)
  • Materialized views
  • Bit mapped indexes
  • Oracle OLAP option
  • Oracle advanced data types: XML, spatial

Extensions to Oracle functionality

  • Aggregate awareness with physical summary tables
  • Middle-tier caching via Intelligent Cubes
  • Middle-tier report caching
  • Application-level partitioning

Oracle Application Server

  • MicroStrategy Web Universal Edition on Oracle Application Server
  • MicroStrategy SDK on Oracle Application Server
  • Oracle AS 10.1.3 certified

Oracle Application Server Portal

  • Portal Integration Kit (PIK) with Oracle Application Portal Server

Supported Web Servers

  • Apache 2.2.23
  • Apache 2.0.59
  • Apache 1.3.26

LDAP / Single sign-on

MicroStrategy Optimizations for Sybase

Sybase-specific SQL Syntax

  • OLAP Analytical functions
  • CASE expressions
  • Sub queries

Multi-pass SQL for analytical sophistication

  • Use of derived tables
  • Use of temporary tables
  • Column indexes on intermediate tables
  • SELECT INTO syntax for speed

Seamless support for key Sybase features

  • Column indexes
  • Sybase function library

Extensions to Sybase functionality

  • Aggregate awareness with physical summary tables
  • Middle-tier computation of calculations not available in Sybase
  • Middle-tier caching via Intelligent Cubes
  • Report caching
  • Application-level partitioning

MicroStrategy Optimizations for Teradata

MicroStrategy and Teradata have a long history of working together to optimize the performance and capabilities of our joint business intelligence solutions. Both MicroStrategy and Teradata maintain dedicated research and development groups to continue the enhancement of their BI collaborations.

Creation of Teradata-specific SQL syntax

  • Ordered Analytic functions (OLAP functions)
  • Use of CASE expressions
  • Full Outer joins
  • Set operators
  • Sub queries

Use of Multi-pass SQL for analytical sophistication

  • Use of volatile tables
  • Use of derived tables
  • Control over primary indexes on intermediate results
  • Statistics collection on intermediate results
  • Bulk insertion of intermediate results for speed

Seamless support for key Teradata features

  • Partitioned primary indexes
  • Aggregate join indexes
  • Teradata function library
  • User-defined functions
  • UNICODE character sets

Extensions to Teradata functionality

  • Aggregate awareness with physical summary tables
  • Middle-tier computation of calculations not available in Teradata
  • Middle-tier caching via Intelligent Cubes
  • Report caching
  • Application-level partitioning

Integration with Teradata tools

  • Priority Scheduler
  • Query Director

MicroStrategy Optimizations for Vertica

Vertica-specific SQL Syntax

  • CASE expressions
  • Sub queries

Multi-pass SQL for analytical sophistication

  • Union All support
  • Support for temporary tables

Seamless support for key Vertica features

  • Vertica function library

Extensions to Vertica functionality

  • Aggregate awareness with physical summary tables
  • Middle-tier computation of calculations not available in Vertica
  • Middle-tier caching via Intelligent Cubes
  • Report caching