dhis2-devs team mailing list archive
-
dhis2-devs team
-
Mailing list archive
-
Message #12463
Re: [Branch ~dhis2-devs-core/dhis2/trunk] Rev 3841: Data mart: caching aggregated data values in a denormalized in-memory db table during dataelement...
On Mon, Jun 6, 2011 at 7:22 AM, Jason Pickering <jason.p.pickering@xxxxxxxxx
> 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@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
