Cube DSL

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

Element	Description
<Cube>	A collection of dimensions and measures, all centered on a fact table.
<VirtualCube>	A cube defined by combining the dimensions and measures of one or more cubes. A measure originating from another cube can be a <CalculatedMember>.
<CubeUsages>	Base cubes that are imported into a virtual cube
<CubeUsage>	Usage of a base cube by a virtual cube.
<VirtualCubeDimension>	Usage of a dimension by a virtual cube.
<VirtualCubeMeasure>	Usage of a measure by a virtual cube.
<Dimension>	Dimension
<DimensionUsage>	Usage of a shared dimension by a cube.
<Hierarchy>	Hierarchy
<Level>	Level of a hierarchy
<KeyExpression>	SQL expression used as key of the level, in lieu of a column.
<NameExpression>	SQL expression used to compute the name of a member, in lieu of Level.nameColumn.
<CaptionExpression>	SQL expression used to compute the caption of a member, in lieu ofLevel.captionColumn.
<OrdinalExpression>	SQL expression used to sort members of a level, in lieu ofLevel.ordinalColumn.
<ParentExpression>	SQL expression used to compute a measure, in lieu ofLevel.parentColumn.
<Property>	Member property. The definition is against a hierarchy or level, but the property will be available to all members.
<Measure>	Measure
<CalculatedMember>	A member whose value is derived using a formula, defined as part of a cube.
<NamedSet>	A set whose value is derived using a formula, defined as part of a cube.

Physical elements

Element

Description

Fact or dimension table. <View> Defines a 'table' using a SQL query, which can have different variants for different underlying databases. <Join> Defines a 'table' by joining a set of queries. <InlineTable> Defines a table using an inline dataset. <Closure> Maps a parent-child hierarchy onto a closure table. Aggregate tables Element Description <AggExclude> Exclude a candidate aggregate table by name or pattern matching. <AggExclude> Declares an aggregate table to be matched by name. <AggExclude> Declares a set of aggregate tables by regular expression pattern. <AggExclude> Specifies name of the column in the candidate aggregate table which contains the number of fact table rows. <AggExclude> Tells Mondrian to ignore a column in an aggregate table. <AggExclude> Maps foreign key in the fact table to a foreign key column in the candidate aggregate table. <AggExclude> Maps a measure to a column in the candidate aggregate table. <AggExclude> Maps a level to a column in the candidate aggregate table. <> <> <> <> <>