[Branch ~dhis2-devs-core/dhis2/trunk] Rev 3841: Data mart: caching aggregated data values in a denormalized in-memory db table during dataelement...

Merge authors:
  Lars Helge Øverland (larshelge)

revision-diff.txt (42.2 KB)

···

------------------------------------------------------------
revno: 3841 [merge]
committer: Lars Helge Overland <larshelge@gmail.com>
branch nick: dhis2
timestamp: Sun 2011-06-05 18:45:59 +0200
message:
  Data mart: caching aggregated data values in a denormalized in-memory db table during dataelement export process and reading from that cache during indicator export. Total processing time on my complete export benchmark kenya database down 63 %.
added:
  dhis-2/dhis-services/dhis-service-datamart-default/src/main/java/org/hisp/dhis/datamart/DataElementOperandList.java
modified:
  dhis-2/dhis-services/dhis-service-datamart-default/src/main/java/org/hisp/dhis/datamart/crosstab/CrossTabService.java
  dhis-2/dhis-services/dhis-service-datamart-default/src/main/java/org/hisp/dhis/datamart/crosstab/DefaultCrossTabService.java
  dhis-2/dhis-services/dhis-service-datamart-default/src/main/java/org/hisp/dhis/datamart/crosstab/jdbc/CrossTabStore.java
  dhis-2/dhis-services/dhis-service-datamart-default/src/main/java/org/hisp/dhis/datamart/crosstab/jdbc/JDBCCrossTabStore.java
  dhis-2/dhis-services/dhis-service-datamart-default/src/main/java/org/hisp/dhis/datamart/dataelement/DataElementDataMart.java
  dhis-2/dhis-services/dhis-service-datamart-default/src/main/java/org/hisp/dhis/datamart/dataelement/DefaultDataElementDataMart.java
  dhis-2/dhis-services/dhis-service-datamart-default/src/main/java/org/hisp/dhis/datamart/engine/DefaultDataMartEngine.java
  dhis-2/dhis-services/dhis-service-datamart-default/src/main/java/org/hisp/dhis/datamart/indicator/DefaultIndicatorDataMart.java
  dhis-2/dhis-services/dhis-service-datamart-default/src/main/resources/META-INF/dhis/beans.xml
  dhis-2/dhis-services/dhis-service-datamart-default/src/test/java/org/hisp/dhis/datamart/crosstab/CrossTabServiceTest.java
  dhis-2/dhis-services/dhis-service-datamart-default/src/test/java/org/hisp/dhis/datamart/crosstab/CrossTabStoreTest.java

--
lp:dhis2
https://code.launchpad.net/~dhis2-devs-core/dhis2/trunk

Your team DHIS 2 developers is subscribed to branch lp:dhis2.
To unsubscribe from this branch go to https://code.launchpad.net/~dhis2-devs-core/dhis2/trunk/+edit-subscription

Sounds good. Going forward it might make sense to offer this as part
of the aggregation strategy, namely whether to use an in-memory DB (if
you have plenty of memory) or disk (if you do not).

···

On Sun, Jun 5, 2011 at 6:47 PM, <noreply@launchpad.net> wrote:

Merge authors:
Lars Helge Øverland (larshelge)
------------------------------------------------------------
revno: 3841 [merge]
committer: Lars Helge Overland <larshelge@gmail.com>
branch nick: dhis2
timestamp: Sun 2011-06-05 18:45:59 +0200
message:
Data mart: caching aggregated data values in a denormalized in-memory db table during dataelement export process and reading from that cache during indicator export. Total processing time on my complete export benchmark kenya database down 63 %.
added:
dhis-2/dhis-services/dhis-service-datamart-default/src/main/java/org/hisp/dhis/datamart/DataElementOperandList.java
modified:
dhis-2/dhis-services/dhis-service-datamart-default/src/main/java/org/hisp/dhis/datamart/crosstab/CrossTabService.java
dhis-2/dhis-services/dhis-service-datamart-default/src/main/java/org/hisp/dhis/datamart/crosstab/DefaultCrossTabService.java
dhis-2/dhis-services/dhis-service-datamart-default/src/main/java/org/hisp/dhis/datamart/crosstab/jdbc/CrossTabStore.java
dhis-2/dhis-services/dhis-service-datamart-default/src/main/java/org/hisp/dhis/datamart/crosstab/jdbc/JDBCCrossTabStore.java
dhis-2/dhis-services/dhis-service-datamart-default/src/main/java/org/hisp/dhis/datamart/dataelement/DataElementDataMart.java
dhis-2/dhis-services/dhis-service-datamart-default/src/main/java/org/hisp/dhis/datamart/dataelement/DefaultDataElementDataMart.java
dhis-2/dhis-services/dhis-service-datamart-default/src/main/java/org/hisp/dhis/datamart/engine/DefaultDataMartEngine.java
dhis-2/dhis-services/dhis-service-datamart-default/src/main/java/org/hisp/dhis/datamart/indicator/DefaultIndicatorDataMart.java
dhis-2/dhis-services/dhis-service-datamart-default/src/main/resources/META-INF/dhis/beans.xml
dhis-2/dhis-services/dhis-service-datamart-default/src/test/java/org/hisp/dhis/datamart/crosstab/CrossTabServiceTest.java
dhis-2/dhis-services/dhis-service-datamart-default/src/test/java/org/hisp/dhis/datamart/crosstab/CrossTabStoreTest.java

--
lp:dhis2
trunk : Code : DHIS

Your team DHIS 2 developers is subscribed to branch lp:dhis2.
To unsubscribe from this branch go to OpenID transaction in progress

=== added file 'dhis-2/dhis-services/dhis-service-datamart-default/src/main/java/org/hisp/dhis/datamart/DataElementOperandList.java'
--- dhis-2/dhis-services/dhis-service-datamart-default/src/main/java/org/hisp/dhis/datamart/DataElementOperandList.java 1970-01-01 00:00:00 +0000
+++ dhis-2/dhis-services/dhis-service-datamart-default/src/main/java/org/hisp/dhis/datamart/DataElementOperandList.java 2011-06-05 16:40:42 +0000
@@ -0,0 +1,63 @@
+package org.hisp.dhis.datamart;
+
+import java.util.Arrays;
+import java.util.List;
+
+import org.hisp.dhis.dataelement.DataElementOperand;
+import org.hisp.dhis.organisationunit.OrganisationUnit;
+import org.hisp.dhis.period.Period;
+
+public class DataElementOperandList
+{
+ private List<DataElementOperand> operands;
+
+ private Object[] valueList;
+
+ private boolean hasValues;
+
+ public DataElementOperandList( List<DataElementOperand> operands )
+ {
+ this.operands = operands;
+ }
+
+ public void init( Period period, OrganisationUnit unit )
+ {
+ this.hasValues = false;
+
+ if ( valid() )
+ {
+ this.valueList = new Object[operands.size() + 2];
+ this.valueList[0] = period.getId();
+ this.valueList[1] = unit.getId();
+ }
+ }
+
+ public void addValue( DataElementOperand operand, Double value )
+ {
+ if ( valid() )
+ {
+ final int index = operands.indexOf( operand );
+
+ if ( index != -1 && value != null )
+ {
+ this.valueList[index + 2] = value;
+ this.hasValues = true;
+ }
+ }
+ }
+
+ public List<Object> getList()
+ {
+ return valid() ? Arrays.asList( this.valueList ) : null;
+ }
+
+ public boolean valid()
+ {
+ return operands != null && operands.size() > 0;
+ }
+
+ public boolean hasValues()
+ {
+ return hasValues;
+ }
+}

=== modified file 'dhis-2/dhis-services/dhis-service-datamart-default/src/main/java/org/hisp/dhis/datamart/crosstab/CrossTabService.java'
--- dhis-2/dhis-services/dhis-service-datamart-default/src/main/java/org/hisp/dhis/datamart/crosstab/CrossTabService.java 2011-06-04 13:52:42 +0000
+++ dhis-2/dhis-services/dhis-service-datamart-default/src/main/java/org/hisp/dhis/datamart/crosstab/CrossTabService.java 2011-06-04 16:56:41 +0000
@@ -29,6 +29,7 @@

import java.util.Collection;
import java.util.List;
+import java.util.Map;
import java.util.Set;

import org.hisp.dhis.dataelement.DataElementOperand;
@@ -67,6 +68,10 @@
*/
void dropCrossTabTable( String key );

+ void createAggregatedDataCache( List<DataElementOperand> operands, String key );
+
+ void dropAggregatedDataCache( String key );
+
/**
* Gets all CrossTabDataValues for the given collection of period ids and source ids.
*
@@ -88,4 +93,7 @@
*/
Collection<CrossTabDataValue> getCrossTabDataValues( Collection<DataElementOperand> operands, Collection<Integer> periodIds,
int sourceId, String key );
+
+ Map<DataElementOperand, Double> getAggregatedDataCacheValue( Collection<DataElementOperand> operands,
+ int periodId, int sourceId, String key );
}

=== modified file 'dhis-2/dhis-services/dhis-service-datamart-default/src/main/java/org/hisp/dhis/datamart/crosstab/DefaultCrossTabService.java'
--- dhis-2/dhis-services/dhis-service-datamart-default/src/main/java/org/hisp/dhis/datamart/crosstab/DefaultCrossTabService.java 2011-06-04 13:52:42 +0000
+++ dhis-2/dhis-services/dhis-service-datamart-default/src/main/java/org/hisp/dhis/datamart/crosstab/DefaultCrossTabService.java 2011-06-04 16:56:41 +0000
@@ -106,9 +106,8 @@
crossTabStore.dropCrossTabTable( key );
crossTabStore.createCrossTabTable( operands, key );

- final BatchHandler<Object> batchHandler = batchHandlerFactory.createBatchHandler( GenericBatchHandler.class );
- batchHandler.setTableName( CrossTabStore.TABLE_PREFIX + key );
- batchHandler.init();
+ final BatchHandler<Object> batchHandler = batchHandlerFactory.createBatchHandler( GenericBatchHandler.class ).
+ setTableName( CrossTabStore.CROSSTAB_TABLE_PREFIX + key ).init();

        for \( final Integer periodId : periodIds \)
        \{

@@ -161,6 +160,16 @@
crossTabStore.dropCrossTabTable( key );
}

+ public void createAggregatedDataCache( List<DataElementOperand> operands, String key )
+ {
+ crossTabStore.createAggregatedDataCache( operands, key );
+ }
+
+ public void dropAggregatedDataCache( String key )
+ {
+ crossTabStore.dropAggregatedDataCache( key );
+ }
+
public Collection<CrossTabDataValue> getCrossTabDataValues( Collection<DataElementOperand> operands,
Collection<Integer> periodIds, Collection<Integer> sourceIds, String key )
{
@@ -172,6 +181,12 @@
{
return crossTabStore.getCrossTabDataValues( operands, periodIds, sourceId, key );
}
+
+ public Map<DataElementOperand, Double> getAggregatedDataCacheValue( Collection<DataElementOperand> operands,
+ int periodId, int sourceId, String key )
+ {
+ return crossTabStore.getAggregatedDataCacheValue( operands, periodId, sourceId, key );
+ }

// \-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-
// Supportive methods

=== modified file 'dhis-2/dhis-services/dhis-service-datamart-default/src/main/java/org/hisp/dhis/datamart/crosstab/jdbc/CrossTabStore.java'
--- dhis-2/dhis-services/dhis-service-datamart-default/src/main/java/org/hisp/dhis/datamart/crosstab/jdbc/CrossTabStore.java 2011-06-04 13:52:42 +0000
+++ dhis-2/dhis-services/dhis-service-datamart-default/src/main/java/org/hisp/dhis/datamart/crosstab/jdbc/CrossTabStore.java 2011-06-04 16:56:41 +0000
@@ -29,6 +29,7 @@

import java.util.Collection;
import java.util.List;
+import java.util.Map;

import org.hisp.dhis.dataelement.DataElementOperand;
import org.hisp.dhis.datamart.CrossTabDataValue;
@@ -40,8 +41,8 @@
public interface CrossTabStore
{
final String ID = CrossTabStore.class.getName();
- final String TABLE_PREFIX = "datavaluecrosstab_";
- final String TABLE_PREFIX_TRIMMED = "datavaluecrosstabtrimmed_";
+ final String CROSSTAB_TABLE_PREFIX = "crosstab_table_";
+ final String AGGREGATEDDATA_CACHE_PREFIX = "aggregateddata_cache_";

/\*\*
 \* Creates a crosstab table where the first column is the period identifier,

@@ -57,6 +58,10 @@
*/
void dropCrossTabTable( String key );

+ void createAggregatedDataCache( List<DataElementOperand> operands, String key );
+
+ void dropAggregatedDataCache( String key );
+
/**
* Gets all CrossTabDataValues for the given collection of period ids and source ids.
*
@@ -78,4 +83,6 @@
*/
Collection<CrossTabDataValue> getCrossTabDataValues( Collection<DataElementOperand> operands, Collection<Integer> periodIds,
int sourceId, String key );
+
+ Map<DataElementOperand, Double> getAggregatedDataCacheValue( Collection<DataElementOperand> operands, int periodId, int sourceId, String key );
}

=== modified file 'dhis-2/dhis-services/dhis-service-datamart-default/src/main/java/org/hisp/dhis/datamart/crosstab/jdbc/JDBCCrossTabStore.java'
--- dhis-2/dhis-services/dhis-service-datamart-default/src/main/java/org/hisp/dhis/datamart/crosstab/jdbc/JDBCCrossTabStore.java 2011-06-04 13:52:42 +0000
+++ dhis-2/dhis-services/dhis-service-datamart-default/src/main/java/org/hisp/dhis/datamart/crosstab/jdbc/JDBCCrossTabStore.java 2011-06-04 16:56:41 +0000
@@ -33,7 +33,9 @@
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Collection;
+import java.util.HashMap;
import java.util.List;
+import java.util.Map;

import org.amplecode.quick.StatementHolder;
import org.amplecode.quick.StatementManager;
@@ -64,7 +66,7 @@

public void createCrossTabTable\( List&lt;DataElementOperand&gt; operands, String key \)
\{

- final StringBuffer sql = new StringBuffer( "CREATE TABLE " + TABLE_PREFIX + key + " ( " );
+ final StringBuffer sql = new StringBuffer( "CREATE TABLE " + CROSSTAB_TABLE_PREFIX + key + " ( " );

    sql\.append\( &quot;periodid INTEGER NOT NULL, &quot; \);
    sql\.append\( &quot;sourceid INTEGER NOT NULL, &quot; \);

@@ -78,14 +80,34 @@

    statementManager\.getHolder\(\)\.executeUpdate\( sql\.toString\(\) \);
\}

-
+
public void dropCrossTabTable( String key )
{
- final String sql = "DROP TABLE IF EXISTS " + TABLE_PREFIX + key;
-
- statementManager.getHolder().executeUpdate( sql );
- }
-
+ statementManager.getHolder().executeUpdate( "DROP TABLE IF EXISTS " + CROSSTAB_TABLE_PREFIX + key );
+ }
+
+ public void createAggregatedDataCache( List<DataElementOperand> operands, String key )
+ {
+ final StringBuffer sql = new StringBuffer( "CREATE TABLE " + AGGREGATEDDATA_CACHE_PREFIX + key + " ( " );
+
+ sql.append( "periodid INTEGER NOT NULL, " );
+ sql.append( "sourceid INTEGER NOT NULL, " );
+
+ for ( DataElementOperand operand : operands )
+ {
+ sql.append( operand.getColumnName() ).append( " DOUBLE, " );
+ }
+
+ sql.append( "PRIMARY KEY ( periodid, sourceid ) );" );
+
+ statementManager.getHolder().executeUpdate( sql.toString() );
+ }
+
+ public void dropAggregatedDataCache( String key )
+ {
+ statementManager.getHolder().executeUpdate( "DROP TABLE IF EXISTS " + AGGREGATEDDATA_CACHE_PREFIX + key );
+ }
+
// -------------------------------------------------------------------------
// CrossTabDataValue
// -------------------------------------------------------------------------
@@ -95,7 +117,7 @@
{
final StatementHolder holder = statementManager.getHolder();

- final String sql = "SELECT * FROM " + TABLE_PREFIX + key + " AS c WHERE c.periodid IN (" +
+ final String sql = "SELECT * FROM " + CROSSTAB_TABLE_PREFIX + key + " AS c WHERE c.periodid IN (" +
getCommaDelimitedString( periodIds ) + ") AND c.sourceid IN (" + getCommaDelimitedString( sourceIds ) + ")";

    try

@@ -119,7 +141,7 @@
{
final StatementHolder holder = statementManager.getHolder();

- final String sql = "SELECT * FROM " + TABLE_PREFIX + key + " AS c WHERE c.periodid IN (" +
+ final String sql = "SELECT * FROM " + CROSSTAB_TABLE_PREFIX + key + " AS c WHERE c.periodid IN (" +
getCommaDelimitedString( periodIds ) + ") AND c.sourceid = " + sourceId;

    try

@@ -138,6 +160,44 @@
}
}

+ public Map<DataElementOperand, Double> getAggregatedDataCacheValue( Collection<DataElementOperand> operands,
+ int periodId, int sourceId, String key )
+ {
+ final StatementHolder holder = statementManager.getHolder();
+
+ final String sql = "SELECT * FROM " + AGGREGATEDDATA_CACHE_PREFIX + key + " AS a WHERE a.periodid = " + periodId + " AND a.sourceid = " + sourceId;
+
+ try
+ {
+ final Map<DataElementOperand, Double> valueMap = new HashMap<DataElementOperand, Double>();
+
+ final ResultSet resultSet = holder.getStatement().executeQuery( sql );
+
+ if ( resultSet.next() )
+ {
+ for ( DataElementOperand operand : operands )
+ {
+ final Double columnValue = resultSet.getDouble( operand.getColumnName() );
+
+ if ( columnValue != null )
+ {
+ valueMap.put( operand, columnValue );
+ }
+ }
+ }
+
+ return valueMap;
+ }
+ catch ( SQLException ex )
+ {
+ throw new RuntimeException( "Failed to get Map", ex );
+ }
+ finally
+ {
+ holder.close();
+ }
+ }
+
// -------------------------------------------------------------------------
// Supportive methods
// -------------------------------------------------------------------------
@@ -156,9 +216,7 @@

        for \( DataElementOperand operand : operands \)
        \{

- final String columnName = operand.getColumnName();
-
- final String columnValue = resultSet.getString( columnName );
+ final String columnValue = resultSet.getString( operand.getColumnName() );

            if \( columnValue \!= null \)
            \{

=== modified file 'dhis-2/dhis-services/dhis-service-datamart-default/src/main/java/org/hisp/dhis/datamart/dataelement/DataElementDataMart.java'
--- dhis-2/dhis-services/dhis-service-datamart-default/src/main/java/org/hisp/dhis/datamart/dataelement/DataElementDataMart.java 2011-06-03 21:33:10 +0000
+++ dhis-2/dhis-services/dhis-service-datamart-default/src/main/java/org/hisp/dhis/datamart/dataelement/DataElementDataMart.java 2011-06-05 16:40:02 +0000
@@ -30,6 +30,7 @@
import java.util.Collection;

import org.hisp.dhis.dataelement.DataElementOperand;
+import org.hisp.dhis.datamart.DataElementOperandList;
import org.hisp.dhis.organisationunit.OrganisationUnit;
import org.hisp.dhis.period.Period;

@@ -39,5 +40,5 @@
public interface DataElementDataMart
{
int exportDataValues( Collection<DataElementOperand> operands, Collection<Period> periods,
- Collection<OrganisationUnit> organisationUnits, String key );
+ Collection<OrganisationUnit> organisationUnits, DataElementOperandList operandList, String key );
}

=== modified file 'dhis-2/dhis-services/dhis-service-datamart-default/src/main/java/org/hisp/dhis/datamart/dataelement/DefaultDataElementDataMart.java'
--- dhis-2/dhis-services/dhis-service-datamart-default/src/main/java/org/hisp/dhis/datamart/dataelement/DefaultDataElementDataMart.java 2011-06-03 21:33:10 +0000
+++ dhis-2/dhis-services/dhis-service-datamart-default/src/main/java/org/hisp/dhis/datamart/dataelement/DefaultDataElementDataMart.java 2011-06-05 16:40:02 +0000
@@ -28,6 +28,7 @@
*/

import static org.hisp.dhis.system.util.MathUtils.getRounded;
+import static org.hisp.dhis.datamart.crosstab.jdbc.CrossTabStore.*;

import java.util.Collection;
import java.util.HashMap;
@@ -40,9 +41,11 @@
import org.apache.commons.logging.LogFactory;
import org.hisp.dhis.aggregation.AggregatedDataValue;
import org.hisp.dhis.dataelement.DataElementOperand;
+import org.hisp.dhis.datamart.DataElementOperandList;
import org.hisp.dhis.datamart.aggregation.cache.AggregationCache;
import org.hisp.dhis.datamart.aggregation.dataelement.DataElementAggregator;
import org.hisp.dhis.jdbc.batchhandler.AggregatedDataValueBatchHandler;
+import org.hisp.dhis.jdbc.batchhandler.GenericBatchHandler;
import org.hisp.dhis.organisationunit.OrganisationUnit;
import org.hisp.dhis.organisationunit.OrganisationUnitHierarchy;
import org.hisp.dhis.organisationunit.OrganisationUnitService;
@@ -75,7 +78,14 @@
{
this.batchHandlerFactory = batchHandlerFactory;
}
+
+ private BatchHandlerFactory inMemoryBatchHandlerFactory;

+ public void setInMemoryBatchHandlerFactory( BatchHandlerFactory inMemoryBatchHandlerFactory )
+ {
+ this.inMemoryBatchHandlerFactory = inMemoryBatchHandlerFactory;
+ }
+
private AggregationCache aggregationCache;

public void setAggregationCache\( AggregationCache aggregationCache \)

@@ -123,15 +133,17 @@
// -------------------------------------------------------------------------

public int exportDataValues\( final Collection&lt;DataElementOperand&gt; operands, final Collection&lt;Period&gt; periods,

- final Collection<OrganisationUnit> organisationUnits, String key )
+ final Collection<OrganisationUnit> organisationUnits, DataElementOperandList operandList, String key )
{
final BatchHandler<AggregatedDataValue> batchHandler = batchHandlerFactory.createBatchHandler( AggregatedDataValueBatchHandler.class ).init();

+ final BatchHandler<Object> cacheHandler = inMemoryBatchHandlerFactory.createBatchHandler( GenericBatchHandler.class ).setTableName( AGGREGATEDDATA_CACHE_PREFIX + key ).init();
+
final OrganisationUnitHierarchy hierarchy = organisationUnitService.getOrganisationUnitHierarchy().prepareChildren( organisationUnits );

    int count = 0;

- final AggregatedDataValue value = new AggregatedDataValue();
+ final AggregatedDataValue aggregatedValue = new AggregatedDataValue();

    for \( final Period period : periods \)
    \{

@@ -143,6 +155,8 @@

        for \( final OrganisationUnit unit : organisationUnits \)
        \{

+ operandList.init( period, unit );
+
final int level = aggregationCache.getLevelOfOrganisationUnit( unit.getId() );

            final Map&lt;DataElementOperand, Double&gt; valueMap = new HashMap&lt;DataElementOperand, Double&gt;\(\);

@@ -153,22 +167,33 @@
valueMap.putAll( sumBoolAggregator.getAggregatedValues( sumBoolOperands, period, unit, level, hierarchy, key ) );
valueMap.putAll( averageBoolAggregator.getAggregatedValues( averageBoolOperands, period, unit, level, hierarchy, key ) );

+ //TODO check size of value map
+
for ( Entry<DataElementOperand, Double> entry : valueMap.entrySet() )
{
- value.clear();
-
- value.setDataElementId( entry.getKey().getDataElementId() );
- value.setCategoryOptionComboId( entry.getKey().getOptionComboId() );
- value.setPeriodId( period.getId() );
- value.setPeriodTypeId( period.getPeriodType().getId() );
- value.setOrganisationUnitId( unit.getId() );
- value.setLevel( level );
- value.setValue( getRounded( entry.getValue(), DECIMALS ) );
-
- batchHandler.addObject( value );
+ aggregatedValue.clear();
+
+ final double value = getRounded( entry.getValue(), DECIMALS );
+
+ aggregatedValue.setDataElementId( entry.getKey().getDataElementId() );
+ aggregatedValue.setCategoryOptionComboId( entry.getKey().getOptionComboId() );
+ aggregatedValue.setPeriodId( period.getId() );
+ aggregatedValue.setPeriodTypeId( period.getPeriodType().getId() );
+ aggregatedValue.setOrganisationUnitId( unit.getId() );
+ aggregatedValue.setLevel( level );
+ aggregatedValue.setValue( value );
+
+ batchHandler.addObject( aggregatedValue );
+
+ operandList.addValue( entry.getKey(), value );

                count\+\+;
            \}

+
+ if ( operandList.hasValues() )
+ {
+ cacheHandler.addObject( operandList.getList() );
+ }
}

        log\.debug\( &quot;Exported data values for period: &quot; \+ period \);

@@ -176,6 +201,8 @@

    batchHandler\.flush\(\);

+ cacheHandler.flush();
+
return count;
}
}

=== modified file 'dhis-2/dhis-services/dhis-service-datamart-default/src/main/java/org/hisp/dhis/datamart/engine/DefaultDataMartEngine.java'
--- dhis-2/dhis-services/dhis-service-datamart-default/src/main/java/org/hisp/dhis/datamart/engine/DefaultDataMartEngine.java 2011-06-04 13:52:42 +0000
+++ dhis-2/dhis-services/dhis-service-datamart-default/src/main/java/org/hisp/dhis/datamart/engine/DefaultDataMartEngine.java 2011-06-05 16:45:59 +0000
@@ -30,6 +30,7 @@
import java.util.ArrayList;
import java.util.Collection;
import java.util.HashSet;
+import java.util.List;
import java.util.Set;

import org.apache.commons.logging.Log;
@@ -40,6 +41,7 @@
import org.hisp.dhis.dataelement.DataElementCategoryService;
import org.hisp.dhis.dataelement.DataElementOperand;
import org.hisp.dhis.dataelement.DataElementService;
+import org.hisp.dhis.datamart.DataElementOperandList;
import org.hisp.dhis.datamart.aggregation.cache.AggregationCache;
import org.hisp.dhis.datamart.crosstab.CrossTabService;
import org.hisp.dhis.datamart.dataelement.DataElementDataMart;
@@ -54,7 +56,6 @@
import org.hisp.dhis.system.util.ConversionUtils;
import org.hisp.dhis.system.util.TimeUtils;
import org.springframework.transaction.annotation.Transactional;
-import org.springframework.util.CollectionUtils;

/**
* @author Lars Helge Overland
@@ -181,7 +182,7 @@
// ---------------------------------------------------------------------

    Collection&lt;DataElementOperand&gt; dataElementOperands = categoryService\.getOperands\( dataElements \);

- Collection<DataElementOperand> indicatorOperands = categoryService.populateOperands( getOperandsInIndicators( indicators ) );
+ List<DataElementOperand> indicatorOperands = new ArrayList<DataElementOperand>( categoryService.populateOperands( getOperandsInIndicators( indicators ) ) );

    Set&lt;DataElementOperand&gt; allOperands = new HashSet&lt;DataElementOperand&gt;\(\);
    allOperands\.addAll\( dataElementOperands \);

@@ -195,13 +196,12 @@

    allOperands = crossTabService\.getOperandsWithData\( allOperands \);

- dataElementOperands.retainAll( allOperands );
indicatorOperands.retainAll( allOperands );

    log\.info\( &quot;Number of operands with data: &quot; \+ allOperands\.size\(\) \+ &quot;, &quot;\+ TimeUtils\.getHMS\(\) \);

    // \-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-

- // Create and trim crosstabtable
+ // Create crosstabtable
// ---------------------------------------------------------------------

    state\.setMessage\( &quot;crosstabulating\_data&quot; \);

@@ -211,14 +211,19 @@

    String key = crossTabService\.populateCrossTabTable\( new ArrayList&lt;DataElementOperand&gt;\( allOperands \), intersectingPeriodIds, childrenIds \);

- if ( CollectionUtils.isEmpty( allOperands ) )
- {
- return 0;
- }
-
log.info( "Populated crosstab table: " + TimeUtils.getHMS() );

    // \-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-

+ // Create aggregated data cache
+ // ---------------------------------------------------------------------
+
+ DataElementOperandList operandList = new DataElementOperandList( indicatorOperands );
+
+ crossTabService.createAggregatedDataCache( indicatorOperands, key );
+
+ log.info( "Created aggregated data cache" );
+
+ // ---------------------------------------------------------------------
// Drop potential indexes
// ---------------------------------------------------------------------

@@ -246,11 +251,11 @@

    state\.setMessage\( &quot;exporting\_data\_for\_data\_elements&quot; \);

- if ( dataElementOperands.size() > 0 )
+ if ( allOperands.size() > 0 )
{
- count += dataElementDataMart.exportDataValues( dataElementOperands, periods, organisationUnits, key );
+ count += dataElementDataMart.exportDataValues( allOperands, periods, organisationUnits, operandList, key );

- log.info( "Exported values for data element operands (" + dataElementOperands.size() + "): " + TimeUtils.getHMS() );
+ log.info( "Exported values for data element operands (" + allOperands.size() + "): " + TimeUtils.getHMS() );
}

    // \-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-

@@ -267,12 +272,14 @@
}

    // \-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-

- // Drop crosstab tables
+ // Drop crosstab table and aggregated data cache
// ---------------------------------------------------------------------

- crossTabService.dropCrossTabTable( key );
-
- log.info( "Dropped crosstab table: " + TimeUtils.getHMS() );
+ crossTabService.dropCrossTabTable( key ); //TODO move before indicator export
+
+ crossTabService.dropAggregatedDataCache( key );
+
+ log.info( "Dropped crosstab table and aggregated data cache: " + TimeUtils.getHMS() );

    // \-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-
    // Create potential indexes

=== modified file 'dhis-2/dhis-services/dhis-service-datamart-default/src/main/java/org/hisp/dhis/datamart/indicator/DefaultIndicatorDataMart.java'
--- dhis-2/dhis-services/dhis-service-datamart-default/src/main/java/org/hisp/dhis/datamart/indicator/DefaultIndicatorDataMart.java 2011-06-03 21:33:10 +0000
+++ dhis-2/dhis-services/dhis-service-datamart-default/src/main/java/org/hisp/dhis/datamart/indicator/DefaultIndicatorDataMart.java 2011-06-05 16:40:02 +0000
@@ -28,12 +28,11 @@
*/

import static org.hisp.dhis.options.SystemSettingManager.KEY_OMIT_INDICATORS_ZERO_NUMERATOR_DATAMART;
+import static org.hisp.dhis.system.util.DateUtils.daysBetween;
import static org.hisp.dhis.system.util.MathUtils.calculateExpression;
import static org.hisp.dhis.system.util.MathUtils.getRounded;
-import static org.hisp.dhis.system.util.DateUtils.daysBetween;

import java.util.Collection;
-import java.util.HashMap;
import java.util.Map;

import org.amplecode.quick.BatchHandler;
@@ -43,14 +42,12 @@
import org.hisp.dhis.aggregation.AggregatedIndicatorValue;
import org.hisp.dhis.dataelement.DataElementOperand;
import org.hisp.dhis.datamart.aggregation.cache.AggregationCache;
-import org.hisp.dhis.datamart.aggregation.dataelement.DataElementAggregator;
+import org.hisp.dhis.datamart.crosstab.CrossTabService;
import org.hisp.dhis.expression.ExpressionService;
import org.hisp.dhis.indicator.Indicator;
import org.hisp.dhis.jdbc.batchhandler.AggregatedIndicatorValueBatchHandler;
import org.hisp.dhis.options.SystemSettingManager;
import org.hisp.dhis.organisationunit.OrganisationUnit;
-import org.hisp.dhis.organisationunit.OrganisationUnitHierarchy;
-import org.hisp.dhis.organisationunit.OrganisationUnitService;
import org.hisp.dhis.period.Period;
import org.hisp.dhis.period.PeriodType;
import org.hisp.dhis.system.util.DateUtils;
@@ -72,40 +69,12 @@
// Dependencies
// -------------------------------------------------------------------------

- private OrganisationUnitService organisationUnitService;
-
- public void setOrganisationUnitService( OrganisationUnitService organisationUnitService )
- {
- this.organisationUnitService = organisationUnitService;
- }
-
private ExpressionService expressionService;

public void setExpressionService\( ExpressionService expressionService \)
\{
    this\.expressionService = expressionService;
\}

-
- private DataElementAggregator sumIntAggregator;
-
- public void setSumIntAggregator( DataElementAggregator sumIntDataElementAggregator )
- {
- this.sumIntAggregator = sumIntDataElementAggregator;
- }
-
- private DataElementAggregator averageIntAggregator;
-
- public void setAverageIntAggregator( DataElementAggregator averageIntDataElementAggregator )
- {
- this.averageIntAggregator = averageIntDataElementAggregator;
- }
-
- private DataElementAggregator averageIntSingleValueAggregator;
-
- public void setAverageIntSingleValueAggregator( DataElementAggregator averageIntSingleValueAggregator )
- {
- this.averageIntSingleValueAggregator = averageIntSingleValueAggregator;
- }

private AggregationCache aggregationCache;

@@ -121,13 +90,20 @@
this.systemSettingManager = systemSettingManager;
}

+ private CrossTabService crossTabService;
+
+ public void setCrossTabService( CrossTabService crossTabService )
+ {
+ this.crossTabService = crossTabService;
+ }
+
private BatchHandlerFactory batchHandlerFactory;

public void setBatchHandlerFactory\( BatchHandlerFactory batchHandlerFactory \)
\{
    this\.batchHandlerFactory = batchHandlerFactory;
\}

-
+
// -------------------------------------------------------------------------
// IndicatorDataMart implementation
// -------------------------------------------------------------------------
@@ -137,15 +113,8 @@
{
final BatchHandler<AggregatedIndicatorValue> batchHandler = batchHandlerFactory.createBatchHandler( AggregatedIndicatorValueBatchHandler.class ).init();

- final OrganisationUnitHierarchy hierarchy = organisationUnitService.getOrganisationUnitHierarchy().prepareChildren( organisationUnits );
-
int count = 0;

- double annualizationFactor = 0.0;
- double factor = 0.0;
- double aggregatedValue = 0.0;
- double annualizedFactor = 0.0;
-
final boolean omitZeroNumerator = (Boolean) systemSettingManager.getSystemSetting( KEY_OMIT_INDICATORS_ZERO_NUMERATOR_DATAMART, false );

    final AggregatedIndicatorValue indicatorValue = new AggregatedIndicatorValue\(\);

@@ -156,57 +125,47 @@

        final PeriodType periodType = period\.getPeriodType\(\);

- final Collection<DataElementOperand> sumOperands = sumIntAggregator.filterOperands( operands, periodType );
- final Collection<DataElementOperand> averageOperands = averageIntAggregator.filterOperands( operands, periodType );
- final Collection<DataElementOperand> averageSingleValueOperands = averageIntSingleValueAggregator.filterOperands( operands, periodType );
-
for ( final OrganisationUnit unit : organisationUnits )
{
final int level = aggregationCache.getLevelOfOrganisationUnit( unit.getId() );

- final Map<DataElementOperand, Double> sumIntValueMap = sumIntAggregator.getAggregatedValues( sumOperands, period, unit, level, hierarchy, key );
- final Map<DataElementOperand, Double> averageIntValueMap = averageIntAggregator.getAggregatedValues( averageOperands, period, unit, level, hierarchy, key );
- final Map<DataElementOperand, Double> averageIntSingleValueMap = averageIntSingleValueAggregator.getAggregatedValues( averageSingleValueOperands, period, unit, level, hierarchy, key );
-
- final Map<DataElementOperand, Double> valueMap = new HashMap<DataElementOperand, Double>( sumIntValueMap );
- valueMap.putAll( averageIntValueMap );
- valueMap.putAll( averageIntSingleValueMap );
-
- for ( final Indicator indicator : indicators )
- {
- final double numeratorValue = calculateExpression( expressionService.generateExpression( indicator.getExplodedNumerator(), valueMap, days ) );
- final double denominatorValue = calculateExpression( expressionService.generateExpression( indicator.getExplodedDenominator(), valueMap, days ) );
-
- // ---------------------------------------------------------
- // AggregatedIndicatorValue
- // ---------------------------------------------------------
-
- if ( denominatorValue != 0 && !( omitZeroNumerator && numeratorValue == 0 ) )
+ final Map<DataElementOperand, Double> valueMap = crossTabService.getAggregatedDataCacheValue( operands, period.getId(), unit.getId(), key );
+
+ if ( valueMap.size() > 0 )
+ {
+ for ( final Indicator indicator : indicators )
{
- annualizationFactor = DateUtils.getAnnualizationFactor( indicator, period.getStartDate(), period.getEndDate() );
-
- factor = indicator.getIndicatorType().getFactor();
-
- aggregatedValue = ( numeratorValue / denominatorValue ) * factor * annualizationFactor;
-
- annualizedFactor = factor * annualizationFactor;
-
- indicatorValue.clear();
-
- indicatorValue.setIndicatorId( indicator.getId() );
- indicatorValue.setPeriodId( period.getId() );
- indicatorValue.setPeriodTypeId( periodType.getId() );
- indicatorValue.setOrganisationUnitId( unit.getId() );
- indicatorValue.setLevel( level );
- indicatorValue.setAnnualized( getAnnualizationString( indicator.getAnnualized() ) );
- indicatorValue.setFactor( annualizedFactor);
- indicatorValue.setValue( getRounded( aggregatedValue, DECIMALS ) );
- indicatorValue.setNumeratorValue( getRounded( numeratorValue, DECIMALS ) );
- indicatorValue.setDenominatorValue( getRounded( denominatorValue, DECIMALS ) );
-
- batchHandler.addObject( indicatorValue );
-
- count++;
+ final double numeratorValue = calculateExpression( expressionService.generateExpression( indicator.getExplodedNumerator(), valueMap, days ) );
+ final double denominatorValue = calculateExpression( expressionService.generateExpression( indicator.getExplodedDenominator(), valueMap, days ) );
+
+ // ---------------------------------------------------------
+ // AggregatedIndicatorValue
+ // ---------------------------------------------------------
+
+ if ( denominatorValue != 0 && !( omitZeroNumerator && numeratorValue == 0 ) )
+ {
+ final double annualizationFactor = DateUtils.getAnnualizationFactor( indicator, period.getStartDate(), period.getEndDate() );
+ final double factor = indicator.getIndicatorType().getFactor();
+ final double aggregatedValue = ( numeratorValue / denominatorValue ) * factor * annualizationFactor;
+ final double annualizedFactor = factor * annualizationFactor;
+
+ indicatorValue.clear();
+
+ indicatorValue.setIndicatorId( indicator.getId() );
+ indicatorValue.setPeriodId( period.getId() );
+ indicatorValue.setPeriodTypeId( periodType.getId() );
+ indicatorValue.setOrganisationUnitId( unit.getId() );
+ indicatorValue.setLevel( level );
+ indicatorValue.setAnnualized( getAnnualizationString( indicator.getAnnualized() ) );
+ indicatorValue.setFactor( annualizedFactor);
+ indicatorValue.setValue( getRounded( aggregatedValue, DECIMALS ) );
+ indicatorValue.setNumeratorValue( getRounded( numeratorValue, DECIMALS ) );
+ indicatorValue.setDenominatorValue( getRounded( denominatorValue, DECIMALS ) );
+
+ batchHandler.addObject( indicatorValue );
+
+ count++;
+ }
}
}
}

=== modified file 'dhis-2/dhis-services/dhis-service-datamart-default/src/main/resources/META-INF/dhis/beans.xml'
--- dhis-2/dhis-services/dhis-service-datamart-default/src/main/resources/META-INF/dhis/beans.xml 2011-06-03 21:33:10 +0000
+++ dhis-2/dhis-services/dhis-service-datamart-default/src/main/resources/META-INF/dhis/beans.xml 2011-06-05 16:40:02 +0000
@@ -140,6 +140,7 @@
<property name="organisationUnitService"
ref="org.hisp.dhis.organisationunit.OrganisationUnitService"/>
<property name="batchHandlerFactory" ref="batchHandlerFactory"/>
+ <property name="inMemoryBatchHandlerFactory" ref="inMemoryBatchHandlerFactory"/>
<property name="aggregationCache"
ref="org.hisp.dhis.datamart.aggregation.cache.AggregationCache"/>
<property name="sumIntAggregator"
@@ -158,20 +159,14 @@

<bean id="org.hisp.dhis.datamart.indicator.IndicatorDataMart"
class="org.hisp.dhis.datamart.indicator.DefaultIndicatorDataMart">
- <property name="organisationUnitService"
- ref="org.hisp.dhis.organisationunit.OrganisationUnitService"/>
<property name="expressionService"
ref="org.hisp.dhis.expression.ExpressionService"/>
- <property name="sumIntAggregator"
- ref="org.hisp.dhis.datamart.aggregation.dataelement.SumIntAggregator"/>
- <property name="averageIntAggregator"
- ref="org.hisp.dhis.datamart.aggregation.dataelement.AverageIntAggregator"/>
- <property name="averageIntSingleValueAggregator"
- ref="org.hisp.dhis.datamart.aggregation.dataelement.AverageIntSingleValueAggregator"/>
<property name="aggregationCache"
ref="org.hisp.dhis.datamart.aggregation.cache.AggregationCache"/>
<property name="systemSettingManager"
ref="org.hisp.dhis.options.SystemSettingManager"/>
+ <property name="crossTabService"
+ ref="org.hisp.dhis.datamart.crosstab.CrossTabService"/>
<property name="batchHandlerFactory" ref="batchHandlerFactory"/>
</bean>

=== modified file 'dhis-2/dhis-services/dhis-service-datamart-default/src/test/java/org/hisp/dhis/datamart/crosstab/CrossTabServiceTest.java'
--- dhis-2/dhis-services/dhis-service-datamart-default/src/test/java/org/hisp/dhis/datamart/crosstab/CrossTabServiceTest.java 2011-06-04 13:52:42 +0000
+++ dhis-2/dhis-services/dhis-service-datamart-default/src/test/java/org/hisp/dhis/datamart/crosstab/CrossTabServiceTest.java 2011-06-04 16:56:41 +0000
@@ -37,7 +37,11 @@
import java.util.HashSet;
import java.util.Iterator;
import java.util.List;
+import java.util.Map;

+import org.amplecode.quick.BatchHandler;
+import org.amplecode.quick.BatchHandlerFactory;
+import org.apache.commons.lang.RandomStringUtils;
import org.hisp.dhis.DhisTest;
import org.hisp.dhis.dataelement.DataElement;
import org.hisp.dhis.dataelement.DataElementCategory;
@@ -48,7 +52,9 @@
import org.hisp.dhis.dataelement.DataElementOperand;
import org.hisp.dhis.dataelement.DataElementService;
import org.hisp.dhis.datamart.CrossTabDataValue;
+import org.hisp.dhis.datamart.crosstab.jdbc.CrossTabStore;
import org.hisp.dhis.datavalue.DataValueService;
+import org.hisp.dhis.jdbc.batchhandler.GenericBatchHandler;
import org.hisp.dhis.organisationunit.OrganisationUnit;
import org.hisp.dhis.organisationunit.OrganisationUnitService;
import org.hisp.dhis.period.Period;
@@ -65,6 +71,8 @@
{
private CrossTabService crossTabService;

+ private BatchHandlerFactory batchHandlerFactory;
+
private Iterator<Period> generatedPeriods;

private List&lt;DataElementOperand&gt; operands;

@@ -80,6 +88,8 @@
{
crossTabService = (CrossTabService) getBean( CrossTabService.ID );

+ batchHandlerFactory = (BatchHandlerFactory) getBean( "inMemoryBatchHandlerFactory" );
+
categoryService = (DataElementCategoryService) getBean( DataElementCategoryService.ID );

    dataElementService = \(DataElementService\) getBean\( DataElementService\.ID \);

@@ -203,4 +213,36 @@
}
}
}
+
+ @Test
+ public void testPopulateAggregatedDataCache()
+ {
+ String key = RandomStringUtils.randomAlphanumeric( 8 );
+
+ crossTabService.createAggregatedDataCache( operands, key );
+
+ BatchHandler<Object> batchHandler = batchHandlerFactory.createBatchHandler( GenericBatchHandler.class ).
+ setTableName( CrossTabStore.AGGREGATEDDATA_CACHE_PREFIX + key ).init();
+
+ List<Object> valueList = new ArrayList<Object>( operands.size() + 2 );
+ valueList.add( 1 );
+ valueList.add( 1 );
+
+ for ( int i = 0; i < operands.size(); i++ )
+ {
+ valueList.add( 10.0 );
+ }
+
+ batchHandler.addObject( valueList );
+
+ batchHandler.flush();
+
+ Map<DataElementOperand, Double> valueMap = crossTabService.getAggregatedDataCacheValue( operands, 1, 1, key );
+
+ for ( DataElementOperand operand : valueMap.keySet() )
+ {
+ assertNotNull( valueMap.get( operand ) );
+ assertEquals( 10.0, valueMap.get( operand ) );
+ }
+ }
}

=== modified file 'dhis-2/dhis-services/dhis-service-datamart-default/src/test/java/org/hisp/dhis/datamart/crosstab/CrossTabStoreTest.java'
--- dhis-2/dhis-services/dhis-service-datamart-default/src/test/java/org/hisp/dhis/datamart/crosstab/CrossTabStoreTest.java 2011-01-10 10:06:18 +0000
+++ dhis-2/dhis-services/dhis-service-datamart-default/src/test/java/org/hisp/dhis/datamart/crosstab/CrossTabStoreTest.java 2011-06-04 16:56:41 +0000
@@ -51,4 +51,12 @@

    crossTabStore\.dropCrossTabTable\( key \);
\}

+
+ @Test
+ public void testDropAggregatedDataCache()
+ {
+ crossTabStore.createAggregatedDataCache( operands, key );
+
+ crossTabStore.dropAggregatedDataCache( key );
+ }
}

_______________________________________________
Mailing list: DHIS 2 developers in Launchpad
Post to : dhis2-devs@lists.launchpad.net
Unsubscribe : DHIS 2 developers in Launchpad
More help : ListHelp - Launchpad Help

Sounds good. Going forward it might make sense to offer this as part

of the aggregation strategy, namely whether to use an in-memory DB (if

you have plenty of memory) or disk (if you do not).

Yes I agree… In my tests this db never used more than ~ 400 MB of memory so should not be a problem on server setups, but it might be on standalone installations on weaker computers…

···

On Mon, Jun 6, 2011 at 7:22 AM, Jason Pickering jason.p.pickering@gmail.com wrote:

On Sun, Jun 5, 2011 at 6:47 PM, noreply@launchpad.net wrote:

Merge authors:

Lars Helge Øverland (larshelge)


revno: 3841 [merge]

committer: Lars Helge Overland larshelge@gmail.com

branch nick: dhis2

timestamp: Sun 2011-06-05 18:45:59 +0200

message:

Data mart: caching aggregated data values in a denormalized in-memory db table during dataelement export process and reading from that cache during indicator export. Total processing time on my complete export benchmark kenya database down 63 %.

added:

dhis-2/dhis-services/dhis-service-datamart-default/src/main/java/org/hisp/dhis/datamart/DataElementOperandList.java

modified:

dhis-2/dhis-services/dhis-service-datamart-default/src/main/java/org/hisp/dhis/datamart/crosstab/CrossTabService.java

dhis-2/dhis-services/dhis-service-datamart-default/src/main/java/org/hisp/dhis/datamart/crosstab/DefaultCrossTabService.java

dhis-2/dhis-services/dhis-service-datamart-default/src/main/java/org/hisp/dhis/datamart/crosstab/jdbc/CrossTabStore.java

dhis-2/dhis-services/dhis-service-datamart-default/src/main/java/org/hisp/dhis/datamart/crosstab/jdbc/JDBCCrossTabStore.java

dhis-2/dhis-services/dhis-service-datamart-default/src/main/java/org/hisp/dhis/datamart/dataelement/DataElementDataMart.java

dhis-2/dhis-services/dhis-service-datamart-default/src/main/java/org/hisp/dhis/datamart/dataelement/DefaultDataElementDataMart.java

dhis-2/dhis-services/dhis-service-datamart-default/src/main/java/org/hisp/dhis/datamart/engine/DefaultDataMartEngine.java

dhis-2/dhis-services/dhis-service-datamart-default/src/main/java/org/hisp/dhis/datamart/indicator/DefaultIndicatorDataMart.java

dhis-2/dhis-services/dhis-service-datamart-default/src/main/resources/META-INF/dhis/beans.xml

dhis-2/dhis-services/dhis-service-datamart-default/src/test/java/org/hisp/dhis/datamart/crosstab/CrossTabServiceTest.java

dhis-2/dhis-services/dhis-service-datamart-default/src/test/java/org/hisp/dhis/datamart/crosstab/CrossTabStoreTest.java

lp:dhis2

https://code.launchpad.net/~dhis2-devs-core/dhis2/trunk

Your team DHIS 2 developers is subscribed to branch lp:dhis2.

To unsubscribe from this branch go to https://code.launchpad.net/~dhis2-devs-core/dhis2/trunk/+edit-subscription

=== added file ‘dhis-2/dhis-services/dhis-service-datamart-default/src/main/java/org/hisp/dhis/datamart/DataElementOperandList.java’

— dhis-2/dhis-services/dhis-service-datamart-default/src/main/java/org/hisp/dhis/datamart/DataElementOperandList.java 1970-01-01 00:00:00 +0000

+++ dhis-2/dhis-services/dhis-service-datamart-default/src/main/java/org/hisp/dhis/datamart/DataElementOperandList.java 2011-06-05 16:40:42 +0000

@@ -0,0 +1,63 @@

+package org.hisp.dhis.datamart;

+import java.util.Arrays;

+import java.util.List;

+import org.hisp.dhis.dataelement.DataElementOperand;

+import org.hisp.dhis.organisationunit.OrganisationUnit;

+import org.hisp.dhis.period.Period;

+public class DataElementOperandList

+{

  • private List operands;
  • private Object[] valueList;
  • private boolean hasValues;
  • public DataElementOperandList( List operands )
  • {
  •    this.operands = operands;
    
  • }
  • public void init( Period period, OrganisationUnit unit )
  • {
  •    this.hasValues = false;
    
  •    if ( valid() )
    
  •    {
    
  •        this.valueList = new Object[operands.size() + 2];
    
  •        this.valueList[0] = period.getId();
    
  •        this.valueList[1] = unit.getId();
    
  •    }
    
  • }
  • public void addValue( DataElementOperand operand, Double value )
  • {
  •    if ( valid() )
    
  •    {
    
  •        final int index = operands.indexOf( operand );
    
  •        if ( index != -1 && value != null )
    
  •        {
    
  •            this.valueList[index + 2] = value;
    
  •            this.hasValues = true;
    
  •        }
    
  •    }
    
  • }
  • public List getList()
  • {
  •    return valid() ? Arrays.asList( this.valueList ) : null;
    
  • }
  • public boolean valid()
  • {
  •    return operands != null && operands.size() > 0;
    
  • }
  • public boolean hasValues()
  • {
  •    return hasValues;
    
  • }

+}

=== modified file ‘dhis-2/dhis-services/dhis-service-datamart-default/src/main/java/org/hisp/dhis/datamart/crosstab/CrossTabService.java’

— dhis-2/dhis-services/dhis-service-datamart-default/src/main/java/org/hisp/dhis/datamart/crosstab/CrossTabService.java 2011-06-04 13:52:42 +0000

+++ dhis-2/dhis-services/dhis-service-datamart-default/src/main/java/org/hisp/dhis/datamart/crosstab/CrossTabService.java 2011-06-04 16:56:41 +0000

@@ -29,6 +29,7 @@

import java.util.Collection;

import java.util.List;

+import java.util.Map;

import java.util.Set;

import org.hisp.dhis.dataelement.DataElementOperand;

@@ -67,6 +68,10 @@

 */
void dropCrossTabTable( String key );
  • void createAggregatedDataCache( List operands, String key );
  • void dropAggregatedDataCache( String key );
/**
 * Gets all CrossTabDataValues for the given collection of period ids and source ids.
 *

@@ -88,4 +93,7 @@

 */
Collection<CrossTabDataValue> getCrossTabDataValues( Collection<DataElementOperand> operands, Collection<Integer> periodIds,
    int sourceId, String key );
  • Map<DataElementOperand, Double> getAggregatedDataCacheValue( Collection operands,
  •    int periodId, int sourceId, String key );
    

}

=== modified file ‘dhis-2/dhis-services/dhis-service-datamart-default/src/main/java/org/hisp/dhis/datamart/crosstab/DefaultCrossTabService.java’

— dhis-2/dhis-services/dhis-service-datamart-default/src/main/java/org/hisp/dhis/datamart/crosstab/DefaultCrossTabService.java 2011-06-04 13:52:42 +0000

+++ dhis-2/dhis-services/dhis-service-datamart-default/src/main/java/org/hisp/dhis/datamart/crosstab/DefaultCrossTabService.java 2011-06-04 16:56:41 +0000

@@ -106,9 +106,8 @@

        crossTabStore.dropCrossTabTable( key );
        crossTabStore.createCrossTabTable( operands, key );
  •        final BatchHandler<Object> batchHandler = batchHandlerFactory.createBatchHandler( GenericBatchHandler.class );
    
  •        batchHandler.setTableName( CrossTabStore.TABLE_PREFIX + key );
    
  •        batchHandler.init();
    
  •        final BatchHandler<Object> batchHandler = batchHandlerFactory.createBatchHandler( GenericBatchHandler.class ).
    
  •            setTableName( CrossTabStore.CROSSTAB_TABLE_PREFIX + key ).init();
    
        for ( final Integer periodId : periodIds )
        {

@@ -161,6 +160,16 @@

    crossTabStore.dropCrossTabTable( key );
}
  • public void createAggregatedDataCache( List operands, String key )
  • {
  •    crossTabStore.createAggregatedDataCache( operands, key );
    
  • }
  • public void dropAggregatedDataCache( String key )
  • {
  •    crossTabStore.dropAggregatedDataCache( key );
    
  • }
public Collection<CrossTabDataValue> getCrossTabDataValues( Collection<DataElementOperand> operands,
    Collection<Integer> periodIds, Collection<Integer> sourceIds, String key )
{

@@ -172,6 +181,12 @@

{
    return crossTabStore.getCrossTabDataValues( operands, periodIds, sourceId, key );
}
  • public Map<DataElementOperand, Double> getAggregatedDataCacheValue( Collection operands,
  •    int periodId, int sourceId, String key )
    
  • {
  •    return crossTabStore.getAggregatedDataCacheValue( operands, periodId, sourceId, key );
    
  • }
// -------------------------------------------------------------------------
// Supportive methods

=== modified file ‘dhis-2/dhis-services/dhis-service-datamart-default/src/main/java/org/hisp/dhis/datamart/crosstab/jdbc/CrossTabStore.java’

— dhis-2/dhis-services/dhis-service-datamart-default/src/main/java/org/hisp/dhis/datamart/crosstab/jdbc/CrossTabStore.java 2011-06-04 13:52:42 +0000

+++ dhis-2/dhis-services/dhis-service-datamart-default/src/main/java/org/hisp/dhis/datamart/crosstab/jdbc/CrossTabStore.java 2011-06-04 16:56:41 +0000

@@ -29,6 +29,7 @@

import java.util.Collection;

import java.util.List;

+import java.util.Map;

import org.hisp.dhis.dataelement.DataElementOperand;

import org.hisp.dhis.datamart.CrossTabDataValue;

@@ -40,8 +41,8 @@

public interface CrossTabStore

{

final String ID = CrossTabStore.class.getName();
  • final String TABLE_PREFIX = “datavaluecrosstab_”;
  • final String TABLE_PREFIX_TRIMMED = “datavaluecrosstabtrimmed_”;
  • final String CROSSTAB_TABLE_PREFIX = “crosstab_table_”;
  • final String AGGREGATEDDATA_CACHE_PREFIX = “aggregateddata_cache_”;
/**
 * Creates a crosstab table where the first column is the period identifier,

@@ -57,6 +58,10 @@

 */
void dropCrossTabTable( String key );
  • void createAggregatedDataCache( List operands, String key );
  • void dropAggregatedDataCache( String key );
/**
 * Gets all CrossTabDataValues for the given collection of period ids and source ids.
 *

@@ -78,4 +83,6 @@

 */
Collection<CrossTabDataValue> getCrossTabDataValues( Collection<DataElementOperand> operands, Collection<Integer> periodIds,
    int sourceId, String key );
  • Map<DataElementOperand, Double> getAggregatedDataCacheValue( Collection operands, int periodId, int sourceId, String key );

}

=== modified file ‘dhis-2/dhis-services/dhis-service-datamart-default/src/main/java/org/hisp/dhis/datamart/crosstab/jdbc/JDBCCrossTabStore.java’

— dhis-2/dhis-services/dhis-service-datamart-default/src/main/java/org/hisp/dhis/datamart/crosstab/jdbc/JDBCCrossTabStore.java 2011-06-04 13:52:42 +0000

+++ dhis-2/dhis-services/dhis-service-datamart-default/src/main/java/org/hisp/dhis/datamart/crosstab/jdbc/JDBCCrossTabStore.java 2011-06-04 16:56:41 +0000

@@ -33,7 +33,9 @@

import java.sql.SQLException;

import java.util.ArrayList;

import java.util.Collection;

+import java.util.HashMap;

import java.util.List;

+import java.util.Map;

import org.amplecode.quick.StatementHolder;

import org.amplecode.quick.StatementManager;

@@ -64,7 +66,7 @@

public void createCrossTabTable( List<DataElementOperand> operands, String key )
{
  •    final StringBuffer sql = new StringBuffer( "CREATE TABLE " + TABLE_PREFIX + key + " ( " );
    
  •    final StringBuffer sql = new StringBuffer( "CREATE TABLE " + CROSSTAB_TABLE_PREFIX + key + " ( " );
    
    sql.append( "periodid INTEGER NOT NULL, " );
    sql.append( "sourceid INTEGER NOT NULL, " );

@@ -78,14 +80,34 @@

    statementManager.getHolder().executeUpdate( sql.toString() );
}
public void dropCrossTabTable( String key )
{
  •    final String sql = "DROP TABLE IF EXISTS " + TABLE_PREFIX + key;
    
  •    statementManager.getHolder().executeUpdate( sql );
    
  • }
  •    statementManager.getHolder().executeUpdate( "DROP TABLE IF EXISTS " + CROSSTAB_TABLE_PREFIX + key );
    
  • }
  • public void createAggregatedDataCache( List operands, String key )
  • {
  •    final StringBuffer sql = new StringBuffer( "CREATE TABLE " + AGGREGATEDDATA_CACHE_PREFIX + key + " ( " );
    
  •    sql.append( "periodid INTEGER NOT NULL, " );
    
  •    sql.append( "sourceid INTEGER NOT NULL, " );
    
  •    for ( DataElementOperand operand : operands )
    
  •    {
    
  •        sql.append( operand.getColumnName() ).append( " DOUBLE, " );
    
  •    }
    
  •    sql.append( "PRIMARY KEY ( periodid, sourceid ) );" );
    
  •    statementManager.getHolder().executeUpdate( sql.toString() );
    
  • }
  • public void dropAggregatedDataCache( String key )
  • {
  •    statementManager.getHolder().executeUpdate( "DROP TABLE IF EXISTS " + AGGREGATEDDATA_CACHE_PREFIX + key );
    
  • }
// -------------------------------------------------------------------------
// CrossTabDataValue
// -------------------------------------------------------------------------

@@ -95,7 +117,7 @@

{
    final StatementHolder holder = statementManager.getHolder();
  •    final String sql = "SELECT * FROM " + TABLE_PREFIX + key + " AS c WHERE c.periodid IN (" +
    
  •    final String sql = "SELECT * FROM " + CROSSTAB_TABLE_PREFIX + key + " AS c WHERE c.periodid IN (" +
    
        getCommaDelimitedString( periodIds ) + ") AND c.sourceid IN (" + getCommaDelimitedString( sourceIds ) + ")";
    try

@@ -119,7 +141,7 @@

{
    final StatementHolder holder = statementManager.getHolder();
  •    final String sql = "SELECT * FROM " + TABLE_PREFIX + key + " AS c WHERE c.periodid IN (" +
    
  •    final String sql = "SELECT * FROM " + CROSSTAB_TABLE_PREFIX + key + " AS c WHERE c.periodid IN (" +
    
        getCommaDelimitedString( periodIds ) + ") AND c.sourceid = " + sourceId;
    try

@@ -138,6 +160,44 @@

    }
}
  • public Map<DataElementOperand, Double> getAggregatedDataCacheValue( Collection operands,
  •    int periodId, int sourceId, String key )
    
  • {
  •    final StatementHolder holder = statementManager.getHolder();
    
  •    final String sql = "SELECT * FROM " + AGGREGATEDDATA_CACHE_PREFIX + key + " AS a WHERE a.periodid = " + periodId + " AND a.sourceid = " + sourceId;
    
  •    try
    
  •    {
    
  •        final Map<DataElementOperand, Double> valueMap = new HashMap<DataElementOperand, Double>();
    
  •        final ResultSet resultSet = holder.getStatement().executeQuery( sql );
    
  •        if ( resultSet.next() )
    
  •        {
    
  •            for ( DataElementOperand operand : operands )
    
  •            {
    
  •                final Double columnValue = resultSet.getDouble( operand.getColumnName() );
    
  •                if ( columnValue != null )
    
  •                {
    
  •                    valueMap.put( operand, columnValue );
    
  •                }
    
  •            }
    
  •        }
    
  •        return valueMap;
    
  •    }
    
  •    catch ( SQLException ex )
    
  •    {
    
  •        throw new RuntimeException( "Failed to get Map", ex );
    
  •    }
    
  •    finally
    
  •    {
    
  •        holder.close();
    
  •    }
    
  • }
// -------------------------------------------------------------------------
// Supportive methods
// -------------------------------------------------------------------------

@@ -156,9 +216,7 @@

        for ( DataElementOperand operand : operands )
        {
  •            final String columnName = operand.getColumnName();
    
  •            final String columnValue = resultSet.getString( columnName );
    
  •            final String columnValue = resultSet.getString( operand.getColumnName() );
    
            if ( columnValue != null )
            {

=== modified file ‘dhis-2/dhis-services/dhis-service-datamart-default/src/main/java/org/hisp/dhis/datamart/dataelement/DataElementDataMart.java’

— dhis-2/dhis-services/dhis-service-datamart-default/src/main/java/org/hisp/dhis/datamart/dataelement/DataElementDataMart.java 2011-06-03 21:33:10 +0000

+++ dhis-2/dhis-services/dhis-service-datamart-default/src/main/java/org/hisp/dhis/datamart/dataelement/DataElementDataMart.java 2011-06-05 16:40:02 +0000

@@ -30,6 +30,7 @@

import java.util.Collection;

import org.hisp.dhis.dataelement.DataElementOperand;

+import org.hisp.dhis.datamart.DataElementOperandList;

import org.hisp.dhis.organisationunit.OrganisationUnit;

import org.hisp.dhis.period.Period;

@@ -39,5 +40,5 @@

public interface DataElementDataMart

{

int exportDataValues( Collection<DataElementOperand> operands, Collection<Period> periods,
  •    Collection<OrganisationUnit> organisationUnits, String key );
    
  •    Collection<OrganisationUnit> organisationUnits, DataElementOperandList operandList, String key );
    

}

=== modified file ‘dhis-2/dhis-services/dhis-service-datamart-default/src/main/java/org/hisp/dhis/datamart/dataelement/DefaultDataElementDataMart.java’

— dhis-2/dhis-services/dhis-service-datamart-default/src/main/java/org/hisp/dhis/datamart/dataelement/DefaultDataElementDataMart.java 2011-06-03 21:33:10 +0000

+++ dhis-2/dhis-services/dhis-service-datamart-default/src/main/java/org/hisp/dhis/datamart/dataelement/DefaultDataElementDataMart.java 2011-06-05 16:40:02 +0000

@@ -28,6 +28,7 @@

*/

import static org.hisp.dhis.system.util.MathUtils.getRounded;

+import static org.hisp.dhis.datamart.crosstab.jdbc.CrossTabStore.*;

import java.util.Collection;

import java.util.HashMap;

@@ -40,9 +41,11 @@

import org.apache.commons.logging.LogFactory;

import org.hisp.dhis.aggregation.AggregatedDataValue;

import org.hisp.dhis.dataelement.DataElementOperand;

+import org.hisp.dhis.datamart.DataElementOperandList;

import org.hisp.dhis.datamart.aggregation.cache.AggregationCache;

import org.hisp.dhis.datamart.aggregation.dataelement.DataElementAggregator;

import org.hisp.dhis.jdbc.batchhandler.AggregatedDataValueBatchHandler;

+import org.hisp.dhis.jdbc.batchhandler.GenericBatchHandler;

import org.hisp.dhis.organisationunit.OrganisationUnit;

import org.hisp.dhis.organisationunit.OrganisationUnitHierarchy;

import org.hisp.dhis.organisationunit.OrganisationUnitService;

@@ -75,7 +78,14 @@

{
    this.batchHandlerFactory = batchHandlerFactory;
}
  • private BatchHandlerFactory inMemoryBatchHandlerFactory;
  • public void setInMemoryBatchHandlerFactory( BatchHandlerFactory inMemoryBatchHandlerFactory )
  • {
  •    this.inMemoryBatchHandlerFactory = inMemoryBatchHandlerFactory;
    
  • }
private AggregationCache aggregationCache;
public void setAggregationCache( AggregationCache aggregationCache )

@@ -123,15 +133,17 @@

// -------------------------------------------------------------------------
public int exportDataValues( final Collection<DataElementOperand> operands, final Collection<Period> periods,
  •    final Collection<OrganisationUnit> organisationUnits, String key )
    
  •    final Collection<OrganisationUnit> organisationUnits, DataElementOperandList operandList, String key )
    
{
    final BatchHandler<AggregatedDataValue> batchHandler = batchHandlerFactory.createBatchHandler( AggregatedDataValueBatchHandler.class ).init();
  •    final BatchHandler<Object> cacheHandler = inMemoryBatchHandlerFactory.createBatchHandler( GenericBatchHandler.class ).setTableName( AGGREGATEDDATA_CACHE_PREFIX + key ).init();
    
    final OrganisationUnitHierarchy hierarchy = organisationUnitService.getOrganisationUnitHierarchy().prepareChildren( organisationUnits );
    int count = 0;
  •    final AggregatedDataValue value = new AggregatedDataValue();
    
  •    final AggregatedDataValue aggregatedValue = new AggregatedDataValue();
    
    for ( final Period period : periods )
    {

@@ -143,6 +155,8 @@

        for ( final OrganisationUnit unit : organisationUnits )
        {
  •            operandList.init( period, unit );
    
            final int level = aggregationCache.getLevelOfOrganisationUnit( unit.getId() );
            final Map<DataElementOperand, Double> valueMap = new HashMap<DataElementOperand, Double>();

@@ -153,22 +167,33 @@

            valueMap.putAll( sumBoolAggregator.getAggregatedValues( sumBoolOperands, period, unit, level, hierarchy, key ) );
            valueMap.putAll( averageBoolAggregator.getAggregatedValues( averageBoolOperands, period, unit, level, hierarchy, key ) );
  •            //TODO check size of value map
    
            for ( Entry<DataElementOperand, Double> entry : valueMap.entrySet() )
            {
  •                value.clear();
    
  •                value.setDataElementId( entry.getKey().getDataElementId() );
    
  •                value.setCategoryOptionComboId( entry.getKey().getOptionComboId() );
    
  •                value.setPeriodId( period.getId() );
    
  •                value.setPeriodTypeId( period.getPeriodType().getId() );
    
  •                value.setOrganisationUnitId( unit.getId() );
    
  •                value.setLevel( level );
    
  •                value.setValue( getRounded( entry.getValue(), DECIMALS ) );
    
  •                batchHandler.addObject( value );
    
  •                aggregatedValue.clear();
    
  •                final double value = getRounded( entry.getValue(), DECIMALS );
    
  •                aggregatedValue.setDataElementId( entry.getKey().getDataElementId() );
    
  •                aggregatedValue.setCategoryOptionComboId( entry.getKey().getOptionComboId() );
    
  •                aggregatedValue.setPeriodId( period.getId() );
    
  •                aggregatedValue.setPeriodTypeId( period.getPeriodType().getId() );
    
  •                aggregatedValue.setOrganisationUnitId( unit.getId() );
    
  •                aggregatedValue.setLevel( level );
    
  •                aggregatedValue.setValue( value );
    
  •                batchHandler.addObject( aggregatedValue );
    
  •                operandList.addValue( entry.getKey(), value );
    
                count++;
            }
  •            if ( operandList.hasValues() )
    
  •            {
    
  •                cacheHandler.addObject( operandList.getList() );
    
  •            }
    
        }
        log.debug( "Exported data values for period: " + period );

@@ -176,6 +201,8 @@

    batchHandler.flush();
  •    cacheHandler.flush();
    
    return count;
}

}

=== modified file ‘dhis-2/dhis-services/dhis-service-datamart-default/src/main/java/org/hisp/dhis/datamart/engine/DefaultDataMartEngine.java’

— dhis-2/dhis-services/dhis-service-datamart-default/src/main/java/org/hisp/dhis/datamart/engine/DefaultDataMartEngine.java 2011-06-04 13:52:42 +0000

+++ dhis-2/dhis-services/dhis-service-datamart-default/src/main/java/org/hisp/dhis/datamart/engine/DefaultDataMartEngine.java 2011-06-05 16:45:59 +0000

@@ -30,6 +30,7 @@

import java.util.ArrayList;

import java.util.Collection;

import java.util.HashSet;

+import java.util.List;

import java.util.Set;

import org.apache.commons.logging.Log;

@@ -40,6 +41,7 @@

import org.hisp.dhis.dataelement.DataElementCategoryService;

import org.hisp.dhis.dataelement.DataElementOperand;

import org.hisp.dhis.dataelement.DataElementService;

+import org.hisp.dhis.datamart.DataElementOperandList;

import org.hisp.dhis.datamart.aggregation.cache.AggregationCache;

import org.hisp.dhis.datamart.crosstab.CrossTabService;

import org.hisp.dhis.datamart.dataelement.DataElementDataMart;

@@ -54,7 +56,6 @@

import org.hisp.dhis.system.util.ConversionUtils;

import org.hisp.dhis.system.util.TimeUtils;

import org.springframework.transaction.annotation.Transactional;

-import org.springframework.util.CollectionUtils;

/**

  • @author Lars Helge Overland

@@ -181,7 +182,7 @@

    // ---------------------------------------------------------------------
    Collection<DataElementOperand> dataElementOperands = categoryService.getOperands( dataElements );
  •    Collection<DataElementOperand> indicatorOperands = categoryService.populateOperands( getOperandsInIndicators( indicators ) );
    
  •    List<DataElementOperand> indicatorOperands = new ArrayList<DataElementOperand>( categoryService.populateOperands( getOperandsInIndicators( indicators ) ) );
    
    Set<DataElementOperand> allOperands = new HashSet<DataElementOperand>();
    allOperands.addAll( dataElementOperands );

@@ -195,13 +196,12 @@

    allOperands = crossTabService.getOperandsWithData( allOperands );
  •    dataElementOperands.retainAll( allOperands );
    
    indicatorOperands.retainAll( allOperands );
    [log.info](http://log.info)( "Number of operands with data: " + allOperands.size() + ", "+ TimeUtils.getHMS() );
    // ---------------------------------------------------------------------
  •    // Create and trim crosstabtable
    
  •    // Create crosstabtable
    
    // ---------------------------------------------------------------------
    state.setMessage( "crosstabulating_data" );

@@ -211,14 +211,19 @@

    String key = crossTabService.populateCrossTabTable( new ArrayList<DataElementOperand>( allOperands ), intersectingPeriodIds, childrenIds );
  •    if ( CollectionUtils.isEmpty( allOperands ) )
    
  •    {
    
  •        return 0;
    
  •    }
    
    [log.info](http://log.info)( "Populated crosstab table: " + TimeUtils.getHMS() );
    // ---------------------------------------------------------------------
  •    // Create aggregated data cache
    
  •    // ---------------------------------------------------------------------
    
  •    DataElementOperandList operandList = new DataElementOperandList( indicatorOperands );
    
  •    crossTabService.createAggregatedDataCache( indicatorOperands, key );
    
  •    [log.info](http://log.info)( "Created aggregated data cache" );
    
  •    // ---------------------------------------------------------------------
    
    // Drop potential indexes
    // ---------------------------------------------------------------------

@@ -246,11 +251,11 @@

    state.setMessage( "exporting_data_for_data_elements" );
  •    if ( dataElementOperands.size() > 0 )
    
  •    if ( allOperands.size() > 0 )
    
    {
  •        count += dataElementDataMart.exportDataValues( dataElementOperands, periods, organisationUnits, key );
    
  •        count += dataElementDataMart.exportDataValues( allOperands, periods, organisationUnits, operandList, key );
    
  •        [log.info](http://log.info)( "Exported values for data element operands (" + dataElementOperands.size() + "): " + TimeUtils.getHMS() );
    
  •        [log.info](http://log.info)( "Exported values for data element operands (" + allOperands.size() + "): " + TimeUtils.getHMS() );
    
    }
    // ---------------------------------------------------------------------

@@ -267,12 +272,14 @@

    }
    // ---------------------------------------------------------------------
  •    // Drop crosstab tables
    
  •    // Drop crosstab table and aggregated data cache
    
    // ---------------------------------------------------------------------
  •    crossTabService.dropCrossTabTable( key );
    
  •    [log.info](http://log.info)( "Dropped crosstab table: " + TimeUtils.getHMS() );
    
  •    crossTabService.dropCrossTabTable( key ); //TODO move before indicator export
    
  •    crossTabService.dropAggregatedDataCache( key );
    
  •    [log.info](http://log.info)( "Dropped crosstab table and aggregated data cache: " + TimeUtils.getHMS() );
    
    // ---------------------------------------------------------------------
    // Create potential indexes

=== modified file ‘dhis-2/dhis-services/dhis-service-datamart-default/src/main/java/org/hisp/dhis/datamart/indicator/DefaultIndicatorDataMart.java’

— dhis-2/dhis-services/dhis-service-datamart-default/src/main/java/org/hisp/dhis/datamart/indicator/DefaultIndicatorDataMart.java 2011-06-03 21:33:10 +0000

+++ dhis-2/dhis-services/dhis-service-datamart-default/src/main/java/org/hisp/dhis/datamart/indicator/DefaultIndicatorDataMart.java 2011-06-05 16:40:02 +0000

@@ -28,12 +28,11 @@

*/

import static org.hisp.dhis.options.SystemSettingManager.KEY_OMIT_INDICATORS_ZERO_NUMERATOR_DATAMART;

+import static org.hisp.dhis.system.util.DateUtils.daysBetween;

import static org.hisp.dhis.system.util.MathUtils.calculateExpression;

import static org.hisp.dhis.system.util.MathUtils.getRounded;

-import static org.hisp.dhis.system.util.DateUtils.daysBetween;

import java.util.Collection;

-import java.util.HashMap;

import java.util.Map;

import org.amplecode.quick.BatchHandler;

@@ -43,14 +42,12 @@

import org.hisp.dhis.aggregation.AggregatedIndicatorValue;

import org.hisp.dhis.dataelement.DataElementOperand;

import org.hisp.dhis.datamart.aggregation.cache.AggregationCache;

-import org.hisp.dhis.datamart.aggregation.dataelement.DataElementAggregator;

+import org.hisp.dhis.datamart.crosstab.CrossTabService;

import org.hisp.dhis.expression.ExpressionService;

import org.hisp.dhis.indicator.Indicator;

import org.hisp.dhis.jdbc.batchhandler.AggregatedIndicatorValueBatchHandler;

import org.hisp.dhis.options.SystemSettingManager;

import org.hisp.dhis.organisationunit.OrganisationUnit;

-import org.hisp.dhis.organisationunit.OrganisationUnitHierarchy;

-import org.hisp.dhis.organisationunit.OrganisationUnitService;

import org.hisp.dhis.period.Period;

import org.hisp.dhis.period.PeriodType;

import org.hisp.dhis.system.util.DateUtils;

@@ -72,40 +69,12 @@

// Dependencies
// -------------------------------------------------------------------------
  • private OrganisationUnitService organisationUnitService;
  • public void setOrganisationUnitService( OrganisationUnitService organisationUnitService )
  • {
  •    this.organisationUnitService = organisationUnitService;
    
  • }
private ExpressionService expressionService;
public void setExpressionService( ExpressionService expressionService )
{
    this.expressionService = expressionService;
}
  • private DataElementAggregator sumIntAggregator;
  • public void setSumIntAggregator( DataElementAggregator sumIntDataElementAggregator )
  • {
  •    this.sumIntAggregator = sumIntDataElementAggregator;
    
  • }
  • private DataElementAggregator averageIntAggregator;
  • public void setAverageIntAggregator( DataElementAggregator averageIntDataElementAggregator )
  • {
  •    this.averageIntAggregator = averageIntDataElementAggregator;
    
  • }
  • private DataElementAggregator averageIntSingleValueAggregator;
  • public void setAverageIntSingleValueAggregator( DataElementAggregator averageIntSingleValueAggregator )
  • {
  •    this.averageIntSingleValueAggregator = averageIntSingleValueAggregator;
    
  • }
private AggregationCache aggregationCache;

@@ -121,13 +90,20 @@

    this.systemSettingManager = systemSettingManager;
}
  • private CrossTabService crossTabService;
  • public void setCrossTabService( CrossTabService crossTabService )
  • {
  •    this.crossTabService = crossTabService;
    
  • }
private BatchHandlerFactory batchHandlerFactory;
public void setBatchHandlerFactory( BatchHandlerFactory batchHandlerFactory )
{
    this.batchHandlerFactory = batchHandlerFactory;
}
// -------------------------------------------------------------------------
// IndicatorDataMart implementation
// -------------------------------------------------------------------------

@@ -137,15 +113,8 @@

{
    final BatchHandler<AggregatedIndicatorValue> batchHandler = batchHandlerFactory.createBatchHandler( AggregatedIndicatorValueBatchHandler.class ).init();
  •    final OrganisationUnitHierarchy hierarchy = organisationUnitService.getOrganisationUnitHierarchy().prepareChildren( organisationUnits );
    
    int count = 0;
  •    double annualizationFactor = 0.0;
    
  •    double factor = 0.0;
    
  •    double aggregatedValue = 0.0;
    
  •    double annualizedFactor = 0.0;
    
    final boolean omitZeroNumerator = (Boolean) systemSettingManager.getSystemSetting( KEY_OMIT_INDICATORS_ZERO_NUMERATOR_DATAMART, false );
    final AggregatedIndicatorValue indicatorValue = new AggregatedIndicatorValue();

@@ -156,57 +125,47 @@

        final PeriodType periodType = period.getPeriodType();
  •        final Collection<DataElementOperand> sumOperands = sumIntAggregator.filterOperands( operands, periodType );
    
  •        final Collection<DataElementOperand> averageOperands = averageIntAggregator.filterOperands( operands, periodType );
    
  •        final Collection<DataElementOperand> averageSingleValueOperands = averageIntSingleValueAggregator.filterOperands( operands, periodType );
    
        for ( final OrganisationUnit unit : organisationUnits )
        {
            final int level = aggregationCache.getLevelOfOrganisationUnit( unit.getId() );
  •            final Map<DataElementOperand, Double> sumIntValueMap = sumIntAggregator.getAggregatedValues( sumOperands, period, unit, level, hierarchy, key );
    
  •            final Map<DataElementOperand, Double> averageIntValueMap = averageIntAggregator.getAggregatedValues( averageOperands, period, unit, level, hierarchy, key );
    
  •            final Map<DataElementOperand, Double> averageIntSingleValueMap = averageIntSingleValueAggregator.getAggregatedValues( averageSingleValueOperands, period, unit, level, hierarchy, key );
    
  •            final Map<DataElementOperand, Double> valueMap = new HashMap<DataElementOperand, Double>( sumIntValueMap );
    
  •            valueMap.putAll( averageIntValueMap );
    
  •            valueMap.putAll( averageIntSingleValueMap );
    
  •            for ( final Indicator indicator : indicators )
    
  •            {
    
  •                final double numeratorValue = calculateExpression( expressionService.generateExpression( indicator.getExplodedNumerator(), valueMap, days ) );
    
  •                final double denominatorValue = calculateExpression( expressionService.generateExpression( indicator.getExplodedDenominator(), valueMap, days ) );
    
  •                // ---------------------------------------------------------
    
  •                // AggregatedIndicatorValue
    
  •                // ---------------------------------------------------------
    
  •                if ( denominatorValue != 0 && !( omitZeroNumerator && numeratorValue == 0 ) )
    
  •            final Map<DataElementOperand, Double> valueMap = crossTabService.getAggregatedDataCacheValue( operands, period.getId(), unit.getId(), key );
    
  •            if ( valueMap.size() > 0 )
    
  •            {
    
  •                for ( final Indicator indicator : indicators )
    
                {
  •                    annualizationFactor = DateUtils.getAnnualizationFactor( indicator, period.getStartDate(), period.getEndDate() );
    
  •                    factor = indicator.getIndicatorType().getFactor();
    
  •                    aggregatedValue = ( numeratorValue / denominatorValue ) * factor * annualizationFactor;
    
  •                    annualizedFactor = factor * annualizationFactor;
    
  •                    indicatorValue.clear();
    
  •                    indicatorValue.setIndicatorId( indicator.getId() );
    
  •                    indicatorValue.setPeriodId( period.getId() );
    
  •                    indicatorValue.setPeriodTypeId( periodType.getId() );
    
  •                    indicatorValue.setOrganisationUnitId( unit.getId() );
    
  •                    indicatorValue.setLevel( level );
    
  •                    indicatorValue.setAnnualized( getAnnualizationString( indicator.getAnnualized() ) );
    
  •                    indicatorValue.setFactor( annualizedFactor);
    
  •                    indicatorValue.setValue( getRounded( aggregatedValue, DECIMALS ) );
    
  •                    indicatorValue.setNumeratorValue( getRounded( numeratorValue, DECIMALS ) );
    
  •                    indicatorValue.setDenominatorValue( getRounded( denominatorValue, DECIMALS ) );
    
  •                    batchHandler.addObject( indicatorValue );
    
  •                    count++;
    
  •                    final double numeratorValue = calculateExpression( expressionService.generateExpression( indicator.getExplodedNumerator(), valueMap, days ) );
    
  •                    final double denominatorValue = calculateExpression( expressionService.generateExpression( indicator.getExplodedDenominator(), valueMap, days ) );
    
  •                    // ---------------------------------------------------------
    
  •                    // AggregatedIndicatorValue
    
  •                    // ---------------------------------------------------------
    
  •                    if ( denominatorValue != 0 && !( omitZeroNumerator && numeratorValue == 0 ) )
    
  •                    {
    
  •                        final double annualizationFactor = DateUtils.getAnnualizationFactor( indicator, period.getStartDate(), period.getEndDate() );
    
  •                        final double factor = indicator.getIndicatorType().getFactor();
    
  •                        final double aggregatedValue = ( numeratorValue / denominatorValue ) * factor * annualizationFactor;
    
  •                        final double annualizedFactor = factor * annualizationFactor;
    
  •                        indicatorValue.clear();
    
  •                        indicatorValue.setIndicatorId( indicator.getId() );
    
  •                        indicatorValue.setPeriodId( period.getId() );
    
  •                        indicatorValue.setPeriodTypeId( periodType.getId() );
    
  •                        indicatorValue.setOrganisationUnitId( unit.getId() );
    
  •                        indicatorValue.setLevel( level );
    
  •                        indicatorValue.setAnnualized( getAnnualizationString( indicator.getAnnualized() ) );
    
  •                        indicatorValue.setFactor( annualizedFactor);
    
  •                        indicatorValue.setValue( getRounded( aggregatedValue, DECIMALS ) );
    
  •                        indicatorValue.setNumeratorValue( getRounded( numeratorValue, DECIMALS ) );
    
  •                        indicatorValue.setDenominatorValue( getRounded( denominatorValue, DECIMALS ) );
    
  •                        batchHandler.addObject( indicatorValue );
    
  •                        count++;
    
  •                    }
    
                }
            }
        }

=== modified file ‘dhis-2/dhis-services/dhis-service-datamart-default/src/main/resources/META-INF/dhis/beans.xml’

— dhis-2/dhis-services/dhis-service-datamart-default/src/main/resources/META-INF/dhis/beans.xml 2011-06-03 21:33:10 +0000

+++ dhis-2/dhis-services/dhis-service-datamart-default/src/main/resources/META-INF/dhis/beans.xml 2011-06-05 16:40:02 +0000

@@ -140,6 +140,7 @@

<property name="organisationUnitService"
  ref="org.hisp.dhis.organisationunit.OrganisationUnitService"/>
<property name="batchHandlerFactory" ref="batchHandlerFactory"/>
  •   <property name="inMemoryBatchHandlerFactory" ref="inMemoryBatchHandlerFactory"/>
    
<property name="aggregationCache"
  ref="org.hisp.dhis.datamart.aggregation.cache.AggregationCache"/>
<property name="sumIntAggregator"

@@ -158,20 +159,14 @@

<bean id=“org.hisp.dhis.datamart.indicator.IndicatorDataMart”

class="org.hisp.dhis.datamart.indicator.DefaultIndicatorDataMart">
  • <property name=“organisationUnitService”
  •  ref="org.hisp.dhis.organisationunit.OrganisationUnitService"/>
    
   <property name="expressionService"
     ref="org.hisp.dhis.expression.ExpressionService"/>
  • <property name=“sumIntAggregator”
  •  ref="org.hisp.dhis.datamart.aggregation.dataelement.SumIntAggregator"/>
    
  • <property name=“averageIntAggregator”
  •  ref="org.hisp.dhis.datamart.aggregation.dataelement.AverageIntAggregator"/>
    
  •   <property name="averageIntSingleValueAggregator"
    
  •  ref="org.hisp.dhis.datamart.aggregation.dataelement.AverageIntSingleValueAggregator"/>
    
<property name="aggregationCache"
  ref="org.hisp.dhis.datamart.aggregation.cache.AggregationCache"/>
<property name="systemSettingManager"
  ref="org.hisp.dhis.options.SystemSettingManager"/>
  • <property name=“crossTabService”
  •  ref="org.hisp.dhis.datamart.crosstab.CrossTabService"/>
    
   <property name="batchHandlerFactory" ref="batchHandlerFactory"/>

=== modified file ‘dhis-2/dhis-services/dhis-service-datamart-default/src/test/java/org/hisp/dhis/datamart/crosstab/CrossTabServiceTest.java’

— dhis-2/dhis-services/dhis-service-datamart-default/src/test/java/org/hisp/dhis/datamart/crosstab/CrossTabServiceTest.java 2011-06-04 13:52:42 +0000

+++ dhis-2/dhis-services/dhis-service-datamart-default/src/test/java/org/hisp/dhis/datamart/crosstab/CrossTabServiceTest.java 2011-06-04 16:56:41 +0000

@@ -37,7 +37,11 @@

import java.util.HashSet;

import java.util.Iterator;

import java.util.List;

+import java.util.Map;

+import org.amplecode.quick.BatchHandler;

+import org.amplecode.quick.BatchHandlerFactory;

+import org.apache.commons.lang.RandomStringUtils;

import org.hisp.dhis.DhisTest;

import org.hisp.dhis.dataelement.DataElement;

import org.hisp.dhis.dataelement.DataElementCategory;

@@ -48,7 +52,9 @@

import org.hisp.dhis.dataelement.DataElementOperand;

import org.hisp.dhis.dataelement.DataElementService;

import org.hisp.dhis.datamart.CrossTabDataValue;

+import org.hisp.dhis.datamart.crosstab.jdbc.CrossTabStore;

import org.hisp.dhis.datavalue.DataValueService;

+import org.hisp.dhis.jdbc.batchhandler.GenericBatchHandler;

import org.hisp.dhis.organisationunit.OrganisationUnit;

import org.hisp.dhis.organisationunit.OrganisationUnitService;

import org.hisp.dhis.period.Period;

@@ -65,6 +71,8 @@

{

private CrossTabService crossTabService;
  • private BatchHandlerFactory batchHandlerFactory;
private Iterator<Period> generatedPeriods;
private List<DataElementOperand> operands;

@@ -80,6 +88,8 @@

{
    crossTabService = (CrossTabService) getBean( CrossTabService.ID );
  •    batchHandlerFactory = (BatchHandlerFactory) getBean( "inMemoryBatchHandlerFactory" );
    
    categoryService = (DataElementCategoryService) getBean( DataElementCategoryService.ID );
    dataElementService = (DataElementService) getBean( DataElementService.ID );

@@ -203,4 +213,36 @@

        }
    }
}
  • @Test
  • public void testPopulateAggregatedDataCache()
  • {
  •    String key = RandomStringUtils.randomAlphanumeric( 8 );
    
  •    crossTabService.createAggregatedDataCache( operands, key );
    
  •    BatchHandler<Object> batchHandler = batchHandlerFactory.createBatchHandler( GenericBatchHandler.class ).
    
  •        setTableName( CrossTabStore.AGGREGATEDDATA_CACHE_PREFIX + key ).init();
    
  •    List<Object> valueList = new ArrayList<Object>( operands.size() + 2 );
    
  •    valueList.add( 1 );
    
  •    valueList.add( 1 );
    
  •    for ( int i = 0; i < operands.size(); i++ )
    
  •    {
    
  •        valueList.add( 10.0 );
    
  •    }
    
  •    batchHandler.addObject( valueList );
    
  •    batchHandler.flush();
    
  •    Map<DataElementOperand, Double> valueMap = crossTabService.getAggregatedDataCacheValue( operands, 1, 1, key );
    
  •    for ( DataElementOperand operand : valueMap.keySet() )
    
  •    {
    
  •        assertNotNull( valueMap.get( operand ) );
    
  •        assertEquals( 10.0, valueMap.get( operand ) );
    
  •    }
    
  • }

}

=== modified file ‘dhis-2/dhis-services/dhis-service-datamart-default/src/test/java/org/hisp/dhis/datamart/crosstab/CrossTabStoreTest.java’

— dhis-2/dhis-services/dhis-service-datamart-default/src/test/java/org/hisp/dhis/datamart/crosstab/CrossTabStoreTest.java 2011-01-10 10:06:18 +0000

+++ dhis-2/dhis-services/dhis-service-datamart-default/src/test/java/org/hisp/dhis/datamart/crosstab/CrossTabStoreTest.java 2011-06-04 16:56:41 +0000

@@ -51,4 +51,12 @@

    crossTabStore.dropCrossTabTable( key );
}
  • @Test
  • public void testDropAggregatedDataCache()
  • {
  •    crossTabStore.createAggregatedDataCache( operands, key );
    
  •    crossTabStore.dropAggregatedDataCache( key );
    
  • }

}


Mailing list: https://launchpad.net/~dhis2-devs

Post to : dhis2-devs@lists.launchpad.net

Unsubscribe : https://launchpad.net/~dhis2-devs

More help : https://help.launchpad.net/ListHelp

Or, cloud based systems, which tend to have plenty of disk, but little memory.

···

2011/6/6 Lars Helge Øverland <larshelge@gmail.com>:

On Mon, Jun 6, 2011 at 7:22 AM, Jason Pickering > <jason.p.pickering@gmail.com> wrote:

Sounds good. Going forward it might make sense to offer this as part
of the aggregation strategy, namely whether to use an in-memory DB (if
you have plenty of memory) or disk (if you do not).

Yes I agree.. In my tests this db never used more than ~ 400 MB of memory so
should not be a problem on server setups, but it might be on standalone
installations on weaker computers..

On Sun, Jun 5, 2011 at 6:47 PM, <noreply@launchpad.net> wrote:
> Merge authors:
> Lars Helge Øverland (larshelge)
> ------------------------------------------------------------
> revno: 3841 [merge]
> committer: Lars Helge Overland <larshelge@gmail.com>
> branch nick: dhis2
> timestamp: Sun 2011-06-05 18:45:59 +0200
> message:
> Data mart: caching aggregated data values in a denormalized in-memory
> db table during dataelement export process and reading from that cache
> during indicator export. Total processing time on my complete export
> benchmark kenya database down 63 %.
> added:
>
> dhis-2/dhis-services/dhis-service-datamart-default/src/main/java/org/hisp/dhis/datamart/DataElementOperandList.java
> modified:
>
> dhis-2/dhis-services/dhis-service-datamart-default/src/main/java/org/hisp/dhis/datamart/crosstab/CrossTabService.java
>
> dhis-2/dhis-services/dhis-service-datamart-default/src/main/java/org/hisp/dhis/datamart/crosstab/DefaultCrossTabService.java
>
> dhis-2/dhis-services/dhis-service-datamart-default/src/main/java/org/hisp/dhis/datamart/crosstab/jdbc/CrossTabStore.java
>
> dhis-2/dhis-services/dhis-service-datamart-default/src/main/java/org/hisp/dhis/datamart/crosstab/jdbc/JDBCCrossTabStore.java
>
> dhis-2/dhis-services/dhis-service-datamart-default/src/main/java/org/hisp/dhis/datamart/dataelement/DataElementDataMart.java
>
> dhis-2/dhis-services/dhis-service-datamart-default/src/main/java/org/hisp/dhis/datamart/dataelement/DefaultDataElementDataMart.java
>
> dhis-2/dhis-services/dhis-service-datamart-default/src/main/java/org/hisp/dhis/datamart/engine/DefaultDataMartEngine.java
>
> dhis-2/dhis-services/dhis-service-datamart-default/src/main/java/org/hisp/dhis/datamart/indicator/DefaultIndicatorDataMart.java
>
> dhis-2/dhis-services/dhis-service-datamart-default/src/main/resources/META-INF/dhis/beans.xml
>
> dhis-2/dhis-services/dhis-service-datamart-default/src/test/java/org/hisp/dhis/datamart/crosstab/CrossTabServiceTest.java
>
> dhis-2/dhis-services/dhis-service-datamart-default/src/test/java/org/hisp/dhis/datamart/crosstab/CrossTabStoreTest.java
>
>
> --
> lp:dhis2
> https://code.launchpad.net/~dhis2-devs-core/dhis2/trunk
>
> Your team DHIS 2 developers is subscribed to branch lp:dhis2.
> To unsubscribe from this branch go to
> OpenID transaction in progress
>
> === added file
> 'dhis-2/dhis-services/dhis-service-datamart-default/src/main/java/org/hisp/dhis/datamart/DataElementOperandList.java'
> ---
> dhis-2/dhis-services/dhis-service-datamart-default/src/main/java/org/hisp/dhis/datamart/DataElementOperandList.java
> 1970-01-01 00:00:00 +0000
> +++
> dhis-2/dhis-services/dhis-service-datamart-default/src/main/java/org/hisp/dhis/datamart/DataElementOperandList.java
> 2011-06-05 16:40:42 +0000
> @@ -0,0 +1,63 @@
> +package org.hisp.dhis.datamart;
> +
> +import java.util.Arrays;
> +import java.util.List;
> +
> +import org.hisp.dhis.dataelement.DataElementOperand;
> +import org.hisp.dhis.organisationunit.OrganisationUnit;
> +import org.hisp.dhis.period.Period;
> +
> +public class DataElementOperandList
> +{
> + private List<DataElementOperand> operands;
> +
> + private Object[] valueList;
> +
> + private boolean hasValues;
> +
> + public DataElementOperandList( List<DataElementOperand> operands )
> + {
> + this.operands = operands;
> + }
> +
> + public void init( Period period, OrganisationUnit unit )
> + {
> + this.hasValues = false;
> +
> + if ( valid() )
> + {
> + this.valueList = new Object[operands.size() + 2];
> + this.valueList[0] = period.getId();
> + this.valueList[1] = unit.getId();
> + }
> + }
> +
> + public void addValue( DataElementOperand operand, Double value )
> + {
> + if ( valid() )
> + {
> + final int index = operands.indexOf( operand );
> +
> + if ( index != -1 && value != null )
> + {
> + this.valueList[index + 2] = value;
> + this.hasValues = true;
> + }
> + }
> + }
> +
> + public List<Object> getList()
> + {
> + return valid() ? Arrays.asList( this.valueList ) : null;
> + }
> +
> + public boolean valid()
> + {
> + return operands != null && operands.size() > 0;
> + }
> +
> + public boolean hasValues()
> + {
> + return hasValues;
> + }
> +}
>
> === modified file
> 'dhis-2/dhis-services/dhis-service-datamart-default/src/main/java/org/hisp/dhis/datamart/crosstab/CrossTabService.java'
> ---
> dhis-2/dhis-services/dhis-service-datamart-default/src/main/java/org/hisp/dhis/datamart/crosstab/CrossTabService.java
> 2011-06-04 13:52:42 +0000
> +++
> dhis-2/dhis-services/dhis-service-datamart-default/src/main/java/org/hisp/dhis/datamart/crosstab/CrossTabService.java
> 2011-06-04 16:56:41 +0000
> @@ -29,6 +29,7 @@
>
> import java.util.Collection;
> import java.util.List;
> +import java.util.Map;
> import java.util.Set;
>
> import org.hisp.dhis.dataelement.DataElementOperand;
> @@ -67,6 +68,10 @@
> */
> void dropCrossTabTable( String key );
>
> + void createAggregatedDataCache( List<DataElementOperand> operands,
> String key );
> +
> + void dropAggregatedDataCache( String key );
> +
> /**
> * Gets all CrossTabDataValues for the given collection of period
> ids and source ids.
> *
> @@ -88,4 +93,7 @@
> */
> Collection<CrossTabDataValue> getCrossTabDataValues(
> Collection<DataElementOperand> operands, Collection<Integer> periodIds,
> int sourceId, String key );
> +
> + Map<DataElementOperand, Double> getAggregatedDataCacheValue(
> Collection<DataElementOperand> operands,
> + int periodId, int sourceId, String key );
> }
>
> === modified file
> 'dhis-2/dhis-services/dhis-service-datamart-default/src/main/java/org/hisp/dhis/datamart/crosstab/DefaultCrossTabService.java'
> ---
> dhis-2/dhis-services/dhis-service-datamart-default/src/main/java/org/hisp/dhis/datamart/crosstab/DefaultCrossTabService.java
> 2011-06-04 13:52:42 +0000
> +++
> dhis-2/dhis-services/dhis-service-datamart-default/src/main/java/org/hisp/dhis/datamart/crosstab/DefaultCrossTabService.java
> 2011-06-04 16:56:41 +0000
> @@ -106,9 +106,8 @@
> crossTabStore.dropCrossTabTable( key );
> crossTabStore.createCrossTabTable( operands, key );
>
> - final BatchHandler<Object> batchHandler =
> batchHandlerFactory.createBatchHandler( GenericBatchHandler.class );
> - batchHandler.setTableName( CrossTabStore.TABLE_PREFIX + key
> );
> - batchHandler.init();
> + final BatchHandler<Object> batchHandler =
> batchHandlerFactory.createBatchHandler( GenericBatchHandler.class ).
> + setTableName( CrossTabStore.CROSSTAB_TABLE_PREFIX + key
> ).init();
>
> for ( final Integer periodId : periodIds )
> {
> @@ -161,6 +160,16 @@
> crossTabStore.dropCrossTabTable( key );
> }
>
> + public void createAggregatedDataCache( List<DataElementOperand>
> operands, String key )
> + {
> + crossTabStore.createAggregatedDataCache( operands, key );
> + }
> +
> + public void dropAggregatedDataCache( String key )
> + {
> + crossTabStore.dropAggregatedDataCache( key );
> + }
> +
> public Collection<CrossTabDataValue> getCrossTabDataValues(
> Collection<DataElementOperand> operands,
> Collection<Integer> periodIds, Collection<Integer> sourceIds,
> String key )
> {
> @@ -172,6 +181,12 @@
> {
> return crossTabStore.getCrossTabDataValues( operands, periodIds,
> sourceId, key );
> }
> +
> + public Map<DataElementOperand, Double> getAggregatedDataCacheValue(
> Collection<DataElementOperand> operands,
> + int periodId, int sourceId, String key )
> + {
> + return crossTabStore.getAggregatedDataCacheValue( operands,
> periodId, sourceId, key );
> + }
>
> //
> -------------------------------------------------------------------------
> // Supportive methods
>
> === modified file
> 'dhis-2/dhis-services/dhis-service-datamart-default/src/main/java/org/hisp/dhis/datamart/crosstab/jdbc/CrossTabStore.java'
> ---
> dhis-2/dhis-services/dhis-service-datamart-default/src/main/java/org/hisp/dhis/datamart/crosstab/jdbc/CrossTabStore.java
> 2011-06-04 13:52:42 +0000
> +++
> dhis-2/dhis-services/dhis-service-datamart-default/src/main/java/org/hisp/dhis/datamart/crosstab/jdbc/CrossTabStore.java
> 2011-06-04 16:56:41 +0000
> @@ -29,6 +29,7 @@
>
> import java.util.Collection;
> import java.util.List;
> +import java.util.Map;
>
> import org.hisp.dhis.dataelement.DataElementOperand;
> import org.hisp.dhis.datamart.CrossTabDataValue;
> @@ -40,8 +41,8 @@
> public interface CrossTabStore
> {
> final String ID = CrossTabStore.class.getName();
> - final String TABLE_PREFIX = "datavaluecrosstab_";
> - final String TABLE_PREFIX_TRIMMED = "datavaluecrosstabtrimmed_";
> + final String CROSSTAB_TABLE_PREFIX = "crosstab_table_";
> + final String AGGREGATEDDATA_CACHE_PREFIX = "aggregateddata_cache_";
>
> /**
> * Creates a crosstab table where the first column is the period
> identifier,
> @@ -57,6 +58,10 @@
> */
> void dropCrossTabTable( String key );
>
> + void createAggregatedDataCache( List<DataElementOperand> operands,
> String key );
> +
> + void dropAggregatedDataCache( String key );
> +
> /**
> * Gets all CrossTabDataValues for the given collection of period
> ids and source ids.
> *
> @@ -78,4 +83,6 @@
> */
> Collection<CrossTabDataValue> getCrossTabDataValues(
> Collection<DataElementOperand> operands, Collection<Integer> periodIds,
> int sourceId, String key );
> +
> + Map<DataElementOperand, Double> getAggregatedDataCacheValue(
> Collection<DataElementOperand> operands, int periodId, int sourceId, String
> key );
> }
>
> === modified file
> 'dhis-2/dhis-services/dhis-service-datamart-default/src/main/java/org/hisp/dhis/datamart/crosstab/jdbc/JDBCCrossTabStore.java'
> ---
> dhis-2/dhis-services/dhis-service-datamart-default/src/main/java/org/hisp/dhis/datamart/crosstab/jdbc/JDBCCrossTabStore.java
> 2011-06-04 13:52:42 +0000
> +++
> dhis-2/dhis-services/dhis-service-datamart-default/src/main/java/org/hisp/dhis/datamart/crosstab/jdbc/JDBCCrossTabStore.java
> 2011-06-04 16:56:41 +0000
> @@ -33,7 +33,9 @@
> import java.sql.SQLException;
> import java.util.ArrayList;
> import java.util.Collection;
> +import java.util.HashMap;
> import java.util.List;
> +import java.util.Map;
>
> import org.amplecode.quick.StatementHolder;
> import org.amplecode.quick.StatementManager;
> @@ -64,7 +66,7 @@
>
> public void createCrossTabTable( List<DataElementOperand> operands,
> String key )
> {
> - final StringBuffer sql = new StringBuffer( "CREATE TABLE " +
> TABLE_PREFIX + key + " ( " );
> + final StringBuffer sql = new StringBuffer( "CREATE TABLE " +
> CROSSTAB_TABLE_PREFIX + key + " ( " );
>
> sql.append( "periodid INTEGER NOT NULL, " );
> sql.append( "sourceid INTEGER NOT NULL, " );
> @@ -78,14 +80,34 @@
>
> statementManager.getHolder().executeUpdate( sql.toString() );
> }
> -
> +
> public void dropCrossTabTable( String key )
> {
> - final String sql = "DROP TABLE IF EXISTS " + TABLE_PREFIX +
> key;
> -
> - statementManager.getHolder().executeUpdate( sql );
> - }
> -
> + statementManager.getHolder().executeUpdate( "DROP TABLE IF
> EXISTS " + CROSSTAB_TABLE_PREFIX + key );
> + }
> +
> + public void createAggregatedDataCache( List<DataElementOperand>
> operands, String key )
> + {
> + final StringBuffer sql = new StringBuffer( "CREATE TABLE " +
> AGGREGATEDDATA_CACHE_PREFIX + key + " ( " );
> +
> + sql.append( "periodid INTEGER NOT NULL, " );
> + sql.append( "sourceid INTEGER NOT NULL, " );
> +
> + for ( DataElementOperand operand : operands )
> + {
> + sql.append( operand.getColumnName() ).append( " DOUBLE, "
> );
> + }
> +
> + sql.append( "PRIMARY KEY ( periodid, sourceid ) );" );
> +
> + statementManager.getHolder().executeUpdate( sql.toString() );
> + }
> +
> + public void dropAggregatedDataCache( String key )
> + {
> + statementManager.getHolder().executeUpdate( "DROP TABLE IF
> EXISTS " + AGGREGATEDDATA_CACHE_PREFIX + key );
> + }
> +
> //
> -------------------------------------------------------------------------
> // CrossTabDataValue
> //
> -------------------------------------------------------------------------
> @@ -95,7 +117,7 @@
> {
> final StatementHolder holder = statementManager.getHolder();
>
> - final String sql = "SELECT * FROM " + TABLE_PREFIX + key + " AS
> c WHERE c.periodid IN (" +
> + final String sql = "SELECT * FROM " + CROSSTAB_TABLE_PREFIX +
> key + " AS c WHERE c.periodid IN (" +
> getCommaDelimitedString( periodIds ) + ") AND c.sourceid IN
> (" + getCommaDelimitedString( sourceIds ) + ")";
>
> try
> @@ -119,7 +141,7 @@
> {
> final StatementHolder holder = statementManager.getHolder();
>
> - final String sql = "SELECT * FROM " + TABLE_PREFIX + key + " AS
> c WHERE c.periodid IN (" +
> + final String sql = "SELECT * FROM " + CROSSTAB_TABLE_PREFIX +
> key + " AS c WHERE c.periodid IN (" +
> getCommaDelimitedString( periodIds ) + ") AND c.sourceid = "
> + sourceId;
>
> try
> @@ -138,6 +160,44 @@
> }
> }
>
> + public Map<DataElementOperand, Double> getAggregatedDataCacheValue(
> Collection<DataElementOperand> operands,
> + int periodId, int sourceId, String key )
> + {
> + final StatementHolder holder = statementManager.getHolder();
> +
> + final String sql = "SELECT * FROM " +
> AGGREGATEDDATA_CACHE_PREFIX + key + " AS a WHERE a.periodid = " + periodId +
> " AND a.sourceid = " + sourceId;
> +
> + try
> + {
> + final Map<DataElementOperand, Double> valueMap = new
> HashMap<DataElementOperand, Double>();
> +
> + final ResultSet resultSet =
> holder.getStatement().executeQuery( sql );
> +
> + if ( resultSet.next() )
> + {
> + for ( DataElementOperand operand : operands )
> + {
> + final Double columnValue = resultSet.getDouble(
> operand.getColumnName() );
> +
> + if ( columnValue != null )
> + {
> + valueMap.put( operand, columnValue );
> + }
> + }
> + }
> +
> + return valueMap;
> + }
> + catch ( SQLException ex )
> + {
> + throw new RuntimeException( "Failed to get Map", ex );
> + }
> + finally
> + {
> + holder.close();
> + }
> + }
> +
> //
> -------------------------------------------------------------------------
> // Supportive methods
> //
> -------------------------------------------------------------------------
> @@ -156,9 +216,7 @@
>
> for ( DataElementOperand operand : operands )
> {
> - final String columnName = operand.getColumnName();
> -
> - final String columnValue = resultSet.getString(
> columnName );
> + final String columnValue = resultSet.getString(
> operand.getColumnName() );
>
> if ( columnValue != null )
> {
>
> === modified file
> 'dhis-2/dhis-services/dhis-service-datamart-default/src/main/java/org/hisp/dhis/datamart/dataelement/DataElementDataMart.java'
> ---
> dhis-2/dhis-services/dhis-service-datamart-default/src/main/java/org/hisp/dhis/datamart/dataelement/DataElementDataMart.java
> 2011-06-03 21:33:10 +0000
> +++
> dhis-2/dhis-services/dhis-service-datamart-default/src/main/java/org/hisp/dhis/datamart/dataelement/DataElementDataMart.java
> 2011-06-05 16:40:02 +0000
> @@ -30,6 +30,7 @@
> import java.util.Collection;
>
> import org.hisp.dhis.dataelement.DataElementOperand;
> +import org.hisp.dhis.datamart.DataElementOperandList;
> import org.hisp.dhis.organisationunit.OrganisationUnit;
> import org.hisp.dhis.period.Period;
>
> @@ -39,5 +40,5 @@
> public interface DataElementDataMart
> {
> int exportDataValues( Collection<DataElementOperand> operands,
> Collection<Period> periods,
> - Collection<OrganisationUnit> organisationUnits, String key );
> + Collection<OrganisationUnit> organisationUnits,
> DataElementOperandList operandList, String key );
> }
>
> === modified file
> 'dhis-2/dhis-services/dhis-service-datamart-default/src/main/java/org/hisp/dhis/datamart/dataelement/DefaultDataElementDataMart.java'
> ---
> dhis-2/dhis-services/dhis-service-datamart-default/src/main/java/org/hisp/dhis/datamart/dataelement/DefaultDataElementDataMart.java
> 2011-06-03 21:33:10 +0000
> +++
> dhis-2/dhis-services/dhis-service-datamart-default/src/main/java/org/hisp/dhis/datamart/dataelement/DefaultDataElementDataMart.java
> 2011-06-05 16:40:02 +0000
> @@ -28,6 +28,7 @@
> */
>
> import static org.hisp.dhis.system.util.MathUtils.getRounded;
> +import static org.hisp.dhis.datamart.crosstab.jdbc.CrossTabStore.*;
>
> import java.util.Collection;
> import java.util.HashMap;
> @@ -40,9 +41,11 @@
> import org.apache.commons.logging.LogFactory;
> import org.hisp.dhis.aggregation.AggregatedDataValue;
> import org.hisp.dhis.dataelement.DataElementOperand;
> +import org.hisp.dhis.datamart.DataElementOperandList;
> import org.hisp.dhis.datamart.aggregation.cache.AggregationCache;
> import
> org.hisp.dhis.datamart.aggregation.dataelement.DataElementAggregator;
> import org.hisp.dhis.jdbc.batchhandler.AggregatedDataValueBatchHandler;
> +import org.hisp.dhis.jdbc.batchhandler.GenericBatchHandler;
> import org.hisp.dhis.organisationunit.OrganisationUnit;
> import org.hisp.dhis.organisationunit.OrganisationUnitHierarchy;
> import org.hisp.dhis.organisationunit.OrganisationUnitService;
> @@ -75,7 +78,14 @@
> {
> this.batchHandlerFactory = batchHandlerFactory;
> }
> +
> + private BatchHandlerFactory inMemoryBatchHandlerFactory;
>
> + public void setInMemoryBatchHandlerFactory( BatchHandlerFactory
> inMemoryBatchHandlerFactory )
> + {
> + this.inMemoryBatchHandlerFactory = inMemoryBatchHandlerFactory;
> + }
> +
> private AggregationCache aggregationCache;
>
> public void setAggregationCache( AggregationCache aggregationCache )
> @@ -123,15 +133,17 @@
> //
> -------------------------------------------------------------------------
>
> public int exportDataValues( final Collection<DataElementOperand>
> operands, final Collection<Period> periods,
> - final Collection<OrganisationUnit> organisationUnits, String
> key )
> + final Collection<OrganisationUnit> organisationUnits,
> DataElementOperandList operandList, String key )
> {
> final BatchHandler<AggregatedDataValue> batchHandler =
> batchHandlerFactory.createBatchHandler(
> AggregatedDataValueBatchHandler.class ).init();
>
> + final BatchHandler<Object> cacheHandler =
> inMemoryBatchHandlerFactory.createBatchHandler( GenericBatchHandler.class
> ).setTableName( AGGREGATEDDATA_CACHE_PREFIX + key ).init();
> +
> final OrganisationUnitHierarchy hierarchy =
> organisationUnitService.getOrganisationUnitHierarchy().prepareChildren(
> organisationUnits );
>
> int count = 0;
>
> - final AggregatedDataValue value = new AggregatedDataValue();
> + final AggregatedDataValue aggregatedValue = new
> AggregatedDataValue();
>
> for ( final Period period : periods )
> {
> @@ -143,6 +155,8 @@
>
> for ( final OrganisationUnit unit : organisationUnits )
> {
> + operandList.init( period, unit );
> +
> final int level =
> aggregationCache.getLevelOfOrganisationUnit( unit.getId() );
>
> final Map<DataElementOperand, Double> valueMap = new
> HashMap<DataElementOperand, Double>();
> @@ -153,22 +167,33 @@
> valueMap.putAll( sumBoolAggregator.getAggregatedValues(
> sumBoolOperands, period, unit, level, hierarchy, key ) );
> valueMap.putAll(
> averageBoolAggregator.getAggregatedValues( averageBoolOperands, period,
> unit, level, hierarchy, key ) );
>
> + //TODO check size of value map
> +
> for ( Entry<DataElementOperand, Double> entry :
> valueMap.entrySet() )
> {
> - value.clear();
> -
> - value.setDataElementId(
> entry.getKey().getDataElementId() );
> - value.setCategoryOptionComboId(
> entry.getKey().getOptionComboId() );
> - value.setPeriodId( period.getId() );
> - value.setPeriodTypeId(
> period.getPeriodType().getId() );
> - value.setOrganisationUnitId( unit.getId() );
> - value.setLevel( level );
> - value.setValue( getRounded( entry.getValue(),
> DECIMALS ) );
> -
> - batchHandler.addObject( value );
> + aggregatedValue.clear();
> +
> + final double value = getRounded( entry.getValue(),
> DECIMALS );
> +
> + aggregatedValue.setDataElementId(
> entry.getKey().getDataElementId() );
> + aggregatedValue.setCategoryOptionComboId(
> entry.getKey().getOptionComboId() );
> + aggregatedValue.setPeriodId( period.getId() );
> + aggregatedValue.setPeriodTypeId(
> period.getPeriodType().getId() );
> + aggregatedValue.setOrganisationUnitId( unit.getId()
> );
> + aggregatedValue.setLevel( level );
> + aggregatedValue.setValue( value );
> +
> + batchHandler.addObject( aggregatedValue );
> +
> + operandList.addValue( entry.getKey(), value );
>
> count++;
> }
> +
> + if ( operandList.hasValues() )
> + {
> + cacheHandler.addObject( operandList.getList() );
> + }
> }
>
> log.debug( "Exported data values for period: " + period );
> @@ -176,6 +201,8 @@
>
> batchHandler.flush();
>
> + cacheHandler.flush();
> +
> return count;
> }
> }
>
> === modified file
> 'dhis-2/dhis-services/dhis-service-datamart-default/src/main/java/org/hisp/dhis/datamart/engine/DefaultDataMartEngine.java'
> ---
> dhis-2/dhis-services/dhis-service-datamart-default/src/main/java/org/hisp/dhis/datamart/engine/DefaultDataMartEngine.java
> 2011-06-04 13:52:42 +0000
> +++
> dhis-2/dhis-services/dhis-service-datamart-default/src/main/java/org/hisp/dhis/datamart/engine/DefaultDataMartEngine.java
> 2011-06-05 16:45:59 +0000
> @@ -30,6 +30,7 @@
> import java.util.ArrayList;
> import java.util.Collection;
> import java.util.HashSet;
> +import java.util.List;
> import java.util.Set;
>
> import org.apache.commons.logging.Log;
> @@ -40,6 +41,7 @@
> import org.hisp.dhis.dataelement.DataElementCategoryService;
> import org.hisp.dhis.dataelement.DataElementOperand;
> import org.hisp.dhis.dataelement.DataElementService;
> +import org.hisp.dhis.datamart.DataElementOperandList;
> import org.hisp.dhis.datamart.aggregation.cache.AggregationCache;
> import org.hisp.dhis.datamart.crosstab.CrossTabService;
> import org.hisp.dhis.datamart.dataelement.DataElementDataMart;
> @@ -54,7 +56,6 @@
> import org.hisp.dhis.system.util.ConversionUtils;
> import org.hisp.dhis.system.util.TimeUtils;
> import org.springframework.transaction.annotation.Transactional;
> -import org.springframework.util.CollectionUtils;
>
> /**
> * @author Lars Helge Overland
> @@ -181,7 +182,7 @@
> //
> ---------------------------------------------------------------------
>
> Collection<DataElementOperand> dataElementOperands =
> categoryService.getOperands( dataElements );
> - Collection<DataElementOperand> indicatorOperands =
> categoryService.populateOperands( getOperandsInIndicators( indicators ) );
> + List<DataElementOperand> indicatorOperands = new
> ArrayList<DataElementOperand>( categoryService.populateOperands(
> getOperandsInIndicators( indicators ) ) );
>
> Set<DataElementOperand> allOperands = new
> HashSet<DataElementOperand>();
> allOperands.addAll( dataElementOperands );
> @@ -195,13 +196,12 @@
>
> allOperands = crossTabService.getOperandsWithData( allOperands
> );
>
> - dataElementOperands.retainAll( allOperands );
> indicatorOperands.retainAll( allOperands );
>
> log.info( "Number of operands with data: " + allOperands.size()
> + ", "+ TimeUtils.getHMS() );
>
> //
> ---------------------------------------------------------------------
> - // Create and trim crosstabtable
> + // Create crosstabtable
> //
> ---------------------------------------------------------------------
>
> state.setMessage( "crosstabulating_data" );
> @@ -211,14 +211,19 @@
>
> String key = crossTabService.populateCrossTabTable( new
> ArrayList<DataElementOperand>( allOperands ), intersectingPeriodIds,
> childrenIds );
>
> - if ( CollectionUtils.isEmpty( allOperands ) )
> - {
> - return 0;
> - }
> -
> log.info( "Populated crosstab table: " + TimeUtils.getHMS() );
>
> //
> ---------------------------------------------------------------------
> + // Create aggregated data cache
> + //
> ---------------------------------------------------------------------
> +
> + DataElementOperandList operandList = new
> DataElementOperandList( indicatorOperands );
> +
> + crossTabService.createAggregatedDataCache( indicatorOperands,
> key );
> +
> + log.info( "Created aggregated data cache" );
> +
> + //
> ---------------------------------------------------------------------
> // Drop potential indexes
> //
> ---------------------------------------------------------------------
>
> @@ -246,11 +251,11 @@
>
> state.setMessage( "exporting_data_for_data_elements" );
>
> - if ( dataElementOperands.size() > 0 )
> + if ( allOperands.size() > 0 )
> {
> - count += dataElementDataMart.exportDataValues(
> dataElementOperands, periods, organisationUnits, key );
> + count += dataElementDataMart.exportDataValues( allOperands,
> periods, organisationUnits, operandList, key );
>
> - log.info( "Exported values for data element operands (" +
> dataElementOperands.size() + "): " + TimeUtils.getHMS() );
> + log.info( "Exported values for data element operands (" +
> allOperands.size() + "): " + TimeUtils.getHMS() );
> }
>
> //
> ---------------------------------------------------------------------
> @@ -267,12 +272,14 @@
> }
>
> //
> ---------------------------------------------------------------------
> - // Drop crosstab tables
> + // Drop crosstab table and aggregated data cache
> //
> ---------------------------------------------------------------------
>
> - crossTabService.dropCrossTabTable( key );
> -
> - log.info( "Dropped crosstab table: " + TimeUtils.getHMS() );
> + crossTabService.dropCrossTabTable( key ); //TODO move before
> indicator export
> +
> + crossTabService.dropAggregatedDataCache( key );
> +
> + log.info( "Dropped crosstab table and aggregated data cache: "
> + TimeUtils.getHMS() );
>
> //
> ---------------------------------------------------------------------
> // Create potential indexes
>
> === modified file
> 'dhis-2/dhis-services/dhis-service-datamart-default/src/main/java/org/hisp/dhis/datamart/indicator/DefaultIndicatorDataMart.java'
> ---
> dhis-2/dhis-services/dhis-service-datamart-default/src/main/java/org/hisp/dhis/datamart/indicator/DefaultIndicatorDataMart.java
> 2011-06-03 21:33:10 +0000
> +++
> dhis-2/dhis-services/dhis-service-datamart-default/src/main/java/org/hisp/dhis/datamart/indicator/DefaultIndicatorDataMart.java
> 2011-06-05 16:40:02 +0000
> @@ -28,12 +28,11 @@
> */
>
> import static
> org.hisp.dhis.options.SystemSettingManager.KEY_OMIT_INDICATORS_ZERO_NUMERATOR_DATAMART;
> +import static org.hisp.dhis.system.util.DateUtils.daysBetween;
> import static org.hisp.dhis.system.util.MathUtils.calculateExpression;
> import static org.hisp.dhis.system.util.MathUtils.getRounded;
> -import static org.hisp.dhis.system.util.DateUtils.daysBetween;
>
> import java.util.Collection;
> -import java.util.HashMap;
> import java.util.Map;
>
> import org.amplecode.quick.BatchHandler;
> @@ -43,14 +42,12 @@
> import org.hisp.dhis.aggregation.AggregatedIndicatorValue;
> import org.hisp.dhis.dataelement.DataElementOperand;
> import org.hisp.dhis.datamart.aggregation.cache.AggregationCache;
> -import
> org.hisp.dhis.datamart.aggregation.dataelement.DataElementAggregator;
> +import org.hisp.dhis.datamart.crosstab.CrossTabService;
> import org.hisp.dhis.expression.ExpressionService;
> import org.hisp.dhis.indicator.Indicator;
> import
> org.hisp.dhis.jdbc.batchhandler.AggregatedIndicatorValueBatchHandler;
> import org.hisp.dhis.options.SystemSettingManager;
> import org.hisp.dhis.organisationunit.OrganisationUnit;
> -import org.hisp.dhis.organisationunit.OrganisationUnitHierarchy;
> -import org.hisp.dhis.organisationunit.OrganisationUnitService;
> import org.hisp.dhis.period.Period;
> import org.hisp.dhis.period.PeriodType;
> import org.hisp.dhis.system.util.DateUtils;
> @@ -72,40 +69,12 @@
> // Dependencies
> //
> -------------------------------------------------------------------------
>
> - private OrganisationUnitService organisationUnitService;
> -
> - public void setOrganisationUnitService( OrganisationUnitService
> organisationUnitService )
> - {
> - this.organisationUnitService = organisationUnitService;
> - }
> -
> private ExpressionService expressionService;
>
> public void setExpressionService( ExpressionService
> expressionService )
> {
> this.expressionService = expressionService;
> }
> -
> - private DataElementAggregator sumIntAggregator;
> -
> - public void setSumIntAggregator( DataElementAggregator
> sumIntDataElementAggregator )
> - {
> - this.sumIntAggregator = sumIntDataElementAggregator;
> - }
> -
> - private DataElementAggregator averageIntAggregator;
> -
> - public void setAverageIntAggregator( DataElementAggregator
> averageIntDataElementAggregator )
> - {
> - this.averageIntAggregator = averageIntDataElementAggregator;
> - }
> -
> - private DataElementAggregator averageIntSingleValueAggregator;
> -
> - public void setAverageIntSingleValueAggregator(
> DataElementAggregator averageIntSingleValueAggregator )
> - {
> - this.averageIntSingleValueAggregator =
> averageIntSingleValueAggregator;
> - }
>
> private AggregationCache aggregationCache;
>
> @@ -121,13 +90,20 @@
> this.systemSettingManager = systemSettingManager;
> }
>
> + private CrossTabService crossTabService;
> +
> + public void setCrossTabService( CrossTabService crossTabService )
> + {
> + this.crossTabService = crossTabService;
> + }
> +
> private BatchHandlerFactory batchHandlerFactory;
>
> public void setBatchHandlerFactory( BatchHandlerFactory
> batchHandlerFactory )
> {
> this.batchHandlerFactory = batchHandlerFactory;
> }
> -
> +
> //
> -------------------------------------------------------------------------
> // IndicatorDataMart implementation
> //
> -------------------------------------------------------------------------
> @@ -137,15 +113,8 @@
> {
> final BatchHandler<AggregatedIndicatorValue> batchHandler =
> batchHandlerFactory.createBatchHandler(
> AggregatedIndicatorValueBatchHandler.class ).init();
>
> - final OrganisationUnitHierarchy hierarchy =
> organisationUnitService.getOrganisationUnitHierarchy().prepareChildren(
> organisationUnits );
> -
> int count = 0;
>
> - double annualizationFactor = 0.0;
> - double factor = 0.0;
> - double aggregatedValue = 0.0;
> - double annualizedFactor = 0.0;
> -
> final boolean omitZeroNumerator = (Boolean)
> systemSettingManager.getSystemSetting(
> KEY_OMIT_INDICATORS_ZERO_NUMERATOR_DATAMART, false );
>
> final AggregatedIndicatorValue indicatorValue = new
> AggregatedIndicatorValue();
> @@ -156,57 +125,47 @@
>
> final PeriodType periodType = period.getPeriodType();
>
> - final Collection<DataElementOperand> sumOperands =
> sumIntAggregator.filterOperands( operands, periodType );
> - final Collection<DataElementOperand> averageOperands =
> averageIntAggregator.filterOperands( operands, periodType );
> - final Collection<DataElementOperand>
> averageSingleValueOperands = averageIntSingleValueAggregator.filterOperands(
> operands, periodType );
> -
> for ( final OrganisationUnit unit : organisationUnits )
> {
> final int level =
> aggregationCache.getLevelOfOrganisationUnit( unit.getId() );
>
> - final Map<DataElementOperand, Double> sumIntValueMap =
> sumIntAggregator.getAggregatedValues( sumOperands, period, unit, level,
> hierarchy, key );
> - final Map<DataElementOperand, Double>
> averageIntValueMap = averageIntAggregator.getAggregatedValues(
> averageOperands, period, unit, level, hierarchy, key );
> - final Map<DataElementOperand, Double>
> averageIntSingleValueMap =
> averageIntSingleValueAggregator.getAggregatedValues(
> averageSingleValueOperands, period, unit, level, hierarchy, key );
> -
> - final Map<DataElementOperand, Double> valueMap = new
> HashMap<DataElementOperand, Double>( sumIntValueMap );
> - valueMap.putAll( averageIntValueMap );
> - valueMap.putAll( averageIntSingleValueMap );
> -
> - for ( final Indicator indicator : indicators )
> - {
> - final double numeratorValue = calculateExpression(
> expressionService.generateExpression( indicator.getExplodedNumerator(),
> valueMap, days ) );
> - final double denominatorValue =
> calculateExpression( expressionService.generateExpression(
> indicator.getExplodedDenominator(), valueMap, days ) );
> -
> - //
> ---------------------------------------------------------
> - // AggregatedIndicatorValue
> - //
> ---------------------------------------------------------
> -
> - if ( denominatorValue != 0 && !( omitZeroNumerator
> && numeratorValue == 0 ) )
> + final Map<DataElementOperand, Double> valueMap =
> crossTabService.getAggregatedDataCacheValue( operands, period.getId(),
> unit.getId(), key );
> +
> + if ( valueMap.size() > 0 )
> + {
> + for ( final Indicator indicator : indicators )
> {
> - annualizationFactor =
> DateUtils.getAnnualizationFactor( indicator, period.getStartDate(),
> period.getEndDate() );
> -
> - factor =
> indicator.getIndicatorType().getFactor();
> -
> - aggregatedValue = ( numeratorValue /
> denominatorValue ) * factor * annualizationFactor;
> -
> - annualizedFactor = factor *
> annualizationFactor;
> -
> - indicatorValue.clear();
> -
> - indicatorValue.setIndicatorId(
> indicator.getId() );
> - indicatorValue.setPeriodId( period.getId() );
> - indicatorValue.setPeriodTypeId(
> periodType.getId() );
> - indicatorValue.setOrganisationUnitId(
> unit.getId() );
> - indicatorValue.setLevel( level );
> - indicatorValue.setAnnualized(
> getAnnualizationString( indicator.getAnnualized() ) );
> - indicatorValue.setFactor( annualizedFactor);
> - indicatorValue.setValue( getRounded(
> aggregatedValue, DECIMALS ) );
> - indicatorValue.setNumeratorValue( getRounded(
> numeratorValue, DECIMALS ) );
> - indicatorValue.setDenominatorValue( getRounded(
> denominatorValue, DECIMALS ) );
> -
> - batchHandler.addObject( indicatorValue );
> -
> - count++;
> + final double numeratorValue =
> calculateExpression( expressionService.generateExpression(
> indicator.getExplodedNumerator(), valueMap, days ) );
> + final double denominatorValue =
> calculateExpression( expressionService.generateExpression(
> indicator.getExplodedDenominator(), valueMap, days ) );
> +
> + //
> ---------------------------------------------------------
> + // AggregatedIndicatorValue
> + //
> ---------------------------------------------------------
> +
> + if ( denominatorValue != 0 && !(
> omitZeroNumerator && numeratorValue == 0 ) )
> + {
> + final double annualizationFactor =
> DateUtils.getAnnualizationFactor( indicator, period.getStartDate(),
> period.getEndDate() );
> + final double factor =
> indicator.getIndicatorType().getFactor();
> + final double aggregatedValue = (
> numeratorValue / denominatorValue ) * factor * annualizationFactor;
> + final double annualizedFactor = factor *
> annualizationFactor;
> +
> + indicatorValue.clear();
> +
> + indicatorValue.setIndicatorId(
> indicator.getId() );
> + indicatorValue.setPeriodId( period.getId()
> );
> + indicatorValue.setPeriodTypeId(
> periodType.getId() );
> + indicatorValue.setOrganisationUnitId(
> unit.getId() );
> + indicatorValue.setLevel( level );
> + indicatorValue.setAnnualized(
> getAnnualizationString( indicator.getAnnualized() ) );
> + indicatorValue.setFactor(
> annualizedFactor);
> + indicatorValue.setValue( getRounded(
> aggregatedValue, DECIMALS ) );
> + indicatorValue.setNumeratorValue(
> getRounded( numeratorValue, DECIMALS ) );
> + indicatorValue.setDenominatorValue(
> getRounded( denominatorValue, DECIMALS ) );
> +
> + batchHandler.addObject( indicatorValue );
> +
> + count++;
> + }
> }
> }
> }
>
> === modified file
> 'dhis-2/dhis-services/dhis-service-datamart-default/src/main/resources/META-INF/dhis/beans.xml'
> ---
> dhis-2/dhis-services/dhis-service-datamart-default/src/main/resources/META-INF/dhis/beans.xml
> 2011-06-03 21:33:10 +0000
> +++
> dhis-2/dhis-services/dhis-service-datamart-default/src/main/resources/META-INF/dhis/beans.xml
> 2011-06-05 16:40:02 +0000
> @@ -140,6 +140,7 @@
> <property name="organisationUnitService"
> ref="org.hisp.dhis.organisationunit.OrganisationUnitService"/>
> <property name="batchHandlerFactory" ref="batchHandlerFactory"/>
> + <property name="inMemoryBatchHandlerFactory"
> ref="inMemoryBatchHandlerFactory"/>
> <property name="aggregationCache"
> ref="org.hisp.dhis.datamart.aggregation.cache.AggregationCache"/>
> <property name="sumIntAggregator"
> @@ -158,20 +159,14 @@
>
> <bean id="org.hisp.dhis.datamart.indicator.IndicatorDataMart"
> class="org.hisp.dhis.datamart.indicator.DefaultIndicatorDataMart">
> - <property name="organisationUnitService"
> - ref="org.hisp.dhis.organisationunit.OrganisationUnitService"/>
> <property name="expressionService"
> ref="org.hisp.dhis.expression.ExpressionService"/>
> - <property name="sumIntAggregator"
> -
> ref="org.hisp.dhis.datamart.aggregation.dataelement.SumIntAggregator"/>
> - <property name="averageIntAggregator"
> -
> ref="org.hisp.dhis.datamart.aggregation.dataelement.AverageIntAggregator"/>
> - <property name="averageIntSingleValueAggregator"
> -
> ref="org.hisp.dhis.datamart.aggregation.dataelement.AverageIntSingleValueAggregator"/>
> <property name="aggregationCache"
> ref="org.hisp.dhis.datamart.aggregation.cache.AggregationCache"/>
> <property name="systemSettingManager"
> ref="org.hisp.dhis.options.SystemSettingManager"/>
> + <property name="crossTabService"
> + ref="org.hisp.dhis.datamart.crosstab.CrossTabService"/>
> <property name="batchHandlerFactory" ref="batchHandlerFactory"/>
> </bean>
>
>
> === modified file
> 'dhis-2/dhis-services/dhis-service-datamart-default/src/test/java/org/hisp/dhis/datamart/crosstab/CrossTabServiceTest.java'
> ---
> dhis-2/dhis-services/dhis-service-datamart-default/src/test/java/org/hisp/dhis/datamart/crosstab/CrossTabServiceTest.java
> 2011-06-04 13:52:42 +0000
> +++
> dhis-2/dhis-services/dhis-service-datamart-default/src/test/java/org/hisp/dhis/datamart/crosstab/CrossTabServiceTest.java
> 2011-06-04 16:56:41 +0000
> @@ -37,7 +37,11 @@
> import java.util.HashSet;
> import java.util.Iterator;
> import java.util.List;
> +import java.util.Map;
>
> +import org.amplecode.quick.BatchHandler;
> +import org.amplecode.quick.BatchHandlerFactory;
> +import org.apache.commons.lang.RandomStringUtils;
> import org.hisp.dhis.DhisTest;
> import org.hisp.dhis.dataelement.DataElement;
> import org.hisp.dhis.dataelement.DataElementCategory;
> @@ -48,7 +52,9 @@
> import org.hisp.dhis.dataelement.DataElementOperand;
> import org.hisp.dhis.dataelement.DataElementService;
> import org.hisp.dhis.datamart.CrossTabDataValue;
> +import org.hisp.dhis.datamart.crosstab.jdbc.CrossTabStore;
> import org.hisp.dhis.datavalue.DataValueService;
> +import org.hisp.dhis.jdbc.batchhandler.GenericBatchHandler;
> import org.hisp.dhis.organisationunit.OrganisationUnit;
> import org.hisp.dhis.organisationunit.OrganisationUnitService;
> import org.hisp.dhis.period.Period;
> @@ -65,6 +71,8 @@
> {
> private CrossTabService crossTabService;
>
> + private BatchHandlerFactory batchHandlerFactory;
> +
> private Iterator<Period> generatedPeriods;
>
> private List<DataElementOperand> operands;
> @@ -80,6 +88,8 @@
> {
> crossTabService = (CrossTabService) getBean( CrossTabService.ID
> );
>
> + batchHandlerFactory = (BatchHandlerFactory) getBean(
> "inMemoryBatchHandlerFactory" );
> +
> categoryService = (DataElementCategoryService) getBean(
> DataElementCategoryService.ID );
>
> dataElementService = (DataElementService) getBean(
> DataElementService.ID );
> @@ -203,4 +213,36 @@
> }
> }
> }
> +
> + @Test
> + public void testPopulateAggregatedDataCache()
> + {
> + String key = RandomStringUtils.randomAlphanumeric( 8 );
> +
> + crossTabService.createAggregatedDataCache( operands, key );
> +
> + BatchHandler<Object> batchHandler =
> batchHandlerFactory.createBatchHandler( GenericBatchHandler.class ).
> + setTableName( CrossTabStore.AGGREGATEDDATA_CACHE_PREFIX +
> key ).init();
> +
> + List<Object> valueList = new ArrayList<Object>( operands.size()
> + 2 );
> + valueList.add( 1 );
> + valueList.add( 1 );
> +
> + for ( int i = 0; i < operands.size(); i++ )
> + {
> + valueList.add( 10.0 );
> + }
> +
> + batchHandler.addObject( valueList );
> +
> + batchHandler.flush();
> +
> + Map<DataElementOperand, Double> valueMap =
> crossTabService.getAggregatedDataCacheValue( operands, 1, 1, key );
> +
> + for ( DataElementOperand operand : valueMap.keySet() )
> + {
> + assertNotNull( valueMap.get( operand ) );
> + assertEquals( 10.0, valueMap.get( operand ) );
> + }
> + }
> }
>
> === modified file
> 'dhis-2/dhis-services/dhis-service-datamart-default/src/test/java/org/hisp/dhis/datamart/crosstab/CrossTabStoreTest.java'
> ---
> dhis-2/dhis-services/dhis-service-datamart-default/src/test/java/org/hisp/dhis/datamart/crosstab/CrossTabStoreTest.java
> 2011-01-10 10:06:18 +0000
> +++
> dhis-2/dhis-services/dhis-service-datamart-default/src/test/java/org/hisp/dhis/datamart/crosstab/CrossTabStoreTest.java
> 2011-06-04 16:56:41 +0000
> @@ -51,4 +51,12 @@
>
> crossTabStore.dropCrossTabTable( key );
> }
> +
> + @Test
> + public void testDropAggregatedDataCache()
> + {
> + crossTabStore.createAggregatedDataCache( operands, key );
> +
> + crossTabStore.dropAggregatedDataCache( key );
> + }
> }
>
>
> _______________________________________________
> Mailing list: DHIS 2 developers in Launchpad
> Post to : dhis2-devs@lists.launchpad.net
> Unsubscribe : DHIS 2 developers in Launchpad
> More help : ListHelp - Launchpad Help
>
>

Hi
how can i add a country name and flag in DHIS 2.2.

Lungo

Just tell me what flag you are missing and I will add it.

Lars

···

2011/6/6 Juma Lungo jlungo@yahoo.com

Hi
how can i add a country name and flag in DHIS 2.2.

Lungo