View source | Discuss this page | Page history | Printable version   

Modules:Analytics CubeDef

Contents

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.

AnalyticsCubeDef1.png

Header

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

AnalyticsCubeDef2.png


Fields to note:

<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.

AnalyticsCubeDef3.png


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.

AnalyticsDim1.png

Fields to note:

<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.

AnalyticsDim2.png

Fields to note:

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.

AnalyticsDim3.png

Fields to note:


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.

AnalyticsDim4.png

Fields to note:

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.

AnalyticsDim5.png

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.

AnalyticsDim6.png

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.

AnalyticsDim7.png

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':


Analytics Refresh Button1.png


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

Retrieved from "http://wiki.openbravo.com/wiki/Modules:Analytics_CubeDef"

This page has been accessed 7,118 times. This page was last modified on 8 November 2015, at 08:32. Content is available under Creative Commons Attribution-ShareAlike 2.5 Spain License.