Query & Function Overview
Pyramid's Query, Analytics and Calculation engine is called "PYRANA" (pronounced like the fish). PYRANA is a powerful and comprehensive engine for building and running queries and the associated calculations against numerous SQL and MDX engines.
PYRANA includes both the tools to generate queries against data sources as well as construct and execute member and set based calculations with advanced query constructs. It can execute:
- Native MDX: for sources like Microsoft OLAP / Tabular models and SAP BW Infocubes and BEX queries
- Native SQL: for all other databases supported by Pyramid. Typically these involve models designed in Pyramid but can also include external models like SAP HANA views. The SQL is generated by PYRANA using Pyramid's own query language "PQL" - which is structurally and logically the same as MDX; yet optimized for SQL executions on the underlying data stacks.
The Pyramid Query Language or PQL (pronounced 'Prequel') is a language built into PYRANA to allow users to construct queries that can run against the many data stacks that Pyramid can query natively with SQL. PQL includes a vast array of operations and functions that allows users to query data and build analytical logic and also includes all the other functions and methods to drive the application beyond just querying. This includes libraries for data modeling, dynamic text (NLG) and infographics, and client side functional programming. It also includes a common library of basic programming methods that can be used in conjunction with the various specialized libraries.
- For more details on PQL, its structures and the PQL function reference, click here.
Data stacks that use MDX are supported by Pyramid and any compliant MDX statement can be incorporated into Pyramid via its Formulate tools. Unlike PQL compliant data sources, MDX is limited to semantic operations only on MDX data stacks. In this case, PQL functionality is available in non-semantic parts of the application to close the gap. On Microsoft MDX sources, users can also use the VBA common library to supplement native MDX functions.
- For more details on MDX and the MDX function reference, click here.