dhis2-devs team mailing list archive
-
dhis2-devs team
-
Mailing list archive
-
Message #12462
Re: [Branch ~dhis2-devs-core/dhis2/trunk] Rev 3841: Data mart: caching aggregated data values in a denormalized in-memory db table during dataelement...
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@xxxxxxxxxxxxx> wrote:
> Merge authors:
> Lars Helge Øverland (larshelge)
> ------------------------------------------------------------
> revno: 3841 [merge]
> committer: Lars Helge Overland <larshelge@xxxxxxxxx>
> 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<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: https://launchpad.net/~dhis2-devs
> Post to : dhis2-devs@xxxxxxxxxxxxxxxxxxx
> Unsubscribe : https://launchpad.net/~dhis2-devs
> More help : https://help.launchpad.net/ListHelp
>
>
Follow ups
References
