четверг, 29 мая 2014 г.

MicroStrategy Technology Philosophy

MicroStrategy Technology Philosophy: Leverage the Power of the RDBMS

The MicroStrategy architecture has its roots in the principles of Relational OLAP (ROLAP). A ROLAP architecture provides OLAP functionality to the end user (e.g. multidimensional framework, slice-and-dice interaction, drilling, etc.), but uses a relational database to resolve queries and perform calculations, rather then using a specialized proprietary multidimensional database.
MicroStrategy applications have been able to achieve industry-leading data scale because of the reliance on the processing power and data management capabilities of the underlying RDBMS.

Model-based Dynamic SQL Generation

Within the MicroStrategy Intelligence Server lies the MicroStrategy SQL Engine, which is responsible for generating SQL for all requests to the data warehouse. Hence, to the RDBMS, a MicroStrategy application is an SQL-based application, in many ways like any other SQL application accessing Teradata.

Schema Abstraction

The SQL Engine performs its work based on a metadata model defined to the system. Note that the MicroStrategy metadata is not used to store joins or schema-type information, such as star or snowflake. Instead, the metadata model stores content information for each table indicating that it contains a set of particular facts and a set of particular attributes. When a report request is submitted, the Engine breaks the report down into the individual components (i.e. attributes and facts), then begins searching the model to determine which combination of tables will be necessary and efficient in resolving the request.
Schema abstraction of the database columns (into MicroStrategy attributes and facts) provides the flexibility necessary to allow applications to be created quickly without having to change the structure of the data model. MicroStrategy is able to support virtually any type of star, snowflake, or hybrid physical design, including transactional schemas. The business model defined in MicroStrategy is easily able to span multiple stars/snowflakes in a single application and even a single query. MicroStrategy supports dimensional models well, but does not require a dimensional model.

Aggregate Awareness

Query performance in many data warehouses is enhanced through the use of aggregate tables. Aggregate tables, which are also called summary tables, store pre-computed results of data allowing users to query from a summarized set of data rather than the detail level data that would be stored in the fact table. In many cases, use of aggregate tables will improve query performance by orders of magnitude.
MicroStrategy provides an aggregate-aware Engine. MicroStrategy has allowed transparent navigation of aggregate tables, directing queries to summary tables when they exist without the user having to specify to use the table. The MicroStrategy SQL engine determines use of aggregate tables transparently at query time.

Multi-pass SQL

One of the key elements to providing analytical sophistication in business intelligence applications is MicroStrategy’s ability to generate multi-pass SQL. Multi-pass SQL is required to answer analytical questions that cannot be answered with a single SQL query block. Examples of questions / scenarios that require multi-pass SQL include:
  • Set qualification: “Show me sales by region over the last six months, but only for customers who purchased one of the 5 most popular products.”
  • Split metrics: query returns sales data from a sales star schema and inventory data from an inventory star schema
  • Metrics calculated at different levels of aggregation
  • Metrics calculated with different filtering criteria
  • Simulating outer joins on RDBMS platforms that do not support them natively
  • Querying multiple tables due to application-level partitioning
    Support for these scenarios, especially when combined together, provide a framework for significant analytic questions and value to the users of the system. One of the main optimizations the MicroStrategy SQL Engine makes is to generate SQL that performs these multi-pass queries as efficiently as possible.