Difference between revisions of "Cube DSL"

Revision as of 13:04, 8 August 2016

Copyright Notice

All rights are reserved by Compex Systemhaus GmbH. In particular, duplications, translations, microfilming, saving and processing in electronic systems are protected by copyright. Use of this manual is only authorized with the permission of Compex Systemhaus GmbH. Infringements of the law shall be punished in accordance with civil and penal laws. We have taken utmost care in putting together texts and images. Nevertheless, the possibility of errors cannot be completely ruled out. The Figures and information in this manual are only given as approximations unless expressly indicated as binding. Amendments to the manual due to amendments to the standard software remain reserved. Please note that the latest amendments to the manual can be accessed through our helpdesk at any time. The contractually agreed regulations of the licensing and maintenance of the standard software shall apply with regard to liability for any errors in the documentation. Guarantees, particularly guarantees of quality or durability can only be assumed for the manual insofar as its quality or durability are expressly stipulated as guaranteed. If you would like to make a suggestion, the Compex Team would be very pleased to hear from you.

(c) 2016-2024 Compex Systemhaus GmbH

Introduction

MDX and OLAP Cube

MDX(MultiDimensional eXpressions) is a query language for OLAP (OnLine Analytical Processing) databases, much like SQL is a query language for relational databases. It is also a calculation language, with syntax similar to spreadsheet formulas.

SQL was designed to query dimensional data structures, called tables, where data are organized in rows and columns. In OLAP, data are organized around multiple measures, dimensions, hierarchies, and levels.

Similar to the way tables and columns are central to SQL, dimensions, hierarchies, and levels, are the centerpieces of MDX. They are mapping business models into language-specific concepts (e.g. a list of countries will be mapped as an MDX dimension). Measures in MDX are a special case of dimensions and they hold numeric values. The concrete value of a dimension (e.g. Paris) is a member of the dimension and is similar to the value of an SQL table.

An OLAP Cube is a collection of dimensions indexing a list of measures. E.g. geometrical cube can have three dimensions, whereas an logistical cube can have many more dimensions.

More information:

http://en.wikipedia.org/wiki/MultiDimensional_eXpressions

http://en.wikipedia.org/wiki/OLAP_cube

Mondrian

Mondrian is an open source OLAP server, written in Java. It supports the MDX query language and the XML for analysis and olap4j interface specifications. It reads from SQL and other data sources and aggregates data in a memory cache.

Mondrian is used for:

• High performance, interactive analysis of large or small volumes of information
• Dimensional exploration of data, for example analyzing sales by product line, by region, by time period
• Parsing the MDX language into SQL to retrieve answers to dimensional queries
• High-speed queries through the use of aggregate tables in the RDBMS(Relational DataBase Management System)
• Advanced calculations using the calculation expressions of the MDX language

Schema

A schema defines a multi-dimensional database. It contains a logical model, consisting of cubes, hierarchies, and members, and a mapping of this model onto a physical model.

The logical model consists of the constructs used to write queries in MDX language: cubes, dimensions, hierarchies, levels, and members.

The physical model is the source of the data which is presented through the logical model. It is typically a star schema, which is a set of tables in a relational database.

Schema file

The schema file defines the dimensions and measures for the cube and maps them to the DB tables. The product and location dimensions contain several levels each. Each level defines its corresponding field in the DB table. The different values of the field become members of the level.

Note: The levels must be specified in the intended order of breakdown.

• The structure of the XML document is as follows:
  

  <Schema>
     <Cube>
        <Table>
           <AggName>
              aggElements
           <AggPattern>
              aggElements
           <Dimension>
              <Hierarchy>
                 relation
              <Closure/>
              <Level>
              <KeyExpression>
                 <SQL/>
              <NameExpression>
                 <SQL/>
              <CaptionExpression>
                 <SQL/>
              <OrdinalExpression>
                 <SQL/>
              <ParentExpression>
                 <SQL/>
              <Property>
                 <PropertyExpression>
                    <SQL/>
           <DimensionUsage>
           <Measure>
              <MeasureExpression>
                 <SQL/>
              <CalculatedMemberProperty/>
           <CalculatedMember>
              <Formula/>
              <CalculatedMemberProperty/>
           <NamedSet>
              <Formula/>
           <VirtualCube>
              <CubeUsages>
                 <CubeUsage>
              <VirtualCubeDimension>
              <VirtualCubeMeasure>
           <Role>
              <SchemaGrant>
                 <CubeGrant>
                    <DimensionGrant>
                    <HierarchyGrant>
                       <MemberGrant/>
        <Union>
           <RoleUsage/>
     <UserDefinedFunction/>
     <Parameter/>
  

  
  
• relation ::=
  

     <Table>
        <SQL/>
     <View>
        <SQL/>
     <InlineTable>
        <ColumnDefs>
           <ColumnDef>
        <Rows>
           <Row>
              <Value>
     <Join>
        relation
  

  
  
• aggElement ::=
  

     <AggExclude>
     <AggFactCount>
     <AggIgnoreColumn>
     <AggForeignKey>
     <AggMeasure>
     <AggLevel>
  

  
  

Logical elements

Physical elements

Aggregate tables

Access control

Extensions

Miscellaneous