Opentaps Analytics

From Opentaps Wiki

Contents 1 Introduction 2 Screenshots 3 Architecture 4 Installation 4.1 Installing opentaps Analytics from Download 4.2 Installing opentaps Analytics with opentaps 4.3 Installing opentaps Analytics on its own 4.4 Setting up the Database 4.5 Creating Tables and Seed Data 4.6 Transforming your Data 4.7 Setting Up Users and Security 4.8 Setting url base for xaction 4.9 Changing Ports 4.10 Google Map Key 5 Reports 5.1 Accessing the Reports 5.2 Writing Your Own Reports 5.3 Dashboards 5.3.1 Example 5.3.1.1 The Java class 5.3.1.2 The Xaction file 5.3.1.3 In the Jsp 5.4 Report Filters 6 Unit Tests 6.1 Architecture 6.2 A Unit Test Example 6.3 Running the Analytics tests for Opentaps 7 References

Introduction

How can I run my business better?

opentaps analytics is a set of tools to help you make better business decisions. It provides you with reports and dashboards to understand how your products, customers, and employees together contribute to revenues and profits and gives you the information you need to improve your sales and bottom-line results. Opentaps analytics is designed to work with opentaps out-of-the-box, or you can use it with other popular open-source and commercial business applications by transforming data from your application into opentaps analytics and then using our pre-built reports, dashboards, and other tools.

The goals of opentaps analytics are the following:

• To provide you with a set of best practices for analyzing business data. Our goal is not to provide every possible report but rather follow established analytical methodologies, so each of our reports provides you with valuable insight.
• To provide a set of out-of-the-box, ready to use analytical reports and tools which could give you most of the information you need to understand your business
• To allow you to customize it according to your organization's specific requirements for analyzing data
• To interface with external services that could enhance your data, including demographics, address cleanup, and removal of duplicate records
• To maximize your IT resources by supporting multiple organizations in the same instance (" multi-tenancy")
• To be easy to use and fun to work with

Opentaps analytics can help you answer the following questions:

1. Who are my biggest customers?
2. What are my best selling products?
3. How will my sales vary across different geographic areas or seasons of the year?
4. Which products or customers have a lot of returns?
5. For a specific subgroup of products or customers, such as customers in California or consumer electronics, what are the best sellers?

Screenshots

See Opentaps Analytics Screenshots.

Architecture

opentaps analytics is built on top of the following open-source projects:

• MySQL database
• Pentaho business intelligence application suite
• JasperReports reporting tools, so that you can design or customize your reports with the iReport report designer

opentaps analytics provides you with the following additional out-of-the-box components:

• a standardized and customizable data warehouse model
• standard data transformation routines to take data from your ERP and CRM system, such as the opentaps Open Source ERP + CRM system or other open-source and commercial business applications, into opentaps analytics
• pre-built reports
• dashboards
• integration with external service and service providers
• unit tests to make sure the transformations and reports are working properly

Installation

Installing opentaps Analytics from Download

After downloading opentaps_analytics_pentaho zip file from SourceForge, unzip it. Then, go to the opentaps-analytics-pentaho/analytics and follow the steps in Setting up the Database to configure the data warehouse and load the seed data.

Installing opentaps Analytics with opentaps

This is not supported yet.

Installing opentaps Analytics on its own

Check out opentaps analytics,

 svn co svn://svn.opentaps.org/analytics/trunk analytics

Download Kettle.

Then, follow the instructions in Serving Opentaps Analytics with Pentaho.

Setting up the Database

 These are preliminary instructions and do not reflect actual setup requirements for Opentaps Analytics.

First, we need to create the data warehouse:

 create database opentaps_analytics default character set utf8 collate utf8_general_ci;
 grant all privileges on opentaps_analytics.* to 'opentaps'@'localhost' identified by 'opentaps';

Then the test database needed to run the unit tests:

 create database opentaps_analytics_testing default character set utf8 collate utf8_general_ci;
 grant all privileges on opentaps_analytics_testing.* to 'opentaps'@'localhost' identified by 'opentaps';

Next, we have to specify the location of both the main opentaps and the analytic databases. You can do this by editing the file config/database-config.xml:

 <dbconfig>
     <operational>
         <url>jdbc:postgresql://localhost/opentaps</url>
         <driver>org.postgresql.Driver</driver>
         <user>opentaps</user>
         <password>opentaps</password>
     </operational>
     <analytics>
         <url>jdbc:mysql://localhost/opentaps_analytics</url>
         <driver>com.mysql.jdbc.Driver</driver>
         <user>opentaps</user>
         <password>opentaps</password>
     </analytics>
 </dbconfig>

The operational database is your opentaps database. The analytics database is the data warehouse. Change the driver, url, user and password to match each database's credentials. Also, in the Pentaho project, make sure that the JDBC drivers for your databases are the correct version.

You'll have to configure also a database account to create the seed data specific to pentaho, by editing the file config/install.properties:

 # MySQL user and password for creating the pentaho databases
 pentaho.db.user=root
 pentaho.db.password=

Creating Tables and Seed Data

You can create tables and set up the seed data for your data warehouse using the ant from the analytics/ directory:

 ant setup-db

When this command completes, you should have the seed data such as the date_dimension and pentaho specifics databases populated.

For reference, running ant without arguments prints out the options. They are,

ant                   <-- Nothing, just prints out targets and documentation on them
ant setup-db          <-- Creates tables, seed data
ant transform         <-- Runs the operational -> analytics transformations
ant clean-db          <-- clears out the DB for development 
ant deploy-pentaho    <-- Deploys reporting solution to Pentaho/JBoss
ant setup-testing-db  <-- Sets up the opentaps analytics testing database
ant clean-testing-db  <-- Remove all data from testing database

Transforming your Data

Once the data warehouse is properly setup, you can transform your operational data into the data warehouse from the command line by running:

  ant transform

This would populate the dimension and fact tables, such as customer_dimension.

Setting Up Users and Security

See Setting up users in opentaps analytics.

Setting url base for xaction

By default, the dashboard and the reports are configured to use the "localhost" IP address. If you want to set up opentaps analytics to serve the reports and dashboard from another IP address, you would need to change the base URL by modifying it in pentaho/pentaho.war/WEB-INF/web.xml, changing it to the IP and port you are listening to:

  <context-param>
    <param-name>base-url</param-name>
    <param-value>http://192.168.0.124:8080/pentaho/</param-value>
  </context-param>

Changing Ports

If the Opentaps Analytics on JBoss won't start due to a port conflict, you may wish to change the port settings. See Opentaps Analytics JBoss Configuration for a list of configurable ports.

Google Map Key

Google map is configured to accept requests from a particular IP address. To set up the Google maps API, go to http://code.google.com/apis/maps/signup.html to obtain a map key, and then modify the file pentaho-solutions/system/google/googlesettings.xml to set your map key.

Reports

Accessing the Reports

Once you you have built your database and deployed opentaps analytics, you can start it from Pentaho with $ sh start-pentaho.sh and then access your reports from the browser with the URL http://localhost:8080/pentaho

You will be prompted for a login, and you can use the default login analytics and password opentaps. On the main page you will see a list of the reports available, including PDF reports, maps, and analytical cubes. You can also put your mouse over the "Go" link in the upper right-hand corner and click on "Solutions", which shows you the reports available in the solutions repository. The solutions repository is divided into different sections, such as "Customers" and "Orders", and you can click into the section to see the individual reports available.

Writing Your Own Reports

For simple reports, you can use Ad Hoc Reporting.

For standard reports, we have created a set of templates which can be used with the opentaps Analytics Report Generator.

For more customized reports, we recommend that you use iReport to design your reports in JasperReports and then deploy them into the Pentaho server. For more information on how to use iReports, see our iReports Tutorial.

Once you have created your report, save it into the one of the subdirectories of analytics/pentaho/opentaps/. For example, if it's a report for customer information, you can save it into the analytics/pentaho/opentaps/customer subdirectory. Your report will be saved as a .jrxml file.

Next, create an .xaction file which would register your report with Pentaho and tell it to run your report with JasperReports. You can create one by copying an existing .xaction file and changing just the name, description, and the .jrxml file referenced in it.

Finally, you need to deploy your new report and its corresponding .xaction file into the Pentaho server. This can be done by running $ ant deploy-pentaho Once this is done, your report should be ready to use. You do not even have to restart your Pentaho server.

Your reports can be internationalized by following the steps described in Internationalizing opentaps Analytics.

Dashboards

For opentaps analytics, we created a set of Java classes that you can use to create dashboards, instead of having to use the chart widget XML from Pentaho. Dashboard elements consist of a Java class to define the UI and set parameters in org.opentaps.analytics.dashboards. Some dashboards are in fact charts and they can also use a Xaction file to retrieve the data from the database. Defining a new dashboard just require to write the corresponding java class. There is no special interface to implement, only a toString() method used to render the dashboard in the HTML page. To use a Dashboard just instantiate the class in the Jsp, set the parameters and display it where needed.

Example

The Java class

Here is an example to implement a new Chart.

First the Java class NewDashboard should extend org.opentaps.analytics.dashboards.GenericChart (which define the methods to set the size of the chart).

Then you need to implement the constructors:

NewDashboard(IPentahoSession userSession, Integer width, Integer height) { super(userSession, width, height); }

or/and

NewDashboard(IPentahoSession userSession, Locale locale, Integer width, Integer height) { super(userSession, locale, width, height); }

which just need to pass the parameters to the superclass constructor. The locale can be used to localize the dashboard output (for example the chart title).

Then add some setter methods and the corresponding attributes to handle the parameters.

Finally implement the render() method, the most interesting part is to render the Chart:

StringBuffer content = new StringBuffer();

Chart chart = new Chart(userSession, width, height);
chart.setTitle(MessageUtil.getString(bundle, "NewDashboardTitle"));
chart.setDataAction("opentaps", "Xaction Jsp", "NewDashboard.xaction");

chart.setActionParameter("parameterName", parameterValue);
       
chart.doXXXChart(content);

return content.toString();

The Chart constructor set the chart image size. The setTitle() method illustrate how to localize the chart title, but setting a simple string here is also possible. The setDataAction define which xaction file is to be used, the arguments here define the path: solution_folder/subfolder/file.xaction If the xaction defined input parameters you can set them with setActionParameter() Finally the doXXXChart will render the chart, for now the available chart types are: doPieChart, doVBarChart, doHBarChart, doAreaChart. See org.opentaps.analytics.charts.Chart for the API.

The Xaction file

An xaction file can be combined with a dashboard Java class to get the data for a dashboard. The xaction file used for charting need to follow a few conventions:

• The output must be of type result-set and in with the name swresult, so in the action-sequence:

<outputs>
  <!-- an output stream will be provided by default -->
  <swresult type="result-set"/>
</outputs>

• And in the action-definition:

<action-outputs>
  <query-result type="result-set" mapping="swresult"/>
</action-outputs>

• Finally, the sql query must return data compatible with the chart type, for example a Bar or Pie Chart expects [name, value] and an Area Chart expects [name, date, value].

In the Jsp

When you want to display your dashboard, just instantiate the dashboard object in your JSP and display it:

<%
NewDashboard dashboard = new NewDashboard(userSession, locale, 300, 300);
dashboard.setParameter1("value");
%>

<%= dashboard %>

Report Filters

Report filters define which dimension can be used to filter each report. They are listed in pentaho/system/custom/query-builder.xml.

Each report is associated to the list of filters that cane be used, for example:

<report name="SalesByProductEx">
  <parameters>
    <parameter class="org.opentaps.analytics.query.filters.Product"/>
    <parameter class="org.opentaps.analytics.query.filters.Customer"/>
  </parameters>
</report>

Each filter is implemented as a Java class in org.opentaps.analytics.query.filters. A filter is basically a list of conditions, so to implement a new filter is very simple. For example this defines a filter whose title is the label FilterTitleLabel with one condition:

public class MyFilter extends GenericFilter {
    public MyFilter() {
        super("FilterTitleLabel");
        addParameter(new TextParameter("ParameterLabel", "sql_field_name", 40, 100));
        // ...
    }
}

Each condition is a sub class of org.opentaps.analytics.query.conditions.ReportParameter. For now only the TextParameter is implemented, the constructor takes the label, the sql field name(s), the text field length and max length. It also support passing a list of sql fields, for example if a table has both a primary_email and secondary_email fields, this allows to filter either of them:

 addParameter(new TextParameter("ParameterEmail", "email", new String[]{"primary_email", "other_email"}, 40, 100));

Unit Tests

Architecture

Unit tests for opentaps analytics are designed to verify that is giving you the correct results for your business data. It does this by checking that the combination of data transformation and reports correctly reflect the original data from the operational ERP and CRM systems that they came from. Because opentaps analytics is designed to work with a variety of ERP and CRM systems, the unit tests must span both the original ERP and CRM system and opentaps analytics.

We have designed the unit test strategy for opentaps analytics in the following fashion:

1. The original operational ERP and CRM system, such as opentaps, needs to have a test data generator which could generate large volumes of random data in its data schema. If possible, you should do this by calling the business logic tier of the ERP and CRM systems, rather than simply by writing to their database tables, so that the test could as much as possible mimic actual data generated during production.
2. While it is generating the data, the test data generator should record the expected results of the opentaps analytics reports based on the data and store it standard expected results tables. These expected results tables are defined as part of opentaps analytics and will be used later by opentaps analytics.
3. The test data generator should be run once to create a database with the operational data and the expected report results. Wide as possible to rerun the test data generator each time unit tests are needed, this could be very time consuming, as the test data generator should be set to generate a very large number of data points.
4. Then, opentaps analytics can be run against this database to transform the test operational data into its data warehouse model, run the reports, and then compare the results of the reports versus the expected results.

The expected results tables are the "handoff point" between the operational systems and the opentaps analytics. For each operational ERP and CRM system, a separate test data generator would be necessary, but the same expected results tables would be used in every case.

A Unit Test Example

As an example, considering writing a unit test for the transformation of customer data from opentaps to opentaps analytics. we want to verify that the transformation is done correctly and that the following report would generate the correct results:

  SELECT COUNTRY, STATE, COUNT(CUSTOMER_DIM_KEY) FROM CUSTOMER_DIMENSION GROUP BY COUNTRY, STATE 

The first step is to define a table of expected results for this query:

  CREATE TABLE EXP_CUST_COUNT_STATE_COUNTRY (
     COUNTRY VARCHAR(10) NOT NULL PRIMARY KEY,
     STATE VARCHAR(10) NOT NULL PRIMARY KEY,
     COUNT INT
  );

Next, create a customer generator in the original opentaps, which could call the opentaps business logic tier services for creating leads, contacts, or accounts with their full addresses, and then associating them with the role of BILL_TO_CUSTOMER:

for (long i = 0; i < 1000000; i++) {
    String firstName = ...; // random first name
    // ....
    String countryGeoId = ...; // randomly generated
    String stateGeoId = ...; // randomly generated from GeoAssoc of the countryGeoId 

    Map tmpResult = dispatcher.runSync("crmsfa.createLead", UtilMisc.toMap("firstName", firstName, 
                  ...., "generalStateProvinceGeoId", stateGeoId, "generalCountryGeoId", countryGeoId));
    // ....
    String partyId = (String) tmpResult.get("partyId");
    delegator.create("PartyRole", UtilMisc.toMap("partyId", partyId, "roleTypeId", "BILL_TO_CUSTOMER"));

While you do this, also make sure that you keep updating the expected results of the report:

    // then, add to the tally for this in EXP_CUST_COUNT_STATE_COUNTRY
    
}

Next, in your unit test, you can point the operational data source to the opentaps database in which you have just created the test data. You can then run your record and compare its results to the values already stored in EXP_CUST_COUNT_STATE_COUNTRY

Running the Analytics tests for Opentaps

To run the unit tests for opentaps analytics, follow these steps:

1. create a MySQL database called opentaps_analytics_testing and grant a user (ie, opentaps@localhost) access to it
2. from opentaps analytics, run $ ant setup-testing-db
3. start opentaps and use webtools to import the test geo data, which consist of ZIP codes, cities, states from hot-deploy/opentaps-tests/data/TestGeoData.xml
4. run opentaps.createCustomerDimensionTestData service from webtools
5. go to analytics/tests and run $ ant

References

1. iReports Tutorial
2. Transforming Data with Kettle
3. Introduction to MDX
4. Mondrian MDX Specifications
5. Pentaho xactions