Modules:Analytics CubeDef

Contents 1 Cube Definition 2 Header 2.1 Cube Definition as a Template 3 Dimensions and Measures 3.1 Dimensions 3.2 Measures 3.3 Calculated Members 4 Cube Level 5 Cube Report 6 Cube Report Dimension 7 Cube Report Dimension Element 8 Refreshing the Mondrian Schema

Cube Definition

In Mondrian, the OLAP Cube is defined by it's 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. Mondrian schemas are represented in an XML file. Mondrian Documentation.

In Openbravo the Cube can be defined through Application Dictonary, and this definition will be translated into an XML file with the Mondrian schema when needed.

In the near future (2014-2015) we will probably upgrade to Mondrian 4, Mondrian 4 uses a different schema than Mondrian 3.*. In various locations in the Cube definition you can alternatively use direct XML. Take into account that possibly your XML has to be adapted to the new standard when upgrading to Mondrian 4. We expect direct manual XML only to be used in specific situations, Openbravo will generate most of the mapping automatically. So normally it should not be needed to use direct XML definitions, therefore we expect the impact (when upgrading to Mondrian 4) to be limited.

Defining a Cube in Openbravo is done in the Cube Definition Window.

Header

The Header Tab describes the more generic elements of the Cube, such as the Table from which the data is retrieved.

Fields to note:

• Module. This field is not mandatory, and usually it should be empty. If this field is left empty, the Cube Definition data will be considered as Client data, which means it won't be affected by updates or exports actions against the database when compiling. If this filed is filled, the Cube Definition Window can be exported into a particular Module. This is useful when the Cube Definition is created as a template.
• Xml Definition. This field is not mandatory, and usually it should be empty. In this field the full XML definition of the schema can be written, in that case there is no need to define anything else, because this definition will be used for generating the Cube. Note: your specific XML definition will need to be adapted when we move to Mondrian 4.
• Table. The Table from which the data is retrieved.
• Sql Filter. An SQL expression that can be added to the where clause of the final query used to retrieve the data. When the XML file with the Cube schema is generated, the expression that describes the source of the data for the Cube looks like this.

<Cube name="Sales Orders" caption="Sales Orders">
    <View alias="alias1_25">
      <SQL>select * from c_orderline alias1_26 where alias1_26.ad_org_id in
('E443A31992CB4635AFCAEABE7183CE85','0','DC206C91AA6A4897B44DA897936E0EC3','7BABA5FF80494CAFA54DEBD22EC46F01','BAE22373FEBE4CCCA24517E23F0C8A48',
'19404EAD144C49A0AF37D54377CF452D','B843C30461EA4501935CB1D125C9C25A','2E60544D37534C0B89E765FE29BC0B43') 
and (1 = (select 1 from c_invoice as i where i.issotrx='Y' and alias1_26.c_invoice_id=i.c_invoice_id))</SQL>
    </View>

It can be noted that the SQL expression of the field has been added to the end of the where clause statement. One thing to notice too, is that the expression [alias] from the field has been replaced with alias1_26 in the SQL expression.

Note. In the Sql Filter field, to reference the Table from which the data is retrieved in the Cube, it is possible to use the expression [alias]. When the XML file with the schema of the Cube is generated, this expression will be replaced with the actual SQL alias of the table.

Cube Definition as a Template

If a Cube Definition is done with a System Admin role, an aditional check, called Template, will be available. By checking it the Cube Definition will be created as a Template.

This is used in scenarios in which part of the Cube Definition depends on Client data, and there is no option to generalize it. An example of this scenario are the Multi-Dimensional Financial Reports.

When a Cube is flagged as a Template, a button called Generate Cubes will appear. This button is only available as System Admin. By clicking on it it will show a pop up.

• Delete Previous Cubes. If this flag is checked and there is a previously created Cube for a Client, it will be deleted before being created again. If it is not checked and there is a previously created Cube for a Client, it will not be modified.
• Client. A list of the Clients for which a Cube is going to be generated based on the Template.

Note. When generating a Cube based on a template, if in the Sql Filter field there is an expression like @ad_client_id@, it will be replaced by the correspondent ad_client_id.

Note. The Module of the Cube must be in development before launching the Generate Cubes process.

Additional operations can be performed against a Template through Java code by extending the Class GenerateCubesFromTemplate, an example of how it can be done can be found in the Multi-Dimensional Financial Reports.

Dimensions and Measures

In the Cube Dimensions Tab, both Dimensions and Measures are defined. An OLAP Cube consists of numeric facts called measures which are categorized by dimensions.

Dimensions

A Dimension is a data set composed of individual, non-overlapping data elements which provides structured labeling information to the numeric measures.

Dimensions are used to order, group and filter Measures.

Fields to note:

• Module. This field is not mandatory, and usually it should be empty. If this field is left empty, the Cube Definition data will be considered as Client data, which means it won't be affected by updates or exports actions against the database when compiling. If this filed is filled, the Cube Definition Window can be exported into a particular Module. This is useful when the Cube Definition is created as a template.
• Line No. It is used as the reference to order Dimensions when generating the XML file with the Cube Schema. It is important, as noted below, that Dimensions and Measures are ordered with Dimensions before Measures and Measures before Calculated Members.
• Model Element. The Property that is going to define the Dimension itself. For setting it the Property path is used, like in the Property Fields, starting with the Table defined in the Header. For example, if the Table defined in the Cube Definition Header is the Fact_Acct, by setting period.Year as the Model Element, it is possible to use the Fiscal Year as a Dimension.
• Ignore Tree Hierarchy: field is only relevant for dimensions based on an entity which is structured using Openbravo trees, for example Organization. If checked/yes then this dimension does not map the Openbravo tree and it will represent a flat structure. If not-checked then the dimension is mapped using an Openbravo tree if one is defined for the entity.
• Name: set a Caption and Name for the dimension, only relevant if mapping the same model element multiple times for filtering or other reasons, when setting a name, use preferably only standard latin characters and no special characters.
• Xml Definition. This field is not mandatory, and usually it should be empty. In this field the full XML definition of the dimension can be written, in that case there is no need to define anything else, because this definition will be used as the XML description of the Dimension. Note: your specific XML definition will need to be adapted when we move to Mondrian 4.
• Measure. Since this is a Dimension, this flag should be unchecked.
• Sql Filter. An SQL expression that can be added to the <SQL> tag of the Dimension Definition. When the XML definition of the Dimension is generated, it looks like this.

<Dimension name="account" foreignKey="account_id" caption="Account">
      <Hierarchy hasAll="true" primaryKey="c_elementvalue_id" primaryKeyTable="c_elementvalue">
        <Table name="c_elementvalue" alias="alias1_90">
          <SQL>alias1_90.ad_org_id in ('E443A31992CB4635AFCAEABE7183CE85','0','DC206C91AA6A4897B44DA897936E0EC3','7BABA5FF80494CAFA54DEBD22EC46F01',
'BAE22373FEBE4CCCA24517E23F0C8A48','19404EAD144C49A0AF37D54377CF452D','B843C30461EA4501935CB1D125C9C25A','2E60544D37534C0B89E765FE29BC0B43') 
and 1=1 and exists(select c_element_id from c_acctschema_element elem0 
where alias1_90.c_element_id=elem0.c_element_id and elem0.c_acctschema_id = '732913485BB040FFA4643FF06D1AA095')</SQL>
        </Table>
        <Level name="accountLevel" column="c_elementvalue_id" caption="Account" table="alias1_90" 
uniqueMembers="true" ordinalColumn="em_obanaly_treeordinal" parentColumn="em_obanaly_treeparentid">
          <Annotations>
            <Annotation name="entity">FinancialMgmtElementValue</Annotation>
          </Annotations>
          <MemberFormatter className="org.openbravo.client.analytics.OBMondrianMemberFormatter"/>
          <Closure parentColumn="parent_id" childColumn="child_id">
            <Table alias="obanaly_tree_closure" name="obanaly_tree_closure">
              <SQL>obanaly_tree_closure.ad_tree_id = '894BD9E7395143ACB94FD8998E1840CD'</SQL>
            </Table>
          </Closure>
        </Level>
      </Hierarchy>
    </Dimension>

Measures

A Measure is a numerical fact in the Cube, for example, Units Sold or Net Price could be Measures.

Fields to note:

• Module. This field is not mandatory, and usually it should be empty. If this field is left empty, the Cube Definition data will be considered as Client data, which means it won't be affected by updates or exports actions against the database when compiling. If this filed is filled, the Cube Definition Window can be exported into a particular Module. This is useful when the Cube Definition is created as a template.
• Line No. It is used as the reference to order Measures when generating the XML file with the Cube Schema. It is important, as noted below, that Dimensions and Measures are ordered with Dimensions before Measures and Measures before Calculated Members.
• Model Element. select a property of the Cube Definition Table or a derived property by following the dot notation (for example: salesOrder.documentNo).
• Xml Definition. This field is not mandatory, and usually it should be empty. In this field the full XML definition of the measure can be written, in that case there is no need to define anything else, because this definition will be used as the XML description of the Measure. Note: your specific XML definition will need to be adapted when we move to Mondrian 4.
• Measure. Since this is a Measure, this flag should be checked.
• Name. The name for the Measure.
• Measure Expression. An expression to calculate the numeric value of the Measure. It can be a numeric column of the Table of the Cube Definition or a numerical expression. For example, if the Table is the Fact_Acct, the expression could be something like (AmtAcctDr - AmtAcctCr) for calcultating the Balance.
• Calculated Member. Since this is a Measure, this flag should be unchecked.

Calculated Members

A Calculated Member is a Measure, but it's value is calculated based on an MDX query, which allows for more complex expressions than a simple Measure.

Fields to note:

• Module. This field is not mandatory, and usually it should be empty. If this field is left empty, the Cube Definition data will be considered as Client data, which means it won't be affected by updates or exports actions against the database when compiling. If this filed is filled, the Cube Definition Window can be exported into a particular Module. This is useful when the Cube Definition is created as a template.
• Line No. It is used as the reference to order Calculated Members when generating the XML file with the Cube Schema. It is important, as noted below, that Dimensions and Measures are ordered with Dimensions before Measures and Measures before Calculated Members.
• Model Element. Although this Field is mandatory, for Calculated Members is not used, so any property of the Cube Definition Table can be set as the Model Element.
• Xml Definition. This field is not mandatory, and usually it should be empty. In this field the full XML definition of the measure can be written, in that case there is no need to define anything else, because this definition will be used as the XML description of the Measure. Note: your specific XML definition will need to be adapted when we move to Mondrian 4.
• Measure. Since the Calculated Member is a Measure, this flag should be checked.
• Name. The name for the Calculated Member.
• Calculated Member. Since this is a Calculated Member, this flag should be checked.
• Formula. The MDX expression for the Calculated Member.

Note. The order based on the Line Numbers of the Dimensions, Measures and Calculated Members is important, since the generation of one member may be based on another one, which should be generated first.

As a general rule, Dimensions should go first. Then Measures and, in the last position, Calculated Members. Also, for Measures and Calculated Members, if there is a dependence from one member to another, meaning that one member uses another one in its Formula, this should be taken into account in the order too. For example, there is a Calculated Member called Initial Balance, and there is another Calculated Member called Ending Balance, which in it's formula uses the Initial Balance, in this case Initial Balance should go before Ending Balance.

Cube Level

This Tab is used only when defining a Dimension which Model Element is not present in the Cube Definition Table.

For example, if the Cube Definition Table is Sales Invoice, and the Dimension's Model Element is businessPartner.BusinessPartnerCategory, or if the Cube Definition Table is Fact_Acct, and the Dimension's Model Element is period.Year.

In those scenarios, a record should be created for each Element in the Model Element Path, for example, for the businessPartner.BusinessPartnerCategory, a record should be created for the businessPartner, and another one for the businessPartnerCategory.

In any other case, this Tab should be empty.

Fields to note:

• Name. Name of the Model Element Property, for example businessPartnerCategory.
• Unique Members. Usually unchecked.
• Order Property. Dimensions are ordered based on the Columns marked as Identifiers in the Application Dictionary. But sometimes this order is not wanted. In this Field a Model Element Property can be set to be used in the order clause. For example, Fiscal Periods should be ordered by the period No. instead of the Name.

Cube Report

This Tab is usually empty. It is used by developers to define a static structure of Dimensions for building specific reports, such as the Multi-Dimensional Financial Reports.

• Report Type. The Report can be defined by either a Process or a Process Definition, but only by one of them. The type should be selected in this Field.
• Process. Process that defines the Report.
• Process Definition. Process Definition that defines the Report.

Cube Report Dimension

This Tab is usually empty. It is used by developers to define a static structure of Dimensions for building specific reports, such as the Multi-Dimensional Financial Reports.

• Axis. The Axis in which the Dimension is going to be placed, Rows or Columns.
• OBANALY_Cube_Dimension_ID/Cube Dimension. One of the Dimensions of the Cube, defined in Cube Dimensions Tab.
• Fixed. For a fixed Dimension, checked.

Cube Report Dimension Element

This Tab is usually empty. It is used by developers to define a static structure of Dimensions for building specific reports, such as the Multi-Dimensional Financial Reports.

• Show. How the Element of the Dimension is shown. It can be shown itself (Member), it's Children (Children), or both itself and it's Children (Member and Children)
• Exclude. If this check is flagged, the Element selected will be excluded instead of shown. With this flag it is possible to have an Element shown with it's Children, and then exclude only one Children.

Refreshing the Mondrian Schema

After changing the cube definition through the user interface, you sometimes need to tell Mondrian/Saiku to refresh the mondrian schema which is loaded in-memory. This can be done through the Saiku user interface. In the top right of the cube section (on the left) you can find a refresh button, see on the right of the word 'Cubes':

Clicking it should flush all caches, both data and the schema file itself.

Category: Analytics