Modules:Analytics Debug

Contents 1 Load OB Analytics Window 2 Debugging Queries with Mondrian 3 Debug Cube Schemas 3.1 View 3.2 Dimensions 3.3 Measures 4 Debug Queries for Saiku 4.1 Header 4.2 Axes 4.3 Properties

Load OB Analytics Window

The first thing to confirm when a Client has a problem with Analytics Module is that the basic operations work.

As Client Administrator, open the OB Analytics Window. It may take some time to load, as explained in the Documentation.

When the Window is opened, click on the dropdown list available to select a Cube. If there are Cubes defined, and the list is empty, there is already a problem in the instance. At this point the best source of information to find the problems are the logs.

If the Cubes are loaded, further errors may be able to be debugged without looking into the logs.

Debugging Queries with Mondrian

When Mondrian resolves and MDX Query, what is really doing behind the curtain is generating several SQL Queries that are fired against the relational database to retrieve the information.

When there is a problem when retrieving data of a MDX Query, it might be helpful to see the SQL Queries launched as a result of it.

It is possible to do so by adding this lines at the end of the log4j.lcf file.

# Set root logger level to DEBUG and its only appender to MONDRIAN.
log4j.rootLogger=WARN, MONDRIAN
 
# MONDRIAN is set to be a ConsoleAppender.
log4j.appender.MONDRIAN=org.apache.log4j.ConsoleAppender
 
# MONDRIAN uses PatternLayout.
log4j.appender.MONDRIAN.layout=org.apache.log4j.PatternLayout
log4j.appender.MONDRIAN.layout.ConversionPattern=%-4r [%t] %-5p %c %x - %m%n
 
# Trace MDX and SQL statements
log4j.category.mondrian.mdx=DEBUG, MONDRIAN
log4j.category.mondrian.sql=DEBUG, MONDRIAN

Or if using Openbravo PR19Q1 or newer, edit the following lines in config/log4j2-web.xml:

<Appenders>
  ...
  <!-- Declare a console appender for Mondrian queries -->
  <Console name="Mondrian" target="SYSTEM_OUT">
    <PatternLayout pattern="%-4r [%t] %-5p %c %x - %m%n"/>
  </Console>
</Appenders>
<Loggers>
  <Root level="warn">
    <AppenderRef ref="Mondrian"/>
  </Root>
  <Logger name="mondrian.mdx" level="debug"/>
  <Logger name="mondrian.sql" level="debug"/>
</Loggers>

Debug Cube Schemas

It is possible to see the XML definition of the Cubes by using the Mondrian mapping service. Using a specific web url:

http://localhost:8080/openbravo/analytics/schema

Replace the host/port/context with the settings relevant for your installation.

The structure of the XML file with the Cube definition is more extensively explained in the Mondrian Documentation.

The MDX file can be split basically in three different parts.

View

In this part of the XML code is defined how the data is going to be retrieved from the main table.

<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_order o where o.issotrx='Y' and alias1_26.c_order_id=o.c_order_id))
</SQL>
    </View>

Dimensions

In this part of the XML code the Dimensions are defined. For each Dimension it is possible to see the Tables from which the data is retrieved, plus the SQL code that is going to be added to the where clause.

Also the different levels of each Dimension are shown.

<Dimension name="salesOrder-businessPartnerCategory" foreignKey="c_order_id" caption="Sales Order - Business Partner Category">
        <Hierarchy hasAll="true" primaryKey="c_order_id" primaryKeyTable="c_order">
            <Join leftKey="c_bpartner_id" rightKey="c_bpartner_id">
                <Table name="c_order" alias="alias1_37">
                    <SQL>
alias1_37.ad_org_id in ('E443A31992CB4635AFCAEABE7183CE85','0','DC206C91AA6A4897B44DA897936E0EC3','7BABA5FF80494CAFA54DEBD22EC46F01','BAE22373FEBE4CCCA24517E23F0C8A48',
'19404EAD144C49A0AF37D54377CF452D','B843C30461EA4501935CB1D125C9C25A','2E60544D37534C0B89E765FE29BC0B43')
</SQL>
                </Table>
                <Join leftKey="c_bp_group_id" rightKey="c_bp_group_id" leftAlias="alias1_38" rightAlias="alias1_39">
                    <Table name="c_bpartner" alias="alias1_38">
                        <SQL>
alias1_38.ad_org_id in ('E443A31992CB4635AFCAEABE7183CE85','0','DC206C91AA6A4897B44DA897936E0EC3','7BABA5FF80494CAFA54DEBD22EC46F01','BAE22373FEBE4CCCA24517E23F0C8A48',
'19404EAD144C49A0AF37D54377CF452D','B843C30461EA4501935CB1D125C9C25A','2E60544D37534C0B89E765FE29BC0B43')
</SQL>
                    </Table>
                    <Table name="c_bp_group" alias="alias1_39">
                        <SQL>
alias1_39.ad_org_id in ('E443A31992CB4635AFCAEABE7183CE85','0','DC206C91AA6A4897B44DA897936E0EC3','7BABA5FF80494CAFA54DEBD22EC46F01','BAE22373FEBE4CCCA24517E23F0C8A48',
'19404EAD144C49A0AF37D54377CF452D','B843C30461EA4501935CB1D125C9C25A','2E60544D37534C0B89E765FE29BC0B43')
</SQL>
                    </Table>
                </Join>
            </Join>
            <Level name="businessPartnerCategoryLevel" column="c_bp_group_id" caption="Business Partner Category" table="alias1_39" uniqueMembers="true" ordinalColumn="name">
                <Annotations>
                    <Annotation name="entity">BusinessPartnerCategory</Annotation>
                </Annotations>
                <MemberFormatter className="org.openbravo.client.analytics.OBMondrianMemberFormatter" />
            </Level>
            <Level name="businessPartnerLevel" column="c_bpartner_id" caption="Business Partner" table="alias1_38" uniqueMembers="true" ordinalColumn="name">
                <Annotations>
                    <Annotation name="entity">BusinessPartner</Annotation>
                </Annotations>
                <MemberFormatter className="org.openbravo.client.analytics.OBMondrianMemberFormatter" />
            </Level>
        </Hierarchy>
    </Dimension>

Measures

This part of the XML code shows the definition of the Measures. Some of them might have an MDX expression to build them.

<Measure name="invoicedQuantity" column="qtyinvoiced" aggregator="sum">
        <CellFormatter className="org.openbravo.client.analytics.MondrianCellFormatter$Quantity" />
    </Measure>

Debug Queries for Saiku

It is possible to see the saved Saiku Queries in the Analytics Queries Window.

A hint to develop a report that generates a Saiku query is to create the query itself using OB Analytics Window and then save it. Using the XML code of the query generated by Saiku is helpful as a guide to create the query for the report.

Each query can be split in three different parts.

Header

In the header the connection details are set. The name is something that must be unique to the query, an UUID can be used. The connection is the same that OB Analytics Window shows when displaying the Cubes.

<Query name="6793C26C4B504EF9822E16C2C63BAE0FDAF5CA2984604E1A8673D1C659B4057D" type="QM" connection="Openbravo" cube="DPL - P&amp;L - F&amp;B International Group US_A_US Dollar" 
catalog="Openbravo" schema="Openbravo">

Axes

For each axis the Dimensions that are going to be shown are specified. Also the members that are going to be included or excluded.

<Axis location="COLUMNS" nonEmpty="true">
                <Dimensions>
                    <Dimension name="accountingDate">
                        <Inclusions>
                            <Selection dimension="accountingDate" type="member" node="[accountingDate].[2013]" operator="MEMBER" />
                        </Inclusions>
                        <Exclusions />
                    </Dimension>
                    <Dimension name="organization" hierarchizeMode="PRE" hierarchyConsistent="true">
                        <Inclusions>
                            <Selection dimension="organization" type="level" node="[organization].[organizationLevel]" operator="MEMBERS" />
                        </Inclusions>
                        <Exclusions />
                    </Dimension>
                </Dimensions>
            </Axis>

Properties

Properties needed by Saiku to understand how to show the data. Always the same.

<Properties>
        <Property name="saiku.ui.render.mode" value="table" />
        <Property name="org.saiku.query.explain" value="true" />
        <Property name="saiku.olap.query.nonempty.columns" value="true" />
        <Property name="saiku.olap.query.nonempty.rows" value="true" />
        <Property name="org.saiku.connection.scenario" value="false" />
        <Property name="saiku.ui.formatter" value="flat" />
        <Property name="saiku.olap.query.automatic_execution" value="true" />
        <Property name="saiku.olap.query.drillthrough" value="true" />
        <Property name="saiku.olap.query.filter" value="true" />
        <Property name="saiku.olap.query.limit" value="true" />
        <Property name="saiku.olap.query.nonempty" value="true" />
    </Properties>

Category: Analytics