Group items tagged

Multidimensional Expressions (MDX) is a query language for OLAP databases, much like SQL is a query language for relational databases. It is also a calculation language, with syntax similar to spreadsheet formulas.

The specification was quickly followed by commercial release of Microsoft OLAP Services 7.0 in 1998 and later by Microsoft Analysis Services.

While it was not an open standard, but rather a Microsoft-owned specification, it was adopted by the wide range of OLAP vendors. This included both vendors on the server side such as Applix, icCube, MicroStrategy, NCR, Oracle Corporation, SAS, SAP, Teradata, Symphony Teleca, and vendors on the client side such as Panorama Software, PowerOLAP, XLCubed, Proclarity, AppSource, Jaspersoft, Cognos, Business Objects, Brio Technology, Crystal Reports, Microsoft Excel, and Microsoft Reporting Services.

The choice between deploying relational tables or OLAP cubes is not a trivial matter. Weigh these 34 pros and cons of each approach early in the design of your extract-transform-load system.

A cube can be considered a generalization of a three-dimensional spreadsheet.

Each cell of the cube holds a number that represents some measure of the business

OLAP data is typically stored in a star schema or snowflake schema in a relational data warehouse or in a special-purpose data management system.

The usual interface to manipulate an OLAP cube is a matrix interface like Pivot tables

MOLAP stores this data in an optimized multi-dimensional array storage, rather than in a relational database

The problem of deciding which aggregations (views) to calculate is known as the view selection problem. View selection can be constrained by the total size of the selected set of aggregations, the time to update them from changes in the base data, or both. The objective of view selection is typically to minimize the average time to answer OLAP queries, although some studies also minimize the update time

MDX stands for 'Multi-Dimensional Expressions' and is the standard language defined by Microsoft to query OLAP servers.

racle SQL Developer Data Modeler provides a model driven approach for database design and generation, implemented by integrated set of models – Logical, Data types, Dimensional, Relational, Data Flow diagrams and Physical models for supported Oracle Databases, Microsoft SQL Server 2000 and 2005

Cube Views metadata

Dimensional Models   Dedicated full featured Dimensional model – star and snowflake schema easily can be built and expressed on detailed and compact diagrams   Dimensions – support for merging (level can belongs to more than one dimension), shared, fact and role playing dimensions   Hierarchies – value based hierarchies (parent-child), and regular and ragged level based hierarchies   Measures – fully, semi and none additive; different aggregation functions on different dimensions ; fact dimension; calculated measures   Query wizard allows Select statements to be generated from the dimensional model   Support for Oracle OLAP. This includes specifics like cube partitioning, sparse dimensions, and compressed measures   Built-in wizards help to define all required object types and view definitions that enable SQL access to dimensional data in Oracle AW (using the OLAP_TABLE interface)   Bidirectional integration with Oracle physical model - Dimensional model can be created using SQL dimension definitions in physical model or the definitions can be created from the dimensional model

Hyperion adds complementary products to Oracle's business intelligence offerings including a leading enterprise planning solution, world-class financial close and reporting products, and a powerful multi-source OLAP server